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

技術文章

在 Delphi 中逐步追蹤 Excel 公式求值過程

HotXLS Component

Excel 在顯眼之處隱藏了一個小型除錯工具。選取一個儲存格，開啟「公式」並點擊「評估公式」，就會出現一個對話框，其中的公式有一個子運算式帶有底線。按下「評估」後，該子運算式會折疊成其值，然後下一個子運算式帶上底線，您可以看著一個長運算式一次一個化簡步驟地收縮為單一數字。這是找出巢狀 IF 中哪個分支實際被觸發、或哪個參照送出了錯誤合計的最快方法。HotXLS 透過 TXLSFormulaTracer 重現了這個精確的行為，讓 Delphi 或 C++Builder 程式能為稽核活頁簿、除錯生成的公式，或向某人說明結果為何如此產生，而渲染出相同的步驟清單。每個記錄的步驟都帶有子運算式文字及其化簡後的值

化簡引擎如何遍歷運算式

追蹤器並不深入計算引擎。它將公式進行記號化並用遞迴下降解析器解析，然後以深度優先、由最內層可求值的子運算式開始化簡語法樹。當一個節點化簡為一個值時，該值以字面量的形式代入周圍的運算式，引擎再要求真正的計算器重新計算現在更簡單的運算式。由於每個步驟都是透過工作表的公開 Calculate 方法而非私有捷徑來求值，每個步驟的結果都與對該儲存格進行完整重新計算所得的結果完全一致。解析器在設計上是非侵入式的，這讓它能在不打擾工作表狀態的情況下對任何工作表執行

解析器遵循運算子優先級階梯，每個優先級帶各有一個遞迴層級。從最低繫結到最高，各帶依次為：第 0 級比較（=、<>、<、>、<=、>=），第 1 級字串連結（&），第 2 級加法和減法，第 3 級乘法和除法，第 4 級指數運算，最後是一元正號和負號。每個層級以上一層級解析其運算元，因此較高的帶繫結更緊。這與 Excel 套用的優先級相同，這就是為什麼 A1*B1+A2*B1 在求和之前先化簡兩個乘積：乘法在第 3 級，加法在第 2 級，所以乘法在樹中更深，先化簡

追蹤公式並遍歷步驟

用法與 Demo/Delphi/FormulaTrace/FormulaTrace.dpr 中附帶的範例一致。建立一個工作表（或開啟現有活頁簿），在工作表上建構一個追蹤器，呼叫 Trace，並遍歷傳回的陣列。每個 TXLSFormulaStep 提供了用於縮排的 Depth、原始子運算式的 Source、已代入運算元的子運算式的 Expression，以及步驟結果的 Value

uses
  SysUtils, Variants, lxHandle, lxHandleX, lxFormulaTrace;

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Tracer: TXLSFormulaTracer;
  Steps: TXLSFormulaStepArray;
  Final: Variant;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Order');
    Sheet.Cells[1, 1].Value := 10;    // A1 units
    Sheet.Cells[1, 2].Value := 25;    // B1 unit price
    Sheet.Cells[1, 3].Value := 0.08;  // C1 tax rate

    Tracer := TXLSFormulaTracer.Create(Sheet);
    try
      Final := Tracer.Trace('A1*B1*(1+C1)', Steps);
      for I := 0 to High(Steps) do
        Writeln(StringOfChar(' ', Steps[I].Depth * 2),
                Steps[I].Source, ' -> ', Steps[I].Expression,
                ' = ', VarToStr(Steps[I].Value));
      Writeln('result = ', VarToStr(Final));
    finally
      Tracer.Free;
    end;
  finally
    Book.Free;
  end;
end;

儲存格參照首先被解析並顯示為各自的步驟，然後乘積化簡，再是括弧中的稅率因子，最後是最終的乘法。Depth 欄位讓您縮排，使最內層的化簡明顯地位於最深處，恰如 Excel 在任何外層之前先給最內層的項目加底線

不依賴地區設定的字面量陷阱

整個方案中最危險的細節在英文機器上是看不見的，卻會在德文機器上大聲出錯。當一個計算出的數字被代回公式文字時，它必須被寫成字串，然後再由計算引擎重新解析，而計算引擎將 . 視為小數點。如果代入使用了系統地區設定，德文的 TFormatSettings 會把稅率因子寫成 1,08，逗號會被讀作引數分隔符號，而 A1*B1*1,08 的重新計算要麼被解析成錯誤的形狀，要麼完全失敗

追蹤器透過一個在建構時固定的私有 TFormatSettings 來格式化每個數字字面量，其中 DecimalSeparator 被強制設為 .，ThousandSeparator 設為 #0，這樣就不會發出任何分組字元，從而避免了這個問題。FloatToStr 產生一個引擎無論在任何地區設定下都能讀回的字面量

