Luotettavin tapa tuottaa tyylitelty Excel-raportti Delphillä on aloittaa työkirjasta, jonka suunnittelija on jo rakentanut. Joku taloushallinnossa sommittelee laskun Excelissä: logon, sarakeotsikot, tietorivien reunukset, lihavoidun loppusummarivin ja valuuttamuotoilut. Koodisi avaa tiedoston, sijoittaa ajantasaiset tiedot soluihin, jotka suunnittelija on varannut niille, ja tallentaa tuloksen. Ulkoasu on heidän, luvut sinun. HotXLS, natiivi Delphi- ja C++Builder-kirjasto, joka lukee ja kirjoittaa XLS- ja XLSX-työkirjoja ilman Excelin automatisointia, tarjoaa tähän toimintatapaan tarvittavat kolme operaatiota: solun etsimisen tekstin perusteella, alueen kopioinnin tyyleineen ja kaavoineen sekä rivien lisäämisen niin, että kaikki alla oleva siirtyy tietojen mukana alaspäin
Yksi sääntö erottaa pohjan muokkauksista selviytyvän generaattorin sellaisesta, joka hajoaa ensimmäiseen muutokseen: älä koskaan osoita soluja kiinteillä rivi- ja sarakenumeroilla. Pohja on asiakirja, jota muut ihmiset muokkaavat. Taloustiimi lisää verorivin, kasvattaa logorivin korkeutta, järjestää osoitelohkon uudelleen, eikä tiedostomuoto auta lainkaan: BIFF- tai OOXML-tallennus onnistuu riippumatta siitä, tarkoittaako rivi 10 yhä samaa kuin viime neljänneksellä. Generaattori, joka kirjoittaa ensimmäisen tietorivin kiinteästi riville 10, leimaa ensimmäisellä tieto-osan yläpuolelle lisätyllä lohkolla rivikohdat vääriin soluihin ja laskee summan alueelta, joka ei enää kata tietoja. Mikään ei heitä virhettä, jokainen tallennus palauttaa onnistumisen, ja ainoa merkki on asiakkaan huomaama virheellinen lasku
Kiinnitä jokainen koordinaatti paikkamerkkitunnukseen
Ratkaisu on, että pohja kantaa omat koordinaattinsa. Suunnittelija kirjoittaa soluihin, joihin generaattorin on koskettava, tunnukset kuten {{CUSTOMER}}, {{DATE}} ja {{DETAIL_START}}, ja generaattori selvittää jokaisen sijainnin ajon aikana siitä, mistä se löytää tunnukset. Asettelun muokkaukset eivät enää haittaa, sillä tunnus liikkuu sen solun mukana, jossa se on. Sopimuksen toinen puoli on epäonnistumissääntö: jos vaadittu tunnus puuttuu, työ pysähtyy ennen kuin asiakastietoja päätyy tiedostoon. Harhautuneen pohjan tulee tuottaa epäonnistunut työlippu, ei toimitettu asiakirja
Tunnusten etsiminen: FindText ja ReplaceText
Molemmat HotXLS-luokkaperheet tarjoavat laskentataulukon tasoisen haun. FindText palauttaa ensimmäisen tekstiltään vastaavan solun rivin ja sarakkeen sekä tarjoaa ylikuormituksen, jolla lisätään kirjainkoon huomiointi. ReplaceText vaihtaa kaikki esiintymät ja palauttaa muutettujen määrän. Ne kattavat kaksi tavallista tunnustyyppiä: yksittäisen ankkurin, kuten kerran paikannettavan asiakasnimen, jonka viereen kirjoitetaan, ja tunnuksen, jonka tulee esiintyä täsmälleen kerran, kuten raportin päivämäärän, joka korvataan ja jonka lukumäärä tarkistetaan. XLSX-puolella tällä tavoin ankkuroitu täyttö näyttää tältä:
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 on 0-pohjainen
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');
// yksityiskohtien laajennus ja tallennus alla
finally
Book.Free;
end;
end;Kaksi yksityiskohtaa on tärkeää. Ensinnäkin FindText ja ReplaceText vastaavat solun tekstiarvoa; kaavamerkkijonon sisään upotettu tunnus ei näy niille, joten paikkamerkkitunnukset kuuluvat tavallisiin soluihin, eivät koskaan kaavojen sisään. Toiseksi korvausten lukumäärä toimii poikkeamadetektorina. Jos pohjassa pitäisi olla täsmälleen yksi {{DATE}}-tunnus mutta korvauksia ilmoitetaan nolla, pohjaa on muokattu, ja poikkeuksen nostaminen juuri silloin muuttaa hiljaisen asettelun poikkeaman näkyväksi virheeksi
Tietorivin kloonaaminen ilman tyylien tai kaavojen menettämistä
Laskun tieto-osa kasvaa tietojen mukana. Arvojen kirjoittaminen suoraan mallirivin alapuolella oleville tyhjille riveille hävittää kaiken, minkä suunnittelija valmisteli: reunukset, lukumuotoilut ja rivikohtaiset kaavat. Kaiken säilyttävä toimintamalli on jättää pohjaan yksi täysin muotoiltu mallirivi ja kloonata se jokaiselle kohteelle. CopyRange monistaa tyylit ja kaavat yhdellä kutsulla, minkä jälkeen generaattori korvaa vain arvot sisältävät solut
const
DetailRow = 10; // mallin muotoiltu esimerkkirivi
var
I: Integer;
begin
// Tee tilaa ennen summablokkia ensin, jotta SUM-alue
// yksityiskohtavyön alapuolella venyy yhdessä datan kanssa.
if Length(Items) > 1 then
Sheet.InsertRows(DetailRow + 1, Length(Items) - 1);
for I := 0 to High(Items) do
begin
if I > 0 then // kloonaa tyylit ja kaavat esimerkkirivistä
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]); // ei '='-etuliitettä
end;
end;Huomioi kaavan sijoittaminen tarkasti. XLSX:n Formula-ominaisuus vastaanottaa lausekkeen ilman alussa olevaa yhtäsuuruusmerkkiä, kun taas XLS-julkisivu odottaa '=B10*C10'-arvon sijoitettavan Value-ominaisuuden kautta. Näiden käytäntöjen sekoittaminen on yleisin luokkaperheiden välinen siirto-ongelma, ja se epäonnistuu ilman huomautusta: solu sisältää vain kirjaimellisen merkkijonon, jonka Excel näyttää tekstinä. Jos pohja koristaa tieto-osan yhdistetyillä otsikkoriveillä, muista, että vain yhdistetyn alueen vasemmassa yläkulmassa oleva solu sisältää arvon. Yhdistettyjä soluja asetteluvetoisissa raporttipohjissa käsittelevä kumppaniartikkeli selittää, miksi yhdistetyt alueet kuuluvat kokonaan tieto-osan ulkopuolelle
Mitä InsertRows siirtää ja mitä se jättää ennalleen
Rivien lisääminen loppusummalohkon eteen pitää SUM-alueen venyvänä tieto-osan kasvaessa. XLSX-puolella InsertRows siirtää solujen mukana pitkän joukon riippuvia rakenteita: yhdistetyt alueet, rivikorkeudet, hyperlinkit, kommentit, jäädytetyt ruudut, automaattisuodatinalueet, ehdolliset muotoilut, tietojen kelpoisuustarkistukset, taulukot, määritetyt nimet sekä kuvien ja kaavioiden ankkurit. Luettelossa on yksi raja, joka kannattaa muistaa. Kaavojen uudelleenkirjoitus ulottuu vain saman laskentataulukon viittauksiin. Yhteenvetotaulukon kaava, joka osoittaa siirretylle alueelle, säilyttää vanhat koordinaattinsa ja lukee hiljaisesti vääriä soluja, minkä vuoksi arkit ylittävät loppusummat on turvallisempaa ilmaista työkirjatason nimien avulla. Määritettyjä nimiä ja arkkien välisiä kaavoja käsittelevä kumppaniartikkeli käy läpi tämän mallin
Vanha XLS-muoto vetää rajan ankarampaan kohtaan. HotXLS säilyttää BIFF-tiedostojen pivot-taulukot, kyselytaulukot ja ulkoiset tietoyhteydet raakoina tavulohkoina. Ne säilyvät muuttumattomina avauksessa ja tallennuksessa, mutta niitä ei mallinneta, joten rivien lisäys ei koskaan kosketa niitä. Pohja, joka sijoittaa pivot-taulukon laajenevan tieto-osan alle, tallentuu täysin ilman varoitusta samalla kun pivotin lähderektangeli ajautuu pois tiedoista. Ratkaisu on rakenteellinen, ei puolustava: pidä pivot- ja kyselysisältö arkeilla, joihin generaattori ei koskaan lisää rivejä, jolloin vanhentuminen ei ole mahdollista
Laske uudelleen ennen toimitusta tai tiedä, miksi jätit sen tekemättä
HotXLS ei laske kaavoja SaveAs-kutsun aikana. Kun henkilö avaa tiedoston, Excel laskee kaiken uudelleen (XLS-julkisivu tarjoaa CalculationMode- ja RecalcOnSave-ominaisuudet, jos haluat ohjata tätä), joten ihmisen sähköpostiin tarkoitettu raportti ei vaadi sinulta enempää. Tilanne muuttuu heti, kun työkirja syöttää toista ohjelmaa. CSV-vienti kirjoittaa kaavat kirjaimellisena tekstinä eikä koskaan laske niitä, ja mikä tahansa välimuistiarvoihin luottava jatkojalostaja lukee vanhentuneita lukuja tai tyhjiä arvoja. Näillä poluilla laske palvelimella Calculate-kutsulla, joka arvioi mielivaltaisen lausekkeen ladattua työkirjaa vasten ja palauttaa tuloksen:
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;Lasketun loppusumman tarkistaminen tilausriviä vasten ennen tallennusta on edullinen varmistus, josta saa hyvän hyödyn. Se muuttaa virheellisen laskun epäonnistuneeksi työksi. Operaattori voi yrittää epäonnistunutta työtä uudelleen sekunneissa; asiakkaan postilaatikossa oleva virheellinen lasku maksaa asiakkuuspäällikölle anteeksipyynnön ja korjauksen
Kaksi luokkaperhettä, yksi algoritmi
Sama logiikka siirtyy muotojen välillä, mutta sama koodi ei. Vanhaa .xls-muotoa varten tarkoitettu TXLSWorkbook on rajapintapohjainen ja viitelaskettu, sen arkkien indeksointi alkaa yhdestä eikä sitä koskaan vapauteta käsin. .xlsx-muotoa varten tarkoitettu TXLSXWorkbook on tavallinen olio, joka on vapautettava try..finally-lohkossa, sen arkkien indeksointi alkaa nollasta ja sen kaavakäytäntö on esitetty yllä. FindText, ReplaceText, CopyRange ja InsertRows ovat käytettävissä molemmilla puolilla, joten ankkuroi-kloonaa-laske uudelleen -rakenne siirtyy siististi. Käytännön neuvo on sitoutua yhteen muotoon kutakin putkea kohti tai piilottaa kaksi oliota elinkaarta oman ohuen sovittimen taakse sen sijaan, että levittäisit erot generaattoriin
Koko on harvoin merkityksellinen tällaisen raportin tuottamisessa. Muotoillun rivin kloonaaminen muutaman tuhannen kerran ei rasita nykyistä laitteistoa. Tallennuspolusta tulee pullonkaula vasta, kun tieto-osassa on satojatuhansia rivejä, ja silloin StreamingWrite-asetuksen käyttöönotto lähettää laskentataulukon XML:n suoraan tulospakettiin puskuroinnin sijaan; palvelineräajojen suoratoistokirjoitusta käsittelevä artikkeli kertoo, milloin tämä kompromissi kannattaa. Kaaviot käyttäytyvät samoin kuin muu asettelu: XLSX-puolella sekä kaavion ankkuri että sen sarjaviittaukset siirtyvät, kun niiden yläpuolella suoritetaan InsertRows, joten loppusummarivin alla oleva kaavio pysyy sidottuna oikeisiin tietoihin, kun taas XLS-puolella kaaviot sijaitsevat omilla kaavioarkeillaan eivätkä pivot-taulukoiden tavoin koskaan siirry. Tämä on yksi lisäperuste pitää esitysarkit erillään arkista, jota generaattori laajentaa
Tämä ankkuroi-kloonaa-laske uudelleen -lähestymistapa antaa suunnittelijan omistaa työkirjan ulkoasun ja koodisi sen sisällön, mikä yleensä tekee tuotetun Excel-tulosteen ylläpidosta kannattavaa. Tässä esitetyt haku-, kopiointi- ja lisäyskutsut sekä toimitusta edeltävään loppusumman tarkistukseen käytetty kaavamoottori sisältyvät Delphiä ja C++Builderia varten tarkoitettuun HotXLS Delphi Component -komponenttiin