技術文章

新 Excel 函式的 _xlfn 前綴與 BIFF8 名稱呼叫

Delphi 產生的工作簿裡放著 =GAMMA(5) 與 =XIRR(B2:B9,A2:A9),Excel 開啟後 GAMMA 那格是 #NAME?,XIRR 那格卻算出正確結果。在 HotXLS 裡,XLSX 中帶 _xlfn. 前綴、或在 BIFF8 .xls 裡以名稱呼叫的函式,正是 Excel 自己也那麼存的那批——這份清單與 MS-XLSX 文法並不一致

為什麼 Excel 對 GAMMA 顯示 #NAME?,對 XIRR 卻不會?

公式文字沒錯,錯的是儲存方式。XLSX 工作表裡的公式是 <f> 元素裡的純文字,Excel 2007 之後新增的函式寫成 _xlfn.NAME。前綴告訴讀取器「這是你可能還不認得的內建函式」,所以舊版 Excel 顯示 #NAME? 卻不會把呼叫誤當使用者定義名稱,而新版 Excel 顯示時剝掉前綴,使用者看到的就是 =GAMMA(5)。少了它,新版 Excel 會去找叫 GAMMA 的定義名稱,找不到,於是顯示 #NAME?。XIRR 年紀更大:它隨 Analysis ToolPak 問世,Excel 裸寫它,帶前綴的 _xlfn.XIRR 是另一個記號,與 Excel 產出的不同。GAMMA、GAUSS、PHI、SHEET、SHEETS 與 XOR 都需要前綴,Excel 2013 加進工程與複數函式集的八個函式(IMCOSH、IMCOT、IMCSC、IMCSCH、IMSEC、IMSECH、IMSINH 與 IMTAN)也是,而更老的 IMSIN、IMSQRT 與 IMSUM 保持裸寫。僅限工作表的陣列函式還有第二個前綴 _xlfn._xlws.,MS-XLSX §2.2.2 為 FILTER、SORT 與 PY 保留了它。讀取時 HotXLS 兩種前綴都剝掉,Cell.Formula 回傳乾淨的函式名

HotXLS for Delphi 中新 Excel 函式的儲存路線:GAMMA 帶 _xlfn 前綴,ToolPak 的 XIRR 則裸寫
前綴標記讀取器可能還不認得的內建函式,新版 Excel 會剝掉它,舊版則以名稱錯誤回應

哪些函式 Excel 加前綴,而 MS-XLSX 文法卻讓它裸著?

MS-XLSX §2.2.2 用 ABNF 寫出清單:future-function-list = ("_xlfn." ("AGGREGATE" / "ACOT" / … / "Z.TEST")) / "ECMA.CEILING" / "ISO.CEILING" / "NETWORKDAYS.INTL" / "WORKDAY.INTL" / "FORECAST.ETS" / …。照字面讀,Z.TEST 後面的右括號結束了帶前綴那組,其後的一切都是裸名稱。第二條產生規則——普通內建函式清單——把 CONCAT、TEXTJOIN、IFS、MAXIFS、MINIFS 與 SWITCH 和 SUM、VLOOKUP 並列。Excel 在這兩處都不買帳:

函式MS-XLSX 文法把它放哪Excel 寫什麼
NETWORKDAYS.INTL、WORKDAY.INTL排在 _xlfn. 那組之後,所以裸寫_xlfn.NETWORKDAYS.INTL、_xlfn.WORKDAY.INTL
ISO.CEILING排在 _xlfn. 那組之後,所以裸寫_xlfn.ISO.CEILING
FORECAST.LINEAR 與 FORECAST.ETS 家族排在 _xlfn. 那組之後,所以裸寫加前綴,例如 _xlfn.FORECAST.LINEAR
CONCAT、TEXTJOIN、IFS、MAXIFS、MINIFS、SWITCH普通內建函式清單加前綴,例如 _xlfn.TEXTJOIN
XIRR、XNPV、ERFC不在 future-function list 裡裸寫

