LlosLab Software Development BlogEngineering notes for PDF, Delphi, and document software developers.

Technical Article

在 Delphi 中寫入 BIFF8 樞紐分析表：SXDB 與 SXLI

HotXLS Component

舊版 Excel 二進位格式的幾乎每個部分都是帶有乾淨雙位元組型態和雙位元組長度的單個記錄；儲存格是 LABELSST 或 NUMBER；合併區域是 MERGEDCELLS；您可以透過一次遍歷一筆記錄並在型態字詞上進行分派來讀取大部分工作表；樞紐分析表打破了這種節奏；單個樞紐分析表不是記錄，它是一個由數十個協同記錄組成的小型程式，分佈在同一個 OLE 複合檔案串流中的兩個不同位置，且它們之間的關係是位置性的、位元打包的且不容出錯；這是大多數 BIFF8 讀取器要麼完全跳過，要麼保留為不透明位元組的結構，因為從頭開始寫入一個意味著要重現 Excel 本身維護的每個交叉參照

樞紐分析表之所以困難，是因為它實際上是兩個焊接在一起的產出物；包括樞紐分析快取（具有其自身子串流的來源資料的獨立快照）以及資料表檢視（表示哪些欄位位於哪個軸上的版面配置）；快取和檢視透過索引互相參照；弄錯一個索引，檔案開啟時就會出現重新整理錯誤或靜默的空白網格

樞紐分析快取本身就是一個子串流

快取作為完整的 BIFF子串流存在於活頁簿全域串流中，由檔案型態為 0x0006 的 BOF 記錄框定（該值標記樞紐分析快取，不同於活頁簿的 0x0005 或工作表的 0x0010），並由對應的 EOF 關閉；在該訊框內部，結構是固定的；SXDB 記錄是快取標頭；它攜帶記錄計數、快取欄位數量，以及資料表檢視將引用以將自身繫結到此快取的串流識別碼；每個來源欄位隨後會貢獻一個 SXFDB 欄位定義記錄，後接對其進行分類的 SXFDBType，然後是該欄位取得的唯一值（以每個不重複值輸出一個項目記錄的形式發送）

項目記錄是快取發揮作用的地方；文字值變為 SXSTRING，數值變為 SXNUM，邏輯值變為 SXBOOLEAN，且公式錯誤變為 SXERR；快取不儲存來源網格，它儲存每個欄位的不重複值，以及一個表示對於記錄 n，每個欄位取得哪個不重複項目的索引表；這就是為什麼以程式設計方式建立樞紐分析表不是複製儲存格的問題；您必須掃描來源範圍、從其持有的值推斷每個欄位的型態、將它們去重為型態化項目清單，並將每一列記錄為項目索引的元組（Tuple）；HotXLS 正是這樣做的：全數值欄位使用 SXNUM 項目輸出，混合文字欄位變為 SXSTRING 項目，且日期作為序號值透過相同的數值路徑攜帶

SXDBB 以及使其變得有趣的位元打包

每筆記錄的索引表是整個結構中在技術上最令人好奇的部分，它存在於 SXDBB 記錄中；傳統的編碼會將每個欄位的項目索引儲存為 16 位元字詞；Excel 不會這樣做；它將每個欄位的索引精確地打包到定址該欄位項目所需的位元數中，且不多不少；寬度是 ceil(log2(itemCount + 1)) 位元；+ 1 非常重要：額外的值是一個哨兵，表示「空白，此記錄中此欄位沒有值」，因此具有三個不重複項目的欄位需要表示四個狀態，因此需要兩個位元，而不是僅三個項目所暗示的一個位元；完全沒有項目的欄位貢獻零個位元，並在打包過程中被完全跳過

一筆記錄的位元跨所有欄位級聯，然後下一筆記錄在新的位元組邊界上開始；記錄是位元組對齊的，而不是端到端位元打包的，這使得對資料表的隨機存取變得易於處理，代價是每列有幾個填充位元；位元組內的打包是最低有效位元優先；一旦您接受了這兩個規則，編碼器就是一個簡單的位元泵，而解碼器則是它的鏡像

// Width of one field's index in the SXDBB stream.
// citmTotal distinct items need ceil(log2(citmTotal + 1)) bits,
// the +1 reserving a "blank" sentinel value.
function BitsForFieldItems(itemCount: Integer): Integer;
var
  capacity: Integer;
begin
  Result := 0;
  if itemCount <= 0 then
    Exit;            // empty field contributes zero bits
  Result := 1;
  capacity := 2;
  while capacity < itemCount + 1 do
  begin
    Inc(Result);
    capacity := capacity * 2;
  end;
end;

無法忽略此細節的原因是單個 BIFF 記錄上的 8224 位元組上限；格式中的每筆記錄（包括樞紐分析記錄）都必須將其承載資料限制在最多 8224 位元組中，且具有數千個來源列的繁忙樞紐分析快取在輸出每一列之前很久就會突破該限制；因此索引表被拆分了；HotXLS 將單個 SXDBB 主體限制在 8220 位元組（這是 8224 記錄限制減去型態和長度的 4 位元組記錄標頭），將其除以一個打包記錄的位元組寬度以了解符合多少個完整列，然後根據列計數的要求輸出多個連續的 SXDBB 記錄；每個連續記錄在記錄邊界上乾淨地重新開始，因此沒有任何列會被跨越兩個記錄切開；知道每筆記錄之位元寬度的讀取器可以按順序步進遍歷每個 SXDBB，就像它們是一個連續的位元陣列一樣

檢視配置：主體使用 SXLI，頁面使用 SXPI

