Interop ODS de HotXLS: fórmulas y reglas que Excel lee

Para producir un archivo ODS que tanto Excel como LibreOffice lean correctamente, HotXLS escribe cada fórmula en sintaxis OpenFormula bajo un namespace of: declarado, y escribe dos veces cada formato condicional de valor o de fórmula: como un <style:map> en el estilo de cada celda cubierta, que es la única forma que Excel 16 lee, y como un bloque calcext:conditional-formats, que es la forma en la que LibreOffice confía. Cada aplicación ignora la mitad destinada a la otra, así que un archivo que se ve bien en una de ellas no prueba nada sobre la otra

Esa última oración es la lección detrás de seis versiones de HotXLS entre v2.384.55 y v2.384.72. Cada corrección empezó con un archivo que HotXLS escribió, releyó perfectamente, y una de las dos aplicaciones objetivo leyó mal. Lo que sigue es lo que cada aplicación realmente acepta, el markup que satisface a ambas, y las llamadas a la API de HotXLS que lo producen desde Delphi

¿Por qué un archivo ODS se ve bien en una aplicación y roto en la otra?

Un archivo ODS se ve bien en una aplicación y roto en la otra porque Excel y LibreOffice leen partes distintas del mismo paquete. OpenDocument da a las fórmulas y a los formatos condicionales más de una escritura legal, LibreOffice encima agrega su propio namespace de extensión, y cada consumidor elige el subconjunto que implementa. Un writer probado contra un solo consumidor convergerá felizmente en markup que el otro lee mal en silencio

Ninguna de las dos aplicaciones reporta un error. LibreOffice muestra #VALUE! en las celdas cuyas fórmulas no pudo parsear; Excel abre el workbook con los formatos condicionales simplemente ausentes, o con una fórmula reescrita en algo que evalúa a #NAME? o a la constante 0. Un writer que hace round-trip de su propia salida jamás ve nada de esto. HotXLS cayó exactamente en esa trampa con el namespace de fórmulas: su reader emparejaba el prefijo of: como texto plano, así que cada round-trip consigo mismo pasaba mientras LibreOffice mostraba #VALUE! en cada celda con fórmula

Característica	Excel 16 lee	LibreOffice 26.2 lee
Columna completa escrita como A:A	Mal leída como A:(A)	Tolerada
Columna completa escrita como [.A:.A]	Sí	Sí
Formatos condicionales en <style:map>	Sí, la única forma que lee	Ignorados cuando calcext está presente
Formatos condicionales en calcext:conditional-formats	Ignorados	Sí, preferidos
Regla de valor calcext con atributo calcext:operator	Ignorada	Importada como "igual a 0"
Regla de fórmula calcext escrita is-true-formula(...)	Ignorada	Importada como comparación de valor con 0

OpenFormula en ODS: declare el namespace y luego acierte la sintaxis

Una celda de fórmula en ODS solo es legible por LibreOffice cuando el prefijo of: en table:formula resuelve a un namespace XML declarado. El prefijo no es decoración. of: mapea a urn:oasis:names:tc:opendocument:xmlns:of:1.2, y msoxl:, el prefijo que HotXLS usa para fórmulas que su traductor OpenFormula no modela, mapea a http://schemas.microsoft.com/office/excel/formula. Antes de v2.384.56 la raíz de content.xml usaba ambos prefijos sin declararlos, y LibreOffice no podía identificar la gramática de fórmulas en absoluto

<!-- Antes de v2.384.56: prefijo usado, jamás declarado; LibreOffice muestra #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"/>

<!-- Desde v2.384.56: ambos namespaces de fórmula declarados en la raíz -->
<office:document-content
    xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
    xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>

Con el namespace arreglado, la expresión en sí todavía tiene que ser OpenFormula válido, según se define en OpenDocument 1.3 Parte 4. Las trampas están en los lugares donde la sintaxis Excel y OpenFormula se parecen pero no son lo mismo:

