Generowanie raportów Excel z szablonów w Delphi z HotXLS

Niezawodny sposób na wyprodukowanie ostylowanego raportu Excel z Delphi to wyjście od skoroszytu, który ktoś już zaprojektował. Ktoś z działu finansów układa fakturę w Excelu: logo, nagłówki kolumn, obramowania pasma detali, pogrubiony wiersz sum, formaty walutowe. Twój kod otwiera ten plik, wrzuca żywe dane do komórek, które projektant dla nich zarezerwował, i zapisuje wynik. Wygląd należy do nich; liczby do ciebie. HotXLS, natywna biblioteka dla Delphi i C++Builder, która czyta i zapisuje skoroszyty XLS oraz XLSX bez sterowania Excelem, daje ci trzy operacje, których to podejście potrzebuje: wyszukanie komórki po jej tekście, skopiowanie zakresu wraz ze stylami i formułami, oraz wstawianie wierszy tak, by wszystko poniżej przesuwało się w dół razem z danymi

Jedyna reguła, która odróżnia generator przeżywający edycje szablonu od takiego, który psuje się przy pierwszej z nich, brzmi: nigdy nie adresuj komórek dosłownymi numerami wierszy i kolumn. Szablon to dokument, który edytują inni ludzie. Zespół finansowy dodaje wiersz podatku, podnosi wysokość wiersza z logo, przestawia blok adresowy, a format pliku wcale ci nie pomaga: zapis BIFF albo OOXML kończy się powodzeniem niezależnie od tego, czy wiersz 10 wciąż znaczy to, co znaczył w zeszłym kwartale. Generator, który wpisuje pierwszą linię detalu do zaszytego na sztywno wiersza 10, przy pierwszym wstawieniu bloku nad sekcją detali odbije pozycje na niewłaściwych komórkach i zsumuje zakres, który już nie obejmuje danych. Nic nie rzuca wyjątku, każdy zapis zwraca sukces, a jedynym sygnałem jest klient, który zauważa błędną fakturę

Zakotwicz każdą współrzędną w tokenie zastępczym

Naprawą jest sprawienie, żeby szablon niósł własne współrzędne. Projektant wpisuje tokeny takie jak {{CUSTOMER}}, {{DATE}} i {{DETAIL_START}} do komórek, których generator musi dotknąć, a generator wylicza każdą pozycję w czasie działania z miejsca, w którym te tokeny znajdzie. Edycje układu przestają mieć znaczenie, bo token przesuwa się razem z komórką, w której siedzi. Druga połowa kontraktu to reguła awarii: jeśli brakuje wymaganego tokenu, zadanie zatrzymuje się, zanim jakiekolwiek dane klienta trafią do pliku. Szablon, który się rozjechał, powinien produkować zgłoszenie nieudanego zadania, a nie dostarczony dokument

Znajdowanie tokenów: FindText i ReplaceText

Obie rodziny klas HotXLS udostępniają wyszukiwanie na poziomie arkusza. FindText zwraca wiersz i kolumnę pierwszej komórki, której tekst pasuje, a przeciążona wersja dokłada rozróżnianie wielkości liter. ReplaceText podmienia każde wystąpienie i zwraca, ile ich zmienił. Te dwie funkcje pokrywają dwa rodzaje tokenów, jakie zwykle masz. Pojedynczą kotwicę, taką jak nazwa klienta, lokalizujesz raz i piszesz obok niej; token, który ma wystąpić dokładnie raz, jak data raportu, podmieniasz i sprawdzasz licznik. Po stronie XLSX wypełnianie zakotwiczone w ten sposób wygląda tak:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R, C: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open('invoice-template.xlsx') <> 1 then
      raise Exception.Create('Cannot open invoice template');
    Sheet := Book.Sheets[0];               // TXLSXSheets.Items liczone od zera

    if not Sheet.FindText('{{CUSTOMER}}', R, C) then
      raise Exception.Create('Template drift: {{CUSTOMER}} anchor missing');
    Sheet.Cells[R, C].Value := 'ACME Corp';

    if Sheet.ReplaceText('{{DATE}}',
         FormatDateTime('yyyy-mm-dd', Date)) = 0 then
      raise Exception.Create('Template drift: {{DATE}} token missing');
    // rozwinięcie pasma detali i zapis następują poniżej
  finally
    Book.Free;
  end;
end;

