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 回傳乾淨的函式名
哪些函式 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("-",TRUE,B1:D1)。引號顯示成 " 是因為常式直接印 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: " & <
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?,儲存時把原始記錄原樣再寫一次
每個名稱都必須在第一張工作表之前存在
順序是下一道限制。BIFF8 檔案在任何工作表子流之前先寫全域——NAME 表與 EXTERNSHEET——所以公式將指向的每個名稱,都得在第一個儲存格寫出之前就緒。走到第三張工作表一半才碰上 XLOOKUP 的寫入器,再也補不進它需要的那個隱藏名稱。若您自己產 BIFF8,寫全域之前先掃過每一個公式來源:儲存格公式、工作簿與工作表層級名稱、資料驗證規則與條件格式。大小由記號決定:名稱呼叫在引數之外多花 9 位元組(ptgName 5、ptgFuncVar 4),增益集呼叫多花 11(ptgNameX 7、ptgFuncVar 4);寫出前算的記錄長度,必須與之後寫出的位元組選同一種記號。HotXLS 在寫任何東西之前,一趟就把這些名稱全部解析完
運算元類別改變 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 號函式呼叫