Bezztrátový převod XLSX tam a zpět v Delphi: Témata, extLst, calcChain

HotXLS, nativní knihovna Excelu pro Delphi a C++Builder, je postavena pro bezztrátové převody XLSX tam a zpět: otevřete sešit, změňte jednu buňku, uložte a zákazníkovo vlastní téma, cizí rozšiřující bloky extLst i výpočetní řetězec zůstanou zachovány. Tuto funkčnost zajišťují tři mechanismy — doslovné cachování souboru xl/theme/theme1.xml, re-serializace neznámých bloků <ext> na základě událostí a vytvoření nového, specifikaci odpovídajícího souboru xl/calcChain.xml při každém uložení sešitu se vzorci

Scénář, který k tomu všemu dává podnět, je skličujícím způsobem běžný. Fakturační služba načte šablonu, kterou zákazník navrhl v Excelu — firemní barevné téma, minigrafy (sparklines) ve sloupci KPI, pravidlo podmíněného formátování přidané novější verzí Excelu — zapíše jeden součet faktury do buňky B3 a soubor uloží. Zákazník otevře výsledek a firemní barvy skočí zpět na výchozí modř sady Office, minigrafy zmizí a Excel nabídne soubor „opravit“. Nic v kódu se těchto funkcí nedotklo. Knihovna ano, jednoduše tím, že provedla uložení

Proč soubory Excel po úpravách knihovnou ztrácejí formátování?

Soubory Excel ztrácejí formátování po úpravách knihovnou, protože většina knihoven soubor neupravuje — ony jej znovu sestavují. Balík .xlsx je ZIP soubor částí XML: xl/workbook.xml, jeden soubor xl/worksheets/sheetN.xml na list, xl/styles.xml, xl/theme/theme1.xml, xl/calcChain.xml a další. Typická knihovna tyto části při otevření analyzuje do objektového modelu a při uložení každou část z tohoto modelu znovu vygeneruje. Jakákoli funkce, kterou model nereprezentuje — téma, které nikdy neanalyzoval, nebo rozšiřující blok z novějšího Excelu — nemá v paměti kde existovat, takže ji regenerovaná část potichu vynechá

Norma ECMA-376 polovinu tohoto problému předvídala. Schéma SpreadsheetML definuje extLst (ECMA-376 Part 1, oblast „Future Feature Data Storage Area“, §18.2.10 pro prvek na úrovni sešitu) jako určený bod rozšíření: novější nástroje tam ukládají funkce, každou zabalenou v prvku <ext> s atributem uri, který funkci identifikuje, a od starších spotřebitelů se očekává, že zachovají to, čemu nerozumí. Tímto způsobem se přenášejí minigrafy, průřezy (slicers) a novější typy podmíněných formátů. Knihovna, která zahazuje neznámé bloky <ext>, proto není pouze ztrátová — porušuje kontrakt o zpětné kompatibilitě, na němž byl formát navržen. Otázka, kterou byste měli položit jakékoli tabulkové knihovně, již hodnotíte, je přímočará: když změním jednu buňku, co dalšího se změní

Jak HotXLS uchovává vlastní téma bajt po bajtu?

Knihovna HotXLS zachovává téma sešitu tak, že při otevření uloží do cache původní bajty souboru xl/theme/theme1.xml a při uložení je zapíše doslova zpět. Část tématu (ECMA-376 Part 1, §14.2.7) spadá pod DrawingML, nikoli SpreadsheetML — barevná schémata, schémata písem, formátovací schémata — a tabulkový engine nemá důvod ji do hloubky modelovat. Starší verze HotXLS generovaly při každém uložení pevně dané téma sady Office, což způsobovalo právě ono výše popsané „vrácení firemních barev zpět“; od verze v2.89.46 se téma otevřeného balíku ukládá v surovém stavu a znovu se vysílá beze změny, přičemž integrované téma Office se generuje pouze pro sešity vytvořené od nuly. Surové bajty představují nejsilnější možnou záruku věrnosti: žádná analýza, žádná opětovná serializace, žádná možnost odchylky

Doslovné kopírování záměrně vítězí nad programovým přístupem k tématům. Vlastnosti ThemeMajorFont a ThemeMinorFont jsou vystaveny v TXLSXWorkbook, takže můžete vybrat písma nadpisů a těla pro nové sešity, ale pokud bylo při otevření zachyceno doslovné téma, tyto settery nemají na uložený soubor žádný vliv — převod tam a zpět má prioritu. Pokud opravdu potřebujete změnit téma existujícího sešitu, je to signálem k úpravě šablony v samotném Excelu, nikoli přes datově orientované API. Běžný případ nevyžaduje vůbec žádné rozhraní API:

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;

Co se stane s neznámými bloky extLst při uložení?

HotXLS zachycuje každý prvek <ext> na úrovni listu, který nativně nemodeluje, a přehrává jej do extLst uloženého listu, takže funkce vytvořené novějšími verzemi Excelu přežijí převod tam a zpět nedotčené. Od verze v2.131.0 jsou zachycené fragmenty viditelné přes vlastnost RawWorksheetExts (pouze pro čtení), což je TStringList na každém listu XLSX, což umožňuje auditovat tuto záruku z testovacího kódu namísto spoléhání se na víru:

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;

