Zanesljiv način za ustvarjanje stiliziranega Excel poročila iz Delphija je začetek z delovnim zvezkom, ki ga je oblikovalec že pripravil. Nekdo v finančnem oddelku oblikuje račun v Excelu: logotip, glave stolpcev, obrobe v vrstici s podrobnostmi, krepko vrstico za vsote in formate valut. Vaša koda odpre to datoteko, vpiše dejanske podatke v celice, ki jih je oblikovalec rezerviral zanje, in shrani rezultat. Videz je njihov, številke pa vaše. HotXLS, izvorna knjižnica za Delphi in C++Builder, ki bere in zapisuje delovne zvezke XLS in XLSX brez uporabe avtomatizacije Excela, vam omogoča tri operacije, ki jih ta pristop potrebuje: iskanje celice po njenem besedilu, kopiranje obsega z ohranjenimi slogi in formulami ter vstavljanje vrstic, tako da se vse pod njimi pomakne navzdol skupaj s podatki.
Edino pravilo, ki loči generator, ki preživi spremembe predlog, od tistega, ki se pokvari že ob prvi spremembi, je, da celic nikoli ne naslavljate z dobesednimi številkami vrstic in stolpcev. Predloga je dokument, ki ga urejajo drugi ljudje. Finančna ekipa doda vrstico za davek, poveča višino vrstice z logotipom ali prerazporedi naslovni blok, pri čemer vam format datoteke prav nič ne pomaga: shranjevanje v formatu BIFF ali OOXML uspe ne glede na to, ali vrstica 10 še vedno pomeni isto kot prejšnje četrtletje. Generator, ki prvo vrstico s podrobnostmi zapiše v trdo kodirano vrstico 10, bo ob prvem vstavljanju bloka nad odsekom s podrobnostmi prepisal napačne celice in seštel obseg vsot, ki ne pokriva več dejanskih podatkov. Pri tem se ne sproži nobena izjema, vsako shranjevanje vrne uspeh, edino opozorilo pa je stranka, ki opazi napačen račun.
Vsako koordinato zasidrajte na žeton mesta za vstavljanje
Rešitev je v tem, da predloga sama nosi svoje koordinate. Oblikovalec vpiše žetone, kot so {{CUSTOMER}}, {{DATE}} in {{DETAIL_START}}, v celice, ki se jih mora generator dotakniti, ta ja pa med izvajanjem določi vsak položaj glede na to, kje najde te žetone. Spremembe postavitve niso več pomembne, saj se žeton premika skupaj s celico, v kateri se nahaja. Drugi del pravila pa je obravnava napak: če zahtevani žeton manjka, se izvajanje ustavi, preden kakršni koli podatki o strankah dosežejo datoteko. Predloga, ki se je spremenila, bi morala povzročiti napako pri izvajanju opravila, ne pa dostave napačnega dokumenta.
Iskanje žetonov: FindText in ReplaceText
Obe družini razredov HotXLS omogočata iskanje na ravni delovnega lista. Funkcija FindText vrne vrstico in stolpec prve celice z ujemajočim se besedilom, pri čemer preobremenitev metode omogoča tudi razlikovanje med velikimi in malimi črkami. Funkcija ReplaceText zamenja vse pojave in vrne število spremenjenih celic. Ti dve metodi pokrivata obe vrsti žetonov, ki jih običajno uporabljate. Enkratno sidro, kot je ime stranke, poiščete enkrat in pišete vanj ali poleg njega; žeton, ki se mora pojaviti natanko enkrat, kot je datum poročila, pa preprosto zamenjate in preverite število sprememb. Na strani XLSX je primer takšnega sidranja prikazan spodaj:
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 is 0-based
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');
// detail expansion and save follow below
finally
Book.Free;
end;
end;Pomembni sta dve podrobnosti. Prvič, FindText in ReplaceText iščeta po besedilni vrednosti celice; žeton, vgrajen v niz formule, je zanju neviden, zato žetoni mesta za vstavljanje sodijo v navadne celice in nikoli v formule. Drugič, število zamenjav je vaš detektor odstopanja predloge. Če bi morala predloga vsebovati natanko en žeton {{DATE}}, vendar funkcija vrne nič zamenjav, pomeni, da je bila predloga spremenjena. Sprožitev izjeme v tem trenutku je tisto, logično odstopanje postavitve spremeni v vidno napako.
Kloniranje vrstice s podrobnostmi brez izgube slogov ali formul
Odsek s podrobnostmi računa se širi glede na podatke. Pisanje vrednosti neposredno v prazne vrstice pod vzorčno vrstico zavrže vse, kar je oblikovalec pripravil: obrobe, formate števil in formule za posamezne vrstice. Vzorec, ki ohrani vse to, je, da v predlogi pustite eno popolnoma oblikovano vzorčno vrstico in jo klonirate za vsak element. Metoda CopyRange podvoji sloge in formule z enim samim klicem, nato pa generator prepiše le celice z vrednostmi.
const
DetailRow = 10; // the formatted sample row in the template
var
I: Integer;
begin
// Open space before the totals block first, so the SUM range
// below the detail band stretches together with the data.
if Length(Items) > 1 then
Sheet.InsertRows(DetailRow + 1, Length(Items) - 1);
for I := 0 to High(Items) do
begin
if I > 0 then // clone styles + formulas from the sample row
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]); // no '=' prefix
end;
end;Pozorno spremljajte dodeljevanje formul. Lastnost XLSX Formula sprejme izraz brez začetnega enačaja, medtem ko fasada XLS pričakuje '=B10*C10', dodeljeno prek lastnosti Value. Mešanje teh dveh konvencij je najpogostejša napaka pri prenosu med družinama razredov in se zaključi brez opozorila o napaki: celica preprosto vsebuje dobesedni niz, ki ga Excel prikaže kot besedilo. Če predloga okrasi vrstico s podrobnostmi z združenimi naslovnimi vrsticami, ne pozabite, da le zgornja leva celica združenega območja nosi vrednost. Pravila postavitve v spremljevalnem članku o združenih celicah v predlogah poročil z vodeno postavitvijo pojasnjujejo, zakaj združena območja sodijo povsem izven podatkovnega pasu.
Kaj InsertRows premakne in česa ne
Vstavljanje vrstic pred blokom za vsote je tisto, kar ohranja obseg funkcije SUM raztegnjen, ko se odsek s podrobnostmi širi. Na strani XLSX metoda InsertRows skupaj s celicami navzdol premakne dolg seznam odvisnih struktur: združena območja, višine vrstic, hiperpovezave, komentarje, zamrznjene vrstice in stolpce, obsege samodejnega filtriranja, pogojno oblikovanje, preverjanje podatkov, tabele, definirana imena ter sidra slik in grafikonov. V tem seznamu pa obstaja ena meja, ki si jo velja zapomniti. Prilagajanje formul doseže le reference znotraj istega delovnega lista. Formula na zbirnem listu, ki kaže na premaknjeno območje, ohrani svoje stare koordinate in tiho bere podatke iz napačnih celic. Zato je vsote, ki se vlečejo čez več listov, varneje izraziti z imenom na ravni delovnega zvezka. Ta vzorec je podrobneje obravnavan v spremljevalnem članku o definiranih imenih in formulah med delovnimi listi.
Starejši format XLS potegne mejo na še zahtevnejšem mestu. HotXLS hrani vrtilne tabele, poizvedovalne tabele in zunanje podatkovne povezave v datotekah BIFF kot neobdelane bajtne bloke. Te strukture preživijo odpiranje in shranjevanje nespremenjene, vendar niso modelirane v knjižnici, zato se vstavljanje vrstic nanje ne nanaša. Predloga, ki vsebuje vrtilno tabelo pod razširjajočim se odsekom podrobnosti, se bo shranila brez opozorila, medtem ko se bo izvorni pravokotnik podatkov za vrtilno tabelo zamaknil. Rešitev je strukturna in ne programska: vsebino vrtilnih tabel in poizvedb hranite na delovnih listih, v katere generator nikoli ne vstavlja vrstic, s čimer preprečite neusklajenost podatkov.
Preračunajte pred dostavo ali pa imejte utemeljen razlog, zakaj ste to izpustili
HotXLS med klicem SaveAs ne ovrednoti formul. Ko uporabnik odpre datoteko, Excel samodejno preračuna vse formule (fasada XLS po potrebi izpostavlja lastnosti CalculationMode in RecalcOnSave), zato poročilo, namenjeno človeškemu prejemniku, ne potrebuje dodatnih korakov z vaše strani. Situacija pa se spremeni, ko delovni zvezek služi kot vhod za drug program. Izvoz v format CSV zapiše formule kot njihovo dobesedno besedilo in jih nikoli ne izračuna. Kakršen koli naslednji parser, ki se zanaša na predpomnjene vrednosti, bo prebral zastarele številke ali prazne celice. V teh primerih izračunajte vrednosti na strežniku s funkcijo Calculate, ki ovrednoti poljuben izraz v naloženem delovnem zvezku in vrne rezultat:
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;Preverjanje izračunane vsote z zapisom naročila pred shranjevanjem je poceni in učinkovito zavarovanje. S tem preprečite pošiljanje napačnega računa, ker se izvajanje opravila konča z napako. Skrbnik lahko neuspelo opravilo ponovi v nekaj sekundah, medtem ko napačen račun v strankinem poštnem predalu zahteva opravičilo in popravke s strani vodje ključnih strank.
Dve družini razredov, en algoritem
Ista logika se prenaša med formati, vendar ne z isto kodo. Razred TXLSWorkbook za starejši format .xls temelji na vmesnikih in šteje reference (z indeksiranjem listov od 1 dalje), zato ga nikoli ne sproščate ročno. Razred TXLSXWorkbook za .xlsx pa je navaden objekt, ki ga morate sprostiti v bloku try..finally (z indeksiranjem listov od 0 dalje in zgoraj prikazano konvencijo za formule). Funkcije FindText, ReplaceText, CopyRange in InsertRows so na voljo v obeh družinah, zato se pristop sidranja, kloniranja in preračunavanja prenaša brez težav. Praktičen nasvet je, da se v posameznem cevovodu zavežete enemu formatu ali pa oba življenjska cikla objektov skrijete za preprostim vmesnikom po meri, namesto da bi razlike razpršili po celotnem generatorju.
Velikost le redko igra vlogo pri tovrstnih poročilih. Kloniranje stilizirane vrstice nekaj tisočkrat za sodobno strojno opremo ne predstavlja nikakršnega napora. Shranjevanje postane ozko grlo le takrat, ko vrstica s podrobnostmi doseže šestmestno število vrstic. V tem primeru nastavitev StreamingWrite pošlje XML delovnega lista neposredno v izhodni paket namesto v medpomnilnik; kdaj se ta kompromis izplača, opisuje članek o pretočnem pisanju za strežniška paketna opravila. Grafikoni se obnašajo enako kot preostala postavitev: na strani XLSX se tako sidro grafikona kot reference serij premaknejo, ko nad njimi teče funkcija InsertRows, tako da grafikon pod vrstico vsot ostane povezan s pravilnimi podatki. Na strani XLS pa grafikoni stojijo na lastnih delovnih listih za grafikone in se, podobno kot vrtilne tabele, nikoli ne premaknejo. To je še en razlog več, da predstavitvene liste ločite od lista, ki ga generator širi.
Pristop sidranja, kloniranja in preračunavanja oblikovalcu omogoča nadzor nad izgledom delovnega zvezka, medtem ko vaša koda nadzoruje njegovo vsebino, kar običajno olajša vzdrževanje generiranih Excelovih izhodov. Iskanje, kopiranje in vstavljanje, ki so prikazani tukaj, skupaj z mehanizmom za formule, uporabljenim za preverjanje skupne vsote pred dostavo, so del knjižnice HotXLS Component za Delphi in C++Builder.