Otwieranie i zapis plików ODS w Delphi z HotXLS

Backend raportowy w Delphi, który od lat emituje .xlsx, dostaje nowe wymaganie: reguły zamówień publicznych klienta z sektora publicznego nakazują wyjście w formacie OpenDocument Spreadsheet, a analitycy na tym koncie odsyłają swoje poprawki jako pliki .ods zapisane z LibreOffice. Więc teraz ten sam kod musi zapisywać ODS i go czytać. HotXLS, natywna biblioteka arkuszowa losLab w Object Pascalu dla Delphi i C++Buildera, obsługuje oba kierunki bez Excela ani LibreOffice zainstalowanego gdziekolwiek. Oba kierunki są do siebie zbliżone, ale nie identyczne. Od HotXLS 2.88.229 ścieżka importu przynosi formuły z powrotem jako żywe formuły, obok ich buforowanych wyników, a bieżące wersje czytają także style, formaty liczbowe, scalone zakresy, komentarze, obrazy, wykresy i formaty warunkowe. Tym, co nadal nie przeżywa podróży, są pełna semantyka OpenFormula i garść szczegółów na poziomie pakietu, więc zespół, który założy doskonałe lustrzane odbicie, wciąż trafi na przypadki brzegowe gdzieś między poprawką klienta a następnym raportem, zwykle bez żadnego błędu, na który dałoby się wskazać

Wsparcie ODS mieszka na fasadzie XLSX, nie na XLS

HotXLS wysyła dwie niezależne hierarchie klas w jednym pakiecie: TXLSWorkbook w jednostce lxHandle dla binarnych plików BIFF8 .xls i TXLSXWorkbook w jednostce lxHandleX dla pakietów OOXML .xlsx. Każdy punkt wejścia OpenDocument - OpenODS, SaveAsODS, GetODSSheetNames - wisi na TXLSXWorkbook. To umieszczenie nie jest przypadkowe. Pakiet ODS, zgodnie ze specyfikacją OASIS ODF 1.3, to archiwum zip niosące człon mimetype, manifest i ciało content.xml, co czyni go strukturalnym kuzynem zipa OOXML; BIFF8 to binarny strumień rekordów z lat dziewięćdziesiątych, niemający z tym nic wspólnego

To umieszczenie ma praktyczną krawędź: starszy skoroszyt .xls nie stanie się .ods jednym wywołaniem. Najpierw przerzucasz treść BIFF do modelu XLSX, przez SaveXLSWorkbookAsXLSX z jednostki lxXlsxExport, otwierasz wynik ponownie przez TXLSXWorkbook, a potem eksportujesz stamtąd. Pomost nie jest bezstratny i warto znać luki, zanim na nim zbudujesz. Kopiuje wartości, formuły, formaty liczbowe, czcionki, wypełnienia, obramowania, scalone zakresy, szerokości kolumn i wysokości wierszy. Porzuca komentarze, wykresy i formatowanie warunkowe. Źródło .xls z ciężkim formatowaniem dotrze do ODS wyglądając skromniej, niż wyruszyło, a to właściwość pomostu, nie zapisującego ODS

Wykrywanie po stronie importu jest automatyczne. Zwykła metoda Open rozpoznaje pakiet ODS po jego członie mimetype, wracając do sprawdzenia content.xml na najwyższym poziomie, gdy tego członu brak, więc ogólna ścieżka kodu w rodzaju "otwórz cokolwiek wrzucił użytkownik" nie potrzebuje własnego węszenia po rozszerzeniach. Po otwarciu właściwość SourceFormat raportuje, która gałąź odpaliła

Eksport do ODS przez TODSExportOptions

Samo wywołanie eksportu to jedna linia; obiekt opcji wokół niego niesie decyzje, o które recenzent zapyta później:

var
  Book: TXLSXWorkbook;
  Opts: TODSExportOptions;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('quarterly-report.xlsx');
    Opts := TODSExportOptions.Create;        // wołający jest właścicielem i to zwalnia
    try
      Opts.Generator := 'ReportService 4.2'; // nadpisanie meta:generator
      Opts.IncludeCharts := True;
      Opts.IncludeImages := True;
      Book.SaveAsODS('quarterly-report.ods', Opts);
    finally
      Opts.Free;
    end;
  finally
    Book.Free;
  end;
end;

