表格資料是最常見的資料型別,Excel 是日常辦公中最常接觸的檔案。
其實 Python 處理 Excel 第三方模組主要有這幾個:
xlrd
、
xlwt
和
xlutils
,支援讀寫 Excel 檔案。
openpyxl
,支援讀寫和修改 OpenXML 格式的 Excel 檔案。
xlswriter
,支援寫檔案,以及更多 Excel 功能特性。
pandas
,底層依賴
xlrd
和
xlwt
讀寫 Excel 檔案,處理資料神器。
pywin32
,呼叫 Win32 API 操作檔案,只適用於 Windows。
xlwings
,可理解為
pywin32
的多平臺加強版,有商業版支援。
簡單做個對比分析:
第 1 類中 3 個模組,屬於同一作者的開源專案,目前已停更,不建議直接使用。
pywin32
和
xlwings
基於原生 Excel 軟體提供服務,需提前安裝軟體,讀寫效能和功能最佳,
xlwings
還有商業版本支援。
pandas
側重資料分析,Excel 檔案讀寫只是其資料輸入和輸出部分。
openpyxl
不需要依賴 Excel 功能,直接讀寫 OpenXML 內容,相容性好。
xlswriter
側重寫 Excel 檔案,支援 VBA 等更多特性。
建議的使用場景:
如果企業內大量使用 Excel,並用了很多如 VBA 宏的特性,建議用 xlwings。一方面功能全,另一方面可以採買商業付費版支援。
對於普通 Excel 檔案自動處理,建議用
openpyxl
,大部分情況下夠用。
GB 級以下的資料分析,建議用
pandas
,後續會單獨出一個數據分析系列來介紹它。
本文重點介紹
openpyxl
的使用,它足夠應付大部分人日常的表格自動化處理場景。
日常處理 Excel 檔案,最常見的應用場景有 3 類:
讀寫 Excel 檔案
合併、分割文件資料
報表自動化,批次生成圖表
模組安裝:
pip install openpyxl
基本使用
Excel 表格檔案中主要元素有這幾個:
sheet(工作表):一個 Excel 檔案可以有多個 sheet。
row/col(行和列),
iter_rows()
和
iter_cols()
可以返回所有行和列。
cell(單元格):和 Excel 一樣用
sheet[‘A1’]
表示,注意行列都以 1 開始。
formulae(公式):可以為單元格設定計算公式,和 Excel 概念一致。
charts(圖表):比如餅圖、柱狀圖等。
style(樣式):支援自定義樣式,以及條件格式化等。
image(圖片),支援
pillow
的影象處理。
開啟 Excel 檔案
import pathlibimport datetimefrom openpyxl import load_workbookpath = list(pathlib。Path。cwd()。parents)[1]。joinpath(‘data/automate/004excel’)in_path = path。joinpath(‘input。xlsx’)wb = load_workbook(in_path, read_only=True)for name in wb。sheetnames: ws = wb[name] print(f‘{name} 表有 {ws。max_row-ws。min_row+1} 行, {ws。max_column-ws。min_column+1} 列。’)
複製程式碼
讀取 Excel 檔案內的資料
import pathlibimport datetimefrom openpyxl import load_workbookpath = list(pathlib。Path。cwd()。parents)[1]。joinpath(‘data/automate/004excel’)in_path = path。joinpath(‘input。xlsx’)wb = load_workbook(in_path)ws = wb[wb。sheetnames[0]] # 第一個工作表# 操作單列for cell in ws[“A”]: print(cell。value)# 操作單行for cell in ws[“1”]: print(cell。value, end=‘\t’)# 操作多列for column in ws[‘A:C’]: for cell in column: print(cell。value)# 操作多行for row in ws[‘1:3’]: for cell in row: print(cell。value, end=‘\t’) print()# 指定範圍for row in ws[‘A1:C3’]: for cell in row: print(cell。value)# 所有行for row in ws。iter_rows(): for cell in row: print(cell。value)# 所有列for column in ws。iter_cols(): for cell in column: print(cell。value)
複製程式碼
建立 Excel 檔案
import pathlibimport datetimefrom openpyxl import Workbookpath = list(pathlib。Path。cwd()。parents)[1]。joinpath(‘data/automate/004excel’)out_path = path。joinpath(‘004excel_basic。xlsx’)sheet_path = path。joinpath(‘004excel_sheet。xlsx’)wb = Workbook()ws = wb。active # 找當前Sheetws[‘A1’] = 42ws。append([1, 2, 3]) # 新增行ws[‘A2’] = datetime。datetime。now()wb。save(out_path)# 建立第二個sheetwb。create_sheet(‘sheet2’)ws = wb[‘sheet2’]ws。title = ‘第二個工作表’ws。sheet_properties。tabColor = “FF0000”# 增加一整行資料ws。append([1, 2, 3, datetime。datetime。now()])ws。cell(row=1, column=3, value=10)# 獲取cell方式一d1 = ws。cell(row=1, column=4)# 獲取cell方式二d2 = ws[‘D1’]print(d1==d2)print(type(d2。value))wb。save(sheet_path)
複製程式碼
設定單元格樣式
單元格樣式可以用 NumberFormat(資料格式)、Alignment(對齊)、Font(字型)、Border(邊框)、PatternFill(填充)、Protection(保護)等來設定。
也可以透過
NamedStyle
自定義樣式,重複應用到單元格。
此外,透過
row_dimensions
和
column_dimensions
可以設定整行/整列的樣式。
import pathlibimport datetimefrom openpyxl import Workbookfrom openpyxl。styles import numbers, Font, GradientFill, PatternFill, Border, Side, Alignment, Protectionfrom openpyxl。styles import NamedStylepath = list(pathlib。Path。cwd()。parents)[1]。joinpath(‘data/automate/004excel’)out_path = path。joinpath(‘004excel_style。xlsx’)wb = Workbook()ws = wb。activews。cell(row=1, column=1, value=‘微軟雅黑’)。font = Font(name=‘微軟雅黑’, size=18, bold=True, color=‘123456’)ws。cell(row=2, column=1, value=‘居中對齊’)。alignment = Alignment(horizontal=‘center’, vertical=‘center’)ws。cell(row=3, column=1, value=‘填充漸變色’)。fill = GradientFill(stop=[‘FF0000’, ‘0000FF’])ws。cell(row=4, column=1, value=‘設定邊線’)。border = Border(left=Side(border_style=‘thin’, color=‘123456’), right= Side(border_style=‘thick’, color=‘FF0000’))ws。cell(row=5, column=1, value=‘受保護的’)。protection = Protection(locked=True, hidden=True)ws。cell(row=6, column=1, value=0。88)。number_format =numbers。FORMAT_PERCENTAGEws。column_dimensions[‘A’]。width = 80 # 修改列寬# 設定整行row = ws。row_dimensions[4]row。font = Font(name=‘宋體’, size=30, italic=True, color=‘FF0000’)# 設定整列column = ws。column_dimensions[“B”]column。fill = PatternFill(“solid”, fgColor=“1874CD”)# 自定義樣式my_style = NamedStyle(name=‘my_style’, font=Font(name=‘宋體’,size=12, color=‘FF000000’), fill=PatternFill(fill_type=‘solid’, fgColor=“FFDAB9”), border=Border(top=Side(border_style=‘thin’, color=‘FF000000’)), alignment=Alignment(horizontal=‘left’,vertical=‘center’))ws[‘B4’] = ‘自定義樣式’ws[‘B4’]。style=my_stylewb。save(out_path)
複製程式碼
合併單元格
合併單元格:
merge_cells()
解除合併:
unmerge_cells()
注意:對於沒有合併過單元格的位置呼叫
unmerge_cells()
會出錯;此外合併的單元格順序是從左上角到右下角。
import pathlibimport datetimefrom openpyxl import Workbookfrom openpyxl。styles import numbers, Font, GradientFill, Border, Side, Alignment, Protectionpath = list(pathlib。Path。cwd()。parents)[1]。joinpath(‘data/automate/004excel’)out_path = path。joinpath(‘004excel_cell_merge。xlsx’)wb = Workbook()ws = wb。active# 合併,必須是從左上角,到右下角的順序ws。merge_cells(‘A3:D5’)ws。merge_cells(‘A7:D10’)# 合併後以起點為代表讀寫值,其他的cell都被設定為只讀ws[‘A3’]=‘test’# 解除合併ws。unmerge_cells(‘A7:D10’)# 也可以用起止行列數字來選取合併區域ws。merge_cells(start_row=12,start_column=11,end_row=15,end_column=14)ws[‘K12’]=‘合併過’ws。unmerge_cells(start_row=12,start_column=11,end_row=15,end_column=14)wb。save(out_path)
複製程式碼
圖表和公式應用
openpyxl
支援的圖表都在
openpyxl。chart
包中,比如直方圖、餅圖、折線圖等。
可以直接把單元格的值設定為公式,在 Excel 軟體中可以獲得結果,但
openpyxl
並不支援公式的計算。
如果生成的
xlsx
檔案被 Excel 軟體開啟過,這時公式的計算結果會快取在檔案中,這時用
openpyxl
開啟檔案時候,增加一個
data_only=True
引數,也可以讀取到計算結果。
import pathlibimport datetimefrom openpyxl import Workbookfrom openpyxl。chart import BarChart, PieChart, LineChart, Referencepath = list(pathlib。Path。cwd()。parents)[1]。joinpath(‘data/automate/004excel’)out_path = path。joinpath(‘004excel_graph。xlsx’)wb = Workbook()ws = wb。active# 裝載資料data = [ (‘月份’, ‘產品A銷量’, ‘產品B銷量’), (1, 100, 200), (2, 106, 100), (3, 120, 200), (4, 180, 100), (5, 200, 300), (6, 280, 400), (7, 400, 200), (8, 500, 800), (9, 600, 1000), (10, 800, 300),]for d in data: ws。append(d)# 用Python做些統計ws[‘A12’] = ‘彙總累加’ws[‘B12’] = sum([d[1] for i, d in enumerate(data) if i > 0])ws[‘C12’] = sum([d[2] for i, d in enumerate(data) if i > 0])# 用公式統計ws[‘A13’] = ‘公式累加’ws[‘B13’] = ‘=SUM(B2:B11)’ws[‘C13’] = ‘=SUM(C2:C11)’# 增加一列總銷量ws[‘D1’] = ‘當月總銷量’for i in range(2, 13): ws[f‘D{i}’] = ws[f‘C{i}’]。value + ws[f‘B{i}’]。value# 列狀圖ct_bar= BarChart()ct_bar。type = “col” # 列狀圖ct_bar。style = 10ct_bar。title = “銷量柱狀圖”ct_bar。y_axis。title = ‘銷量’ct_bar。x_axis。title = ‘月份’d_ref = Reference(ws, min_col=2, min_row=1, max_row=11, max_col=3)series = Reference(ws, min_col=1, min_row=2, max_row=11)ct_bar。add_data(d_ref, titles_from_data=True)ct_bar。set_categories(series)ws。add_chart(ct_bar, ‘A15’)# 餅圖ct_pie = PieChart()ct_pie。title = “產品銷量佔比”d_ref = Reference(ws, min_col=2, min_row=12, max_col=3)series = Reference(ws, min_col=2, min_row=1, max_col=3)ct_pie。add_data(d_ref, titles_from_data=True)ct_pie。set_categories(series)ct_pie。splitType=‘val’ws。add_chart(ct_pie, ‘I15’)# 一個圖兩個軸ct_bar = BarChart()d_ref = Reference(ws, min_col=2, min_row=1, max_row=11, max_col=3)ct_bar。add_data(d_ref, titles_from_data=True)ct_bar。x_axis。title = ‘月份’ct_bar。y_axis。title = ‘產品AB銷量’ct_bar。y_axis。majorGridlines = Nonect_bar。title = ‘銷量分析’ct_line = LineChart()d_ref = Reference(ws, min_col=4, min_row=1, max_row=11)ct_line。add_data(d_ref, titles_from_data=True)ct_line。y_axis。axId = 200 # 不為空即可ct_line。y_axis。title = ‘總銷量’# 讓線條和第一圖的最大值相交ct_line。y_axis。crosses = ‘max’ct_bar += ct_line # 只支援+=賦值,不能直接+ws。add_chart(ct_bar, ‘A31’)wb。save(out_path)
複製程式碼
插入影象
openpyxl
的影象處理基於
PIL。Image
。
PIL。Image
的寬和高單位預設使用 Pixel(畫素),如果需要對映到 Excel 中的位置,可以用
pixels_to_EMU()
轉換。
import pathlibfrom openpyxl import Workbook, load_workbookfrom openpyxl。drawing。image import Imagefrom openpyxl。drawing。spreadsheet_drawing import AbsoluteAnchorfrom openpyxl。drawing。xdr import XDRPoint2D, XDRPositiveSize2Dfrom openpyxl。utils。units import pixels_to_EMU as p2epath = list(pathlib。Path。cwd()。parents)[1]。joinpath(‘data/automate/004excel’)out_path = path。joinpath(‘004excel_image。xlsx’)img_path = path。joinpath(‘image。jpg’)wb = Workbook()ws = wb。activeimg = Image(img_path)img。width /= 2img。height /= 2h, w = img。height, img。width# 絕對定點陣圖片position = XDRPoint2D(p2e(800), p2e(800))size = XDRPositiveSize2D(p2e(h), p2e(w))img。anchor = AbsoluteAnchor(pos=position, ext=size)ws。add_image(img)# 單元格定位img = Image(img_path)size = (90, 90)img。width, img。height = sizews。row_dimensions[3]。height=90ws。column_dimensions[‘B’]。width=90ws。add_image(img, ‘B3’)wb。save(out_path)
複製程式碼
總結
本文主要介紹了使用
openpyxl
模組處理 Excel 檔案,包括基本的 Excel 檔案讀寫、單元格樣式處理、圖表生成和影象結合等。
用 Excel 應對日常工作中的小規模資料足以。
比如實踐應用中有一個“多門店加盟品牌資料統計”的案例,其中正好用到了上面的各項知識點。
由於篇幅限制,就不一一展示了,有需要文中 python 處理 Excel 檔案筆記完整版的朋友可私信@不禿頭的小鹿回覆“666”即可領取噢~