AI 工作實務
openpyxl 自動產生 Excel 報表:公式快取、大檔與驗算方法
用 Python openpyxl 自動產生 Excel 報表:為什麼公式讀回是 None、如何用 LibreOffice 重算、read_only 與 write_only 大檔模式,搭配營收、佣金、比價等 8 個報表情境。

本文目錄
openpyxl 適合做什麼?和其他工具怎麼選4 個技術重點:避免漂亮但錯誤的報表安裝與版本確認第一個可執行範例:產生報表並獨立驗算8 個實務情境:從營收月報到敏感資料常見錯誤、原因與修正上線前驗收清單常見問題官方資料來源延伸閱讀與企業實作用 Python openpyxl 自動產生 Excel 報表:為什麼公式讀回是 None、如何用 LibreOffice 重算、read_only 與 write_only 大檔模式,搭配營收、佣金、比價等 8 個報表情境。
每月結帳時,財務要把各門市的日結檔合併成一張營收表,業務要算佣金,採購要做比價表。openpyxl 是 Python 讀寫 Excel(.xlsx)最常用的套件,能把這些重複整理的工作寫成程式,每月一鍵產生。
但 openpyxl 有一個常被誤解的特性:它會寫入公式,卻不會計算公式。本文以 openpyxl 3.1 實測,說明公式快取、大檔模式、資料驗證與報表驗收的做法。
重點回答:openpyxl 是讀寫 Excel 的 Python 套件,可自動產生含格式與公式的報表,但不會計算公式。
openpyxl 適合做什麼?和其他工具怎麼選
| 需求 | 建議工具 | 原因 |
|---|---|---|
| 產生有格式、有公式的 xlsx | openpyxl | 可控制儲存格、樣式、凍結窗格 |
| 大量資料清理與彙總 | pandas(輸出時用 openpyxl) | 資料運算較方便 |
| 計算公式並讀回結果 | Excel 或 LibreOffice 重算 | openpyxl 不計算公式 |
| 舊版 .xls 檔 | 先轉成 xlsx | openpyxl 不支援 .xls |
| 上百萬列的分析資料 | 資料庫、CSV 或 Parquet | xlsx 單表上限 1,048,576 列 |
如果報表的價值在「主管打開就能看、還能自己調整公式」,openpyxl 很合適。如果只是要算數字,先在 Python 或資料庫裡算好,再寫進 Excel 會更可靠。
4 個技術重點:避免漂亮但錯誤的報表
先定義一列代表什麼
一列是一張訂單、一筆出貨,還是一張請款單?粒度沒定清楚就合併資料,總額可能重複計算。報表程式的第一步應該是定義主鍵與欄位型別,而不是設定字型與顏色。
寫入公式不等於算出結果
ws['E2'] = '=C2*D2' 只是把公式文字存進檔案。用 load_workbook(path, data_only=True) 讀取時,拿到的是 Excel 上次存檔時留下的快取值;檔案若從未被試算表軟體開啟存檔,讀到的會是 None。
我們實測:openpyxl 產生的檔案讀回公式欄位得到 None,以 LibreOffice headless 轉存一次後,同一格讀回正確的計算結果。所以驗證總額時,要用 Python 獨立計算一次,不要依賴公式快取。
大檔用 read_only 與 write_only 模式
load_workbook(path, read_only=True) 以串流方式逐列讀取,記憶體用量較低。Workbook(write_only=True) 適合逐列 append 大量資料,但寫過的列無法再回頭修改。
被排除的資料要留下紀錄
格式錯誤、缺值、重複與負數,不能在程式裡默默跳過。另外輸出一份 rejects.csv,寫明原因與來源列號,讓負責人可以複查。
安裝與版本確認
用 pip install openpyxl 安裝,建議在專案的虛擬環境中固定版本,例如在 requirements.txt 寫明 openpyxl==3.1.5。openpyxl 只讀寫 xlsx、xlsm 等 Office Open XML 格式,不需要安裝 Excel。
需要自動重算公式時,執行環境要另外安裝 LibreOffice,並記錄它的版本。
第一個可執行範例:產生報表並獨立驗算
程式以 openpyxl 3.1.5 搭配三筆範例訂單執行過,驗算結果與手算一致。它讀入訂單 CSV,寫入含公式的 Excel,並凍結標題列、加上篩選。
import csv
from openpyxl import Workbook
from openpyxl.styles import Font, PatternFill
wb = Workbook()
ws = wb.active
ws.title = "Sales"
ws.append(["訂單編號", "品項", "數量", "單價", "總價"])
with open("orders.csv", encoding="utf-8") as f:
for i, row in enumerate(csv.DictReader(f), start=2):
qty, price = int(row["數量"]), int(row["單價"])
assert qty > 0 and price >= 0, f"第 {i} 列數值異常"
ws.append([row["訂單編號"], row["品項"], qty, price, f"=C{i}*D{i}"])
ws.freeze_panes = "A2"
ws.auto_filter.ref = ws.dimensions
for c in ws[1]:
c.font = Font(bold=True, color="FFFFFF")
c.fill = PatternFill("solid", fgColor="15372E")
wb.save("report.xlsx")驗證時重新開檔,檢查公式文字與資料筆數,總額用 Python 自己算:
from openpyxl import load_workbook
ws = load_workbook("report.xlsx")["Sales"]
assert ws["E2"].value == "=C2*D2"
total = sum(ws[f"C{r}"].value * ws[f"D{r}"].value for r in range(2, ws.max_row + 1))
print("獨立驗算總額", total)需要讓檔案帶有公式計算結果時,可以用 soffice --headless --convert-to xlsx --outdir recalc report.xlsx 重算轉存。

