Bezstratny odczyt i zapis XLSX w Delphi: Motyw, extLst, calcChain

HotXLS, natywna biblioteka Excel dla środowisk Delphi i C++Builder, została zaprojektowana z myślą o bezstratnym odczycie i zapisie plików XLSX (round-trip): otwórz skoroszyt, zmień jedną komórkę, zapisz, a niestandardowy motyw klienta, obce bloki rozszerzeń extLst i łańcuch obliczeń (calculation chain) zostaną zachowane. Odpowiadają za to trzy mechanizmy — dosłowne buforowanie pliku xl/theme/theme1.xml, oparta na zdarzeniach ponowna serializacja nieznanych bloków <ext> oraz generowanie nowego, zgodnego ze specyfikacją pliku xl/calcChain.xml przy każdym zapisie skoroszytu z formułami

Scenariusz, który motywuje te rozwiązania, jest przygnębiająco powszechny. Usługa bilingowa ładuje szablon zaprojektowany przez klienta w programie Excel — z firmowym motywem kolorystycznym, miniwykresami (sparklines) w kolumnie KPI oraz regułą formatowania warunkowego dodaną przez nowszą kompilację programu Excel — zapisuje jedną sumę faktury w komórce B3 i zachowuje plik. Klient otwiera wynik, a kolory marki powróciły do domyślnego niebieskiego koloru pakietu Office, miniwykresy zniknęły, a Excel proponuje „naprawę” pliku. Nic w kodzie nie dotykało tych funkcji — zrobiła to biblioteka, po prostu zapisując plik

Dlaczego pliki Excela tracą formatowanie po edycji przez biblioteki?

Pliki Excela tracą formatowanie po edycji za pomocą bibliotek, ponieważ większość z nich nie edytuje pliku — one go przebudowują. Pakiet .xlsx to archiwum ZIP składające się z części XML: xl/workbook.xml, jednego pliku xl/worksheets/sheetN.xml na arkusz, xl/styles.xml, xl/theme/theme1.xml, xl/calcChain.xml i innych. Typowa biblioteka analizuje te części do modelu obiektowego przy otwarciu i generuje każdą część na nowo z tego modelu przy zapisie. Każda funkcja, której model nie reprezentuje — motyw, którego nigdy nie analizował, lub blok rozszerzeń z nowszej wersji programu Excel — nie ma miejsca w pamięci, więc generowana na nowo część po cichu go pomija

Standard ECMA-376 przewidział połowę tego problemu. Język SpreadsheetML definiuje element extLst (ECMA-376 Part 1, „Future Feature Data Storage Area”, §18.2.10 dla elementu na poziomie skoroszytu) jako wyznaczony punkt rozszerzeń: nowsze programy umieszczają tam funkcje, z których każda jest owinięta w element <ext> z atrybutem uri identyfikującym funkcję, a starsze programy mają zachować to, czego nie rozumieją. Miniwykresy, fragmentatory i nowsze typy formatowania warunkowego są przesyłane właśnie w ten sposób. Biblioteka, która odrzuca nieznane bloki <ext>, nie tylko powoduje utratę danych — narusza kontrakt zgodności w przód, wokół którego zaprojektowano ten format. Pytanie zadawane każdej ocenianej bibliotece do arkuszy jest proste: jeśli zmienię jedną komórkę, co jeszcze się zmieni

W jaki sposób HotXLS zachowuje niestandardowy motyw bajt po bajcie?

HotXLS zachowuje motyw skoroszytu poprzez buforowanie oryginalnych bajtów pliku xl/theme/theme1.xml w momencie otwarcia i zapisywanie ich z powrotem dosłownie przy zapisie. Część motywu (ECMA-376 Part 1, §14.2.7) to plik DrawingML, a nie SpreadsheetML — schematy kolorów, czcionek, formatów — i silnik arkusza kalkulacyjnego nie ma powodu, aby szczegółowo go modelować. Wcześniejsze wersje HotXLS generowały stały motyw pakietu Office przy każdym zapisie, co prowadziło do wspomnianego wyżej powrotu do domyślnych kolorów; od wersji 2.89.46 motyw otwieranego pakietu jest przechowywany w stanie surowym i ponownie emitowany w niezmienionej postaci, a wbudowany motyw pakietu Office jest generowany tylko dla skoroszytów tworzonych od podstaw. Surowe bajty to najsilniejsza możliwa gwarancja wierności: bez parsowania, bez ponownej serializacji i bez ryzyka zmian

