Генерація звітів Excel на основі шаблонів у Delphi з HotXLS

Надійний спосіб отримати стилізований звіт Excel із Delphi — почати з книги, яку вже побудував дизайнер. Хтось у фінансовому відділі верстає рахунок-фактуру в Excel: логотип, заголовки стовпців, рамки на смузі деталей, жирний рядок підсумків, формати валюти. Ваш код відкриває цей файл, вкладає живі дані в клітинки, які дизайнер для них зарезервував, і зберігає результат. Вигляд належить йому; цифри — вам. HotXLS, нативна бібліотека для Delphi та C++Builder, що читає й записує книги XLS та XLSX без керування Excel, дає вам три операції, потрібні для цього підходу: пошук клітинки за її текстом, копіювання діапазону зі збереженими стилями й формулами та вставку рядків так, щоб усе нижче зсувалося разом із даними

Єдине правило, що відрізняє генератор, який переживає редагування шаблону, від того, що ламається на першому ж редагуванні, — ніколи не адресувати клітинки буквальними номерами рядків і стовпців. Шаблон — це документ, який редагують інші люди. Фінансова команда додає рядок податку, збільшує висоту рядка логотипа, переставляє блок адреси, і формат файлу вам жодним чином не допомагає: збереження BIFF чи OOXML вдається незалежно від того, чи означає рядок 10 те саме, що й минулого кварталу. Генератор, що записує перший рядок деталей у жорстко закодований рядок 10, тієї миті, коли хтось вставить блок над секцією деталей, поставить позиції рахунку на неправильні клітинки й підсумує діапазон підсумків, що вже не покриває дані. Нічого не викидає винятку, кожне збереження повертає успіх, і єдиний сигнал — клієнт, що помітив неправильний рахунок

Прив’язуйте кожну координату до токена-заповнювача

Виправлення — змусити шаблон нести власні координати. Дизайнер вписує токени на кшталт {{CUSTOMER}}, {{DATE}} і {{DETAIL_START}} у клітинки, яких має торкнутися генератор, і генератор обчислює кожну позицію під час виконання за тим, де він знаходить ці токени. Редагування макета більше не має значення, бо токен рухається разом із клітинкою, в якій сидить. Друга половина контракту — правило збою: якщо потрібний токен відсутній, завдання зупиняється до того, як будь-які дані клієнта потраплять у файл. Шаблон, що зазнав дрейфу, має видати провалений квиток завдання, а не доставлений документ

Пошук токенів: FindText і ReplaceText

Обидва сімейства класів HotXLS відкривають пошук на рівні аркуша. FindText повертає рядок і стовпець першої клітинки, чий текст збігається, з перевантаженням, що додає чутливість до регістру. ReplaceText замінює кожне входження й повертає, скільки їх змінено. Ці два покривають два види токенів, які зазвичай трапляються. Єдиний якір, як-от ім’я клієнта, ви знаходите один раз і записуєте поруч; токен, що має з’явитися рівно один раз, як-от дата звіту, ви замінюєте й перевіряєте кількість. З боку XLSX заповнення, що прив’язується таким чином, виглядає так:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, C: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('invoice-template.xlsx') <> 1 then
      raise Exception.Create('Cannot open invoice template');
    Sheet := Book.Sheets[0];               // TXLSXSheets.Items з нуля

    if not Sheet.FindText('{{CUSTOMER}}', R, C) then
      raise Exception.Create('Template drift: {{CUSTOMER}} anchor missing');
    Sheet.Cells[R, C].Value := 'ACME Corp';

    if Sheet.ReplaceText('{{DATE}}',
         FormatDateTime('yyyy-mm-dd', Date)) = 0 then
      raise Exception.Create('Template drift: {{DATE}} token missing');
    // розгортання деталей і збереження — нижче
  finally
    Book.Free;
  end;
end;