Obiekt opcji należy do wołającego. HotXLS go nie zwolni, i dlatego wewnętrzne try..finally tam jest i nie jest opcjonalne. Dwie właściwości, które zmieniają wyjście, a nie tylko je etykietują, zasługują na bliższe spojrzenie. Ustawienie IncludeCharts := False robi więcej niż ukrycie wykresów: usuwa poddokumenty wykresów i ich wpisy w manifeście z pakietu, czyli dokładnie to, czego chcesz, gdy konsumentem jest potok danych, który by się o nie potknął. Generator nadpisuje ciąg meta:generator z ODF, który inaczej brzmi HotXLS/<version>; nadpisz go, gdy narzędzia niżej w łańcuchu identyfikują producentów plików, by kierować wsparcie. Jeśli nic z tego nie dotyczy, pomiń obiekt opcji w całości. Wywołanie SaveAs(FileName, xlsxOpenDocumentSpreadsheet) jest tym samym co SaveAsODS z ustawieniami domyślnymi, a przeciążenia strumieniowe na obu pozwalają zapisać pakiet prosto do odpowiedzi HTTP bez pliku tymczasowego

Co czyta ścieżka importu i co wciąż pomija

Przeczytaj tę część uważnie, zanim obiecasz komukolwiek wierność podróży w obie strony. Import ODS w HotXLS zaczął jako lekka ścieżka, która trzymała tylko wartości skalarne i buforowane wyniki formuł, i od tego czasu mocno urósł. Bieżące wersje czytają content.xml razem z styles.xml i settings.xml: wartości skalarne i buforowane wyniki formuł, powtarzane wiersze i kolumny rozwinięte do siatki, scalone zakresy, tekst sformatowany, hiperłącza, komentarze, osadzone obrazy i wykresy, dziedziczone i domyślne style komórek, formaty liczb i dat, wysokości wierszy i szerokości kolumn, nazwane zakresy, zamrożone okienka, zakresy AutoFiltrów, walidacje danych i formaty warunkowe. Tym, czego OpenODS wciąż nie konwertuje, są pełna semantyka OpenFormula, klucze ochrony ODS, geometria komentarzy, zaawansowane formatowanie komentarzy oraz funkcje pakietu wykraczające poza treść arkusza

Formuły to część, która najpewniej ugryzie. Komórka ODF przechowuje obok siebie dwie rzeczy: wyrażenie formuły, zapisane w dialekcie OpenFormula zdefiniowanym w ODF 1.3 część 4, i ostatnią wartość, jaką aplikacja produkująca dla niej policzyła. Wczesne wersje czytały tylko wartość buforowaną. Od HotXLS 2.88.229 OpenODS importuje też wyrażenie: komórka zapisana z prefiksem of:= lub oooc:= zachowuje swoją wartość buforowaną i dostaje znormalizowaną formułę Excela, przy czym referencje w nawiasach kwadratowych, takie jak [.A1:.B1], i separatory argumentów średnikami są zamieniane na składnię Excela. Od 2.384.32 import konwertuje też wiersze tablic w linii ({1;2|3;4} staje się {1,2;3,4}) oraz operator sumy referencji ~, a od 2.384.55 taka suma czyta się z powrotem bez drugiej pary nawiasów. Tłumaczenie OpenFormula na składnię Excela to nadal problem konwersji dialektów, z prawdziwymi przypadkami brzegowymi wokół słowników funkcji, składni referencji i modeli błędów — dlatego dostajesz też wartość buforowaną: liczby, które importujesz, są dokładnie tymi, które nadawca widział ostatnio

Tryb awarii, wokół którego trzeba projektować, odpowiednio się zawęził. Przed 2.88.229 arkusz, którego sumy były poprawne, gdy LibreOffice ostatnio go zapisał, importował się z poprawnymi liczbami, które były już tylko stałymi: zmień komórkę wejściową, przelicz i nic się nie ruszy. W bieżących wersjach formuły wracają jako żywe, więc pozostałym ryzykiem jest konstrukcja, której tłumacz nie modeluje — może zostawić formułę, która przelicza się inaczej niż w aplikacji produkującej, podczas gdy wartość buforowana wciąż pokazuje wynik nadawcy. Dla formuł, od których zależy twój potok, porównaj wyniki przeliczenia z zaimportowanymi wartościami buforowanymi, a każdą formułę, która się nie zgadza, odtwórz z własnych reguł biznesowych przez Cell.Formula, które na fasadzie XLSX bierze wyrażenie bez wiodącego znaku równości

Projektowanie wokół pozostałych luk podróży w obie strony

