在 Delphi 中以恆定記憶體寫入百萬列 XLSX

一個報表任務良好地運作了一年。它建置了一個活頁簿，用查詢傳回的任何內容填滿工作表，然後將其儲存。接著，一位擁有五年歷史資料的客戶要求完整匯出，列數超過了一百萬，該處理程序在檔案還未寫入磁碟之前就因為記憶體不足錯誤而終止了。程式碼本身並沒有錯。它是為了能在最後將整個活頁簿序列化，而將其全部保留在 RAM 中，因此它所需的記憶體與被要求寫入的列數同步增加。

解決方案不是換一台更大的機器，而是一種不同的寫入模型。HotXLS 中的串流直接寫入器（direct writer）會隨著列資料的到來，增量地發射 OOXML 封裝，因此它使用的記憶體不取決於您寫入了多少列。它是串流讀取器在寫入端的對應工具：讀取器是在不建構儲存格節點樹的情況下走訪巨大的工作表，而寫入器同樣是在不建構儲存格節點樹的情況下產出工作表。

為何一般的儲存路徑會隨著資料量成長

常規的 TXLSXWorkbook 路徑會先建構完整的物件模型。每個儲存格連同其值、類型與樣式參考，都會作為物件存在於記憶體中，直到您呼叫儲存，此時整個節點樹才會被序列化到封裝中。當您想要讀取工作表、編輯它、重新計算，然後將其寫回時，該模型是正確的選擇，因為對任何儲存格的隨機存取正是編輯所需要的。但當您只是單向地傾注列資料而且不會回頭看時，這就是錯誤的選擇了，因為您為保持每一列常駐付出了代價卻得不到任何好處。一百萬列的物件，無論您是否會重新存取它們，它就是一百萬列的物件。

串流寫入器移除了節點樹

儲存格一被寫入，它就會變成工作表部分中的位元組，並且這些位元組會被移交給 zip 輸出。工作表串流是唯一會增長的緩衝區，而且它是在輸出端增長，而非作為常駐在堆積（heap）中的 Delphi 物件。常駐在記憶體中的是固定數量的記帳資訊：工作表名稱、幾個旗標、目前的列號，以及一個儲存格計數器。從第一列到第一千萬列，這組資訊都不會改變。

共用字串表是個陷阱，而行內字串是出路

大多數串流 XLSX 寫入器在遇到文字之前都表現得很好。OOXML 格式通常將字串儲存在共用字串表中：每個不同的字串只會被寫入到一個獨立的部分中一次，而每個包含該字串的儲存格帶有的是指向該表的索引，而非文字本身。對於充滿重複標籤的檔案來說，這是一個很好的空間最佳化，也是標準儲存路徑使用的預設設定。然而這對於串流寫入器卻是個殘酷的問題。為了刪除重複項目，該表必須在整個任務過程中保持常駐，因為任何即將到來的列都可能重複已經寫過的列中的字串，且只有一份包含所有已見過字串的完整記憶體對應表，才能分配正確的索引。因此，串流寫入器唯一無法串流的結構，正是那個原本應該讓檔案變小的結構。文字密集的資料擊潰了您一開始所追求的串流。

直接寫入器完全避開了這個表。字串以行內方式寫入，作為 t="inlineStr" 儲存格，其文字透過 <is><t> 元素直接位於儲存格內。沒有需要累積的表，也沒有需要保留的已見字串對應圖，因此文字欄位所耗費的記憶體並不會比數字欄位多。這項權衡是明確的，值得清楚說明。行內字串會在其出現的任何地方重複相同的文字，因此，帶有許多相同標籤的檔案，其磁碟大小會比使用共用字串表的版本更大。您犧牲檔案大小來換取恆定的記憶體。對於單次（one-pass）匯出來說，這是明智的取捨，而且 zip 壓縮無論如何在輸出時都會吸收掉大部分的重複。

樣式表在最後到達，並帶有一個日期格式

樣式帶來與字串相同的緊張局勢。活頁簿透過一個樣式部分來參考其格式，而串流寫入器無法讓不斷成長的樣式調色盤與已經清除（flushed）的儲存格保持同步。直接寫入器解決此問題的方法是保持樣式表小而固定，並在關閉時發射它，而非在最前面。一個預設的儲存格格式涵蓋了一般的儲存格。一個日期數字格式涵蓋了日期，並以 yyyy-mm-dd 的格式代碼註冊在儲存格格式清單中的已知位置。

該日期格式就是 WriteDateTime 作為專有呼叫存在的原因。Excel 沒有原生的日期類型；日期就是一個披著日期格式的數字。WriteDateTime 會將值作為純序列號寫入，並使用該日期樣式標記儲存格，讓試算表將其渲染為日期而非五位數的整數。它所寫入的序列號對於往返操作（round-tripping）很重要。它將 TDateTime 值直接儲存在 1900 日期系統下，這與常規 TXLSXWorkbook 儲存路徑所使用的慣例相同。因為兩條路徑在序列號上是一致的，由串流寫入器產生的檔案在透過 HotXLS 讀取器讀回並在 Excel 開啟時，日期將與您預期的一致，在寫入器和讀取器之間不會出現差一錯誤（off-by-one）或紀元（epoch）上的意外。

順序是強制的，因為位元組已經送出

