ODS-интероп HotXLS: формулы и правила, читаемые Excel

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

Последняя фраза — урок шести релизов HotXLS между v2.384.55 и v2.384.72. Каждая правка начиналась с файла, который HotXLS написал, сам прочёл назад идеально, а одно из двух целевых приложений прочитало неверно. Дальше — что каждое приложение реально принимает, какая разметка устраивает обоих и какие вызовы API HotXLS производят её из Delphi

Почему ODS-файл выглядит целым в одном приложении и битым в другом?

ODS-файл выглядит целым в одном приложении и битым в другом, потому что Excel и LibreOffice читают разные части одного пакета. OpenDocument даёт формулам и условным форматам больше одного легального написания, LibreOffice сверху добавляет собственный extension-namespace, и каждый потребитель выбирает подмножество, которое реализует. Писатель, протестированный против одного лишь потребителя, радостно сойдётся на разметке, которую другой беззвучно прочтёт неправильно

Ни одно приложение не сообщает об ошибке. LibreOffice показывает #VALUE! в ячейках, чьи формулы не смог разобрать; Excel открывает книгу с условными форматами, попросту отсутствующими, или с формулой, переписанной во что-то, что вычисляется в #NAME? или константу 0. Писатель, прокручивающий собственный вывод туда-обратно, ничего этого не видит. HotXLS угодил ровно в ту ловушку с formula-namespace: его читатель сопоставлял префикс of: как простой текст, так что всякий самозамыкающийся прогон проходил, а LibreOffice показывал #VALUE! в каждой формульной ячейке

Возможность	Читает Excel 16	Читает LibreOffice 26.2
Целый столбец, записанный как A:A	Прочитан неверно как A:(A)	Терпится
Целый столбец, записанный как [.A:.A]	Да	Да
Условные форматы в <style:map>	Да, единственная читаемая форма	Игнорируются при наличии calcext
Условные форматы в calcext:conditional-formats	Игнорируются	Да, предпочтительно
Правило по значению calcext с атрибутом calcext:operator	Игнорируется	Импортируется как «равно 0»
Правило по формуле calcext в написании 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, — отображается в http://schemas.microsoft.com/office/excel/formula. До v2.384.56 корень content.xml пользовался обоими префиксами без их объявления, и LibreOffice не мог опознать формульную грамматику вовсе

<!-- До 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-namespace объявлены на корне -->
<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 выглядят похоже, но не совпадают:

