Ein Delphi-erzeugtes Workbook hält =GAMMA(5) und =XIRR(B2:B9,A2:A9), und Excel öffnet es mit #NAME? in der GAMMA-Zelle und einem korrekten Ergebnis in der XIRR-Zelle. In HotXLS tragen genau die Funktionen einen _xlfn.-Präfix in XLSX beziehungsweise einen Name-basierten Call in BIFF8-.xls, die Excel auch so speichert — eine Liste, die von der MS-XLSX-Grammatik abweicht
Warum zeigt Excel #NAME? für GAMMA, aber nicht für XIRR?
Der Formeltext ist richtig; die Speicherung ist es nicht. In einem XLSX-Worksheet ist eine Formel schlichter Text in einem <f>-Element, und eine Funktion, die nach Excel 2007 dazugekommen ist, wird als _xlfn.NAME geschrieben. Der Präfix sagt dem Reader "das ist eine eingebaute Funktion, die du vielleicht noch nicht kennst", also zeigt ein älteres Excel #NAME?, ohne den Call für einen benutzerdefinierten Namen zu halten, und das aktuelle Excel schneidet den Präfix bei der Anzeige ab, sodass Nutzer =GAMMA(5) sehen. Lässt man ihn weg, sucht das aktuelle Excel nach einem definierten Namen namens GAMMA, findet keinen und zeigt #NAME?. XIRR ist älter. Es kam mit dem Analysis ToolPak, Excel schreibt es ohne Präfix, und ein präfixiertes _xlfn.XIRR ist ein anderes Token als das, das Excel erzeugt. GAMMA, GAUSS, PHI, SHEET, SHEETS und XOR brauchen alle den Präfix, und dasselbe gilt für die acht Funktionen, die Excel 2013 zur Engineering- und Complex-Number-Sammlung hinzufügte (IMCOSH, IMCOT, IMCSC, IMCSCH, IMSEC, IMSECH, IMSINH und IMTAN), während die älteren IMSIN, IMSQRT und IMSUM ohne bleiben. Nur im Worksheet verfügbare Array-Funktionen tragen einen zweiten Präfix, _xlfn._xlws., den MS-XLSX §2.2.2 für FILTER, SORT und PY reserviert. Beim Lesen entfernt HotXLS beide Präfixe, also liefert Cell.Formula den sauberen Funktionsnamen
Welche Funktionen präfixiert Excel, die die MS-XLSX-Grammatik ohne lässt?
MS-XLSX §2.2.2 buchstabiert die Liste in ABNF als future-function-list = ("_xlfn." ("AGGREGATE" / "ACOT" / … / "Z.TEST")) / "ECMA.CEILING" / "ISO.CEILING" / "NETWORKDAYS.INTL" / "WORKDAY.INTL" / "FORECAST.ETS" / …. Wörtlich gelesen beendet die schließende Klammer nach Z.TEST die präfixierte Gruppe, also ist alles danach ein nackter Name. Eine zweite Produktion, die gewöhnliche Built-in-Funktionsliste, führt CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS und SWITCH neben SUM und VLOOKUP. Excel widerspricht beidem:
| Funktion | Wo die MS-XLSX-Grammatik sie ablegt | Was Excel schreibt |
|---|---|---|
| NETWORKDAYS.INTL, WORKDAY.INTL | Nach der _xlfn.-Gruppe, also ohne Präfix | _xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL |
| ISO.CEILING | Nach der _xlfn.-Gruppe, also ohne Präfix | _xlfn.ISO.CEILING |
| FORECAST.LINEAR und die FORECAST.ETS-Familie | Nach der _xlfn.-Gruppe, also ohne Präfix | Präfixiert, zum Beispiel _xlfn.FORECAST.LINEAR |
| CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCH | Gewöhnliche Built-in-Funktionsliste | Präfixiert, zum Beispiel _xlfn.TEXTJOIN |
| XIRR, XNPV, ERFC | Nicht in der Future-Function-Liste | Ohne Präfix |
Ein aus der Grammatik generierter Writer produziert also NETWORKDAYS.INTL(…) ohne Präfix, und Excel antwortet mit #NAME? in einer Datei, die gegen die veröffentlichte Syntax validiert. Nehmen Sie die Liste aus gespeicherten Excel-Dateien und behandeln Sie die ABNF als Ausgangspunkt. HotXLS tut das für jede Funktion, die es berechnet, also kommen der präfixierte CONCAT und das präfixierte NETWORKDAYS.INTL beide so heraus, wie Excel sie schreibt. Der folgende Code speichert dieselbe Art von Formeln in beide Richtungen und liest eine zurück:
uses
SysUtils, lxCalc, lxHandle, lxHandleX;
procedure SaveModernFunctions;
var
XBook, XCheck: TXLSXWorkbook;
XSheet: TXLSXWorksheet;
Book: TXLSWorkbook;
Sheet: TXLSWorksheet;
begin
// XLSX: Präfixe kommen beim Speichern dazu und fallen beim Laden weg
XBook := TXLSXWorkbook.Create;
try
XSheet := XBook.Sheets.Add('Calc');
XSheet.Cells[1, 1].Value := 46000.0; // ein Datums-Serial
XSheet.Cells[1, 2].Formula := 'EDATE(A1,1)'; // ToolPak: kein Präfix
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');
// Erwartet: NETWORKDAYS.INTL(A1,A1+30,11), Präfix bereits entfernt
Writeln(string(XCheck.Sheets[1].Cells[1, 4].Formula));
finally
XCheck.Free;
end;
// BIFF8: dieselben Calls werden Name-Tokens plus Ftab-Eintrag 255
Book := TXLSWorkbook.Create;
try
Sheet := Book.Sheets.Add;
Sheet.Cells[1, 1].Value := 46000.0;
Sheet.Cells[1, 2].Formula := '=EDATE(A1,1)'; // Add-in-EXTERNNAME
Sheet.Cells[1, 3].Formula := '=ISOWEEKNUM(A1)'; // versteckter _xlfn-NAME
if Book.SaveAs('modern.xls') <> lxOk then // standardmäßig BIFF8
raise Exception.Create('XLS save failed');
finally
Book.Free;
end;
end;
Beide Dateien sollten im aktuellen Excel mit jeder Funktion intakt öffnen. Um die Präfixe zu sehen, öffnen Sie das Paket als ZIP und listen Sie den rohen <f>-Text des Worksheet-Parts auf; für modern.xlsx sollte die Routine unten EDATE(A1,1), _xlfn.GAMMA(5), _xlfn.NETWORKDAYS.INTL(A1,A1+30,11) und _xlfn.TEXTJOIN("-",TRUE,B1:D1) drucken. Die Anführungszeichen kommen als " heraus, weil die Routine den XML-Text ohne Unescaping druckt, was für eine Diagnose harmlos ist. Sie arbeitet mit Dateien jedes Produzenten, also ist sie die schnellste Prüfung, wenn ein fremdes Workbook #NAME? in einer Funktion zeigt, die Ihr Excel unterstützt
uses
SysUtils, Classes, Zip;
// Jedes <f>-Element eines Worksheet-Parts drucken, Präfixe eingeschlossen
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>: keine Zellformel
P := Pos('>', Xml, P) + 1; // Attribute wie t="shared" überspringen
if Xml[P - 2] = '/' then
Continue; // leeres <f .../> einer geteilten Formel
Close := Pos('</f>', Xml, P);
if Close = 0 then
Break;
Writeln(Copy(Xml, P, Close - P)); // XML-escapet: " & <
P := Close + 4;
end;
end;
Wie ruft eine BIFF8-.xls-Datei eine Funktion auf, für die sie keine Nummer hat?
BIFF8-Formeln enthalten keinerlei Funktionsnamen. Jede eingebaute Funktion ist eine Nummer in einer festen Funktionstabelle (Ftab), und eine Formel speichert ptgFunc oder ptgFuncVar mit dieser Nummer. Neuere Funktionen haben keine Nummer. MS-XLS leitet solche Calls über den Ftab-Eintrag 0x00FF, "User Defined Function": ein ptgFuncVar mit Funktionsindex 255, dessen erstes Argument ein Name-Token ist, das auf die echte Funktion zeigt. Die im Token gespeicherte Argumentzahl ist um eins höher als das, was der Nutzer tippte, denn dieser führende Name zählt als Argument. Welches Name-Token Excel schreibt, hängt von der Familie der Funktion ab:
| Funktionsfamilie | Name-Token | Worauf der Name zeigt |
|---|---|---|
| Analysis-ToolPak-Funktionen: EDATE, EOMONTH, WORKDAY, NETWORKDAYS, RANDBETWEEN, XIRR … | ptgNameX | Ein EXTERNNAME-Record im Add-in-SUPBOOK des Workbooks |
Funktionen, die Excel mit _xlfn. schreibt: XLOOKUP, ISOWEEKNUM, IFS, CONCAT … | ptgName | Ein versteckter NAME-Record namens _xlfn.XLOOKUP (oder _xlfn._xlws.FILTER) mit der Definition #NAME? |
| Funktionen, die die speichernde Excel-Kopie nicht kennt | ptgName | Ein gewöhnlicher Workbook-NAME-Record mit leerer Definition |
Das Add-in-SUPBOOK in der ersten Zeile ist derselbe Record, dem ein Reader begegnet, wenn es externe Referenzen sortiert, und wie BIFF-SUPBOOK-Records externe Referenzen klassifizieren zeigt, wo es unter den anderen Arten sitzt. Nicht alles, was wie ein Add-in aussieht, läuft so. DATEDIF, USDOLLAR, FINDB und ISPMT haben unter anderem native Ftab-Nummern und werden als schlichte Funktions-Calls gespeichert. Die dritte Zeile ist die fragile: Eine Funktion, zu neu für das Excel, das die Datei speicherte, etwa REGEXTEST und REGEXEXTRACT, wird zu einem Call über einen gewöhnlichen Namen ohne Definition. Ein Reader, der Namen mit leerer Definition als Müll wegwirft, lässt die Formel auf einen Namensindex zeigen, der nicht mehr existiert, und Excel verweigert die Öffnung der gesamten Datei, die es zurückschreibt. HotXLS behält so einen Namen mit einer #NAME?-Definition und schreibt den Original-Record beim Speichern wieder heraus
Jeder Name muss vor dem ersten Worksheet existieren
Die Reihenfolge ist die nächste Zwangsbedingung. Eine BIFF8-Datei schreibt ihre Globals — die NAME-Tabelle und EXTERNSHEET — vor jedem Worksheet-Substream, also muss jeder Name, auf den eine Formel zeigen wird, bekannt sein, bevor die erste Zelle geschrieben ist. Ein Writer, der auf halbem Weg durch Sheet drei auf XLOOKUP stößt, kann den versteckten Namen, den es braucht, nicht mehr ergänzen. Produzieren Sie BIFF8 selbst, scannen Sie jede Formelquelle, bevor Sie die Globals schreiben: Zellformeln, Workbook- und Sheet-Level-Namen, Datenvalidierungsregeln und bedingte Formatierung. Die Größen ergeben sich aus den Tokens. Ein Name-Call kostet 9 Bytes oberhalb der Argumente (5 für ptgName, 4 für ptgFuncVar), ein Add-in-Call 11 (7 für ptgNameX, 4 für ptgFuncVar), und Record-Längen, die vor dem Schreiben berechnet werden, müssen dieselbe Token-Wahl treffen wie die danach geschriebenen Bytes. HotXLS löst all diese Namen in einem Durchgang auf, bevor es irgendetwas schreibt
Operandenklassen ändern, was Excel berechnet
Jedes Argument in einer BIFF8-Formel ist als Reference-, Value- oder Array-Operand getaggt, und Excel nimmt das Tag beim Wort. Taggt man das dritte Argument von AGGREGATE als Value, gibt Excel #VALUE! zurück. Taggt man das innere Ergebnis von SUM(SORTBY(…)) als Value, baut Excel eine implizite Intersection ein, zeigt SUM(@SORTBY(…)) und summiert eine einzige Zelle. Ein Writer muss diese Argumente so taggen, wie Excel es tut, was nicht immer die Klasse ist, die die Funktionsdokumentation suggeriert: Add-in-Funktionsargumente werden als References geschrieben, Dynamic-Array-Funktionen geben Arrays zurück, und XLOOKUP, TAKE, DROP, IFS und SWITCH geben References zurück, wenn sie dort sitzen, wo eine Reference erwartet wird. Falsche Klassen brechen die Datei nicht. Sie ändern die Antwort
VBA-Funktionen halten ein XLSB-Workbook binär
XLSB ergänzt einen dritten Speicherfall. Ein Macro-Workbook kann seine eigenen VBA-Function-Prozeduren aus Zellen aufrufen, und das Binärformat deklariert jede als einen Namen, der als Prozedur mit leerer Formel geflaggt ist; die Zelle ruft sie dann über dasselbe ptgName-plus-Funktion-255-Muster auf wie BIFF8. Die XML-Formate haben kein Äquivalent. Ein leerer definierter Name ist dort ungültig, und ein als #NAME? definierter Name würde die gleichnamige VBA-Funktion verdecken. HotXLS speichert so ein Workbook zurück nach XLSB, mit den Deklarationen intakt, und verweigert die Konvertierung in jedes andere Format, bevor ein Byte geschrieben ist. Fordern Sie den Konvertierungsreport an, bevor Sie konvertieren, nicht nachdem ein Speichern scheitert: TXLSXWorkbook.GetConversionReport liefert einen VBAFunctionNames-Eintrag, markiert als nicht unterstützt für diese Workbooks, und Code kann darauf verzweigen
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 // z. B. VBAFunctionNames
Book.SaveAs(ChangeFileExt(XlsbFile, '.edited.xlsb'),
xlsxExcelBinaryWorkbook)
else
Book.SaveAs(ChangeFileExt(XlsbFile, '.xlsx'), xlsxOpenXMLWorkbook);
finally
Book.Free;
end;
end;
Zeigt ein Workbook aus einem anderen Tool #NAME? bei einer Funktion, die Excel nachweislich kennt, laufen Sie zuerst DumpRawFormulas darauf: Ein nacktes NETWORKDAYS.INTL oder TEXTJOIN im <f>-Text ist die ganze Diagnose. Weisen Sie denselben Formeltext über Cell.Formula in der HotXLS Delphi Excel Component zu, bekommt die XLSX den Präfix, den Excel erwartet, während ein .xls-Speichern des Workbooks den versteckten Namen und den Funktion-255-Call bekommt