Artículo técnico

Prefijos _xlfn y llamadas BIFF8 en funciones nuevas

Un libro generado desde 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 basada en nombre en BIFF8 .xls, son las que Excel guarda así — una lista que se desvía de la gramática 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 añadida después de Excel 2007 se escribe como _xlfn.NAME. El prefijo dice al lector «esta es una función integrada que quizá aún no conoces», 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 mostrarla para que los usuarios vean =GAMMA(5). Déjalo fuera y el Excel actual va a buscar un nombre definido llamado GAMMA, no encuentra ninguno y muestra #NAME?. XIRR es más vieja. Vino con el Analysis ToolPak, Excel la escribe desnuda, y un _xlfn.XIRR con prefijo es un token distinto del que Excel produce. GAMMA, GAUSS, PHI, SHEET, SHEETS y XOR necesitan todas el prefijo, y también las ocho funciones que Excel 2013 añadió al conjunto 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 desnudas. Las funciones de array solo de hoja llevan un segundo prefijo, _xlfn._xlws., que el 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 de funciones nuevas de Excel en HotXLS para Delphi: GAMMA lleva el prefijo _xlfn mientras XIRR de ToolPak se queda desnuda
el prefijo marca una integrada que el lector quizá aún no conoce, así que el Excel actual lo quita y las versiones viejas responden con un error de nombre

¿Qué funciones prefija Excel que la gramática MS-XLSX deja desnudas?

El MS-XLSX §2.2.2 da 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 literalmente, el paréntesis de cierre después de Z.TEST termina el grupo con prefijo, así que todo lo que va detrás es un nombre desnudo. 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 escribe Excel
NETWORKDAYS.INTL, WORKDAY.INTLDetrás del grupo _xlfn., o sea desnuda_xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL
ISO.CEILINGDetrás del grupo _xlfn., o sea desnuda_xlfn.ISO.CEILING
FORECAST.LINEAR y la familia FORECAST.ETSDetrás del grupo _xlfn., o sea desnudaCon prefijo, por ejemplo _xlfn.FORECAST.LINEAR
CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCHLista ordinaria de funciones integradasCon prefijo, por ejemplo _xlfn.TEXTJOIN
XIRR, XNPV, ERFCNo está en la lista de funciones futurasDesnuda

Un writer generado desde la gramática produce por tanto NETWORKDAYS.INTL(…) sin prefijo, y Excel responde con #NAME? en un archivo que valida contra la sintaxis publicada. Toma la lista de archivos de Excel guardados y trata la 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 añaden al guardar y se quitan al cargar
  XBook := TXLSXWorkbook.Create;
  try
    XSheet := XBook.Sheets.Add('Calc');
    XSheet.Cells[1, 1].Value := 46000.0;                // un número de serie 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 convierten en tokens de nombre más la entrada 255 del 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 defecto
      raise Exception.Create('XLS save failed');
  finally
    Book.Free;
  end;
end;

Ambos archivos deberían abrirse en el Excel actual con todas las funciones intactas. Para ver los prefijos, abre el paquete como ZIP y lista el texto <f> crudo de la parte de hoja de cálculo; para modern.xlsx la rutina de abajo debería 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 con archivos de cualquier productor, así que es la comprobación más rápida cuando un libro de terceros muestra #NAME? en una función que tu copia de Excel 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;               // 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 función en absoluto. 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. El MS-XLS enruta esas llamadas por la entrada 0x00FF del 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 recuento de argumentos guardado en el token es uno más de lo que el usuario tecleó, porque ese nombre cabecero 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-in 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 una definición vacía

El SUPBOOK de add-in de la primera fila es el mismo registro que un lector encuentra cuando ordena referencias externas, y la forma en que los registros SUPBOOK de BIFF clasifican referencias externas muestra dónde se sienta entre las demás clases. No todo lo que parece un add-in va por aquí. DATEDIF, USDOLLAR, FINDB e ISPMT, entre otros, tienen números Ftab nativos y se almacenan como llamadas de función planas. La tercera fila es la frágil: una función demasiado nueva para el Excel que guardó el archivo, como REGEXTEST y REGEXEXTRACT, se convierte en una llamada por un nombre ordinario sin definición. Un lector que tira 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 todo el archivo que devuelve a escribir. HotXLS conserva tal nombre con una definición #NAME? y vuelve a escribir el registro original al guardar

Rutas de name call BIFF8 en HotXLS para Delphi: las funciones ToolPak van sobre 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 user-defined y apunta a un registro de nombre que debe sobrevivir al viaje de ida y vuelta

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 subflujo de hoja, así que todo nombre al que una fórmula vaya a apuntar debe conocerse antes de escribir la primera celda. Un writer que se encuentra con XLOOKUP a mitad de la hoja tres ya no puede añadir el nombre oculto que necesita. Si produces BIFF8 por tu cuenta, escanea toda fuente de fórmulas antes de escribir los globals: fórmulas de celda, nombres de nivel de libro y de hoja, reglas de validación de datos y formato condicional. Los tamaños se derivan de los tokens. Una llamada por nombre cuesta 9 bytes encima de los argumentos (5 por el ptgName, 4 por el ptgFuncVar) y una llamada de add-in 11 (7 por el ptgNameX, 4 por el ptgFuncVar), y las longitudes de registro calculadas antes de escribir tienen que usar la misma elección de token que los bytes escritos después. HotXLS resuelve todos estos nombres en una sola pasada antes de escribir nada

Orden de escritura de un archivo BIFF8 en HotXLS para Delphi: los registros de nombre en los globals preceden a toda hoja, así que las fuentes de fórmulas se escanean primero
un nombre encontrado a mitad de la hoja tres no puede unirse a los globals, así que toda 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 array, y Excel se toma la etiqueta al pie de la letra. Etiqueta el tercer argumento de AGGREGATE como valor y Excel devuelve #VALUE!. Etiqueta el resultado interior 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 dynamic-array devuelven arrays, y XLOOKUP, TAKE, DROP, IFS y SWITCH devuelven referencias cuando se sientan donde se espera una referencia. Las clases equivocadas no rompen el archivo. Cambian la respuesta

Las funciones VBA mantienen un libro binario XLSB

XLSB añade un tercer caso de almacenamiento. Un libro de macros puede llamar a sus propias procedures VBA Function desde celdas, y el formato binario declara cada una como un nombre marcado como procedure con una fórmula vacía; la celda luego la llama por el mismo patrón 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 tal libro de vuelta a XLSB con las declaraciones intactas, y se niega a la conversión a cualquier otro formato antes de escribir un byte. Pide el informe 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 por 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, ejecuta DumpRawFormulas sobre él antes que nada: un NETWORKDAYS.INTL o TEXTJOIN desnudo en el texto <f> es todo el diagnóstico. Asigna ese mismo texto de fórmula por Cell.Formula en el componente Excel de 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