Техническая статья

Префиксы _xlfn и name-вызовы BIFF8 для новых функций Excel

Сгенерированная на Delphi книга держит =GAMMA(5) и =XIRR(B2:B9,A2:A9), и Excel открывает её с #NAME? в ячейке GAMMA и верным результатом в ячейке XIRR. В HotXLS функции, несущие префикс _xlfn. в XLSX или name-вызов в BIFF8 .xls, — это те, что Excel сам так сохраняет, и список здесь расходится с грамматикой MS-XLSX

Почему Excel показывает #NAME? для GAMMA, но не для XIRR?

Текст формулы верен; хранение — нет. Внутри листа XLSX формула — простой текст в элементе <f>, и функция, добавленная после Excel 2007, записывается как _xlfn.NAME. Префикс говорит читалке: «это встроенная функция, которой у тебя может ещё не быть», поэтому старый Excel показывает #NAME?, не принимая вызов за пользовательское имя, а нынешний Excel срезает префикс при показе, и пользователи видят =GAMMA(5). Уберите префикс — и нынешний Excel отправится искать определённое имя с именем GAMMA, не найдёт и покажет #NAME?. XIRR старше. Он пришёл с Analysis ToolPak, Excel пишет его без префикса, а _xlfn.XIRR с префиксом — другой токен, не тот, что производит Excel. GAMMA, GAUSS, PHI, SHEET, SHEETS и XOR требуют префикс, и восемь функций, добавленных Excel 2013 в инженерный набор и набор комплексных чисел (IMCOSH, IMCOT, IMCSC, IMCSCH, IMSEC, IMSECH, IMSINH и IMTAN), тоже, тогда как старые IMSIN, IMSQRT и IMSUM остаются без префикса. Служащие только листам массивные функции несут второй префикс, _xlfn._xlws., зарезервированный MS-XLSX §2.2.2 для FILTER, SORT и PY. При чтении HotXLS снимает оба префикса, и Cell.Formula возвращает чистое имя функции

Маршруты хранения новых функций Excel в HotXLS для Delphi: GAMMA несёт префикс _xlfn, а ToolPak-функция XIRR остаётся без префикса
префикс помечает встроенную функцию, которой читалка может ещё не знать, поэтому нынешний Excel его срезает, а старые версии отвечают ошибкой имени

Какие функции Excel снабжает префиксом, а грамматика MS-XLSX оставляет без него?

MS-XLSX §2.2.2 выписывает список в ABNF как future-function-list = ("_xlfn." ("AGGREGATE" / "ACOT" / … / "Z.TEST")) / "ECMA.CEILING" / "ISO.CEILING" / "NETWORKDAYS.INTL" / "WORKDAY.INTL" / "FORECAST.ETS" / …. Прочитано буквально, закрывающая скобка после Z.TEST закрывает группу с префиксом, и всё после неё — голое имя. Вторая продукция, обычный список встроенных функций, включает CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS и SWITCH рядом с SUM и VLOOKUP. Excel не согласен в обоих пунктах:

ФункцияКуда грамматика MS-XLSX его помещаетЧто пишет Excel
NETWORKDAYS.INTL, WORKDAY.INTLПосле группы _xlfn., значит без префикса_xlfn.NETWORKDAYS.INTL, _xlfn.WORKDAY.INTL
ISO.CEILINGПосле группы _xlfn., значит без префикса_xlfn.ISO.CEILING
FORECAST.LINEAR и семейство FORECAST.ETSПосле группы _xlfn., значит без префиксаС префиксом, например _xlfn.FORECAST.LINEAR
CONCAT, TEXTJOIN, IFS, MAXIFS, MINIFS, SWITCHОбычный список встроенных функцийС префиксом, например _xlfn.TEXTJOIN
XIRR, XNPV, ERFCНет в списке future-функцийБез префикса

Поэтому писатель, порождённый грамматикой, выпускает NETWORKDAYS.INTL(…) без префикса, и Excel отвечает #NAME? в файле, валидном против опубликованного синтаксиса. Берите список из сохранённых файлов Excel и считайте ABNF отправной точкой. HotXLS делает это для каждой функции, которую вычисляет, поэтому CONCAT с префиксом и NETWORKDAYS.INTL с префиксом выходят так, как их пишет Excel. Следующий код сохраняет одни и те же формулы обоими путями и читает одну обратно:

uses
  SysUtils, lxCalc, lxHandle, lxHandleX;