Дві деталі мають значення. По-перше, FindText і ReplaceText зіставляються з текстовим значенням клітинки; токен, вбудований усередині рядка формули, для них невидимий, тож токени-заповнювачі мають бути у звичайних клітинках, а не всередині формул. По-друге, кількість замін — ваш детектор дрейфу. Шаблон, що має містити рівно один токен {{DATE}}, але звітує про нуль замін, було відредаговано, і виклик винятку в цю мить — це саме те, що перетворює мовчазний дрейф макета на видимий збій

Клонування рядка деталей без втрати стилів чи формул

Секція деталей рахунку зростає разом із даними. Записування значень прямо в порожні рядки під зразковим рядком викидає все, що підготував дизайнер: рамки, числові формати, формули для кожного рядка. Шаблон, що зберігає все це, — лишити один повністю відформатований зразковий рядок у шаблоні й клонувати його для кожного елемента. CopyRange дублює стилі й формули одним викликом, після чого генератор перезаписує лише клітинки значень

const
  DetailRow = 10;            // відформатований зразковий рядок у шаблоні
var
  I: Integer;
begin
  // Спершу звільнити місце перед блоком підсумків, щоб діапазон SUM
  // під смугою деталей розтягувався разом із даними
  if Length(Items) > 1 then
    Sheet.InsertRows(DetailRow + 1, Length(Items) - 1);

  for I := 0 to High(Items) do
  begin
    if I > 0 then              // клонувати стилі та формули зі зразкового рядка
      Sheet.CopyRange(DetailRow, 1, DetailRow, 5, DetailRow + I, 1);
    Sheet.Cells[DetailRow + I, 1].Value := Items[I].Name;
    Sheet.Cells[DetailRow + I, 2].Value := Items[I].Qty;
    Sheet.Cells[DetailRow + I, 3].Value := Items[I].UnitPrice;
    Sheet.Cells[DetailRow + I, 4].Formula :=
      Format('B%d*C%d', [DetailRow + I, DetailRow + I]);  // без префікса '='
  end;
end;

Уважно стежте за присвоєнням формули. Властивість Formula у XLSX приймає вираз без провідного знака рівності, тоді як фасад XLS очікує '=B10*C10', присвоєне через Value. Змішування цих двох домовленостей — найпоширеніша помилка перенесення між сімействами класів, і вона провалюється без жодної скарги: клітинка просто тримає буквальний рядок, який Excel показує як текст. Якщо шаблон прикрашає смугу деталей об’єднаними рядками заголовків, пам’ятайте, що значення несе лише верхня-ліва клітинка об’єднаної області. Правила макета в суміжній статті про об’єднані клітинки в шаблонах звітів, керованих макетом, пояснюють, чому регіони об’єднання мають лишатися повністю поза смугою даних

Що InsertRows зсуває, а що лишає позаду

Вставка рядків перед блоком підсумків — це те, що тримає діапазон SUM розтягнутим у міру зростання секції деталей. У фасаді XLSX InsertRows тягне разом із клітинками довгий список залежних структур: об’єднані діапазони, висоти рядків, гіперпосилання, коментарі, закріплені області, діапазони автофільтра, умовне форматування, перевірку даних, таблиці, визначені імена, а також прив’язки зображень і діаграм. У цьому списку є одна межа, яку варто запам’ятати. Переписування формул сягає лише посилань у межах того самого аркуша. Формула на аркуші зведення, що вказує на переміщений регіон, зберігає свої старі координати й мовчки читає неправильні клітинки, і саме тому підсумки, витягнуті через аркуші, безпечніше виражати через імена рівня книги. Суміжна стаття про визначені імена та міжаркушеві формули розбирає цей шаблон

Застарілий формат XLS проводить межу в жорсткішому місці. HotXLS тримає зведені таблиці, таблиці запитів і з’єднання із зовнішніми даними у файлах BIFF як сирі блоки байтів. Вони переживають відкриття та збереження незмінними, але не моделюються, тож вставка рядків ніколи їх не торкається. Шаблон, що розміщує зведену таблицю під смугою деталей, яка розширюється, зберігається взагалі без попередження, поки прямокутник джерела зведеної таблиці дрейфує геть від даних. Вихід — структурний, а не захисний: тримайте вміст зведених таблиць і запитів на аркушах, у які генератор ніколи не вставляє, і застарілість статися не може

