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
¿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ón | Dónde la pone la gramática MS-XLSX | Lo que escribe Excel |
|---|---|---|
| NETWORKDAYS.INTL, WORKDAY.INTL | Detrás del grupo _xlfn., o sea desnuda | _xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL |
| ISO.CEILING | Detrás del grupo _xlfn., o sea desnuda | _xlfn.ISO.CEILING |
| FORECAST.LINEAR y la familia FORECAST.ETS | Detrás del grupo _xlfn., o sea desnuda | Con prefijo, por ejemplo _xlfn.FORECAST.LINEAR |
| CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCH | Lista ordinaria de funciones integradas | Con prefijo, por ejemplo _xlfn.TEXTJOIN |
| XIRR, XNPV, ERFC | No está en la lista de funciones futuras | Desnuda |
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("-",TRUE,B1:D1). Las comillas salen como " 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: " & <
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 funciones | Token de nombre | A qué apunta el nombre |
|---|---|---|
| Funciones del Analysis ToolPak: EDATE, EOMONTH, WORKDAY, NETWORKDAYS, RANDBETWEEN, XIRR … | ptgNameX | Un registro EXTERNNAME en el SUPBOOK de add-in del libro |
Funciones que Excel escribe con _xlfn.: XLOOKUP, ISOWEEKNUM, IFS, CONCAT … | ptgName | Un registro NAME oculto llamado _xlfn.XLOOKUP (o _xlfn._xlws.FILTER) cuya definición es #NAME? |
| Funciones que la copia de Excel que guarda no reconoce | ptgName | Un 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
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
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