HotXLS: database export to spreadsheet reports в Delphi

Превращение результата запроса в отчёт 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;  // подписи, а не имена столбцов SQL
    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 столбцах; установка значения 50000 разбивает большой результирующий набор на листы ещё до того, как предел формата его обрежет. За внешний вид отвечают свойства 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