Определённые имена и межлистовые формулы в Delphi (HotXLS)

Определённое имя — это метка, заменяющая константу, диапазон ячеек или формульное выражение, сохранённая в книге один раз и упоминаемая символически везде, где нужна. Напишите TaxRate в формуле, и движок разрешит его в то, что хранит определение имени, будь то литерал 0.08 или диапазон Data!$A$2:$D$100. Межлистовая ссылка — ортогональная идея: Data!D2 дотягивается до ячейки на другом листе, уточняя адрес именем листа. Соедините эти две вещи — и сводный лист сможет суммировать лист детализации через имя, которое ни разу не упоминает буквальный адрес, а это ровно то, чего вы хотите в книге, которую собирает генератор и позже проверяет бухгалтер

HotXLS, нативная библиотека losLab для Delphi, работающая с файлами XLS и XLSX, открывает таблицу имён обоих форматов с доступом на создание, поиск и удаление, а также движок формул, который разрешает имена и межлистовые ссылки внутри процесса. Два формата держат раздельные иерархии классов, и именно различия их API имён подводят код, перенесённый из одного в другой

Два хранилища имён, не разделяющих общий интерфейс

На стороне XLS TXLSWorkbook.GetNames возвращает коллекцию IXLSNames, чья перегрузка Add(Name, RefersTo, Visible) пишет имя в таблицу имён BIFF. Отдельные записи возвращаются как объекты IXLSName, несущие Name, RefersTo, разрешённый RefersToRange и метод Delete. На стороне XLSX TXLSXWorkbook.DefinedNames — это коллекция TXLSXDefinedNames с методами Add, FindByName и DeleteByName

Соглашения поиска расходятся так, что это всплывает при переносе, а не на этапе компиляции. Свойство Item по умолчанию у коллекции XLS принимает Variant, поэтому через него разрешаются и Names[0], и Names['TaxRate']. У коллекции XLSX такого свойства по умолчанию нет; вы вызываете FindByName('TaxRate'), который возвращает nil, когда имени нет. Код, написанный под один фасад, компилируется под другой лишь по случайности, и сбой обычно проявляется как обращение по nil во время выполнения, а не красной волнистой линией в IDE

Область видимости — первое решение, а не флаг, добавляемый позже

Определённое имя либо имеет область книги и видно формулам на всех листах, либо область листа и видно только формулам своего листа-владельца. В API XLSX это различие сводится к одному необязательному параметру. DefinedNames.Add(AName, AFormula) создаёт имя уровня книги, а Add(AName, AFormula, ASheetIndex) привязывает его к одному листу. При обратном чтении TXLSXDefinedName.SheetIndex возвращает -1 для области книги и индекс листа с нуля во всех прочих случаях

Область видимости заодно служит вашей политикой разрешения коллизий, и потому её стоит утрясти до того, как вы напишете первое имя. Excel допускает локальное для листа Total на каждом листе плюс Total уровня книги, и формула на данном листе разрешает сначала локальное. Сгенерированные книги должны опираться на это осознанно. Бизнес-допущения, которые потребляют несколько листов, — ставки налога, курсы валют, отчётный период — принадлежат области книги. Вспомогательные диапазоны, на которые ссылаются формулы лишь одного листа, безопаснее держать в области листа, где ничто не может их затенить и они не могут затенить ничего

var
  Book: TXLSXWorkbook;
  Data, Summary: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Data := Book.Sheets.Add('Data');
    Summary := Book.Sheets.Add('Summary');
    // ... заполняем Data!A2:D100 строками детализации ...

    Book.DefinedNames.Add('TaxRate', '0.08');                // область книги, константа
    Book.DefinedNames.Add('DataBlock', 'Data!$A$2:$D$100');  // область книги, диапазон
    Book.DefinedNames.Add('LocalNote', 'Summary!$B$1', 1);   // только область листа с индексом 1

    // формулы XLSX идут без ведущего '='
    Summary.Cells[2, 2].Formula := 'SUM(Data!D2:D100)*TaxRate';
    Book.SaveAs('model.xlsx');
  finally
    Book.Free;
  end;
end;

Определённое имя вовсе не обязано указывать на диапазон. TaxRate выше ссылается на голую константу 0.08, и это самый чистый способ опубликовать бизнес-допущение. Оно появляется в диспетчере имён Excel один раз, каждая формула ссылается на него символически, а изменение ставки в следующем квартале превращается в правку одной строки генератора вместо поиска по четырнадцати собранным формульным строкам

Знак равенства, который уместен только с одной стороны

Канал ввода формул — то место, где перенесённый код ломается чаще всего, потому что два фасада расходятся насчёт знака равенства. Ячейки XLS получают формулы через Value с ведущим =. У ячеек XLSX есть выделенное свойство Formula, принимающее выражение без префикса. Запишите '=SUM(A1:A10)' в TXLSXCell.Formula — и знак равенства станет частью сохранённого текста выражения, а не маркером, и файл поведёт себя не так, как та же строка вела себя на стороне XLS

var
  Book: IXLSWorkbook;   // считается по ссылкам: не вызывайте Free
  Names: IXLSNames;
begin
  Book := TXLSWorkbook.Create;
  // считаем, что лист с именем 'Data' уже содержит строки детализации
  Names := Book.GetNames;
  Names.Add('TaxRate', '0.08');
  Names.Add('Helper', 'Data!$A$2:$A$100', False);  // False = скрыто от диспетчера имён

  // формулы XLS идут через Value, с префиксом '='
  Book.Sheets[1].Cells.Item[2, 2].Value := '=SUM(Data!A2:A100)*TaxRate';
  Book.SaveAs('model.xls');