Kopiowanie dosłowne celowo wygrywa z programowym dostępem do motywu. Klasa TXLSXWorkbook udostępnia właściwości ThemeMajorFont i ThemeMinorFont, dzięki czemu można wybrać czcionki nagłówków i treści dla nowych skoroszytów, ale gdy przy otwarciu przechwycono motyw dosłowny, te metody zapisu nie mają wpływu na zapisany plik — proces odczytu i zapisu (round-trip) ma priorytet. Jeśli naprawdę musisz zmienić motyw istniejącego skoroszytu, jest to sygnał, aby edytować szablon w samym programie Excel, a nie za pomocą interfejsu API zorientowanego na dane. Typowy przypadek nie wymaga w ogóle użycia 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 dzieje się z nieznanymi blokami extLst przy zapisie?

HotXLS przechwytuje każdy blok <ext> na poziomie arkusza, którego nie modeluje natywnie, i odtwarza go w sekcji extLst zapisanego arkusza, dzięki czemu funkcje zapisane przez nowsze wersje programu Excel przeżywają proces zapisu i odczytu w stanie nienaruszonym. Od wersji 2.131.0 przechwycone fragmenty są widoczne za pomocą tylko do odczytu właściwości RawWorksheetExts (będącej listą TStringList na każdym arkuszu XLSX), co ułatwia weryfikację tego mechanizmu w kodzie testowym:

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;

Szczegół implementacji, który warto znać, to fakt, że przechwytywanie polega na ponownej serializacji na poziomie zdarzeń, a nie na kopiowaniu surowych bajtów. Czytnik strumieniowy XML w HotXLS nie ujawnia przesunięć źródłowych, więc nieznane poddrzewo jest odbudowywane ze zdarzeń Element, Text i EndElement w miarę ich odczytywania. To podejście kryje w sobie jedną klasyczną pułapkę: element samozamykający się, taki jak <a/>, wywołuje jedynie zdarzenie Element oznaczone jako puste i nigdy nie wywołuje EndElement, więc każdy licznik głębokości, który dekrementuje się wyłącznie przy EndElement, nigdy nie zobaczy zamknięcia poddrzewa. Po rozwiązaniu tego problemu odbudowany fragment jest semantycznie równoważny oryginałowi — cudzysłowy atrybutów i formy samozamykające są normalizowane, więc nie jest on identyczny bajtowo, ale Excel odczytuje znaczenie, a nie bajty. Dwie właściwości własnego wyjścia programu Excel sprawiają, że odtwarzanie jest bezpieczne: Excel deklaruje niezbędne atrybuty xmlns na elemencie <ext> lub wewnątrz niego, so każdy przechwycony fragment jest niezależny pod względem przestrzeni nazw, i ta sama niezależność pozwala na to, by duplikowanie arkusza wewnątrz lub między skoroszytami przenosiło obce bloki wraz ze zwykłym przypisaniem listy ciągów znaków

Zapisywanie calcChain.xml, aby Excel ufał Twoim formułom

