Sablon-alapú Excel jelentésgenerálás Delphi-ben HotXLS-szel

A megbízható módja annak, hogy Delphiből stílusos Excel-jelentést állíts elő, ha egy olyan munkafüzetből indulsz ki, amelyet egy tervező már megépített. A pénzügyi csapat valakije Excelben állítja össze a számlát: a logót, az oszlopfejléceket, a részletsáv kereteit, a félkövér összesítő sort, a pénznemformátumokat. A kódod megnyitja ezt a fájlt, élő adatokat helyez azokba a cellákba, amelyeket a tervező erre a célra tartott fenn, majd elmenti az eredményt. A megjelenés az övék, a számok a tieid. A HotXLS, egy natív Delphi- és C++Builder-könyvtár, amely az Excel indítása nélkül olvassa és írja az XLS és XLSX munkafüzeteket, három olyan műveletet ad a kezedbe, amelyre ehhez a megközelítéshez szükséged van: cella keresése szöveg alapján, tartomány másolása úgy, hogy a stílusai és képletei érintetlenek maradnak, valamint sorok beszúrása úgy, hogy minden alatta lévő elem lecsússzon az adatokkal együtt

Az egyetlen szabály, amely elválasztja a sablon-szerkesztéseket túlélő generátort attól, amelyik már az elsőn összeomlik: soha ne címezz cellákat literál sor- és oszlopszámokkal. A sablon olyan dokumentum, amelyet mások szerkesztenek. A pénzügyi csapat hozzáad egy adósort, megemeli a logósor magasságát, átrendezi a címblokkot, és a fájlformátum ebben semmit sem segít: egy BIFF- vagy OOXML-mentés sikeres lesz, függetlenül attól, hogy a 10. sor még mindig azt jelenti-e, amit az előző negyedévben jelentett. Egy generátor, amely az első részletsort a kemény kódolt 10. sorba írja, amint valaki egy blokkot beszúr a részletsáv fölé, rossz cellákra bélyegzi a tételsorokat, és egy olyan összesítő tartományt összegez, amely már nem fedi le az adatokat. Semmi nem dob hibát, minden mentés sikerrel tér vissza, és az egyetlen jel az, hogy egy ügyfél észreveszi a hibás számlát

Minden koordinátát egy helyőrző tokenhez rögzíts

A megoldás az, hogy a sablon maga hordozza a saját koordinátáit. A tervező olyan tokeneket ír a cellákba, amelyeket a generátornak érintenie kell, mint például {{CUSTOMER}}, {{DATE}} és {{DETAIL_START}}, a generátor pedig futásidőben határozza meg minden pozíciót aszerint, hol találja meg ezeket a tokeneket. A layout-szerkesztések ezután már nem számítanak, mert a token együtt mozog azzal a cellával, amelyben ül. A szerződés második fele a hibaszabály: ha egy kötelező token hiányzik, a feladat leáll, mielőtt bármilyen ügyféladat elérné a fájlt. Egy elsodródott sablonnak sikertelen jobjegyet kell eredményeznie, nem kézbesített dokumentumot

A tokenek megtalálása: FindText és ReplaceText

Mindkét HotXLS osztálycsalád munkalap-szintű keresést kínál. A FindText visszaadja az első olyan cella sorát és oszlopát, amelynek szövege illeszkedik, egy túlterheléssel, amely kis- és nagybetű-érzékenységet ad hozzá. A ReplaceText minden előfordulást kicserél, és visszaadja, hányat változtatott meg. A kettő lefedi a token két típusát, amelyekkel általában találkozol. Egy egyszeri horgonyt, mint az ügyfél nevét, egyszer keresel meg, és mellé írsz; egy tokent, amelynek pontosan egyszer kell megjelennie, mint a jelentés dátumát, lecserélsz, és ellenőrzöd a darabszámot. Az XLSX oldalon egy ilyen módon önmagát horgonyzó kitöltés így néz ki:

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];               // a TXLSXSheets.Items 0-alapú

    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');
    // a részletek bővítése és a mentés lentebb következik
  finally
    Book.Free;
  end;
end;