快取建置完成後，資料表檢視是後半部分；它的核心是軸線項目（Axis line item），即樞紐分析主體的列，列舉了資料表所繪製的列欄位值和欄欄位值的每種組合；這些攜帶在 SXLI 記錄中（記錄型態 0x00B5，描述於 [MS-XLS] §2.4.275）；一個 SXLI 保存許多行，同樣直到 8224 位元組限制迫使產生新記錄為止，且它使用了一個小壓縮技巧：每行僅儲存它與其上方行的不同之處（表示為公共前綴計數），因此深度巢狀的軸不會在每列上重複外部欄位值；總計行以及任何記錄的第一行總是將該前綴計數重設為零，因此讀取器永遠不需要向後跨越記錄邊界來重構行

頁面軸（座落在樞紐分析表上方的篩選器下拉選單）是獨立的記錄；SXPI（記錄型態 0x00B6，[MS-XLS] §2.4.276）攜帶每個頁面欄位一個 10 位元組的項目：樞紐分析欄位索引 isxvd、選定的快取項目 iCache、位置字詞 ipos，以及遺留的物件識別碼 objId；iCache 值是需要注意的值；顯示「(全部)」、不進行任何篩選的頁面欄位儲存哨兵值 0x7FFD，而不是真實的項目索引；以程式設計方式建置的樞紐分析表開啟時，每個頁面欄位都設定為「(全部)」，直到呼叫者預先選取一個項目為止，此時該項目的快取索引會替換哨兵值，且 Excel 開啟時已套用了篩選器；在這些記錄旁邊座落著描述個別欄位及其格式設定的支援記錄，用於欄位檢視定義的 SXVD 和 SXVDEx、用於對每個軸進行排序之欄位索引清單的 SXIVD，以及用於數值格式設定的 SXFormat，每一個都向後索引到主體行所參照的相同快取中

二合一寫入器：原始 Blob 與型態化模型

HotXLS 保留兩個完全獨立的寫入樞紐分析表路徑是有結構性原因的，這直接源於對保真度的要求；當從磁碟讀取活頁簿時，其樞紐分析記錄是由 Excel 或其他產生器寫入的，它們可能會使用工作表寫入器無法完全模擬的記錄變體、排序奇特之處或擴充記錄；對這些位元組唯一安全的操作是將它們原封不動地交回；因此，從檔案匯入的樞紐分析表被標記為 FromRawBlobs = True，且在儲存時，寫入器會逐字重新播放保留的記錄 blob；沒有任何內容被重新產生，沒有任何內容被重新解釋，且透過開啟和儲存的來回轉換是位元組穩定的

程式建置的樞紐分析表則是相反的情況；沒有要保留的原始位元組，只有型態化物件模型：具有其欄位和項目清單的 TXLSPivotCache，以及具有其軸分配的 TXLSPivotTable；該資料表被標記為 FromRawBlobs = False，且寫入器以艱難的方式對其進行序列化，輸出新的 BOF = 0x0006 快取子串流，從型態化模型持有的項目索引中打包 SXDBB 索引表，並根據軸設定配置 SXLI 和 SXPI 記錄；該旗標是讓這兩種型態共存於同一個活頁簿中的關鍵；沒有它，單一寫入器要麼必須放棄讀入之資料表的保真度，要麼拒絕產生新的資料表；讀入之資料表所攜帶的任何產生器特定擴充記錄都會保留為補充記錄（可透過資料表的 SupplementalRecords 清單連入），因此透過型態化模型檢查的資料表不會遺失模型未描述的部分

在程式碼中建置樞紐分析表

上述所有機制都位於單個呼叫背後；AddPivotTable 獲取 A1 標記法中的來源範圍、資料表左上角錨定的目標儲存格，以及名稱；它解析該範圍、掃描它以推斷欄位型態並建置快取（如果另一個資料表已繫結到相同範圍，則重複使用現有快取），並返回一個型態化的 TXLSPivotTable（每個來源欄位有一個欄位，每個欄位最初都在軸外）；然後您將欄位放在軸上並選擇彙總方式；特徵標記正是如此，且快取、SXDBB 打包以及檢視記錄都會在儲存時為您產生

uses
  lxHandle, lxPivot;

var
  Book : TXLSWorkbook;
  Sheet: IXLSWorkSheet;
  Pivot: TXLSPivotTable;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('Sales.xls');
    Sheet := Book.Sheets[1];

    // Source A1:E500 on 'Data'; anchor the pivot at row 3, col 1.
    Pivot := Sheet.AddPivotTable('Data!$A$1:$E$500', 3, 1, 'SalesByRegion');
    if Pivot <> nil then
    begin
      Pivot.AddRowField('Region');
      Pivot.AddColumnField('Quarter');
      Pivot.AddDataFieldByName('Revenue', xlpaSum);
    end;

    Book.SaveAs('Sales-Pivot.xls');
  finally
    Book.Free;
  end;
end;

來源範圍的第一列被讀取為命名快取欄位的標頭，因此 AddRowField('Region') 透過其標頭文字而不是透過位置來匹配欄位；因為傳回的資料表是 FromRawBlobs = False 的型態化模型，所以寫入器會走從頭開始的路徑：它建置一個獨立的快取，該快取在重新整理時不依賴於仍然存在的來源範圍，這正是當樞紐分析表將發送給可能移動或刪除底層資料的收件者時您所期望的屬性

讀取和協調您未產生的檔案之樞紐分析和快取記錄（包括原始 blob 保留路徑）在活頁簿稽核和轉換工作台逐步說明中有所介紹；當來源範圍達到數萬列且 SXDBB 串流跨越許多連續記錄時，大型活頁簿效能筆記中的技術可以防止快取建置主導您的執行時間；兩者都與 Delphi 和 C++Builder 的 HotXLS 試算表元件中隨附的樞紐分析寫入器搭配，並與本部落格其他地方介紹的儲存格、公式、圖表和格式設定 API 搭配