• Las referencias de celda van entre corchetes y con punto delante, y los marcadores $ son parte de la referencia: [.$A$1] y [.A$1:.$B2] son OpenFormula válido. Antes de v2.384.55 el writer de HotXLS eliminaba cada $, así que las referencias absolutas volvían relativas y solo salían mal cuando alguien copiaba la celda
• Las columnas y filas completas deben usar la forma entre corchetes [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Un of:=SUM(A:A) suelto lo tolera LibreOffice, pero Excel 16 lo abre como =SUM(A:(A)) con #NAME?, y convierte las referencias de fila y $A:$B en la constante 0. HotXLS escribe la forma entre corchetes desde v2.384.65
• Los argumentos de función se separan con ;, no con ,
• Las uniones de referencias usan el operador ~: el AREAS((A1,B2)) de Excel se vuelve AREAS(([.A1]~[.B2])). Traducir esa coma a ; en cambio convierte un argumento unión en dos argumentos
• Los arrays inline separan columnas con ; y filas con |: el {1,2;3,4} de Excel se vuelve {1;2|3;4}. Antes de v2.384.55 HotXLS producía {1;2;3;4}, una sola fila de cuatro valores

La coma es la parte difícil, porque un solo carácter de Excel carga tres significados. Desde v2.384.55 el writer de HotXLS lleva una pila de paréntesis mientras traduce: un ( inmediatamente después de un nombre abre una llamada a función, cuyas comas se vuelven ;; cualquier otro ( es un paréntesis de agrupación, cuyas comas se vuelven ~; y las comas dentro de {} son separadores de columnas de array. Con eso y la corrección del namespace, LibreOffice 26.2 evaluó correctamente las ocho fórmulas de prueba de arrays y uniones, INDEX y AREAS sobre uniones incluidas

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;

    // Se escribe como of:=SUM([.A:.A]) desde v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Se escribe como of:=[.A1]*[.$B$1]; los $ sobreviven desde v2.384.55
    Sheet.Cells[2, 4].Formula := 'A1*$B$1';

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

Las fórmulas que el traductor no modela caen en msoxl:= con el texto Excel sin cambios, razón por la cual la declaración msoxl también importa. En el writer actual esa ruta incluye referencias calificadas por hoja como Sheet2!A1 y referencias estructuradas de tabla. HotXLS lee de vuelta las fórmulas msoxl: al importar, así que su propio round-trip conserva la expresión intacta, pero cómo las trate otra aplicación está fuera del control del writer. Si una fórmula de la que dependen sus consumidores sale con el prefijo msoxl:, abra el archivo en ambas aplicaciones antes de enviarlo

¿Por qué Excel no ve los formatos condicionales escritos solo como calcext?

Excel 16 no ve los formatos condicionales calcext porque lee los formatos condicionales de ODS exclusivamente de los hijos <style:map> de los estilos de celda e ignora por completo el bloque calcext:conditional-formats. El experimento que lo zanja es corto: tome un ODS guardado por LibreOffice, borre los elementos style:map, y Excel lee cero reglas; borre en cambio el bloque calcext, y Excel las sigue leyendo todas. LibreOffice se comporta al revés. calcext es el namespace de extensión de LibreOffice, no forma parte del estándar ODF, y cuando hay una regla calcext presente LibreOffice la toma e ignora el style:map

Antes de v2.384.69 HotXLS escribía solo calcext, así que un archivo ODS con un resaltado perfectamente bueno se abría en Excel sin reglas de valor ni reglas de fórmula alguna. HotXLS ahora escribe ambas formas. La mitad style:map usa la gramática de condiciones del esquema OpenDocument (ODF 1.3 Parte 3), con las escrituras exactas que tanto Excel 16 como LibreOffice 26.2 producen al guardar ODS:

<!-- Simplificado. Estilo portador para cada celda de A1:A50 (dos reglas de valor) -->
<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>

<!-- Estilo portador para cada celda de C1:C50 (una regla de fórmula) -->
<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>

El detalle con style:map es que vive en los estilos de celda, así que es por celda. Cada celda del rango de la regla tiene que llevar un estilo que contenga el map, celdas vacías incluidas, o la regla simplemente no cubre esa celda en Excel. HotXLS copia el estilo de formato existente de cada celda, agrega los maps, y deduplica los estilos portadores por el par de estilo original y texto del map, así que un rango de 500 celdas con formato idéntico sigue produciendo un solo estilo. El writer además extiende la tabla escrita hasta el rango de la regla, lo que significa que las filas finales vacías dentro de una regla se emiten en vez de descartarse. Desde v2.384.69 styles.xml también lleva un estilo de celda Default vacío, así que style:apply-style-name="Default" siempre tiene un destino

La escritura calcext que LibreOffice realmente acepta

LibreOffice acepta una regla de valor calcext solo cuando el operador de comparación es parte del texto del valor, como >3 o between(1,10), y una regla de fórmula solo cuando está escrita formula-is(...). Ambos puntos le costaron a HotXLS una versión, porque las escrituras equivocadas producen una regla que importa sin error y después empareja las celdas equivocadas

El primer error fue un atributo calcext:operator junto a calcext:value. Se lee natural, pero está inventado: LibreOffice no conoce ese atributo, así que importaba cada regla de valor como "igual a 0". El segundo fue meter is-true-formula(...), la escritura de style:map, en una condición calcext, que LibreOffice importaba también como comparación de valor de celda con 0. La corrección de fórmulas salió en v2.384.66 y la de valores en v2.384.69:

<!-- Mal: LibreOffice ignora calcext:operator e importa "igual a 0" -->
<calcext:condition calcext:apply-style-name="CF_Hit"
                   calcext:operator="greater-than" calcext:value="100"/>

<!-- Bien: el operador viaja dentro del valor -->
<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"/>

<!-- Bien: las reglas de fórmula usan formula-is, refs relativas ancladas en la celda base -->
<calcext:condition calcext:apply-style-name="CF_Dup"
                   calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])&gt;1)"
                   calcext:base-cell-address=".C1"/>

