Articolo tecnico

Prefissi _xlfn e name call BIFF8 per le nuove funzioni Excel

Un workbook generato da Delphi contiene =GAMMA(5) e =XIRR(B2:B9,A2:A9), e Excel lo apre con #NAME? nella cella di GAMMA e un risultato corretto nella cella di XIRR. In HotXLS, le funzioni che portano un prefisso _xlfn. in XLSX, oppure una chiamata basata su name in BIFF8 .xls, sono quelle che Excel salva allo stesso modo — una lista che prende le distanze dalla grammatica MS-XLSX

Perché Excel mostra #NAME? per GAMMA ma non per XIRR?

Il testo della formula è giusto; la memorizzazione no. Dentro un worksheet XLSX una formula è testo semplice in un elemento <f>, e una funzione aggiunta dopo Excel 2007 si scrive come _xlfn.NAME. Il prefisso dice al reader "questa è una funzione incorporata che forse non conosci ancora", così un Excel più vecchio mostra #NAME? senza scambiare la chiamata per un name definito dall'utente, e l'Excel corrente toglie il prefisso nella visualizzazione così gli utenti vedono =GAMMA(5). Lasciatelo fuori e l'Excel corrente va a cercare un defined name chiamato GAMMA, non lo trova, e mostra #NAME?. XIRR è più vecchia. Arrivò con l'Analysis ToolPak, Excel la scrive nuda, e una _xlfn.XIRR con prefisso è un token diverso da quello che Excel produce. GAMMA, GAUSS, PHI, SHEET, SHEETS e XOR vogliono tutti il prefisso, e così le otto funzioni che Excel 2013 aggiunse al set di ingegneria e numeri complessi (IMCOSH, IMCOT, IMCSC, IMCSCH, IMSEC, IMSECH, IMSINH e IMTAN), mentre le più vecchie IMSIN, IMSQRT e IMSUM restano nude. Le funzioni array solo-foglio portano un secondo prefisso, _xlfn._xlws., che MS-XLSX §2.2.2 riserva a FILTER, SORT e PY. In lettura HotXLS toglie entrambi i prefissi, così Cell.Formula restituisce il nome pulito della funzione

Percorsi di memorizzazione delle nuove funzioni Excel in HotXLS per Delphi: GAMMA porta il prefisso _xlfn mentre la XIRR del ToolPak resta nuda
Il prefisso segnala una funzione incorporata che il reader forse non conosce ancora, così l'Excel corrente lo toglie e le versioni più vecchie rispondono con un errore di name

Quali funzioni Excel prefissa e la grammatica MS-XLSX lascia nude?

MS-XLSX §2.2.2 scrive la lista in ABNF come future-function-list = ("_xlfn." ("AGGREGATE" / "ACOT" / … / "Z.TEST")) / "ECMA.CEILING" / "ISO.CEILING" / "NETWORKDAYS.INTL" / "WORKDAY.INTL" / "FORECAST.ETS" / …. Letta alla lettera, la parentesi di chiusura dopo Z.TEST chiude il gruppo con prefisso, quindi tutto ciò che viene dopo è un nome nudo. Una seconda produzione, la lista ordinaria delle funzioni incorporate, include CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS e SWITCH accanto a SUM e VLOOKUP. Excel non è d'accordo su entrambi i punti:

FunzioneDove la mette la grammatica MS-XLSXCosa scrive Excel
NETWORKDAYS.INTL, WORKDAY.INTLDopo il gruppo _xlfn., quindi nude_xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL
ISO.CEILINGDopo il gruppo _xlfn., quindi nuda_xlfn.ISO.CEILING
FORECAST.LINEAR e la famiglia FORECAST.ETSDopo il gruppo _xlfn., quindi nudeCon prefisso, per esempio _xlfn.FORECAST.LINEAR
CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCHLista ordinaria delle funzioni incorporateCon prefisso, per esempio _xlfn.TEXTJOIN
XIRR, XNPV, ERFCNon nella future-function listNude

Uno writer generato dalla grammatica produce quindi NETWORKDAYS.INTL(…) senza prefisso, e Excel risponde con #NAME? in un file che valida contro la sintassi pubblicata. Prendete la lista dai file Excel salvati e trattate l'ABNF come un punto di partenza. HotXLS lo fa per ogni funzione che calcola, così la CONCAT con prefisso e la NETWORKDAYS.INTL con prefisso escono entrambe come le scrive Excel. Il codice seguente salva lo stesso tipo di formule in entrambi i modi e ne rilegge una:

uses
  SysUtils, lxCalc, lxHandle, lxHandleX;

