用Python自動(dòng)生成Excel報(bào)表
你好，我是林驥。
在日常工作中，可能會(huì)有一些重復(fù)無聊的任務(wù)，比如說，從 Excel 或數(shù)據(jù)庫中收集一些數(shù)據(jù)，設(shè)置相應(yīng)的數(shù)據(jù)格式并做成報(bào)表。
類似這種重復(fù)無聊的任務(wù)，我們完全可以交給 Python 去自動(dòng)完成，只要第一次把 Python 代碼寫好，以后就可以一鍵完成，省時(shí)省力，還不容易出錯(cuò)，這是提升工作質(zhì)量和工作效率的利器，讓我們能夠騰出時(shí)間和精力，去做更有價(jià)值的事情。
1. 安裝和導(dǎo)入模塊
以 Python 中的 openpyxl 模塊為例，它能夠讀取和修改 Excel 文件，如果你還沒有安裝，可以通過以下命令進(jìn)行安裝：
pip?install?openpyxl
要測試 openpyxl 是否正確安裝，可以在 Jupyter Lab 中運(yùn)行以下代碼：
#?導(dǎo)入庫
import?openpyxl
#?查看版本
openpyxl.__version__
如果該模塊正確安裝，那么會(huì)輸出版本號(hào)，假如你在使用過程中遇到問題，可以查閱官方文檔。
2. 讀取和處理數(shù)據(jù)
為了演示用 Python 自動(dòng)生成 Excel 報(bào)表，我從網(wǎng)上找了一個(gè)數(shù)據(jù)集，是一家跨國公司的 54 萬多行在線零售業(yè)務(wù)的交易數(shù)據(jù)，你可以進(jìn)入公眾號(hào)「林驥」的后臺(tái)，回復(fù)「零售」兩個(gè)字，獲取該數(shù)據(jù)集的完整下載鏈接。
把這個(gè)數(shù)據(jù)文件保存到代碼上級(jí)目錄的 data 文件夾，然后用 Pandas 讀取它：
#?讀取數(shù)據(jù)
import?pandas?as?pd
df?=?pd.read_excel('../data/Online?Retail.xlsx')
df
其中每一列代表的含義如下：
InvoiceNo：發(fā)票編號(hào)
StockCode：產(chǎn)品代碼
Description：產(chǎn)品名稱
Quantity：產(chǎn)品數(shù)量
InvoiceDate：開票時(shí)間
UnitPrice：產(chǎn)品單價(jià)
CustomerID：客戶編號(hào)
Country：國家名稱
為了統(tǒng)計(jì)每天的銷售額，我們先在數(shù)據(jù)中增加兩列：日期和銷售額，然后用函數(shù)實(shí)現(xiàn)匯總：
#?從時(shí)間列中提取日期
df['日期']?=?df.InvoiceDate.dt.to_period('D').astype(str)
#?計(jì)算銷售額
df['銷售額']?=?df.Quantity?*?df.UnitPrice
#?匯總每天的銷售額
df_daily?=?pd.DataFrame(df.groupby('日期')['銷售額'].agg('sum')).reset_index()
df_daily
3. 設(shè)置和保存報(bào)表
接下來，我們對(duì)表格進(jìn)行相應(yīng)的設(shè)置，包括：重命名工作表、把數(shù)據(jù)寫入工作表、自定義標(biāo)題和表格邊框樣式、設(shè)置行高和列寬、不顯示網(wǎng)格線、凍結(jié)窗格、自動(dòng)篩選、設(shè)置日期和數(shù)字格式等等。
from?openpyxl.utils.dataframe?import?dataframe_to_rows
from?openpyxl.styles?import?Font,?Color,?NamedStyle,?Border,?Side,?PatternFill,?Alignment,?numbers
#?創(chuàng)建工作簿
wb?=?openpyxl.Workbook()
#?激活工作表
ws?=?wb.active
#?重命名工作表
ws.title?=?'每日銷售額'
#?把數(shù)據(jù)寫入工作表
for?row?in?dataframe_to_rows(df_daily,?index=False,?header=True):
????ws.append(row)
????
#?創(chuàng)建自定義的標(biāo)題樣式
mytitle?=?NamedStyle(name='mytitle')
mytitle.font?=?Font(bold=True,?size=11,?color='FFFFFF')
bd?=?Side(style='thin',?color='A6A6A6')
mytitle.border?=?Border(left=bd,?top=bd,?right=bd,?bottom=bd)
mytitle.fill?=?PatternFill('solid',?fgColor='00589F')
mytitle.alignment?=?Alignment(horizontal='left',?vertical='center')
wb.add_named_style(mytitle)
#?創(chuàng)建自定義表格邊框樣式
myborder?=?NamedStyle(name='myborder')
myborder.font?=?Font(bold=False,?size=11,?color='000000')
bd?=?Side(style='thin',?color='A6A6A6')
myborder.border?=?Border(left=bd,?top=bd,?right=bd,?bottom=bd)
myborder.alignment?=?Alignment(vertical='center')
wb.add_named_style(myborder)
#?應(yīng)用標(biāo)題樣式
for?cell?in?ws[1]:
????cell.style?=?mytitle
????
#?對(duì)表格區(qū)域加邊框
from?openpyxl.utils?import?get_column_letter,?column_index_from_string
table_range?=?ws['A2:'?+?get_column_letter(ws.max_column)?+?str(ws.max_row)]
for?row?in?table_range:
????for?cell?in?row:
????????cell.style?=?myborder
????
#?設(shè)置行高和列寬
ws.row_dimensions[1].height?=?26
ws.column_dimensions['A'].width?=?15
ws.column_dimensions['B'].width?=?12
#?設(shè)置不顯示網(wǎng)格線
ws.views.sheetView[0].showGridLines?=?False
#?凍結(jié)窗格
ws.freeze_panes?=?'A3'
#?自動(dòng)篩選
ws.auto_filter.ref?=?'A1:'?+?get_column_letter(ws.max_column)?+?str(ws.max_row)
#?設(shè)置對(duì)齊格式
for?cell?in?ws['A']:
????cell.alignment?=?Alignment(horizontal='center',?vertical='center')
#?設(shè)置日期格式
for?cell?in?ws['A']:
????cell.number_format?=?numbers.FORMAT_DATE_YYYYMMDD2
????
#?設(shè)置數(shù)字格式
for?cell?in?ws['B']:
????cell.number_format?=?numbers.BUILTIN_FORMATS[3]
最后，保存自動(dòng)生成的 Excel 報(bào)表文件：
#?保存為新的表格
wb.save('../data/每日銷售報(bào)表.xlsx')
打開這個(gè)新生成的 Excel 報(bào)表文件，其中的內(nèi)容如下：
小結(jié)
本文介紹了用 Python 自動(dòng)生成 Excel 報(bào)表的一種方法，從 openpyxl 模塊的安裝和導(dǎo)入，到讀取和處理數(shù)據(jù)，再到設(shè)置和保存報(bào)表，只要你把數(shù)據(jù)源放在合適的位置，就能在 Jupyter Lab 中一鍵運(yùn)行，自動(dòng)生成相應(yīng)的報(bào)表。
你完全可以根據(jù)自己的實(shí)際情況，修改數(shù)據(jù)源和報(bào)表格式等設(shè)置，充分發(fā)揮自己的創(chuàng)意，生成個(gè)性化定制的報(bào)表。
長按下方的二維碼，關(guān)注林驥的公眾號(hào)，更多干貨早知道。
歡迎加入我的免費(fèi)知識(shí)星球，我每天都會(huì)在星球內(nèi)分享讀書筆記和思考感悟，點(diǎn)擊左下角的閱讀原文即可加入。