8 個實務情境:從營收月報到敏感資料
這些情境是依財務、業務與行政常見報表設計的示範,不是客戶實際資料或成效。
情境 1:門市月營收彙整
各門市日結檔的日期格式不一,有的是 2026/10/01,有的是 10-01-2026。先統一轉成日期型別,再依訂單編號與交易期間去重。
寫進 Excel 前,先用 Python 算出總額並和來源比對。重複請款會讓營收虛增,而在漂亮的報表裡很難一眼看出。
情境 2:業務佣金試算
佣金率和退貨扣回規則每季可能調整。把費率放在一張有生效期間的規則表,不覆蓋歷史費率。
每筆佣金都要追得到使用了哪一期的費率。改了費率表後重算舊月份,結果不應該跟著改變。
情境 3:採購比價表
各廠商報價的幣別、含稅與否和交期都不同。比價表要分開保存原幣金額、幣別、稅別與換算匯率的日期。
把不同幣別直接加總,是最常見也最難發現的錯誤。比較基準應先經採購主管確認。
情境 4:課程報名名單
學員資料常有重複 Email 和缺漏欄位。清理規則與原始資料分開,原檔不直接修改。
輸出可匯入名單與 rejects.csv 兩份檔案。姓名不適合當唯一識別,同名同姓的學員會被誤合併。
情境 5:主管可調整的管理報表
主管希望打開報表能自己改假設、看結果。這時就保留公式,另外放一張「資料來源」工作表說明每個欄位的出處。
交付前用 Excel 或 LibreOffice 重算一次,確認公式結果和 Python 獨立計算一致。
情境 6:大型資料匯出
50 萬列資料一次載入記憶體,排程很容易中斷。改用 Workbook(write_only=True) 從資料庫游標逐列寫入,並記錄輸出列數。
需要反覆查詢的資料,不一定要做成 Excel,CSV 或資料庫可能更適合。
情境 7:跨部門主檔格式一致
各部門的欄位名稱和順序都不同,合併時常對錯欄。先定一份欄位規格(名稱、型別、是否必填),再設定欄寬、凍結窗格與資料驗證清單。
openpyxl 的 DataValidation 可以在儲存格加上下拉選單,減少人工輸入錯字。
情境 8:薪酬與客戶資料
含薪酬或客戶個資的報表,只在授權目錄產生,寄出前重新開檔檢查。隱藏的工作表、被隱藏的欄與刪不乾淨的明細,都可能跟著附件一起寄出。
Excel 的密碼保護主要防止誤改,不等於完整的資訊安全控管。
常見錯誤、原因與修正
| 現象 | 常見原因 | 修正方式 |
|---|---|---|
| 讀公式欄位得到 None | 檔案沒有計算過的快取值 | 用試算表軟體重算,或在 Python 獨立計算 |
| 數字被當成文字 | CSV 讀入未轉型 | 寫入前轉成 int、float 或 Decimal |
| 日期變成一串數字 | 儲存格格式未設定 | 寫入 datetime 並設定 number_format |
| 記憶體不足中斷 | 整份資料一次載入 | 改用 read_only 或 write_only 模式 |
| 開 .xls 檔報錯 | openpyxl 不支援舊格式 | 先轉成 xlsx |
上線前驗收清單
- 每張報表都有欄位定義:一列代表什麼、主鍵是哪一欄。
- 總額與關鍵數字由 Python 獨立計算,並和來源資料比對。
- 被排除的資料有 rejects.csv 與排除原因。
- 有公式的報表已用試算表軟體重算並抽查。
- 寄出前檢查隱藏工作表與敏感欄位。
常見問題
openpyxl 可以執行 Excel 巨集嗎?
不行。用 keep_vba=True 開啟 xlsm 可以保留巨集內容,但 openpyxl 不會執行 VBA。
為什麼存了公式,讀出來卻是 None?
openpyxl 不計算公式,data_only=True 只讀取試算表軟體上次存檔的快取值。需要結果時,要先用 Excel 或 LibreOffice 重算。
openpyxl 和 pandas 該選哪個?
資料清理與計算用 pandas 較方便;需要控制格式、公式與工作表結構時用 openpyxl。兩者常一起使用,pandas 寫 xlsx 時預設就會呼叫 openpyxl。
可以處理百萬列資料嗎?
xlsx 單一工作表上限是 1,048,576 列。資料量接近上限時,應改用資料庫或 CSV,Excel 只放彙總結果。
官方資料來源
圖片為原創示意圖。
延伸閱讀與企業實作
- PptxGenJS 自動產生 PowerPoint:可編輯簡報產線怎麼做?
- AI 合併 Excel 後,營收為何變多?用 6 筆訂單找出重複、漏配與錯誤加總
- 報表的空白不是 0:缺漏資料如何讓平均值與績效判斷失真?
- 報表收了一堆,為什麼還是無法決策?用 AI 建立管理週報的 6 個步驟
如果財務或業務每月都在重複整理同一張 Excel,可以帶一份現行報表與原始資料,和天地人一起確認欄位定義、計算口徑與驗算方式,再把產出流程寫成可交接的程式;報表結果也能接著用 PptxGenJS 轉成可編輯的管理簡報。偏向提升同仁 Excel 與資料整理能力時,可安排數位工具內訓。
本文由 TARAISG(TIANDIREN AI SOLUTION GROUP)使用 AI 模型產生正文、整理來源並設計實務工具。方法依據與限制列於文末;案例為明確標示的教學假設。未有具名紀錄時,不宣稱經人工或專家審核。
查看內容製作與更正說明