英文：Chris Moffitt
譯文： 伯樂(lè)在線 - icebear
鏈接：http://python.jobbole.com/85320/
引言
我已寫過(guò)多篇文章介紹如何使用 Python 和 Pandas 操作數(shù)據(jù)得到有用的 Excel 表格。依我的經(jīng)驗(yàn)，不管 Python 工具有多么強(qiáng)大，有時(shí)候仍然需要利用更高階的 Excel 特性來(lái)傳遞信息或者進(jìn)一步分析數(shù)據(jù)。本文會(huì)逐步解釋如何通過(guò)下列方法改善基于 Excel 的輸出數(shù)據(jù)：
用 XlsxWriter 添加 Excel 數(shù)據(jù)表
在 Excel 文件中插入自定義 VBA
用 COM 合并 Excel 工作簿
Excel 表
在前文中，我討論了如何無(wú)縫銜接 Pandas 和 XlsxWriter 來(lái)格式化并表達(dá)數(shù)據(jù)，比 Pandas 的 to_excel() 更為復(fù)雜。
最近有一個(gè)項(xiàng)目，我要給一個(gè)簡(jiǎn)單的數(shù)據(jù)表添加更多格式，發(fā)現(xiàn)如果用 XlsxWriter 這一切都變得很有用而且非常簡(jiǎn)單。我推薦閱讀 XlsxWriter 文檔來(lái)了解所有選項(xiàng)的背景和細(xì)節(jié)。
這個(gè)例子中，我會(huì)使用以前用過(guò)的銷售數(shù)據(jù)做樣例。該數(shù)據(jù)將會(huì)呈現(xiàn)一段時(shí)間內(nèi)針對(duì)各式各樣客戶的銷售情況。我們來(lái)匯總一下數(shù)據(jù)，看看每個(gè)客戶的購(gòu)買量以及所有客戶的平均購(gòu)買量是多少：
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ù)就好了，非常簡(jiǎn)單。我一般會(huì)寫一個(gè) format_excel 函數(shù)來(lái)保證格式的統(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ù)也非常簡(jiǎn)單：
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 中就有）來(lái)從已有 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 來(lái)重命名文件，另外一種更簡(jiǎn)單的方法是將想要的文件名賦給文件名屬性。
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è)問(wèn)題的最簡(jiǎn)方法。盡管有小小的不方便，這仍然不失為一種強(qiáng)大的特性，可以用 Python 腳本構(gòu)建健壯的基于 Excel 的解決方案。
用 COM 復(fù)制 Excel 工作簿
可以用 XlsxWriter 從零開(kāi)始創(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 修改非常簡(jiǎn)單，卻難以用 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ì)彈窗問(wèn)你是否要覆蓋舊文件。你需要在腳本中妥善處理。
我個(gè)人覺(jué)得用 win32com 必須小心翼翼，所以我盡量少用。但是它仍不失為一個(gè)好用的工具，值得放入你的代碼工具庫(kù)。
總結(jié)
和其他工具一樣，Excel 若被濫用會(huì)帶來(lái)一些非常難以維護(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 開(kāi)發(fā)基于 Excel 的解決方案。
更新
2015年12月7日 – 更新 GitHub 代碼，現(xiàn)在可以動(dòng)態(tài)計(jì)算數(shù)據(jù)表的大小了。
聯(lián)系客服
