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

Zamislite noćni posao koji u kodu kreira radnu svesku sa fakturama i upisuje je kao CSV kako bi je neki drugi sistem uvezao. Brojevi izgledaju ispravno u Excel-u. CSV se čisto otvara u tekstualnom editoru. A onda se uvoznik zaglavi na koloni sa ukupnim vrednostima, jer polje za iznos u redu 42 glasi =SUM(D2:D41) — formula kao doslovan tekst, a ne broj na koji bi trebalo da se proračuna. Ništa nije pokvareno. Ovo je dokumentovano ponašanje, i to je prva stvar koju treba razumeti u vezi sa izvozom iz HotXLS-a: pisač serijalizuje model ćelije tačno onako kako on stoji, a ćelija sa formulom čija vrednost nikada nije izračunata ima samo svoj tekst formule koji može da preda

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

HotXLS čuva tekst formule i izračunatu vrednost kao dve odvojene stvari. Po dizajnu, SaveAsCSV ne pokreće mehanizam za proračun prilikom izvoza: izvoz ne bi trebalo da menja radnu svesku i ne bi trebalo da rizikuje zastoje na patološkim lancima formula. Fajlovi koje je sam Excel sačuvao nose keširane rezultate pored formula, tako da se ponovni izvoz tih fajlova ponaša onako kako očekujete. Zamka je specifična za radne sveske koje je generisao vaš sopstveni kod, gde su formule napisane ali nikada nisu evaluirane. Rešenje je da učinite da vrednosti postoje pre nego što izvršite izvoz, koristeći isti mehanizam Calculate koji razrešava međulistne reference 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;

Pogledajte šta petlja zapravo radi: ona prepisuje ćelije sa formulama njihovim izračunatim vrednostima. To je sasvim u redu za privremeni prolaz izvoza, ali je pogrešno ako nameravate da radnu svesku nakon toga ponovo sačuvate kao .xlsx, jer ste upravo zamenili žive formule statičkim brojevima. Izvršite izvoz iz kopije ili ograničite povratni upis (write-back) tako da utiče samo na proces izvoza. Mehanizam iza metode Calculate ide i dalje od ovoga, uključujući registrovanje sopstvenih funkcija, što je tema članka HotXLS mehanizam formula i prilagođene funkcije

Šta pisač razgraničenog teksta garantuje

Putanja CSV-a proizvodi UTF-8 sa oznakom redosleda bajtova (BOM), CRLF završecima redova i navodnicima prema standardu RFC 4180. Svako polje koje sadrži graničnik (delimiter), navodnik ili prelom reda biva umotano u navodnike, a ugrađeni navodnici se dupliraju. Datumi se prikazuju u obliku yyyy-mm-dd hh:nn:ss bez obzira na format prikaza ćelije. To je ispravna odluka za mašinske potrošače, iako iznenađuje sve koji su očekivali da se formatiranje sa ekrana prenese. Ćelije sa bogatim tekstom (rich text) se ravnaju spajanjem njihovih delova

Ova podrazumevana podešavanja rešavaju većinu sporova sa uvoznikom pre nego što počnu, ali dva od njih bi svakako trebala da budu deo vašeg ugovora o interfejsu. Prvi je BOM. To je ono što omogućava Excel-u da otvori fajl sa sačuvanim karakterima sa akcentima, ali nekoliko strogih parsera tretira ova tri bajta kao podatke; ako je vaš parser takav, uklonite ih prilikom primopredaje. Drugi je TSV. To uopšte nije zasebna funkcija, već isti pisač koji se poziva sa #9 kao graničnikom, tako da se sve gore navedeno odnosi i na njega bez promena. List za izvoz se bira pomoću indeksa koji počinje od 0 u preopterećenju sa više argumenata, dok skraćenica SaveAsCSV(FileName) sa jednim argumentom uzima aktivni list

Izvoz u HTML je snimak stanja, a ne format za razmenu

Tamo gde CSV odbacuje sve osim vrednosti, metoda SaveAsHTML pokušava da zadrži izgled: jedna <table> po listu, spojene oblasti izražene kroz colspan i rowspan, i osnovno stilizovanje ćelija ugrađeno kao CSS. Boje relativne u odnosu na temu se preskaču umesto da se razrešavaju, tako da š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 prenos. Objekat opcija kontroliše omotač:

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 ovom isečku koda zaslužuju pažnju. Postavite WriteDocument na False i izlaz postaje čist fragment tabele umesto pune stranice, što je upravo ono što želite kada ubacujete pregled u postojeći raspored: podesite TableClass i prepustite stilizovanje stilskom listu (stylesheet) domaćina. Konvencija o povratnoj vrednosti je takođe suprotna većini poziva u HotXLS-u. Metoda SaveAsHTML vraća 0 u slučaju uspeha i -1 za neispravan indeks lista, tako da će rutinska provera na vrednost = 1 prijaviti svaki uspešan izvoz kao neuspeh. Kada vam je potreban samo deo (regija) umesto celog lista, na primer za slanje e-poštom ili ugrađivanje pojedinačnog bloka, TXLSXRange.SaveAsHTML izvozi bilo koji pravougaoni opseg pod istim pravilima renderovanja

