Artykuł techniczny

_xlfn i wywołania nazw BIFF8 dla nowych funkcji Excela

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

Ścieżki zapisu nowych funkcji Excela w HotXLS dla Delphi: GAMMA niesie prefiks _xlfn, a ToolPakowy XIRR zostaje nagi
Prefiks oznacza wbudowaną, której czytnik jeszcze może nie znać, więc obecny Excel go zdejmuje, a starsze wersje odpowiadają błędem nazwy

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:

FunkcjaGdzie stawia ją gramatyka MS-XLSXCo zapisuje Excel
NETWORKDAYS.INTL, WORKDAY.INTLZa grupą _xlfn., więc nago_xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL
ISO.CEILINGZa grupą _xlfn., więc nago_xlfn.ISO.CEILING
FORECAST.LINEAR i rodzina FORECAST.ETSZa grupą _xlfn., więc nagoPrefiksowana, na przykład _xlfn.FORECAST.LINEAR
CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCHZwykła lista funkcji wbudowanychPrefiksowana, na przykład _xlfn.TEXTJOIN
XIRR, XNPV, ERFCNie ma ich na liście funkcji przyszłychNago

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(&quot;-&quot;,TRUE,B1:D1). Cudzysłowy wychodzą jako &quot;, 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: &quot; &amp; &lt;
    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 funkcjiToken nazwyNa co wskazuje nazwa
Funkcje Analysis ToolPak: EDATE, EOMONTH, WORKDAY, NETWORKDAYS, RANDBETWEEN, XIRR …ptgNameXRekord EXTERNNAME w SUPBOOK dodatku skoroszytu
Funkcje, które Excel zapisuje z _xlfn.: XLOOKUP, ISOWEEKNUM, IFS, CONCAT …ptgNameUkryty rekord NAME o nazwie _xlfn.XLOOKUP (albo _xlfn._xlws.FILTER) o definicji #NAME?
Funkcje, których nie rozpoznaje zapisująca kopia ExcelaptgNameZwykł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

Ścieżki wywołań nazw BIFF8 w HotXLS dla Delphi: funkcje ToolPak jadą ptgNameX, a funkcje _xlfn wołają przez ukryty rekord nazwy i funkcję 255
Formuła w pliku .xls trzyma numery, więc nowa funkcja pożycza slot zdefiniowany-przez-użytkownika i wskazuje rekord nazwy, który musi przeżyć round trip

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

Kolejność zapisu pliku BIFF8 w HotXLS dla Delphi: rekordy nazw w globals wyprzedzają każdy arkusz, więc źródła formuł są skanowane najpierw
Nazwa spotkana w połowie trzeciego arkusza nie dołączy już do globals, więc każde źródło formuł jest skanowane, zanim zostanie zapisany pierwszy bajt

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