HotXLS zapisuje plik xl/calcChain.xml (część łańcucha obliczeń, ECMA-376 Part 1, §12.3.1) za każdym razem, gdy zapisywany skoroszyt zawiera formuły, i wybiera jeden z dwóch sposobów uporządkowania. Jeśli graf zależności formuł został już zbudowany i jest aktualny — czyli wywołałeś Recalculate po ostatniej edycji — łańcuch jest zapisywany w pełnym porządku topologicznym (zależności przed elementami zależnymi), a wszelkie elementy odwołań cyklicznych są doklejane na końcu. W przeciwnym razie komórki są wymieniane w kolejności dokumentu. Oba podejścia są poprawne: uwagi implementacyjne firmy Microsoft dotyczące tego formatu, [MS-XLSX], traktują łańcuch obliczeń jako wskazówkę, którą Excel weryfikuje i porządkuje podczas ładowania, więc każda kompletna lista jest dozwolona. HotXLS celowo odmawia wymuszania budowania grafu wewnątrz metody SaveAs — budowanie krawędzi jest kwadratowe względem liczby komórek, co stanowiłoby niedopuszczalny ukryty koszt przy zapisie miliona komórek

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

Dlaczego należy dbać o część, którą Excel traktuje jako opcjonalną? Ponieważ jej brak jest sygnałem. Niektóre programy — heurystyki naprawcze, przeglądarki firm trzecich, narzędzia porównujące — oczekują, że skoroszyt z formułami będzie zawierał łańcuch obliczeń, a biblioteka, która po cichu odrzuca tę część przy zapisie, tworzy pliki różniące się od tych zapisywanych przez program Excel. Emisja poprawnego łańcucha utrzymuje plik wyjściowy w granicach tego, na czym testowano resztę ekosystemu, co jest cichym i mało efektownym jądrem inżynierii bezstratnego odczytu i zapisu

Gdzie kończy się bezstratny zapis i odczyt

Uczciwość ma tu większe znaczenie niż pole wyboru w materiałach marketingowych, dlatego granice te zasługują na równe traktowanie. HotXLS nie kopiuje całego pakietu bajt po bajcie: pliki XML arkusza, style, wspólne ciągi znaków (shared strings) i części skoroszytu są generowane na nowo z przeanalizowanego modelu, więc wyjście jest semantycznie wierne, ale nie identyczne binarnie — same nagłówki lokalne ZIP niosą świeże znaczniki czasu DOS. Przechwycone fragmenty <ext> powracają w postaci znormalizowanej, jak opisano powyżej. Programowe nadpisywanie czcionek motywu jest ignorowane, gdy obecny jest dosłowny motyw. Sieć zabezpieczająca ma określoną strukturę: obejmuje funkcje modelowane natywnie przez HotXLS (na przykład miniwykresy są analizowane i przepisywane, a nie bezmyślnie kopiowane) plus obcą zawartość extLst oraz części buforowane dosłownie. Część, która nie jest ani modelowana, ani nie znajduje się w punkcie rozszerzeń — na przykład niestandardowa część egzotycznego dodatku — wykracza poza te trzy mechanizmy, więc przetestuj swoje rzeczywiste szablony zamiast zakładać zgodność w ciemno

Inne elementy zabezpieczające dopełniają obrazu. Projekty VBA i zewnętrzne odwołania do skoroszytów przechodzą przez proces zapisu zgodnie z tą samą filozofią zachowywania tego, czego nie modelujemy, opisaną w powiązanym artykule o zachowywaniu VBA i odnośników zewnętrznych, a właściwości dokumentu w docProps mają własny interfejs API do odczytu i zapisu, zamiast być cicho usuwanymi. Kiedy oceniasz dowolną bibliotekę do arkuszy, wykonaj test jednej komórki: otwórz bogaty w funkcje produkcyjny skoroszyt, zmień jedną wartość, zapisz i porównaj rozpakowane części z oryginałem. To, co zmieniło się poza edytowanym arkuszem, powie Ci o bibliotece więcej niż jakakolwiek tabela funkcji

Opisane tutaj mechanizmy zapisu i odczytu — dosłowne zachowanie motywu od wersji 2.89.46, przechwytywanie obcego extLst oraz emisja calcChain.xml od wersji 2.131.0 — są dostarczane w bieżącej wersji komponentu HotXLS Delphi Excel Component, którego strona produktu dokumentuje pełny zestaw funkcji odczytu i zapisu XLSX dla Delphi i C++Builder