Interop ODS HotXLS: Formula dan Aturan yang Terbaca Excel

Untuk menghasilkan file ODS yang dibaca benar oleh Excel dan LibreOffice sekaligus, HotXLS menulis setiap formula dalam sintaks OpenFormula di bawah namespace of: yang dideklarasikan, dan menulis setiap conditional format value atau formula dua kali: sebagai <style:map> pada style tiap sel yang tercakup, satu-satunya bentuk yang dibaca Excel 16, dan sebagai blok calcext:conditional-formats, bentuk yang dipercaya LibreOffice. Masing-masing aplikasi mengabaikan setengah yang ditujukan bagi pihak lain, jadi file yang tampil benar di salah satunya tak membuktikan apa pun tentang yang satunya

Kalimat terakhir itulah pelajaran di balik enam rilis HotXLS antara v2.384.55 dan v2.384.72. Tiap perbaikan berawal dari file yang ditulis HotXLS, terbaca balik dengan sempurna, dan salah dibaca oleh salah satu dari dua aplikasi target. Berikut ini apa yang benar-benar diterima tiap aplikasi, markup yang memuaskan keduanya, dan panggilan API HotXLS yang menghasilkannya dari Delphi

Mengapa file ODS tampak baik di satu aplikasi dan rusak di satunya?

File ODS tampak baik di satu aplikasi dan rusak di satunya karena Excel dan LibreOffice membaca bagian berbeda dari paket yang sama. OpenDocument memberi formula dan conditional format lebih dari satu ejaan yang sah, LibreOffice menambahkan namespace extension miliknya di atasnya, dan tiap consumer memilih subset yang diimplementasikannya. Writer yang dites hanya terhadap satu consumer akan dengan senang hati konvergen ke markup yang diam-diam salah dibaca oleh yang lain

Tak satu pun aplikasi melaporkan error. LibreOffice menampilkan #VALUE! di sel yang formula-nya tak bisa di-parse; Excel membuka workbook dengan conditional format yang sekadar absen, atau dengan formula yang ditulis ulang menjadi sesuatu yang mengevaluasi ke #NAME? atau konstanta 0. Writer yang me-round-trip output-nya sendiri tak pernah melihat hal-hal ini. HotXLS menginjak jebakan itu persis di namespace formula: readernya mencocokkan prefix of: sebagai teks polos, sehingga setiap round trip mandiri lulus sementara LibreOffice menampilkan #VALUE! di setiap sel formula

Fitur	yang dibaca Excel 16	yang dibaca LibreOffice 26.2
Kolom utuh ditulis sebagai A:A	Salah dibaca sebagai A:(A)	Ditoleransi
Kolom utuh ditulis sebagai [.A:.A]	Ya	Ya
Conditional format dalam <style:map>	Ya, satu-satunya bentuk yang dibaca	Diabaikan saat calcext ada
Conditional format dalam calcext:conditional-formats	Diabaikan	Ya, yang diutamakan
Value rule calcext dengan atribut calcext:operator	Diabaikan	Diimpor sebagai "sama dengan 0"
Formula rule calcext yang dieja is-true-formula(...)	Diabaikan	Diimpor sebagai perbandingan nilai dengan 0

OpenFormula di ODS: deklarasikan namespace, lalu benarkan sintaksnya

Sel formula di ODS hanya terbaca oleh LibreOffice ketika prefix of: di table:formula ter-resolve ke namespace XML yang dideklarasikan. Prefix itu bukan hiasan. of: dipetakan ke urn:oasis:names:tc:opendocument:xmlns:of:1.2, dan msoxl:, prefix yang dipakai HotXLS untuk formula yang tak dimodelkan translater OpenFormula-nya, dipetakan ke http://schemas.microsoft.com/office/excel/formula. Sebelum v2.384.56, root content.xml memakai kedua prefix tanpa mendeklarasikannya, dan LibreOffice sama sekali tak bisa mengidentifikasi grammar formula

<!-- Sebelum v2.384.56: prefix dipakai, tak pernah dideklarasikan; LibreOffice menampilkan #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"/>

<!-- Sejak v2.384.56: kedua namespace formula dideklarasikan di root -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

Dengan namespace yang sudah benar, ekspresinya sendiri tetap harus OpenFormula yang valid, sebagaimana didefinisikan di OpenDocument 1.3 Part 4. Jebakannya ada di tempat-tempat yang tampak mirip antara sintaks Excel dan OpenFormula tapi tak sama:

• Reference sel dibungkus kurung dan berawalan titik, dan penanda $ adalah bagian dari reference: [.$A$1] dan [.A$1:.$B2] adalah OpenFormula yang valid. Sebelum v2.384.55, writer HotXLS membuang semua $, sehingga reference absolut kembali sebagai relatif dan baru salah saat ada yang menyalin selnya
• Kolom dan baris utuh harus memakai bentuk berkurung [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. of:=SUM(A:A) polos ditoleransi LibreOffice, tapi Excel 16 membukanya sebagai =SUM(A:(A)) dengan #NAME?, dan mengubah reference baris serta $A:$B menjadi konstanta 0. HotXLS menulis bentuk berkurung sejak v2.384.65
• Argumen fungsi dipisahkan ;, bukan ,
• Reference union memakai operator ~: AREAS((A1,B2)) milik Excel menjadi AREAS(([.A1]~[.B2])). Menerjemahkan koma itu ke ; justru mengubah satu argumen union menjadi dua argumen
• Array inline memisahkan kolom dengan ; dan baris dengan |: {1,2;3,4} milik Excel menjadi {1;2|3;4}. Sebelum v2.384.55, HotXLS menghasilkan {1;2;3;4}, satu baris berisi empat nilai

Koma adalah bagian yang sulit, karena satu karakter Excel membawa tiga makna. Sejak v2.384.55, writer HotXLS melacak parenthesis stack saat menerjemahkan: ( tepat setelah sebuah nama membuka function call yang komanya menjadi ;; ( lainnya adalah parenthesis pengelompokan yang komanya menjadi ~; dan koma di dalam {} adalah pemisah kolom array. Dengan itu dan perbaikan namespace, LibreOffice 26.2 mengevaluasi kedelapan formula probe array dan union dengan benar, termasuk INDEX dan AREAS atas union

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;

    // Ditulis sebagai of:=SUM([.A:.A]) sejak v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Ditulis sebagai of:=[.A1]*[.$B$1]; penanda $ bertahan sejak v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

Formula yang tak dimodelkan translater fallback ke msoxl:= dengan teks Excel tak berubah, itulah kenapa deklarasi msoxl juga penting. Di writer saat ini, jalur itu mencakup reference berkualifikasi sheet seperti Sheet2!A1 dan structured table reference. HotXLS membaca formula msoxl: kembali saat impor, jadi round trip miliknya sendiri menjaga ekspresi tetap utuh, tapi bagaimana aplikasi lain memperlakukannya di luar kendali writer. Kalau ada formula yang jadi andalan consumer Anda keluar dengan prefix msoxl:, buka file itu di kedua aplikasi sebelum dikirim

Mengapa Excel tak melihat conditional format yang ditulis hanya sebagai calcext?

Excel 16 tak melihat conditional format calcext karena ia membaca conditional format ODS secara eksklusif dari children <style:map> milik cell style dan mengabaikan blok calcext:conditional-formats sepenuhnya. Eksperimen penuntasnya pendek: ambil ODS yang disimpan LibreOffice, hapus elemen style:map, dan Excel membaca nol rule; hapus blok calcext sebagai gantinya, dan Excel tetap membaca semuanya. LibreOffice berperilaku sebaliknya. calcext adalah namespace extension milik LibreOffice, bukan bagian standar ODF, dan ketika ada rule calcext, LibreOffice mengambilnya dan mengabaikan style:map

Sebelum v2.384.69, HotXLS hanya menulis calcext, sehingga file ODS dengan highlighting yang sangat baik terbuka di Excel tanpa satu pun value rule dan formula rule. HotXLS kini menulis kedua bentuk. Setengah style:map memakai grammar kondisi dari schema OpenDocument (ODF 1.3 Part 3), dengan ejaan persis yang dihasilkan Excel 16 dan LibreOffice 26.2 keduanya saat menyimpan ODS:

<!-- Disederhanakan. Carrier style untuk tiap sel A1:A50 (dua value rule) -->
<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>

<!-- Carrier style untuk tiap sel C1:C50 (satu formula rule) -->
<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>

Celah style:map adalah ia hidup di cell style, jadi per sel. Setiap sel di range rule harus membawa style yang memuat map, termasuk sel kosong, jika tidak rule itu sekadar tak menjangkau sel tersebut di Excel. HotXLS menyalin style format yang sudah dimiliki tiap sel, menambahkan map-mapnya, dan mendeduplikasi carrier style berdasarkan pasangan style asli dan teks map, sehingga range 500 sel dengan format identik tetap menghasilkan satu style. Writer juga memperluas tabel tertulis sampai range rule, yang berarti baris ekor kosong di dalam rule ikut dipancarkan alih-alih dibuang. Sejak v2.384.69, styles.xml juga membawa cell style Default yang kosong, sehingga style:apply-style-name="Default" selalu punya target

Ejaan calcext yang benar-benar diterima LibreOffice

LibreOffice menerima value rule calcext hanya ketika operator perbandingan menjadi bagian dari teks value, seperti >3 atau between(1,10), dan formula rule hanya ketika dieja formula-is(...). Kedua poin itu masing-masing menghabiskan satu rilis HotXLS, karena ejaan yang salah menghasilkan rule yang terimpor tanpa error lalu mencocokkan sel yang keliru

Kesalahan pertama adalah atribut calcext:operator di samping calcext:value. Terbaca alami, tapi itu rekayasa: LibreOffice tak mengenal atribut itu, sehingga ia mengimpor setiap value rule sebagai "sama dengan 0". Kesalahan kedua adalah menaruh is-true-formula(...), ejaan style:map, ke dalam kondisi calcext, yang juga diimpor LibreOffice sebagai perbandingan nilai sel dengan 0. Perbaikan formula terbit di v2.384.66 dan perbaikan value di v2.384.69:

<!-- Salah: LibreOffice mengabaikan calcext:operator dan mengimpor "sama dengan 0" -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Benar: operator berjalan di dalam value -->
<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"/>

<!-- Benar: formula rule memakai formula-is, ref relatif ter-anchor di base cell -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>

Base cell-lah yang memberi reference relatif maknanya. HotXLS meng-anchor setiap rule di sel kiri atas dari area range pertamanya, sehingga formula yang ditulis untuk C1 terevaluasi sebagai C2, C3, dan seterusnya menyusuri range, persis seperti yang terjadi di conditional formatting milik Excel sendiri. Ekspresi rule melewati translater yang sama dengan formula sel, jadi array, union, kolom utuh, dan penanda $ keluar dalam bentuk-bentuk yang dijelaskan di atas. Di sisi Delphi, Anda menambahkan rule persis seperti yang Anda lakukan untuk file .xlsx

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Value rule: style:map cell-content()>100 plus calcext value ">100"
  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR: merah muda

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

  // Formula rule dalam sintaks Excel (pemisah koma, relatif ke C1):
  // style:map is-true-formula(...) dan calcext formula-is(...)
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: kuning muda

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

Membaca ODS dari Excel dan LibreOffice kembali ke Delphi

Ketika HotXLS membuka file ODS, readernya menerima kedua dialek conditional format dan kedua ejaan calcext, serta tak menghitung satu rule dua kali ketika file membawanya dalam kedua bentuk. File nyata datang dari tiga writer, masing-masing dengan kebiasaannya sendiri:

• Calcext lama dan baru. File dengan atribut calcext:operator, termasuk ODS yang ditulis HotXLS sebelum v2.384.69, tetap melewati parse legacy. Kondisi formula dikenali sebagai formula-is(...) maupun is-true-formula(...)
• Ejaan style:map milik Excel. Excel mengawali kondisi dengan of:, seperti of:cell-content-is-between(1,10), dan menghilangkan base cell pada value rule. Keduanya diterima
• Sel kosong. Excel dan LibreOffice sama-sama menaruh map untuk sel kosong di column default style alih-alih di sel, jadi reader me-resolve column default style untuk repeated cells sebelum mengumpulkan map
• Rekonstruksi region. Map dikumpulkan per sel, jadi setelah sheet terbaca, reader menggabungkan sel-sel yang berbagi kondisi dan base cell yang sama kembali menjadi range, dulu menyilang tiap baris lalu ke bawah sepanjang span kolom yang cocok, dan membuang rule yang sudah terbaca dari calcext

Perbaikan v2.384.72 menyangkut number style, bukan rule. Excel 16 dan LibreOffice 26.2 sama-sama menulis format General sebagai number style yang elemen number:number-nya tak punya number:decimal-places, biasanya <number:number number:min-integer-digits="1"/>. Reader HotXLS memperlakukan angka yang hilang itu sebagai dua desimal tetap, sehingga setiap nilai di style Default terimpor dengan 0.00 dan 1.5 tampil sebagai 1.50. Sejak v2.384.72, elemen number polos tanpa tempat desimal, tanpa desimal minimum, tanpa pengelompokan, dan paling banyak satu digit integer dipetakan ke General, dan General yang berdiri sendiri membiarkan sel tanpa number format sama sekali. Teks di sekitarnya dipertahankan, seperti General" kg", dan angka bergrup mempertahankan pemetaan sebelumnya karena Excel tak punya format General bergrup

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]; // indexer Sheets bersifat 1-based
    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;

    // Sel di style General milik Excel terbaca kembali tanpa number format
    // sejak v2.384.72, alih-alih '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Formula rule kembali dalam sintaks Excel dengan pemisah koma, bentuk yang sama dengan yang Anda berikan ke AddCondFormatExpression, sehingga rule yang ditulis HotXLS terbaca balik sebagai string identik. Untuk gambaran lebih luas tentang apa yang disimpan dan dibuang jalur impor ODS, lihat panduan round-trip buka dan simpan ODS HotXLS; untuk cara baris berulang dari Excel dan LibreOffice diekspansi saat impor, lihat baris berulang ODS sebagai run tinggi baris

Apa batas interop conditional format ODS HotXLS?

Pendekatan dual-markup mencakup rule perbandingan nilai dan formula rule, dan berhenti di situ. Sisanya satu sisi atau tidak ditulis sama sekali:

• Color scale dan data bar hanya ditulis sebagai elemen calcext, sehingga LibreOffice menampilkannya dan Excel tidak
• Jenis rule lain, seperti icon set, rule teks, top-N, above-average, dan rule duplikat, tak punya keluaran ODS di writer saat ini. Rule teks biasanya bisa dinyatakan ulang sebagai formula rule, misalnya ISNUMBER(SEARCH("late",B2)) atas B2:B200, yang lalu menjangkau kedua aplikasi
• Rule kolom utuh dan baris utuh seperti C:C hanya diletakkan di atas area tabel yang benar-benar ditulis, alih-alih di atas seluruh 1.048.576 baris, sehingga Excel melihat rule-rule itu hanya pada sel yang ada di file
• File dengan hanya style:map. Ketika file tak punya blok calcext, HotXLS menafsirkan reference relatif di formula rule dari sudut kiri atas range hasil rekonstruksi, bukan dengan menggeser dari base cell yang tercantum
• Rule yang tumpang tindih dari LibreOffice. Ketika satu sel dicakup beberapa rule, LibreOffice hanya menulis map rule pertama ke sel itu. File semacam itu tak bisa dibaca lengkap dari style:map saja, satu alasan lagi reader mengutamakan calcext ketika keduanya ada

Batas proses lebih penting daripada semua itu. Cacat di balik rilis-rilis ini lolos dari round trip yang menulis ODS dan membacanya kembali dengan HotXLS, dan sebagian juga akan lulus pemeriksaan manual di aplikasi yang salah: formula kolom utuh bekerja di LibreOffice sementara Excel menampilkan #NAME?, dan sejak v2.384.66 formula rule bekerja di LibreOffice sementara Excel masih tak menampilkan satu pun rule sampai v2.384.69. Kalau interop ODS adalah requirement, test penerimanya adalah membuka file di Excel dan di LibreOffice lalu membandingkan apa yang ditampilkan masing-masing. Disiplin yang sama berlaku untuk style yang ditunjuk rule; artikel conditional formatting dan style HotXLS membahas cara style highlight didefinisikan di sisi workbook

Referensi cepat: ODS yang terbaca kedua aplikasi

• Deklarasikan xmlns:of dan xmlns:msoxl di root content.xml, atau LibreOffice menampilkan #VALUE! untuk setiap formula (HotXLS sejak v2.384.56)
• Tulis reference sebagai [.A1], pertahankan setiap $, dan tulis kolom serta baris utuh sebagai [.A:.A] dan [.1:.1] (sejak v2.384.55 dan v2.384.65)
• Pakai ; untuk argumen, ~ untuk reference union, dan | di antara baris array inline
• Tulis setiap rule value atau formula sebagai <style:map> pada style setiap sel tercakup untuk Excel, dan sebagai kondisi calcext untuk LibreOffice (sejak v2.384.69)
• Di calcext, taruh operator di dalam value (>3, between(1,10)) dan eja formula rule sebagai formula-is(...) dengan base cell (sejak v2.384.66 dan v2.384.69)
• Bersiap menerima number style General tanpa number:decimal-places saat impor; HotXLS membacanya sebagai General sejak v2.384.72
• Verifikasi setiap profil ekspor baru dengan membuka file di Excel dan LibreOffice, jangan pernah hanya di salah satunya

HotXLS adalah library spreadsheet native Delphi dan C++Builder yang membaca dan menulis XLS, XLSX, dan ODS tanpa Excel atau LibreOffice terinstal; source lengkap, daftar fitur, dan lisensi ada di halaman komponen spreadsheet Delphi HotXLS