Két részlet számít. Először is, a FindText és a ReplaceText egy cella szöveges értékére illeszkedik; egy képletszövegbe ágyazott token láthatatlan számukra, ezért a helyőrző tokenek helye egyszerű cellákban van, sosem képleteken belül. Másodszor, a csere darabszáma a te elsodródás-érzékelőd. Egy sablon, amelynek pontosan egy {{DATE}} tokent kellene tartalmaznia, de nulla cserét jelent, szerkesztve lett, és pontosan ez a pillanat az, amikor egy kivétel dobása a néma layout-elsodródást látható hibává alakítja

A részletsor klónozása stílusok és képletek elvesztése nélkül

Egy számla részletsávja az adatokkal együtt növekszik. Ha az értékeket közvetlenül a mintasor alatti üres sorokba írod, azzal eldobod mindazt, amit a tervező előkészített: a kereteket, a számformátumokat, a soronkénti képleteket. A minta, amely mindezt megőrzi, az, hogy egy teljesen formázott mintasort hagysz a sablonban, és azt klónozod minden egyes tételhez. A CopyRange egyetlen hívással duplikálja a stílusokat és a képleteket, ami után a generátor csak az érték-cellákat írja felül

const
  DetailRow = 10;            // a formázott mintasor a sablonban
var
  I: Integer;
begin
  // Először nyiss helyet az összesítő blokk előtt, hogy a SUM tartomány
  // a részletsáv alatt együtt nyúljon az adatokkal.
  if Length(Items) > 1 then
    Sheet.InsertRows(DetailRow + 1, Length(Items) - 1);

  for I := 0 to High(Items) do
  begin
    if I > 0 then              // stílusok + képletek klónozása a mintasorból
      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]);  // nincs '=' előtag
  end;
end;

Figyeld meg alaposan a képlet-hozzárendelést. Az XLSX Formula tulajdonsága a kifejezést vezető egyenlőségjel nélkül várja, míg az XLS-felület a '=B10*C10' formát várja a Value-n keresztül hozzárendelve. A két konvenció keverése a leggyakoribb portolási hiba az osztálycsaládok között, és panasz nélkül hibázik: a cella egyszerűen egy literál karakterláncot tartalmaz, amelyet az Excel szövegként jelenít meg. Ha a sablon egyesített címsorokkal díszíti a részletsávot, ne feledd, hogy egy egyesített terület csak a bal felső cellája hordoz értéket. A layout-vezérelt jelentéssablonok egyesített celláiról szóló kísérő cikk elmagyarázza, miért tartoznak az egyesítési régiók teljes egészében az adatsávon kívülre

Mit mozgat az InsertRows, és mit hagy maga mögött

Az összesítő blokk elé sorokat beszúrni az, ami a SUM tartományt a részletsáv növekedésével együtt nyújtja. Az XLSX oldalon az InsertRows a cellákkal együtt egy hosszú listányi függő struktúrát is lehoz magával: egyesített tartományokat, sormagasságokat, hiperhivatkozásokat, megjegyzéseket, rögzített ablaktáblákat, automatikus szűrő-tartományokat, feltételes formázásokat, adatérvényesítéseket, táblázatokat, elnevezett tartományokat, valamint kép- és diagram-horgonyokat. Van ebben a listában egy határ, amelyet érdemes fejben tartani. A képlet-átírás csak az azonos munkalapon belüli hivatkozásokat éri el. Egy összesítő munkalapon lévő képlet, amely az áthelyezett régióba mutat, megtartja a régi koordinátáit, és csendben rossz cellákat olvas, ezért a munkalapok között áthúzott összesítéseket biztonságosabb munkafüzet-szintű nevekkel kifejezni. Az elnevezett tartományokról és a munkalapok közötti képletekről szóló kísérő cikk végigveszi ezt a mintát

A régi XLS formátum egy keményebb helyen húzza meg a határt. A HotXLS a pivot táblákat, lekérdezéstáblákat és külső adatkapcsolatokat nyers bájtblokkokként tartja meg a BIFF-fájlokban. Változatlanul túlélik a megnyitást és a mentést, de nincsenek modellezve, így a sorbeszúrás soha nem érinti őket. Egy sablon, amely egy pivot táblát egy bővülő részletblokk alá parkol, mindenféle figyelmeztetés nélkül elmenthető, miközben a pivot forrás-téglalapja elsodródik az adatoktól. A kiút strukturális, nem védekező jellegű: tartsd a pivot- és lekérdezés-tartalmat olyan munkalapokon, amelyekbe a generátor soha nem szúr be sorokat, és az elavulás nem történhet meg

