Article technique

Préfixes _xlfn et noms BIFF8 pour fonctions Excel récentes

Un classeur généré par Delphi porte =GAMMA(5) et =XIRR(B2:B9,A2:A9), et Excel l'ouvre avec #NAME? dans la cellule GAMMA et un résultat correct dans la cellule XIRR. Dans HotXLS, les fonctions qui transportent un préfixe _xlfn. en XLSX, ou un appel par nom en BIFF8 .xls, sont celles qu'Excel enregistre de cette façon — une liste qui s'écarte de la grammaire MS-XLSX

Pourquoi Excel affiche #NAME? pour GAMMA mais pas pour XIRR ?

Le texte de la formule est juste ; c'est le stockage qui ne l'est pas. Dans une feuille XLSX, une formule est du texte simple dans un élément <f>, et une fonction ajoutée après Excel 2007 s'écrit _xlfn.NAME. Le préfixe dit au lecteur "voici une fonction intégrée que vous ne connaissez peut-être pas encore", si bien qu'un Excel plus ancien affiche #NAME? sans prendre l'appel pour un nom défini par l'utilisateur, et l'Excel courant retire le préfixe à l'affichage si bien que les utilisateurs voient =GAMMA(5). Omettez-le et l'Excel courant part à la recherche d'un nom défini appelé GAMMA, n'en trouve pas, et affiche #NAME?. XIRR est plus ancienne. Elle est arrivée avec l'Analysis ToolPak, Excel l'écrit nue, et un _xlfn.XIRR préfixé est un jeton différent de celui qu'Excel produit. GAMMA, GAUSS, PHI, SHEET, SHEETS et XOR réclament toutes le préfixe, ainsi que les huit fonctions qu'Excel 2013 a ajoutées à l'ensemble ingénierie et nombres complexes (IMCOSH, IMCOT, IMCSC, IMCSCH, IMSEC, IMSECH, IMSINH et IMTAN), tandis que les plus anciennes IMSIN, IMSQRT et IMSUM restent nues. Les fonctions de tableau réservées aux feuilles transportent un second préfixe, _xlfn._xlws., que le §2.2.2 de MS-XLSX réserve à FILTER, SORT et PY. À la lecture, HotXLS retire les deux préfixes, si bien que Cell.Formula renvoie le nom de fonction propre

Routes de stockage des nouvelles fonctions Excel dans HotXLS pour Delphi : GAMMA porte le préfixe _xlfn tandis que l'XIRR ToolPak reste nue
le préfixe marque une fonction intégrée que le lecteur ne connaît peut-être pas encore, si bien que l'Excel courant le retire et que les versions plus anciennes répondent par une erreur de nom

Quelles fonctions Excel préfixe-t-il que la grammaire MS-XLSX laisse nues ?

Le §2.2.2 de MS-XLSX épelle la liste en ABNF : future-function-list = ("_xlfn." ("AGGREGATE" / "ACOT" / … / "Z.TEST")) / "ECMA.CEILING" / "ISO.CEILING" / "NETWORKDAYS.INTL" / "WORKDAY.INTL" / "FORECAST.ETS" / …. Lu littéralement, la parenthèse fermante après Z.TEST clôt le groupe préfixé, si bien que tout ce qui suit est un nom nu. Une seconde production, la liste ordinaire des fonctions intégrées, range CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS et SWITCH à côté de SUM et VLOOKUP. Excel est en désaccord sur les deux points :

FonctionOù la grammaire MS-XLSX la rangeCe qu'Excel écrit
NETWORKDAYS.INTL, WORKDAY.INTLAprès le groupe _xlfn., donc nue_xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL
ISO.CEILINGAprès le groupe _xlfn., donc nue_xlfn.ISO.CEILING
FORECAST.LINEAR et la famille FORECAST.ETSAprès le groupe _xlfn., donc nuePréfixé, par exemple _xlfn.FORECAST.LINEAR
CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCHListe ordinaire des fonctions intégréesPréfixé, par exemple _xlfn.TEXTJOIN
XIRR, XNPV, ERFCHors de la liste des fonctions futuresNue

Un writer généré depuis la grammaire produit donc NETWORKDAYS.INTL(…) sans préfixe, et Excel répond par #NAME? dans un fichier qui valide contre la syntaxe publiée. Prenez la liste dans des fichiers Excel enregistrés et traitez l'ABNF comme un point de départ. HotXLS le fait pour chaque fonction qu'il calcule, si bien que le CONCAT préfixé et le NETWORKDAYS.INTL préfixé sortent tous deux comme Excel les écrit. Le code suivant enregistre le même type de formules par les deux voies et en relit une :

