HotXLS 公式引擎與自訂函式：Delphi 實作

一個只儲存公式字串的試算表函式庫，和一個擁有可用公式引擎的函式庫，是兩個不同的產品，它們看起來一模一樣，直到你向其中一個要一個數字的那一刻。多數 Delphi 試算表程式碼從未察覺這個落差，因為 Excel 把它掩蓋過去了：把 SUM(B2:B501) 寫進儲存格、儲存，Excel 就在人類開啟檔案的那一瞬重新計算總和。把人類從迴圈裡拿掉，讓同一個活頁簿跑過一條直接匯出成 CSV 的伺服器管線，這個差異就不再是理論上的了。CSV 會在一個數字該出現的地方帶著字面文字 =SUM(B2:B501)，因為從頭到尾沒有任何東西真正評估過這個公式

HotXLS 站在這條線正確的那一側。它對待公式的方式跟檔案格式一樣，是儲存的文字加上一個選用的快取結果，所以單純的 CSV 匯出重現的是食譜，而不是菜餚。但它也帶著一個你可以直接呼叫的計算引擎，XLS 與 XLSX 兩個外觀用的是同一個引擎，外加一個用來解析引擎從未聽過的函式名稱的掛鉤。HotXLS 是一個原生 Object Pascal 函式庫，能從 Delphi 與 C++Builder 讀寫 XLS 與 XLSX，不需 Excel 自動化，而它的計算那一半，正是把儲存的公式依需求變回數值的東西

公式是被儲存，而非立即評估

把公式寫進儲存格並不會計算任何東西。在儲存時，活頁簿記錄的是公式文字。在 XLS 那一側，它還會記錄受 RecalcOnSave 治理的旗標，該旗標預設為 True，告訴 Excel 在開啟時重新計算。這個模型對注定要給 Excel 用的檔案是正確的，對直接消費儲存格數值的管線則是錯的——無論那是 CSV 匯出、HTML 匯出，還是你自己的程式碼把儲存格讀回來。對那些情況，要用 Calculate 明確評估。它存在於四個進入點：TXLSWorkbook、IXLSWorksheet、TXLSXWorkbook 與 TXLSXWorksheet 全都公開 function Calculate(const Formula: WideString): Variant

// 在處理程序內求值，再送出數值而非配方
Total := Book.Calculate('SUM(Sales!B2:B501)');
Sheet.Cells[502, 2].Value := Total;
Book.SaveAsCSV('sales.csv', 0, ',');   // CSV 現在攜帶數字

交給 Calculate 的運算式就是普通的 Excel 公式文字。跨工作表參照、定義名稱與巢狀函式，全都會針對目前記憶體中的活頁簿解析，這讓這個呼叫的用處遠遠超出修補 CSV 匯出。把它當成一種斷言機制。一個剛寫完五百列明細的產生器，可以向活頁簿索取它自己的總計，並拿來與它在 Pascal 裡獨立算出的數字比較，在客戶的審計人員之前逮到一個差一的範圍錯誤

這也框定了公式密集輸出的正確測試策略。Excel 仍然是公式語言的參考實作，所以對於少數幾個帶有業務後果的公式，保留一份核可的測試夾具檔案，其預期數值是由 Excel 自己產生的，並讓建置管線用 Calculate 針對這些夾具評估生成活頁簿的公式。於是差異會以 Delphi 裡失敗的測試浮現，而不是以客戶比較兩份報表時發現的差異浮現

用 OnUserFunction 加入業務函式

當引擎遇到一個它不認得的函式名稱時，它會引發一個事件，而不是直接失敗。在任一個活頁簿類別上指派 OnUserFunction，你就能自己解析這個呼叫：

procedure TReportBuilder.HandleUserFunction(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'DISCOUNT') then
  begin
    Value := Args[0] * 0.9;   // Args 以 Variant 陣列到達
    Handled := True;
  end;
end;

// 接線與使用
Book.OnUserFunction := HandleUserFunction;
Sheet.Cells[1, 1].Value := 200;
Sheet.Cells[1, 2].Formula := 'DISCOUNT(A1)';
Net := Book.Calculate('DISCOUNT(A1) + SUM(A1:A1)');

