LlosLab 軟體開發部落格面向 PDF、Delphi 與文件軟體開發者的工程筆記

技術文章

Delphi 中 XLSX 共用公式 si 展開：常見陷阱

HotXLS Component

XLSX 裡的共用公式從屬儲存格（follower），本身不帶任何公式文字。它的 <f t="shared" si="N"/> 元素，指向工作表中別處的一個主儲存格，讀取端必須依列與欄的差值，位移主公式，才能重建出完整的公式文字。適用於 Delphi 與 C++Builder 的 HotXLS Component，會在開啟檔案時就完成這項展開工作，所以每一個從屬儲存格，回報的都是一個完整的公式

如果你曾經用某個第三方函式庫，載入一份真實世界的 XLSX 檔案，結果發現一整欄一千個公式裡，恰好只有一個儲存格有公式文字，其餘 999 個都是空字串，那你已經從錯誤的一端遇上了這項功能。檔案沒有任何損毀，它做的只是 ECMA-376 所允許的事，而讀取端只是單純地在 XML 停下的地方也跟著停下來

為何共用公式的儲存格是空的？

因為這種格式是刻意把公式只儲存一次。在 ECMA-376 第 1 部分與 ISO/IEC 29500-1 中，<f> 元素（§18.3.1.40）帶有一個型別為 ST_CellFormulaType 的 t 屬性，其值 shared 代表這個儲存格屬於由 si 屬性所識別的某個群組。該群組中恰好只有一個儲存格──主儲存格──同時帶有一個 ref 屬性，標明這個群組所適用的範圍，也只有這個儲存格，會以元素內容的形式攜帶公式文字。群組中其他每一個儲存格，都是從屬儲存格。它同樣帶有 t="shared" 與相同的 si，但元素內容卻是空的。Excel 積極地寫出這類群組，因為對一個二十萬列的欄位往下填滿，能把二十萬個公式字串，壓縮成一個字串加上十九萬九千九百九十九個微小的佔位元素。這種節省是真實的，而代價則完全落在讀取端身上：沒有展開處理，從屬儲存格本身毫無意義可言

這種位移是一種轉換，不是文字複製

HotXLS 解析一個從屬儲存格的方式，是先找到登記在同一個 si 底下的主儲存格，計算出從主儲存格錨點到目前儲存格之間的列與欄差值，再依這個差值，轉換主公式裡的每一個參照。相對維度會移動，絕對維度不會移動，而混合參照則只有其非絕對的那一半會移動。字面字串會被完全跳過，所以一個恰好包含文字 "A1" 的公式，在每一個從屬儲存格裡，這段文字都會保持不變

const
  // xl/worksheets/sheet1.xml, trimmed to the interesting cells
  SheetXml: WideString=
    '<row r="1"><c r="A1"><v>1</v></c>'+
    '<c r="B1"><f t="shared" si="4" ref="B1:B3">'+
    'A1+$A$1+A$1+$A1+&quot;A1&quot;+SUM(A1:A2)</f><v>7</v></c></row>'+
    '<row r="2"><c r="B2"><f t="shared" si="4"/><v>8</v></c></row>'+
    '<row r="3"><c r="B3"><f t="shared" si="4"></f><v>9</v></c></row>';

var
  Wb: TXLSXWorkbook;
  Sh: TXLSXWorksheet;
begin
  Wb:= TXLSXWorkbook.Create;
  try
    Wb.Open(FileName);
    Sh:= Wb.Sheets[1];
    // Master, verbatim
    // B1 -> A1+$A$1+A$1+$A1+"A1"+SUM(A1:A2)
    // Follower one row down: relative row moves, absolute row frozen,
    // the mixed A$1 keeps its row, and the literal stays a literal
    // B2 -> A2+$A$1+A$1+$A2+"A1"+SUM(A2:A3)
    ShowMessage(Sh.Cells[2, 2].Formula);
  finally
    Wb.Free;
  end;
end;

ref 屬性是一道閘門，不是裝飾用的。如果某個從屬儲存格的座標，落在主儲存格適用範圍之外，它就不會被展開，因為這時檔案所做的宣稱，已經超出該群組能支援的範圍。同樣地，當某次位移會把參照推到第一列之上、或 A 欄之左時，HotXLS 會為那個 token 輸出 #REF!，而不是悄悄把它箝制在邊界內──這正是 Excel 本身在做同樣編輯時會產生的結果。這種轉換，與插入或刪除列時發生的參照改寫，是近親、卻不是同一件事。後者有自己一套規則，決定當一次編輯切過某個範圍時該怎麼處理，另外在 插入與刪除時的公式參照調整一文 中有描述。共用展開則單純得多：它是相對於一個已知錨點的一次純位移運算，只在剖析當下執行一次