RTF izlaz i gde on još uvek nalazi svoje mesto

Četvrto ciljno odredište piše tabele RTF 1.6, jedan list po pozivu preko SaveAsRTF. Širine kolona su aproksimirane na otprilike 96 twipa po karakteru širine kolone. Strukturno ograničenje koje treba znati je da se spojene ćelije ne šire u izlazu: samo ćelija sidro nosi svoj sadržaj, dok se prekrivene ćelije emituju kao prazne. To isključuje RTF za šablone sa složenim rasporedom. On i dalje nalazi svoje mesto kao put najmanjeg otpora za prenos tabelarnih rezultata u procesor teksta ili u starije sisteme za upravljanje dokumentima koji su nastali pre uvođenja HTML-a

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

Čitanje CSV-a nazad ima sopstveni ugovor. Metoda OpenCSV prazni čitavu radnu svesku i ponovo je izgrađuje kao jedan list pod nazivom Sheet1. To je u duhu konstruktor, a ne spajanje, tako da je 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 logičke vrednosti (booleani). Isključite je kada sadržaj nosi identifikatore sa vodećim nulama, poštanske brojeve ili šifre proizvoda, koje bi promocija tiho pokvarila pretvaranjem u brojeve (vodeća nula jednostavno nestaje onog trenutka kada 00123 postane 123). Obe fasade izlažu isti uvoz. Uparite ga sa gore navedenim pozivima za izvoz i dobićete most formata koji ne zahteva instaliran Excel nigde u cevovodu, što je scenario pokriven u članku generisanje izveštaja iz baze podataka u Excel pomoću HotXLS-a

Izvoz direktno u tok (stream)

Svaki pisač ovde ima preopterećenje toka (stream overload) koje se nalazi pored verzije sa nazivom fajla: CSV, HTML, RTF i sami formati radnih svezaka. U serverskom kodu ta preopterećenja su ona koja treba koristiti. Veb krajnja tačka (web endpoint) koja isporučuje CSV preuzimanje može pisati u TMemoryStream i predati ga direktno objektu odgovora, bez privremenog fajla, bez posla oko čišćenja i bez sudara između dva zahteva koja su slučajno izabrala isto generisano ime. Isto važi i za slanje izvoza u blob skladište ili njihovo priloženje odlaznoj e-pošti. Fajl sistem u potpunosti ispada iz slike

Ovaj šablon se nadovezuje na način na koji se biblioteka primenjuje. Obe fasade su izvorni Object Pascal čitači i pisači, tako da nema instalacije Excel-a, nema COM automatizacije i nema uskog grla po procesu koje serijalizuje zahteve na serveru. Svaki zahtev može posedovati sopstveni objekat radne sveske, pokrenuti povratni proračun iz prvog odeljka i strimovati svoj izvoz paralelno sa susednim procesima. Memorija je jedini resurs na koji treba obratiti pažnju. Model radne sveske živi u RAM-u tokom trajanja izvoza, tako da bi servis koji otvara veoma velike fajlove samo da bi ih ponovo emitovao kao CSV trebao da ograniči broj istovremenih poslova, ili da stavi u red one prevelike, pre nego što dozvoli da skok saobraćaja odluči o radnom skupu

Jedan manji parametar: podesite IncludeBOM u HTML opcijama kada se fragment čuva kao samostalan fajl koji neki nizvodni alat analizira radi prepoznavanja kodiranja. Kada HTML servirate 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 izvozom u CSV je problem otvaranja u drugom ruhu: Excel prikazuje čudne karaktere (mojibake) umesto karaktera sa akcentima. Instinkt je da se okrivi pisač, ali on emituje UTF-8 BOM upravo iz tog razloga, i fajl je gotovo uvek ispravan kada napusti vaš kod. Nešto između tog mesta i Excel-a je pojelo 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 marker i ostaviti Excel to nagađa kodiranje, što on radi loše. Dijagnostikujte ovo na granici prenosnog puta, a ne u samom pozivu za izvoz. Otvorite isporučeni fajl u hex 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, i HotXLS donosi opravdane izbore pri svakoj odluci sa kojom se pisač suočava. Propusti žive na spojevima, tamo gde se tekst formule susreće sa parserom koji želi broj, gde se BOM susreće sa transportom koji ga ne čuva, gde se spojena ćelija susreće sa RTF flat modelom tabele. Svaka od tih stavki je činjenica koju treba upisati u ugovor između vašeg izvoznika i onoga što ga konzumira, jer potrošač ne može čitati vaše namere iz samih bajtova. Za kompletnu listu metoda na obe fasade radnih svezaka, stranica proizvoda HotXLS komponenta sadrži punu referencu