一份靠手工維護的財務活頁簿,累積了樞紐分析快取、交叉分析篩選器、條件式格式與好幾年的儲存格樣式,而夜間批次工作要做的只是換掉某張資料頁上的數字。整份載入再儲存,會把每個部件重新剖析、重建、壓縮一遍。只重寫那張工作表部件、其他 zip 項目原樣帶過,幾乎省掉了全部這些工
讓這件事成立的是套件格式本身。一份 .xlsx 檔案是照 Open Packaging Conventions(ECMA-376 Part 2)鋪排的 XML 部件 zip 壓縮檔,每個 zip 項目都在本地表頭記錄自己的壓縮方式、CRC-32 與壓縮後大小,中央目錄裡再記一次。因此一個項目可以從一份壓縮檔搬進另一份,全程不必解壓——前提是複製方把這三個值連同原始位元組一起寫過去。樞紐分析快取不需要被理解才能保留,它只需要被照抄
為什麼複製壓縮位元組,而不是重新儲存活頁簿?
在壓縮層級複製,讓修補的大半工作變成單純的串流複製。HotXLS 的 TXLSDirectWriter.BeginPatch 以唯讀方式開啟來源套件,把修補不去動的每個項目交給 TZipArchive.CopyCompressedFrom,由它把來源項目的壓縮位元組、CRC 與大小直接寫進目標壓縮檔。傳統寫入路徑進來時付一次解壓、出去時付一次壓縮,而壓縮是昂貴的那一半。在一份主體是大型樞紐分析快取加幾十張內嵌影像的活頁簿上,時間會花在你真正要寫的那張工作表上
原樣複製涵蓋未壓縮儲存的項目(zip method 0)與 deflate 項目(method 8)。用其他壓縮方式的項目照樣帶得過去:寫入器經由解壓串流讀進來、再寫出去,部件無論如何都會落到目標檔裡。目錄標記項目不含內容,直接跳過,因為寫入器會產生自己的。少數幾個項目則從不複製,因為它們會被重新產生或改寫:目標工作表與其 _rels 部件、xl/calcChain.xml、xl/_rels/workbook.xml.rels 與 [Content_Types].xml,插入工作表時再加上 xl/workbook.xml
用 BeginPatch 原地取代單一工作表
兩引數的多載 BeginPatch(FileName, SheetName) 取代既有工作表,並把結果寫回同一路徑。它透過 xl/workbook.xml 裡的工作表登記表、以及把每張工作表綁到其部件的關聯對照來解析名稱,名稱比對不分大小寫,什麼都沒對上時拋出內含「worksheet not found」的例外。接下來就用串流寫入器寫新檔的那套 AddSheet、AddRow 與 Write* 呼叫來編寫這張工作表
var
W: TXLSDirectWriter;
begin
W := TXLSDirectWriter.Create;
try
W.BeginPatch('monthly-dashboard.xlsx', 'Data'); // replace in place
W.AddSheet('Data'); // name is informational in patch mode
W.AddRow(1);
W.WriteString(1, 'Region');
W.WriteString(2, 'Revenue');
W.AddRow(2);
W.WriteString(1, 'North');
W.WriteNumber(2, 184320.55);
W.AddRow(3);
W.WriteString(1, 'South');
W.WriteNumber(2, 97410.20);
W.AddRow(4);
W.WriteString(1, 'Total');
W.WriteFormula(2, 'SUM(B2:B3)'); // no leading '='
W.Close;
finally
W.Free;
end;
end;
這段清單裡有兩個細節容易誤讀。傳給 AddSheet 的名稱不會改名任何東西:那個頁籤保留它在 xl/workbook.xml 裡原有的名稱、sheetId 與位置,所以寫 AddSheet('Totals') 仍然寫進 Data。第二次呼叫 AddSheet 會拋例外,因為一次修補恰好寫一張工作表。要重新整理三個頁籤,就是三次修補,每次都再把套件複製一遍
公式放進去時不帶等號。WriteFormula 把文字直接放進儲存格的 <f> 元素,而 ECMA-376 Part 1 §18.3.1.40 存在這裡的公式本來就沒有 Excel 資料編輯列顯示的那個「=」;寫 '=SUM(B2:B3)' 會把一個字面等號放進 XML。WriteFormula 也不輸出快取結果。如果下游有東西讀快取值而不重算,WriteFormulaNumber 可以連同你已知的求值結果一起寫
原地多載在運作過程中絕不寫進原始檔。修補後的套件先建在來源旁邊、以來源檔名加上 .hxlpatch.tmp 的兄弟檔案裡。Close 時釋放來源控制代碼,暫存檔以單次改名取代原始檔。若改名失敗,Close 拋出的例外訊息會點名兩個路徑、保留暫存副本、讓原始檔留在原處。在 Close 之前被殺掉的行程會留下完好的原始檔和一個遊離的 .hxlpatch.tmp,下次對該檔案呼叫 BeginPatch 時會先把它刪掉再開工
在範本副本上附加一張新工作表
四引數的多載 BeginPatch(SourceFileName, TargetFileName, SheetName, InsertSheet) 不碰來源檔,另寫一份新套件。InsertSheet = False 時取代副本裡指定名稱的工作表;True 時以該名稱附加一張新工作表,範本裡已有同名頁籤則拋出「worksheet already exists」
// template.xlsx stays untouched; output.xlsx gets one extra tab
W.BeginPatch('template.xlsx', 'output.xlsx', 'Extra', True);
W.AddSheet('Extra');
W.AddRow(1);
W.WriteString(1, 'Appended by the nightly job');
W.Close;
插入是套件簿記真正被動手腳的地方。寫入器掃描既有的 xl/worksheets/sheetN.xml 項目取最大部件編號、掃描 <sheet> 元素取最大 sheetId、掃描活頁簿關聯取最大 rId,各取下一個值。新的 <sheet> 元素插在 </sheets> 正前方,所以新頁籤排在最後。關聯屬性沿用來源活頁簿綁工作表用的前綴,通常是 r:id,但規範並不要求
關聯類型同樣跟著來源套件走。活頁簿關聯若已採用 purl.oclc.org 底下的 ISO/IEC 29500 Strict 命名空間,新工作表關聯就用 Strict 類型 URI 寫出;Transitional 活頁簿拿到 Transitional 那種。修補過的 Strict 活頁簿因此不會混進一條 Transitional 關聯
樣式、字串,以及修補不去動的部件
修補模式下,新工作表指向的一切都必須已存在於來源套件。字串因此以行內方式寫進工作表,而不是寫進 xl/sharedStrings.xml,因為共用字串表逐位元組帶過、無法增長。把 UseSharedStrings 設為 True 會讓 AddSheet 在修補模式下拋例外
同一條規則也改變了 StyleIndex 的含義。修補時它指的是來源活頁簿 cellXfs 清單(ECMA-376 Part 1 §18.8.10)裡的位置,寫入器只檢查它落在 0 到 65534 之間。它無從判斷索引 14 在這份特定檔案裡存不存在,寫錯的數字會直通輸出。找對索引最可靠的辦法,是把目前版本的工作表解壓出來,讀一個已帶著你要格式的儲存格上的 s 屬性;那個數字就是 cellXfs 位置。只要有人在範本裡調過儲存格格式,Excel 就可能往那個清單加條目,所以每次範本編輯後都要重新查一遍索引,別信一年前寫死的常數
const
// cellXfs position of a date format in THIS template, taken from the
// s="..." attribute of an existing date cell; re-check after template edits
TemplateDateXf = 5;
begin
...
W.AddRow(5);
W.WriteString(1, 'Refreshed');
W.WriteDateTime(2, Now, TemplateDateXf); // explicit index: accepted
// W.WriteDateTime(2, Now); raises in patch mode: its implicit date style
// lives in a styles part the patch never writes
end;
任何會新增套件部件的操作一律拒絕。AddTable、AddChart、AddImage 家族、AddComment、AddDefinedName 與 AddCellStyle 一被呼叫就拋例外,Close 送出前會再查一次,這也攔得住自帶填滿、字型色彩、粗體或斜體樣式的條件式格式,因為那些需要在 styles.xml 裡新增條目。被拒絕的呼叫,好過一份工作表引用著沒人註冊過的表格部件的套件
取代模式也讓工作表從零開始。舊工作表的關聯部件不會被複製,掛在舊版上的繪圖、註解或外部超連結從此不再掛在新版上;存在舊工作表 XML 裡的欄寬、凍結窗格、合併範圍與資料驗證也一併消失。住在工作表 XML 裡的那些,可以用 SetColumnWidth、FreezePanes、MergeCells、AddDataValidation 與 AddHyperlink 重寫回來。錨在資料頁上的圖表或圖片無法靠修補回來,所以把圖表放在獨立的工作表上,讓它的數列指向資料頁
兩種模式下計算鏈都會被丟掉。xl/calcChain.xml 按 Excel 上次求值的順序列出公式儲存格;取代之後,它的條目描述的是已不復存在的儲存格形態,插入之後,它引用的工作表索引整體位移。這個部件不進複製結果,其關聯與它在 [Content_Types].xml 裡的 Override 也一併移除,套件才不會引用一個失踪的部件。Excel 下次重算時會自己把鏈建回來
資料列迴圈跑到一半丟出例外時
TXLSDirectWriter 的解構子會在 Close 從未被呼叫時收尾套件,所以忘記呼叫 Close 不會留下一個寫了一半的 zip。正因為它會把寫入器手上有的東西收尾完,原地多載之下穿過 Free 展開的例外,仍然會送出目前為止寫好的資料列、把它們換過原始檔。一個抓列、寫列、中途拋例外、最後在 finally 裡釋放寫入器的迴圈,結局恰恰就是這樣
對任何無人值守的工作,請寫到明確指定的目標、開啟結果、檢查,然後才親自替換線上檔案。資料列迴圈丟出的例外仍會讓 Free 收尾暫存檔,但同時跳過替換,線上活頁簿於是保留昨天的數字,而不是今天的半份
uses
Windows, SysUtils, Variants, lxDirectWrite, lxHandleX;
procedure RefreshDashboard;
const
Live = 'monthly-dashboard.xlsx';
Staged = 'monthly-dashboard.next.xlsx';
var
W: TXLSDirectWriter;
Check: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
begin
W := TXLSDirectWriter.Create;
try
W.BeginPatch(Live, Staged, 'Data', False);
W.AddSheet('Data');
WriteRefreshedRows(W); // 你自己的資料列迴圈,不是 HotXLS 的一部分
W.Close;
finally
W.Free;
end;
Check := TXLSXWorkbook.Create;
try
if Check.Open(Staged) <> 1 then
raise Exception.Create('Staged workbook does not open');
Sheet := Check.Sheets.SheetByName('Data');
if (Sheet = nil) or (VarToStr(Sheet.Cells[2, 1].Value) <> 'North') then
raise Exception.Create('Data sheet did not come back as written');
finally
Check.Free;
end;
// Windows API,不是 HotXLS:檢查全過之後才替換線上檔案
if not MoveFileEx(PChar(Staged), PChar(Live), MOVEFILE_REPLACE_EXISTING) then
RaiseLastOSError;
end;
TXLSXWorkbook.Open 成功時回傳 1,Sheets 與 Cells 都是 1 起算:Check.Sheets[1] 是第一個頁籤,Cells[2, 1] 是 A2。除了抽查數值之外,兩項結構檢查能抓走大部分錯誤。Check.Sheets.Count 在取代後應等於範本的數量、插入後應多一;用任何 zip 工具列出壓縮檔,不應看到 xl/calcChain.xml,也不應出現重複的項目名稱。重複名稱要緊,是因為某些讀取端取同名項目的最後一個,兩張工作表看起來就會像內容被對調了
修補、串流,還是載入活頁簿?
活頁簿很大、變更只限一張工作表、其餘部分必須逐位元組倖存時,修補是對的工具。變更橫跨多張工作表、新資料需要範本尚未定義的格式、必須建立圖表或表格,或檔案小到正常載入儲存的成本不值得量測時,它就不是對的工具。從零開始的大量產生,串流直接寫入器 更合適,而且它與修補共用同一套 AddRow 與 Write* 呼叫,兩邊搬程式碼多半只是把 BeginFile 換成 BeginPatch
當工作真的需要完整物件模型,已載入活頁簿裡的工作表層級操作在 複製 XLSX 套件中的工作表 一文有說明。而如果考慮修補只是因為整份活頁簿處理變慢了,大型活頁簿效能 會先帶你看載入與儲存的時間和記憶體實際去哪了,再談怎麼選
修補寫入器、串流寫入器與完整的 TXLSXWorkbook 物件模型都在同一套 Delphi 與 C++Builder 程式庫裡;版本與完整功能清單見 HotXLS Delphi 試算表元件頁面