Генериране на 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 is 0-based

    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');
    // detail expansion and save follow below
  finally
    Book.Free;
  end;
end;

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

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

Разделът с детайли на фактурата расте заедно с данните. Писането на стойности директно в празни редове под мострата изхвърля всичко, което дизайнерът е подготвил: границите, числовите формати, формулите за всеки ред. Моделът, който запазва всичко това, е да се остави един напълно форматиран ред-мостра в шаблона и да се клонира за всяка позиция. CopyRange дублира стиловете и формулите с едно извикване, след което генераторът презаписва само клетките със стойности.

const
  DetailRow = 10;            // the formatted sample row in the template
var
  I: Integer;
begin
  // Open space before the totals block first, so the SUM range
  // below the detail band stretches together with the data.
  if Length(Items) > 1 then
    Sheet.InsertRows(DetailRow + 1, Length(Items) - 1);

  for I := 0 to High(Items) do
  begin
    if I > 0 then              // clone styles + formulas from the sample row
      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]);  // no '=' prefix
  end;
end;

Следете внимателно присвояването на формули. Свойството Formula в XLSX приема израза без водещ знак за равенство, докато XLS фасадата очаква '=B10*C10', присвоен през Value. Смесването на двете конвенции е най-честата грешка при пренасяне между фамилиите класове и се проваля без оплакване: клетката просто държи буквален низ, който Excel показва като текст. Ако шаблонът украсява детайлната област с обединени заглавни редове, помнете, че само горната лява клетка на обединената област съдържа стойност. Правилата за оформление в придружаващата статия за обединени клетки в шаблони за отчети обясняват защо обединените региони принадлежат изцяло извън диапазона от данни.

Какво мести InsertRows и какво оставя зад себе си

Вмъкването на редове пред блока за общи суми е това, което поддържа разтягането на диапазона SUM с нарастването на детайлната секция. От страна на XLSX, InsertRows премества дълъг списък от зависими структури надолу заедно с клетките: обединени диапазони, височини на редове, хипервръзки, коментари, замразени панели, диапазони на автофилтри, условни формати, валидиране на данни, таблици, дефинирани имена, и закотвяния на изображения и диаграми. В този списък има една граница, която си струва да запомните. Пренаписването на формули засяга само препратки вътре в същия лист. Формула на обобщаващ лист, която сочи към преместения регион, запазва старите си координати и тихо чете грешни клетки, поради което общите суми, извлечени между листове, са по-безопасни, когато се изразяват чрез имена на ниво работна книга. Придружаващата статия за дефинирани имена и формули между листове разглежда този модел.

Остарелият XLS формат поставя границата на по-неудобно място. HotXLS съхранява обобщените таблици (pivot tables), заявките за таблици и външните връзки към данни в 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 е базиран на интерфейси и е reference-counted, с 1-базирано индексиране на листове, и никога не го освобождавате ръчно. TXLSXWorkbook за .xlsx е обикновен обект, който трябва да освободите в try..finally, с 0-базирано индексиране на листове и формулната конвенция, показана по-горе. FindText, ReplaceText, CopyRange и InsertRows живеят и от двете страни, така че алгоритъмът за закотвяне-клониране-преизчисляване се пренася чисто. Практическият съвет е да се ангажирате с един формат за всеки конвейер, или да скриете двата жизнени цикъла на обектите зад ваш тънък адаптер, вместо да разпръсквате разликата из целия генератор.

Размерът рядко е от значение за отчетите, които този модел генерира. Клонирането на стилизиран ред няколко хиляди пъти е нищожно натоварване за съвременния хардуер. Пътят на записване става тясно място едва когато областта с детайли достигне шестцифрен брой редове, и в този момент активирането на StreamingWrite изпраща XML на листа директно в изходния пакет, вместо да го буферира; статията за поточно записване при сървърни пакетни задачи описва кога този компромис си струва. Диаграмите се държат по същия начин, по който и останалата част от оформлението: от страна на XLSX както закотвянето на диаграмата, така и препратките за серията се преместват, когато InsertRows се изпълнява над тях, така че диаграма под реда с общите суми остава обвързана с правилните данни, докато от страна на XLS диаграмите се разполагат на собствени листове за диаграми и, подобно на обобщените таблици, никога не се изместват. Това е още един аргумент да държите презентационните листове далеч от листа, който генераторът разширява.

Този подход с котва-клониране-преизчисляване позволява на дизайнера да притежава начина, по който изглежда работната книга, докато вашият код притежава това, което тя казва, което обикновено прави генерирания изход от Excel лесен за поддръжка. Търсенето, копирането и вмъкването на редове, представени тук, заедно с изчислителния двигател, използван за проверката на общите суми преди доставка, се доставят с компонента HotXLS за Delphi и C++Builder.