用Python 創(chuàng)建 Excel 高級工作表

River_LaLaLa 2016-08-21

展開全文

英文：Chris Moffitt
譯文：伯樂在線 - icebear
鏈接：http://python./85320/
點擊 → 了解如何加入專欄作者

引言

我已寫過多篇文章介紹如何使用 Python 和 Pandas 操作數(shù)據(jù)得到有用的 Excel 表格。依我的經(jīng)驗，不管 Python 工具有多么強大，有時候仍然需要利用更高階的 Excel 特性來傳遞信息或者進一步分析數(shù)據(jù)。本文會逐步解釋如何通過下列方法改善基于 Excel 的輸出數(shù)據(jù)：

用 XlsxWriter 添加 Excel 數(shù)據(jù)表
在 Excel 文件中插入自定義 VBA
用 COM 合并 Excel 工作簿

Excel 表

在前文中，我討論了如何無縫銜接 Pandas 和 XlsxWriter 來格式化并表達數(shù)據(jù)，比 Pandas 的 to_excel() 更為復(fù)雜。

最近有一個項目，我要給一個簡單的數(shù)據(jù)表添加更多格式，發(fā)現(xiàn)如果用 XlsxWriter 這一切都變得很有用而且非常簡單。我推薦閱讀 XlsxWriter 文檔來了解所有選項的背景和細(xì)節(jié)。

這個例子中，我會使用以前用過的銷售數(shù)據(jù)做樣例。該數(shù)據(jù)將會呈現(xiàn)一段時間內(nèi)針對各式各樣客戶的銷售情況。我們來匯總一下數(shù)據(jù)，看看每個客戶的購買量以及所有客戶的平均購買量是多少：

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)該是這個樣子：

有用但卻普通。

若要將其轉(zhuǎn)換為真正的 Excel 數(shù)據(jù)表，只要用 XlsxWriter 中的 add_table 函數(shù)就好了，非常簡單。我一般會寫一個 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'})

用這個函數(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()

改進后的全新輸出格式應(yīng)該是這個樣子：

用 Excel 數(shù)據(jù)表給數(shù)據(jù)添加總數(shù)和其他統(tǒng)計信息是種相當(dāng)不錯的方法。數(shù)據(jù)表還有便捷工具可以格式化輸出得到較好的展示效果。我鼓勵你通讀 XlsxWriter 文檔學(xué)習(xí)和數(shù)據(jù)表格式化相關(guān)的所有選項。

可以參考 GitHub 上的完整腳本。

給 Excel 添加 VBA

我最近創(chuàng)建了一個交互的 Excel 工作簿，用的就是我常在博客中介紹的工具。我想給最終的文件加一小段 VBA 但是不知道該怎么做。幸運地是 XlsxWriter 可以從已有的文件提取 VBA 保存到獨立的二進制文件然后將其插入到其他文件中。VBA 宏文檔的操作非常清晰明了，這里還是給出一個快速示例。

用 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()

敏銳的讀者會發(fā)現(xiàn)輸出文件的拓展名是 .XLSX，但是 Excel 只會執(zhí)行拓展名為 .XLSM 的文件的 VBA 代碼。

不幸的是，如果你嘗試像這樣將其保存為 XLSM 文件：

writer = pd.ExcelWriter('sales_summary.xlsm', engine='xlsxwriter')

會報錯：

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()

這個方法或許有點折騰，卻是解決這個問題的最簡方法。盡管有小小的不方便，這仍然不失為一種強大的特性，可以用 Python 腳本構(gòu)建健壯的基于 Excel 的解決方案。

用 COM 復(fù)制 Excel 工作簿

可以用 XlsxWriter 從零開始創(chuàng)建一個 Excel 文件，但是并不能從已有的工作簿復(fù)制數(shù)據(jù)然后引入到一個新文件中。這時最佳方案是用 win32com 實現(xiàn)自動化操作。這一方法的缺點是必須在 Windows 操作系統(tǒng)上使用 win32com，但是如果要合并兩個文件，至少有這個選擇。

我用這個方法的一個主要原因是，有一些表有復(fù)雜的格式或結(jié)構(gòu)，用 Excel 修改非常簡單，卻難以用 XlsxWriter 進行編程操作。這時一個方案是創(chuàng)建一個「模板」文件，然后將用 Python 完成的工作簿合并進去。

下面的例子基于 Stack Overflow 上的一個回答。這段代碼的目的是，從一個標(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

這段代碼有一些值得注意的點：

需要安裝 pywin32 – 我推薦使用 anaconda 這個 Python 發(fā)行版
務(wù)必使用 Excel 文件的絕對路徑
保存新文件時，Excel 可能會彈窗問你是否要覆蓋舊文件。你需要在腳本中妥善處理。

我個人覺得用 win32com 必須小心翼翼，所以我盡量少用。但是它仍不失為一個好用的工具，值得放入你的代碼工具庫。

總結(jié)

和其他工具一樣，Excel 若被濫用會帶來一些非常難以維護的工作簿。但是，正因為 Excel 可能會是個麻煩，你必須認(rèn)識到，在你的工作場景下，何時才應(yīng)該使用 Excel。Excel 仍會在商業(yè)軟件生態(tài)系統(tǒng)中占據(jù)支配地位。本文應(yīng)該可以幫助你進一步提高能力，更好地用 Python 和 Pandas 開發(fā)基于 Excel 的解決方案。

更新