1. 首頁 > 云知道 > >

如何用Python自動化操作Excel Python辦公自動化

云知道Python


如何用Python自動化操作Excel Python辦公自動化


自動化辦公，我相信很多人都有強烈的需求，都希望從繁瑣重復的勞動中掙脫出來，把精力用在有意義的事情上 。
來自知乎提問
現代辦公室里幾乎任何一項工作都會用到Excel、Word 。在之前文章中我已分享過Python自動化操作word的文章《Python辦公自動化｜只需三秒，一鍵生成數據分析報告》 。
私信小編01即可獲取大量Python學習資源
今天我教大家如何利用Python自動化操作Excel，包括：介紹操作Excel的工具包、安裝方法及操作Excel具體方法 。對于每天有大量重復性工作的同學來說，這款工具絕對是福利 。
openpyxl是什么openpyxl是一個Python庫，用于讀取/寫入Excel xlsx / xlsm / xltx / xltm文件 。它的誕生是因為缺少可從Python本地讀取/寫入Office Open XML格式的庫 。官方文檔：
http://yumos.gitee.io/openpyxl3.0
openpyxl安裝使用pip安裝openpyxl 。建議在不帶系統軟件包的Python virtualenv中執行此操作：
pip install openpyxl【如何用Python自動化操作Excel Python辦公自動化】支持流行的lxml庫（如果已安裝） 。這在創建大文件時特別有用 。
openpyxl操作指南1、創建工作簿from openpyxl import Workbookwb = Workbook()ws_00 = wb.active #默認不取名稱ws_00['A1']= 'Python學習與數據挖掘'ws_01 = wb.create_sheet("new_sheet", 0) # 取一個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、將工作表轉換為數據框df = DataFrame(ws.values)6、2D區域圖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、雷達圖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")

推薦閱讀