Detail implementace, který stojí za to znát, je, že zachycení je re-serializací na úrovni událostí, nikoli kopírováním surových bajtů. Streamová čtečka XML v HotXLS neodkrývá žádné zdrojové offsety, takže neznámý podstrom je znovu sestaven z událostí Element, Text a EndElement, jak plynou kolem. Tento přístup skrývá jedno klasické úskalí: prvek, který se uzavírá sám, jako například <a/>, vyvolá pouze událost Element označenou jako prázdná a nikdy událost EndElement, takže jakékoli počítadlo hloubky, které klesá výhradně při události EndElement, nikdy neuvidí uzavření podstromu. Pokud to vyřešíte, je znovu sestavený fragment sémanticky ekvivalentní originálu — uvozovky atributů a tvary samouzavíracích prvků se normalizují, takže není bajtově identický, ale Excel čte význam, nikoli bajty. Dvě vlastnosti vlastního výstupu z Excelu činí toto přehrání bezpečným: Excel deklaruje potřebné atributy xmlns na prvku <ext> nebo uvnitř něj, takže každý zachycený fragment je z hlediska jmenného prostoru samostatný, a tato samostatnost je také důvodem, proč duplikování listu uvnitř sešitu nebo napříč sešity může přenést cizí bloky spolu s prostým přiřazením seznamu řetězců

Zápis souboru calcChain.xml, aby Excel důvěřoval vašim vzorcům

HotXLS zapisuje soubor xl/calcChain.xml (část Calculation Chain, norma ECMA-376 Part 1, §12.3.1), kdykoli uložený sešit obsahuje vzorce, a vybírá si mezi dvěma způsoby uspořádání. Pokud již byl sestaven graf závislostí vzorců a je aktuální — tedy po poslední úpravě jste zavolali Recalculate — řetězec je vysílán v úplném topologickém uspořádání, tedy závislosti před závislými buňkami, přičemž případné prvky cyklických odkazů se připojují na konec. V opačném případě jsou buňky uvedeny v pořadí dokumentu. Obě možnosti jsou správné: poznámky k implementaci formátu od společnosti Microsoft, [MS-XLSX], považují výpočetní řetězec za nápovědu, kterou Excel při načítání ověřuje a mění její pořadí, takže jakýkoli kompletní výpis je legální. Knihovna HotXLS se záměrně vyhýbá vynucení sestavení grafu uvnitř metody SaveAs — konstrukce hran je kvadratická vůči počtu buněk, což by představovalo nepřijatelný skrytý náklad při ukládání milionů buněk

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');

Kde bezztrátový převod tam a zpět končí

Upřímnost zde hraje větší roli než marketingové zaškrtávací políčko, proto si tyto limity zaslouží stejnou pozornost. HotXLS nekopíruje celý balík bajt po bajtu: XML listů, styly, sdílené řetězce (shared strings) a části sešitu jsou regenerovány z analyzovaného modelu, takže výstup je sémanticky věrný, ale nikoli binárně identický — samotné lokální hlavičky ZIP nesou nové časové značky systému DOS. Zachycené fragmenty <ext> se vrací normalizované, jak je popsáno výše. Programové přepisy písem tématu jsou ignorovány, pokud je přítomno doslovné téma. Síť ochrany má jasně definovaná oka: funkce, které HotXLS modeluje nativně (například minigrafy jsou analyzovány a přepsány, nikoli slepě kopírovány) plus cizí obsah extLst plus doslovně cachované části. Část, která není modelována ani se nenachází uvnitř rozšiřujícího bodu — například vlastní část exotického doplňku — spadá mimo tři mechanismy, kterými se tento článek zabývá, takže raději testujte své vlastní šablony, než abyste to pouze předpokládali

Související ochranářská práce doplňuje celkový obraz. Projekty VBA a odkazy na externí sešity procházejí uložením na základě stejné filozofie „zachovej to, co nemodeluješ“, popsané v doprovodném článku o zachování VBA a externích odkazů, a vlastnosti dokumentu v docProps mají své vlastní rozhraní API pro čtení a zápis namísto tichého zahození. Kdykoli hodnotíte jakoukoli knihovnu pro tabulky, spusťte test jedné buňky: otevřete produkční sešit bohatý na funkce, změňte jedinou hodnotu, uložte jej a porovnejte rozbalené části s originálem. To, co se změnilo kromě listu, kterého jste se dotkli, vám řekne o knihovně více než jakákoli tabulka funkcí

Mechanismy převodu tam a zpět popsané v tomto článku — doslovné uchování témat od verze v2.89.46, zachycení cizích extLst a zápis calcChain.xml od verze v2.131.0 — jsou dodávány v aktuální verzi komponenty HotXLS Delphi Excel Component, jejíž stránka produktu dokumentuje kompletní sadu funkcí pro čtení a zápis XLSX pro Delphi a C++Builder