Artículo técnico

_xlfn y llamadas BIFF8 para funciones nuevas de Excel

Un libro generado en Delphi guarda =GAMMA(5) y =XIRR(B2:B9,A2:A9), y Excel lo abre con #NAME? en la celda de GAMMA y un resultado correcto en la celda de XIRR. En HotXLS, las funciones que llevan un prefijo _xlfn. en XLSX, o una llamada por nombre en BIFF8 .xls, son justo las que Excel guarda de esa forma, una lista que se aparta de la gramática de MS-XLSX

¿Por qué Excel muestra #NAME? para GAMMA pero no para XIRR?

El texto de la fórmula está bien; el almacenamiento no. Dentro de una hoja XLSX una fórmula es texto plano en un elemento <f>, y una función agregada después de Excel 2007 se escribe como _xlfn.NAME. El prefijo le dice al lector "this is a built-in function you may not know yet", así que un Excel viejo muestra #NAME? sin confundir la llamada con un nombre definido por el usuario, y el Excel actual quita el prefijo al mostrar, de modo que los usuarios ven =GAMMA(5). Déjelo fuera y el Excel actual sale a buscar un nombre definido llamado GAMMA, no encuentra ninguno, y muestra #NAME?. XIRR es más vieja. Llegó con el Analysis ToolPak, Excel la escribe sin prefijo, y un _xlfn.XIRR con prefijo es un token distinto del que Excel produce. GAMMA, GAUSS, PHI, SHEET, SHEETS y XOR todas necesitan el prefijo, y también las ocho funciones que Excel 2013 agregó al set de ingeniería y números complejos (IMCOSH, IMCOT, IMCSC, IMCSCH, IMSEC, IMSECH, IMSINH e IMTAN), mientras que las más viejas IMSIN, IMSQRT e IMSUM se quedan sin prefijo. Las funciones de arreglo exclusivas de hoja llevan un segundo prefijo, _xlfn._xlws., que MS-XLSX §2.2.2 reserva para FILTER, SORT y PY. Al leer, HotXLS quita ambos prefijos, así que Cell.Formula devuelve el nombre limpio de la función

Rutas de almacenamiento para funciones nuevas de Excel en HotXLS para Delphi: GAMMA lleva el prefijo _xlfn mientras la XIRR del ToolPak se queda sin prefijo
el prefijo marca una función integrada que el lector quizá no conozca aún, así que el Excel actual lo quita y las versiones viejas responden con un error de nombre

¿Qué funciones llevan prefijo en Excel y no en la gramática MS-XLSX?

MS-XLSX §2.2.2 deletrea la lista en ABNF como future-function-list = ("_xlfn." ("AGGREGATE" / "ACOT" / … / "Z.TEST")) / "ECMA.CEILING" / "ISO.CEILING" / "NETWORKDAYS.INTL" / "WORKDAY.INTL" / "FORECAST.ETS" / …. Leída al pie de la letra, el paréntesis de cierre después de Z.TEST termina el grupo con prefijo, así que todo lo que viene después es un nombre sin prefijo. Una segunda producción, la lista ordinaria de funciones integradas, incluye CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS y SWITCH junto a SUM y VLOOKUP. Excel discrepa en ambos puntos:

FunciónDónde la pone la gramática MS-XLSXLo que Excel escribe
NETWORKDAYS.INTL, WORKDAY.INTLDespués del grupo _xlfn., o sea sin prefijo_xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL
ISO.CEILINGDespués del grupo _xlfn., o sea sin prefijo_xlfn.ISO.CEILING
FORECAST.LINEAR y la familia FORECAST.ETSDespués del grupo _xlfn., o sea sin prefijoCon prefijo, por ejemplo _xlfn.FORECAST.LINEAR
CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCHLista ordinaria de funciones integradasCon prefijo, por ejemplo _xlfn.TEXTJOIN
XIRR, XNPV, ERFCFuera de la lista future-functionSin prefijo

Un writer generado desde la gramática produce entonces NETWORKDAYS.INTL(…) sin prefijo, y Excel responde con #NAME? en un archivo que valida contra la sintaxis publicada. Tome la lista de archivos de Excel guardados y trate el ABNF como punto de partida. HotXLS lo hace para cada función que calcula, así que el CONCAT con prefijo y el NETWORKDAYS.INTL con prefijo salen como los escribe Excel. El siguiente código guarda el mismo tipo de fórmulas por ambas vías y lee una de vuelta:

uses
  SysUtils, lxCalc, lxHandle, lxHandleX;