位移器必須涵蓋哪些參照形式？

必須涵蓋全部，否則這個展開機制就是一個偽裝過的資料遺失錯誤。一個只認得 A1 與 A1:B2 的天真位移器，會損毀或丟掉那些較為罕見的形式，而真實的活頁簿裡，這類形式比比皆是。HotXLS 的共用公式轉換器，會先辨識出整個 A1 家族，再決定要移動哪個部分。像 [Book.xlsx]Sheet1!A1 這樣的外部活頁簿參照，以及像 Sheet1:Sheet3!A1 這樣的 3D 參照，會保持前綴不變，只有尾端的儲存格參照會位移。加了引號的工作表名稱能存活下來，甚至包括工作表恰好名為 A1 這種棘手情況，所以 'A1'!A1 只會位移驚嘆號之後的部分。整欄參照 A:A 只會移動它的欄維度，別無其他；整列參照 1:1 只會移動它的列維度，別無其他；$A:$A 則完全不會移動。像 Table[A1] 這種結構化表格參照，則完全不動，因為方括號內的部分是欄名，不是座標

// One master, expanded two columns to the right and zero rows down.
// Master D1: A1+A:A+$A:$A
// F1       : C1+C:C+$A:$A
//
// One master, expanded three rows down and zero columns across.
// Master A1: B1+$C$1+"A1"+A:A+1:1+'Data'!A1+LOG10(A1)+Table[A1]+'A1'!A1
// A3       : B3+$C$1+"A1"+A:A+3:3+'Data'!A3+LOG10(A3)+Table[A1]+'A1'!A3
//
// Note what did NOT move in the second line: the absolute $C$1, the
// string literal "A1", the whole column A:A under a pure row delta,
// the function name LOG10, and the structured reference Table[A1]

函式名稱在這裡是個不動聲色的陷阱。一個只會抓取「字母接數字」樣式的 token 掃描器，會很樂意把 LOG10 在往下一列時改寫成 LOG11。HotXLS 要求候選 token 的前後都必須存在參照邊界，所以一個後面接著字母、數字、底線、句點，或開括號的識別碼，就不算是一個儲存格參照。如果你處理的是另一套記法家族，同樣的邊界問題，會以不同的方式浮現，R1C1 記法一文 涵蓋了這兩套模型分歧之處

為何一個自封閉的 f 元素會吞掉下一個值？

因為一個自封閉元素，不會產生任何結束元素事件。這是整個功能裡代價最高昂的一個錯誤，而且並非特定於哪一個 XML 剖析器。在 TXMLReader 中，<f t="shared" si="4"/> 恰好只會觸發一個 Element 事件、IsEmptyElement 設為 True，卻從不觸發對應的 EndElement。因此，一個只在 EndElement 時才結束公式擷取狀態的剖析器，會一直停留在公式狀態中，而它接下來看到的文字──也就是 <v> 裡快取的結果值──就會被附加到公式緩衝區裡。更糟的是，這個狀態會跨越儲存格邊界存活下來，所以下一個真正帶有 <f> 的儲存格，其公式文字會被前一個儲存格吞掉。修復方式，是每當 IsEmptyElement 為 True 時，就在 Element 事件本身結束公式狀態，並在那裡就完成整個從屬儲存格的解析，而不是等待之後再處理。這代表要在處理空元素的那個分支裡，就從屬性中讀出 t、si、ref、aca 與 ca，套用共用展開，把重新計算屬性寫回儲存格，並清除共用狀態。請留意，格式允許兩種寫法並存，<f t="shared" si="4"/> 與 <f t="shared" si="4"></f>，而第二種寫法確實會觸發 EndElement。一個正確的讀取器，必須讓這兩種寫法得到完全相同的處理，這正是為何 HotXLS 在同一份回歸測試檔案裡，同時涵蓋這兩種寫法的原因

稀疏、無序的 si 值與待處理佇列

si 屬性是檔案本身提供的一個不帶正負號整數，不是一個由你掌控的陣列位置。規範裡沒有任何規定要求共用索引必須是連續的、必須從零開始，或必須依遞增順序出現，也沒有任何東西能阻止一份惡意或單純奇怪的檔案，在第一個儲存格上就使用 si="4294967290"。因此，用觀察到的最大 si 值來設定查找陣列的大小，是一種記憶體耗盡的攻擊手法，而不是一種最佳化。HotXLS 在開啟活頁簿的路徑上，改用一張已排序的稀疏表：共用群組是以其整數鍵，登記在一個已排序的 TStringList 中，讓查找變成一次對實際存在群組數量做的二元搜尋，與索引數值的大小完全無關。順序則是問題的另一半。主儲存格通常會在文件順序中先於它的從屬儲存格出現，但這只是慣例、不是規則，所以任何在剖析當下無法解析其 si 的從屬儲存格，都會被放進一個待處理佇列。當這張工作表處理完畢後，佇列會依此時已完整的表重播一次，較晚出現的主儲存格，就能解析出它們的孤兒從屬儲存格。始終找不到主儲存格的儲存格，則會保持公式為空，這對一份參照了自己從未定義過的群組的檔案而言，是誠實的結果

