Eksport zbiorów danych Delphi do raportów Excela z HotXLS

Zamiana wyniku zapytania w raport Excela to trzy problemy w jednym płaszczu. Każdy typ pola Delphi musi wylądować w komórce jako właściwy typ Excela, wiersz nagłówka musi czytać się jak raport, a nie jak zrzut schematu, a liczby, daty i pieniądze muszą nieść formaty, które przetrwają podróż. Pomiń którykolwiek z nich, a plik nadal się otworzy, nadal będzie wyglądał wiarygodnie i nadal zawiedzie w chwili, gdy użytkownik z finansów zaznaczy kolumnę i będzie czekał na sumę, która nigdy się nie pojawi. Wartości zapisano jako tekst, Excel traktuje je jak etykiety i nigdy nie podniesiono żadnego wyjątku, który by cię ostrzegł

HotXLS to natywna biblioteka arkuszowa w Object Pascalu, która zapisuje pliki XLS i XLSX wprost z Delphi i C++Buildera, bez udziału automatyzacji Excela. Oferuje dwie drogi z TDataset do skoroszytu: gotowy do wpięcia komponent TDataToXLS oraz ręcznie napisaną pętlę na API skoroszytu. Nie są one wymienne. Komponent jest obywatelem VCL zbudowanym na fasadzie XLS, więc właściwy wybór zależy od tego, gdzie działa kod i jakiego formatu pliku oczekuje konsument. Poniżej obie drogi, granica, na której komponent przestaje być właściwym narzędziem, i sposób na zachowanie typów pól w całości, niezależnie od tego, co wybierzesz

Typy pól to prawdziwy kontrakt eksportu

Przed jakimkolwiek wywołaniem API zdecyduj, jak każdy typ pola Delphi ląduje w komórce. Komórka, która dostaje ciąg z Delphi, zostaje ciągiem. HotXLS nie zgaduje, że '1,234.50' miało być liczbą, i nie powinien, bo ponowne parsowanie zależne od ustawień regionalnych to dokładnie ten mechanizm, przez który niemiecki przecinek dziesiętny zmienia się w separator tysięcy na angielskim serwerze. Niezawodny wzorzec to przypisywanie przez akcesory typowane: AsFloat albo AsCurrency dla pól liczbowych, AsDateTime dla dat, żeby komórka trzymała prawdziwy numer seryjny daty Excela, a nie sformatowany ciąg, i AsString tylko dla pól, które naprawdę są tekstem

Obsługa wartości NULL zasługuje na jawną decyzję, a nie na ustawienie domyślne. Konwersja wartości pola przez VarToStr zamienia SQL NULL w pusty ciąg, czyli w komórkę tekstową, podczas gdy pominięcie przypisania zostawia komórkę naprawdę pustą, czego oczekują AVERAGE, COUNT i konsumenci tabel przestawnych. Dla kolumn pieniężnych zdecyduj przed napisaniem pętli, czy NULL znaczy zero, czy nieznane. Oba renderują się identycznie, gdy ktoś sformatuje kolumnę, a różnica zmienia każdą agregację liczoną niżej w łańcuchu

Droga komponentu: TDataToXLS w aplikacjach VCL

Dla klasycznej aplikacji VCL z zapytaniem już wpiętym w moduł danych TDataToXLS jest drogą jednego wywołania. Przechodzi po dowolnym potomku TDataset, czy to FireDAC, ADO, IBX, czy czymkolwiek innym, co implementuje abstrakcyjny interfejs zbioru danych, i produkuje ostylowany arkusz z podpisami nagłówków, czcionkami, obramowaniami, opcjonalnymi sumami częściowymi grup i automatycznym dzieleniem arkuszy dla dużych zbiorów wyników

var
  Exporter: TDataToXLS;
begin
  Exporter := TDataToXLS.Create(nil);
  try
    Exporter.Dataset := OrdersQuery;          // dowolny potomek TDataset
    Exporter.WorksheetName := 'Orders';
    Exporter.HeaderSource := hsDisplayLabel;  // podpisy, nie surowe nazwy kolumn
    Exporter.GroupFields.Add('CustomerID');   // blok sum częściowych na klienta
    Exporter.RowsPerSheet := 50000;           // trzymaj się poniżej limitu wierszy BIFF8
    Exporter.VisibleFieldsOnly := True;             // respektuj Field.Visible
    Exporter.SaveDatasetAs('orders.xls');
  finally
    Exporter.Free;
  end;
end;

Dwie właściwości niosą tu większość ciężaru produkcyjnego. HeaderSource := hsDisplayLabel zapisuje DisplayLabel każdego pola zamiast surowej nazwy kolumny SQL, więc skoroszyt mówi "Customer Name", a nie CUST_NM. RowsPerSheet istnieje, bo komponent zapisuje BIFF8, którego siatka kończy się na 65536 wierszach na 256 kolumn; ustawienie go na 50000 dzieli duży zbiór wyników na arkusze, zanim limit formatu go utnie. Wyglądem zajmują się właściwości HeaderFont, DetailFont, GroupColor i style obramowań, a zbiór DisableFormat wyłącza całe kategorie formatowania, gdy konsument chce zwykłych komórek. Do czegokolwiek szytego na miarę zdarzenia AfterCell i AfterRow podają ci właśnie zapisany zakres do obróbki

Gdzie kończy się komponent

W TDataToXLS zaprojektowano trzy ograniczenia, a znajomość ich z góry oszczędza niezręcznego przeprojektowania dwa sprinty później