procedure SaveModernFunctions;
var
  XBook, XCheck: TXLSXWorkbook;
  XSheet: TXLSXWorksheet;
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  // XLSX: i prefissi si aggiungono al salvataggio e si tolgono al caricamento
  XBook := TXLSXWorkbook.Create;
  try
    XSheet := XBook.Sheets.Add('Calc');
    XSheet.Cells[1, 1].Value := 46000.0;                // un seriale data
    XSheet.Cells[1, 2].Formula := 'EDATE(A1,1)';       // ToolPak: nessun prefisso
    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');
    // Atteso: NETWORKDAYS.INTL(A1,A1+30,11), prefisso già rimosso
    Writeln(string(XCheck.Sheets[1].Cells[1, 4].Formula));
  finally
    XCheck.Free;
  end;

  // BIFF8: le stesse chiamate diventano name token più la voce 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 di add-in
    Sheet.Cells[1, 3].Formula := '=ISOWEEKNUM(A1)';    // NAME _xlfn nascosto
    if Book.SaveAs('modern.xls') <> lxOk then          // BIFF8 per default
      raise Exception.Create('XLS save failed');
  finally
    Book.Free;
  end;
end;

Entrambi i file dovrebbero aprirsi nell'Excel corrente con ogni funzione intatta. Per vedere i prefissi, aprite il pacchetto come ZIP ed elencate il testo <f> grezzo della parte worksheet; per modern.xlsx la routine qui sotto dovrebbe stampare EDATE(A1,1), _xlfn.GAMMA(5), _xlfn.NETWORKDAYS.INTL(A1,A1+30,11) e _xlfn.TEXTJOIN(&quot;-&quot;,TRUE,B1:D1). Le virgolette escono come &quot; perché la routine stampa il testo XML senza togliere l'escaping, il che per una diagnosi non è un problema. Funziona su file di qualunque produttore, quindi è il controllo più rapido quando un workbook di terze parti mostra #NAME? su una funzione che la vostra copia di Excel conosce

uses
  SysUtils, Classes, Zip;

// Stampa ogni elemento <f> di una parte worksheet, prefissi inclusi
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>: non è una formula di cella
    P := Pos('>', Xml, P) + 1;               // salta gli attributi tipo t="shared"
    if Xml[P - 2] = '/' then
      Continue;                                // <f .../> vuoto di una formula condivisa
    Close := Pos('</f>', Xml, P);
    if Close = 0 then
      Break;
    Writeln(Copy(Xml, P, Close - P));          // XML escaped: &quot; &amp; &lt;
    P := Close + 4;
  end;
end;

Come chiama una funzione un file .xls BIFF8 che per essa non ha numero?

Le formule BIFF8 non contengono affatto nomi di funzioni. Ogni funzione incorporata è un numero in una tabella funzioni fissa (Ftab), e una formula memorizza ptgFunc o ptgFuncVar con quel numero. Le funzioni più nuove non hanno numero. MS-XLS instrada quelle chiamate attraverso la voce 0x00FF del Ftab, "User Defined Function": un ptgFuncVar con indice di funzione 255 il cui primo argomento è un name token che punta alla funzione vera. Il conteggio degli argomenti memorizzato nel token è di uno più alto di quello che l'utente ha digitato, perché quel name in testa conta come argomento. Quale name token scriva Excel dipende dalla famiglia della funzione:

Famiglia di funzioniName tokenA cosa punta il name
Funzioni dell'Analysis ToolPak: EDATE, EOMONTH, WORKDAY, NETWORKDAYS, RANDBETWEEN, XIRR …ptgNameXUn record EXTERNNAME nel SUPBOOK add-in del workbook
Funzioni che Excel scrive con _xlfn.: XLOOKUP, ISOWEEKNUM, IFS, CONCAT …ptgNameUn record NAME nascosto chiamato _xlfn.XLOOKUP (o _xlfn._xlws.FILTER) la cui definizione è #NAME?
Funzioni che la copia di Excel che salva non riconosceptgNameUn normale record NAME del workbook con una definizione vuota

Il SUPBOOK add-in della prima riga è lo stesso record che un reader incontra quando ordina i riferimenti esterni, e il modo in cui i record BIFF SUPBOOK classificano i riferimenti esterni mostra dove siede tra gli altri tipi. Non tutto ciò che sembra un add-in passa di qui. DATEDIF, USDOLLAR, FINDB e ISPMT, tra gli altri, hanno numeri Ftab nativi e vengono memorizzate come normali chiamate di funzione. La terza riga è quella fragile: una funzione troppo nuova per l'Excel che ha salvato il file, come le REGEXTEST e REGEXEXTRACT, diventa una chiamata attraverso un name qualunque senza definizione. Un reader che butta via i name a definizione vuota come spazzatura lascia la formula che punta a un indice di name che non esiste più, e Excel si rifiuta di aprire l'intero file che rimette a letto. HotXLS tiene un name del genere con una definizione #NAME? e riscrive il record originale al salvataggio

