„Excel“ knygų eksportavimas į CSV, TSV, HTML ir RTF iš „Delphi“ su „HotXLS“

Įsivaizduokite kasdienę užduotį, kuri sugeneruoja sąskaitos-faktūros failą ir įrašo jį kaip CSV formato failą, skirtą importuoti į kitą sistemą. Skaičiai „Excel“ programoje atrodo teisingi. CSV failas puikiai atsidaro tekstiniame redaktoriuje. Tačiau importavimo programa sugenda ties bendros sumos stulpeliu, nes 42-oje eilutėje sumos laukelis rodo =SUM(D2:D41) – tai yra formulę kaip tekstą, o ne skaičių, kurį ji turėtų apskaičiuoti. Nieko nėra sugadinta. Tai yra numatytasis elgesys ir pirmoji taisyklė, kurią reikia suprasti apie eksportą iš „HotXLS“: eksportavimo funkcija įrašo langelių modelį tiksliai tokį, koks jis yra tuo momentu, o formulės langelis, kurio reikšmė niekada nebuvo apskaičiuota, turi tik savo formulės tekstą.

Kodėl jūsų CSV faile yra formulės, o ne skaičiai

„HotXLS“ laiko formulės tekstą ir apskaičiuotą reikšmę kaip du atskirus dalykus. Funkcija SaveAsCSV neatlieka skaičiavimų eksporto metu. Tai daryti suprojektuota sąmoningai: eksportavimas neturi keisti darbalapio būsenos ir neturi kelti rizikos užstrigti dėl sudėtingų formulių grandinių. Failai, kuriuos išsaugojo pati „Excel“ programa, šalia formulių turi ir paskutines apskaičiuotas reikšmes (angl. cached values), todėl jų eksportas veikia teisingai. Problema kyla su darbalapiais, kuriuos sugeneravo jūsų pačių kodas, kur formulės buvo įrašytos, bet niekada neapskaičiuotos. Sprendimas yra sugeneruoti reikšmes prieš eksportą naudojant Calculate variklį, kuris išsprendžia nuorodas tarp lapų ir pasirinktines funkcijas:

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;

Atkreipkite dėmesį į tai, ką daro šis ciklas: jis perrašo formulės langelius jų apskaičiuotomis reikšmėmis. Tai teisinga vienkartiniam eksportui, tačiau netinka, jei po to planuojate išsaugoti tą pačią knygą XLSX formatu, nes ką tik pakeitėte veikiančias formules statiniais skaičiais. Vykdykite eksportą iš kopijos arba sukurkite logiką taip, kad ji veiktų tik eksporto metu. Calculate variklis leidžia atlikti ir daugiau užduočių, pavyzdžiui, užregistruoti savo funkcijas, kas aprašyta straipsnyje HotXLS formulių variklis ir pasirinktinės funkcijos.

Ką garantuoja CSV rašyklė

CSV eksporto kelias sugeneruoja UTF-8 failą su baitų tvarkos žyma (angl. byte order mark, BOM), CRLF eilučių pabaigomis ir RFC 4180 taisykles atitinkančiomis kabutėmis. Bet kuris laukelis, kuriame yra skyriklis, kabutė ar eilutės perkėlimas, bus apgaubtas kabutėmis, o vidinės kabutės – padvigubintos. Datos pateikiamos yyyy-mm-dd hh:nn:ss formatu, nepriklausomai nuo to, koks formatas nustatytas pačiame langelyje. Tai tinkamiausias sprendimas automatizuotam nuskaitymui, nors vizualiniai formatai ir neperkeliami. Raiškiojo teksto langeliai supaprastinami apjungiant jų fragmentus.

