Експортиране на резултати от 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 реда â€?задаването на стойност като 50 000 разделя данните на листове пред достигане на лимита. Външният вид се контролира от свойствата за шрифтове на заглавието и данните, цветовете на групите и стиловете на рамките. За потребителско форматиране събитията AfterCell и AfterRow предоставят достъп до записания диапазон.

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

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

• Това е изцяло VCL компонент. Неговият модул изисква Forms, Controls и Dialogs, което въвежда VCL зависимости в конзолни програми или Windows услуги. Ядрото на библиотеката не изисква VCL и се нуждае само от базови системни модули.
• Той е изграден върху 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 гарантират коректност на данните. Задаването на StreamingWrite := True стриймва XML данните директно в ZIP архива при запис, вместо да ги събира първо в паметта, което намалява пиковете при голям брой редове. Всеки метод за запис поддържа и потоци (TStream), което позволява изпращане по HTTP без записване на диска. Този модел е разгледан в записване на потоци и автоматизирани задачи, а методите за оптимизация при много големи обеми са описани в производителност при големи работни книги.

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

В XLSX формата редовете са ограничени до 1 048 576, така че разделянето на листове е рядко необходимо. За много големи обеми от данни CSV форматът е по-подходящ, като подробности за изчисляването на формули и BOM маркерите са разгледани в експортиране в CSV и TSV.

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

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