有三個細節值得注意。第一，只有在你真的認得那個名稱時，才把 Handled := True 設上去。讓它保持 False 能讓引擎繼續它正常的未知函式處理，所以單一處理常式就能服務好幾個活頁簿，而不會把經過的一切都攬下來。第二，用 SameText 不分大小寫地比較名稱，因為公式作者打 discount( 與 DISCOUNT( 是互通的。第三，引數送達時已經被預先評估過了：DISCOUNT(A1) 交給你的是 A1 的數值，而不是參照，所以一個函式無從得知它的輸入來自哪裡。最後這一點設定了下一節要談的限制

對待處理常式主體要像對待任何外部進入點一樣有防備心。Args 陣列反映的是公式作者打的任何東西，所以在對它做索引之前，先驗證引數數量與型別，並事先決定一個無效呼叫回傳什麼：一個 Variant 錯誤值，或一個引發的例外。這個選擇很重要，因為在處理常式內部丟出的例外，會傳播出去、穿過觸發評估的那個 Calculate 呼叫。在一個緊密控制的產生器裡這可以接受，但在一個評估使用者自寫活頁簿的服務裡就很失禮，因為一個壞公式就會把整個請求帶垮。在那種設定下，要在處理常式內部接住，並回傳一個周圍工作流程能辨識並記錄的哨兵值

需要位置的函式要用 Ex 變體

有些函式合理地依賴它們被評估的位置。一個因工作表而異的費率、一個對列相對的查閱、一個只在區域工作表上適用的每區乘數：這些沒有一個能單靠引數數值來回答。單純的事件無法表達這件事，所以引擎提供了 OnUserFunctionEx，它除了多一個參數之外其餘完全相同：

procedure TReportBuilder.HandleUserFunctionEx(Sender: TObject;
  const FunctionName: WideString; const Args: Variant;
  const Context: TXLSUserFunctionContext;
  var Value: Variant; var Handled: Boolean);
begin
  if SameText(FunctionName, 'REGIONRATE') then
  begin
    // 同一個公式在各區域工作表中產生不同的費率
    Value := RateForSheet(Context.SheetIndex) * Args[0];
    Handled := True;
  end;
end;

TXLSUserFunctionContext 帶著評估中儲存格的 SheetIndex、Row 與 Col。如果一個函式的結果即使只是輕微地依賴它的位置，就從一開始接上 Ex 事件。把脈絡安插進一個已有三十個公式在呼叫的處理常式，遠比在第一天就選對簽章要麻煩得多，而且這兩個事件在其他方面如此相似，幾乎沒有理由從較窄的那一個開始

自訂函式不會跟著檔案到 Excel

一個自訂函式完全活在你的處理程序裡。DISCOUNT 這個名稱只有在你的 Delphi 程式碼與其事件處理常式在執行時才有意義。在 Excel 裡開啟儲存的檔案，DISCOUNT 就只是一個無法辨識的名稱；儲存格會顯示 #NAME?，除非使用者的機器上恰好存在一個相符的 VBA 函式或增益集。這是區分一個展示與一個可出貨產品的設計事實，而它逼著你做一個必須刻意去做、而非事後才發現的選擇

逐格決定你出貨的是兩種合約裡的哪一種。使用者預期會在 Excel 裡看到重新計算的儲存格，必須完全用 Excel 自己的函式詞彙來建置。邏輯屬於機密的儲存格，應該在處理程序內用 Calculate 評估，並以純數值持久化，於是自訂函式的行為就像一條內部計算規則，而非檔案內容。會可靠地生出支援工單的失敗模式，是那個中間地帶：把一個自訂函式公式持久化，卻期待 Excel 會尊重它

純數值合約有一個安靜的好處：它保護智慧財產。一條在你的 Delphi 處理程序裡評估、並以數值出貨的計價規則，無法像一條看得見的公式那樣從活頁簿被逆向工程出來，而且使用者無法藉由編輯一個中間儲存格來破壞它。發票產生器、佣金報表與費率卡幾乎總是屬於這一營。真正需要活公式的情況，是互動式的假設分析模型，其中客戶被預期會去改變輸入並看著總計移動，而那些必須用 Excel 自己的詞彙加上定義名稱來建置

計算模式、反覆運算與 R1C1：XLS 外觀的旋鈕

XLS 外觀公開了 Excel 從檔案讀取的 BIFF 層級計算設定。CalculationMode 接受 xlCalcManual、xlCalcAutomatic（預設）或 xlCalcAutomaticExceptTables，它決定的是檔案一旦開啟後 Excel 的行為。一個帶有數千個公式的模型活頁簿，以手動模式交付往往更友善，這樣收件者就能自己決定那場重新計算風暴何時發生。EnableIteration（預設 False）連同 MaxIterations（預設 100）與 MaxIterationChange（預設 0.001），解鎖了那種出現在某些財務模型裡、屬於反覆收斂性質的刻意循環參照。ReferenceStyle 在 A1 與 R1C1 顯示之間切換，而 UseFullPrecision 則映射 Excel 的顯示精度選項

這些屬性存在於 XLS 外觀上，是因為它們映射到 BIFF 紀錄；在生成 .xlsx 時，要把公式規劃成不依賴反覆設定，或在 Delphi 裡算出收斂數值並寫入結果

陣列公式：公開進入點是 XLSX

舊式 CSE 風格的陣列公式是透過 TXLSXRange.SetArrayFormula 建立的：

// 一個涵蓋 A2:A4 的陣列公式
Sheet.RCRange[2, 1, 4, 1].SetArrayFormula('A1*{1;2;3}');

同等的方法在 XLS 類別階層裡存在，但它位於 private 區段，所以沒有受支援的方式能把新的陣列公式寫進 .xls 檔案。已開啟檔案裡既有的陣列公式能完整地來回轉換；你做不到的是建立它們。由此而來的規則夠簡單了：當陣列語意是需求的一部分時，就瞄準 .xlsx。如果一份舊式 .xls 交付物真的需要陣列行為，務實的路線是在 Delphi 裡算出陣列結果，並把個別數值寫進儲存格

本站另外兩篇相關文章：定義名稱與跨工作表公式涵蓋了引擎執行的名稱解析，而CSV 與 TSV 匯出文章則詳述了那個讓明確計算變得必要的匯出行為。完整的引擎參考，包含受支援的函式集合，隨HotXLS Delphi Component一起出貨