Be nuostolių XLSX failų persiuntimas (Round-Trip) Delphi aplinkoje: Theme, extLst, calcChain

„HotXLS“ – vietinė Delphi ir C++Builder Excel biblioteka – yra sukurta beprarastiniam (lossless) XLSX persiuntimui (round-trip): atidarykite darbo knygą, pakeiskite vieną langelį, išsaugokite, ir kliento pasirinktinė tema, svetimi extLst plėtinių blokai bei skaičiavimo grandinė (calculation chain) išliks nepaliesti. Tai užtikrina trys mechanizmai – pažodinis xl/theme/theme1.xml saugojimas talpykloje, įvykiais pagrįstas nežinomų <ext> blokų pakartotinis serijozavimas ir naujas, specifikaciją atitinkantis xl/calcChain.xml failas, kuriamas kaskart išsaugant darbo knygą su formulėmis

Scenarijus, kuris skatina kurti šiuos tris mechanizmus, yra liūdnai dažnas. Sąskaitų faktūrų išrašymo tarnyba įkelia šabloną, kurį klientas sukūrė „Excel“ programoje – įmonės spalvų temą, mini-grafikus (sparklines) KPI stulpelyje, sąlyginio formatavimo taisyklę, pridėtą naujesnės „Excel“ versijos – įrašo vieną sąskaitos sumą į langelį B3 ir išsaugo. Klientas atidaro rezultatą, o prekės ženklo spalvos sugrįžo į standartinę „Office“ mėlyną, mini-grafikai dingo, o „Excel“ siūlo „pataisyti“ failą. Niekas kode nelietė šių funkcijų. Tai padarė biblioteka tiesiog išsaugodama failą

Kodėl po bibliotekos redagavimų „Excel“ failai praranda formatavimą?

„Excel“ failai praranda formatavimą po bibliotekos redagavimų, nes dauguma bibliotekų neredaguoja failo – jos jį sukuria iš naujo. .xlsx paketas yra XML dalių ZIP archyvas: xl/workbook.xml, po vieną xl/worksheets/sheetN.xml kiekvienam lapui, xl/styles.xml, xl/theme/theme1.xml, xl/calcChain.xml ir kt. Įprasta biblioteka atidarymo metu išanalizuoja šias dalis į objektų modelį, o išsaugojimo metu iš naujo sugeneruoja kiekvieną dalį iš to modelio. Bet kokia funkcija, kurios modelis neatvaizduoja – pavyzdžiui, tema, kurios ji niekada neanalizavo, arba plėtinių blokas iš naujesnės „Excel“ – neturi kur likti atmintyje, todėl sugeneruota dalis ją tyliai praleidžia

ECMA-376 numatė pusę šios problemos. „SpreadsheetML“ apibrėžia extLst (ECMA-376 1 dalis, „Future Feature Data Storage Area“, §18.2.10 darbo knygos lygio elementui) kaip numatytą plėtinio tašką: naujesni generatoriai ten patalpina funkcijas, kurių kiekviena įvyniota į elementą <ext> su atributu uri, identifikuojančiu funkciją, o senesni vartotojai turėtų išsaugoti tai, ko nesupranta. Mini-grafikai (sparklines), pjaustytuvai (slicers) ir naujesni sąlyginio formatavimo tipai keliauja būtent taip. Biblioteka, kuri atmeta nežinomus <ext> blokus, ne tik sukelia praradimus – ji pažeidžia atgalinio suderinamumo sutartį, kuria remiantis buvo sukurtas šis formatas. Klausimas, kurį reikėtų užduoti bet kuriai lentelių bibliotekai, yra paprastas: jei aš pakeisiu vieną langelį, kas dar pasikeis?

Kaip HotXLS išsaugo pasirinktinę temą baitas po baito?

