Technisch artikel

_xlfn-prefixen en BIFF8-naamaanroepen voor nieuwe functies

Een door Delphi gegenereerde werkmap bevat =GAMMA(5) en =XIRR(B2:B9,A2:A9), en Excel opent hem met #NAME? in de GAMMA-cel en een correct resultaat in de XIRR-cel. In HotXLS zijn de functies die een _xlfn.-prefix meedragen in XLSX, of een call via een naam in BIFF8 .xls, precies degene die Excel zelf zo opslaat — een lijst die afwijkt van de MS-XLSX-grammatica

Waarom toont Excel #NAME? voor GAMMA maar niet voor XIRR?

De formuliertekst klopt; de opslag niet. Binnen een XLSX-werkblad is een formule plain text in een <f>-element, en een functie die na Excel 2007 is toegevoegd wordt geschreven als _xlfn.NAME. De prefix vertelt een lezer "this is a built-in function you may not know yet", dus een oudere Excel toont #NAME? zonder de call voor een door de gebruiker gedefinieerde naam aan te zien, en de huidige Excel stript de prefix bij weergave zodat gebruikers =GAMMA(5) zien. Laat hem weg en de huidige Excel gaat op zoek naar een gedefinieerde naam GAMMA, vindt die niet en toont #NAME?. XIRR is ouder. Hij kwam met de Analysis ToolPak mee, Excel schrijft hem naakt weg, en een geprefixede _xlfn.XIRR is een ander token dan wat Excel zelf produceert. GAMMA, GAUSS, PHI, SHEET, SHEETS en XOR hebben allemaal de prefix nodig, en ook de acht functies die Excel 2013 aan de engineering- en complexe-getallenset toevoegde (IMCOSH, IMCOT, IMCSC, IMCSCH, IMSEC, IMSECH, IMSINH en IMTAN), terwijl de oudere IMSIN, IMSQRT en IMSUM naakt blijven. Alleen-op-werkblad-arrayfuncties dragen een tweede prefix, _xlfn._xlws., dat MS-XLSX §2.2.2 reserveert voor FILTER, SORT en PY. Bij het lezen verwijdert HotXLS beide prefixes, dus Cell.Formula geeft de schone functienaam terug

Opslagroutes voor nieuwe Excel-functies in HotXLS voor Delphi: GAMMA draagt het _xlfn-prefix terwijl ToolPak-XIRR naakt blijft
het prefix markeert een ingebouwde functie die de lezer nog niet hoeft te kennen, dus de huidige Excel stript hem en oudere versies antwoorden met een naamfout

Welke functies prefixed Excel die de MS-XLSX-grammatica naakt laat?

MS-XLSX §2.2.2 spelt de lijst in ABNF als future-function-list = ("_xlfn." ("AGGREGATE" / "ACOT" / … / "Z.TEST")) / "ECMA.CEILING" / "ISO.CEILING" / "NETWORKDAYS.INTL" / "WORKDAY.INTL" / "FORECAST.ETS" / …. Letterlijk gelezen eindigt het sluithaakje na Z.TEST de geprefixede groep, dus alles daarna is een kale naam. Een tweede productie, de gewone lijst met ingebouwde functies, noemt CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS en SWITCH naast SUM en VLOOKUP. Excel spreekt op beide punten een andere taal:

FunctieWaar de MS-XLSX-grammatica hem plaatstWat Excel wegschrijft
NETWORKDAYS.INTL, WORKDAY.INTLNa de _xlfn.-groep, dus kaal_xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL
ISO.CEILINGNa de _xlfn.-groep, dus kaal_xlfn.ISO.CEILING
FORECAST.LINEAR en de FORECAST.ETS-familieNa de _xlfn.-groep, dus kaalGeprefixed, bijvoorbeeld _xlfn.FORECAST.LINEAR
CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCHGewone lijst met ingebouwde functiesGeprefixed, bijvoorbeeld _xlfn.TEXTJOIN
XIRR, XNPV, ERFCNiet in de future-function-lijstKaal

Een writer die uit de grammatica is gegenereerd produceert dus NETWORKDAYS.INTL(…) zonder prefix, en Excel antwoordt met #NAME? in een bestand dat tegen de gepubliceerde syntaxis valideert. Neem de lijst uit opgeslagen Excel-bestanden en behandel de ABNF als startpunt. HotXLS doet dat voor elke functie die hij berekent, dus de geprefixede CONCAT en de geprefixede NETWORKDAYS.INTL komen er allebei uit zoals Excel ze wegschrijft. De volgende code slaat dezelfde soort formules op beide manieren op en leest er één terug:

uses
  SysUtils, lxCalc, lxHandle, lxHandleX;

procedure SaveModernFunctions;
var
  XBook, XCheck: TXLSXWorkbook;
  XSheet: TXLSXWorksheet;
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  // XLSX: prefixes worden bij save toegevoegd en bij load gestript
  XBook := TXLSXWorkbook.Create;
  try
    XSheet := XBook.Sheets.Add('Calc');
    XSheet.Cells[1, 1].Value := 46000.0;                // een datumserial
    XSheet.Cells[1, 2].Formula := 'EDATE(A1,1)';       // ToolPak: geen prefix
    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');
    // Verwacht: NETWORKDAYS.INTL(A1,A1+30,11), prefix al verwijderd
    Writeln(string(XCheck.Sheets[1].Cells[1, 4].Formula));
  finally
    XCheck.Free;
  end;

  // BIFF8: dezelfde calls worden naamtokens plus Ftab-entry 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)';    // verborgen _xlfn NAME
    if Book.SaveAs('modern.xls') <> lxOk then          // standaard BIFF8
      raise Exception.Create('XLS save failed');
  finally
    Book.Free;
  end;
end;

Beide bestanden horen in de huidige Excel te openen met elke functie intact. Om de prefixes te zien: open het pakket als ZIP en som de rauwe <f>-tekst van het worksheetonderdeel op; voor modern.xlsx hoort de routine hieronder EDATE(A1,1), _xlfn.GAMMA(5), _xlfn.NETWORKDAYS.INTL(A1,A1+30,11) en _xlfn.TEXTJOIN(&quot;-&quot;,TRUE,B1:D1) af te drukken. De aanhalingstekens komen eruit als &quot; omdat de routine XML-tekst print zonder te unescapen, wat voor een diagnose geen kwaad kan. Hij werkt op bestanden van elke producent, dus het is de snelste check wanneer een werkmap van derden #NAME? toont bij een functie die uw exemplaar van Excel wél kent

uses
  SysUtils, Classes, Zip;

// Print elk <f>-element van één worksheetonderdeel, prefixes incluis
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>: geen celformule
    P := Pos('>', Xml, P) + 1;               // sla attributen als t="shared" over
    if Xml[P - 2] = '/' then
      Continue;                                // leeg <f .../> van een gedeelde formule
    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;

Hoe roept een BIFF8 .xls-bestand een functie aan waarvoor het geen nummer heeft?

BIFF8-formules bevatten helemaal geen functienamen. Elke ingebouwde functie is een nummer in een vaste functietabel (Ftab), en een formule slaat ptgFunc of ptgFuncVar met dat nummer op. Nieuwere functies hebben geen nummer. MS-XLS leidt die calls om via Ftab-entry 0x00FF, "User Defined Function": een ptgFuncVar met functie-index 255 waarvan het eerste argument een naamtoken is dat naar de echte functie wijst. Het argumentaantal in het token staat er één hoger dan wat de gebruiker typte, want die voorloopnaam telt als argument. Welk naamtoken Excel wegschrijft hangt af van de familie van de functie:

FunctiefamilieNaamtokenWaar de naam naar wijst
Analysis ToolPak-functies: EDATE, EOMONTH, WORKDAY, NETWORKDAYS, RANDBETWEEN, XIRR …ptgNameXEen EXTERNNAME-record in de add-in-SUPBOOK van de werkmap
Functies die Excel met _xlfn. wegschrijft: XLOOKUP, ISOWEEKNUM, IFS, CONCAT …ptgNameEen verborgen NAME-record genaamd _xlfn.XLOOKUP (of _xlfn._xlws.FILTER) waarvan de definitie #NAME? is
Functies die de opslagende Excel-editie niet herkentptgNameEen gewoon NAME-record van de werkmap met een lege definitie

De add-in-SUPBOOK in de eerste rij is hetzelfde record dat een lezer tegenkomt bij het sorteren van externe referenties, en de manier waarop BIFF SUPBOOK-records externe referenties classificeren laat zien waar hij tussen de andere soorten zit. Niet alles wat op een add-in lijkt gaat deze weg. DATEDIF, USDOLLAR, FINDB en ISPMT hebben, onder meer, eigen Ftab-nummers en worden als gewone functie-aanroepen opgeslagen. De derde rij is de kwetsbare: een functie te nieuw voor de Excel die het bestand opsloeg, zoals REGEXTEST en REGEXEXTRACT, wordt een call via een gewone naam zonder definitie. Een lezer die naamdefinities zonder inhoud als afval weggooit, laat de formule wijzen naar een naamindex die niet meer bestaat, en Excel weigert het hele bestand te openen dat hij terugschrijft. HotXLS houdt zo'n naam vast met een #NAME?-definitie en schrijft het oorspronkelijke record bij het opslaan opnieuw weg

