Tương thích ODS HotXLS: Công thức mà Excel đọc được

Muốn tạo ra một tệp ODS mà cả Excel lẫn LibreOffice đọc đúng, HotXLS ghi mọi công thức theo cú pháp OpenFormula dưới một namespace of: được khai báo, và ghi mọi conditional format dạng giá trị hay công thức hai lần: một lần là <style:map> trên style của từng ô được phủ, dạng duy nhất Excel 16 đọc, và một lần là khối calcext:conditional-formats, dạng mà LibreOffice tin. Mỗi ứng dụng bỏ qua nửa dành cho bên kia, nên một tệp hiện đúng trong một trong hai chẳng chứng minh gì về bên còn lại

Câu cuối đó là bài học đằng sau sáu bản phát hành HotXLS giữa v2.384.55 và v2.384.72. Mỗi bản sửa bắt đầu từ một tệp HotXLS viết ra, đọc lại hoàn hảo, và một trong hai ứng dụng đích hiểu sai. Những gì tiếp theo là thứ mỗi ứng dụng thực sự chấp nhận, markup thỏa cả hai, và những lời gọi API HotXLS tạo ra chúng từ Delphi

Vì sao một tệp ODS đẹp ở ứng dụng này mà hỏng ở ứng dụng kia?

Một tệp ODS đẹp ở ứng dụng này mà hỏng ở ứng dụng kia vì Excel và LibreOffice đọc những phần khác nhau của cùng một gói. OpenDocument cho công thức và conditional format nhiều hơn một cách viết hợp lệ, LibreOffice chồng thêm namespace extension riêng của nó lên trên, và mỗi consumer chọn lấy tập con nó có hiện thực. Một writer chỉ test với một consumer duy nhất sẽ ngon lành hội tụ về markup mà bên kia đọc sai một cách âm thầm

Chẳng ứng dụng nào báo lỗi. LibreOffice hiện #VALUE! trong các ô mà nó không parse nổi công thức; Excel mở workbook với các conditional format giản đơn vắng bóng, hay với một công thức bị viết lại thành thứ đánh giá ra #NAME? hay hằng 0. Một writer round-trip chính output của nó sẽ chẳng bao giờ thấy mấy thứ này. HotXLS đã dính đúng cái bẫy đó với namespace công thức: reader của nó khớp prefix of: như plain text, nên mọi self round trip đều qua trong khi LibreOffice hiện #VALUE! ở mọi ô công thức

Tính năng	Excel 16 đọc	LibreOffice 26.2 đọc
Cả cột ghi thành A:A	Đọc nhầm thành A:(A)	Chấp nhận được
Cả cột ghi thành [.A:.A]	Có	Có
Conditional format dạng <style:map>	Có, dạng duy nhất nó đọc	Bỏ qua khi có calcext
Conditional format dạng calcext:conditional-formats	Bỏ qua	Có, ưu tiên
calcext value rule với thuộc tính calcext:operator	Bỏ qua	Import thành "equal to 0"
calcext formula rule viết thành is-true-formula(...)	Bỏ qua	Import thành phép so giá trị với 0

OpenFormula trong ODS: khai báo namespace, rồi chỉnh cú pháp chuẩn

Một ô công thức trong ODS chỉ đọc nổi bởi LibreOffice khi prefix of: trong table:formula phân giải được về một XML namespace đã khai báo. Prefix không phải trang trí. of: ánh xạ tới urn:oasis:names:tc:opendocument:xmlns:of:1.2, còn msoxl:, prefix HotXLS dùng cho các công thức mà bộ dịch OpenFormula của nó chưa mô hình được, ánh xạ tới http://schemas.microsoft.com/office/excel/formula. Trước v2.384.56, root content.xml dùng cả hai prefix mà không khai báo, và LibreOffice chẳng xác định nổi ngữ pháp công thức

<!-- Trước v2.384.56: prefix được dùng nhưng không khai báo; LibreOffice hiện #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"/>

<!-- Kể từ v2.384.56: cả hai namespace công thức được khai báo trên root -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

Namespace đã xong thì biểu thức vẫn phải là OpenFormula hợp lệ, theo định nghĩa trong OpenDocument 1.3 Part 4. Các cái bẫy nằm ở những chỗ cú pháp Excel và OpenFormula trông giống nhau mà không phải một thứ:

