Artigo Técnico

_xlfn e name calls BIFF8 para funções novas do Excel

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

Rotas de armazenamento para funções novas do Excel no HotXLS para Delphi: GAMMA carrega o prefixo _xlfn enquanto a XIRR do ToolPak fica sem prefixo
o prefixo marca uma embutida que o leitor talvez ainda não conheça, então o Excel atual o remove e versões mais velhas respondem com um erro de nome

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çãoOnde a gramática MS-XLSX a põeO que o Excel grava
NETWORKDAYS.INTL, WORKDAY.INTLDepois do grupo _xlfn., então sem prefixo_xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL
ISO.CEILINGDepois do grupo _xlfn., então sem prefixo_xlfn.ISO.CEILING
FORECAST.LINEAR e a família FORECAST.ETSDepois do grupo _xlfn., então sem prefixoCom prefixo, por exemplo _xlfn.FORECAST.LINEAR
CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCHLista comum de funções embutidasCom prefixo, por exemplo _xlfn.TEXTJOIN
XIRR, XNPV, ERFCNão está na future-function listSem 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(&quot;-&quot;,TRUE,B1:D1). As aspas saem como &quot; 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: &quot; &amp; &lt;
    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çõesToken de nomePara o que o nome aponta
Funções do Analysis ToolPak: EDATE, EOMONTH, WORKDAY, NETWORKDAYS, RANDBETWEEN, XIRR …ptgNameXUm record EXTERNNAME no SUPBOOK de add-in da pasta de trabalho
Funções que o Excel grava com _xlfn.: XLOOKUP, ISOWEEKNUM, IFS, CONCAT …ptgNameUm 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 reconheceptgNameUm 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

Rotas de name call BIFF8 no HotXLS para Delphi: funções ToolPak viajam pelo ptgNameX enquanto funções _xlfn chamam por um record de nome oculto e a função 255
uma fórmula num arquivo .xls guarda números, então uma função nova toma emprestada a posição de user-defined e aponta para um record de nome que precisa sobreviver ao round trip

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

Ordem de gravação de um arquivo BIFF8 no HotXLS para Delphi: records de nome nos globals precedem toda worksheet, então fontes de fórmula são escaneadas primeiro
um nome encontrado no meio da sheet três não pode entrar nos globals, então toda fonte de fórmula é escaneada antes de o primeiro byte ser gravado

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