Izvoz Excel radnih svezaka u CSV, TSV, HTML i RTF iz Delphi-ja pomoću HotXLS

Zamislite noćni posao koji u kodu gradi radnu svesku faktura i zapisuje je kao CSV kako bi je uvezao sistem u kasnijoj fazi. Brojevi izgledaju ispravno u Excel-u. CSV se uredno otvara u uređivaču teksta. Zanim se uvoznik blokira na koloni sa ukupnim iznosima, jer polje za iznos u 42. redu glasi =SUM(D2:D41) – formula kao doslovan tekst, a ne cifra na koju bi trebalo da se izračuna. Ništa nije pokvareno. Ovo je dokumentovano ponašanje i to je prva stvar koju treba razumeti kod izvoza iz HotXLS-a: pisac serijalizuje model ćelije tačno onako kako stoji, a ćelija formule čija vrednost nikada nije izračunata ima samo tekst svoje formule da preda dalje.

Zašto vaš CSV sadrži formule umesto brojeva

HotXLS čuva tekst formule i izračunatu vrednost kao dve odvojene stvari. Funkcija SaveAsCSV po dizajnu ne pokreće mehanizam za proračune prilikom izlaza: izvoz ne bi trebalo da menja radnu svesku i ne bi trebalo da rizikuje zastoje na komplikovanim lancima formula. Fajlovi koje je sam Excel sačuvao nose keširane rezultate pored formula, pa se njihov ponovni izvoz ponaša onako kako očekujete. Zamka je specifična za radne sveske koje je vaš sopstveni kod generisao, gde su formule upisane ali nikada nisu procenjene. Rešenje je da vrednosti postoje pre izvoza, koristeći isti mehanizam Calculate koji razrešava reference između listova i prilagođene funkcije:

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

    // Materialize formula results so the CSV carries numbers, not '=...' text
    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, ',');    // sheet 0, comma
    Book.SaveAsCSV('feed.tsv', 0, #9);     // same sheet as TSV
  finally
    Book.Free;
  end;
end;

Obratite pažnju na to šta petlja zapravo radi: ona prepisuje ćelije formula njihovim izračunatim vrednostima. To je sasvim u redu za privremeni prolaz izvoza, ali je pogrešno ako nakon toga nameravate ponovo da sačuvate radnu svesku kao .xlsx, jer ste upravo zamenili aktivne formule fiksiranim brojevima. Izvezite iz kopije ili ograničite upisivanje tako da utiče samo na izvozni tok. Mehanizam iza funkcije Calculate ide i dalje od ovoga, uključujući registraciju vaših sopstvenih funkcija, što je tema članka o HotXLS mehanizmu za formule i prilagođenim funkcijama.

Šta pisac razgraničenog teksta garantuje

CSV putanja proizvodi UTF-8 sa oznakom redosleda bajtova (BOM), CRLF završetke redova i navodnike prema RFC 4180 standardu. Svako polje koje sadrži graničnik, navodnik ili prelom reda biva umotano u navodnike, a ugrađeni navodnici se dupliraju. Datumi se prikazuju u formatu yyyy-mm-dd hh:nn:ss bez obzira na prikazni format ćelije. To je ispravna odluka za mašinsko konzumiranje, iako može iznenaditi one koji su očekivali da se formatiranje sa ekrana prenese. Ćelije bogatog teksta se spljoštavaju spajanjem njihovih segmenata.

Ova podrazumevana podešavanja rešavaju većinu sporova sa uvoznikom pre nego što počnu, ali dva od njih bi ipak trebalo da bude deo vašeg ugovora o interfejsu. Prvo je BOM. To je ono što omogućava Excel-u da otvori fajl sa sačuvanim dijakritičkim znakovima, ali nekoliko strogih parsera tretira ta tri bajta kao podatke; ako je vaš parser među njima, uklonite ih prilikom predaje. Drugo je TSV. To uopšte nije zasebna funkcija, već isti pisac pozvan sa #9 kao graničnikom, tako da se sve gorenavedeno odnosi i na njega bez izmena. List za izvoz se bira pomoću indeksa zasnovanog na nuli u preopterećenju sa više argumenata, dok skraćeni jednokomponentni poziv SaveAsCSV(FileName) uzima aktivni list.

HTML izvoz je snimak stanja, a ne format za razmenu

Dok CSV odbacuje sve osim vrednosti, SaveAsHTML pokušava da zadrži izgled: jedna <table> po listu, spojene ćelije izražene kroz colspan i rowspan, i osnovni stil ćelija ugrađen kao CSS. Boje povezane sa temom se preskaču umesto da se razrešavaju, pa šablon koji se oslanja na slotove tema ispada jednostavniji nego što izgleda u Excel-u. Postavite eksplicitne RGB boje na sve što mora da preživi konverziju. Objekat opcija kontroliše detalje:

var
  Opts: TXLSXHtmlExportOptions;
begin
  Opts := TXLSXHtmlExportOptions.Create;
  try
    Opts.Title := 'Weekly settlement';
    Opts.TableClass := 'report-grid';     // hook for the host page stylesheet
    Opts.WriteDocument := True;           // full page, not a fragment
    if Book.SaveAsHTML('settlement.html', 0, Opts) <> 0 then
      raise Exception.Create('Sheet index out of range');
  finally
    Opts.Free;
  end;
end;

Dva detalja u tom isečku koda zaslužuju pažnju. Postavite WriteDocument na False i izlaz postaje običan fragment tabele umesto pune stranice, što je upravo ono što želite kada ubacujete pregled u postojeći raspored: postavite TableClass i prepustite stilizovanje tabeli domaćinu. Konvencija povratne vrednosti je takođe suprotna većini poziva u HotXLS-u. SaveAsHTML vraća 0 u slučaju uspeha i -1 za neispravan indeks lista, pa će provera za = 1 prijaviti svaki uspešan izvoz kao grešku. Kada vam je potrebna regija umesto celog lista, možda da biste je poslali e-poštom ili ugradili pojedinačni blok, TXLSXRange.SaveAsHTML izvozi bilo koji pravougaoni opseg pod istim pravilima renderovanja.

RTF izlaz i gde on i dalje nalazi svoje mesto

Četvrta ciljna destinacija upisuje RTF 1.6 tabele, jedan list po pozivu preko SaveAsRTF. Širine kolona su aproksimirane na otprilike 96 twipa po karakteru širine kolone. Strukturno ograničenje koje treba znati jeste da se spojene ćelije ne šire u izlazu: samo početna ćelija nosi svoj sadržaj, dok se prekrivene ćelije emituju kao prazne. To isključuje RTF za šablone sa složenim rasporedom. Ipak, on i dalje nalazi svoje mesto kao linija najmanjeg otpora za ubacivanje tabelarnih rezultata u procesor teksta ili u nasleđeni sistem za upravljanje dokumentima koji ne podržava HTML uvoz.

Kružni tok: uvoz CSV-a je destruktivan po dizajnu

Čitanje CSV-a nazad ima sopstvena pravila. OpenCSV briše celu radnu svesku i ponovo je gradi kao jedan list pod nazivom Sheet1. To je po duhu konstruktor, a ne spajanje, tako da ga nikada ne pozivajte na radnoj svesci koja još uvek sadrži nesačuvani sadržaj. Prosleđivanje #0 kao separatora pokreće automatsku detekciju graničnika. Zastavica ADetectTypes kontroliše promociju tipova: kada je uključena, numerički stringovi postaju brojevi, ISO-8601 stringovi postaju datumi, a true/false postaju bulove vrednosti. Isključite je kada tok nosi identifikatore sa vodećim nulama, poštanske brojeve ili šifre proizvoda, jer ih promocija tiho pretvara u brojeve (vodeća nula jednostavno nestaje onog trenutka kada 00123 postane 123). Oba interfejsa izlažu isti uvoz. Kombinujte ga sa gorepomenutim pozivima za izvoz i dobićete most između formata koji ne zahteva instaliran Excel nigde u sistemu, što je scenario pokriven u članku o generisanju Excel izveštaja iz baze podataka pomoću HotXLS-a.

Izvoz direktno u tok (stream)

Svaki pisac ovde ima preopterećenje toka (stream) koje stoji pored verzije sa nazivom fajla: CSV, HTML, RTF, kao i sami formati radnih svezaka. U serverskom kodu, to su preopterećenja koja treba koristiti. Veb krajnja tačka (web endpoint) koja isporučuje preuzimanje CSV-a može pisati u TMemoryStream i to predati direktno objektu odgovora, bez privremenih fajlova, poslova čišćenja i sudara između dva zahteva koja su slučajno odabrala isto generisano ime. Isto važi i za slanje izvezenih podataka u blob skladište ili njihovo prilaganje odlaznoj e-pošti. U potpunosti se gubi iz jednačine.

Ovaj obrazac se kombinuje sa načinom na koji se biblioteka implementira. Oba interfejsa su izvorni Object Pascal čitači i pisci, tako da nema instalacije Excel-a, COM automatizacije, niti uskog grla po procesu koji serijalizuje zahteve na serveru. Svaki zahtev može imati sopstveni objekat radne sveske, pokrenuti upisivanje rezultata proračuna iz prvog odeljka i strimovati svoj izvoz paralelno sa susednim zahtevima. Memorija je jedini resurs na koji treba obratiti pažnju. Model radne sveske živi u RAM-u tokom trajanja izvoza, pa servis koji otvara veoma velike fajlove samo da bi ih ponovo emitovao kao CSV treba da ograniči broj istovremenih poslova ili da stavi u red one prevelike, umesto da dozvoli da skok u saobraćaju odluči o radnom setu.

Još jedna sitnica: postavite IncludeBOM u HTML opcijama kada će se fragment čuvati kao samostalan fajl čije kodiranje neki alat u kasnijoj fazi proverava. Kada HTML isporučujete direktno preko HTTP-a, ostavite deklaraciju skupa karaktera (charset) zaglavljima odgovora.

Kada bajtovi i dalje izlaze pogrešno

Najčešće pitanje podrške u vezi sa CSV izvozom jeste problem otvaranja u drugom obliku: Excel prikazuje nečitljive karaktere (mojibake) umesto dijakritika. Instinkt je da se okrivi pisac, ali on emituje UTF-8 BOM upravo iz tog razloga, i fajl je skoro uvek ispravan kada napusti vaš kod. Nešto između tog mesta i Excel-a je "pojedlo" BOM. FTP prenos u tekstualnom režimu, kopiranje toka koje preskače prva tri bajta, proksi koji vrši ponovno kodiranje na putu – bilo šta od toga će ukloniti oznaku i prepustiti Excel-u da nagađa kodiranje, što on radi loše. Dijagnostikujte ovo na samoj granici prenosa, a ne u pozivu za izvoz. Otvorite isporučeni fajl u heksadecimalnom pregledaču i potvrdite da je EF BB BF i dalje prva stvar u njemu.

To je zajednička nit za sva četiri formata. Poziv za izvoz je lakši deo posla, a HotXLS donosi opravdanu odluku pri svakom izboru sa kojim se pisac suočava. Neuspesi žive na spojevima: tamo gde se tekst formule susreće sa parserom koji je želeo broj, gde se BOM susreće sa transportom koji ga ne čuva, gde se spojena ćelija susreće sa RTF-ovim ravnim modelom tabele. Svaka od tih stavki je činjenica koju treba upisati u ugovor između vašeg izvoznika i onoga što izvoz konzumira, jer primalac ne može pročitati vaše namere iz samih bajtova. Za kompletnu listu metoda kroz oba interfejsa radnih svezaka, stranica proizvoda HotXLS Component nosi punu referencu.