|
英文：Chris Moffitt 譯文： 伯樂在線 - icebear 鏈接：http://python./85320/ 點(diǎn)擊 → 了解如何加入專欄作者
引言
我已寫過多篇文章介紹如何使用 Python 和 Pandas 操作數(shù)據(jù)得到有用的 Excel 表格。依我的經(jīng)驗(yàn)，不管 Python 工具有多么強(qiáng)大，有時(shí)候仍然需要利用更高階的 Excel 特性來傳遞信息或者進(jìn)一步分析數(shù)據(jù)。本文會(huì)逐步解釋如何通過下列方法改善基于 Excel 的輸出數(shù)據(jù)：
Excel 表
在前文中，我討論了如何無縫銜接 Pandas 和 XlsxWriter 來格式化并表達(dá)數(shù)據(jù)，比 Pandas 的 to_excel() 更為復(fù)雜。
最近有一個(gè)項(xiàng)目，我要給一個(gè)簡單的數(shù)據(jù)表添加更多格式，發(fā)現(xiàn)如果用 XlsxWriter 這一切都變得很有用而且非常簡單。我推薦閱讀 XlsxWriter 文檔來了解所有選項(xiàng)的背景和細(xì)節(jié)。
這個(gè)例子中，我會(huì)使用以前用過的銷售數(shù)據(jù)做樣例。該數(shù)據(jù)將會(huì)呈現(xiàn)一段時(shí)間內(nèi)針對(duì)各式各樣客戶的銷售情況。我們來匯總一下數(shù)據(jù)，看看每個(gè)客戶的購買量以及所有客戶的平均購買量是多少：
import pandas as pd sales_df = pd.read_excel('https://github.com/chris1610/pbpython/blob/master/data/sample-salesv3.xlsx?raw=true') sales_summary = sales_df.groupby(['name'])['ext price'].agg(['sum', 'mean']) # Reset the index for consistency when saving in Excel sales_summary.reset_index(inplace=True) writer = pd.ExcelWriter('sales_summary.xlsx', engine='xlsxwriter') sales_summary.to_excel(writer, 'summary', index=False) writer.save()
標(biāo)準(zhǔn)的 Excel 輸出應(yīng)該是這個(gè)樣子：
有用但卻普通。
若要將其轉(zhuǎn)換為真正的 Excel 數(shù)據(jù)表，只要用 XlsxWriter 中的 add_table 函數(shù)就好了，非常簡單。我一般會(huì)寫一個(gè) format_excel 函數(shù)來保證格式的統(tǒng)一。這種格式化函數(shù)一般是這樣子的：
def format_excel(writer): ''' Add Excel specific formatting to the workbook ''' # Get the workbook and the summary sheet so we can add the formatting workbook = writer.book worksheet = writer.sheets['summary'] # Add currency formatting and apply it money_fmt = workbook.add_format({'num_format': 42, 'align': 'center'}) worksheet.set_column('A:A', 20) worksheet.set_column('B:C', 15, money_fmt) worksheet.add_table('A1:C22', {'columns': [{'header': 'account', 'total_string': 'Total'}, {'header': 'Total Sales', 'total_function': 'sum'}, {'header': 'Average Sales', 'total_function': 'average'}], 'autofilter': False, 'total_row': True, 'style': 'Table Style Medium 20'})
用這個(gè)函數(shù)也非常簡單：
sales_df = pd.read_excel('https://github.com/chris1610/pbpython/blob/master/data/sample-salesv3.xlsx?raw=true') sales_summary = sales_df.groupby(['name'])['ext price'].agg(['sum', 'mean']) # Reset the index for consistency when saving in Excel sales_summary.reset_index(inplace=True) writer = pd.ExcelWriter('sales_summary.xlsx', engine='xlsxwriter') sales_summary.to_excel(writer, 'summary', index=False) format_excel(writer) writer.save()
改進(jìn)后的全新輸出格式應(yīng)該是這個(gè)樣子：
用 Excel 數(shù)據(jù)表給數(shù)據(jù)添加總數(shù)和其他統(tǒng)計(jì)信息是種相當(dāng)不錯(cuò)的方法。數(shù)據(jù)表還有便捷工具可以格式化輸出得到較好的展示效果。我鼓勵(lì)你通讀 XlsxWriter 文檔學(xué)習(xí)和數(shù)據(jù)表格式化相關(guān)的所有選項(xiàng)。
可以參考 GitHub 上的完整腳本。
給 Excel 添加 VBA
我最近創(chuàng)建了一個(gè)交互的 Excel 工作簿，用的就是我常在博客中介紹的工具。我想給最終的文件加一小段 VBA 但是不知道該怎么做。幸運(yùn)地是 XlsxWriter 可以從已有的文件提取 VBA 保存到獨(dú)立的二進(jìn)制文件然后將其插入到其他文件中。VBA 宏文檔的操作非常清晰明了，這里還是給出一個(gè)快速示例。
用 vba_extract.py （XlsxWriter 中就有）來從已有 Excel 文件提取 VBA：
vba_extract.py source_file.xlsm Extracted vbaProject.bin
利用類似的代碼處理上面的例子，這里演示如何將 VBA 插入 Excel 的輸出文件中：
import pandas as pd sales_df = pd.read_excel('https://github.com/chris1610/pbpython/blob/master/data/sample-salesv3.xlsx?raw=true') sales_summary = sales_df.groupby(['name'])['ext price'].agg(['sum', 'mean']) # Reset the index for consistency when saving in Excel sales_summary.reset_index(inplace=True) writer = pd.ExcelWriter('sales_summary.xlsx', engine='xlsxwriter') sales_summary.to_excel(writer, 'summary', index=False) workbook = writer.book workbook.add_vba_project('vbaProject.bin') writer.save()
敏銳的讀者會(huì)發(fā)現(xiàn)輸出文件的拓展名是 .XLSX，但是 Excel 只會(huì)執(zhí)行拓展名為 .XLSM 的文件的 VBA 代碼。
不幸的是，如果你嘗試像這樣將其保存為 XLSM 文件：
writer = pd.ExcelWriter('sales_summary.xlsm', engine='xlsxwriter')
會(huì)報(bào)錯(cuò)：
ValueError: Invalid extension for engine 'xlsxwriter': 'xlsm'
第一種解決方法是用 os.rename 來重命名文件，另外一種更簡單的方法是將想要的文件名賦給文件名屬性。
writer = pd.ExcelWriter('sales_summary.xlsx', engine='xlsxwriter') sales_summary.to_excel(writer, 'summary', index=False) workbook = writer.book workbook.filename = 'sales_summary.xlsm' workbook.add_vba_project('vbaProject.bin') writer.save()
這個(gè)方法或許有點(diǎn)折騰，卻是解決這個(gè)問題的最簡方法。盡管有小小的不方便，這仍然不失為一種強(qiáng)大的特性，可以用 Python 腳本構(gòu)建健壯的基于 Excel 的解決方案。
用 COM 復(fù)制 Excel 工作簿
可以用 XlsxWriter 從零開始創(chuàng)建一個(gè) Excel 文件，但是并不能從已有的工作簿復(fù)制數(shù)據(jù)然后引入到一個(gè)新文件中。這時(shí)最佳方案是用 win32com 實(shí)現(xiàn)自動(dòng)化操作。這一方法的缺點(diǎn)是必須在 Windows 操作系統(tǒng)上使用 win32com，但是如果要合并兩個(gè)文件，至少有這個(gè)選擇。
我用這個(gè)方法的一個(gè)主要原因是，有一些表有復(fù)雜的格式或結(jié)構(gòu)，用 Excel 修改非常簡單，卻難以用 XlsxWriter 進(jìn)行編程操作。這時(shí)一個(gè)方案是創(chuàng)建一個(gè)「模板」文件，然后將用 Python 完成的工作簿合并進(jìn)去。
下面的例子基于 Stack Overflow 上的一個(gè)回答。這段代碼的目的是，從一個(gè)標(biāo)準(zhǔn)的「指導(dǎo)」表復(fù)制內(nèi)容到我們用 Pandas 創(chuàng)建的 sales_summary 文件中。
from win32com.client import DispatchEx excel = DispatchEx('Excel.Application') excel.Visible = False workbook_1 = excel.Workbooks.Open(r'C:fullpathtosales_summary.xlsx') workbook_2 = excel.Workbooks.Open(r'C:fullpathtosales_template.xlsx') workbook_2.Worksheets('Instructions').Move(Before=workbook_1.Worksheets('summary')) workbook_1.SaveAs(r'C:fullpathtosales_summary_complete.xlsx') excel.Application.Quit() del excel
這段代碼有一些值得注意的點(diǎn)：
需要安裝 pywin32 – 我推薦使用 anaconda 這個(gè) Python 發(fā)行版 務(wù)必使用 Excel 文件的絕對(duì)路徑 保存新文件時(shí)，Excel 可能會(huì)彈窗問你是否要覆蓋舊文件。你需要在腳本中妥善處理。
我個(gè)人覺得用 win32com 必須小心翼翼，所以我盡量少用。但是它仍不失為一個(gè)好用的工具，值得放入你的代碼工具庫。
總結(jié)
和其他工具一樣，Excel 若被濫用會(huì)帶來一些非常難以維護(hù)的工作簿。但是，正因?yàn)?Excel 可能會(huì)是個(gè)麻煩，你必須認(rèn)識(shí)到，在你的工作場(chǎng)景下，何時(shí)才應(yīng)該使用 Excel。Excel 仍會(huì)在商業(yè)軟件生態(tài)系統(tǒng)中占據(jù)支配地位。本文應(yīng)該可以幫助你進(jìn)一步提高能力，更好地用 Python 和 Pandas 開發(fā)基于 Excel 的解決方案。
更新
|