La celda base es lo que da significado a las referencias relativas. HotXLS ancla cada regla en la celda superior izquierda de su primera área de rango, así que una fórmula escrita para C1 evalúa como C2, C3 y así sucesivamente por el rango, exactamente como lo hace en el formato condicional del propio Excel. La expresión de la regla pasa por el mismo traductor que las fórmulas de celda, así que arrays, uniones, columnas completas y marcadores $ salen en las formas descritas arriba. Del lado Delphi usted agrega reglas exactamente como lo haría para un archivo .xlsx

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Reglas de valor: style:map cell-content()>100 más valor calcext ">100"
  Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR: rojo claro

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

  // Regla de fórmula en sintaxis Excel (separadores por coma, relativa a C1):
  // style:map is-true-formula(...) más calcext formula-is(...)
  Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
  Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: amarillo claro

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

Leer ODS de Excel y LibreOffice de vuelta a Delphi

Cuando HotXLS abre un archivo ODS, su reader acepta ambos dialectos de formato condicional y ambas escrituras calcext, y no cuenta una regla dos veces cuando el archivo la trae en ambas formas. Los archivos reales vienen de tres writers, cada uno con sus propias manías:

• Calcext viejo y nuevo. Los archivos con atributo calcext:operator, incluidos los ODS escritos por HotXLS antes de v2.384.69, todavía pasan por el parseo legacy. Las condiciones de fórmula se reconocen como formula-is(...) o is-true-formula(...)
• La escritura style:map de Excel. Excel prefija las condiciones con of:, como en of:cell-content-is-between(1,10), y omite la celda base en las reglas de valor. Ambas cosas se aceptan
• Celdas vacías. Excel y LibreOffice ambos ponen el map de las celdas vacías en el estilo por defecto de la columna y no en una celda, así que el reader resuelve los estilos por defecto de columna para las celdas repetidas antes de recolectar los maps
• Reconstrucción de regiones. Los maps se recolectan por celda, así que después de leer una hoja el reader fusiona de vuelta en rangos las celdas que comparten la misma condición y celda base, primero a lo largo de cada fila y luego hacia abajo en tramos de columna que coincidan, y descarta cualquier regla ya leída de calcext

La corrección de v2.384.72 concierne a los estilos de número, no a las reglas. Excel 16 y LibreOffice 26.2 ambos escriben el formato General como un estilo de número cuyo elemento number:number no tiene number:decimal-places, típicamente <number:number number:min-integer-digits="1"/>. El reader de HotXLS trataba el conteo faltante como dos decimales fijos, así que cada valor del estilo Default se importaba con 0.00 y 1.5 se mostraba como 1.50. Desde v2.384.72 un elemento de número simple sin decimales, sin decimales mínimos, sin agrupamiento y con a lo sumo un dígito entero mapea a General, y un General solitario deja la celda sin formato de número alguno. El texto alrededor se conserva, como en General" kg", y los números agrupados conservan el mapeo anterior porque Excel no tiene un formato General agrupado

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]; // el indexador Sheets es base 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;

    // Una celda del estilo General de Excel se lee de vuelta sin formato de número
    // desde v2.384.72, en vez de '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