procedure SaveModernFunctions;
var
  XBook, XCheck: TXLSXWorkbook;
  XSheet: TXLSXWorksheet;
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  // XLSX: los prefijos se agregan al guardar y se quitan al cargar
  XBook := TXLSXWorkbook.Create;
  try
    XSheet := XBook.Sheets.Add('Calc');
    XSheet.Cells[1, 1].Value := 46000.0;                // un serial de fecha
    XSheet.Cells[1, 2].Formula := 'EDATE(A1,1)';       // ToolPak: sin prefijo
    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');
    // Esperado: NETWORKDAYS.INTL(A1,A1+30,11), prefijo ya quitado
    Writeln(string(XCheck.Sheets[1].Cells[1, 4].Formula));
  finally
    XCheck.Free;
  end;

  // BIFF8: las mismas llamadas se vuelven tokens de nombre más la entrada 255 de Ftab
  Book := TXLSWorkbook.Create;
  try
    Sheet := Book.Sheets.Add;
    Sheet.Cells[1, 1].Value := 46000.0;
    Sheet.Cells[1, 2].Formula := '=EDATE(A1,1)';       // EXTERNNAME de add-in
    Sheet.Cells[1, 3].Formula := '=ISOWEEKNUM(A1)';    // NAME _xlfn oculto
    if Book.SaveAs('modern.xls') <> lxOk then          // BIFF8 por default
      raise Exception.Create('XLS save failed');
  finally
    Book.Free;
  end;
end;

Ambos archivos deben abrir en el Excel actual con todas las funciones intactas. Para ver los prefijos, abra el paquete como ZIP y liste el texto crudo <f> de la parte de la hoja; para modern.xlsx la rutina de abajo debe imprimir EDATE(A1,1), _xlfn.GAMMA(5), _xlfn.NETWORKDAYS.INTL(A1,A1+30,11) y _xlfn.TEXTJOIN(&quot;-&quot;,TRUE,B1:D1). Las comillas salen como &quot; porque la rutina imprime texto XML sin desescaparlo, lo que es inofensivo para un diagnóstico. Funciona sobre archivos de cualquier productor, así que es la revisión más rápida cuando un libro de terceros muestra #NAME? en una función que su copia de Excel sí soporta

uses
  SysUtils, Classes, Zip;

// Imprime cada elemento <f> de una parte de hoja, prefijos incluidos
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>: no es una fórmula de celda
    P := Pos('>', Xml, P) + 1;               // se salta atributos como t="shared"
    if Xml[P - 2] = '/' then
      Continue;                                // <f .../> vacío de una fórmula compartida
    Close := Pos('</f>', Xml, P);
    if Close = 0 then
      Break;
    Writeln(Copy(Xml, P, Close - P));          // escapado XML: &quot; &amp; &lt;
    P := Close + 4;
  end;
end;

¿Cómo llama un archivo .xls BIFF8 a una función para la que no tiene número?

Las fórmulas BIFF8 no contienen nombres de funciones para nada. Cada función integrada es un número en una tabla de funciones fija (Ftab), y una fórmula guarda ptgFunc o ptgFuncVar con ese número. Las funciones más nuevas no tienen número. MS-XLS enruta esas llamadas por la entrada 0x00FF de Ftab, "User Defined Function": un ptgFuncVar con índice de función 255 cuyo primer argumento es un token de nombre que apunta a la función real. El conteo de argumentos guardado en el token es uno más de lo que el usuario tecleó, porque ese nombre inicial cuenta como argumento. Qué token de nombre escribe Excel depende de la familia de la función:

Familia de funcionesToken de nombreA qué apunta el nombre
Funciones del Analysis ToolPak: EDATE, EOMONTH, WORKDAY, NETWORKDAYS, RANDBETWEEN, XIRR …ptgNameXUn registro EXTERNNAME en el SUPBOOK de add-ins del libro
Funciones que Excel escribe con _xlfn.: XLOOKUP, ISOWEEKNUM, IFS, CONCAT …ptgNameUn registro NAME oculto llamado _xlfn.XLOOKUP (o _xlfn._xlws.FILTER) cuya definición es #NAME?
Funciones que la copia de Excel que guarda no reconoceptgNameUn registro NAME ordinario del libro con definición vacía

El SUPBOOK de add-ins de la primera fila es el mismo registro que un lector encuentra cuando clasifica referencias externas, y la forma en que los registros SUPBOOK de BIFF clasifican referencias externas muestra dónde vive entre las demás clases. No todo lo que parece un add-in va por este camino. DATEDIF, USDOLLAR, FINDB e ISPMT, entre otros, tienen números nativos de Ftab y se almacenan como llamadas de función simples. La tercera fila es la frágil: una función demasiado nueva para el Excel que guardó el archivo, como REGEXTEST y REGEXEXTRACT, se vuelve una llamada por un nombre ordinario sin definición. Un lector que descarta los nombres de definición vacía como basura deja la fórmula apuntando a un índice de nombre que ya no existe, y Excel se niega a abrir el archivo entero que devuelve escrito. HotXLS conserva ese nombre con definición #NAME? y vuelve a escribir el registro original al guardar

