Експорт результатів баз даних Delphi у звіти Excel за допомогою HotXLS

Перетворення результату запиту на звіт Excel — це три задачі в одному пальті. Кожен тип поля Delphi має приземлитися в клітинку правильним типом Excel, рядок заголовків має читатися як звіт, а не як викид схеми, а числа, дати й гроші мають нести формати, що переживуть подорож. Пропустіть бодай одну — і файл усе одно відкриється, усе одно матиме правдоподібний вигляд і усе одно провалиться тієї миті, коли фінансист виділить стовпець і чекатиме на суму, якої так і не буде. Значення записали як текст, Excel сприймає їх як підписи, і жодного винятку, який попередив би вас, не виникло

HotXLS — це нативна бібліотека електронних таблиць на Object Pascal, що пише файли XLS і XLSX прямо з Delphi та C++Builder, без жодної автоматизації Excel. Вона пропонує два шляхи від TDataset до книги: готовий компонент TDataToXLS і написаний руками цикл проти API книги. Вони не взаємозамінні. Компонент — повноцінний мешканець VCL, побудований на фасаді XLS, тож правильний вибір залежить від того, де виконується код і якого формату файлу очікує споживач. Далі йдуть обидва шляхи, межа, за якою компонент перестає бути правильним інструментом, і те, як зберегти типи полів неушкодженими, хоч би що ви обрали

Типи полів — це і є справжній контракт експорту

Перш ніж робити будь-який виклик API, вирішіть, як кожен тип поля Delphi приземлиться в клітинку. Клітинка, що отримує рядок Delphi, лишається рядком. HotXLS не вгадує, що '1,234.50' мало бути числом, — і правильно робить, бо повторний розбір із залежністю від локалі — це рівно той шлях, яким німецька десяткова кома перетворюється на роздільник тисяч на англійському сервері. Надійний підхід — присвоювати через типізовані аксесори: AsFloat або AsCurrency для числових полів, AsDateTime для дат, щоб клітинка тримала справжній серійний номер дати Excel, а не форматований рядок, і AsString лише для полів, які насправді є текстом

Обробка NULL заслуговує на явне рішення, а не на усталену поведінку. Перетворення значення поля через VarToStr обертає SQL NULL на порожній рядок, тобто на текстову клітинку, тоді як пропуск присвоєння лишає клітинку справді порожньою — саме цього й очікують AVERAGE, COUNT та споживачі зведених таблиць. Для грошових стовпців вирішіть ще до написання циклу, чи означає NULL нуль, чи невідомо. Ці два випадки промальовуються однаково, щойно хтось відформатує стовпець, а різниця між ними змінює кожен агрегат, обчислений нижче за течією

Шлях компонента: TDataToXLS у застосунках VCL

Для класичного застосунку VCL із запитом, уже підключеним у модулі даних, TDataToXLS — це шлях в один виклик. Він обходить будь-якого нащадка TDataset, чи то FireDAC, ADO, IBX, чи будь-що інше, що реалізує абстрактний інтерфейс набору даних, і видає оформлений аркуш із заголовками, шрифтами, рамками, необов’язковими проміжними підсумками за групами та автоматичним розбиттям на аркуші для великих наборів результатів

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // будь-який нащадок TDataset
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // підписи, а не сирі назви стовпців
    Exporter.GroupFields.Add('CustomerID');   // блок проміжних підсумків на клієнта
    Exporter.RowsPerSheet := 50000;           // лишатися нижче стелі рядків BIFF8
    Exporter.VisibleFieldsOnly := True;             // зважати на Field.Visible
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

Дві властивості несуть тут більшу частину виробничої ваги. HeaderSource := hsDisplayLabel записує DisplayLabel кожного поля замість сирої назви стовпця SQL, тож книга каже «Customer Name», а не CUST_NM. RowsPerSheet існує тому, що компонент пише BIFF8, чия сітка спиняється на 65 536 рядках і 256 стовпцях; значення 50 000 розбиває великий набір результатів на кілька аркушів, перш ніж стеля формату його обріже. За вигляд відповідають властивості HeaderFont, DetailFont, GroupColor та стилі рамок, а множина DisableFormat вимикає цілі категорії форматування, коли споживач хоче звичайні клітинки. Для чогось нетипового події AfterCell і AfterRow віддають вам щойно записаний діапазон на подальшу обробку

Де компонент спиняється

У TDataToXLS закладено три обмеження, і знання про них наперед рятує від незручного перепроєктування через два спринти