串流透過一個您必須遵守的規則來換取其記憶體設定檔。輸出是邊處理邊發射的且無法重訪，因此所有內容必須依照它在檔案中出現的順序寫入。在同一列中，儲存格要依遞增的欄位順序寫入。在同一個工作表中，列要依遞增的順序寫入。這裡沒有緩衝區能讓寫入器在事後對您的儲存格進行排序，因為您剛才關閉的列已經是 zip 串流中的位元組且無法再觸及。若在同一列中先交給它第 5 欄接著再給第 2 欄，輸出格式就會出錯，因為寫入器只是單純依照您給它的順序發射出您給它的東西。

針對常見情況，列的 API 有個小小的便利功能。AddRow 接收以 1 為基底的列索引，但傳入 0 表示接續上一列的下一列，因此循序填入時不需要追蹤並傳遞遞增的計數器。每一次 AddRow 都會關閉其前一列，而每一次 AddSheet 都會關閉其前一個工作表，因此您絕不需要明確結束一列或一個工作表。您只要開始下一個，寫入器就會為您完成已經開啟的結構。

在文字進入 XML 時處理跳脫（Escaping）

您寫入的任何文字都會成為 XML 文件的一部分，因此那五個預先定義的 XML 實體必須被跳脫（escape），否則只要值包含了 & 符號或角括號，該封裝就會失效。寫入器會為您在行內字串文字和公式文字上跳脫 &、<、>、" 以及 '，這兩個地方是呼叫端提供的字元落入標記語言內的位置。您只需傳遞原始的 WideString，寫入器就會確保其安全性。像 Smith & Co <Ltd> 這樣的產品名稱或參照已引用工作表名稱的公式，出來的將是格式正確的 XML，您完全不需要在您的程式碼端處理任何跳脫。

生命週期，以及為何 Destroy 仍會執行關閉

完成封裝就是寫入活頁簿部分、樣式部分、內容類型和關聯部分，最後是 zip 中央目錄的動作。那項工作發生在 Close 中。一個從未被關閉的封裝是一個不完整的 zip 檔，沒有任何試算表程式能夠開啟，因此關閉並非選用的清理動作，它是讓檔案生效的必要步驟。為了防止在錯誤路徑中忘記呼叫 Close，若封裝仍處於開啟狀態，Destroy 會盡力進行關閉，因此釋放寫入器並不會洩漏底層的 zip 物件，即使例外情況略過了明確的呼叫。最可靠的模式依然是一般的 Delphi 模式：在 try 區塊內寫入，呼叫 Close，並在 finally 區塊中進行釋放（free）。

從頭到尾串流一張大工作表

這份任務的輪廓是開始、新增工作表、傾注列資料、關閉。以下範例寫入了一個標頭列，接著寫入一長串具類型的資料列，混合了字串、數字、沒有快取結果的公式以及日期。它在處理十列和處理一千萬列所使用的記憶體是一樣的，因為每個儲存格一經寫入就會立即進入 zip 串流中。

uses
  lxDirectWrite;

procedure StreamReport(const Path: string; RowCount: Integer);
var
  W: TXLSDirectWriter;
  I: Integer;
begin
  W := TXLSDirectWriter.Create;
  try
    W.BeginFile(Path);
    W.AddSheet('Sales');

    // 標頭列，以遞增的欄位順序寫入
    W.AddRow(1);
    W.WriteString(1, 'Item');
    W.WriteString(2, 'Qty');
    W.WriteString(3, 'Price');
    W.WriteString(4, 'Total');
    W.WriteString(5, 'Date');

    // 資料列；傳遞 0 給 AddRow 以自動接續下一列
    for I := 1 to RowCount do
    begin
      W.AddRow(0);
      W.WriteString(1, 'Item ' + IntToStr(I));
      W.WriteNumber(2, I);
      W.WriteNumber(3, 1.5 + (I mod 10));
      W.WriteFormula(4, Format('B%d*C%d', [I + 1, I + 1]));
      W.WriteDateTime(5, EncodeDate(2026, 1, 1) + I);
    end;

    W.Close;                       // 完成封裝
  finally
    W.Free;
  end;
end;

加入第二張工作表只需在您繼續之前再呼叫一次 AddSheet 即可，寫入器會在開啟第二張時關閉第一張。布林旗標使用 WriteBoolean，它會寫入一個具類型的布林儲存格，而不是文字「True」。如果您想確認檔案是完好的並且支援往返操作，CellCount 屬性會報告總共寫入了多少個儲存格，而使用串流讀取器讀回該結果時，應該會報告相同的總數。

  // 在上述的資料工作表之後加入第二張具類型旗標的工作表
  W.AddSheet('Flags');
  W.AddRow(1);
  W.WriteString(1, 'Name');
  W.WriteString(2, 'Active');
  W.AddRow(0);
  W.WriteString(1, 'alpha');
  W.WriteBoolean(2, True);

  WriteLn(Format('寫入了 %d 個儲存格', [W.CellCount]));

寫入到串流而非檔案也是相同的程式碼，只需將 BeginFile 替換成 BeginStream，這讓伺服器可以將活頁簿傳送到 HTTP 回應或是記憶體串流中，而不需要在磁碟上產生暫存檔。寫入器並不擁有您傳入的串流，因此您可以掌握它的生命週期。

當這項工作是一個應需求建置活頁簿的伺服器端點時，針對伺服器和批次任務的串流寫入中的模式示範了如何將其連接到請求處理常式以及排程匯出中。當問題涉及非常大型活頁簿的廣泛成本（包含讀取與寫入）時，Delphi 中大型活頁簿的效能涵蓋了時間和記憶體實際消耗的地方。串流直接寫入器作為 Delphi 與 C++Builder 的 HotXLS 元件的一部分提供，並且與本部落格其他地方涵蓋的完整讀取、編輯以及儲存 API 齊名。