日本免费全黄少妇一区二区三区-高清无码一区二区三区四区-欧美中文字幕日韩在线观看-国产福利诱惑在线网站-国产中文字幕一区在线-亚洲欧美精品日韩一区-久久国产精品国产精品国产-国产精久久久久久一区二区三区-欧美亚洲国产精品久久久久

  1. 首頁 > 云知道 > >

如何用Python自動(dòng)化操作Excel Python辦公自動(dòng)化

云知道Python


如何用Python自動(dòng)化操作Excel Python辦公自動(dòng)化


自動(dòng)化辦公，我相信很多人都有強(qiáng)烈的需求，都希望從繁瑣重復(fù)的勞動(dòng)中掙脫出來，把精力用在有意義的事情上 。
來自知乎提問
現(xiàn)代辦公室里幾乎任何一項(xiàng)工作都會(huì)用到Excel、Word 。在之前文章中我已分享過Python自動(dòng)化操作word的文章《Python辦公自動(dòng)化｜只需三秒，一鍵生成數(shù)據(jù)分析報(bào)告》 。
私信小編01即可獲取大量Python學(xué)習(xí)資源
今天我教大家如何利用Python自動(dòng)化操作Excel，包括：介紹操作Excel的工具包、安裝方法及操作Excel具體方法 。對(duì)于每天有大量重復(fù)性工作的同學(xué)來說，這款工具絕對(duì)是福利 。
openpyxl是什么openpyxl是一個(gè)Python庫，用于讀取/寫入Excel xlsx / xlsm / xltx / xltm文件 。它的誕生是因?yàn)槿鄙倏蓮腜ython本地讀取/寫入Office Open XML格式的庫 。官方文檔：
http://yumos.gitee.io/openpyxl3.0
openpyxl安裝使用pip安裝openpyxl 。建議在不帶系統(tǒng)軟件包的Python virtualenv中執(zhí)行此操作：
pip install openpyxl【如何用Python自動(dòng)化操作Excel Python辦公自動(dòng)化】支持流行的lxml庫（如果已安裝） 。這在創(chuàng)建大文件時(shí)特別有用 。
openpyxl操作指南1、創(chuàng)建工作簿from openpyxl import Workbookwb = Workbook()ws_00 = wb.active #默認(rèn)不取名稱ws_00['A1']= 'Python學(xué)習(xí)與數(shù)據(jù)挖掘'ws_01 = wb.create_sheet("new_sheet", 0) # 取一個(gè)new_sheet的名稱ws_01['A1']= 23wb.save('/Users/***/Desktop/document.xlsx')
2、寫工作簿from openpyxl import Workbookfrom openpyxl.utils import get_column_letterwb = Workbook()dest_filename = '/Users/****/Desktop/empty_book.xlsx'ws1 = wb.activews1.title = "range names"for row in range(1, 40):ws1.append(range(600))ws2 = wb.create_sheet(title="Pi")ws2['F5'] = 3.14ws3 = wb.create_sheet(title="Data")for row in range(10, 20):for col in range(27, 54):_ = ws3.cell(column=col, row=row, value="https://www.shwenmu.com/wenda/{0}".format(get_column_letter(col)))wb.save(filename = dest_filename)3、插入圖片from openpyxl import Workbookfrom openpyxl.drawing.image import Imagewb = Workbook()ws = wb.activews['A1'] = 'You should see three logos below'img = Image('/Users/***/work/logo.png')ws.add_image(img, 'A1')wb.save('/Users/***/document01.xlsx')4、刪除行和列刪除列F:H
ws.delete_cols(6, 3)5、將工作表轉(zhuǎn)換為數(shù)據(jù)框df = DataFrame(ws.values)6、2D區(qū)域圖from openpyxl import Workbookfrom openpyxl.chart import ( AreaChart, Reference, Series, ) wb = Workbook()ws = wb.activerows = [['Number', 'Batch 1', 'Batch 2'],[2, 40, 30],[3, 40, 25],[4, 50, 30],[5, 30, 10],[6, 25, 5],[7, 50, 10],]for row in rows:ws.append(row)chart = AreaChart()chart.title = "Area Chart"chart.style = 13chart.x_axis.title = 'Test'chart.y_axis.title = 'Percentage'cats = Reference(ws, min_col=1, min_row=1, max_row=7)data = https://www.shwenmu.com/wenda/Reference(ws, min_col=2, min_row=1, max_col=3, max_row=7)chart.add_data(data, titles_from_data=True)chart.set_categories(cats)ws.add_chart(chart,"A10")wb.save("area.xlsx")
6、雷達(dá)圖from openpyxl import Workbookfrom openpyxl.chart import (RadarChart,Reference,)wb = Workbook()ws = wb.activerows = [['Month', "Bulbs", "Seeds", "Flowers", "Trees & shrubs"],['Jan', 0, 2500, 500, 0,],['Feb', 0, 5500, 750, 1500],['Mar', 0, 9000, 1500, 2500],['Apr', 0, 6500, 2000, 4000],['May', 0, 3500, 5500, 3500],['Jun', 0, 0, 7500, 1500],['Jul', 0, 0, 8500, 800],['Aug', 1500, 0, 7000, 550],['Sep', 5000, 0, 3500, 2500],['Oct', 8500, 0, 2500, 6000],['Nov', 3500, 0, 500, 5500],['Dec', 500, 0, 100, 3000 ],]for row in rows:ws.append(row)chart = RadarChart()chart.type = "filled"labels = Reference(ws, min_col=1, min_row=2, max_row=13)data = https://www.shwenmu.com/wenda/Reference(ws, min_col=2, max_col=5, min_row=1, max_row=13)chart.add_data(data, titles_from_data=True)chart.set_categories(labels)chart.style = 26chart.title ="Garden Centre Sales"chart.y_axis.delete = Truews.add_chart(chart, "A17")wb.save("radar.xlsx")

推薦閱讀