Експортиране на Delphi бази данни в Excel отчети с HotXLS

Преобразуването на резултат от заявка в Excel отчет се състои от три основни стъпки: съпоставяне на полетата от Delphi към правилните типове в Excel, оформяне на заглавния ред като отчет (а не като схема на база данни) и правилно форматиране на числа, дати и валути. Ако пропуснете някое от тях, файлът ще се отвори без грешка, но потребителите няма да могат да сумират колоните, тъй като стойностите са записани като текст и Excel ги третира как етикети без предупреждение

HotXLS е библиотека на чист Object Pascal, която записва XLS и XLSX файлове директно от Delphi и C++Builder без Excel автоматизация. Тя предлага два начина за прехвърляне на TDataset към работна книга: чрез готовия компонент TDataToXLS или чрез ръчно програмиран цикъл. Те не са взаимозаменяеми - компонентът е проектиран за VCL приложения и ползва XLS фасадата, така че изборът зависи от средата на изпълнение и изисквания формат. По-долу разглеждаме и двата подхода, границите на компонента и как да запазите типовете данни

Типовете данни като основно изискване при експорт

Преди всяко извикване дефинирайте как типовете от 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;          // any TDataset descendant
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // captions, not raw column names
    Exporter.GroupFields.Add('CustomerID');   // subtotal block per customer
    Exporter.RowsPerSheet := 50000;           // stay below the BIFF8 row ceiling
    Exporter.OnlyVisible := True;             // respect Field.Visible
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

Две свойства носят основната тежест в реална среда. HeaderSource := hsDisplayLabel записва DisplayLabel на всяко поле вместо необработеното име на SQL колоната, така че работната книга показва „Име на клиента“ вместо CUST_NM. RowsPerSheet съществува, защото компонентът записва BIFF8, чиято мрежа спира при 65 536 реда и 256 колони; стойност 50 000 разделя голям резултат между листове, преди ограничението на формата да го отреже. Външният вид се управлява чрез HeaderFont, DetailFont, GroupColor и свойствата за стил на рамките, а наборът DisableFormat изключва цели категории форматиране, когато потребителят иска обикновени клетки. За специални нужди събитията AfterCell и AfterRow предоставят току-що записания диапазон за допълнителна обработка

Ограничения на компонента TDataToXLS

Три основни ограничения трябва да се вземат предвид при избора на TDataToXLS:

Това е изцяло VCL компонент. Неговият модул изисква Forms, Controls и Dialogs, което въвежда VCL зависимости в конзолни програми или Windows услуги. Ядрото на библиотеката няма такава зависимост и се нуждае само от Windows, Classes, SysUtils и Variants, затова сървърният код трябва да използва показания по-долу цикъл
Той е изграден върху XLS фасадата. Компонентът попълва IXLSWorkbook и записва .xls (BIFF8) без опция за директен OOXML изход
Събитията му ползват XLS диалект. Параметърът Cell: IXLSRange в AfterCell е част от XLS модела, така че промените в клетките се описват по правилата на по-стария формат

Генериране на .xlsx от изхода на компонента

Ако се изисква .xlsx изход, но логиката вече ползва TDataToXLS, мостът в модула lxXlsxExport конвертира записаната книга с едно извикване:

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// the component exposes the IXLSWorkbook it populated
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

Разглеждайте този мост като инструмент за пренос на таблични данни, а не за пълно преобразуване. Той копира стойности, формули, формати, цветове, шрифтове и размери на колоните, но не поддържа рамки, обединени клетки, коментари, диаграми или условно форматиране. За сложни отчети е по-добре да генерирате XLSX файла директно

Ръчен цикъл за услуги и автоматизирани задачи

Сървърният код трябва да използва директно TXLSXWorkbook. Обърнете внимание на разликата в жизнения цикъл на двата формата: TXLSWorkbook е интерфейсен обект с обратно броене и не се освобождава ръчно, докато 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;  // stream sheet XML straight into the zip
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

Важните редове са типизираните присвоявания и проверката IsNull. Датите постъпват като серийни стойности за дата, сумите като double, а NULL датите за поръчка остават действително празни вместо да се превръщат в празни низове. StreamingWrite := True променя само пътя за запис: XML на листа се подава директно в ZIP контейнера, вместо първо да се сглобява като един голям низ, което изравнява пика на паметта при SaveAs за шестцифрен брой редове. Всеки метод за запис има и претоварване с TStream, така че работната книга може да отиде директно в HTTP отговор без запис на диск. Статията за поточно записване и пакетни задачи разглежда този модел за внедряване, а статията за производителност при големи работни книги описва следващите стъпки при още повече редове

Този ръчен цикъл е подходящ и за многонишкови среди. И двата модула са написани изцяло на Object Pascal и не зависят от COM обекти или Excel лицензи на сървъра. Това позволява паралелна работа в отделни нишки, като всяка нишка трябва да използва собствена работна книга. Обектите не са нишково безопасни (thread-safe) за споделено ползване

Едно ограничение трябва да е известно преди проектирането. XLSX мрежата спира при 1 048 576 реда и 16 384 колони, затова разделянето на листове, което RowsPerSheet извършва при XLS, рядко е необходимо тук. Работна книга с милион реда рядко е удобна и за човек. Когато резултатът действително е толкова голям, файл с разделители обикновено е по-добрият договор, а статията за CSV и TSV експортиране разглежда разделителите, поведението на BOM и особеността при изчисляване на формули

Избор на подход

Ако разработвате VCL десктоп приложение и .xls форматът е приемлив, използвайте TDataToXLS с неговата поддръжка на междинни суми. Мостът през SaveXLSWorkbookAsXLSX е наличен при нужда от .xlsx. За сървърни услуги или твърдо изискване за .xlsx изход напишете ръчен цикъл. Двата подхода се доставят с работещи примери в пакета на HotXLS Component