Перерахуйте перед доставкою, або знайте, чому пропустили це

HotXLS не обчислює формули під час SaveAs. Коли людина відкриває файл, Excel перераховує все (фасад XLS відкриває CalculationMode і RecalcOnSave, якщо вам потрібно це керувати), тож звіт, що йде до поштової скриньки людини, більше нічого від вас не потребує. Картина змінюється тієї миті, коли книга живить іншу програму. Експорт CSV записує формули як буквальний текст і ніколи їх не обчислює, і будь-який подальший парсер, що довіряє кешованим значенням, прочитає застарілі числа чи порожні клітинки. Для таких шляхів обчислюйте на сервері через Calculate, що обчислює довільний вираз проти завантаженої книги й повертає результат:

var
  Total: Variant;
  LastDetail: Integer;
begin
  LastDetail := DetailRow + Length(Items) - 1;
  Total := Book.Calculate(Format('SUM(Invoice!D%d:D%d)',
    [DetailRow, LastDetail]));
  if (not VarIsNumeric(Total)) or
     (Abs(Total - ExpectedTotal) > 0.005) then
    raise Exception.Create('Invoice total does not match the order record');

  if Book.SaveAs('invoice-2026-0611.xlsx') <> 1 then
    raise Exception.Create('Save failed: check output path and permissions');
end;

Перевірка обчисленого підсумку проти запису замовлення перед збереженням — дешева страховка з гарною віддачею. Вона перетворює неправильний рахунок на провалене завдання. Оператор може повторити провалене завдання за секунди; неправильний рахунок, що вже потрапив у поштову скриньку клієнта, коштує менеджеру з обслуговування вибачення та виправлення

Два сімейства класів, один алгоритм

Та сама логіка переноситься між форматами, але не той самий код. TXLSWorkbook для застарілого .xls базується на інтерфейсах і підраховує посилання, з індексацією аркушів з одиниці, і ви ніколи не звільняєте його вручну. TXLSXWorkbook для .xlsx — звичайний об’єкт, який ви маєте звільнити в try..finally, з індексацією аркушів з нуля та домовленістю щодо формул, показаною вище. FindText, ReplaceText, CopyRange і InsertRows усі живуть на обох боках, тож форма «прив’язка-клонування-перерахунок» переноситься чисто. Практична порада — тримайтеся одного формату на конвеєр або сховайте два життєвих цикли об’єктів за власним тонким адаптером, а не розсіювати різницю по всьому генератору

Розмір рідко має значення для такого типу звіту, який видає цей шаблон. Клонування стилізованого рядка кілька тисяч разів — ніщо для сучасного заліза. Шлях збереження стає вузьким місцем лише тоді, коли смуга деталей сягає шести цифр рядків, і в цей момент встановлення StreamingWrite надсилає XML аркуша прямо у вихідний пакет замість буферизації; стаття про потокові записи для серверних пакетних завдань розглядає, коли цей компроміс виправданий. Діаграми поводяться так само, як і решта макета: у фасаді XLSX і прив’язка діаграми, і посилання на її ряди даних рухаються, коли InsertRows виконується над ними, тож діаграма під рядком підсумків лишається прив’язаною до правильних даних, тоді як у фасаді XLS діаграми сидять на власних аркушах діаграм і, як і зведені таблиці, ніколи не зсуваються. Це ще один аргумент на користь того, щоб тримати презентаційні аркуші подалі від аркуша, який розширює генератор

Цей підхід «прив’язка-клонування-перерахунок» дозволяє дизайнеру володіти тим, як виглядає книга, поки ваш код володіє тим, що вона каже, а це зазвичай і робить згенерований вивід Excel вартим підтримки. Виклики пошуку, копіювання й вставки, показані тут, разом із рушієм формул, що використовується для перевірки підсумку перед доставкою, постачаються з HotXLS Delphi Component для Delphi та C++Builder