Šios numatytosios taisyklės išsprendžia daugumą suderinamumo problemų, tačiau du dalykus verta aptarti. Pirmasis yra BOM žyma. Ji leidžia „Excel“ teisingai atidaryti failą su specifiniais simboliais, tačiau kai kurie griežti analizatoriai (angl. parsers) šiuos tris baitus traktuoja kaip duomenis ir sugenda. Tokiu atveju juos reikia pašalinti rankiniu būdu. Antrasis dalykas yra TSV. Tai nėra atskira funkcija, o tiesiog tas pats eksporto metodas, iškviestas su tabuliacijos skyrikliu #9, todėl visos taisyklės jam galioja vienodai. Eksportuojamas lapas pasirenkamas pagal 0 pagrindo indeksą, o paprastesnis metodas SaveAsCSV(FileName) naudoja šiuo metu aktyvų darbalapį.

HTML eksportas yra vaizdas, o ne duomenų mainų formatas

Jei CSV atsisako visko, išskyrus reikšmes, SaveAsHTML bando išlaikyti vizualinę išvaizdą: sukuria po vieną <table> kiekvienam lapui, atvaizduoja sujungtus langelius per colspan ir rowspan, bei pritaiko pagrindinį langelių stilių kaip CSS kodą. Temų spalvos yra praleidžiamos, o ne išsprendžiamos, todėl darbalapis, kuris rėmėsi temos spalvomis, atrodys paprasčiau nei „Excel“ programoje. Nustatykite konkrečias RGB spalvas viskam, kas turi išlikti po konvertavimo. Eksporto parinkčių objektas valdo visą procesą:

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;

Atkreipkite dėmesį į dvi šio kodo detales. Nustačius WriteDocument į False, bus sugeneruotas tik lentelės fragmentas (angl. table fragment), o ne visas HTML puslapis. Tai patogu įterpiant peržiūrą į esamą svetainės išdėstymą: nustatykite TableClass ir leiskite svetainės CSS kodui valdyti stilių. Grąžinama reikšmė taip pat skiriasi nuo daugelio HotXLS funkcijų: SaveAsHTML grąžina 0 sėkmės atveju ir -1, jei lapo indeksas neteisingas. Įprotis tikrinti reikšmę = 1 kiekvieną sėkmingą eksportą įvertins kaip klaidą. Jei reikia eksportuoti tik tam tikrą regioną (pavyzdžiui, įterpti į el. laišką), TXLSXRange.SaveAsHTML eksportuoja bet kurį stačiakampį rėžį pagal tas pačias taisykles.

RTF išvestis ir jos taikymo sritys

Ketvirtasis formatas leidžia įrašyti RTF 1.6 lenteles po vieną lapą per iškvietimą naudojant SaveAsRTF. Stulpelių pločiai nustatomi apytiksliai (apie 96 twips vienam stulpelio simboliui). Svarbu žinoti struktūrinį apribojimą: sujungti langeliai (angl. merged cells) RTF išvestyje nėra sujungiami, o duomenis turi tik pagrindinis inkarinis langelis (kiti langeliai bus tušti). Tai reiškia, kad RTF formatas netinka sudėtingo išdėstymo šablonams. Tačiau jis vis dar naudingas kaip paprasčiausias kelias įkelti lentelės rezultatus į teksto redaktorių arba seną dokumentų valdymo sistemą, kuri nepalaiko HTML.

Dvipusis procesas: CSV importas yra destruktyvus pagal konstrukciją

CSV failo nuskaitymas turi savo taisykles. Funkcija OpenCSV išvalo visą darbalapį ir perkuria jį kaip vieną lapą pavadinimu „Sheet1“. Tai yra darbalapio sukūrimas iš naujo, o ne sujungimas, todėl niekada neiškvieskite jos dokumente, kuris turi neišsaugotų duomenų. Perdavus #0 kaip skyriklį, automatiškai aptinkamas failo formatas. Vėliavėlė ADetectTypes valdo tipų atpažinimą: ją įjungus, skaičių eilutės tampa skaičiais, ISO-8601 datos tampa datų objektais, o true/false – loginėmis reikšmėmis. Išjunkite šį nustatymą, jei faile yra kodai su priekiniais nuliais (pavyzdžiui, pašto kodai ar produktų ID), nes konvertavimas juos sugadins (priekinis nulis išnyks, kai reikšmė 00123 taps skaičiumi 123). Abi sąsajos palaiko šį importą. Sujunkite jį su aukščiau aprašytomis eksporto funkcijomis, ir turėsite formatų keitiklį, kuriam nereikia įdiegtos „Excel“ programos, kaip aprašyta straipsnyje Excel ataskaitų generavimas iš duomenų bazės su HotXLS.

