Skoroszyt wygenerowany w Delphi trzyma =GAMMA(5) i =XIRR(B2:B9,A2:A9), a Excel otwiera go z #NAME? w komórce GAMMA i poprawnym wynikiem w komórce XIRR. W HotXLS funkcje, które niosą prefiks _xlfn. w XLSX albo wywołanie oparte na nazwie w BIFF8 .xls, to te, które Excel zapisuje tak samo — lista odbiegająca od gramatyki MS-XLSX
Dlaczego Excel pokazuje #NAME? dla GAMMA, ale nie dla XIRR?
Tekst formuły jest prawidłowy; zapis nie. Wewnątrz arkusza XLSX formuła to czysty tekst w elemencie <f>, a funkcja dodana po Excelu 2007 jest zapisywana jako _xlfn.NAME. Prefiks mówi czytnikowi „to wbudowana funkcja, której jeszcze możesz nie znać”, więc starszy Excel pokazuje #NAME? bez brania wywołania za zdefiniowaną nazwę, a obecny Excel zdejmuje prefiks przy wyświetlaniu, więc użytkownicy widzą =GAMMA(5). Zostaw go, a obecny Excel ruszy szukać zdefiniowanej nazwy GAMMA, nie znajdzie i pokaże #NAME?. XIRR jest starszy. Przyszedł z Analysis ToolPak, Excel zapisuje go nago, a oprefiksowany _xlfn.XIRR to inny token niż ten, który produkuje Excel. GAMMA, GAUSS, PHI, SHEET, SHEETS i XOR wszystkie potrzebują prefiksu, i osiem funkcji dodanych przez Excela 2013 do zbioru inżynierskiego i liczb zespolonych (IMCOSH, IMCOT, IMCSC, IMCSCH, IMSEC, IMSECH, IMSINH i IMTAN) też, podczas gdy starsze IMSIN, IMSQRT i IMSUM zostają nagie. Funkcje tablicowe tylko dla arkusza niosą drugi prefiks, _xlfn._xlws., który MS-XLSX §2.2.2 rezerwuje dla FILTER, SORT i PY. Przy czytaniu HotXLS zdejmuje oba prefiksy, więc Cell.Formula zwraca czystą nazwę funkcji
Które funkcje Excel oprefiksowuje, a gramatyka MS-XLSX zostawia nagie?
MS-XLSX §2.2.2 wylicza listę w ABNF jako future-function-list = ("_xlfn." ("AGGREGATE" / "ACOT" / … / "Z.TEST")) / "ECMA.CEILING" / "ISO.CEILING" / "NETWORKDAYS.INTL" / "WORKDAY.INTL" / "FORECAST.ETS" / …. Czytane dosłownie, nawias zamykający po Z.TEST kończy grupę oprefiksowaną, więc wszystko po nim to goła nazwa. Druga produkcja, zwykła lista funkcji wbudowanych, wymienia CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS i SWITCH obok SUM i VLOOKUP. Excel rozmija się z oboma punktami:
| Funkcja | Gdzie stawia ją gramatyka MS-XLSX | Co zapisuje Excel |
|---|---|---|
| NETWORKDAYS.INTL, WORKDAY.INTL | Za grupą _xlfn., więc nago | _xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL |
| ISO.CEILING | Za grupą _xlfn., więc nago | _xlfn.ISO.CEILING |
| FORECAST.LINEAR i rodzina FORECAST.ETS | Za grupą _xlfn., więc nago | Prefiksowana, na przykład _xlfn.FORECAST.LINEAR |
| CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCH | Zwykła lista funkcji wbudowanych | Prefiksowana, na przykład _xlfn.TEXTJOIN |
| XIRR, XNPV, ERFC | Nie ma ich na liście funkcji przyszłych | Nago |
Pisarz wygenerowany z gramatyki produkuje więc NETWORKDAYS.INTL(…) bez prefiksu, a Excel odpowiada #NAME? w pliku, który przechodzi walidację wobec opublikowanej składni. Bierz listę z zapisanych plików Excela i traktuj ABNF jako punkt startowy. HotXLS tak robi dla każdej liczonej funkcji, więc oprefiksowany CONCAT i oprefiksowany NETWORKDAYS.INTL wychodzą tak, jak zapisuje je Excel. Poniższy kod zapisuje ten sam rodzaj formuł w obie strony i wczytuje jedną z powrotem:
uses
SysUtils, lxCalc, lxHandle, lxHandleX;
procedure SaveModernFunctions;
var
XBook, XCheck: TXLSXWorkbook;
XSheet: TXLSXWorksheet;
Book: TXLSWorkbook;
Sheet: TXLSWorksheet;
begin
// XLSX: prefiksy są dodawane przy zapisie i zdejmowane przy wczytaniu
XBook := TXLSXWorkbook.Create;
try
XSheet := XBook.Sheets.Add('Calc');
XSheet.Cells[1, 1].Value := 46000.0; // numer seryjny daty
XSheet.Cells[1, 2].Formula := 'EDATE(A1,1)'; // ToolPak: bez prefiksu
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');
// Oczekiwane: NETWORKDAYS.INTL(A1,A1+30,11), prefiks już zdjęty
Writeln(string(XCheck.Sheets[1].Cells[1, 4].Formula));
finally
XCheck.Free;
end;
// BIFF8: te same wywołania stają się tokenami nazw plus wpis Ftab 255
Book := TXLSWorkbook.Create;
try
Sheet := Book.Sheets.Add;
Sheet.Cells[1, 1].Value := 46000.0;
Sheet.Cells[1, 2].Formula := '=EDATE(A1,1)'; // EXTERNNAME dodatku
Sheet.Cells[1, 3].Formula := '=ISOWEEKNUM(A1)'; // ukryty NAME _xlfn
if Book.SaveAs('modern.xls') <> lxOk then // BIFF8 domyślnie
raise Exception.Create('XLS save failed');
finally
Book.Free;
end;
end;
Oba pliki powinny otworzyć się w obecnym Excelu z każdą funkcją nietkniętą. Żeby zobaczyć prefiksy, otwórz pakiet jako ZIP i wylistuj surowy tekst <f> części arkusza; dla modern.xlsx poniższa procedura powinna wydrukować EDATE(A1,1), _xlfn.GAMMA(5), _xlfn.NETWORKDAYS.INTL(A1,A1+30,11) i _xlfn.TEXTJOIN("-",TRUE,B1:D1). Cudzysłowy wychodzą jako ", bo procedura drukuje tekst XML bez od-escapowania, co dla diagnozy jest nieszkodliwe. Działa na plikach od każdego producenta, więc to najszybsza kontrola, gdy cudzy skoroszyt pokazuje #NAME? w funkcji, którą twój egzemplarz Excela obsługuje
uses
SysUtils, Classes, Zip;
// Drukuj każdy element <f> jednej części arkusza, łącznie z prefiksami
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>: to nie formuła komórki
P := Pos('>', Xml, P) + 1; // przeskocz atrybuty typu t="shared"
if Xml[P - 2] = '/' then
Continue; // puste <f .../> formuły współdzielonej
Close := Pos('</f>', Xml, P);
if Close = 0 then
Break;
Writeln(Copy(Xml, P, Close - P)); // z escapingiem XML: " & <
P := Close + 4;
end;
end;
Jak plik .xls BIFF8 woła funkcję, na którą nie ma numeru?
Formuły BIFF8 nie zawierają nazw funkcji wcale. Każda funkcja wbudowana to numer w stałej tabeli funkcji (Ftab), a formuła trzyma ptgFunc albo ptgFuncVar z tym numerem. Nowsze funkcje nie mają numeru. MS-XLS prowadzi te wywołania przez wpis Ftab 0x00FF, „funkcję zdefiniowaną przez użytkownika”: ptgFuncVar z indeksem funkcji 255, którego pierwszy argument to token nazwy wskazujący prawdziwą funkcję. Liczba argumentów zapisana w tokenie jest o jeden większa od tego, co wpisał użytkownik, bo ta wiodąca nazwa liczy się jako argument. Który token nazwy zapisze Excel, zależy od rodziny funkcji:
| Rodzina funkcji | Token nazwy | Na co wskazuje nazwa |
|---|---|---|
| Funkcje Analysis ToolPak: EDATE, EOMONTH, WORKDAY, NETWORKDAYS, RANDBETWEEN, XIRR … | ptgNameX | Rekord EXTERNNAME w SUPBOOK dodatku skoroszytu |
Funkcje, które Excel zapisuje z _xlfn.: XLOOKUP, ISOWEEKNUM, IFS, CONCAT … | ptgName | Ukryty rekord NAME o nazwie _xlfn.XLOOKUP (albo _xlfn._xlws.FILTER) o definicji #NAME? |
| Funkcje, których nie rozpoznaje zapisująca kopia Excela | ptgName | Zwykły rekord NAME skoroszytu z pustą definicją |
SUPBOOK dodatku z pierwszego wiersza to ten sam rekord, jaki czytnik spotyka przy sortowaniu odwołań zewnętrznych, a sposób, w jaki rekordy BIFF SUPBOOK klasyfikują odwołania zewnętrzne, pokazuje, gdzie siedzi wśród pozostałych rodzajów. Nie wszystko, co wygląda jak dodatek, idzie tą drogą. DATEDIF, USDOLLAR, FINDB i ISPMT, między innymi, mają natywne numery Ftab i są zapisywane jako zwykłe wywołania funkcji. Trzeci wiersz jest ten kruchy: funkcja zbyt nowa dla Excela, który zapisał plik, jak REGEXTEST i REGEXEXTRACT, staje się wywołaniem przez zwykłą nazwę bez definicji. Czytnik, który wyrzuca nazwy o pustej definicji jako śmieci, zostawia formułę wskazującą indeks nazwy, który już nie istnieje, a Excel odmawia otwarcia całego pliku, który z powrotem zapisuje. HotXLS trzyma taką nazwę z definicją #NAME? i zapisuje oryginalny rekord ponownie przy zapisie
Każda nazwa musi istnieć przed pierwszym arkuszem
Kolejność to następne ograniczenie. Plik BIFF8 zapisuje swoje globals — tabelę NAME i EXTERNSHEET — przed jakimkolwiek substreamem arkusza, więc każda nazwa, na którą formuła będzie wskazywać, musi być znana, zanim pierwsza komórka zostanie zapisana. Pisarz, który spotyka XLOOKUP w połowie trzeciego arkusza, nie doda już ukrytej nazwy, której potrzebuje. Jeśli sam produkujesz BIFF8, przeskanuj każde źródło formuł przed zapisem globals: formuły komórek, nazwy na poziomie skoroszytu i arkusza, reguły walidacji danych i formatowanie warunkowe. Rozmiary wynikają z tokenów. Wywołanie nazwy kosztuje 9 bajtów ponad argumenty (5 dla ptgName, 4 dla ptgFuncVar), a wywołanie dodatku 11 (7 dla ptgNameX, 4 dla ptgFuncVar), i długości rekordów liczone przed zapisem muszą używać tego samego wyboru tokenu co bajty zapisane potem. HotXLS rozwiązuje wszystkie te nazwy w jednym przebiegu, zanim coś zapisze
Klasy operandów zmieniają to, co liczy Excel
Każdy argument w formule BIFF8 jest otagowany jako operand reference, value albo array, a Excel bierze tag za słowo. Otaguj trzeci argument AGGREGATE jako value, a Excel zwróci #VALUE!. Otaguj wewnętrzny wynik SUM(SORTBY(…)) jako value, a Excel wstawi niejawną intersekcję, pokaże SUM(@SORTBY(…)) i zsumuje pojedynczą komórkę. Pisarz musi tagować te argumenty tak jak Excel, co nie zawsze jest klasą sugerowaną przez dokumentację funkcji: argumenty funkcji dodatków są zapisywane jako reference, funkcje tablic dynamicznych zwracają array, a XLOOKUP, TAKE, DROP, IFS i SWITCH zwracają reference, kiedy siedzą tam, gdzie oczekiwane jest odwołanie. Złe klasy nie psują pliku. Zmieniają odpowiedź
Funkcje VBA trzymają skoroszyt w binarium XLSB
XLSB dodaje trzeci przypadek zapisu. Skoroszyt makr może wołać z komórek własne procedury Function VBA, a format binarny deklaruje każdą z nich jako nazwę oflagowaną jako procedura z pustą formułą; komórka woła ją potem przez ten sam wzorzec ptgName plus funkcja 255 co BIFF8. Formaty XML nie mają odpowiednika. Pusta zdefiniowana nazwa nie jest tam poprawna, a nazwa zdefiniowana jako #NAME? schowałaby funkcję VBA o tej samej nazwie. HotXLS zapisuje taki skoroszyt z powrotem do XLSB z deklaracjami nietkniętymi i odmawia konwersji do jakiegokolwiek innego formatu, zanim powstanie bajt. Proś o raport konwersji przed konwersją, nie po nieudanym zapisie: TXLSXWorkbook.GetConversionReport zwraca wpis VBAFunctionNames oznaczony jako niewspierany dla tych skoroszytów, i kod może się na nim rozgałęzić
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 // np. VBAFunctionNames
Book.SaveAs(ChangeFileExt(XlsbFile, '.edited.xlsb'),
xlsxExcelBinaryWorkbook)
else
Book.SaveAs(ChangeFileExt(XlsbFile, '.xlsx'), xlsxOpenXMLWorkbook);
finally
Book.Free;
end;
end;
Kiedy skoroszyt z innego narzędzia pokazuje #NAME? na funkcji, którą Excel ewidentnie zna, odpal na nim DumpRawFormulas przed czymkolwiek innym: goły NETWORKDAYS.INTL albo TEXTJOIN w tekście <f> to cała diagnoza. Przypisz ten sam tekst formuły przez Cell.Formula w komponencie HotXLS Delphi Excel, a XLSX dostanie prefiks, którego Excel oczekuje, a zapis .xls skoroszytu dostanie ukrytą nazwę i wywołanie funkcji 255