按文法產生的寫入器因此會寫出不帶前綴的 NETWORKDAYS.INTL(…),檔案對得上公布的語法,Excel 卻回敬一個 #NAME?。清單請從存好的 Excel 檔案取,ABNF 當起點就好。HotXLS 對它計算的每個函式都這麼做,所以帶前綴的 CONCAT 與帶前綴的 NETWORKDAYS.INTL 都按 Excel 的寫法輸出。下面的程式碼把同一類公式兩種格式各存一次,再讀回一次:

uses
  SysUtils, lxCalc, lxHandle, lxHandleX;

procedure SaveModernFunctions;
var
  XBook, XCheck: TXLSXWorkbook;
  XSheet: TXLSXWorksheet;
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  // XLSX:前綴在儲存時加上、載入時剝除
  XBook := TXLSXWorkbook.Create;
  try
    XSheet := XBook.Sheets.Add('Calc');
    XSheet.Cells[1, 1].Value := 46000.0;                // 日期序列值
    XSheet.Cells[1, 2].Formula := 'EDATE(A1,1)';       // ToolPak:無前綴
    XSheet.Cells[1, 3].Formula := 'GAMMA(5)';          // _xlfn.GAMMA
    XSheet.Cells[1, 4].Formula := 'NETWORKDAYS.INTL(A1,A1+30,11)';
    XSheet.Cells[1, 5].Formula := 'TEXTJOIN("-",TRUE,B1:D1)';
    if XBook.SaveAs('modern.xlsx', xlsxOpenXMLWorkbook) <> lxOk then
      raise Exception.Create('XLSX save failed');
  finally
    XBook.Free;
  end;

  XCheck := TXLSXWorkbook.Create;
  try
    if XCheck.Open('modern.xlsx') <> lxOk then
      raise Exception.Create('XLSX open failed');
    // 預期:NETWORKDAYS.INTL(A1,A1+30,11),前綴已剝除
    Writeln(string(XCheck.Sheets[1].Cells[1, 4].Formula));
  finally
    XCheck.Free;
  end;

  // BIFF8:同樣的呼叫變成名稱記號加 Ftab 第 255 項
  Book := TXLSWorkbook.Create;
  try
    Sheet := Book.Sheets.Add;
    Sheet.Cells[1, 1].Value := 46000.0;
    Sheet.Cells[1, 2].Formula := '=EDATE(A1,1)';       // 增益集 EXTERNNAME
    Sheet.Cells[1, 3].Formula := '=ISOWEEKNUM(A1)';    // 隱藏的 _xlfn NAME
    if Book.SaveAs('modern.xls') <> lxOk then          // 預設 BIFF8
      raise Exception.Create('XLS save failed');
  finally
    Book.Free;
  end;
end;

兩個檔案在新版 Excel 裡都應完整開啟、每個函式無恙。想親眼看前綴,把套件當 ZIP 打開,列出工作表部件的原始 <f> 文字;對 modern.xlsx,下面的常式應印出 EDATE(A1,1)、_xlfn.GAMMA(5)、_xlfn.NETWORKDAYS.INTL(A1,A1+30,11) 與 _xlfn.TEXTJOIN(&quot;-&quot;,TRUE,B1:D1)。引號顯示成 &quot; 是因為常式直接印 XML 文字、沒有還原跳脫,對診斷無妨。它對任何產生器產的檔案都適用,所以當第三方工作簿在某個您的 Excel 明明支援的函式上顯示 #NAME?,這是最快的檢查

uses
  SysUtils, Classes, Zip;

// 印出一個工作表部件的每個 <f> 元素,含前綴
procedure DumpRawFormulas(const XlsxFile: string;
  const Part: string = 'xl/worksheets/sheet1.xml');
var
  Zip: TZipFile;
  Bytes: TBytes;
  Xml: string;
  P, StartTag, Close: Integer;
