Interop ODS w HotXLS: formuły i reguły czytelne dla Excela

Żeby wyprodukować plik ODS, który Excel i LibreOffice czytają poprawnie, HotXLS zapisuje każdą formułę w składni OpenFormula pod zadeklarowaną przestrzenią nazw of:, a każdy format warunkowy oparty na wartości albo formule zapisuje dwukrotnie: jako <style:map> na stylu każdej objętej komórki — jedynej formy, jaką czyta Excel 16 — i jako blok calcext:conditional-formats, czyli formy, której ufa LibreOffice. Każda aplikacja ignoruje połowę przeznaczoną dla tamtej, więc plik, który wygląda poprawnie w jednej z nich, nie dowodzi niczego o drugiej

To ostatnie zdanie to puenta sześciu wydań HotXLS między v2.384.55 a v2.384.72. Każda poprawka zaczynała się od pliku, który HotXLS zapisał, czytał z powrotem bez zarzutu, a jedna z dwóch docelowych aplikacji czytała źle. Niżej: co każda z aplikacji faktycznie akceptuje, jaki znacznik zadowala obie i jakie wywołania API HotXLS produkują to z Delphi

Dlaczego plik ODS wygląda dobrze w jednej aplikacji, a w drugiej jest zepsuty?

Plik ODS wygląda dobrze w jednej aplikacji, a w drugiej jest zepsuty, bo Excel i LibreOffice czytają różne części tego samego pakietu. OpenDocument dopuszcza dla formuł i formatów warunkowych więcej niż jedną legalną pisownię, LibreOffice dokłada na wierzchu własną przestrzeń rozszerzeń, a każdy konsument wybiera podzbiór, który zaimplementował. Pisarz testowany wyłącznie na jednym konsumencie z przyjemnością zbiegnie ku znacznikowi, który tamten po cichu czyta źle

Żadna z aplikacji nie zgłasza błędu. LibreOffice pokazuje #VALUE! w komórkach z formułami, których nie umiał sparsować; Excel otwiera skoroszyt z formatami warunkowymi po prostu nieobecnymi albo z formułą przepisaną na coś, co wylicza się do #NAME? albo stałej 0. Pisarz robiący rundy na własnym wyjściu nie zobaczy żadnej z tych rzeczy. HotXLS wpadł dokładnie w tę pułapkę z przestrzenią formuł: jego czytnik dopasowywał prefiks of: jako zwykły tekst, więc każda własna runda przechodziła, a LibreOffice pokazywał #VALUE! w każdej komórce z formułą

Cecha	Excel 16 czyta	LibreOffice 26.2 czyta
Cała kolumna zapisana jako A:A	Źle czytane jako A:(A)	Tolerowane
Cała kolumna zapisana jako [.A:.A]	Tak	Tak
Formaty warunkowe w <style:map>	Tak, jedyna czytana forma	Ignorowane, gdy jest calcext
Formaty warunkowe w calcext:conditional-formats	Ignorowane	Tak, preferowane
Reguła wartości calcext z atrybutem calcext:operator	Ignorowane	Importowane jako "równe 0"
Reguła formuły calcext zapisana is-true-formula(...)	Ignorowane	Importowane jako porównanie wartości z 0

OpenFormula w ODS: najpierw zadeklaruj przestrzeń nazw, potem dopnij składnię

Komórka z formułą w ODS jest czytelna dla LibreOffice tylko wtedy, gdy prefiks of: w table:formula rozwiązuje się do zadeklarowanej przestrzeni nazw XML. Prefiks nie jest dekoracją. of: mapuje się na urn:oasis:names:tc:opendocument:xmlns:of:1.2, a msoxl: — prefiks, którego HotXLS używa dla formuł nieschwytanych przez swój translator OpenFormula — mapuje się na http://schemas.microsoft.com/office/excel/formula. Przed v2.384.56 korzeń content.xml używał obu prefiksów bez ich deklarowania i LibreOffice nie umiał w ogóle zidentyfikować gramatyki formuł

<!-- Przed v2.384.56: prefiks używany, nigdy niezadeklarowany; LibreOffice pokazuje #VALUE! -->
<office:document-content xmlns:table="urn:oasis:names:tc:opendocument:xmlns:table:1.0" ...>
  <table:table-cell table:formula="of:=SUM([.A1:.A3])" office:value-type="float" office:value="245"/>