uses
  SysUtils, lxCalc, lxHandle, lxHandleX;

procedure SaveModernFunctions;
var
  XBook, XCheck: TXLSXWorkbook;
  XSheet: TXLSXWorksheet;
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  // XLSX : les préfixes sont ajoutés à la sauvegarde et retirés au chargement
  XBook := TXLSXWorkbook.Create;
  try
    XSheet := XBook.Sheets.Add('Calc');
    XSheet.Cells[1, 1].Value := 46000.0;                // un numéro de série de date
    XSheet.Cells[1, 2].Formula := 'EDATE(A1,1)';       // ToolPak : aucun préfixe
    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');
    // Attendu : NETWORKDAYS.INTL(A1,A1+30,11), préfixe déjà retiré
    Writeln(string(XCheck.Sheets[1].Cells[1, 4].Formula));
  finally
    XCheck.Free;
  end;

  // BIFF8 : les mêmes appels deviennent des jetons de nom plus l'entrée Ftab 255
  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 complément
    Sheet.Cells[1, 3].Formula := '=ISOWEEKNUM(A1)';    // NAME _xlfn caché
    if Book.SaveAs('modern.xls') <> lxOk then          // BIFF8 par défaut
      raise Exception.Create('XLS save failed');
  finally
    Book.Free;
  end;
end;

Les deux fichiers devraient s'ouvrir dans l'Excel courant avec chaque fonction intacte. Pour voir les préfixes, ouvrez le paquet comme un ZIP et listez le texte brut <f> de la partie feuille ; pour modern.xlsx, la routine ci-dessous devrait imprimer EDATE(A1,1), _xlfn.GAMMA(5), _xlfn.NETWORKDAYS.INTL(A1,A1+30,11) et _xlfn.TEXTJOIN(&quot;-&quot;,TRUE,B1:D1). Les guillemets sortent en &quot; parce que la routine imprime le texte XML sans le déséchapper, ce qui est sans conséquence pour un diagnostic. Elle marche sur des fichiers de n'importe quel producteur, si bien que c'est le contrôle le plus rapide quand un classeur tiers affiche #NAME? sur une fonction que votre Excel prend en charge

uses
  SysUtils, Classes, Zip;

// Imprime chaque élément <f> d'une partie feuille, préfixes compris
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> : pas une formule de cellule
    P := Pos('>', Xml, P) + 1;               // saute les attributs comme t="shared"
    if Xml[P - 2] = '/' then
      Continue;                                // <f .../> vide d'une formule partagée
    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;

Comment un fichier .xls BIFF8 appelle-t-il une fonction sans numéro ?

Les formules BIFF8 ne contiennent aucun nom de fonction. Chaque fonction intégrée est un numéro dans une table de fonctions fixe (Ftab), et une formule stocke ptgFunc ou ptgFuncVar avec ce numéro. Les fonctions plus récentes n'ont pas de numéro. MS-XLS route ces appels par l'entrée Ftab 0x00FF, "User Defined Function" : un ptgFuncVar d'index de fonction 255 dont le premier argument est un jeton de nom pointant vers la vraie fonction. Le compte d'arguments stocké dans le jeton est supérieur d'un à ce que l'utilisateur a tapé, car ce nom de tête compte comme un argument. Le jeton de nom qu'Excel écrit dépend de la famille de la fonction :

Famille de fonctionsJeton de nomVers quoi le nom pointe
Fonctions de l'Analysis ToolPak : EDATE, EOMONTH, WORKDAY, NETWORKDAYS, RANDBETWEEN, XIRR …ptgNameXUn enregistrement EXTERNNAME dans le SUPBOOK de complément du classeur
Fonctions qu'Excel écrit avec _xlfn. : XLOOKUP, ISOWEEKNUM, IFS, CONCAT …ptgNameUn enregistrement NAME caché appelé _xlfn.XLOOKUP (ou _xlfn._xlws.FILTER) dont la définition est #NAME?
Fonctions que la copie d'Excel qui enregistre ne reconnaît pasptgNameUn enregistrement NAME ordinaire de classeur à définition vide

Le SUPBOOK de complément de la première ligne est le même enregistrement qu'un lecteur rencontre quand il trie les références externes, et la façon dont les enregistrements BIFF SUPBOOK classent les références externes montre où il siège parmi les autres sortes. Tout ce qui ressemble à un complément n'emprunte pas cette voie. DATEDIF, USDOLLAR, FINDB et ISPMT, entre autres, ont des numéros Ftab natifs et sont stockés comme de simples appels de fonction. La troisième ligne est la fragile : une fonction trop neuve pour l'Excel qui a enregistré le fichier, comme REGEXTEST et REGEXEXTRACT, devient un appel par un nom ordinaire sans définition. Un lecteur qui jette les noms à définition vide comme des déchets laisse la formule pointer vers un index de nom qui n'existe plus, et Excel refuse d'ouvrir le fichier entier qu'il réécrit. HotXLS garde un tel nom avec une définition #NAME? et réécrit l'enregistrement original à la sauvegarde