Eksport renderuje z pełnego modelu skoroszytu w pamięci: wartości, formuły, style oraz, jeśli o nie poprosisz, wykresy i obrazy. Eksport formuł był zaostrzany z wydania na wydanie. Od 2.384.55 tablice w linii, sumy referencji i referencje bezwzględne zachowują swoje znaczenie w OpenFormula; od 2.384.56 HotXLS deklaruje przestrzenie nazw swoich prefiksów formuł, więc LibreOffice przelicza formuły zamiast pokazywać #VALUE! w każdej komórce z formułą; a od 2.384.65 referencje całej kolumny i całego wiersza są zapisywane w formie OpenFormula [.A:.A], więc Excel nie otwiera ich już jako #NAME?. Formaty warunkowe poszły w ślad: od 2.384.66 reguły oparte na formułach są zapisywane jako formula-is(...) z komórką bazową, co LibreOffice rozpoznaje, a od 2.384.69 reguły wartości i formuł są zapisywane także jako standardowe warunki style:map — jedyna forma, którą czyta Excel — przy czym strona importu czyta obie formy. Pełny cykl .xlsx do .ods do .xlsx na bieżącej wersji zachowuje więc wartości, formuły, style i formaty warunkowe; to, co nadal może stracić, siedzi w lukach wymienionych w poprzedniej sekcji

Book := TXLSXWorkbook.Create;
try
  Book.Open('vendor-revision.ods');          // format wykrywany automatycznie
  if Book.SourceFormat = xlsxOpenDocumentSpreadsheet then
  begin
    // Od HotXLS 2.88.229 formuły wracają jako żywe, obok swoich
    // buforowanych wyników; odtwórz każdą formułę, której potok
    // niżej w łańcuchu nie może ufać jako przetłumaczonej, przed zapisem.
    Book.Sheets[0].Cells[2, 5].Formula := 'SUM(B2:D2)';
    Book.SaveAs('vendor-revision.xlsx');
  end;
finally
  Book.Free;
end;

Wzorzec architektoniczny, który z tego wypada: traktuj przychodzące pliki .ods jak strumienie danych, a nie jak dokumenty do edycji w miejscu. Trzymaj kanoniczny skoroszyt w .xlsx, wyczytuj wartości z poprawek klienta i emituj świeży ODS na żądanie z kopii kanonicznej. Weryfikacja należy do obu obozów - otwieraj wyeksportowane pliki w LibreOffice Calc, referencyjnym konsumencie ODF, i w Excelu, który czyta ODS od lat, ale rozchodzi się z LibreOffice na krawędziach wsparcia wykresów i stylów. Liczba arkuszy, garstka kluczowych komórek i obecność wykresów wystarczają za kontrolę dymną na profil eksportu

Selekcja pliku ODS przed zobowiązaniem się do importu

Kiedy punkt końcowy przyjmuje wysyłane pliki, wypisanie nazw arkuszy jest dużo tańsze niż pełne parsowanie i wcześnie łapie strukturalne niespodzianki:

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetODSSheetNames('incoming.ods', Names) <= 0 then
    raise Exception.Create('not a readable ODS package');
  if Names.IndexOf('Data') < 0 then
    raise Exception.Create('revision is missing the Data sheet');
finally
  Book.Free;
  Names.Free;
end;

Konwencja zwracania podkłada ludziom nogę: wywołania HotXLS zwracają zwykle dodatnią liczność albo 1 przy powodzeniu i -1 przy porażce, czyszcząc przy niej listę, więc testuj <= 0, zamiast porównywać z jedną konkretną wartością dodatnią. GetODSSheetNames ani nie resetuje, ani nie wypełnia instancji skoroszytu, więc jeden obiekt sondujący może prześwietlić cały katalog przychodzących plików. Kontrole strukturalne tego rodzaju łapią najczęstszą awarię z prawdziwego świata - analityka przemianowującego albo usuwającego arkusz przed odesłaniem poprawki - już na bramce, gdzie komunikat błędu może jeszcze nazwać plik i brakujący arkusz, zamiast wychodzić jako referencja nil trzy warstwy głębiej

Jeśli budujesz wokół tego szerszy potok konwersji, wzorzec warsztatu audytu i konwersji skoroszytów pokazuje, jak zinwentaryzować funkcje pliku przed wyborem formatu docelowego, a przewodnik po wydajności dużych skoroszytów utrzymuje eksporty wsadowe w rozsądnych granicach pamięci

HotXLS to natywna biblioteka arkuszowa dla Delphi i C++Buildera z pełnym kodem źródłowym; kompletna lista funkcji i szczegóły licencjonowania są na stronie produktu HotXLS Delphi Component