procedure SaveModernFunctions;
var
  XBook, XCheck: TXLSXWorkbook;
  XSheet: TXLSXWorksheet;
  Book: TXLSWorkbook;
  Sheet: TXLSWorksheet;
begin
  // XLSX: префиксы добавляются при сохранении и снимаются при загрузке
  XBook := TXLSXWorkbook.Create;
  try
    XSheet := XBook.Sheets.Add('Calc');
    XSheet.Cells[1, 1].Value := 46000.0;                // серийный номер даты
    XSheet.Cells[1, 2].Formula := 'EDATE(A1,1)';       // ToolPak: без префикса
    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');
    // Ожидается: NETWORKDAYS.INTL(A1,A1+30,11), префикс уже снят
    Writeln(string(XCheck.Sheets[1].Cells[1, 4].Formula));
  finally
    XCheck.Free;
  end;

  // BIFF8: те же вызовы становятся name-токенами плюс запись 255 таблицы 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 надстройки
    Sheet.Cells[1, 3].Formula := '=ISOWEEKNUM(A1)';    // скрытый NAME _xlfn
    if Book.SaveAs('modern.xls') <> lxOk then          // BIFF8 по умолчанию
      raise Exception.Create('XLS save failed');
  finally
    Book.Free;
  end;
end;

Оба файла должны открываться в нынешнем Excel со всеми функциями целыми. Чтобы увидеть префиксы, откройте пакет как ZIP и выведите сырой текст <f> части worksheet; для modern.xlsx рутина ниже должна напечатать EDATE(A1,1), _xlfn.GAMMA(5), _xlfn.NETWORKDAYS.INTL(A1,A1+30,11) и _xlfn.TEXTJOIN(&quot;-&quot;,TRUE,B1:D1). Кавычки выходят как &quot;, потому что рутина печатает XML-текст без обратного экранирования — для диагностики это безвредно. Она работает на файлах любого производителя, так что это самая быстрая проверка, когда чужая книга показывает #NAME? в функции, которую ваша копия Excel поддерживает

uses
  SysUtils, Classes, Zip;

// Печатаем каждый элемент <f> одной части worksheet, с префиксами
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>: это не формула ячейки
    P := Pos('>', Xml, P) + 1;               // пропускаем атрибуты вроде t="shared"
    if Xml[P - 2] = '/' then
      Continue;                                // пустой <f .../> разделяемой формулы
    Close := Pos('</f>', Xml, P);
    if Close = 0 then
      Break;
    Writeln(Copy(Xml, P, Close - P));          // XML-экранировано: &quot; &amp; &lt;
    P := Close + 4;
  end;
end;

Как файл BIFF8 .xls вызывает функцию, для которой у него нет номера?

Формулы BIFF8 вообще не содержат имён функций. Каждая встроенная функция — число в фиксированной таблице функций (Ftab), и формула хранит ptgFunc или ptgFuncVar с тем числом. Более новые функции числа не имеют. MS-XLS ведёт такие вызовы через запись 0x00FF таблицы Ftab, «User Defined Function»: ptgFuncVar с индексом функции 255, чей первый аргумент — name-токен, указывающий на настоящую функцию. Число аргументов в токене на единицу больше того, что набрал пользователь: ведущее имя считается аргументом. Какой name-токен запишет Excel, зависит от семейства функции:

Семейство функцийName-токенНа что указывает имя
Функции Analysis ToolPak: EDATE, EOMONTH, WORKDAY, NETWORKDAYS, RANDBETWEEN, XIRR …ptgNameXЗапись EXTERNNAME в SUPBOOK надстройки книги
Функции, которые Excel пишет с _xlfn.: XLOOKUP, ISOWEEKNUM, IFS, CONCAT …ptgNameСкрытая запись NAME с именем _xlfn.XLOOKUP (или _xlfn._xlws.FILTER), чьё определение — #NAME?
Функции, которых не знает сохраняющая копия ExcelptgNameОбычная запись NAME книги с пустым определением

SUPBOOK надстройки из первой строки — та же запись, которую читалка встречает, сортируя внешние ссылки, и то, как записи BIFF SUPBOOK классифицируют внешние ссылки, показывает её место среди прочих видов. Не всё, что похоже на надстройку, идёт этим путём. DATEDIF, USDOLLAR, FINDB и ISPMT, среди прочих, имеют родные номера Ftab и хранятся как обычные вызовы функций. Третья строка — хрупкая: функция, слишком новая для сохранившего файл Excel, например REGEXTEST и REGEXEXTRACT, становится вызовом через обычное имя без определения. Читалка, выбрасывающая имена с пустым определением как мусор, оставляет формулу указывающей на индекс имени, которого больше не существует, и Excel отказывается открывать весь записанный обратно файл. HotXLS хранит такое имя с определением #NAME? и при сохранении снова записывает исходную запись