Routes d'appel par nom BIFF8 dans HotXLS pour Delphi : les fonctions ToolPak passent par ptgNameX tandis que les fonctions _xlfn appellent via un enregistrement de nom caché et la fonction 255
une formule dans un fichier .xls stocke des numéros, si bien qu'une fonction neuve emprunte le créneau défini par l'utilisateur et pointe vers un enregistrement de nom qui doit survivre au aller-retour

Chaque nom doit exister avant la première feuille

L'ordre est la contrainte suivante. Un fichier BIFF8 écrit ses globales — la table NAME et EXTERNSHEET — avant tout sous-flux de feuille, si bien que chaque nom vers lequel une formule pointera doit être connu avant l'écriture de la première cellule. Un writer qui rencontre XLOOKUP à mi-chemin de la feuille trois ne peut plus ajouter le nom caché dont il a besoin. Si vous produisez du BIFF8 vous-même, balayez chaque source de formule avant d'écrire les globales : formules de cellules, noms au niveau classeur et feuille, règles de validation de données et mise en forme conditionnelle. Les tailles se déduisent des jetons. Un appel par nom coûte 9 octets en plus des arguments (5 pour ptgName, 4 pour ptgFuncVar) et un appel de complément 11 (7 pour ptgNameX, 4 pour ptgFuncVar), et les longueurs d'enregistrement calculées avant l'écriture doivent employer le même choix de jeton que les octets écrits ensuite. HotXLS résout tous ces noms en une passe avant d'écrire quoi que ce soit

Ordre d'écriture d'un fichier BIFF8 dans HotXLS pour Delphi : les enregistrements de noms des globales précèdent chaque feuille, si bien que les sources de formules sont balayées d'abord
un nom rencontré à mi-chemin de la feuille trois ne peut pas rejoindre les globales, si bien que chaque source de formule est balayée avant l'écriture du premier octet

Les classes d'opérandes changent ce qu'Excel calcule

Chaque argument d'une formule BIFF8 est étiqueté comme opérande référence, valeur ou tableau, et Excel prend l'étiquette au pied de la lettre. Étiquetez le troisième argument d'AGGREGATE comme valeur et Excel renvoie #VALUE!. Étiquetez le résultat interne de SUM(SORTBY(…)) comme valeur et Excel insère une intersection implicite, montre SUM(@SORTBY(…)) et somme une seule cellule. Un writer doit étiqueter ces arguments comme le fait Excel, ce qui n'est pas toujours la classe que suggère la documentation de la fonction : les arguments des fonctions de complément sont écrits comme références, les fonctions de tableau dynamique renvoient des tableaux, et XLOOKUP, TAKE, DROP, IFS et SWITCH renvoient des références quand ils siègent là où une référence est attendue. Les classes fausses ne cassent pas le fichier. Elles changent la réponse

Les fonctions VBA gardent un classeur binaire XLSB

XLSB ajoute un troisième cas de stockage. Un classeur macro peut appeler ses propres procédures Function VBA depuis des cellules, et le format binaire déclare chacune comme un nom marqué procédure avec une formule vide ; la cellule l'appelle ensuite par le même motif ptgName plus fonction 255 que le BIFF8. Les formats XML n'ont pas d'équivalent. Un nom défini vide n'y est pas valide, et un nom défini comme #NAME? cacherait la fonction VBA du même nom. HotXLS enregistre un tel classeur en XLSB avec les déclarations intactes, et refuse la conversion vers tout autre format avant d'écrire le moindre octet. Demandez le rapport de conversion avant de convertir, pas après l'échec d'une sauvegarde : TXLSXWorkbook.GetConversionReport renvoie une entrée VBAFunctionNames marquée non prise en charge pour ces classeurs, et le code peut bifurquer dessus

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;

Quand un classeur venant d'un autre outil affiche #NAME? sur une fonction qu'Excel connaît parfaitement, lancez DumpRawFormulas dessus avant toute chose : un NETWORKDAYS.INTL ou un TEXTJOIN nu dans le texte <f> est le diagnostic entier. Affectez ce même texte de formule via Cell.Formula dans le composant Excel HotXLS pour Delphi et le XLSX reçoit le préfixe qu'Excel attend, tandis qu'une sauvegarde .xls du classeur reçoit le nom caché et l'appel de la fonction 255