Uma pasta de trabalho gerada em Delphi guarda =GAMMA(5) e =XIRR(B2:B9,A2:A9), e o Excel abre com #NAME? na célula GAMMA e um resultado correto na célula XIRR. No HotXLS, as funções que carregam o prefixo _xlfn. em XLSX, ou uma chamada por nome em BIFF8 .xls, são as que o Excel salva assim — uma lista que se afasta da gramática MS-XLSX
Por que o Excel mostra #NAME? para GAMMA mas não para XIRR?
O texto da fórmula está certo; o armazenamento não. Dentro de uma worksheet XLSX uma fórmula é texto plain num elemento <f>, e uma função adicionada depois do Excel 2007 é escrita como _xlfn.NAME. O prefixo diz ao leitor "isto é uma função embutida que você talvez ainda não conheça", então um Excel mais velho mostra #NAME? sem confundir a chamada com um nome definido pelo usuário, e o Excel atual tira o prefixo na exibição para os usuários verem =GAMMA(5). Sem ele, o Excel atual sai procurando um nome definido chamado GAMMA, não acha nenhum, e mostra #NAME?. XIRR é mais velha. Veio com o Analysis ToolPak, o Excel a escreve sem prefixo, e um _xlfn.XIRR com prefixo é um token diferente do que o Excel produz. GAMMA, GAUSS, PHI, SHEET, SHEETS e XOR precisam do prefixo, e também as oito funções que o Excel 2013 adicionou ao conjunto de engenharia e números complexos (IMCOSH, IMCOT, IMCSC, IMCSCH, IMSEC, IMSECH, IMSINH e IMTAN), enquanto as mais velhas IMSIN, IMSQRT e IMSUM ficam sem prefixo. Funções de array só de worksheet carregam um segundo prefixo, _xlfn._xlws., que a MS-XLSX §2.2.2 reserva para FILTER, SORT e PY. Na leitura, o HotXLS remove os dois prefixos, então o Cell.Formula retorna o nome limpo da função
Quais funções o Excel prefixa e a gramática MS-XLSX deixa sem prefixo?
A MS-XLSX §2.2.2 escreve a lista em ABNF como future-function-list = ("_xlfn." ("AGGREGATE" / "ACOT" / … / "Z.TEST")) / "ECMA.CEILING" / "ISO.CEILING" / "NETWORKDAYS.INTL" / "WORKDAY.INTL" / "FORECAST.ETS" / …. Lida ao pé da letra, o parêntese de fechamento depois do Z.TEST encerra o grupo com prefixo, então tudo depois dele é um nome sem prefixo. Uma segunda produção, a lista comum de funções embutidas, inclui CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS e SWITCH ao lado de SUM e VLOOKUP. O Excel discorda nos dois pontos:
| Função | Onde a gramática MS-XLSX a põe | O que o Excel grava |
|---|---|---|
| NETWORKDAYS.INTL, WORKDAY.INTL | Depois do grupo _xlfn., então sem prefixo | _xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL |
| ISO.CEILING | Depois do grupo _xlfn., então sem prefixo | _xlfn.ISO.CEILING |
| FORECAST.LINEAR e a família FORECAST.ETS | Depois do grupo _xlfn., então sem prefixo | Com prefixo, por exemplo _xlfn.FORECAST.LINEAR |
| CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCH | Lista comum de funções embutidas | Com prefixo, por exemplo _xlfn.TEXTJOIN |
| XIRR, XNPV, ERFC | Não está na future-function list | Sem prefixo |
Um writer gerado da gramática portanto produz NETWORKDAYS.INTL(…) sem prefixo, e o Excel responde com #NAME? num arquivo que valida contra a sintaxe publicada. Tire a lista de arquivos Excel salvos e trate a ABNF como ponto de partida. O HotXLS faz isso para toda função que calcula, então o CONCAT com prefixo e o NETWORKDAYS.INTL com prefixo saem do jeito que o Excel grava. O código a seguir salva o mesmo tipo de fórmula dos dois jeitos e lê uma de volta:
uses
SysUtils, lxCalc, lxHandle, lxHandleX;
procedure SaveModernFunctions;
var
XBook, XCheck: TXLSXWorkbook;
XSheet: TXLSXWorksheet;
Book: TXLSWorkbook;
Sheet: TXLSWorksheet;
begin
// XLSX: prefixos são adicionados no save e removidos no load
XBook := TXLSXWorkbook.Create;
try
XSheet := XBook.Sheets.Add('Calc');
XSheet.Cells[1, 1].Value := 46000.0; // um serial de data
XSheet.Cells[1, 2].Formula := 'EDATE(A1,1)'; // ToolPak: sem prefixo
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), prefixo já removido
Writeln(string(XCheck.Sheets[1].Cells[1, 4].Formula));
finally
XCheck.Free;
end;
// BIFF8: as mesmas chamadas viram tokens de nome mais a entrada 255 da 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 padrão
raise Exception.Create('XLS save failed');
finally
Book.Free;
end;
end;
Os dois arquivos devem abrir no Excel atual com toda função intacta. Para ver os prefixos, abra o pacote como ZIP e liste o texto <f> cru da parte da worksheet; para o modern.xlsx, a rotina abaixo deve imprimir EDATE(A1,1), _xlfn.GAMMA(5), _xlfn.NETWORKDAYS.INTL(A1,A1+30,11) e _xlfn.TEXTJOIN("-",TRUE,B1:D1). As aspas saem como " porque a rotina imprime texto XML sem desfazer o escape, o que é inofensivo num diagnóstico. Funciona em arquivos de qualquer produtor, então é a checagem mais rápida quando uma pasta de trabalho de terceiros mostra #NAME? numa função que a sua cópia do Excel suporta
uses
SysUtils, Classes, Zip;
// Imprime todo elemento <f> de uma parte de worksheet, prefixos incluídos
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>: não é fórmula de célula
P := Pos('>', Xml, P) + 1; // pula atributos como t="shared"
if Xml[P - 2] = '/' then
Continue; // <f .../> vazio de uma fórmula compartilhada
Close := Pos('</f>', Xml, P);
if Close = 0 then
Break;
Writeln(Copy(Xml, P, Close - P)); // com escape XML: " & <
P := Close + 4;
end;
end;
Como um arquivo BIFF8 .xls chama uma função para a qual não tem número?
Fórmulas BIFF8 não contêm nomes de função de forma alguma. Cada função embutida é um número numa tabela de funções fixa (Ftab), e uma fórmula guarda ptgFunc ou ptgFuncVar com esse número. Funções mais novas não têm número. A MS-XLS encaminha essas chamadas pela entrada 0x00FF da Ftab, "User Defined Function": um ptgFuncVar com índice de função 255 cujo primeiro argumento é um token de nome apontando para a função real. A contagem de argumentos guardada no token é um a mais que o que o usuário digitou, porque o nome à frente conta como argumento. Qual token de nome o Excel grava depende da família da função:
| Família de funções | Token de nome | Para o que o nome aponta |
|---|---|---|
| Funções do Analysis ToolPak: EDATE, EOMONTH, WORKDAY, NETWORKDAYS, RANDBETWEEN, XIRR … | ptgNameX | Um record EXTERNNAME no SUPBOOK de add-in da pasta de trabalho |
Funções que o Excel grava com _xlfn.: XLOOKUP, ISOWEEKNUM, IFS, CONCAT … | ptgName | Um record NAME oculto chamado _xlfn.XLOOKUP (ou _xlfn._xlws.FILTER) cuja definição é #NAME? |
| Funções que a cópia do Excel que salva não reconhece | ptgName | Um record NAME comum de pasta de trabalho com definição vazia |
O SUPBOOK de add-in na primeira linha é o mesmo record que um leitor encontra quando classifica referências externas, e o jeito como os records SUPBOOK BIFF classificam referências externas mostra onde ele fica entre os outros tipos. Nem tudo que parece um add-in segue esse caminho. DATEDIF, USDOLLAR, FINDB e ISPMT, entre outros, têm números nativos da Ftab e são armazenados como chamadas de função plain. A terceira linha é a frágil: uma função nova demais para o Excel que salvou o arquivo, como REGEXTEST e REGEXEXTRACT, vira uma chamada por um nome comum sem definição. Um leitor que descarta nomes de definição vazia como lixo deixa a fórmula apontando para um índice de nome que não existe mais, e o Excel se recusa a abrir o arquivo inteiro que ele grava de volta. O HotXLS mantém esse nome com uma definição #NAME? e grava o record original de novo no save
Todo nome precisa existir antes da primeira worksheet
A ordem é a próxima restrição. Um arquivo BIFF8 grava os globals dele — a tabela NAME e o EXTERNSHEET — antes de qualquer substream de worksheet, então todo nome para o qual uma fórmula vai apontar precisa ser conhecido antes de a primeira célula ser escrita. Um writer que encontra XLOOKUP no meio da sheet três não pode mais adicionar o nome oculto de que precisa. Se você produz BIFF8 você mesmo, escaneie toda fonte de fórmula antes de escrever os globals: fórmulas de célula, nomes de nível de pasta e de sheet, regras de validação de dados e formatação condicional. Os tamanhos saem dos tokens. Um name call custa 9 bytes além dos argumentos (5 para o ptgName, 4 para o ptgFuncVar) e um add-in call 11 (7 para o ptgNameX, 4 para o ptgFuncVar), e os comprimentos de record calculados antes de escrever devem usar a mesma escolha de token que os bytes escritos depois. O HotXLS resolve todos esses nomes numa passada antes de escrever qualquer coisa
Classes de operando mudam o que o Excel calcula
Cada argumento numa fórmula BIFF8 é marcado como operando de referência, valor ou array, e o Excel acredita na marca. Marque o terceiro argumento do AGGREGATE como valor e o Excel retorna #VALUE!. Marque o resultado interno do SUM(SORTBY(…)) como valor e o Excel insere uma interseção implícita, mostra SUM(@SORTBY(…)) e soma uma única célula. Um writer precisa marcar esses argumentos do jeito que o Excel faz, que não é sempre a classe que a documentação da função sugere: argumentos de funções add-in são escritos como referências, funções de dynamic array retornam arrays, e XLOOKUP, TAKE, DROP, IFS e SWITCH retornam referências quando estão onde se espera uma referência. Classes erradas não quebram o arquivo. Elas mudam a resposta
Funções VBA mantêm uma pasta de trabalho XLSB binária
O XLSB adiciona um terceiro caso de armazenamento. Uma pasta de trabalho de macros pode chamar as próprias procedures VBA Function a partir de células, e o formato binário declara cada uma como um nome marcado como procedure com fórmula vazia; a célula então a chama pelo mesmo padrão de ptgName mais função 255 do BIFF8. Os formatos XML não têm equivalente. Um nome definido vazio não é válido ali, e um nome definido como #NAME? esconderia a função VBA de mesmo nome. O HotXLS salva uma pasta dessas de volta para XLSB com as declarações intactas, e recusa a conversão para qualquer outro formato antes de escrever um byte. Peça o relatório de conversão antes de converter, não depois de um save falhar: o TXLSXWorkbook.GetConversionReport retorna uma entrada VBAFunctionNames marcada como unsupported para essas pastas, e o código pode ramificar nisso
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.ex. VBAFunctionNames
Book.SaveAs(ChangeFileExt(XlsbFile, '.edited.xlsb'),
xlsxExcelBinaryWorkbook)
else
Book.SaveAs(ChangeFileExt(XlsbFile, '.xlsx'), xlsxOpenXMLWorkbook);
finally
Book.Free;
end;
end;
Quando uma pasta de trabalho de outra ferramenta mostra #NAME? numa função que o Excel claramente conhece, rode DumpRawFormulas nela antes de qualquer outra coisa: um NETWORKDAYS.INTL ou TEXTJOIN sem prefixo no texto <f> é o diagnóstico inteiro. Atribua esse mesmo texto de fórmula pelo Cell.Formula no componente Excel HotXLS Delphi e o XLSX recebe o prefixo que o Excel espera, enquanto um save .xls da pasta recebe o nome oculto e a chamada da função 255