„HotXLS“ išsaugo darbo knygos temą išsaugodama pradinius xl/theme/theme1.xml baitus atidarymo metu ir įrašydama juos pažodžiui išsaugojimo metu. Temos dalis (ECMA-376 1 dalis, §14.2.7) yra „DrawingML“, o ne „SpreadsheetML“ – spalvų schemos, šriftų schemos, formatavimo schemos – ir lentelių varikliui nėra jokios priežasties ją giliai modeliuoti. Ankstesnės „HotXLS“ versijos sugeneruodavo fiksuotą „Office“ temą kiekvieno išsaugojimo metu, kas ir lėmė prekės ženklo spalvų sugrįžimą į standartines, kaip aprašyta aukščiau; nuo versijos v2.89.46 atidaryto paketo tema saugoma neapdorota ir išvedama nepaliesta, o integruota „Office“ tema generuojama tik darbo knygoms, sukurtoms nuo nulio. Neapdoroti baitai yra stipriausia įmanoma tikslumo garantija: jokių analizavimų, jokių pakartotinių serijozavimų, jokių nukrypimų galimybių

Pažodinis kopijavimas sąmoningai laimi prieš programinę prieigą prie temos. TXLSXWorkbook atveria savybes ThemeMajorFont bei ThemeMinorFont, kad galėtumėte parinkti antraščių ir pagrindinio teksto šriftus naujoms darbo knygoms, tačiau kai atidarant buvo užfiksuota pažodinė tema, šie nustatymai neturi jokios įtakos išsaugotam failui – pirmenybė teikiama persiuntimui (round-trip). Jei jums tikrai reikia pakeisti esamos darbo knygos temą, tai yra ženklas, kad turėtumėte redaguoti šabloną pačioje „Excel“ programoje, o ne per duomenų API. Kasdieniam atvejui API išvis nereikia:

var
  Book: TXLSXWorkbook;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('branded-invoice.xlsx');
    Book.Sheets[0].Cells[3, 2].Value := 42750.00;  // the one edit
    Book.SaveAs('branded-invoice-out.xlsx');
    // theme1.xml in the output is byte-identical to the input
  finally
    Book.Free;
  end;
end;

Kas nutinka nežinomiems extLst blokams išsaugant?

„HotXLS“ užfiksuoja kiekvieną darbinio lapo lygio <ext> bloką, kurio ji pati nemodeliuoja, ir atkuria jį išsaugoto darbinio lapo extLst, todėl naujesnių „Excel“ versijų įrašytos funkcijos išlieka nepaliestos. Nuo versijos v2.131.0 užfiksuoti fragmentai yra matomi per tik skaitomą savybę RawWorksheetExts – tai TStringList kiekviename XLSX darbiname lape, kas leidžia patikrinti šią garantiją testo kode, o ne pasitikėti aklai:

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  i: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('from-newer-excel.xlsx');
    Sheet := Book.Sheets[0];
    WriteLn(Format('%d foreign ext block(s) captured',
      [Sheet.RawWorksheetExts.Count]));
    for i := 0 to Sheet.RawWorksheetExts.Count - 1 do
      WriteLn(Copy(Sheet.RawWorksheetExts[i], 1, 100)); // peek at each uri
  finally
    Book.Free;
  end;
end;

Verta žinoti įgyvendinimo detalę, kad užfiksavimas yra įvykių lygio pakartotinis serijozavimas, o ne neapdorotų baitų kopijavimas. „HotXLS“ srautinis XML skaitytuvas neatveria šaltinio poslinkių, todėl nežinomas po-medis sukuriamas iš naujo iš Element, Text ir EndElement įvykių jiems tekant pro šalį. Šis metodas slepia vienus klasikinius spąstus: savaime užsidarantis elementas, pavyzdžiui, <a/>, sukelia tik Element įvykį, pažymėtą kaip tuščią, ir niekada nesukelia EndElement įvykio, todėl bet koks gylio skaitiklis, mažėjantis tik ties EndElement, niekada nepamatys po-medžio pabaigos. Apdorojus tai, atkurtas fragmentas yra semantiškai lygiavertis originalui – atributų kabutės ir savaime užsidarančios formos normalizuojamos, todėl jis nėra identiškas baitų lygmeniu, tačiau „Excel“ skaito prasmę, o ne baitus. Dvi pačios „Excel“ išvesties savybės daro atvaizdavimą saugų: „Excel“ deklaruoja reikalingus xmlns atributus pačiame <ext> elemente arba jo viduje, todėl kiekvienas užfiksuotas fragmentas turi savo vardų erdvę. Dėl šio autonomiškumo darbinio lapo dubliavimas toje pačioje arba kitose darbo knygose gali perkelti svetimus blokus kartu su paprastu eilučių sąrašo priskyrimu

calcChain.xml įrašymas, kad Excel pasitikėtų jūsų formulėmis

