Excel-työkirjojen vienti CSV-, TSV-, HTML- ja RTF-muotoihin Delphistä HotXLS:llä

Kuvittele yöajo, joka muodostaa laskutyökirjan koodissa ja kirjoittaa sen CSV-muotoon jatkojärjestelmän tuontia varten. Luvut näyttävät oikeilta Excelissä. CSV avautuu siististi tekstieditorissa. Sitten tuoja takeltelee loppusummasarakkeessa, koska rivin 42 määräkentässä lukee =SUM(D2:D41), kaava kirjaimellisena tekstinä eikä laskettavana lukuna. Mikään ei ole rikki. Tämä on dokumentoitua toimintaa, ja se on ensimmäinen asia, joka HotXLS-viennistä on ymmärrettävä: kirjoitin sarjallistaa solumallin täsmälleen senhetkisenä, ja kaavasolulla, jonka arvoa ei ole koskaan laskettu, on luovutettavanaan vain kaavateksti

Miksi CSV-tiedostosi sisältää kaavoja lukujen sijaan

HotXLS säilyttää kaavatekstin ja lasketun arvon kahtena erillisenä asiana. SaveAsCSV ei tarkoituksella suorita laskentamoottoria viennin yhteydessä: viennin ei pidä muuttaa työkirjaa eikä juuttua haitalliseen kaavaketjuun. Excelin itsensä tallentamissa tiedostoissa kaavojen vieressä on välimuistiin tallennetut tulokset, joten niiden uudelleenvienti toimii odotetusti. Ansana ovat oman koodisi luomat työkirjat, joissa kaavat on kirjoitettu, mutta niitä ei ole koskaan arvioitu. Korjaus on varmistaa arvojen olemassaolo ennen vientiä käyttämällä samaa Calculate-moottoria, joka ratkaisee arkkiviittaukset ja mukautetut funktiot:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  R: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('invoice-run.xlsx');
    Sheet := Book.Sheets[0];

    // Materialisoi kaavatulokset, jotta CSV välittää lukuja, ei '=...' tekstiä
    for R := 2 to 41 do
      if Sheet.Cells[R, 4].Formula <> '' then
        Sheet.Cells[R, 4].Value := Book.Calculate(Sheet.Cells[R, 4].Formula);

    Book.SaveAsCSV('feed.csv', 0, ',');    // arkki 0, pilkku
    Book.SaveAsCSV('feed.tsv', 0, #9);     // sama arkki kuin TSV
  finally
    Book.Free;
  end;
end;

Huomaa, mitä silmukka todella tekee: se korvaa kaavasolut niiden lasketuilla arvoilla. Se on juuri oikein kertakäyttöistä vientivaihetta varten ja väärin, jos aiot tallentaa työkirjan myöhemmin uudelleen .xlsx-muodossa, sillä olet juuri korvannut toimivat kaavat pysäytetyillä luvuilla. Vie kopio tai rajaa takaisin kirjoittaminen koskemaan vain vientiajoa. Calculate-moottori tekee tätäkin enemmän, mukaan lukien omien funktioidesi rekisteröinnin, jota käsitellään HotXLS:n kaavamoottoria ja mukautettuja funktioita koskevassa artikkelissa

Mitä erotinmerkkeihin perustuva kirjoitin takaa

CSV-vienti tuottaa UTF-8-tiedoston tavujärjestysmerkinnällä, CRLF-rivinvaihdoilla ja RFC 4180 -lainauskäytännöllä. Kaikki erotinmerkin, lainausmerkin tai rivinvaihdon sisältävät kentät ympäröidään lainausmerkeillä, ja sisäkkäiset lainausmerkit kahdennetaan. Päivämäärät esitetään muodossa yyyy-mm-dd hh:nn:ss solun näyttömuodosta riippumatta. Se on oikea ratkaisu koneelliselle vastaanottajalle, vaikka se yllättää henkilön, joka odotti näytöllä olevan muotoilun säilyvän. Rich text -solut litistetään yhdistämällä niiden osat

Nämä oletukset ratkaisevat useimmat kiistat tuojan kanssa ennen niiden alkamista, mutta kaksi niistä kuuluu silti käyttöliittymäsopimukseesi. Ensimmäinen on BOM. Sen ansiosta Excel avaa tiedoston säilyttäen aksenttimerkit, mutta muutamat tiukat jäsentimet pitävät näitä kolmea tavua tietona; jos omasi on tällainen, poista ne luovutuksessa. Toinen on TSV. Se ei ole lainkaan erillinen ominaisuus, vaan sama kirjoitin, jota kutsutaan käyttäen erotinmerkkinä #9, joten kaikki edellä mainittu koskee sitä muuttumattomana. Vietävä arkki valitaan usean argumentin ylikuormituksessa 0-pohjaisella indeksillä, kun taas yhden argumentin lyhennys SaveAsCSV(FileName) käyttää aktiivista arkkia

HTML-vienti on tilannekuva, ei tiedonsiirtomuoto

Kun CSV hylkää kaiken arvoja lukuun ottamatta, SaveAsHTML pyrkii säilyttämään ulkoasun: yksi <table> arkkia kohden, yhdistetyt alueet colspan- ja rowspan-attribuutteina sekä perussolutyylit upotettuna CSS:nä. Teemaan suhteutetut värit ohitetaan ratkaisemisen sijaan, joten teemapaikkoihin nojaava malli tulee ulos pelkistetympänä kuin Excelissä. Aseta nimenomaiset RGB-värit kaikelle, minkä on säilyttävä matkassa. Asetusobjekti hallitsee kehystä:

var
  Opts: TXLSXHtmlExportOptions;
begin
  Opts := TXLSXHtmlExportOptions.Create;
  try
    Opts.Title := 'Weekly settlement';
    Opts.TableClass := 'report-grid';     // koukku isäntäsivun tyylitiedostolle
    Opts.WriteDocument := True;           // koko sivu, ei fragmentti
    if Book.SaveAsHTML('settlement.html', 0, Opts) <> 0 then
      raise Exception.Create('Sheet index out of range');
  finally
    Opts.Free;
  end;
end;

Kaksi yksityiskohtaa esimerkissä ansaitsee huomiota. Kun WriteDocument-asetuksen vaihtaa arvoon False, tuloksena on paljas taulukko-osa kokonaisen sivun sijaan. Tämä on oikea valinta, kun esikatselu lisätään olemassa olevaan asetteluun: aseta TableClass ja anna isäntäsivun tyylitiedoston hoitaa teemoitus. Myös palautussopimus on useimpiin HotXLS-kutsuihin nähden päinvastainen. SaveAsHTML palauttaa onnistumisesta 0 ja virheellisestä arkki-indeksistä -1, joten tottumuksesta tehty tarkistus = 1 ilmoittaa jokaisen onnistuneen viennin epäonnistuneeksi. Kun tarvitset kokonaisen arkin sijaan alueen, esimerkiksi yhden lohkon sähköpostiin tai upotettavaksi, TXLSXRange.SaveAsHTML vie minkä tahansa suorakulmaisen alueen samoilla hahmonnussäännöillä

RTF-tuloste ja missä se on yhä hyödyllinen

Neljäs kohde kirjoittaa RTF 1.6 -taulukoita, yhden arkin kutsua kohden SaveAsRTF-menetelmällä. Sarakeleveydet arvioidaan noin 96 twipiksi sarakeleveyden merkkiä kohden. Rakenteellinen rajoitus on, etteivät yhdistetyt solut ulotu tulosteessa: vain ankkurisolu sisältää sisältönsä, ja peitetyt solut tulostuvat tyhjinä. Tämä sulkee RTF:n pois paljon asettelua käyttävistä malleista. Se on silti vaivattomin reitti, kun taulukkomaisia tuloksia pitää pudottaa tekstinkäsittelyohjelmaan tai vanhaan asiakirjahallintajärjestelmään, joka on peräisin HTML-tuonnin ajalta

Edestakainen muunnos: CSV-tuonti on tarkoituksella tuhoava

CSV:n takaisinlukemisella on oma sopimuksensa. OpenCSV tyhjentää koko työkirjan ja rakentaa sen uudelleen yhtenä Sheet1-nimisenä arkkina. Se on luonteeltaan konstruktori, ei yhdistäminen, joten älä koskaan kutsu sitä työkirjalle, jossa on vielä tallentamatonta sisältöä. Erottimenä annettu #0 käynnistää automaattisen erotinmerkin tunnistuksen. ADetectTypes-lippu hallitsee tyyppien ylennystä: käytössä ollessaan numeromerkkijonoista tulee lukuja, ISO-8601-merkkijonoista päivämääriä ja true/false-arvoista totuusarvoja. Poista se käytöstä, kun syöte sisältää etunollallisia tunnisteita, postinumeroita tai tuotekoodeja, sillä ylennys muuttaa ne hiljaisesti luvuiksi (etunolla katoaa heti, kun 00123 muuttuu muotoon 123). Molemmat rajapinnat tarjoavat saman tuonnin. Yhdistä se yllä oleviin vientikutsuihin, niin saat muotosillan, joka ei tarvitse Exceliä missään vaiheessa. Tätä tilannetta käsitellään HotXLS:n tietokanta–Excel-raporttien muodostamista koskevassa artikkelissa

Vienti suoraan virtaan

Jokaisella tässä käsitellyllä kirjoittimella on tiedostonimiversion vieressä virran vastaanottava ylikuormitus: CSV, HTML, RTF ja itse työkirjamuodot. Palvelinkoodissa näitä ylikuormituksia kannattaa käyttää. CSV-latauksen tarjoava verkkopäätepiste voi kirjoittaa TMemoryStream-virtaan ja luovuttaa sen suoraan vastausobjektille ilman väliaikaistiedostoa, siivoustyötä tai kahden samaan luotuun nimeen päätyneen pyynnön törmäystä. Sama koskee vientien työntämistä blob-tallennukseen tai niiden liittämistä lähteviin viesteihin. Tiedostojärjestelmä jää kokonaan pois kuvasta

Tämä malli vahvistuu kirjaston käyttöönoton tavasta. Molemmat rajapinnat ovat natiiveja Object Pascal -lukijoita ja -kirjoittajia, joten palvelimella ei tarvita Excel-asennusta, COM-automaatiota eikä pyyntöjä sarjallistavaa prosessikohtaista pullonkaulaa. Jokainen pyyntö voi omistaa oman työkirjaobjektinsa, suorittaa ensimmäisen osan laskennan takaisin kirjoittamisen ja suoratoistaa vientinsä rinnakkain muiden kanssa. Muisti on ainoa resurssi, jota on seurattava. Työkirjamalli pysyy RAM-muistissa viennin ajan, joten palvelun, joka avaa hyvin suuria tiedostoja vain lähettääkseen ne uudelleen CSV-muodossa, kannattaa rajoittaa samanaikaisia töitä tai jonottaa ylisuuria töitä sen sijaan, että liikennepiikin annetaan päättää työmuistin koko

Yksi pienempi säätö: aseta HTML-asetuksissa IncludeBOM, kun osa tallennetaan erilliseksi tiedostoksi, jonka jokin jatkotyökalu tunnistaa koodauksen perusteella. Kun HTML palvellaan suoraan HTTP:n kautta, jätä merkistömäärittely vastausotsakkeille

Kun tavut tulevat yhä ulos väärin

Yleisin CSV-vientiä koskeva tukikysymys on aloitusongelma toisessa asussa: Excel näyttää aksenttimerkkien sijaan merkkisotkua. Vaisto syyttää kirjoitinta, mutta se tuottaa UTF-8 BOM:n juuri tästä syystä, ja tiedosto on lähes aina oikea lähtiessään koodistasi. Jokin sen ja Excelin välissä on syönyt BOM:n. FTP-siirto tekstimuodossa, virran kopio, joka ohittaa ensimmäiset kolme tavua, tai välityspalvelin, joka uudelleenkoodaa tiedot matkalla: mikä tahansa näistä poistaa merkinnän ja jättää Excelin arvaamaan koodauksen, mitä se tekee huonosti. Selvitä tämä rajapinnassa, älä vientikutsussa. Avaa toimitettu tiedosto heksakatselimessa ja varmista, että EF BB BF on yhä sen ensimmäinen sisältö

Se on kaikkien neljän muodon punainen lanka. Vientikutsu on helppo osa, ja HotXLS tekee perusteltavan valinnan jokaisessa kirjoittimen kohtaamassa päätöksessä. Epäonnistumiset ovat liitoskohdissa: kun kaavateksti kohtaa lukuja odottavan jäsentimen, kun BOM kohtaa sitä säilyttämättömän siirron tai kun yhdistetty solu kohtaa RTF:n litteän taulukkomallin. Jokainen niistä on kirjattava viejäsi ja sen vastaanottajan väliseen sopimukseen, sillä vastaanottaja ei voi lukea aikomuksiasi tavuista. Molempien työkirjarajapintojen täydellinen menetelmäluettelo on HotXLS Delphi Component -tuotesivulla