// Conceptually what the tracer pins once, at construction
FFloatFmt := FormatSettings;
FFloatFmt.DecimalSeparator := '.';
FFloatFmt.ThousandSeparator := #0;
// every reduced number is written with: FloatToStr(Double(V), FFloatFmt)

這種錯誤從不出現在開發者自己的測試中，只在另一個地區設定的客戶執行相同程式碼時才浮現，因此值得明確說明：將值透過公式文字往返是一個序列化問題，而序列化必須與地區設定無關

布林值化簡為 1 和 0

另一個相關的代入決策涉及邏輯值。當一個子運算式求值為布林值時，追蹤器將其寫回為 1 或 0，而非 TRUE 或 FALSE。原因是化簡後的字面量必須能在它周圍的任何上下文中乾淨地重新解析，而算術是最苛刻的情況。如果 A1>A2 這樣的比較化簡為文字 TRUE，且該文字落在 TRUE*B1 中，重新計算將取決於引擎是否接受乘法位置中的裸布林關鍵字。代入 1 完全繞過了這個問題，因為 1*B1 在任何算術位置都是無歧義的。這也符合 Excel 自身的強制轉換 - - 一旦期望數字，TRUE 的行為就如同 1，FALSE 如同 0

函式呼叫以原子方式化簡

一個簡單的步驟引擎會先化簡函式的引數，再化簡呼叫本身。這對 Excel 來說是錯誤的，追蹤器刻意不這樣做。函式呼叫是從其原始文字整體求值的，在單一步驟中完成。原因是短路語意。IF、CHOOSE 和 IFERROR 只求值它們選擇的分支，而先化簡引數會強制引擎計算 Excel 從不觸碰的分支。典型的犧牲品是除以零的防護，例如 IF(B1=0,0,A1/B1)：如果追蹤器在求值 IF 之前先化簡 A1/B1，防護就會失效並引發它本應防止的錯誤。透過以原子方式求值整個呼叫，追蹤器保留了使此類防護生效的惰性求值特性

// IF is one atomic step; only the selected branch is evaluated
Final := Tracer.Trace('IF(A1>A2,A1*B1,A2*B1)', Steps);
// A1>A2 is true, so the step records A1*B1 as the chosen result;
// A2*B1 is never computed, exactly as Excel would do it.

取捨之處在於您看不到函式呼叫內部作為獨立步驟，但這是正確的行為。顯示 Excel 從未執行的引數化簡，比將呼叫視為它真正所是的單一求值單元，反而更具誤導性

引數分隔符號與完整範圍

還有兩個正規化保持了重新計算的正確性。計算引擎的編譯器期望 ; 作為函式引數分隔符號，因此當追蹤器從解析樹重建函式呼叫時，它用 ; 連接引數，即使使用者最初輸入的是 ,。寫成 SUM(A1,A2,A3) 的公式以 SUM(A1;A2;A3) 重新計算，引擎可以接受。值的代入使這種重建成為必要，而正確使用分隔符號使重建得以解析

範圍參照是另一種情況。A1:A3 這樣的範圍不是純量，不能被拆分為三個獨立的值，因為使用它的函式期望一個範圍引數。追蹤器將範圍以其原始文字完整保留，讓外圍的函式作為整體化簡。在 SUM(A1:A3)*B1 中，範圍保持完整，SUM(A1:A3) 在一個原子步驟中化簡為一個數字，然後外部的乘法才執行。這是 Excel 在範圍運算元和它最終貢獻的純量之間所劃的同一條界線

// The range A1:A3 is never split; SUM is one atomic reduction,
// then the product with B1 reduces on top of it.
Final := Tracer.Trace('SUM(A1:A3)*B1', Steps);
for I := 0 to High(Steps) do
  Writeln(Steps[I].Source, ' = ', VarToStr(Steps[I].Value));

綜合起來，這些規則使步驟清單成為 Excel「評估公式」命令的忠實鏡像，而非其近似。化簡以 Excel 執行的順序發生，代入的字面量在任何地區設定下都能存活，布林值的強制轉換方式與 Excel 相同，惰性函式保持惰性。若要使用自訂函式進一步延伸引擎，公式引擎和自訂函式文章說明了如何登錄它們；對於更重的數值運算，Delphi 中的統計分布函式文章涵蓋了追蹤器所求值的內建函式庫。這一切都作為 HotXLS 試算表元件的一部分提供，適用於 Delphi 和 C++Builder，並附有本部落格其他地方介紹的讀取、寫入、格式化和計算 API