„HotXLS“ įrašo xl/calcChain.xml (skaičiavimo grandinės dalį, ECMA-376 1 dalis, §12.3.1) kaskart, kai išsaugomoje darbo knygoje yra formulių, ir pasirenka vieną iš dviejų tvarkų. Jei formulių priklausomybės grafikas jau sukurtas ir yra aktualus – tai yra, iškvietėte Recalculate po paskutinio redagavimo – grandinė išvedama pilna topologine tvarka, priklausomybės prieš priklausomus elementus, o visi žiedinių nuorodų nariai pridedami pabaigoje. Kitu atveju langeliai išvardijami dokumento tvarka. Abu būdai teisingi: „Microsoft“ šio formato įgyvendinimo pastabose [MS-XLSX] skaičiavimo grandinė traktuojama kaip užuomina, kurią „Excel“ patikrina ir perrikiuoja įkėlimo metu, todėl bet koks pilnas sąrašas yra legalus. „HotXLS“ sąmoningai atsisako priverstinai kurti grafiką funkcijoje SaveAs – briaunų kūrimas yra kvadratinis pagal langelių skaičių, kas būtų nepriimtina paslėpta kaina išsaugant milijoną langelių

Book.Open('model.xlsx');
Book.Sheets[0].Cells[10, 4].Formula := '=SUM(D2:D9)';
// Saved now, calcChain.xml lists formula cells in document order.
// After Recalculate the dependency graph exists, so the same save
// emits a full topological order instead:
Book.Recalculate;
Book.SaveAs('model-out.xlsx');

Kur baigiasi beprarastinis persiuntimas (round-trip)

Sąžiningumas čia yra svarbesnis už rinkodaros varnelę, todėl ribos vertos tokio paties dėmesio. „HotXLS“ nekopijuoja viso paketo baitas po baito: darbinio lapo XML, stiliai, bendrinamos eilutės ir darbo knygos dalys yra sugeneruojami iš naujo iš išanalizuoto modelio, todėl išvestis yra semantiškai tiksli, bet ne identiška dvejetainiu lygmeniu – vien vietinės ZIP antraštės turi naujas DOS laiko žymas. Užfiksuoti <ext> fragmentai grąžinami normalizuoti, kaip aprašyta aukščiau. Programiniai temos šrifto pakeitimai ignoruojami, kai yra pažodinė tema. Išsaugojimo tinklas turi apibrėžtas ribas: funkcijos, kurias „HotXLS“ modeliuoja vietiniu būdu (pavyzdžiui, mini-grafikai yra išanalizuojami ir perrašomi, o ne aklai kopijuojami), plius išorinis extLst turinys bei pažodžiui talpykloje saugomos dalys. Dalis, kuri nėra nei modeliuojama, zodžiu, nei yra plėtinio taške – pavyzdžiui, egzotinio papildinio pasirinktinė dalis – nepatenka į tris šiame straipsnyje aprašytus mechanizmus, todėl išbandykite savo realius šablonus, užuot spėlioję

Vaizdą užbaigia susiję išsaugojimo darbai. VBA projektai ir išorinės darbo knygų nuorodos išsaugojimo metu išlaikomi remiantis ta pačia „išsaugok-tai-ko-nemodeliuoji“ filosofija, aprašyta susijusiame straipsnyje apie VBA ir išorinių nuorodų išsaugojimą, o dokumento savybės docProps turi savo skaitymo-rašymo API, užuot buvusios tyliai praleistos. Kai vertinate bet kurią lentelių biblioteką, atlikite vieno langelio testą: atidarykite funkcijų kupiną darbo knygą, pakeiskite vieną reikšmę, išsaugokite ir palyginkite išpakuotas dalis su originalu. Tai, kas pasikeitė be jūsų paliesto lapo, pasakys apie biblioteką daugiau nei bet kokia funkcijų matrica

Čia aprašyti persiuntimo (round-trip) mechanizmai – pažodinis temos išsaugojimas nuo v2.89.46, išorinio extLst užfiksavimas ir calcChain.xml išvedimas nuo v2.131.0 – pateikiami dabartinėje „HotXLS Delphi Excel Component“, kurios produkto puslapyje dokumentuojamas visas XLSX skaitymo ir rašymo funkcijų rinkinys, skirtas Delphi bei C++Builder