• Ссылки на ячейки в скобках и с точечным префиксом, а маркеры $ — часть ссылки: [.$A$1] и [.A$1:.$B2] — корректный OpenFormula. До v2.384.55 писатель HotXLS выбрасывал всякий $, абсолютные ссылки возвращались относительными и шалили лишь когда кто-то копировал ячейку
• Целые столбцы и строки обязаны пользоваться формой в скобках [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Голый of:=SUM(A:A) LibreOffice терпит, но Excel 16 открывает его как =SUM(A:(A)) с #NAME?, а ссылки на строки и $A:$B обращает в константу 0. HotXLS пишет форму в скобках с v2.384.65
• Аргументы функций разделяются ;, а не ,
• Объединения ссылок пользуются оператором ~: Excel-овское AREAS((A1,B2)) становится AREAS(([.A1]~[.B2])). Переведёте ту запятую в ; — и один аргумент-объединение станет двумя аргументами
• Встроенные массивы разделяют столбцы ; и строки |: Excel-овский {1,2;3,4} становится {1;2|3;4}. До v2.384.55 HotXLS выдавал {1;2;3;4} — одну строку из четырёх значений

Запятая — трудная часть, потому что один Excel-символ несёт три смысла. С v2.384.55 писатель HotXLS ведёт стек скобок во время перевода: ( сразу после имени открывает вызов функции, чьи запятые становятся ;; любая другая ( — группирующая скобка, чьи запятые становятся ~; а запятые внутри {} — разделители столбцов массива. С этим и с починенным namespace LibreOffice 26.2 верно вычислил все восемь пробных формул над массивами и объединениями, включая INDEX и AREAS над объединениями

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;

Формулы, которые транслятор не моделирует, уходят в msoxl:= с неизменным Excel-текстом, отчего объявление msoxl тоже важно. В нынешнем писателе тот путь включает ссылки с квалификацией листа вроде Sheet2!A1 и структурные ссылки на таблицы. HotXLS читает формулы msoxl: назад при импорте, так что собственный round trip хранит выражение целым, но как с ними обойдётся другое приложение — вне контроля писателя. Если формула, на которую полагаются ваши потребители, вышла с префиксом msoxl:, откройте файл в обоих приложениях, прежде чем выпускать

Почему Excel не видит условные форматы, записанные только как calcext?

Excel 16 не видит условные форматы calcext, потому что читает условные форматы ODS исключительно из детей <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 без правил по значениям и без правил по формулам вовсе. Теперь HotXLS пишет обе формы. Половина style:map пользуется грамматикой условий из схемы OpenDocument (ODF 1.3 Part 3), с точными написаниями, которые оба — Excel 16 и LibreOffice 26.2 — выдают при сохранении ODS:

<!-- Упрощено. Стиль-носитель для каждой ячейки A1:A50 (два правила по значениям) -->
<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>

<!-- Стиль-носитель для каждой ячейки C1:C50 (одно правило по формуле) -->
<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 в том, что он живёт на стилях ячеек, то есть размечается по ячейкам. Каждая ячейка диапазона правила обязана нести стиль с map, пустые включительно, иначе правило просто не накрывает эту ячейку в Excel. HotXLS копирует существующий стиль форматирования каждой ячейки, дописывает map и дедуплицирует стили-носители по паре «исходный стиль + текст map», так что диапазон из 500 ячеек с одинаковым форматированием по-прежнему даёт один стиль. Писатель также дотягивает записанную таблицу до диапазона правила, то есть пустые хвостовые строки внутри правила выпускаются, а не выбрасываются. С v2.384.69 styles.xml несёт и пустой стиль ячейки Default, так что style:apply-style-name="Default" всегда имеет цель

Написание calcext, которое LibreOffice реально принимает

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

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

<!-- Неверно: LibreOffice игнорирует calcext:operator и импортирует «равно 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-is, относительные ссылки заякорены на базовой ячейке -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>

Базовая ячейка — то, что даёт относительным ссылкам смысл. HotXLS заякоривает всякое правило на левой верхней ячейке первой области диапазона, так что формула, написанная для C1, вычисляется как C2, C3 и так далее вниз по диапазону — в точности как в собственном условном форматировании Excel. Выражение правила идёт через тот же транслятор, что и формулы ячеек, так что массивы, объединения, целые столбцы и маркеры $ выходят в описанных выше формах. На стороне Delphi вы добавляете правила в точности как для .xlsx-файла

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Правила по значениям: style:map cell-content()>100 плюс calcext value ">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);

  // Правило по формуле в 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-файл, его читатель принимает оба диалекта условных форматов и оба написания calcext и не считает правило дважды, когда файл несёт его в обеих формах. Реальные файлы приходят от трёх писателей, у каждого свои привычки:

• Старый и новый calcext. Файлы с атрибутом calcext:operator, включая ODS, написанные HotXLS до v2.384.69, всё ещё идут через легаси-разбор. Формульные условия распознаются и как formula-is(...), и как is-true-formula(...)
• Написание style:map от Excel. Excel приставляет к условиям префикс of:, как в of:cell-content-is-between(1,10), и опускает базовую ячейку в правилах по значениям. И то и другое принимается
• Пустые ячейки. И Excel, и LibreOffice кладут map для пустых ячеек на дефолтный стиль столбца, а не на ячейку, так что читатель разрешает дефолтные стили столбцов для повторяющихся ячеек, прежде чем собирать map
• Пересборка областей. Map собираются по ячейкам, так что после прочтения листа читатель сливает ячейки с тем же условием и той же базовой ячейкой обратно в диапазоны — сперва поперёк каждой строки, затем вниз по совпадающим столбцовым прогонам, — и выбрасывает правила, уже прочитанные из calcext

Правка v2.384.72 касается числовых стилей, а не правил. И Excel 16, и LibreOffice 26.2 пишут формат General как числовой стиль, чей элемент number:number не имеет number:decimal-places, типично <number:number number:min-integer-digits="1"/>. Читатель HotXLS трактовал недостающий счётчик как два фиксированных знака, так что всякое значение в стиле Default импортировалось с 0.00 и 1.5 показывалось как 1.50. С v2.384.72 простой числовой элемент без знаков после запятой, без минимальных знаков, без группировки и не более чем с одной целой цифрой отображается в General, а одинокий General оставляет ячейку вовсе без числового формата. Текст вокруг него сохраняется, как в General" kg", а сгруппированные числа держат прежнее отображение, потому что у Excel нет сгруппированного формата 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 с единицы
    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 читается назад без числового формата
    // с v2.384.72, вместо '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Формулы правил возвращаются в Excel-синтаксисе с разделителями-запятыми — в той же форме, какую вы передали бы в AddCondFormatExpression, так что правило, написанное HotXLS, читается назад той же строкой. О том, что путь импорта ODS в целом хранит и что бросает, — руководство HotXLS по round-trip открытия и сохранения ODS; о том, как повторяющиеся строки из Excel и LibreOffice разворачиваются при импорте, — повторяющиеся строки ODS как прогоны высот строк

Каковы пределы интеропа условных форматов ODS у HotXLS?

Подход с двойной разметкой покрывает правила сравнения значений и правила по формулам — и на том останавливается. Всё остальное односторонне или не пишется вовсе:

• Цветовые шкалы и гистограммы данных пишутся только элементами calcext, так что LibreOffice их показывает, а Excel нет
• Другие сорта правил, скажем наборы иконок, текстовые правила, top-N, выше-среднего и правила дубликатов, в нынешнем писателе ODS-вывода не имеют. Текстовое правило обычно можно переформулировать как правило по формуле, например ISNUMBER(SEARCH("late",B2)) поверх B2:B200, — и тогда оно доходит до обоих приложений
• Правила на целые столбцы и строки вроде C:C раскладываются только по реально записанной области таблицы, а не по всем 1 048 576 строкам, так что Excel видит эти правила лишь на ячейках, существующих в файле
• Файлы с одним только style:map. Когда в файле нет блока calcext, HotXLS истолковывает относительные ссылки в правилах по формулам от левого верхнего угла пересобранного диапазона, а не сдвигом от заявленной базовой ячейки
• Перекрывающиеся правила из LibreOffice. Когда одна ячейка накрыта несколькими правилами, LibreOffice пишет на неё map только первого правила. Такие файлы невозможно дочитать из одного лишь style:map — ещё одна причина, по которой читатель предпочитает calcext, когда есть оба

Процессный предел важнее любого из этих. Дефекты за этими релизами проходили round-trip тесты, писавшие ODS и читавшие его назад HotXLS, а некоторые прошли бы и ручную проверку в неверном приложении: формулы на целые столбцы работали в LibreOffice, пока Excel показывал #NAME?, а с v2.384.66 правила по формулам работали в LibreOffice, пока Excel не показывал правил вовсе до v2.384.69. Если интероп с ODS — требование, приёмочный тест — открыть файл в Excel и в LibreOffice и сравнить, что показывает каждый. Та же дисциплина касается стилей, на которые указывают правила; как определяются стили подсветки на стороне книги — в статье HotXLS об условном форматировании и стилях

Шпаргалка: ODS, читаемый обоими приложениями

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

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