Liczą się dwa szczegóły. Po pierwsze, FindText i ReplaceText dopasowują tekstową wartość komórki; token wpleciony w ciąg formuły jest dla nich niewidoczny, więc tokeny zastępcze należą do zwykłych komórek, nigdy do wnętrza formuł. Po drugie, licznik podmian jest twoim detektorem rozjazdu. Szablon, który powinien zawierać dokładnie jeden token {{DATE}}, a raportuje zero podmian, został wyedytowany, i rzucenie wyjątku w tym właśnie momencie jest dokładnie tym, co zamienia cichy rozjazd układu w widoczną awarię

Klonowanie wiersza detalu bez utraty stylów i formuł

Sekcja detali faktury rośnie razem z danymi. Wpisywanie wartości prosto w puste wiersze pod linią przykładową wyrzuca wszystko, co przygotował projektant: obramowania, formaty liczb, formuły przypisane do wiersza. Wzorzec, który to wszystko zachowuje, polega na zostawieniu w szablonie jednego w pełni sformatowanego wiersza przykładowego i klonowaniu go dla każdej pozycji. CopyRange powiela style i formuły jednym wywołaniem, po czym generator nadpisuje wyłącznie komórki wartości

const
  DetailRow = 10;            // sformatowany wiersz przykładowy w szablonie
var
  I: Integer;
begin
  // Najpierw otwórz miejsce przed blokiem sum, żeby zakres SUM
  // pod pasmem detali rozciągał się razem z danymi.
  if Length(Items) > 1 then
    Sheet.InsertRows(DetailRow + 1, Length(Items) - 1);

  for I := 0 to High(Items) do
  begin
    if I > 0 then              // sklonuj style i formuły z wiersza przykładowego
      Sheet.CopyRange(DetailRow, 1, DetailRow, 5, DetailRow + I, 1);
    Sheet.Cells[DetailRow + I, 1].Value := Items[I].Name;
    Sheet.Cells[DetailRow + I, 2].Value := Items[I].Qty;
    Sheet.Cells[DetailRow + I, 3].Value := Items[I].UnitPrice;
    Sheet.Cells[DetailRow + I, 4].Formula :=
      Format('B%d*C%d', [DetailRow + I, DetailRow + I]);  // bez przedrostka '='
  end;
end;

Przyjrzyj się uważnie przypisaniu formuły. Właściwość Formula po stronie XLSX przyjmuje wyrażenie bez wiodącego znaku równości, podczas gdy fasada XLS oczekuje '=B10*C10' przypisanego przez Value. Mieszanie tych dwóch konwencji to najczęstszy błąd przenoszenia kodu między rodzinami klas, a psuje się bez słowa skargi: komórka trzyma po prostu dosłowny ciąg, który Excel pokazuje jako tekst. Jeśli szablon ozdabia pasmo detali scalonymi wierszami tytułowymi, pamiętaj, że tylko lewa górna komórka scalonego obszaru niesie wartość. Reguły układu w towarzyszącym artykule o scalonych komórkach w szablonach raportów sterowanych układem tłumaczą, dlaczego regiony scalenia należą całkowicie poza pasmo danych

Co InsertRows przesuwa, a co zostawia za sobą

Wstawianie wierszy przed blokiem sum jest tym, co utrzymuje rozciąganie zakresu SUM w miarę rośnięcia sekcji detali. Po stronie XLSX InsertRows ciągnie w dół razem z komórkami długą listę zależnych struktur: zakresy scalone, wysokości wierszy, hiperłącza, komentarze, zamrożone panele, zakresy autofiltru, formaty warunkowe, reguły poprawności danych, tabele, nazwy zdefiniowane oraz kotwice obrazów i wykresów. Jest w tej liście jedna granica warta zapamiętania. Przepisywanie formuł sięga wyłącznie odwołań w obrębie tego samego arkusza. Formuła na arkuszu podsumowania, która celuje w przesunięty obszar, zachowuje stare współrzędne i po cichu czyta niewłaściwe komórki, dlatego sumy ciągnięte między arkuszami bezpieczniej wyrażać przez nazwy na poziomie skoroszytu. Towarzyszący artykuł o nazwach zdefiniowanych i formułach międzyarkuszowych przerabia ten wzorzec

Starszy format XLS stawia granicę w trudniejszym miejscu. HotXLS trzyma tabele przestawne, tabele zapytań i zewnętrzne połączenia danych w plikach BIFF jako surowe bloki bajtów. Przeżywają otwarcie i zapis bez zmian, ale nie są modelowane, więc wstawianie wierszy nigdy ich nie dotyka. Szablon, który parkuje tabelę przestawną pod rozszerzającym się blokiem detali, zapisuje się zupełnie bez ostrzeżenia, podczas gdy prostokąt źródłowy tabeli przestawnej zjeżdża z danych. Wyjściem jest struktura, a nie obrona: trzymaj treść przestawną i zapytania na arkuszach, do których generator nigdy nie wstawia, a nieaktualność nie może się zdarzyć