• Cell reference đặt trong ngoặc và có chấm phía trước, còn các dấu $ là một phần của reference: [.$A$1] và [.A$1:.$B2] là OpenFormula hợp lệ. Trước v2.384.55, writer HotXLS đánh rơi mọi $, nên reference tuyệt đối quay về tương đối và chỉ lộ ra khi ai đó copy ô
• Cả cột và cả hàng phải dùng dạng có ngoặc [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Một of:=SUM(A:A) trơn thì LibreOffice chấp nhận, nhưng Excel 16 mở thành =SUM(A:(A)) với #NAME?, và biến row reference cùng $A:$B thành hằng 0. HotXLS ghi dạng có ngoặc kể từ v2.384.65
• Đối số hàm tách bằng ;, không phải ,
• Reference union dùng toán tử ~: AREAS((A1,B2)) của Excel thành AREAS(([.A1]~[.B2])). Dịch cái dấu phẩy đó thành ; thì biến một đối số union thành hai đối số
• Mảng inline tách cột bằng ; và hàng bằng |: {1,2;3,4} của Excel thành {1;2|3;4}. Trước v2.384.55 HotXLS tạo ra {1;2;3;4}, một hàng đơn gồm bốn giá trị

Dấu phẩy là phần khó, vì một ký tự Excel mang ba nghĩa. Kể từ v2.384.55, writer HotXLS theo dõi một stack ngoặc trong khi dịch: một ( đứng ngay sau một tên mở một lời gọi hàm, với dấu phẩy thành ;; mọi ( khác là ngoặc gộp nhóm, với dấu phẩy thành ~; còn dấu phẩy trong {} là dấu tách cột mảng. Với cái đó cùng bản sửa namespace, LibreOffice 26.2 đánh giá đúng cả tám công thức thăm dò mảng và union, kể cả INDEX và AREAS trên 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;

    // Được ghi thành of:=SUM([.A:.A]) kể từ v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Được ghi thành of:=[.A1]*[.$B$1]; các dấu $ sống sót kể từ v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

Các công thức mà bộ dịch chưa mô hình được thì fallback về msoxl:= với text Excel giữ nguyên, đó là lý do khai báo msoxl cũng quan trọng. Ở writer hiện tại, đường đó gồm các reference có định danh sheet như Sheet2!A1 và structured table reference. HotXLS đọc lại các công thức msoxl: khi import, nên self round trip của nó giữ nguyên biểu thức, nhưng ứng dụng khác đối xử ra sao thì ngoài tầm kiểm soát của writer. Nếu một công thức mà consumer của bạn dựa vào lọt ra với prefix msoxl:, hãy mở tệp trong cả hai ứng dụng trước khi giao

Vì sao Excel không thấy conditional format chỉ viết bằng calcext?

Excel 16 không thấy conditional format calcext vì nó đọc conditional format ODS riêng từ các con <style:map> của cell style và bỏ qua hẳn khối calcext:conditional-formats. Thí nghiệm chốt vấn đề thì ngắn: lấy một ODS do LibreOffice lưu, xóa các phần tử style:map, Excel đọc đúng bằng 0 luật; xóa khối calcext thay vào đó, Excel vẫn đọc đủ cả. LibreOffice hành xử ngược lại. calcext là namespace extension của LibreOffice, không thuộc chuẩn ODF, và khi một luật calcext hiện diện, LibreOffice lấy nó và bỏ qua style:map

Trước v2.384.69, HotXLS chỉ ghi calcext, nên một tệp ODS với phần tô màu hoàn toàn lành mạnh mở trong Excel mà không có value rule nào và cũng không formula rule nào. HotXLS giờ ghi cả hai dạng. Nửa style:map dùng ngữ pháp điều kiện của schema OpenDocument (ODF 1.3 Part 3), với đúng những cách viết mà Excel 16 và LibreOffice 26.2 cùng tạo ra khi lưu ODS:

<!-- Đã rút gọn. Style mang cho mọi ô của A1:A50 (hai 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>

<!-- Style mang cho mọi ô của C1:C50 (một 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>

Cái khó của style:map là nó sống trên cell style, tức là theo từng ô. Mọi ô trong range của luật phải mang một style giữ map, kể cả ô rỗng, nếu không luật giản đơn không phủ ô đó trong Excel. HotXLS chép style định dạng sẵn có của từng ô, gắn thêm các map, và khử trùng lặp các style mang theo cặp style gốc + text map, nên một range 500 ô định dạng y hệt vẫn chỉ tạo một style. Writer còn kéo dài bảng đã ghi tới range của luật, nghĩa là các hàng đuôi rỗng trong một luật được phát ra thay vì bỏ đi. Kể từ v2.384.69, styles.xml cũng mang một cell style Default rỗng, nên style:apply-style-name="Default" luôn có đích

Cách viết calcext mà LibreOffice thực sự chấp nhận

LibreOffice chỉ chấp nhận một calcext value rule khi toán tử so sánh nằm trong text value, như >3 hay between(1,10), và một formula rule chỉ khi nó được viết thành formula-is(...). Cả hai điểm đều đòi HotXLS mất một bản phát hành, vì những cách viết sai tạo ra một luật import không lỗi rồi khớp nhầm ô

Lỗi đầu là một thuộc tính calcext:operator cạnh calcext:value. Đọc tự nhiên đấy, nhưng nó là thứ bịa ra: LibreOffice không biết thuộc tính đó, nên nó import mọi value rule thành "equal to 0". Lỗi hai là nhét is-true-formula(...), cách viết của style:map, vào một điều kiện calcext, thứ LibreOffice import thành phép so giá trị ô với 0 luôn. Bản sửa formula lên tàu trong v2.384.66 và bản sửa value trong v2.384.69:

<!-- Sai: LibreOffice bỏ qua calcext:operator và import thành "equal to 0" -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Đúng: operator đi bên trong 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"/>

<!-- Đúng: formula rule dùng formula-is, ref tương đối neo tại 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 là thứ cho các reference tương đối ý nghĩa. HotXLS neo mọi luật tại ô trên-trái của area range đầu tiên, nên một công thức viết cho C1 đánh giá thành C2, C3 và tiếp tục xuôi xuống range, đúng như trong conditional formatting của chính Excel. Biểu thức luật đi qua cùng bộ dịch với công thức ô, nên mảng, union, cả cột và các dấu $ lọt ra ở những dạng đã nói trên. Phía Delphi, bạn thêm luật đúng như với một tệp .xlsx

uses
  lxHandleX;

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

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

  // Formula rule theo cú pháp Excel (dấu phẩy tách, tương đối theo C1):
  // style:map is-true-formula(...) cộng calcext formula-is(...)
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: vàng nhạt

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

Đọc ODS của Excel và LibreOffice ngược về Delphi

Khi HotXLS mở một tệp ODS, reader của nó chấp nhận cả hai phương ngữ conditional format và cả hai cách viết calcext, và nó không đếm một luật hai lần khi tệp mang nó ở cả hai dạng. Tệp thật đến từ ba writer, mỗi bên một thói quen:

• calcext cũ và mới. Các tệp có thuộc tính calcext:operator, gồm cả ODS do HotXLS viết trước v2.384.69, vẫn đi qua phần parse cũ. Điều kiện công thức được nhận ở dạng formula-is(...) hay is-true-formula(...) đều được
• Cách viết style:map của Excel. Excel thêm prefix of: vào các điều kiện, như of:cell-content-is-between(1,10), và bỏ base cell ở các value rule. Cả hai đều được chấp nhận
• Ô rỗng. Excel và LibreOffice đều đặt map cho ô rỗng lên column default style thay vì lên ô, nên reader phân giải column default style cho các repeated cell trước khi thu các map
• Dựng lại vùng. Map được thu theo từng ô, nên sau khi một sheet được đọc, reader gộp các ô chia sẻ cùng điều kiện và base cell trở lại thành range, trước hết dọc theo từng hàng rồi xuống các span cột khớp nhau, và vứt mọi luật đã đọc từ calcext rồi

Bản sửa v2.384.72 liên quan number style, không phải luật. Excel 16 và LibreOffice 26.2 cùng ghi định dạng General thành một number style mà phần tử number:number của nó không có number:decimal-places, thường là <number:number number:min-integer-digits="1"/>. Reader HotXLS từng coi số lượng thiếu đó là hai chữ số thập phân cố định, nên mọi giá trị trong style Default import với 0.00 và 1.5 hiển thị thành 1.50. Kể từ v2.384.72, một phần tử số trơn không có chữ số thập phân, không có minimum decimals, không nhóm và tối đa một chữ số nguyên ánh xạ về General, và một General trơ trãn để ô không có number format nào cả. Text quanh nó được giữ, như trong General" kg", còn số có nhóm giữ ánh xạ cũ vì Excel không có định dạng General có nhóm

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 tính từ 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;

    // Một ô ở style General của Excel đọc lại không có number format
    // kể từ v2.384.72, thay vì '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Các công thức luật quay về theo cú pháp Excel với dấu phẩy tách, cùng dạng bạn sẽ đưa cho AddCondFormatExpression, nên một luật do HotXLS viết đọc lại đúng thành chuỗi cũ. Muốn bức tranh rộng hơn về những gì đường import ODS giữ và bỏ, xem cẩm nang round-trip mở và lưu ODS của HotXLS; còn các hàng repeated của Excel và LibreOffice được mở rộng ra sao khi import, xem ODS repeated rows như các row-height run

Tương thích conditional format ODS của HotXLS bị chặn ở đâu?

Cách tiếp cận markup kép phủ các rule so sánh giá trị và formula rule, và dừng ở đó. Mọi thứ khác là một chiều hoặc không được ghi:

• Color scale và data bar chỉ được ghi thành các phần tử calcext, nên LibreOffice hiện còn Excel thì không
• Các loại luật khác, như icon set, text rule, top-N, above-average và duplicate rule, chưa có đầu ra ODS ở writer hiện tại. Một text rule thường có thể viết lại thành formula rule, ví dụ ISNUMBER(SEARCH("late",B2)) trên B2:B200, khi đó sẽ với tới cả hai ứng dụng
• Luật cả cột và cả hàng như C:C chỉ được phủ lên vùng bảng thực sự được ghi, chứ không phải cả 1,048,576 hàng, nên Excel chỉ thấy các luật này trên những ô có trong tệp
• Tệp chỉ có style:map. Khi tệp không có khối calcext, HotXLS diễn giải reference tương đối trong formula rule từ góc trên-trái của range được dựng lại, chứ không dịch chuyển từ base cell đã nêu
• Luật chồng nhau từ LibreOffice. Khi một ô bị vài luật phủ, LibreOffice chỉ ghi map của luật đầu lên ô đó. Những tệp như vậy không thể đọc trọn từ style:map một mình, thêm một lý do nữa để reader ưu tiên calcext khi cả hai cùng hiện diện

Giới hạn quy trình còn quan trọng hơn cả mấy điều kia. Các lỗi đằng sau những bản phát hành này lọt qua các round trip viết ODS rồi đọc lại bằng HotXLS, và vài cái cũng sẽ qua nổi một lần kiểm tra tay trong ứng dụng sai: công thức cả cột chạy tốt ở LibreOffice trong khi Excel hiện #NAME?, và từ v2.384.66 formula rule chạy tốt ở LibreOffice trong khi Excel vẫn không hiện luật nào cho tới v2.384.69. Nếu tương thích ODS là một yêu cầu, bài test nghiệm thu là mở tệp trong Excel và trong LibreOffice rồi so những gì mỗi bên hiện. Kỷ luật ấy cũng đúng với các style mà luật trỏ tới; bài conditional formatting và styles của HotXLS nói về cách các style tô sáng được định nghĩa phía workbook

Tra nhanh: ODS mà cả hai ứng dụng đọc được

• Khai báo xmlns:of và xmlns:msoxl trên root content.xml, nếu không LibreOffice hiện #VALUE! cho mọi công thức (HotXLS kể từ v2.384.56)
• Ghi reference thành [.A1], giữ mọi $, và ghi cả cột cùng cả hàng thành [.A:.A] và [.1:.1] (kể từ v2.384.55 và v2.384.65)
• Dùng ; cho đối số, ~ cho reference union, và | giữa các hàng mảng inline
• Ghi mỗi value hay formula rule thành một <style:map> trên style của mọi ô được phủ cho Excel, và thành một điều kiện calcext cho LibreOffice (kể từ v2.384.69)
• Trong calcext, đặt operator trong value (>3, between(1,10)) và viết formula rule thành formula-is(...) kèm base cell (kể từ v2.384.66 và v2.384.69)
• Chờ đợi một number style General thiếu number:decimal-places khi import; HotXLS đọc nó thành General kể từ v2.384.72
• Verify mọi profile export mới bằng cách mở tệp trong cả Excel lẫn LibreOffice, đừng bao giờ chỉ mở một bên

HotXLS là một thư viện spreadsheet Delphi và C++Builder thuần nhất đọc và ghi XLS, XLSX và ODS mà không cần cài Excel hay LibreOffice; mã nguồn đầy đủ, danh sách tính năng và license nằm trên trang HotXLS Delphi spreadsheet component