不載入整份活頁簿就展開共用公式

串流讀取器在記憶體預算緊得多的情況下，面臨的是同樣的需求，它們的解法是使用一張工作表本地的表。TXLSDirectReader 與 TXLSRowCursor 都會把從屬儲存格展開成完整的逐儲存格公式，同時保留它們原有的有界記憶體與投影行為，所以對一張 300 MB 工作表做單向掃描，依然能拿到真正的公式文字

var
  Reader: TXLSDirectReader;
  Cursor: TXLSRowCursor;
begin
  // Projection: only rows 2..3, only column A. The master lives in row 1,
  // outside the projection, and is still parsed so the followers resolve
  Reader:= TXLSDirectReader.Create;
  try
    Reader.FirstRow:= 2;
    Reader.LastRow:= 3;
    Reader.IncludeColumn(1);
    Reader.OnCell:= HandleCell;   // Cell.Formula is fully expanded here
    Reader.ReadFile(FileName);
  finally
    Reader.Free;
  end;

  // Forward-only row traversal, same expansion
  Cursor:= TXLSRowCursor.Create;
  try
    Cursor.Open(FileName);
    if Cursor.FindFirst then
      repeat
        if Cursor.CellCount > 0 then
          WriteLn(Cursor.RowIndex, ': ', Cursor.Cells[0].Formula);
      until not Cursor.FindNext;
  finally
    Cursor.Free;
  end;
end;

這樣的設計，衍生出兩個限制。第一，投影絕不能跳過主儲存格。用 FirstRow 與 LastRow 設定的列篩選器，或用 IncludeColumn 建構的欄篩選器，可以不把主儲存格輸出給你的回呼函式，但剖析器仍然必須記錄它的 si、錨點座標、適用範圍與公式文字，否則投影範圍內的每一個從屬儲存格都會解析成空。只有從屬儲存格這一側的工作──位移與數值解碼──才能安全地跳過。第二，這張表是按工作表劃分的，其生命週期必須明確管理：TXLSRowCursor 在一次工作表掃描過程中，只持有一個實例，並會在重新開始、切換工作表、檔案結尾、例外，以及關閉時清除它，所以定義在第一張工作表上的群組，絕不會外洩到第二張工作表。由於串流路徑是一條熱迴圈，它用的是開放定址整數雜湊，而不是已排序的字串表，這樣就能避免每個儲存格都要做一次整數轉字串的運算

儲存時會發生什麼事，邊界又在哪裡

一旦一個從屬儲存格被展開，它就變成一個普通的公式，HotXLS 會把它寫回為一個獨立的 <f> 元素，不帶 t="shared"、也不帶 si。這樣的來回讀寫是穩定的，快取的 <v> 結果也會保留下來，但對一張大量使用共用公式的工作表而言，輸出會比輸入大，而且 Excel 原本建立的分組，並不會在儲存時被重建出來。如果共用群組的位元組層級保真度，對你而言比每個儲存格都有真正的公式文字更重要，這就是你要接受的取捨。順帶一提，XLS 這一側則不同：BIFF8 的 SHRFMLA 記錄，有自己的一套編碼方式與自己的寫入器，並在活頁簿上有一個共用群組開關

有兩個相關的東西，雖然共用同一個 <f> 元素，卻明確地不算是共用公式。舊式的 CSE 陣列公式，使用 t="array"，並帶有一個涵蓋錨定範圍的 ref；動態陣列則使用同樣的 t="array" 寫法，卻是靠一個 cm 屬性、經由 cellMetadata 串接到一個 XLDAPR 記錄來識別的。把一個動態陣列溢位儲存格，當成共用或 CSE 從屬儲存格來處理，是一個貨真價實的正確性錯誤，這個區分在 動態陣列與溢位公式一文 中有討論。把這三種情況，理解成三個恰好共用同一個標籤名稱的剖析器，程式碼就能保持誠實

本文所描述的共用公式展開機制、串流讀取器，以及參照轉換器，都隨附於 HotXLS Excel 元件（適用於 Delphi 與 C++Builder）之中；產品頁面提供完整的公式與直接讀取 API 參考文件，包含上文所用到的投影屬性