Визначені імена та формули між аркушами в 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;   // з підрахунком посилань: не звільняйте
  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