技术文章

新 Excel 函数的 _xlfn 前缀与 BIFF8 名称调用

一份 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 返回干净的函数名

Delphi 中 HotXLS 存储新 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 清单里裸写

从语法生成的写入器因此会产出不带前缀的 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 转义:&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? 定义,保存时原样再写出该记录

Delphi 中 HotXLS 的 BIFF8 名称调用路线:ToolPak 函数走 ptgNameX,_xlfn 函数经隐藏名称记录和函数 255 调用
.xls 文件里的公式只存编号,新函数借用户定义槽位并指向一条必须挺过往返的名称记录

每个名称都得在第一个工作表之前存在

顺序是下一个约束。BIFF8 文件先写全局——NAME 表和 EXTERNSHEET——再写任何工作表子流,所以公式将指向的每个名称都必须在第一个单元格写出之前就已知。写到第三张工作表半途才碰到 XLOOKUP 的写入器,已经来不及补上所需的隐藏名称。自己产 BIFF8 的话,写全局之前扫遍每一个公式来源:单元格公式、工作簿和工作表级名称、数据验证规则、条件格式。尺寸由记号决定:名称调用在参数之外花 9 字节(ptgName 5 字节、ptgFuncVar 4 字节),加载项调用 11 字节(ptgNameX 7 字节、ptgFuncVar 4 字节);写出前计算的记录长度必须与之后写出的字节采用同一记号选择。HotXLS 在写出任何东西之前一趟解析完所有这些名称

Delphi 中 HotXLS 写 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 对这类工作簿返回标记为 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 调用