<!-- Od v2.384.56: obie przestrzenie formuł zadeklarowane na korzeniu -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

Z przestrzenią załatwioną wyrażenie samo w sobie wciąż musi być poprawnym OpenFormula, zgodnie z definicją w OpenDocument 1.3 Part 4. Pułapki siedzą w miejscach, gdzie składnia Excela i OpenFormula wyglądają podobnie, a nie są tym samym:

• Referencje komórek są w nawiasach kwadratowych z kropką z przodu, a znaczniki $ są częścią referencji: [.$A$1] i [.A$1:.$B2] to poprawne OpenFormula. Przed v2.384.55 pisarz HotXLS wyrzucał każdy $, więc referencje absolutne wracały jako relatywne i psuły się dopiero, gdy ktoś skopiował komórkę
• Całe kolumny i wiersze muszą używać postaci w nawiasach: [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Gołe of:=SUM(A:A) LibreOffice toleruje, ale Excel 16 otwiera je jako =SUM(A:(A)) z #NAME?, a referencje wierszy i $A:$B zamienia na stałą 0. HotXLS zapisuje postać w nawiasach od v2.384.65
• Argumenty funkcji rozdziela ;, a nie ,
• Unie referencji używają operatora ~: Excelowe AREAS((A1,B2)) staje się AREAS(([.A1]~[.B2])). Przetłumaczenie tego przecinka na ; zamienia jeden argument-unii w dwa argumenty
• Tablice inline rozdzielają kolumny przez ; i wiersze przez |: Excelowe {1,2;3,4} staje się {1;2|3;4}. Przed v2.384.55 HotXLS produkował {1;2;3;4} — jeden wiersz z czterema wartościami

Przecinek jest najtrudniejszy, bo jeden znak Excela niesie trzy znaczenia. Od v2.384.55 pisarz HotXLS prowadzi przy tłumaczeniu stos nawiasów: ( bezpośrednio po nazwie otwiera wywołanie funkcji, którego przecinki stają się ;; każdy inny ( to nawias grupujący, którego przecinki stają się ~; a przecinki w środku {} to separatory kolumn tablicy. Z tym i z poprawką przestrzeni nazw LibreOffice 26.2 wyliczył poprawnie wszystkie osiem formuł próbnych tablic i unii, łącznie z INDEX i AREAS nad uniami

uses
  lxHandleX;

var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
begin
  Book := TXLSXWorkbook.Create;
  try
    Sheet := Book.Sheets.Add('Orders');
    Sheet.Cells[1, 1].Value := 120;
    Sheet.Cells[2, 1].Value := 80;
    Sheet.Cells[3, 1].Value := 45;
    Sheet.Cells[1, 2].Value := 0.2;

    // Zapisywane jako of:=SUM([.A:.A]) od v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Zapisywane jako of:=[.A1]*[.$B$1]; znaczniki $ przeżywają od v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

    Book.SaveAsODS('orders.ods');
  finally
    Book.Free;
  end;
end;

Formuły, których translator nie modeluje, schodzą na msoxl:= z niezmienionym tekstem Excela, dlatego deklaracja msoxl też ma znaczenie. W bieżącym pisarzu ta ścieżka obejmuje referencje kwalifikowane arkuszem, jak Sheet2!A1, i strukturalne referencje tabel. HotXLS czyta formuły msoxl: z powrotem przy imporcie, więc własna runda zachowuje wyrażenie w całości, ale jak potraktuje je inna aplikacja, już nie leży w rękach pisarza. Jeśli formuła, od której zależą twoi konsumenci, wyjdzie z prefiksem msoxl:, otwórz plik w obu aplikacjach, zanim go wyślesz

Dlaczego Excel nie widzi formatów warunkowych zapisanych wyłącznie jako calcext?

Excel 16 nie widzi formatów warunkowych calcext, bo formaty warunkowe ODS czyta wyłącznie z dzieci <style:map> stylów komórek i ignoruje blok calcext:conditional-formats całkowicie. Eksperyment, który to rozstrzyga, jest krótki: weź ODS zapisany przez LibreOffice, skasuj elementy style:map, a Excel przeczyta zero reguł; skasuj zamiast tego blok calcext, a Excel nadal przeczyta wszystkie. LibreOffice zachowuje się odwrotnie. calcext to przestrzeń rozszerzeń LibreOffice, nie część standardu ODF, i gdy reguła calcext jest obecna, LibreOffice bierze ją i ignoruje style:map

Przed v2.384.69 HotXLS zapisywał tylko calcext, więc plik ODS z jak najbardziej poprawnym podświetleniem otwierał się w Excelu bez żadnych reguł wartości i bez żadnych reguł formuł. HotXLS zapisuje teraz obie formy. Połowa style:map używa gramatyki warunków schematu OpenDocument (ODF 1.3 Part 3), z dokładnymi pisowniami, jakie produkują przy zapisie ODS zarówno Excel 16, jak i LibreOffice 26.2:

<!-- Uproszczone. Styl nośny dla każdej komórki A1:A50 (dwie reguły wartości) -->
<style:style style:name="ce3" style:family="table-cell">
  <style:map style:condition="cell-content()&gt;100"
             style:apply-style-name="CF_Hit"
             style:base-cell-address="Orders.A1"/>
  <style:map style:condition="cell-content-is-between(1,10)"
             style:apply-style-name="CF_Low"
             style:base-cell-address="Orders.A1"/>
</style:style>

<!-- Styl nośny dla każdej komórki C1:C50 (jedna reguła formuły) -->
<style:style style:name="ce4" style:family="table-cell">
  <style:map style:condition="is-true-formula(COUNTIF([.$C:.$C];[.C1])&gt;1)"
             style:apply-style-name="CF_Dup"
             style:base-cell-address="Orders.C1"/>
</style:style>

Haczyk style:map polega na tym, że mieszka na stylach komórek, więc jest per komórka. Każda komórka w zakresie reguły musi nieść styl trzymający mapę — puste komórki wliczone — inaczej reguła po prostu nie obejmuje tej komórki w Excelu. HotXLS kopiuje istniejący styl formatowania każdej komórki, dokleja mapy i deduplikuje style nośne po parze oryginalny styl plus tekst mapy, więc zakres 500 komórek o identycznym formatowaniu nadal daje jeden styl. Pisarz rozciąga też zapisywaną tabelę do zakresu reguły, co znaczy, że puste wiersze ogona wewnątrz reguły są emitowane, a nie wyrzucane. Od v2.384.69 styles.xml niesie też pusty styl komórki Default, więc style:apply-style-name="Default" zawsze ma cel

Pisownia calcext, którą LibreOffice faktycznie akceptuje

LibreOffice akceptuje regułę wartości calcext tylko wtedy, gdy operator porównania jest częścią tekstu wartości, jak >3 albo between(1,10), a regułę formuły tylko wtedy, gdy jest zapisana formula-is(...). Oba punkty kosztowały HotXLS po wydanie, bo złe pisownie produkują regułę, która importuje się bez błędu, a potem łapie złe komórki

Pierwszą pomyłką był atrybut calcext:operator obok calcext:value. Czyta się naturalnie, ale jest wymyślony: LibreOffice nie zna tego atrybutu, więc importował każdą regułę wartości jako „równą 0”. Drugą było wstawienie is-true-formula(...) — pisowni z style:map — do warunku calcext, który LibreOffice importował też jako porównanie wartości komórki z 0. Poprawka formuł wyszła w v2.384.66, a poprawka wartości w v2.384.69:

<!-- Źle: LibreOffice ignoruje calcext:operator i importuje "równe 0" -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Dobrze: operator jedzie wewnątrz wartości -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:value="&gt;100" calcext:base-cell-address=".A1"/>
<calcext:condition calcext:apply-style-name="CF_Low"
                   calcext:value="between(1,10)" calcext:base-cell-address=".A1"/>

<!-- Dobrze: reguły formuł używają formula-is, referencje relatywne zakotwiczone w komórce bazowej -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>

Komórka bazowa to to, co nadaje referencjom relatywnym znaczenie. HotXLS kotwiczy każdą regułę w lewym górnym rogu jej pierwszego obszaru zakresu, więc formuła napisana dla C1 wylicza się jak C2, C3 i tak dalej w dół zakresu — dokładnie jak w warunkowym formatowaniu samego Excela. Wyrażenie reguły przechodzi przez ten sam translator co formuły komórek, więc tablice, unie, całe kolumny i znaczniki $ wychodzą w postaciach opisanych wyżej. Po stronie Delphi dodajesz reguły dokładnie tak, jak w pliku .xlsx

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Reguły wartości: style:map cell-content()>100 plus calcext wartość ">100"
  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR: jasna czerwień

  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpBetween, '1', '10');
  Sheet.ConditionalFormats[Idx].Style.SetFontBold(True);

  // Reguła formuły w składni Excela (przecinki, relatywnie do C1):
  // style:map is-true-formula(...) i calcext formula-is(...)
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: jasny żółty

  Opts := TODSExportOptions.Create;
  try
    Opts.Generator := 'OrderExport 3.1';
    Book.SaveAsODS('orders.ods', Opts);
  finally
    Opts.Free;
  end;
end;

Czytanie ODS od Excela i LibreOffice z powrotem do Delphi

Gdy HotXLS otwiera plik ODS, jego czytnik akceptuje obie gwary formatów warunkowych i obie pisownie calcext, a reguły noszonej w obu formach nie liczy podwójnie. Prawdziwe pliki przychodzą od trzech pisarzy, każdy z własnymi nawykami:

• Stary i nowy calcext. Pliki z atrybutem calcext:operator, w tym ODS zapisane przez HotXLS przed v2.384.69, wciąż przechodzą przez stary parsing. Warunki formuł są rozpoznawane jako formula-is(...) albo is-true-formula(...)
• Excelowa pisownia style:map. Excel poprzedza warunki prefiksem of:, jak w of:cell-content-is-between(1,10), i pomija komórkę bazową w regułach wartości. Jedno i drugie jest akceptowane
• Puste komórki. Excel i LibreOffice kładą mapę dla pustych komórek na domyślnym stylu kolumny, a nie na komórce, więc czytnik rozwiązuje domyślne style kolumn dla powtórzonych komórek, zanim zbierze mapy
• Składanie obszarów. Mapy są zbierane per komórka, więc po przeczytaniu arkusza czytnik scala komórki dzielące ten sam warunek i komórkę bazową z powrotem w zakresy — najpierw w poprzek każdego wiersza, potem w dół po pasujących rozpiętościach kolumn — i wyrzuca każdą regułę już przeczytaną z calcext

Poprawka z v2.384.72 dotyczy stylów liczbowych, nie reguł. Excel 16 i LibreOffice 26.2 zapisują format General jako styl liczbowy, którego element number:number nie ma number:decimal-places, typowo <number:number number:min-integer-digits="1"/>. Czytnik HotXLS traktował brakującą liczbę jako dwie stałe pozycje dziesiętne, więc każda wartość w stylu Default importowała się z 0.00, a 1.5 wyświetlało się jako 1.50. Od v2.384.72 zwykły element liczbowy bez miejsc dziesiętnych, bez minimum miejsc dziesiętnych, bez grupowania i z co najwyżej jedną cyfrą całkowitą mapuje się na General, a samotny General zostawia komórkę bez żadnego formatu liczbowego. Tekst wokół niego jest zachowywany, jak w General" kg", a liczby grupowane trzymają poprzednie mapowanie, bo Excel nie ma grupowanego formatu General

uses
  SysUtils, lxCondFormat, lxHandleX;

procedure DumpOdsRules(const FileName: string);
var
  Book: TXLSXWorkbook;
  Sheet: TXLSXWorksheet;
  Rule: TXLSXConditionalFormat;
  I: Integer;
begin
  Book := TXLSXWorkbook.Create;
  try
    if Book.Open(FileName) <= 0 then
      raise Exception.Create('cannot open ' + FileName);
    if Book.SourceFormat <> xlsxOpenDocumentSpreadsheet then
      raise Exception.Create('not an ODS package');

    Sheet := Book.Sheets[1]; // indeksowanie Sheets liczy od 1
    for I := 0 to Sheet.ConditionalFormats.Count - 1 do
    begin
      Rule := Sheet.ConditionalFormats[I];
      case Rule.Kind of
        cfkCellIs:
          Writeln(Rule.Range, ' value rule ', Ord(Rule.Op), ' ',
            Rule.Formula1, ' ', Rule.Formula2);
        cfkExpression:
          Writeln(Rule.Range, ' formula rule ', Rule.Formula1);
      end;
    end;

    // Komórka w stylu General Excela czyta się z powrotem bez formatu liczbowego
    // od v2.384.72, zamiast '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Formuły reguł wracają w składni Excela z przecinkami — tej samej postaci, którą podajesz do AddCondFormatExpression — więc reguła zapisana przez HotXLS czyta się z powrotem jako identyczny tekst. O szerszym obrazie tego, co ścieżka importu ODS zachowuje, a co wyrzuca, pisze przewodnik po rundzie otwarcia i zapisu ODS w HotXLS; o tym, jak powtórzone wiersze od Excela i LibreOffice są rozwijane przy imporcie, pisze powtórzone wiersze ODS jako ciągi wysokości wierszy

Jakie są granice interopu formatów warunkowych ODS w HotXLS?

Podejście z podwójnym znacznikiem obejmuje reguły porównania wartości i reguły formuł i tam się kończy. Wszystko inne jest jednostronne albo w ogóle niezapisywane:

• Skale kolorów i paski danych są zapisywane wyłącznie jako elementy calcext, więc LibreOffice je pokazuje, a Excel nie
• Pozostałe rodzaje reguł, jak zestawy ikon, reguły tekstowe, top-N, powyżej średniej i duplikaty, nie mają wyjścia ODS w bieżącym pisarzu. Regułę tekstową zwykle da się przepisać jako regułę formuły, na przykład ISNUMBER(SEARCH("late",B2)) nad B2:B200, co dociera już do obu aplikacji
• Reguły na całe kolumny i wiersze, jak C:C, są kładzione tylko na faktycznie zapisany obszar tabeli, a nie na wszystkie 1 048 576 wierszy, więc Excel widzi te reguły wyłącznie na komórkach istniejących w pliku
• Pliki z samym style:map. Gdy plik nie ma bloku calcext, HotXLS interpretuje referencje relatywne w regułach formuł od lewego górnego rogu złożonego zakresu, a nie przez przesuwanie od podanej komórki bazowej
• Nakładające się reguły z LibreOffice. Gdy komórkę obejmuje kilka reguł, LibreOffice zapisuje na niej tylko mapę pierwszej reguły. Takich plików nie da się przeczytać w całości z samego style:map, co jest kolejnym powodem, dla którego czytnik preferuje calcext, gdy obie formy istnieją

Granica procesu znaczy więcej niż którakolwiek z powyższych. Wady stojące za tymi wydaniami przepchnęły przez rundy, które zapisywały ODS i czytały go z powrotem w HotXLS, a niektóre przeszłyby też ręczne sprawdzenie w złej aplikacji: formuły na całe kolumny działały w LibreOffice, podczas gdy Excel pokazywał #NAME?, a od v2.384.66 reguły formuł działały w LibreOffice, podczas gdy Excel wciąż nie pokazywał żadnych reguł aż do v2.384.69. Jeśli interop ODS jest wymaganiem, testem akceptacyjnym jest otwarcie pliku w Excelu i w LibreOffice i porównanie, co każde pokazuje. Ta sama dyscyplina dotyczy stylów, na które wskazują reguły; artykuł o formatowaniu warunkowym i stylach HotXLS opisuje, jak style podświetleń są definiowane po stronie skoroszytu

Ściąga: ODS czytane przez obie aplikacje

• Zadeklaruj xmlns:of i xmlns:msoxl na korzeniu content.xml, inaczej LibreOffice pokaże #VALUE! dla każdej formuły (HotXLS od v2.384.56)
• Zapisuj referencje jako [.A1], zachowuj każdy $, a całe kolumny i wiersze zapisuj jako [.A:.A] i [.1:.1] (od v2.384.55 i v2.384.65)
• Używaj ; dla argumentów, ~ dla unii referencji i | między wierszami tablicy inline
• Zapisuj każdą regułę wartości albo formuły jako <style:map> na stylu każdej objętej komórki dla Excela i jako warunek calcext dla LibreOffice (od v2.384.69)
• W calcext włóż operator do wartości (>3, between(1,10)) i zapisuj reguły formuł jako formula-is(...) z komórką bazową (od v2.384.66 i v2.384.69)
• Przy imporcie spodziewaj się stylu liczbowego General bez number:decimal-places; HotXLS czyta go jako General od v2.384.72
• Weryfikuj każdy nowy profil eksportu, otwierając plik i w Excelu, i w LibreOffice — nigdy w tylko jednej z nich

HotXLS to natywna biblioteka arkuszy dla Delphi i C++Buildera, która czyta i zapisuje XLS, XLSX i ODS bez zainstalowanego Excela czy LibreOffice; pełne źródła, lista funkcji i licencjonowanie są na stronie komponentu arkuszy kalkulacyjnych HotXLS dla Delphi