利用Python輕松讓你的Excel報表自動化

在日常工作中，可能會有一些重復(fù)無聊的任務(wù)，比如說，從 Excel 或數(shù)據(jù)庫中收集一些數(shù)據(jù)，設(shè)置相應(yīng)的數(shù)據(jù)格式并做成報表。

類似這種重復(fù)無聊的任務(wù)，我們完全可以交給 Python 去自動完成，只要第一次把 Python 代碼寫好，以后就可以一鍵完成，省時省力，還不容易出錯，這是提升工作質(zhì)量和工作效率的利器，讓我們能夠騰出時間和精力，去做更有價值的事情。

1. 安裝和導(dǎo)入模塊

以 Python 中的 openpyxl 模塊為例，它能夠讀取和修改 Excel 文件，如果你還沒有安裝，可以通過以下命令進行安裝：

pip install openpyxl

要測試 openpyxl 是否正確安裝，可以在 Jupyter Lab 中運行以下代碼：

# 導(dǎo)入庫
import openpyxl
# 查看版本
openpyxl.__version__

如果該模塊正確安裝，那么會輸出版本號，假如你在使用過程中遇到問題，可以查閱官方文檔。

2. 讀取和處理數(shù)據(jù)

為了演示用 Python 自動生成 Excel 報表，我從網(wǎng)上找了一個數(shù)據(jù)集，是一家跨國公司的 54 萬多行在線零售業(yè)務(wù)的交易數(shù)據(jù)，你可以進入公眾號「林驥」的后臺，回復(fù)「零售」兩個字，獲取該數(shù)據(jù)集的完整下載鏈接。

把這個數(shù)據(jù)文件保存到代碼上級目錄的 data 文件夾，然后用 Pandas 讀取它：

# 讀取數(shù)據(jù)
import pandas as pd
df = pd.read_excel('../data/Online Retail.xlsx')

df

其中每一列代表的含義如下：

InvoiceNo：發(fā)票編號

StockCode：產(chǎn)品代碼

Description：產(chǎn)品名稱

Quantity：產(chǎn)品數(shù)量

InvoiceDate：開票時間

UnitPrice：產(chǎn)品單價

CustomerID：客戶編號

Country：國家名稱

為了統(tǒng)計每天的銷售額，我們先在數(shù)據(jù)中增加兩列：日期和銷售額，然后用函數(shù)實現(xiàn)匯總：

# 從時間列中提取日期
df['日期'] = df.InvoiceDate.dt.to_period('D').astype(str)

# 計算銷售額
df['銷售額'] = df.Quantity * df.UnitPrice

# 匯總每天的銷售額
df_daily = pd.DataFrame(df.groupby('日期')['銷售額'].agg('sum')).reset_index()

df_daily

3. 設(shè)置和保存報表

接下來，我們對表格進行相應(yīng)的設(shè)置，包括：重命名工作表、把數(shù)據(jù)寫入工作表、自定義標題和表格邊框樣式、設(shè)置行高和列寬、不顯示網(wǎng)格線、凍結(jié)窗格、自動篩選、設(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)建自定義的標題樣式
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)用標題樣式
for cell in ws[1]:
    cell.style = mytitle
    
# 對表格區(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'

# 自動篩選
ws.auto_filter.ref = 'A1:' + get_column_letter(ws.max_column) + str(ws.max_row)

# 設(shè)置對齊格式
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]

最后，保存自動生成的 Excel 報表文件：

# 保存為新的表格
wb.save('../data/每日銷售報表.xlsx')

打開這個新生成的 Excel 報表文件，其中的內(nèi)容如下：

小結(jié)

本文介紹了用 Python 自動生成 Excel 報表的一種方法，從 openpyxl 模塊的安裝和導(dǎo)入，到讀取和處理數(shù)據(jù)，再到設(shè)置和保存報表，只要你把數(shù)據(jù)源放在合適的位置，就能在 Jupyter Lab 中一鍵運行，自動生成相應(yīng)的報表。

你完全可以根據(jù)自己的實際情況，修改數(shù)據(jù)源和報表格式等設(shè)置，充分發(fā)揮自己的創(chuàng)意，生成個性化定制的報表。

想要優(yōu)惠買書的同學可以看：優(yōu)惠買書渠道