Számolj újra kézbesítés előtt, vagy tudd, miért hagytad ki

A HotXLS nem értékeli ki a képleteket a SaveAs során. Amikor egy ember megnyitja a fájlt, az Excel mindent újraszámol (az XLS-felület felkínálja a CalculationMode-ot és a RecalcOnSave-et, ha ezt irányítanod kell), így egy emberi postaládába tartó jelentésnek semmi többre nincs szüksége tőled. A kép abban a pillanatban változik meg, amikor a munkafüzet egy másik programot táplál. A CSV-export a képleteket szó szerinti szövegükként írja ki, és soha nem számolja ki őket, és bármely lentebbi elemző, amely megbízik a gyorsítótárazott értékekben, elavult számokat vagy üres mezőket fog olvasni. Ezekhez az útvonalakhoz számolj a szerveren a Calculate-tel, amely egy tetszőleges kifejezést értékel ki a betöltött munkafüzettel szemben, és visszaadja az eredményt:

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;

A számított összeg ellenőrzése a rendelési rekorddal szemben a mentés előtt olcsó biztosítás, jó megtérüléssel. Egy hibás számlát sikertelen joberré alakít. Egy operátor másodpercek alatt újrapróbálhat egy sikertelen jobot; egy ügyfél postaládájában már ott lévő hibás számla egy fiókkezelőnek egy bocsánatkérésébe és egy javításba kerül

Két osztálycsalád, egy algoritmus

Ugyanaz a logika átvihető a formátumok között, de nem ugyanaz a kód. A régi .xls formátumhoz tartozó TXLSWorkbook interfészalapú és referenciaszámlált, 1-alapú munkalap-indexeléssel, és soha nem szabadítod fel kézzel. Az .xlsx-hez tartozó TXLSXWorkbook egy egyszerű objektum, amelyet egy try..finally-ban kell felszabadítanod, 0-alapú munkalap-indexeléssel és a fent bemutatott képlet-konvencióval. A FindText, a ReplaceText, a CopyRange és az InsertRows mindkét oldalon megtalálható, így a horgonyzás-klónozás-újraszámolás minta tisztán átvihető. A gyakorlati tanács az, hogy pipeline-onként dönts egy formátum mellett, vagy rejtsd el a két objektum-életciklust egy saját, vékony adapter mögé, ahelyett hogy a különbséget szétszórnád a generátorban

A méret ritkán számít az ilyen mintázat által előállított jelentéstípusnál. Egy stílusos sor néhány ezerszeri klónozása semmiség a mai hardvernek. A mentési útvonal csak akkor válik szűk keresztmetszetté, amikor egy részletsáv hat számjegyű sorszámba fut, és ekkor a StreamingWrite beállítása a munkalap XML-jét közvetlenül a kimeneti csomagba küldi ahelyett, hogy pufferelné; a szerveroldali kötegelt feladatok streamelt írásairól szóló cikk lefedi, mikor éri meg ezt a kompromisszumot megkötni. A diagramok úgy viselkednek, ahogy a layout többi része: az XLSX oldalon mind a diagram-horgony, mind a sorozathivatkozásai mozognak, amikor az InsertRows felettük fut, így egy összesítő sor alatti diagram a helyes adatokhoz marad kötve, míg az XLS oldalon a diagramok a saját diagram-munkalapjukon ülnek, és a pivot táblákhoz hasonlóan soha nem tolódnak el. Ez egy további érv amellett, hogy a bemutató munkalapokat tartsd tisztán attól a munkalaptól, amelyet a generátor bővít

Ez a horgonyzás-klónozás-újraszámolás megközelítés lehetővé teszi, hogy a tervező birtokolja, hogyan néz ki egy munkafüzet, míg a kódod birtokolja, mit mond, ami általában azt teszi, hogy megéri karbantartani a generált Excel-kimenetet. Az itt bemutatott keresési, másolási és beszúrási hívások, a kézbesítés előtti összeg-ellenőrzéshez használt képletmotorral együtt, a Delphihez és C++Builderhez készült HotXLS Delphi Component részeként érkeznek