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
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 :
| Fonction | Où la grammaire MS-XLSX la range | Ce qu'Excel écrit |
|---|---|---|
| NETWORKDAYS.INTL, WORKDAY.INTL | Après le groupe _xlfn., donc nue | _xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL |
| ISO.CEILING | Après le groupe _xlfn., donc nue | _xlfn.ISO.CEILING |
| FORECAST.LINEAR et la famille FORECAST.ETS | Après le groupe _xlfn., donc nue | Préfixé, par exemple _xlfn.FORECAST.LINEAR |
| CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCH | Liste ordinaire des fonctions intégrées | Préfixé, par exemple _xlfn.TEXTJOIN |
| XIRR, XNPV, ERFC | Hors de la liste des fonctions futures | Nue |
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("-",TRUE,B1:D1). Les guillemets sortent en " 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: " & <
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 fonctions | Jeton de nom | Vers quoi le nom pointe |
|---|---|---|
| Fonctions de l'Analysis ToolPak : EDATE, EOMONTH, WORKDAY, NETWORKDAYS, RANDBETWEEN, XIRR … | ptgNameX | Un enregistrement EXTERNNAME dans le SUPBOOK de complément du classeur |
Fonctions qu'Excel écrit avec _xlfn. : XLOOKUP, ISOWEEKNUM, IFS, CONCAT … | ptgName | Un 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 pas | ptgName | Un 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
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
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