Сгенерированная на 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 снабжает префиксом, а грамматика 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("-",TRUE,B1:D1). Кавычки выходят как ", потому что рутина печатает 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-экранировано: " & <
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? |
| Функции, которых не знает сохраняющая копия Excel | ptgName | Обычная запись NAME книги с пустым определением |
SUPBOOK надстройки из первой строки — та же запись, которую читалка встречает, сортируя внешние ссылки, и то, как записи BIFF SUPBOOK классифицируют внешние ссылки, показывает её место среди прочих видов. Не всё, что похоже на надстройку, идёт этим путём. DATEDIF, USDOLLAR, FINDB и ISPMT, среди прочих, имеют родные номера Ftab и хранятся как обычные вызовы функций. Третья строка — хрупкая: функция, слишком новая для сохранившего файл Excel, например REGEXTEST и REGEXEXTRACT, становится вызовом через обычное имя без определения. Читалка, выбрасывающая имена с пустым определением как мусор, оставляет формулу указывающей на индекс имени, которого больше не существует, и Excel отказывается открывать весь записанный обратно файл. HotXLS хранит такое имя с определением #NAME? и при сохранении снова записывает исходную запись
Каждое имя обязано существовать до первого листа
Порядок — следующее ограничение. Файл BIFF8 пишет свои globals — таблицу NAME и EXTERNSHEET — до любого подпотока листа, поэтому каждое имя, на которое будет указывать формула, обязано быть известным до записи первой ячейки. Писатель, встретивший XLOOKUP посреди третьего листа, уже не может добавить нужное скрытое имя. Если вы производите BIFF8 сами, сканируйте каждый источник формул до записи globals: формулы ячеек, имена уровня книги и листа, правила проверки данных и условное форматирование. Размеры следуют из токенов. Name-вызов стоит 9 байтов сверх аргументов (5 за ptgName, 4 за ptgFuncVar), вызов надстройки — 11 (7 за ptgNameX, 4 за ptgFuncVar), и длины записей, посчитанные до записи, обязаны использовать тот же выбор токена, что и байты, записанные следом. HotXLS разрешает все эти имена одним проходом, прежде чем писать хоть что-то
Классы операндов меняют то, что 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