Las fórmulas de las reglas vuelven en sintaxis Excel con separadores por coma, la misma forma que usted le pasaría a AddCondFormatExpression, así que una regla escrita por HotXLS se lee de vuelta como la cadena idéntica. Para el panorama más amplio de lo que la ruta de importación ODS conserva y descarta, vea la guía de round-trip de apertura y guardado ODS de HotXLS; para cómo se expanden al importar las filas repetidas de Excel y LibreOffice, vea filas repetidas de ODS como tramos de altura de fila

¿Cuáles son los límites del interop de formatos condicionales ODS de HotXLS?

El enfoque de markup doble cubre las reglas de comparación de valor y las reglas de fórmula, y hasta ahí llega. Todo lo demás es de un solo lado o no se escribe en absoluto:

• Las escalas de color y barras de datos se escriben solo como elementos calcext, así que LibreOffice las muestra y Excel no
• Los otros tipos de reglas, como icon sets, reglas de texto, top-N, encima del promedio y duplicados, no tienen salida ODS en el writer actual. Una regla de texto normalmente puede replantearse como regla de fórmula, por ejemplo ISNUMBER(SEARCH("late",B2)) sobre B2:B200, que entonces llega a ambas aplicaciones
• Las reglas de columna completa y fila completa como C:C se tienden solo sobre el área de la tabla que realmente se escribe, en vez de sobre las 1,048,576 filas, así que Excel ve estas reglas solo en las celdas que existen en el archivo
• Archivos con solo style:map. Cuando un archivo no tiene bloque calcext, HotXLS interpreta las referencias relativas en las reglas de fórmula desde la esquina superior izquierda del rango reconstruido, no desplazando desde la celda base declarada
• Reglas solapadas de LibreOffice. Cuando una celda está cubierta por varias reglas, LibreOffice escribe sobre ella solo el map de la primera regla. Archivos así no se pueden leer por completo desde style:map solo, que es una razón más para que el reader prefiera calcext cuando ambos existen

El límite de proceso importa más que cualquiera de estos. Los defectos detrás de estas versiones pasaron por round-trips que escribían ODS y lo releían con HotXLS, y algunos también habrían pasado una revisión manual en la aplicación equivocada: las fórmulas de columna completa funcionaban en LibreOffice mientras Excel mostraba #NAME?, y desde v2.384.66 las reglas de fórmula funcionaban en LibreOffice mientras Excel seguía sin mostrar regla alguna hasta v2.384.69. Si el interop ODS es un requisito, la prueba de aceptación es abrir el archivo en Excel y en LibreOffice y comparar lo que cada uno muestra. La misma disciplina aplica a los estilos a los que apuntan las reglas; el artículo de formato condicional y estilos de HotXLS cubre cómo se definen los estilos de resaltado del lado del workbook

Referencia rápida: ODS que ambas aplicaciones leen

• Declare xmlns:of y xmlns:msoxl en la raíz de content.xml, o LibreOffice muestra #VALUE! para cada fórmula (HotXLS desde v2.384.56)
• Escriba las referencias como [.A1], conserve cada $, y escriba columnas y filas completas como [.A:.A] y [.1:.1] (desde v2.384.55 y v2.384.65)
• Use ; para argumentos, ~ para uniones de referencias, y | entre filas de arrays inline
• Escriba cada regla de valor o de fórmula como un <style:map> en el estilo de cada celda cubierta para Excel, y como una condición calcext para LibreOffice (desde v2.384.69)
• En calcext, ponga el operador en el valor (>3, between(1,10)) y escriba las reglas de fórmula formula-is(...) con una celda base (desde v2.384.66 y v2.384.69)
• Espere un estilo de número General sin number:decimal-places al importar; HotXLS lo lee como General desde v2.384.72
• Verifique cada perfil de exportación nuevo abriendo el archivo tanto en Excel como en LibreOffice, jamás en solo uno de ellos

HotXLS es una librería de hojas de cálculo nativa Delphi y C++Builder que lee y escribe XLS, XLSX y ODS sin Excel ni LibreOffice instalados; el código fuente completo, la lista de funciones y el licenciamiento están en la página del componente de hojas de cálculo HotXLS para Delphi