Un workbook generato da Delphi contiene =GAMMA(5) e =XIRR(B2:B9,A2:A9), e Excel lo apre con #NAME? nella cella di GAMMA e un risultato corretto nella cella di XIRR. In HotXLS, le funzioni che portano un prefisso _xlfn. in XLSX, oppure una chiamata basata su name in BIFF8 .xls, sono quelle che Excel salva allo stesso modo — una lista che prende le distanze dalla grammatica MS-XLSX
Perché Excel mostra #NAME? per GAMMA ma non per XIRR?
Il testo della formula è giusto; la memorizzazione no. Dentro un worksheet XLSX una formula è testo semplice in un elemento <f>, e una funzione aggiunta dopo Excel 2007 si scrive come _xlfn.NAME. Il prefisso dice al reader "questa è una funzione incorporata che forse non conosci ancora", così un Excel più vecchio mostra #NAME? senza scambiare la chiamata per un name definito dall'utente, e l'Excel corrente toglie il prefisso nella visualizzazione così gli utenti vedono =GAMMA(5). Lasciatelo fuori e l'Excel corrente va a cercare un defined name chiamato GAMMA, non lo trova, e mostra #NAME?. XIRR è più vecchia. Arrivò con l'Analysis ToolPak, Excel la scrive nuda, e una _xlfn.XIRR con prefisso è un token diverso da quello che Excel produce. GAMMA, GAUSS, PHI, SHEET, SHEETS e XOR vogliono tutti il prefisso, e così le otto funzioni che Excel 2013 aggiunse al set di ingegneria e numeri complessi (IMCOSH, IMCOT, IMCSC, IMCSCH, IMSEC, IMSECH, IMSINH e IMTAN), mentre le più vecchie IMSIN, IMSQRT e IMSUM restano nude. Le funzioni array solo-foglio portano un secondo prefisso, _xlfn._xlws., che MS-XLSX §2.2.2 riserva a FILTER, SORT e PY. In lettura HotXLS toglie entrambi i prefissi, così Cell.Formula restituisce il nome pulito della funzione
Quali funzioni Excel prefissa e la grammatica MS-XLSX lascia nude?
MS-XLSX §2.2.2 scrive la lista in ABNF come future-function-list = ("_xlfn." ("AGGREGATE" / "ACOT" / … / "Z.TEST")) / "ECMA.CEILING" / "ISO.CEILING" / "NETWORKDAYS.INTL" / "WORKDAY.INTL" / "FORECAST.ETS" / …. Letta alla lettera, la parentesi di chiusura dopo Z.TEST chiude il gruppo con prefisso, quindi tutto ciò che viene dopo è un nome nudo. Una seconda produzione, la lista ordinaria delle funzioni incorporate, include CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS e SWITCH accanto a SUM e VLOOKUP. Excel non è d'accordo su entrambi i punti:
| Funzione | Dove la mette la grammatica MS-XLSX | Cosa scrive Excel |
|---|---|---|
| NETWORKDAYS.INTL, WORKDAY.INTL | Dopo il gruppo _xlfn., quindi nude | _xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL |
| ISO.CEILING | Dopo il gruppo _xlfn., quindi nuda | _xlfn.ISO.CEILING |
| FORECAST.LINEAR e la famiglia FORECAST.ETS | Dopo il gruppo _xlfn., quindi nude | Con prefisso, per esempio _xlfn.FORECAST.LINEAR |
| CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCH | Lista ordinaria delle funzioni incorporate | Con prefisso, per esempio _xlfn.TEXTJOIN |
| XIRR, XNPV, ERFC | Non nella future-function list | Nude |
Uno writer generato dalla grammatica produce quindi NETWORKDAYS.INTL(…) senza prefisso, e Excel risponde con #NAME? in un file che valida contro la sintassi pubblicata. Prendete la lista dai file Excel salvati e trattate l'ABNF come un punto di partenza. HotXLS lo fa per ogni funzione che calcola, così la CONCAT con prefisso e la NETWORKDAYS.INTL con prefisso escono entrambe come le scrive Excel. Il codice seguente salva lo stesso tipo di formule in entrambi i modi e ne rilegge una:
uses
SysUtils, lxCalc, lxHandle, lxHandleX;
procedure SaveModernFunctions;
var
XBook, XCheck: TXLSXWorkbook;
XSheet: TXLSXWorksheet;
Book: TXLSWorkbook;
Sheet: TXLSWorksheet;
begin
// XLSX: i prefissi si aggiungono al salvataggio e si tolgono al caricamento
XBook := TXLSXWorkbook.Create;
try
XSheet := XBook.Sheets.Add('Calc');
XSheet.Cells[1, 1].Value := 46000.0; // un seriale data
XSheet.Cells[1, 2].Formula := 'EDATE(A1,1)'; // ToolPak: nessun prefisso
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');
// Atteso: NETWORKDAYS.INTL(A1,A1+30,11), prefisso già rimosso
Writeln(string(XCheck.Sheets[1].Cells[1, 4].Formula));
finally
XCheck.Free;
end;
// BIFF8: le stesse chiamate diventano name token più la voce 255 del Ftab
Book := TXLSWorkbook.Create;
try
Sheet := Book.Sheets.Add;
Sheet.Cells[1, 1].Value := 46000.0;
Sheet.Cells[1, 2].Formula := '=EDATE(A1,1)'; // EXTERNNAME di add-in
Sheet.Cells[1, 3].Formula := '=ISOWEEKNUM(A1)'; // NAME _xlfn nascosto
if Book.SaveAs('modern.xls') <> lxOk then // BIFF8 per default
raise Exception.Create('XLS save failed');
finally
Book.Free;
end;
end;
Entrambi i file dovrebbero aprirsi nell'Excel corrente con ogni funzione intatta. Per vedere i prefissi, aprite il pacchetto come ZIP ed elencate il testo <f> grezzo della parte worksheet; per modern.xlsx la routine qui sotto dovrebbe stampare EDATE(A1,1), _xlfn.GAMMA(5), _xlfn.NETWORKDAYS.INTL(A1,A1+30,11) e _xlfn.TEXTJOIN("-",TRUE,B1:D1). Le virgolette escono come " perché la routine stampa il testo XML senza togliere l'escaping, il che per una diagnosi non è un problema. Funziona su file di qualunque produttore, quindi è il controllo più rapido quando un workbook di terze parti mostra #NAME? su una funzione che la vostra copia di Excel conosce
uses
SysUtils, Classes, Zip;
// Stampa ogni elemento <f> di una parte worksheet, prefissi inclusi
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>: non è una formula di cella
P := Pos('>', Xml, P) + 1; // salta gli attributi tipo t="shared"
if Xml[P - 2] = '/' then
Continue; // <f .../> vuoto di una formula condivisa
Close := Pos('</f>', Xml, P);
if Close = 0 then
Break;
Writeln(Copy(Xml, P, Close - P)); // XML escaped: " & <
P := Close + 4;
end;
end;
Come chiama una funzione un file .xls BIFF8 che per essa non ha numero?
Le formule BIFF8 non contengono affatto nomi di funzioni. Ogni funzione incorporata è un numero in una tabella funzioni fissa (Ftab), e una formula memorizza ptgFunc o ptgFuncVar con quel numero. Le funzioni più nuove non hanno numero. MS-XLS instrada quelle chiamate attraverso la voce 0x00FF del Ftab, "User Defined Function": un ptgFuncVar con indice di funzione 255 il cui primo argomento è un name token che punta alla funzione vera. Il conteggio degli argomenti memorizzato nel token è di uno più alto di quello che l'utente ha digitato, perché quel name in testa conta come argomento. Quale name token scriva Excel dipende dalla famiglia della funzione:
| Famiglia di funzioni | Name token | A cosa punta il name |
|---|---|---|
| Funzioni dell'Analysis ToolPak: EDATE, EOMONTH, WORKDAY, NETWORKDAYS, RANDBETWEEN, XIRR … | ptgNameX | Un record EXTERNNAME nel SUPBOOK add-in del workbook |
Funzioni che Excel scrive con _xlfn.: XLOOKUP, ISOWEEKNUM, IFS, CONCAT … | ptgName | Un record NAME nascosto chiamato _xlfn.XLOOKUP (o _xlfn._xlws.FILTER) la cui definizione è #NAME? |
| Funzioni che la copia di Excel che salva non riconosce | ptgName | Un normale record NAME del workbook con una definizione vuota |
Il SUPBOOK add-in della prima riga è lo stesso record che un reader incontra quando ordina i riferimenti esterni, e il modo in cui i record BIFF SUPBOOK classificano i riferimenti esterni mostra dove siede tra gli altri tipi. Non tutto ciò che sembra un add-in passa di qui. DATEDIF, USDOLLAR, FINDB e ISPMT, tra gli altri, hanno numeri Ftab nativi e vengono memorizzate come normali chiamate di funzione. La terza riga è quella fragile: una funzione troppo nuova per l'Excel che ha salvato il file, come le REGEXTEST e REGEXEXTRACT, diventa una chiamata attraverso un name qualunque senza definizione. Un reader che butta via i name a definizione vuota come spazzatura lascia la formula che punta a un indice di name che non esiste più, e Excel si rifiuta di aprire l'intero file che rimette a letto. HotXLS tiene un name del genere con una definizione #NAME? e riscrive il record originale al salvataggio
Ogni name deve esistere prima del primo worksheet
L'ordine è il vincolo successivo. Un file BIFF8 scrive i suoi global — la tabella NAME ed EXTERNSHEET — prima di qualunque substream di worksheet, quindi ogni name a cui una formula punterà deve essere noto prima che la prima cella venga scritta. Uno writer che incontra XLOOKUP a metà del foglio tre non può più aggiungere il name nascosto che gli serve. Se producete BIFF8 voi, scansite ogni sorgente di formula prima di scrivere i global: formule di cella, name a livello di workbook e di foglio, regole di data validation e conditional formatting. Le dimensioni discendono dai token. Una name call costa 9 byte sopra gli argomenti (5 per ptgName, 4 per ptgFuncVar) e una chiamata add-in 11 (7 per ptgNameX, 4 per ptgFuncVar), e le lunghezze dei record calcolate prima della scrittura devono usare la stessa scelta di token dei byte scritti dopo. HotXLS risolve tutti questi name in un solo passaggio prima di scrivere qualcosa
Le classi degli operandi cambiano ciò che Excel calcola
Ogni argomento in una formula BIFF8 è etichettato come operando reference, value o array, e Excel prende l'etichetta in parola. Etichettate come value il terzo argomento di AGGREGATE e Excel restituisce #VALUE!. Etichettate come value il risultato interno di SUM(SORTBY(…)) e Excel inserisce un'intersezione implicita, mostra SUM(@SORTBY(…)) e somma una singola cella. Uno writer deve etichettare questi argomenti come fa Excel, il che non è sempre la classe che la documentazione della funzione suggerisce: gli argomenti delle funzioni add-in si scrivono come reference, le funzioni dynamic-array restituiscono array, e XLOOKUP, TAKE, DROP, IFS e SWITCH restituiscono reference quando sedono dove ci si aspetta un reference. Le classi sbagliate non rompono il file. Cambiano la risposta
Le funzioni VBA tengono un workbook binario XLSB
XLSB aggiunge un terzo caso di memorizzazione. Un workbook macro può chiamare dalle celle le proprie procedure VBA Function, e il formato binario dichiara ciascuna come un name contrassegnato come procedura con formula vuota; la cella poi la chiama attraverso lo stesso schema ptgName più funzione 255 di BIFF8. I formati XML non hanno un equivalente. Un defined name vuoto lì non è valido, e un name definito come #NAME? nasconderebbe la funzione VBA omonima. HotXLS salva un workbook simile di nuovo in XLSB con le dichiarazioni intatte, e si rifiuta di convertirlo a qualunque altro formato prima di scrivere un byte. Chiedete il report di conversione prima di convertire, non dopo che un salvataggio è fallito: TXLSXWorkbook.GetConversionReport restituisce una voce VBAFunctionNames marcata non supportata per questi workbook, e il codice può ramificarci sopra
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 // per esempio VBAFunctionNames
Book.SaveAs(ChangeFileExt(XlsbFile, '.edited.xlsb'),
xlsxExcelBinaryWorkbook)
else
Book.SaveAs(ChangeFileExt(XlsbFile, '.xlsx'), xlsxOpenXMLWorkbook);
finally
Book.Free;
end;
end;
Quando un workbook di un altro strumento mostra #NAME? su una funzione che Excel evidentemente conosce, fatelo girare per DumpRawFormulas prima di ogni altra cosa: una NETWORKDAYS.INTL o una TEXTJOIN nude nel testo <f> sono l'intera diagnosi. Assegnate quello stesso testo di formula attraverso Cell.Formula nel componente Excel HotXLS per Delphi e l'XLSX prende il prefisso che Excel si aspetta, mentre un salvataggio .xls del workbook prende il name nascosto e la chiamata alla funzione 255