ODS interop HotXLS: формули та правила, які читає Excel

Щоб продукувати ODS файл, який і Excel, і LibreOffice читають правильно, HotXLS пише кожну формулу в синтаксисі OpenFormula під оголошеним namespace of:, а кожен value чи formula conditional format пише двічі: як <style:map> на стилі кожної покритої клітинки — єдина форма, яку читає Excel 16, — і як блок calcext:conditional-formats, якому довіряє LibreOffice. Кожен застосунок ігнорує половину, призначену для іншого, тож файл, який правильно показується в одному з них, нічого не доводить щодо іншого

Останнє речення — урок шести релізів HotXLS між v2.384.55 і v2.384.72. Кожен fix починався з файлу, який HotXLS написав, прочитав назад бездоганно, а один із двох цільових застосунків прочитав неправильно. Далі — те, що кожен застосунок насправді приймає, розмітка, яка влаштовує обох, і виклики HotXLS API, що продукують її з Delphi

Чому ODS файл виглядає гаразд в одному застосунку і зламаним в іншому?

ODS файл виглядає гаразд в одному застосунку і зламаним в іншому, бо Excel і LibreOffice читають різні частини одного пакета. OpenDocument дає формулам і conditional formats більше одного легального написання, LibreOffice додає зверху власний extension namespace, і кожен consumer обирає підмножину, яку імплементував. Writer, протестований лише проти одного consumer, охоче зійдеться на розмітці, яку інший тихо прочитає неправильно

Жоден застосунок не повідомляє про помилку. LibreOffice показує #VALUE! у клітинках, чиї формули не зміг розпарсити; Excel відкриває workbook, де conditional formats просто відсутні, чи з формулою, переписаною в те, що обчислюється в #NAME? чи константу 0. Writer, який робить round-trip власного виводу, ніколи цього не бачить. HotXLS потрапив точно в ту пастку з formula namespace: його reader матчив префікс of: як plain text, тож кожен self round trip проходив, поки LibreOffice показував #VALUE! у кожній формульній клітинці

Фіча	Excel 16 читає	LibreOffice 26.2 читає
Цілий стовпець, записаний як A:A	Прочитано неправильно як A:(A)	Толерується
Цілий стовпець, записаний як [.A:.A]	Так	Так
Conditional formats у <style:map>	Так, єдина форма, яку він читає	Ігнорується, коли є calcext
Conditional formats у calcext:conditional-formats	Ігнорується	Так, пріоритетно
calcext value правило з атрибутом calcext:operator	Ігнорується	Імпортується як «дорівнює 0»
calcext formula правило, написане як is-true-formula(...)	Ігнорується	Імпортується як порівняння значення з 0

OpenFormula в ODS: оголосіть namespace, потім зробіть правильно синтаксис

Формульна клітинка в ODS читається LibreOffice лише коли префікс of: у table:formula резолвиться в оголошений XML namespace. Префікс — не прикраса. of: мапиться на urn:oasis:names:tc:opendocument:xmlns:of:1.2, а msoxl: — префікс, який HotXLS уживає для формул, що їх OpenFormula translator не моделює, — мапиться на http://schemas.microsoft.com/office/excel/formula. До v2.384.56 корінь content.xml уживав обидва префікси без оголошення, і LibreOffice взагалі не міг ідентифікувати formula grammar

<!-- До v2.384.56: префікс уживано, ніколи не оголошено; LibreOffice показує #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"/>

<!-- Від v2.384.56: обидва formula namespaces оголошені на корені -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

З namespace, виправленим, сам вираз усе ще мусить бути валідним OpenFormula, як визначено в OpenDocument 1.3 Part 4. Пастки — у місцях, де синтаксис Excel і OpenFormula виглядають схоже, але не є тим самим:

• Cell references беруться у дужки з крапковим префіксом, і маркери $ — частина reference: [.$A$1] і [.A$1:.$B2] — валідний OpenFormula. До v2.384.55 writer HotXLS викидав кожен $, тож абсолютні references поверталися відносними і ламалися лише коли хтось копіював клітинку
• Цілі стовпці та рядки мусять уживати дужкову форму [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Голий of:=SUM(A:A) LibreOffice толерує, але Excel 16 відкриває його як =SUM(A:(A)) з #NAME?, а references на рядки та $A:$B перетворює на константу 0. HotXLS пише дужкову форму від v2.384.65
• Аргументи функцій розділяються ;, а не ,
• Reference unions уживають оператор ~: Excel-івське AREAS((A1,B2)) стає AREAS(([.A1]~[.B2])). Перекласти ту кому в ; — і один union-аргумент стає двома аргументами
• Inline arrays розділяють стовпці ; і рядки |: Excel-івське {1,2;3,4} стає {1;2|3;4}. До v2.384.55 HotXLS продукував {1;2;3;4} — один рядок із чотирьох значень

Кома — найтяжча частина, бо один Excel-івський символ несе три значення. Від v2.384.55 writer HotXLS веде стек дужок під час перекладу: ( прямо після імені відкриває виклик функції, чиї коми стають ;; будь-яка інша ( — групувальна дужка, чиї коми стають ~; а коми всередині {} — розділювачі стовпців array. З цим і з виправленням namespace LibreOffice 26.2 обчислив усі вісім probe-формул array та union правильно, включно з INDEX і AREAS над unions

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;

    // Пишеться як of:=SUM([.A:.A]) від v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Пишеться як of:=[.A1]*[.$B$1]; маркери $ виживають від v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

Формули, які translator не моделює, відкатуються до msoxl:= з незміненим текстом Excel — саме тому оголошення msoxl теж має значення. У поточному writer той шлях включає sheet-qualified references на кшталт Sheet2!A1 та structured table references. HotXLS читає формули msoxl: назад при імпорті, тож власний round trip зберігає вираз цілим, але як їх трактуватиме інший застосунок — поза контролем writer. Якщо формула, від якої залежать ваші consumer-и, виходить із префіксом msoxl:, відкрийте файл в обох застосунках, перш ніж випускати

Чому Excel не бачить conditional formats, записаних лише як calcext?

Excel 16 не бачить calcext conditional formats, бо читає ODS conditional formats виключно з дітей <style:map> клітинкових стилів і повністю ігнорує блок calcext:conditional-formats. Експеримент, який це закріплює, короткий: візьміть ODS, збережений LibreOffice, видаліть елементи style:map — Excel читає нуль правил; видаліть натомість блок calcext — Excel все ще читає всі. LibreOffice поводиться навпаки. calcext — extension namespace LibreOffice, не частина стандарту ODF, і коли правило calcext присутнє, LibreOffice бере його та ігнорує style:map

До v2.384.69 HotXLS писав лише calcext, тож ODS файл із цілком робочим підсвічуванням відкривався в Excel без жодного value чи formula правила. Тепер HotXLS пише обидві форми. Половина style:map уживає condition grammar схеми OpenDocument (ODF 1.3 Part 3) з тими написаннями дослівно, які й Excel 16, і LibreOffice 26.2 продукують, зберігаючи ODS:

<!-- Спрощено. Carrier style для кожної клітинки A1:A50 (два value правила) -->
<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 для кожної клітинки C1:C50 (одне formula правило) -->
<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>

Пастка з style:map у тому, що він живе на клітинкових стилях, тож є per cell. Кожна клітинка в range правила мусить нести стиль, що тримає мапу, — порожні клітинки включно, — інакше правило просто не покриває ту клітинку в Excel. HotXLS копіює наявний форматувальний стиль кожної клітинки, додає мапи і дедуплікує carrier styles за парою початкового стилю та тексту мапи, тож range з 500 клітинок з однаковим форматуванням усе ще продукує один стиль. Writer також розширює записану таблицю до range правила, що означає: порожні хвостові рядки всередині правила виводяться, а не відкидаються. Від v2.384.69 styles.xml теж несе порожній клітинковий стиль Default, тож style:apply-style-name="Default" завжди має ціль

Написання calcext, яке LibreOffice насправді приймає

LibreOffice приймає calcext value правило лише коли оператор порівняння — частина тексту значення, як-от >3 чи between(1,10), а formula правило — лише коли воно написане як formula-is(...). Обидва пункти коштували HotXLS по релізу, бо неправильні написання продукували правило, яке імпортується без помилки, а потім збирає неправильні клітинки

Першою помилкою був атрибут calcext:operator поруч із calcext:value. Читається природно, але він вигаданий: LibreOffice не знає того атрибуту, тож імпортував кожне value правило як «дорівнює 0». Другою було вкладання is-true-formula(...), написання зі style:map, у calcext умову, яке LibreOffice імпортував теж як порівняння значення клітинки з 0. Formula fix вийшов у v2.384.66, а value fix — у v2.384.69:

<!-- Неправильно: LibreOffice ігнорує calcext:operator і імпортує "equal to 0" -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Правильно: оператор подорожує всередині значення -->
<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"/>

<!-- Правильно: formula правила вживають formula-is, відносні refs закорені за 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 — те, що дає відносним references зміст. HotXLS закорінює кожне правило у верхньо-ліву клітинку його першої range-області, тож формула, написана для C1, обчислюється як C2, C3 і так далі вниз по range, рівно як у власному conditional formatting Excel. Вираз правила йде через той самий translator, що й клітинкові формули, тож arrays, unions, цілі стовпці та маркери $ виходять у описаних вище формах. З боку Delphi ви додаєте правила точно так, як для .xlsx файлу

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Value правила: style:map cell-content()>100 плюс calcext значення ">100"
  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR: світло-червоний

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

  // Formula правило в синтаксисі Excel (коми-розділювачі, відносно C1):
  // style:map is-true-formula(...) плюс calcext formula-is(...)
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: світло-жовтий

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

Читання ODS з Excel і LibreOffice назад у Delphi

Коли HotXLS відкриває ODS файл, його reader приймає обидва діалекти conditional format і обидва написання calcext, і не рахує правило двічі, коли файл несе його в обох формах. Реальні файли походять від трьох writer, кожен зі своїми звичками:

• Старий і новий calcext. Файли з атрибутом calcext:operator, включно з ODS, написаними HotXLS до v2.384.69, усе ще йдуть legacy-парсом. Formula умови розпізнаються як formula-is(...), так і is-true-formula(...)
• Написання style:map від Excel. Excel префіксує умови of:, як у of:cell-content-is-between(1,10), і опускає base cell у value правилах. Обидва приймаються
• Порожні клітинки. Excel і LibreOffice обидва кладуть мапу для порожніх клітинок на column default стиль, а не на клітинку, тож reader резолвить column default стилі для repeated cells, перш ніж збирати мапи
• Перебудова областей. Мапи збираються per cell, тож після прочитання sheet reader зливає клітинки, що ділять ту саму умову та base cell, назад у ranges — спершу поперек кожного рядка, потім вниз по збіжних стовпцевих проміжках — і відкидає будь-яке правило, уже прочитане з calcext

Fix у v2.384.72 стосується number styles, а не правил. Excel 16 і LibreOffice 26.2 обидва пишуть формат General як number style, чиєму елементу number:number бракує number:decimal-places, типово <number:number number:min-integer-digits="1"/>. Reader HotXLS трактував відсутній рахунок як два фіксовані десяткові, тож кожне значення в стилі Default імпортувалося з 0.00, і 1.5 показувалося як 1.50. Від v2.384.72 голий number елемент без десяткових місць, без мінімуму десяткових, без групування і щонайбільше з однією цифрою цілої частини мапиться на General, а самотній General лишає клітинку взагалі без number format. Текст навколо зберігається, як у General" kg", а згруповані числа тримають попереднє маплення, бо в Excel немає grouped 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]; // індексатор Sheets — від 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;

    // Клітинка в стилі General Excel читається назад без number format
    // від v2.384.72, замість '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Формули правил повертаються в синтаксисі Excel з комами-розділювачами — у тому вигляді, який ви передали б у AddCondFormatExpression, тож правило, написане HotXLS, читається назад як ідентичний рядок. Про ширшу картину того, що ODS import path зберігає і відкидає, — у посібнику HotXLS з round-trip відкриття та збереження ODS; про те, як repeated rows з Excel і LibreOffice розгортаються при імпорті, — у ODS repeated rows як row-height runs

Які межі ODS conditional format interop у HotXLS?

Підхід подвійної розмітки покриває value-порівняльні правила та formula правила, і на тому стає. Все інше — однобоке чи не записується взагалі:

• Color scales та data bars пишуться лише як calcext елементи, тож LibreOffice їх показує, а Excel — ні
• Інші типи правил, як-от icon sets, текстові правила, top-N, above-average та duplicate правила, не мають ODS виводу в поточному writer. Текстове правило зазвичай можна переписати як formula правило, наприклад ISNUMBER(SEARCH("late",B2)) над B2:B200, — таке дістає обох застосунків
• Правила на цілі стовпці та рядки на кшталт C:C лягають лише на реально записану область таблиці, а не на всі 1 048 576 рядків, тож Excel бачить ці правила лише на клітинках, які існують у файлі
• Файли лише зі style:map. Коли файл не має блоку calcext, HotXLS інтерпретує відносні references у formula правилах від верхньо-лівого кута перебудованого range, а не зсувом від заявленого base cell
• Правила, що перекриваються, з LibreOffice. Коли одну клітинку покриває кілька правил, LibreOffice пише на неї мапу лише першого правила. Такі файли неможливо прочитати повністю з одного style:map, і це ще одна причина, чому reader надає перевагу calcext, коли є обидва

Процесна межа важить більше за будь-яку з цих. Дефекти за цими релізами пройшли повз round trips, які писали ODS і читали назад HotXLS-ом, а деякі пройшли б і ручну перевірку в неправильному застосунку: whole-column формули працювали в LibreOffice, поки Excel показував #NAME?, і від v2.384.66 formula правила працювали в LibreOffice, поки Excel не показував узагалі жодних правил аж до v2.384.69. Якщо ODS interop — вимога, acceptance-тест — це відкриття файлу в Excel і в LibreOffice та порівняння того, що показує кожен. Та сама дисципліна стосується стилів, на які правила вказують; стаття HotXLS про conditional formatting і styles покриває, як highlight стилі визначаються з боку workbook

Швидка довідка: ODS, який читають обидва застосунки

• Оголосіть xmlns:of і xmlns:msoxl на корені content.xml, або LibreOffice показує #VALUE! для кожної формули (HotXLS від v2.384.56)
• Пишіть references як [.A1], зберігайте кожен $, а цілі стовпці та рядки пишіть як [.A:.A] і [.1:.1] (від v2.384.55 і v2.384.65)
• Уживайте ; для аргументів, ~ для reference unions і | між рядками inline array
• Пишіть кожне value чи formula правило як <style:map> на стилі кожної покритої клітинки для Excel і як calcext умову для LibreOffice (від v2.384.69)
• У calcext кладіть оператор у значення (>3, between(1,10)) і пишіть formula правила як formula-is(...) з base cell (від v2.384.66 і v2.384.69)
• Очікуйте на імпорті General number style без number:decimal-places; HotXLS читає його як General від v2.384.72
• Верифікуйте кожен новий export-профіль відкриттям файлу і в Excel, і в LibreOffice — ніколи лише в одному з них

HotXLS — нативна spreadsheet бібліотека Delphi та C++Builder, яка читає й пише XLS, XLSX та ODS без установленого Excel чи LibreOffice; повний вихідний код, список фіч і ліцензування — на сторінці HotXLS Delphi spreadsheet component page