begin
  Zip := TZipFile.Create;
  try
    Zip.Open(XlsxFile, zmRead);
    Zip.Read(Part, Bytes);
  finally
    Zip.Free;
  end;
  Xml := TEncoding.UTF8.GetString(Bytes);
  P := 1;
  while True do
  begin
    StartTag := Pos('<f', Xml, P);
    if StartTag = 0 then
      Break;
    P := StartTag + 2;
    if not CharInSet(Xml[P], ['>', ' ', '/']) then
      Continue;                                // <formula>、<font>:不是儲存格公式
    P := Pos('>', Xml, P) + 1;               // 跳過 t="shared" 這類屬性
    if Xml[P - 2] = '/' then
      Continue;                                // 共用公式的空 <f .../>
    Close := Pos('</f>', Xml, P);
    if Close = 0 then
      Break;
    Writeln(Copy(Xml, P, Close - P));          // XML-escaped: &quot; &amp; &lt;
    P := Close + 4;
  end;
end;

BIFF8 .xls 檔怎麼呼叫一個自己沒有編號的函式?

BIFF8 公式裡根本沒有函式名。每個內建函式是固定函式表(Ftab)裡的一個號碼,公式以帶號碼的 ptgFunc 或 ptgFuncVar 儲存。更新的函式沒有號碼。MS-XLS 把這類呼叫經 Ftab 第 0x00FF 項「User Defined Function」轉手:一個函式索引 255 的 ptgFuncVar,其第一個引數是指向真正函式的名稱記號。記號裡存的引數數比使用者輸入的多一,因為那個打頭的名稱也算一個引數。Excel 寫哪種名稱記號,取決於函式所屬的家族:

函式家族名稱記號名稱指向什麼
Analysis ToolPak 函式:EDATE、EOMONTH、WORKDAY、NETWORKDAYS、RANDBETWEEN、XIRR …ptgNameX工作簿增益集 SUPBOOK 裡的 EXTERNNAME 記錄
Excel 以 _xlfn. 寫出的函式:XLOOKUP、ISOWEEKNUM、IFS、CONCAT …ptgName名為 _xlfn.XLOOKUP(或 _xlfn._xlws.FILTER)、定義為 #NAME? 的隱藏 NAME 記錄
儲存檔案的 Excel 不認得的函式ptgName定義為空的普通工作簿 NAME 記錄

第一列的增益集 SUPBOOK,正是讀取器整理外部參照時會碰到的那個記錄,BIFF SUPBOOK 記錄如何分類外部參照說明了它在各類之間的位置。不是每個像增益集的東西都走這條路:DATEDIF、USDOLLAR、FINDB、ISPMT 等有原生的 Ftab 號碼,以普通函式呼叫儲存。第三列最脆弱:對存檔的 Excel 太新的函式,例如 REGEXTEST 與 REGEXEXTRACT,會變成經無定義的普通名稱發出的呼叫。讀取器若把空定義名稱當垃圾丟掉,公式就指向一個不存在的名稱索引,Excel 也會拒開它寫回的整個檔案。HotXLS 保留這種名稱、定義給 #NAME?,儲存時把原始記錄原樣再寫一次

HotXLS for Delphi 中 BIFF8 名稱呼叫的路線:ToolPak 函式走 ptgNameX,_xlfn 函式經隱藏名稱記錄與 255 號函式呼叫
.xls 檔裡的公式存的是號碼,新函式只能借用使用者定義的槽位,指向一個必須挺過往返的名稱記錄

每個名稱都必須在第一張工作表之前存在

