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

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

Последното изречение е урокът от шест release-а на HotXLS между v2.384.55 и v2.384.72. Всяка поправка е започвала с файл, който HotXLS е записала, чете обратно безупречно, а едното от двете целеви приложения чете грешно. Тук следва това, което всяко приложение реално приема, маркировката, която устройва и двете, и HotXLS API извикванията, които я произвеждат от Delphi

Защо ODS файл изглежда наред в едно приложение и счупен в другото?

ODS файл изглежда наред в едно приложение и счупен в другото, защото Excel и LibreOffice четат различни части от един и същ пакет. OpenDocument дава на формулите и на conditional format-ите повече от едно легитимно изписване, LibreOffice надгражда със собствен extension namespace, а всеки консуматор избира подмножеството, което е имплементирал. Writer, тестван само срещу един консуматор, спокойно се добира до маркировка, която другият тихо чете грешно

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

Функционалност	Excel 16 чете	LibreOffice 26.2 чете
Цяла колона, записана като A:A	Прочета погрешно като A:(A)	Толерира се
Цяла колона, записана като [.A:.A]	Да	Да
Conditional формати в <style:map>	Да, единствената форма, която чете	Игнорират се, когато има calcext
Conditional формати в 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 транслаторът ѝ не моделира — сочи към http://schemas.microsoft.com/office/excel/formula. Преди v2.384.56 коренът на content.xml ползваше и двата префикса без да ги декларира, и LibreOffice изобщо не можеше да разпознае граматиката на формулите

<!-- Преди v2.384.56: prefix-ът се ползва, но никога не е деклариран; 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 си приличат, но не са едно и също:

• Референциите към клетки са в квадратни скоби с точка отпред, а $ маркерите са част от референцията: [.$A$1] и [.A$1:.$B2] са валиден OpenFormula. Преди v2.384.55 writer-ът на 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])). Ако преведете тази запетая на ;, един аргумент за обединение тихо става два аргумента
• Inline масивите разделят колоните с ;, а редовете с |: Excel-ският {1,2;3,4} става {1;2|3;4}. Преди v2.384.55 HotXLS произвеждаше {1;2;3;4} — един ред с четири стойности

Запетаята е трудната част, защото един Excel знак носи три значения. От v2.384.55 насам writer-ът на 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 е важна. В текущия writer този път обхваща референции с име на лист като Sheet2!A1 и structured table референции. HotXLS чете msoxl: формулите обратно при импорт, така че собственият ѝ round trip пази израза, но как друго приложение ще се отнася към тях е извън контрола на writer-а. Ако формула, от която консуматорите ви зависят, излезе с msoxl: префикс, отворете файла и в двете приложения, преди да го пуснете

Защо Excel не вижда conditional форматите, записани само като calcext?

Excel 16 не вижда calcext conditional форматите, защото чете ODS conditional форматите изключително от <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 граматиката на OpenDocument схемата (ODF 1.3 Part 3), с точните изписвания, които Excel 16 и LibreOffice 26.2 произвеждат и двете при запис на ODS:

<!-- Опростено. Carrier стил за всяка клетка от 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 стил за всяка клетка от 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 е, че живее върху клетъчни стилове, тоест е на клетка. Всяка клетка в диапазона на правилото трябва да носи стил, държащ map-а, празните клетки включително, иначе правилото просто не покрива тази клетка в Excel. HotXLS копира съществуващия форматен стил на всяка клетка, долепя map-овете и дедупликира carrier стиловете по двойката оригинален стил и текст на map — диапазон от 500 клетки с идентично форматиране пак дава един стил. Writer-ът също разтяга записаната таблица до диапазона на правилото, тоест празните опашни редове вътре в правило се извеждат, вместо да се режат. От v2.384.69 насам styles.xml носи и празен клетъчен стил Default, така че style:apply-style-name="Default" винаги има цел

calcext изписването, което LibreOffice реално приема

LibreOffice приема calcext value правило само когато операторът за сравнение е част от текста на стойността, като >3 или between(1,10), а formula правило — само когато е изписано formula-is(...). Двете точки струваха на HotXLS по един release, защото грешните изписвания дават правило, което се импортира без грешка и после мачва грешните клетки

Първата грешка беше атрибут calcext:operator до calcext:value. Чете се естествено, но е измислен: LibreOffice не познава този атрибут, така че импортира всяко value правило като „равно на 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 правилата ползват 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 и надолу по диапазона — точно както в собствения conditional formatting на Excel. Изразът на правилото минава през същия транслатор като клетъчните формули, така че масивите, обединенията, целите колони и $ маркерите излизат в описаните по-горе форми. От страната на Delphi добавяте правилата точно както бихте добавили за .xlsx файл

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Value правила: 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);

  // 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 файл, четецът ѝ приема и двата conditional format диалекта и двете calcext изписвания, и не брои правило два пъти, когато файлът го носи и в двете форми. Реалните файлове идват от трима writer-и, всеки със своите навици:

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

Поправката в v2.384.72 засяга number стиловете, не правилата. Excel 16 и LibreOffice 26.2 и двете записват General формата като number стил, чийто елемент number:number няма number:decimal-places, обикновено <number:number number:min-integer-digits="1"/>. Четецът на HotXLS третираше липсващия брой като две фиксирани десетични, така че всяка стойност в стил Default се импортираше с 0.00 и 1.5 се показваше като 1.50. От v2.384.72 насам чист number елемент без десетични места, без минимум десетични, без групиране и с най-много една цифра за цялата част се мапва към General, а самотен General оставя клетката изобщо без number формат. Текстът около него се запазва, както в 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 е от 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 импорт пътят вижте ръководството на HotXLS за ODS round-trip при отваряне и запис; за това как repeated редовете от Excel и LibreOffice се разгъват при импорт вижте ODS repeated редовете като row-height run-ове

Какви са границите на ODS conditional format interop в HotXLS?

Подходът с двойна маркировка покрива правилата за сравнение на стойности и formula правилата, и стига дотам. Всичко останало е едностранно или изобщо не се записва:

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

Процесната граница е по-важна от всяка от тези. Дефектите зад тези release-и минаха през round-trip тестове, записващи ODS и четещи го обратно с HotXLS, а някои биха минали и през ръчна проверка в грешното приложение: целоколонните формули работеха в LibreOffice, докато Excel показваше #NAME?, а от v2.384.66 formula правилата работеха в LibreOffice, докато Excel не показваше никакви правила чак до v2.384.69. Ако ODS interop е изискване, приемният тест е отваряне на файла в Excel и в LibreOffice и сравняване на това, което всяко показва. Същата дисциплина важи и за стиловете, към които сочат правилата; статията на HotXLS за conditional formatting и стиловете покрива как highlight стиловете се дефинират от страната на работната книга

Бърза справка: ODS, който и двете приложения четат

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

HotXLS е нативна Delphi и C++Builder spreadsheet библиотека, която чете и записва XLS, XLSX и ODS без инсталиран Excel или LibreOffice; пълният source, списъкът с функционалности и лицензирането са на страницата на HotXLS Delphi spreadsheet component