Eksportas tiesiai į srautą

Kiekviena eksporto funkcija šalia failo įrašymo turi ir srauto (angl. stream) variantą: CSV, HTML, RTF ir pačių darbalapių formatams. Serverio kode šie variantai yra geriausias pasirinkimas. Interneto paslauga, teikianti CSV atsisiuntimą, gali įrašyti duomenis į TMemoryStream ir perduoti jį tiesiai į atsakymo objektą (angl. response). Taip išvengiama laikinų failų kūrimo, jų valymo užduočių ir konfliktų tarp dviejų vienodų užklausų. Taip pat patogu eksportuoti tiesiai į debesų saugyklą arba pridėti prie el. laiškų. Failų sistema čia visiškai nenaudojama.

Šis modelis puikiai dera su bibliotekos veikimu. Abi sąsajos yra vietinės „Object Pascal“ rašyklės, todėl nereikia diegti „Excel“, naudoti COM automatizavimo, ir nėra našumo ribojimų serverio pusėje. Kiekviena užklausa gali valdyti savo knygos objektą, atlikti formulių skaičiavimą ir eksportuoti duomenis lygiagrečiai. Vienintelis resursas, kurį reikia stebėti, yra operatyvioji atmintis (RAM). Knygos modelis yra laikomas atmintyje eksporto metu, todėl paslauga, atidarant ypač didelius failus tik tam, kad paverstų juos CSV, turėtų riboti vienu metu vykdomų užduočių skaičių arba rikiuoti didelius failus į eilę, kad išvengtų atminties trūkumo didelio srauto metu.

Dar viena smulkmena: nustatykite IncludeBOM HTML parinktyse, kai failas bus išsaugotas kaip atskiras dokumentas, kurį kitos programos analizuos dėl kodavimo. Kai teikiate HTML tiesiai per HTTP, koduotę nurodykite atsakymo antraštėse.

Kai baitai vis tiek įrašomi neteisingai

Dažniausias klausimas apie CSV eksportą yra susijęs su ta pačia problema: „Excel“ atidarytame faile rodomi sugadinti simboliai (angl. mojibake). Dažniausiai manoma, kad kalta eksporto funkcija, tačiau ji visada įrašo UTF-8 BOM žymą ir failas yra teisingas. Problema kyla transportavimo metu: BOM žymą gali ištrinti FTP perkėlimas tekstiniu režimu, srauto kopijavimas praleidžiant pirmus tris baitus arba tarpinis serveris (angl. proxy), kuris perkoduoja failą. Tai pašalina žymą ir priverčia „Excel“ spėlioti koduotę. Tikrinkite šią problemą ne eksporto funkcijoje, o gavimo taške. Atidarykite gautą failą hex redaktoriuje ir patikrinkite, ar pirmi baitai yra EF BB BF.

Dimensinis (XLS/XLSX) eksporto kelias yra saugiausias, tačiau CSV, HTML ir RTF suteikia puikų suderinamumą su kitomis sistemomis. Klaidos kyla sujungimo taškuose: kai formulės tekstas patenka į programą, kuri tikėjosi skaičiaus, kai BOM žymė prarandama perkėlimo metu arba kai sujungti langeliai patenka į RTF lentelę. Kiekvienas iš šių atvejų turi būti apibrėžtas jūsų programos ir duomenų vartotojo sutartyje. Pilną metodų sąrašą abiem darbalapių sąsajoms rasite produkto puslapyje HotXLS Component.