Przelicz przed dostawą albo wiedz, dlaczego to pominąłeś

HotXLS nie oblicza formuł podczas SaveAs. Kiedy plik otwiera człowiek, Excel przelicza wszystko (fasada XLS udostępnia CalculationMode i RecalcOnSave, jeśli musisz tym sterować), więc raport zmierzający do ludzkiej skrzynki nie potrzebuje od ciebie nic więcej. Obraz zmienia się w chwili, gdy skoroszyt zasila inny program. Eksport CSV wypisuje formuły jako ich dosłowny tekst i nigdy ich nie oblicza, a dowolny parser niżej w potoku, który ufa wartościom z pamięci podręcznej, odczyta nieaktualne liczby albo puste pola. Dla takich ścieżek licz na serwerze przez Calculate, które oblicza dowolne wyrażenie względem wczytanego skoroszytu i oddaje wynik:

var
  Total: Variant;
  LastDetail: Integer;
begin
  LastDetail := DetailRow + Length(Items) - 1;
  Total := Book.Calculate(Format('SUM(Invoice!D%d:D%d)',
    [DetailRow, LastDetail]));
  if (not VarIsNumeric(Total)) or
     (Abs(Total - ExpectedTotal) > 0.005) then
    raise Exception.Create('Invoice total does not match the order record');

  if Book.SaveAs('invoice-2026-0611.xlsx') <> 1 then
    raise Exception.Create('Save failed: check output path and permissions');
end;

Sprawdzenie obliczonej sumy względem rekordu zamówienia przed zapisem to tania polisa o dobrym zwrocie. Zamienia błędną fakturę w nieudane zadanie. Operator ponowi nieudane zadanie w kilka sekund; błędna faktura już w skrzynce klienta kosztuje opiekuna klienta przeprosiny i korektę

Dwie rodziny klas, jeden algorytm

Ta sama logika przenosi się między formatami, ale nie ten sam kod. TXLSWorkbook dla starszego .xls jest oparty na interfejsach i zliczany referencyjnie, z indeksowaniem arkuszy od jedynki, i nigdy nie zwalniasz go ręcznie. TXLSXWorkbook dla .xlsx to zwykły obiekt, który musisz zwolnić w try..finally, z indeksowaniem arkuszy od zera i konwencją formuł pokazaną wyżej. FindText, ReplaceText, CopyRange i InsertRows żyją po obu stronach, więc kształt zakotwicz-sklonuj-przelicz przenosi się czysto. Praktyczna rada brzmi: zdecyduj się na jeden format na potok albo schowaj oba cykle życia obiektów za własnym cienkim adapterem, zamiast rozsiewać różnicę po całym generatorze

Rozmiar rzadko ma znaczenie dla raportów, jakie produkuje ten wzorzec. Sklonowanie ostylowanego wiersza kilka tysięcy razy to nic dla dzisiejszego sprzętu. Ścieżka zapisu staje się wąskim gardłem dopiero wtedy, gdy pasmo detali dobija do sześciocyfrowej liczby wierszy, a w tym momencie ustawienie StreamingWrite wysyła XML arkusza prosto do pakietu wyjściowego, zamiast go buforować; artykuł o zapisie strumieniowym dla serwerowych zadań wsadowych omawia, kiedy ten kompromis się opłaca. Wykresy zachowują się tak jak reszta układu: po stronie XLSX zarówno kotwica wykresu, jak i odwołania jego serii przesuwają się, gdy InsertRows zadziała powyżej nich, więc wykres pod wierszem sum zostaje związany z właściwymi danymi, a po stronie XLS wykresy siedzą na własnych arkuszach wykresów i, jak tabele przestawne, nigdy się nie przesuwają. To kolejny argument za trzymaniem arkuszy prezentacyjnych z dala od arkusza, który generator rozszerza

To podejście zakotwicz-sklonuj-przelicz pozwala projektantowi władać tym, jak skoroszyt wygląda, a twojemu kodowi tym, co on mówi, i zwykle to właśnie sprawia, że generowane wyjście Excela warto utrzymywać. Wywołania wyszukiwania, kopiowania i wstawiania pokazane tutaj, razem z silnikiem formuł użytym do kontroli sumy przed dostawą, są częścią HotXLS Delphi Component dla Delphi i C++Builder