Naamcall-routes in BIFF8 in HotXLS voor Delphi: ToolPak-functies rijden op ptgNameX terwijl _xlfn-functies bellen via een verborgen naamrecord en functie 255
een formule in een .xls-bestand slaat nummers op, dus leent een nieuwe functie de user-defined-plek en wijst naar een naamrecord dat de round trip moet overleven

Elke naam moet bestaan vóór het eerste werkblad

Volgorde is de volgende beperking. Een BIFF8-bestand schrijft zijn globals — de NAME-tabel en EXTERNSHEET — vóór elke worksheet-substream, dus elke naam waar een formule naar zal wijzen moet bekend zijn voordat de eerste cel wordt weggeschreven. Een writer die halverwege blad drie XLOOKUP tegenkomt, kan de verborgen naam die hij nodig heeft niet meer toevoegen. Produceert u zelf BIFF8, scan dan elke formulebron voordat u de globals schrijft: celformules, namen op werkmap- en bladniveau, data-validatieregels en conditionele opmaak. De groottes volgen uit de tokens. Een naamcall kost 9 bytes bovenop de argumenten (5 voor ptgName, 4 voor ptgFuncVar) en een add-in-call 11 (7 voor ptgNameX, 4 voor ptgFuncVar), en recordlengtes die vóór het schrijven worden berekend moeten dezelfde tokenkeuze gebruiken als de bytes die erna worden weggeschreven. HotXLS lost al deze namen in één keer op voordat hij ook maar iets schrijft

Schrijfvolgorde van een BIFF8-bestand in HotXLS voor Delphi: naamrecords in de globals gaan aan elk werkblad vooraf, dus formulebronnen worden eerst gescand
een naam die halverwege blad drie opduikt kan niet meer bij de globals, dus elke formulebron wordt gescand voordat de eerste byte wordt weggeschreven

Operandklassen veranderen wat Excel berekent

Elk argument in een BIFF8-formule is getagd als reference-, value- of array-operand, en Excel neemt de tag letterlijk. Tag het derde argument van AGGREGATE als value en Excel geeft #VALUE!. Tag het binnenste resultaat van SUM(SORTBY(…)) als value en Excel voegt een impliciete intersectie in, toont SUM(@SORTBY(…)) en telt één enkele cel op. Een writer moet deze argumenten taggen zoals Excel dat doet, wat niet altijd de klasse is die de documentatie van de functie suggereert: argumenten van add-in-functies worden als referenties weggeschreven, dynamic-array-functies geven arrays terug, en XLOOKUP, TAKE, DROP, IFS en SWITCH geven referenties terug als ze staan waar een referentie wordt verwacht. Verkeerde klassen breken het bestand niet. Ze veranderen het antwoord

VBA-functies houden een XLSB-werkmap binair

XLSB voegt een derde opslaggeval toe. Een macrowerkmap kan vanuit cellen zijn eigen VBA-Function-procedures aanroepen, en het binaire formaat declareert elke procedure als een naam die als procedure is gemarkeerd met een lege formule; de cel roept hem dan aan via hetzelfde ptgName-plus-functie-255-patroon als BIFF8. De XML-formaten hebben geen equivalent. Een lege gedefinieerde naam is daar niet geldig, en een naam gedefinieerd als #NAME? zou de VBA-functie met dezelfde naam verbergen. HotXLS slaat zo'n werkmap terug op als XLSB met de declaraties intact, en weigert conversie naar elk ander formaat voordat er een byte is weggeschreven. Vraag het conversierapport voordat u converteert, niet nadat een save faalt: TXLSXWorkbook.GetConversionReport geeft een VBAFunctionNames-entry terug die voor deze werkmappen als unsupported is gemarkeerd, en code kan daarop aftakken

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

Toont een werkmap uit een andere tool #NAME? bij een functie die Excel ondubbelzinnig kent, draai er dan eerst DumpRawFormulas op: een kale NETWORKDAYS.INTL of TEXTJOIN in de <f>-tekst is de hele diagnose. Wijs diezelfde formuliertekst toe via Cell.Formula in de HotXLS Delphi Excel-component en de XLSX krijgt het prefix dat Excel verwacht, terwijl een .xls-save van de werkmap de verborgen naam en de functie-255-call krijgt