順序是下一道限制。BIFF8 檔案在任何工作表子流之前先寫全域——NAME 表與 EXTERNSHEET——所以公式將指向的每個名稱,都得在第一個儲存格寫出之前就緒。走到第三張工作表一半才碰上 XLOOKUP 的寫入器,再也補不進它需要的那個隱藏名稱。若您自己產 BIFF8,寫全域之前先掃過每一個公式來源:儲存格公式、工作簿與工作表層級名稱、資料驗證規則與條件格式。大小由記號決定:名稱呼叫在引數之外多花 9 位元組(ptgName 5、ptgFuncVar 4),增益集呼叫多花 11(ptgNameX 7、ptgFuncVar 4);寫出前算的記錄長度,必須與之後寫出的位元組選同一種記號。HotXLS 在寫任何東西之前,一趟就把這些名稱全部解析完

HotXLS for Delphi 中 BIFF8 檔案的寫出順序:全域裡的名稱記錄先於每張工作表,公式來源得先掃描
第三張工作表半路才遇到的名稱擠不進全域,所以第一個位元組寫出之前,每個公式來源都先掃過

運算元類別改變 Excel 算出的結果

BIFF8 公式裡的每個引數都標記為參照、值或陣列運算元,Excel 對標記照單全收。把 AGGREGATE 的第三個引數標成值,Excel 回傳 #VALUE!;把 SUM(SORTBY(…)) 的內層結果標成值,Excel 插入隱式交集、顯示 SUM(@SORTBY(…)),只加總一個儲存格。寫入器得照 Excel 的方式標這些引數,而那未必是函式文件暗示的類別:增益集函式的引數寫成參照,動態陣列函式回傳陣列,XLOOKUP、TAKE、DROP、IFS 與 SWITCH 在期待參照的位置上回傳參照。類別標錯不會弄壞檔案,卻會改變答案

VBA 函式讓工作簿保持 XLSB 二進位

XLSB 補上第三種儲存情況。巨集工作簿可以從儲存格呼叫自己的 VBA Function 程序,二進位格式把每個程序宣告成一個旗標為 procedure、公式為空的名稱;儲存格再經與 BIFF8 相同的 ptgName 加 255 號函式模式呼叫它。XML 格式沒有對等物:空的定義名稱不合法,而定義成 #NAME? 的名稱會蓋住同名的 VBA 函式。HotXLS 把這種工作簿原樣存回 XLSB、宣告不動,並在寫出任何位元組之前就拒絕轉成其他格式。轉檔前先要轉換報告,別等儲存失敗才看:TXLSXWorkbook.GetConversionReport 會回傳標記不支援的 VBAFunctionNames 項目,程式碼可以據此分流

uses
  SysUtils, lxCalc, lxDiagnostics, lxHandleX;

procedure ConvertOrKeepBinary(const XlsbFile: string);
var
  Book: TXLSXWorkbook;
  Report: TXLSConversionReport;
  I: Integer;
  Blocked: Boolean;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(XlsbFile) <> lxOk then
      raise Exception.Create('Open failed');
    Report := Book.GetConversionReport(xlsConversionOpenXmlWorkbook);
    try
      Blocked := False;
      for I := 0 to Report.Count - 1 do
        if Report[I].Fidelity = xlsConversionUnsupported then
        begin
          Blocked := True;
          Writeln(string(Report[I].Feature), ': ', string(Report[I].Message));
        end;
    finally
      Report.Free;
    end;
    if Blocked then                               // 例如 VBAFunctionNames
      Book.SaveAs(ChangeFileExt(XlsbFile, '.edited.xlsb'),
        xlsxExcelBinaryWorkbook)
    else
      Book.SaveAs(ChangeFileExt(XlsbFile, '.xlsx'), xlsxOpenXMLWorkbook);
  finally
    Book.Free;
  end;
end;

別家工具產的工作簿在某個 Excel 明明認得的函式上顯示 #NAME? 時,先什麼都別動,對它跑一次 DumpRawFormulas:<f> 文字裡一個裸的 NETWORKDAYS.INTL 或 TEXTJOIN,診斷就齊了。把同一段公式文字經 HotXLS Delphi Excel 元件的 Cell.Formula 指派進去,XLSX 就會得到 Excel 期待的前綴;工作簿存成 .xls 時則得到隱藏名稱與 255 號函式呼叫