Маршруты name-вызовов BIFF8 в HotXLS для Delphi: функции ToolPak едут через ptgNameX, а функции _xlfn звонят через скрытую запись имени и функцию 255
формула в файле .xls хранит числа, поэтому новая функция занимает слот пользовательской функции и указывает на запись имени, обязанную пережить round trip

Каждое имя обязано существовать до первого листа

Порядок — следующее ограничение. Файл BIFF8 пишет свои globals — таблицу NAME и EXTERNSHEET — до любого подпотока листа, поэтому каждое имя, на которое будет указывать формула, обязано быть известным до записи первой ячейки. Писатель, встретивший XLOOKUP посреди третьего листа, уже не может добавить нужное скрытое имя. Если вы производите BIFF8 сами, сканируйте каждый источник формул до записи globals: формулы ячеек, имена уровня книги и листа, правила проверки данных и условное форматирование. Размеры следуют из токенов. Name-вызов стоит 9 байтов сверх аргументов (5 за ptgName, 4 за ptgFuncVar), вызов надстройки — 11 (7 за ptgNameX, 4 за ptgFuncVar), и длины записей, посчитанные до записи, обязаны использовать тот же выбор токена, что и байты, записанные следом. HotXLS разрешает все эти имена одним проходом, прежде чем писать хоть что-то

Порядок записи файла BIFF8 в HotXLS для Delphi: записи имён в globals предшествуют каждому листу, поэтому источники формул сканируются первыми
имя, встреченное посреди третьего листа, не может присоединиться к globals, поэтому каждый источник формул сканируется до записи первого байта

Классы операндов меняют то, что Excel вычисляет

Каждый аргумент формулы BIFF8 помечен как операнд-ссылка, значение или массив, и Excel верит метке на слово. Пометьте третий аргумент AGGREGATE значением — Excel вернёт #VALUE!. Пометьте внутренний результат SUM(SORTBY(…)) значением — Excel вставит неявное пересечение, покажет SUM(@SORTBY(…)) и просуммирует одну ячейку. Писатель обязан метить эти аргументы, как это делает Excel, а это не всегда тот класс, что подсказывает документация функции: аргументы функций надстроек пишутся как ссылки, функции динамических массивов возвращают массивы, а XLOOKUP, TAKE, DROP, IFS и SWITCH возвращают ссылки, когда стоят там, где ожидается ссылка. Неверные классы не ломают файл. Они меняют ответ

Функции VBA держат книгу XLSB двоичной

XLSB добавляет третий случай хранения. Книга с макросами может вызывать из ячеек собственные процедуры VBA Function, и двоичный формат объявляет каждую именем, помеченным как процедура с пустой формулой; ячейка затем зовёт её через ту же связку ptgName плюс функция 255, что и в BIFF8. У XML-форматов эквивалента нет. Пустое определённое имя там невалидно, а имя, определённое как #NAME?, спрятало бы функцию VBA с тем же именем. HotXLS сохраняет такую книгу обратно в XLSB с объявлениями целыми и отказывается от конверсии в любой другой формат до записи байта. Запрашивайте отчёт о конверсии до конверсии, а не после провала сохранения: TXLSXWorkbook.GetConversionReport возвращает запись VBAFunctionNames, помеченную как unsupported для таких книг, и код может ветвиться по ней

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                               // например, VBAFunctionNames
      Book.SaveAs(ChangeFileExt(XlsbFile, '.edited.xlsb'),
        xlsxExcelBinaryWorkbook)
    else
      Book.SaveAs(ChangeFileExt(XlsbFile, '.xlsx'), xlsxOpenXMLWorkbook);
  finally
    Book.Free;
  end;
end;

Когда книга из другого инструмента показывает #NAME? на функции, которую Excel очевидно знает, прогоните по ней DumpRawFormulas прежде всего остального: голый NETWORKDAYS.INTL или TEXTJOIN в тексте <f> — весь диагноз целиком. Присвойте тот же текст формулы через Cell.Formula в Excel-компоненте HotXLS для Delphi, и XLSX получит префикс, которого ждёт Excel, а сохранение книги в .xls — скрытое имя и вызов функции 255