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
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:
| Functie | Waar de MS-XLSX-grammatica hem plaatst | Wat Excel wegschrijft |
|---|---|---|
| NETWORKDAYS.INTL, WORKDAY.INTL | Na de _xlfn.-groep, dus kaal | _xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL |
| ISO.CEILING | Na de _xlfn.-groep, dus kaal | _xlfn.ISO.CEILING |
| FORECAST.LINEAR en de FORECAST.ETS-familie | Na de _xlfn.-groep, dus kaal | Geprefixed, bijvoorbeeld _xlfn.FORECAST.LINEAR |
| CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCH | Gewone lijst met ingebouwde functies | Geprefixed, bijvoorbeeld _xlfn.TEXTJOIN |
| XIRR, XNPV, ERFC | Niet in de future-function-lijst | Kaal |
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("-",TRUE,B1:D1) af te drukken. De aanhalingstekens komen eruit als " 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: " & <
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:
| Functiefamilie | Naamtoken | Waar de naam naar wijst |
|---|---|---|
| Analysis ToolPak-functies: EDATE, EOMONTH, WORKDAY, NETWORKDAYS, RANDBETWEEN, XIRR … | ptgNameX | Een EXTERNNAME-record in de add-in-SUPBOOK van de werkmap |
Functies die Excel met _xlfn. wegschrijft: XLOOKUP, ISOWEEKNUM, IFS, CONCAT … | ptgName | Een verborgen NAME-record genaamd _xlfn.XLOOKUP (of _xlfn._xlws.FILTER) waarvan de definitie #NAME? is |
| Functies die de opslagende Excel-editie niet herkent | ptgName | Een 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
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
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