XLSX Surgery

xlsx 外科手術(xlsx Surgery)

什麼時候用

任何時候你要改一個既有的 .xlsx,而且要求「只動我指定的那幾格/那塊,其餘全部一位元不差」—— 尤其原檔含公式、精細樣式、圖表、樞紐表、外部連結這些「用函式庫重載會被動壞」的東西。 xlsx 是一個 zip 包著一堆 XML part 的格式;天真地「載入→改→存回」會靜默破壞你根本沒碰的部分。 這技能是那些破壞的地雷圖 + 避法。

★觸發紀律(什麼時候「必須」對照本技能)

觸發時機不只是「當你動手寫 xlsx 操作碼時」,而是「任何碼路徑會碰到 xlsx 交付物的任何時刻」—— 這包含接既有模組,不限新寫碼。真實事故:法則 1/3/7 白紙黑字都在,卻因為只把「產出交付檔」 接上一個既有的函式(它內部偷偷 load_workbook+wb.save 整檔重存),圖表照樣被毀——因為 沒人在「接線那一刻」把這條既有路徑對照法則過一遍。

硬紀律(兩條,寫進計畫、交付前必跑):

1. 接線前對照:凡任何碼路徑會對 xlsx 交付檔做寫入 / 轉存 / 搬運 / 另存(含呼叫既有模組), 在接它之前先把它對照法則 1–7 過一遍,把「這條路徑用 ZIP 手術還是整檔重寫」寫進計畫。 既有函式的名字取得再無害(append_source/build/save_as…),也要打開看它內部到底怎麼寫檔。
2. 出口自檢必跑:交付前跑法則 3 的出口自檢——拿真 Excel(或位元/圖表-parts 級對照原檔) 驗「開得了 + 圖在 + 結構等值」。寬鬆工具(openpyxl/LO)的綠不算通過。

核心物理法則

法則 1:最小切口——只改目標,絕不整檔/整頁重寫

主流函式庫(如 openpyxl)「載入整個工作簿再存回」會重新序列化每一個 part——它不認得的東西 (某些圖表、樞紐、外部連結、少見的 namespace)可能被丟失、降級或改寫。外科手術式:

• 只對目標 part(某個 worksheet XML)做字串級精確定位取代,其他 part 原封搬運(ZIP 級 copy)。
• 換一個 sheet 就只換那一個 part 的 bytes,別重打包整個工作簿。
• 絕不 parse 整頁的根標籤(見法則 2 的 namespace 雷)。

法則 2:五個「真 Excel 拒開、寬鬆工具看不到」的檔案級地雷

以下每一個,openpyxl / LibreOffice 重載都容忍(顯示正常=假綠),只有真 Microsoft Excel 開 會踩到(典型症狀:「檔案損壞、要修復」、part XML error 0x808c0002「行1欄0」)。

1. 整樹 parse 毀 namespace:用 XML 樹工具(如 ElementTree)parse 整頁 worksheet 再寫出 → 它會把命名空間前綴改名(如 mc:→ns1:),又把它「以為沒用到」的 namespace 宣告當垃圾刪掉 ——但那個宣告其實被**某個屬性的『值』**引用著(如 mc:Ignorable="x14ac",x14ac 是屬性值裡的 前綴,樹工具看不懂屬性值 → 刪了 → 指向未定義前綴 → 根標籤壞)。避法:根標籤永不 parse,只字串改 目標元素。

2. XML 宣告缺失:每個 XML part 的第一行必須是 <?xml version="1.0" …?>。有些序列化路徑 (如 ElementTree.tostring(encoding="unicode"))不輸出宣告 → Excel 判 part 損壞。避法:寫回 任何 .xml/.rels part 前,收口檢查缺宣告就補上。

3. 公式格缺快取值(<f> 缺 <v>):你寫了公式節點 <f> 卻沒寫值節點 <v> → 真 Excel 在重算前 那格顯示空白/0(它信快取);寬鬆工具會現算所以看不出。避法:寫公式時一併回填 <v> 快取值 (值由獨立來源算,見 verification-discipline),或明確標記整檔 fullCalcOnLoad。