Percorsi delle name call BIFF8 in HotXLS per Delphi: le funzioni ToolPak viaggiano su ptgNameX mentre le funzioni _xlfn chiamano attraverso un record name nascosto e la funzione 255
Una formula in un file .xls memorizza numeri, così una funzione nuova prende a prestito lo slot user-defined e punta a un record name che deve sopravvivere al round trip

Ogni name deve esistere prima del primo worksheet

L'ordine è il vincolo successivo. Un file BIFF8 scrive i suoi global — la tabella NAME ed EXTERNSHEET — prima di qualunque substream di worksheet, quindi ogni name a cui una formula punterà deve essere noto prima che la prima cella venga scritta. Uno writer che incontra XLOOKUP a metà del foglio tre non può più aggiungere il name nascosto che gli serve. Se producete BIFF8 voi, scansite ogni sorgente di formula prima di scrivere i global: formule di cella, name a livello di workbook e di foglio, regole di data validation e conditional formatting. Le dimensioni discendono dai token. Una name call costa 9 byte sopra gli argomenti (5 per ptgName, 4 per ptgFuncVar) e una chiamata add-in 11 (7 per ptgNameX, 4 per ptgFuncVar), e le lunghezze dei record calcolate prima della scrittura devono usare la stessa scelta di token dei byte scritti dopo. HotXLS risolve tutti questi name in un solo passaggio prima di scrivere qualcosa

Ordine di scrittura di un file BIFF8 in HotXLS per Delphi: i record name nei global precedono ogni worksheet, così le sorgenti di formula vengono scansionate prima
Un name incontrato a metà del foglio tre non può entrare nei global, quindi ogni sorgente di formula viene scansionata prima che il primo byte venga scritto

Le classi degli operandi cambiano ciò che Excel calcola

Ogni argomento in una formula BIFF8 è etichettato come operando reference, value o array, e Excel prende l'etichetta in parola. Etichettate come value il terzo argomento di AGGREGATE e Excel restituisce #VALUE!. Etichettate come value il risultato interno di SUM(SORTBY(…)) e Excel inserisce un'intersezione implicita, mostra SUM(@SORTBY(…)) e somma una singola cella. Uno writer deve etichettare questi argomenti come fa Excel, il che non è sempre la classe che la documentazione della funzione suggerisce: gli argomenti delle funzioni add-in si scrivono come reference, le funzioni dynamic-array restituiscono array, e XLOOKUP, TAKE, DROP, IFS e SWITCH restituiscono reference quando sedono dove ci si aspetta un reference. Le classi sbagliate non rompono il file. Cambiano la risposta

Le funzioni VBA tengono un workbook binario XLSB

XLSB aggiunge un terzo caso di memorizzazione. Un workbook macro può chiamare dalle celle le proprie procedure VBA Function, e il formato binario dichiara ciascuna come un name contrassegnato come procedura con formula vuota; la cella poi la chiama attraverso lo stesso schema ptgName più funzione 255 di BIFF8. I formati XML non hanno un equivalente. Un defined name vuoto lì non è valido, e un name definito come #NAME? nasconderebbe la funzione VBA omonima. HotXLS salva un workbook simile di nuovo in XLSB con le dichiarazioni intatte, e si rifiuta di convertirlo a qualunque altro formato prima di scrivere un byte. Chiedete il report di conversione prima di convertire, non dopo che un salvataggio è fallito: TXLSXWorkbook.GetConversionReport restituisce una voce VBAFunctionNames marcata non supportata per questi workbook, e il codice può ramificarci sopra

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

Quando un workbook di un altro strumento mostra #NAME? su una funzione che Excel evidentemente conosce, fatelo girare per DumpRawFormulas prima di ogni altra cosa: una NETWORKDAYS.INTL o una TEXTJOIN nude nel testo <f> sono l'intera diagnosi. Assegnate quello stesso testo di formula attraverso Cell.Formula nel componente Excel HotXLS per Delphi e l'XLSX prende il prefisso che Excel si aspetta, mentre un salvataggio .xls del workbook prende il name nascosto e la chiamata alla funzione 255