end;

Этот фрагмент показывает ещё две особенности стороны XLS. Коллекция листов нумеруется с единицы, поэтому Sheets[1] — это первый лист, в отличие от Sheets[0] в XLSX, где счёт идёт с нуля. А третий параметр Add создаёт скрытое имя: присутствующее в файле и пригодное для формул, но невидимое в диспетчере имён Excel. Скрытые имена — правильный носитель для внутренней обвязки генератора, которую конечные пользователи никогда не должны править или удалять по случайности

Межлистовые ссылки и что происходит, когда строки двигаются

Оба движка формул принимают стандартный межлистовой синтаксис. Простые имена листов уточняют адрес напрямую, как Data!A1; имени с пробелами или пунктуацией нужны одинарные кавычки, как в 'Sheet With Space'!A1. Внутри текста RefersTo у имени почти всегда тянитесь к абсолютным ссылкам вроде Data!$A$2:$D$100. Относительная ссылка внутри определённого имени разрешается относительно использующей её ячейки, и это намеренная возможность Excel и надёжный источник путаницы, когда она срабатывает случайно

Структурные правки — это как раз то, где межлистовой учёт окупается, и сторона XLSX держит имена согласованными через них. InsertRows и DeleteRows сдвигают диапазоны определённых имён вместе с ячейками, объединениями, гиперссылками и привязками диаграмм, поэтому имя, указывающее на Data!$A$2:$D$100, по-прежнему покрывает блок данных после того, как генератор открыл разрыв выше него. У формул есть одна задокументированная оговорка: вставка строк корректирует только те ссылки, которые нацелены на редактируемый лист. Формула на Summary, ссылающаяся на Data!D2:D100, переписывается, когда строки вставляют в Data, и обычно это тот случай, который вам и нужен. Проверяйте это, а не предполагайте, потому что движок скажет вам это дёшево:

// движок вычислений разрешает имена и межлистовые ссылки внутри процесса
V := Book.Calculate('SUM(Data!D2:D100)*TaxRate');
if VarIsNumeric(V) then
  Log('net total checks out: ' + FloatToStr(V));

Calculate вычисляет произвольное выражение относительно текущего состояния книги, ничего не сохраняя, что делает его естественным примитивом утверждений для тестов генератора. Вычислите ожидаемый агрегат по исходным данным на Pascal, вычислите собственную формулу книги и сравните оба результата. Статья про движок формул разбирает, что движок вычисляет, когда и как расширять его собственными функциями

Имена _xlnm, которыми владеет слой свойств

Откройте таблицу имён сгенерированного файла низкоуровневым инспектором — и вы найдёте записи, которых никогда не писали: _xlnm.Print_Area, _xlnm.Print_Titles и их родственников. Именно так OOXML (ECMA-376 / ISO 29500) хранит области печати и повторяемые строки заголовков — в виде определённых имён с зарезервированными идентификаторами. HotXLS управляет ими через выделенные свойства листа, поэтому задание PrintArea или PrintTitleRows записывает соответствующую запись _xlnm.* за вас

Ловушка — залезть в это зарезервированное пространство имён вручную. Добавьте запись _xlnm.Print_Area через DefinedNames.Add, задав при этом ещё и свойство PrintArea, — и книга понесёт два противоречащих определения одного зарезервированного имени, а такое состояние Excel разрешает способами, на которые не должен опираться ни один продукт. Считайте, что каждый идентификатор, начинающийся с _xlnm., принадлежит слою свойств. Чтобы осмотреть настройки печати, читайте свойства, а не таблицу имён. Статья про защиту и параметры страницы разбирает свойства области печати в контексте

Две границы, о которых стоит знать до того, как утверждать дизайн

Определённые имена не проезжают через удобный мост из XLS в XLSX. SaveXLSWorkbookAsXLSX копирует содержимое ячеек и базовое форматирование, а таблицы имён нет в его задокументированном списке копирования, поэтому книга, зависевшая от своих имён, теряет их при переходе. Пересоздайте имена через DefinedNames.Add после конвертации. Этот шаг менее обременителен, чем звучит, потому что даёт момент нормализовать их области видимости вместо переноса того, что случайно оказалось в файле XLS

Другая граница — расхождение между строками формул и именами листов. Excel переписывает ссылки на листы внутри формул и имён при интерактивном переименовании, поэтому файлы, которые пользователь правит в Excel, остаются согласованными сами по себе. Уязвимость на стороне генератора: когда код на Pascal собирает строки формул из литерала с именем листа, переименование листа в одном месте и забывчивость в другом рождают ссылку на лист, которого больше нет. Держите имя листа в одной константе Delphi и подавайте её и в Sheets.Add, и в вашу сборку формул — тогда эти двое не смогут разойтись. Это тот же инстинкт, который велит давать имена выходным ячейкам отчёта, а не зашивать адреса: шаблон, чья итоговая ячейка именована, продолжает работать после того, как оформитель вставил над ней три строки, тогда как генератор, пишущий в буквальный B17, тихо кладёт своё число не туда. Статья про генерацию отчётов по шаблонам строится ровно на этом приёме

Полный API определённых имён для обоих форматов вместе со справочником по движку формул поставляется с HotXLS Delphi Component