一份 Delphi 生成的工作簿里有 =GAMMA(5) 和 =XIRR(B2:B9,A2:A9),Excel 打开时 GAMMA 单元格是 #NAME?,XIRR 单元格却结果正确。HotXLS 中,凡是在 XLSX 里带 _xlfn. 前缀、或在 BIFF8 .xls 里走名称调用的函数,都是 Excel 自己也这么保存的——这份清单与 MS-XLSX 语法有出入
为什么 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 清单里 | 裸写 |
从语法生成的写入器因此会产出不带前缀的 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 转义:" & <
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 对这类工作簿返回标记为 unsupported 的 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 component 的 Cell.Formula 赋进去,XLSX 拿到 Excel 期望的前缀,工作簿存成 .xls 则拿到隐藏名称和函数 255 调用