技術文章

HotXLS:Delphi 中的 Excel 結構化表格參照

Amount 欄放著 =[@Qty]*[@Price] 的 Excel 表格,在存出的檔案裡任何地方都不會存 [@Qty]。把活頁簿解壓開,工作表 XML 裡的 <f> 元素顯示 Excel 實際寫的東西:長長的表格限定拼法 Lines[[#This Row],[Qty]]*Lines[[#This Row],[Price]],因為 ECMA-376 Part 1 §18.17 的公式文法有 #This Row 關鍵字而沒有 @。下方某個彙總儲存格放著 SUM(Lines[Amount])。兩條字串都不含任何儲存格位址,所以只會剖析 A1 參照的引擎,在知道表格坐在哪、哪一列帶標題、公式本身住在哪一列之前,無東西可算

最後那項資訊最麻煩。欄參照可以每個表格只轉一次矩形;當前列參照不行——它被複製到每一列,指的就是另一個儲存格

為什麼結構化參照不只是換了好名字的範圍?

定義名稱凍結位址,表格參照不會。把名稱 DataBlock 指向 Sheet1!$A$2:$D$100,它就一直保持那個矩形,直到有東西改寫它。Sales[Amount] 的意思是「Sales 表格的 Amount 欄」,無論求值當下表格範圍恰好是什麼。給表格加二十列,加總就涵蓋它們;沒有參照需要調整,因為公式從頭就沒握過位址

HotXLS 每次為求值編譯公式時都重新解析這些參照。TXLSXWorkbook 在活頁簿的表格模型裡按名稱查表(TXLSXTable.Name 或 DisplayName,不分大小寫比對),對照 TXLSXTable.Columns 裡存的標題文字匹配欄位,套用項目指定詞選出列帶,然後把 'Sales'!$D$2:$D$25 這樣的絕對範圍交給編譯器。改寫後的文字只為那次編譯存在。儲存格保留的公式與當初寫下的一字不差,而且因為查表是對著重算當下的 TXLSXTable.Range 跑的,表格長大之前寫的公式照樣對更大的表格求值

HotXLS 在 Delphi 解析結構化參照 SalesTable[Amount] 的圖解:以名稱找表格、比對 Amount 欄標題、套用 #Data 指定詞,得到具體範圍
HotXLS 在剖析期對照表格模型編譯 Table[Column] 參照,轉成當下的具體範圍;表格長大,公式跟著走

矩形文法:欄、跨度與項目指定詞

HotXLS 求值的每一種整表形式都產出一塊連續矩形。接受的拼法有 [Col] 與其加括號的雙胞胎 [[Col]]、裸項目指定詞 [#Data]、[#All]、[#Headers] 與 [#Totals]、組合形式 [[#Data],[Col]]、項目指定詞內的跨度如 [[#Data],[Col1]:[Col2]],以及純跨度 [[Col1]:[Col2]]。跨度可以從右往左寫;[[Q2]:[Q1]] 解析出的兩欄與 [[Q1]:[Q2]] 相同

每個指定詞選中的列帶取決於表格上的兩個旗標。TXLSXTable.HasHeaderRow 決定 Range 的第一列是不是標題,TXLSXTable.TotalsRowShown 決定最後一列是不是合計列。純欄參照與 [#Data] 覆蓋兩者之間的列。[#All] 拿整個 Range、標題與合計都算。對沒有標題列的表格要 [#Headers]、對沒有合計列的表格要 [#Totals]、或對本文沒有任何列的表格取一欄,參照就變成 #REF!。這與 Excel 對同一請求顯示的一致,也讓不可能的參照不會悄悄變成加總為零的空範圍

含方括號、井號、@ 或單引號的欄名遵循 Excel 的轉義規則:這類字元在參照內以單引號前綴,所以叫 Unit [USD] 的標題寫成 Sales[Unit '[USD']]。單欄與當前列形式在匹配之前先解碼這些轉義。跨度與組合的 [#Data],[Col] 形式對公式所在工作表上的表格解析,所以需要在別張工作表的表格上取跨度的彙總表,改用逐欄的純欄參照更合適

HotXLS 在 Delphi 接受的結構化參照文法地圖:#Data、#Headers、#Totals 與 #All 形式涵蓋哪些表格區域,加括號拼法如何映射到相同的矩形
每種受認可的形式都涵蓋表格一段連續帶狀區域;加括號的變體與其素寫解析結果完全相同

下面的範例建一張 24 列的銷售表格,並在表格同一張工作表的 F2 到 F6 放五條彙總公式。第 1 列是標題列,第 2 到 25 列是本文,沒有合計列,所以 SalesTable[#All] 跨 25 列,而 SalesTable[Amount] 跨 24 列

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Cols: TStringList;
begin
  Book := TXLSXWorkbook.Create;
  Cols := TStringList.Create;
  try
    Sheet := Book.Sheets.Add('Sales');
    Cols.Add('Region');
    Cols.Add('Q1');
    Cols.Add('Q2');
    Cols.Add('Amount');
    Sheet.Tables.Add('SalesTable', 'A1:D25', Cols);
    // ... 寫入標題列與 24 列資料 ...

    Sheet.Cells[2, 6].Formula := 'SUM(SalesTable[Amount])';
    Sheet.Cells[3, 6].Formula := 'SUM(SalesTable[[Q1]:[Q2]])';
    Sheet.Cells[4, 6].Formula := 'COUNTA(SalesTable[[#Data],[Region]])';
    Sheet.Cells[5, 6].Formula := 'ROWS(SalesTable[#All])';
    Sheet.Cells[6, 6].Formula := 'COUNTA(SalesTable[#Headers])';

    Book.Recalculate;
    Book.SaveAs('sales.xlsx');
  finally
    Cols.Free;
    Book.Free;
  end;
end;

那個版面的期望值:F5 是 25,F6 是 4——每個標題儲存格算一個。F2 到 F4 取決於你寫進去的資料

[@Column] 怎麼知道要讀哪一列?

當前列參照的列取自正在求值的儲存格。Recalculate 走到第 7 列含 Lines[@Qty] 的公式時,HotXLS 把它解析成第 7 列的 Qty 欄;同一條公式文字在第 8 列讀的是第 8 列。Excel 寫進磁碟的長拼法 Lines[[#This Row],[Qty]] 被視為同一參照,標題帶空格時的 Lines[@[Unit Price]] 也是

前面不帶表格名的未限定形式 [@Qty] 還需要一件事實:它屬於哪個表格。HotXLS 按位置回答。求值儲存格若坐在同一張工作表上某個表格的本文列與欄跨度之內,就由那個表格供給欄位,而且在這種表格內 [@[Q1]:[Q2]] 變成一條橫跨那些欄的單列範圍。其他任何位置,參照保持未解析。公式裡用當前列參照的定義名稱,其列取自引用該名稱的儲存格,與公式就地使用的行為相同

把當前列公式留在表格自己的本文列上,程式產生它們時帶上表格名限定。限定參照經得起搬移,讀起來與 Excel 存放它們的方式一致,而表格自己列上的公式,正是 Excel 與任何其他檔案消費端對 @ 的意義達成共識的唯一情況

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Cols: TStringList;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  Cols := TStringList.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Cols.Add('Item');
    Cols.Add('Qty');
    Cols.Add('Price');
    Cols.Add('Amount');
    Sheet.Tables.Add('Lines', 'A1:D13', Cols);
    // ... 寫入標題列,第 2..13 列寫品項、數量與單價 ...

    // 一份公式文字,十二種結果:每個儲存格讀自己那一列
    for Row := 2 to 13 do
      Sheet.Cells[Row, 4].Formula := 'Lines[@Qty]*Lines[@Price]';

    // 表格下方交叉驗證:欄總計對上重算的乘積
    Sheet.Cells[15, 4].Formula :=
      'SUM(Lines[Amount])-SUMPRODUCT(Lines[Qty],Lines[Price])';

    Book.Recalculate;
    // 預期:每個 Amount 儲存格都等於 Qty * Price 時,D15 求值為 0
    Book.SaveAs('orders.xlsx');
  finally
    Cols.Free;
    Book.Free;
  end;
end;

D15 裡的檢查很便宜,值得在產生的報表建構期間留著。挑錯列的逐列公式會讓兩個總計不合,差異會以一個非零數字出現在人人能讀的儲存格裡

HotXLS 流程圖:在 Delphi 中對照求值儲存格所在列解析當前列形式 [@Amount],公式文字原樣往返,Excel 存檔後讀到同一參照
當前列參照按儲存格對照公式所在列解析,存出的檔案仍帶原始參照文字供 Excel 讀取

表格改變形狀時會發生什麼

成長是容易的情況,前提是表格定義真的長大。在 Range 為 A1:D25 的表格下方第 26 列寫入值,並不會讓第 26 列變成表格的一部分。自動擴張是 Excel 的編輯功能,由有人在表格旁打字觸發,透過 HotXLS 寫儲存格不會觸發它。匯入之後把 TXLSXTable.Range 設成新範圍,或用 TXLSXWorksheet.InsertRows 在表格內插入列——它會讓 Tables[i].Range 與工作表上所有其他參照一起移。兩條路都讓 [#Data] 變寬,而沒有一條公式需要改

收縮才是參照可能斷掉的地方,HotXLS 遵循 Excel 的規則。對表格欄做 DeleteCols,會把指名該欄的每一條結構化參照改寫為 #REF!——儲存格公式、條件化格式、資料驗證規則、圖表數列與定義名稱都在內。刪掉標題列的 DeleteRows 會刪除表格、以同樣方式使其參照失效;刪掉合計列的會清掉 TotalsRowShown,之後 [#Totals] 就沒有列可指了。普通 A1 參照走同一套調整,見 插入與刪除時的公式參照調整

TXLSXTables.DeleteByName 與透過 TXLSXTable.Name 的重新命名只動表格模型。提到舊名稱的公式保留文字,所以程式碼重新命名表格時,要在同一趟把那些公式也改掉

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Table: TXLSXTable;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('sales.xlsx') <> 1 then Exit;
    Sheet := Book.Sheets[1];  // 活頁簿工作表從 1 起算

    Table := Sheet.Tables.FindByName('SalesTable');
    if Table = nil then Exit;

    // ... 把 20 列匯入資料寫進 A26:D45 ...
    Table.Range := 'A1:D45';  // 本文現在從第 2 列延伸到第 45 列

    Book.Recalculate;  // F2 裡的 SUM(SalesTable[Amount]) 現在涵蓋 44 列

    // 記錄集式查詢,走訪表格本文,回傳以 1 起算的列號,0 = 無
    Row := Table.FindFirst(Sheet, 'Region', 'EMEA');
    while Row > 0 do
    begin
      Log(VarToStr(Sheet.Cells[Row, 4].Value));  // Log 是你自己的常式
      Row := Table.FindNext(Sheet, 'Region', 'EMEA', Row);
    end;

    Book.SaveAs('sales.xlsx');
  finally
    Book.Free;
  end;
end;

FindFirst 與 FindNext 字串不分大小寫比較、數值按值比較,而且只掃本文列,所以標題文字 Region 永遠不會被當成匹配回傳

往返保存紀律

載入時帶著 SUM(SalesTable[Amount]) 的活頁簿,存出時還是 SUM(SalesTable[Amount]);放著 Lines[[#This Row],[Qty]] 的儲存格,出去時文字完好。解析出的位址永遠不會取代使用者寫的東西。HotXLS 若改存 SUM($D$2:$D$25),檔案在 Excel 打開時看起來正常,第一次有人擴充表格就停止涵蓋新列——那是最難回溯成因的一種緩慢損壞

表格定義本身,包括無標題表格與每表註解,透過 資料驗證、自動篩選與 Excel 表格 涵蓋的表格模型往返保存。許多儲存格共用一條公式模式時,XLSX 可以把它存成一條共用公式,那些儲存格的展開見 共用公式 si 展開。Excel 存出的計算欄往往正是那個形式,所以表格本文裡的結構化參照在求值前會先過兩條路徑:共用公式先展開成逐格文字,然後每個儲存格解析自己的列

這裡用到的表格模型、Recalculate 引擎與工作表插入刪除呼叫都是 HotXLS 的一部分——它讓 Delphi 與 C++Builder 不必安裝 Excel 就能讀寫 XLS、XLSX 與 ODS。完整 API 參考在 HotXLS Delphi 試算表元件頁面