Technischer Artikel

_xlfn-Präfixe und BIFF8-Name-Calls für neue Funktionen

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

Speicherrouten für neue Excel-Funktionen in HotXLS für Delphi: GAMMA trägt den _xlfn-Präfix, während ToolPak-XIRR ohne bleibt
Der Präfix markiert ein Built-in, das der Reader vielleicht noch nicht kennt, also schneidet es das aktuelle Excel ab und ältere Versionen antworten mit einem Namensfehler

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:

FunktionWo die MS-XLSX-Grammatik sie ablegtWas Excel schreibt
NETWORKDAYS.INTL, WORKDAY.INTLNach der _xlfn.-Gruppe, also ohne Präfix_xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL
ISO.CEILINGNach der _xlfn.-Gruppe, also ohne Präfix_xlfn.ISO.CEILING
FORECAST.LINEAR und die FORECAST.ETS-FamilieNach der _xlfn.-Gruppe, also ohne PräfixPräfixiert, zum Beispiel _xlfn.FORECAST.LINEAR
CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCHGewöhnliche Built-in-FunktionslistePräfixiert, zum Beispiel _xlfn.TEXTJOIN
XIRR, XNPV, ERFCNicht in der Future-Function-ListeOhne 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(&quot;-&quot;,TRUE,B1:D1) drucken. Die Anführungszeichen kommen als &quot; 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: &quot; &amp; &lt;
    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:

FunktionsfamilieName-TokenWorauf der Name zeigt
Analysis-ToolPak-Funktionen: EDATE, EOMONTH, WORKDAY, NETWORKDAYS, RANDBETWEEN, XIRR …ptgNameXEin EXTERNNAME-Record im Add-in-SUPBOOK des Workbooks
Funktionen, die Excel mit _xlfn. schreibt: XLOOKUP, ISOWEEKNUM, IFS, CONCAT …ptgNameEin versteckter NAME-Record namens _xlfn.XLOOKUP (oder _xlfn._xlws.FILTER) mit der Definition #NAME?
Funktionen, die die speichernde Excel-Kopie nicht kenntptgNameEin 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

BIFF8-Name-Call-Routen in HotXLS für Delphi: ToolPak-Funktionen reiten auf ptgNameX, während _xlfn-Funktionen über einen versteckten Name-Record und Funktion 255 aufrufen
Eine Formel in einer .xls-Datei speichert Nummern, also leiht sich eine neue Funktion den User-Defined-Slot und zeigt auf einen Name-Record, der den Roundtrip überleben muss

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

Schreibreihenfolge einer BIFF8-Datei in HotXLS für Delphi: Name-Records in den Globals gehen jedem Worksheet voraus, also werden Formelquellen zuerst gescannt
Ein Name, der auf halbem Weg durch Sheet drei auftaucht, kann nicht mehr zu den Globals stoßen, also wird jede Formelquelle gescannt, bevor das erste Byte geschrieben ist

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