4. 定義名稱斷引用(definedName → #REF!):刪了某些列/欄/來源後,workbook 的 <definedName> 可能指向已不存在的範圍 → 帶著 #REF! → Excel 抱怨。避法:字串級過濾掉失效的 <definedName> (別 parse workbook 根標籤),全空則連 <definedNames> 容器一起拿掉。

5. 公式被壓平成死值:用「只讀值」模式(如 data_only=True)載入再存 → 活公式變成它上次算出的死值, 將來來源一改就不會更新。值當下看起來對,是最陰的假綠。避法:動含公式的檔一律保留公式表達式, 絕不用 data_only 讀出值當內容寫回。

法則 3:寬鬆工具的「綠」是假綠——真 Excel 才是驗收

上面五雷的共同點:openpyxl 重載無誤、LibreOffice 開得起來,都不代表真 Excel 開得起來。 這些工具容忍度高,會把損壞的檔顯示正常。xlsx 產物的最終驗收必須用真 Microsoft Excel 實際開 (或位元級對照一個已知好的原檔);別拿寬鬆工具的成功當通過。(呼應 verification-discipline 「用使用者的真實環境驗、不憑寬鬆代理」。)

法則 4:整格繼承——樣式從源頭隨格一起寫,不留「補樣式」的漏步

要「複製範本某格的完整外觀(框線/底色/數字格式/字型)、只換裡面的值」時,一步就把樣式索引跟值 一起寫進新格(<c r=.. s=範本的樣式索引>值</c>),不要「先寫值、之後再補樣式」——那個「之後再補」 的步驟遲早會漏某些格(尤其空白但有框線/底色的格)。空白格也要繼承(空白有框線的格 <c r=.. s=S/> 照寫,不跳)。一步寫死,沒有會漏的第二步。

法則 5:數字格式逐欄繼承,別套單一格式

一張表不同欄的數字格式常不同(這欄千分位、那欄百分比、另一欄日期)。填值時每一欄各自繼承對應範本欄 的數字格式,別全表套同一個。格式跑掉不影響「值對」但影響「對不對」(見 verification 的多維度比對)。

法則 6:結構性插入用「insert + 順移」,不是「覆蓋」

新增一欄/一列/一個週期(如月報多一個月欄、圖表多一筆資料點)時,是插入 + 把後面的往後推, 不是把某個既有位置覆蓋掉。覆蓋 = 悄悄吃掉原本那裡的東西。欄要插 → 目標欄以後全部 +1 順移; 圖表滑窗 → 新資料點新增並順移視窗,不是蓋掉最舊那筆。

法則 7:活物件(樞紐/圖表)用 verbatim 搬運 + 「載入時刷新」,別用函式庫重建

樞紐表、圖表這類「活」物件,函式庫重建幾乎必然失真或直接搞丟。做法:

• verbatim ZIP-copy:把樞紐/圖表相關的 part 原封不動搬過去(連同 cache),只改它們指向的來源資料。
• 設**「開檔時刷新」**旗標(如樞紐的 refreshOnLoad),讓 Excel 開檔時自己用新資料重算——你不手算它的內容。
• 這也順帶比函式庫快(少了重建大物件的開銷)。

法則 8:大檔讀取物理——串流,永不整頁物化

法則 1–7 講「怎麼寫不弄壞」;這條講「怎麼讀不炸掉」。xlsx 的 XML 本體常比檔案大得多(壓縮率高), 而主流函式庫一般載入的記憶體 ≈ 檔案大小的 50 倍(官方文件明載:50MB 檔 → 2.5GB 記憶體)。 對數百 MB 的工作表,整頁載入/整頁建格物件字典必然爆記憶體,是函式庫的已知特性,不是你的 bug。

• 讀大頁一律串流:read-only 惰性載入 + 逐列迭代(iter_rows),近恆定記憶體;禁整頁物化 (禁整頁建 cell dict、禁「先全部讀進 list 再處理」)。
• 要反覆查值 → 落磁碟點查,不是進記憶體:串流種進嵌入式資料庫(如 SQLite,單檔零依賴),之後 按格點查(微秒級、近零記憶體)。禁把整個字典 pickle 當持久化——載回=整包重建=記憶體尖峰 原地復活,等於白做。
• 峰值記憶體要被斷言押著:大檔處理路徑的 RSS 峰值進回歸 baseline(見 engineering-economy 法則 12),「恆定記憶體」是被破壞測試守著的保證,不是一句宣稱。

本專案案發現場(佐證,非通用必需)

• 核心模組 字串級XML手術模組:某財務報表自動化專案把上述做成一個「最小化字串級」編輯模組。docstring 開宗明義 「只改目標 <c> 的字串,<worksheet>/<workbook> 根標籤、所有 namespace 宣告、其他所有格一律 原封不動,絕不 parse 整頁根標籤」。
• 五雷全是擁有者用真 Excel 驗收時真踩出來的:
  • 法則2-①:ET parse 整頁把 mc→ns1、刪被 mc:Ignorable="x14ac" 屬性值引用的 xmlns:x14ac → <worksheet> 行2欄0 損壞 0x808c0002(namespace損壞偵測器 即為這雷的專用檢查:檢查 Ignorable 引用的前綴有沒有定義、有沒有 ns0/ns1 改寫殘跡)。
  • 法則2-②:ET.tostring(encoding="unicode") 不輸出宣告 → Excel 判 part XML error; 寫入收口的宣告補全函式 在 zip 寫入收口補 <?xml … standalone="yes"?>。
  • 法則2-③:公式快取值回填函式 專門把「有 <f> 缺 <v>」的格回填快取值。
  • 法則2-④:失效名稱過濾函式 字串級移除失效 <definedName>,全移則拿掉整個 <definedNames>。
  • 法則3:同一份損壞檔「LO/openpyxl 容忍=假綠」白紙黑字寫在 寫入收口的宣告補全函式 註解裡——真 Excel 才抓得到。
• 法則4/5:整格繼承寫入函式 = 一步寫格(s= 從四月範本對應格取整個樣式索引,只覆寫值/公式, 空白也整格繼承),鐵則「無『另外補樣式』這個會漏的步驟」;逐格比對器 的 欄錨點配對對齊 + 逐欄比對(含數字格式)。
• 法則6:欄順移函式(插欄後 col≥at_col 全 +count 順移)、區塊就地替換函式(就地換矩形區 維欄序)、換月新增月欄用「新增+順移」;圖表滑窗同理。
• 法則7:樞紐三份用 verbatim ZIP-copy + refreshOnLoad(比 openpyxl 重建快、又不失真), audit 明寫「樞紐 refreshOnLoad 活算」。
• 法則8:某 296MB(未壓)依賴頁——整頁建百萬格值字典連續兩次被 OOM 殺;改「read_only+iter_rows 串流 → 批次寫 SQLite → 交叉參照走點查」後,同一頁 158.5 秒種完、零 OOM、恆定記憶體;峰值 RSS 隨後立為 baseline 斷言 + 「故意整頁物化→會吠」破壞測試。50 倍記憶體那條是事後查官方文件才知道 「全世界都撞過」——先查外部經驗(engineering-economy 法則 1)能省掉前兩次 OOM。