Rutas de llamada por nombre BIFF8 en HotXLS para Delphi: las funciones del ToolPak viajan en ptgNameX mientras las funciones _xlfn llaman por un registro de nombre oculto y la función 255
una fórmula en un archivo .xls guarda números, así que una función nueva toma prestada la ranura de definida-por-el-usuario y apunta a un registro de nombre que debe sobrevivir al round trip

Todo nombre tiene que existir antes de la primera hoja

El orden es la siguiente restricción. Un archivo BIFF8 escribe sus globals, la tabla NAME y EXTERNSHEET, antes de cualquier substream de hoja, así que todo nombre al que una fórmula vaya a apuntar debe ser conocido antes de escribir la primera celda. Un writer que se encuentra un XLOOKUP a media hoja tres ya no puede agregar el nombre oculto que necesita. Si usted produce BIFF8 por su cuenta, escanee cada fuente de fórmulas antes de escribir los globals: fórmulas de celda, nombres de libro y de hoja, reglas de validación de datos y formato condicional. Los tamaños se siguen de los tokens. Una llamada por nombre cuesta 9 bytes además de los argumentos (5 por ptgName, 4 por ptgFuncVar) y una llamada de add-in 11 (7 por ptgNameX, 4 por ptgFuncVar), y las longitudes de registro calculadas antes de escribir deben usar la misma elección de token que los bytes escritos después. HotXLS resuelve todos estos nombres en una pasada antes de escribir nada

Orden de escritura de un archivo BIFF8 en HotXLS para Delphi: los registros de nombre de los globals preceden a cada hoja, así que las fuentes de fórmulas se escanean primero
un nombre encontrado a media hoja tres no puede unirse a los globals, así que cada fuente de fórmulas se escanea antes de escribir el primer byte

Las clases de operando cambian lo que Excel calcula

Cada argumento en una fórmula BIFF8 va etiquetado como operando de referencia, valor o arreglo, y Excel se toma la etiqueta al pie de la letra. Etiquete el tercer argumento de AGGREGATE como valor y Excel devuelve #VALUE!. Etiquete el resultado interno de SUM(SORTBY(…)) como valor y Excel inserta una intersección implícita, muestra SUM(@SORTBY(…)) y suma una sola celda. Un writer tiene que etiquetar estos argumentos como lo hace Excel, que no siempre es la clase que sugiere la documentación de la función: los argumentos de funciones de add-in se escriben como referencias, las funciones de arreglo dinámico devuelven arreglos, y XLOOKUP, TAKE, DROP, IFS y SWITCH devuelven referencias cuando caen donde se espera una referencia. Las clases equivocadas no rompen el archivo. Cambian la respuesta

Las funciones VBA mantienen un libro XLSB binario

XLSB agrega un tercer caso de almacenamiento. Un libro con macros puede llamar desde las celdas a sus propios procedimientos Function de VBA, y el formato binario declara cada uno como un nombre marcado como procedimiento con fórmula vacía; la celda entonces lo llama por el mismo patrón de ptgName más función 255 que BIFF8. Los formatos XML no tienen equivalente. Un nombre definido vacío no es válido ahí, y un nombre definido como #NAME? escondería la función VBA del mismo nombre. HotXLS guarda ese libro de vuelta a XLSB con las declaraciones intactas, y se niega a convertirlo a cualquier otro formato antes de escribir un byte. Pida el reporte de conversión antes de convertir, no después de que un guardado falle: TXLSXWorkbook.GetConversionReport devuelve una entrada VBAFunctionNames marcada como no soportada para estos libros, y el código puede ramificar sobre ella

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                               // p. ej. VBAFunctionNames
      Book.SaveAs(ChangeFileExt(XlsbFile, '.edited.xlsb'),
        xlsxExcelBinaryWorkbook)
    else
      Book.SaveAs(ChangeFileExt(XlsbFile, '.xlsx'), xlsxOpenXMLWorkbook);
  finally
    Book.Free;
  end;
end;

Cuando un libro de otra herramienta muestra #NAME? en una función que Excel claramente conoce, corra DumpRawFormulas sobre él antes que nada: un NETWORKDAYS.INTL o TEXTJOIN sin prefijo en el texto <f> es todo el diagnóstico. Asigne ese mismo texto de fórmula por Cell.Formula en el componente Excel HotXLS para Delphi y el XLSX recibe el prefijo que Excel espera, mientras que un guardado .xls del libro recibe el nombre oculto y la llamada a la función 255