• To komponent VCL w pełnym znaczeniu. Jego jednostka wciąga Forms, Controls i Dialogs, więc wlinkowanie go do zadania konsolowego albo usługi Windows ciągnie VCL do binarki. Rdzeniowe jednostki skoroszytu nie mają takiej zależności. Potrzebują tylko Windows, Classes, SysUtils i Variants, i dlatego kod po stronie serwera powinien używać pętli pokazanej niżej
• Jest zbudowany na fasadzie XLS. Komponent wypełnia IXLSWorkbook i zapisuje .xls (BIFF8). Nie ma właściwości, która przełączy go na wyjście OOXML
• Jego zdarzenia mówią dialektem XLS. Parametr Cell: IXLSRange w AfterCell należy do modelu obiektowego XLS, więc dostosowanie pojedynczych komórek napisane tam jest kodem w stylu XLS, nawet jeśli plik zostanie potem skonwertowany do .xlsx

Produkowanie .xlsx z wyjścia komponentu

Kiedy konsument upiera się przy .xlsx, ale logika eksportu już mieszka w TDataToXLS, funkcja pomostowa z jednostki lxXlsxExport konwertuje wypełniony skoroszyt jednym wywołaniem:

uses lxXlsxExport;

Exporter.SaveDatasetAs('orders.xls');
// komponent wystawia IXLSWorkbook, który wypełnił
SaveXLSWorkbookAsXLSX(Exporter.Workbook, 'orders.xlsx');

Traktuj ten pomost jako nośnik danych tabelarycznych, a nie konwerter o pełnej wierności. Kopiuje wartości, formuły, formaty liczbowe, kolory wypełnień, atrybuty czcionek, szerokości kolumn i ustawienia widoku. Celowo nie kopiuje obramowań, scalonych zakresów, komentarzy, wykresów ani formatów warunkowych. Dla płaskiej siatki nagłówek plus wiersze to dokładnie tyle, ile trzeba. Dla ostylowanego raportu już nie, a uczciwym lekarstwem jest generowanie XLSX wprost, zamiast łatania skonwertowanego pliku

Ręcznie pisana pętla dla usług i zadań wsadowych

Kod po stronie serwera powinien celować wprost w TXLSXWorkbook. Zauważ różnicę czasu życia między obiema fasadami, zanim skopiujesz jakikolwiek przykład. TXLSWorkbook po stronie XLS trzymany jest przez interfejs zliczający referencje i nie wolno go zwalniać ręcznie, podczas gdy TXLSXWorkbook to zwykła klasa wymagająca try..finally Free. Mieszanie obu konwencji to niezawodny sposób na wyprodukowanie wycieku albo podwójnego zwolnienia

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;  // strumieniuj XML arkusza prosto do zipa
    Book.SaveAs(FileName);
  finally
    Book.Free;
  end;
end;

Liczą się linie z przypisaniami typowanymi i ze strażnikiem IsNull. Daty przychodzą jako numery seryjne dat, kwoty przychodzą jako liczby zmiennoprzecinkowe, a NULL-owe daty zamówień zostają naprawdę puste zamiast stać się pustymi ciągami. StreamingWrite := True zmienia tylko ścieżkę zapisu: XML arkusza strumieniuje prosto do kontenera zip, zamiast być najpierw składany w jeden wielki ciąg, co spłaszcza skok pamięci w chwili SaveAs przy sześciocyfrowych liczbach wierszy. Każda metoda zapisu ma też przeciążenie z TStream, więc skoroszyt może pójść prosto do odpowiedzi HTTP bez dotykania dysku. Artykuł o zapisie strumieniowym i zadaniach wsadowych przeprowadza przez ten wzorzec wdrożenia, a artykuł o wydajności dużych skoroszytów mówi, co robić, gdy liczby wierszy rosną dalej

Ta pętla to również droga, która skaluje się na wątki. Oba silniki to natywne zapisujące w Object Pascalu — strumienie rekordów BIFF8 po jednej stronie, zip OOXML plus XML po drugiej — więc żadna część eksportu nie dotyka automatyzacji COM ani nie potrzebuje licencji Excela na serwerze. Kupujesz tym równoległość bez wąskiego gardła jednej instancji, pod warunkiem że każdy wątek buduje własny skoroszyt. Obiekty skoroszytu nie są bezpieczne wątkowo przy współdzieleniu, więc reguła brzmi: jedna instancja na eksport, nigdy jedna współdzielona pilnowana blokadą

Jeden limit warto znać, zanim zaprojektujesz coś wokół niego. Siatka XLSX kończy się na 1048576 wierszach na 16384 kolumny, więc dzielenie arkuszy, którym po stronie XLS zajmuje się RowsPerSheet, jest tu rzadko potrzebne. Skoroszyt na milion wierszy rzadko też jest tym, czego chce ludzki konsument. Kiedy zbiór wyników naprawdę jest tak duży, plik z separatorami jest zwykle lepszym kontraktem, a artykuł o eksporcie CSV i TSV omawia separatory, zachowanie BOM i zastrzeżenie o wyliczaniu formuł, które tam obowiązuje

Wybór punktu startowego

Jeśli eksport mieszka w desktopowym narzędziu VCL i wyjście .xls jest akceptowalne, zacznij od TDataToXLS i jego wsparcia dla grupowania. To najmniej kodu, a pomost przez SaveXLSWorkbookAsXLSX jest na miejscu, gdy ktoś później poprosi o .xlsx, o ile godzisz się na opisane już ograniczenia wierności. Jeśli kod działa bezobsługowo albo konsument wymaga .xlsx od początku, napisz pętlę. Obie drogi przychodzą z działającymi projektami demonstracyjnymi i są częścią pakietu HotXLS Delphi Component