• Це компонент VCL у повному сенсі. Його модуль тягне за собою Forms, Controls і Dialogs, тож приєднання його до консольного завдання чи служби Windows затягує VCL у двійковий файл. Основні модулі книги такої залежності не мають. Їм потрібні лише Windows, Classes, SysUtils і Variants, і саме тому серверний код має натомість використовувати цикл, показаний нижче
• Він побудований на фасаді XLS. Компонент наповнює IXLSWorkbook і пише .xls (BIFF8). Немає властивості, яка перемкнула б його на вивід OOXML
• Його події говорять діалектом XLS. Параметр Cell: IXLSRange у AfterCell належить до об’єктної моделі XLS, тож поклітинкове налаштування, написане там, — це код у стилі XLS, навіть якщо файл потім конвертують у .xlsx

Отримання .xlsx із виводу компонента

Коли споживач наполягає на .xlsx, а логіка експорту вже живе в TDataToXLS, функція-міст із модуля lxXlsxExport конвертує наповнену книгу одним викликом:

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// компонент відкриває IXLSWorkbook, який він наповнив
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

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

Написаний руками цикл для служб і пакетних завдань

Серверний код має цілитися прямо в TXLSXWorkbook. Зверніть увагу на різницю в часі життя між двома фасадами, перш ніж копіювати будь-який приклад. TXLSWorkbook з боку XLS утримується через інтерфейс із підрахунком посилань, і його не можна звільняти вручну, тоді як TXLSXWorkbook — звичайний клас, що потребує try..finally Free. Змішування цих двох домовленостей — надійний спосіб виготовити або витік, або подвійне звільнення

procedure ExportOrders(Q: TDataSet; const FileName: string);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Row: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Sheet.Cells[1, 1].Value := 'Order No';
    Sheet.Cells[1, 2].Value := 'Customer';
    Sheet.Cells[1, 3].Value := 'Ordered';
    Sheet.Cells[1, 4].Value := 'Amount';

    Row := 2;
    Q.First;
    while not Q.Eof do
    begin
      Sheet.Cells[Row, 1].Value := Q.FieldByName('OrderNo').AsInteger;
      Sheet.Cells[Row, 2].Value := Q.FieldByName('Customer').AsString;
      if not Q.FieldByName('Ordered').IsNull then
        Sheet.Cells[Row, 3].Value := Q.FieldByName('Ordered').AsDateTime;
      Sheet.Cells[Row, 4].Value := Q.FieldByName('Amount').AsFloat;
      Inc(Row);
      Q.Next;
    end;

    Book.StreamingWrite := True;  // транслювати XML аркуша просто в zip
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

Значення тут мають типізовані присвоєння та охоронець IsNull. Дати приходять серійними номерами дат, суми приходять як double, а NULL-дати замовлень лишаються справді порожніми, замість стати порожніми рядками. StreamingWrite := True змінює лише шлях збереження: XML аркуша транслюється просто в zip-контейнер, замість спершу збиратися в один великий рядок, що згладжує стрибок пам’яті під час SaveAs для шестизначних кількостей рядків. Кожен метод збереження має також перевантаження з TStream, тож книга може піти прямо у відповідь HTTP, не торкаючись диска. Стаття про потоковий запис і пакетні завдання проводить крізь цей спосіб розгортання, а стаття про продуктивність великих книг розглядає, що робити, коли кількість рядків росте далі

Цей цикл — ще й той шлях, що масштабується на потоки. Обидва рушії — нативні записувачі на Object Pascal, потоки записів BIFF8 з одного боку і zip плюс XML за OOXML з другого, тож жодна частина експорту не торкається автоматизації COM і не потребує ліцензії Excel на сервері. Це дає вам паралелізм без вузького місця на єдиний екземпляр — за умови, що кожен потік будує власну книгу. Об’єкти книги не є потокобезпечними для спільного використання, тож правило таке: один екземпляр на експорт, і ніколи не спільний під захистом блокування

Одне обмеження варто знати, перш ніж проєктувати навколо нього. Сітка XLSX спиняється на 1 048 576 рядках і 16 384 стовпцях, тож розбиття на аркуші, яким на боці XLS опікується RowsPerSheet, тут потрібне рідко. Книга на мільйон рядків рідко є тим, чого хоче людина-споживач. Коли набір результатів справді такий великий, кращим контрактом зазвичай буде файл із роздільниками, а стаття про експорт у CSV і TSV розглядає роздільники, поведінку BOM і застереження щодо обчислення формул, яке там діє

Як обрати відправну точку

Якщо експорт живе в настільному інструменті на VCL і вивід .xls прийнятний, починайте з TDataToXLS та його підтримки групування. Це найменше коду, а міст через SaveXLSWorkbookAsXLSX напоготові, коли хтось згодом попросить .xlsx, — доки ви приймаєте вже описані обмеження точності. Якщо код виконується без нагляду або споживач від початку вимагає .xlsx, пишіть цикл. Обидва шляхи постачаються з робочими демонстраційними проєктами й входять до пакета HotXLS Delphi Component