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

Para producir un archivo ODS que Excel y 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 <style:map> en el estilo de cada celda cubierta, que es la única forma que Excel 16 lee, y como 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 frase es la lección detrás de seis versiones de HotXLS entre v2.384.55 y v2.384.72. Cada arreglo empezó con un archivo que HotXLS escribió, releyó a la perfección, y una de las dos aplicaciones de destino leyó mal. Lo que sigue es lo que cada aplicación acepta realmente, el marcado 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 ortografía legal, LibreOffice añade encima su propio namespace de extensión, y cada consumidor elige el subconjunto que implementa. Un escritor probado contra un solo consumidor convergerá encantado en marcado que el otro lee mal en silencio

Ninguna aplicación reporta un error. LibreOffice muestra #VALUE! en las celdas cuyas fórmulas no pudo parsear; Excel abre el libro 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 escritor que hace la ida y vuelta de su propia salida jamás ve nada de esto. HotXLS cayó exactamente en esa trampa con el namespace de fórmulas: su lector casaba el prefijo of: como texto plano, así que toda ida y vuelta consigo mismo pasaba mientras LibreOffice mostraba #VALUE! en cada celda con fórmula

Característica	Excel 16 lee	LibreOffice 26.2 lee
Columna entera escrita como A:A	Malinterpretada como A:(A)	Tolerada
Columna entera 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í, los 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: de 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 las 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álida, según se define en OpenDocument 1.3 Parte 4. Las trampas están en los sitios donde la sintaxis Excel y OpenFormula se parecen pero no son lo mismo:

• 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 escritor de HotXLS soltaba cada $, así que las referencias absolutas volvían relativas y solo salían mal cuando alguien copiaba la celda
• Columnas y filas enteras deben usar la forma entre corchetes [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Un of:=SUM(A:A) desnudo 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
• Argumentos de función se separan con ;, no con ,
• Uniones de referencias usan el operador ~: el Excel AREAS((A1,B2)) se convierte en AREAS(([.A1]~[.B2])). Traducir esa coma a ; en su lugar convierte un argumento de unión en dos argumentos
• Arrays en línea separan columnas con ; y filas con |: el Excel {1,2;3,4} se convierte en {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 escritor de HotXLS lleva una pila de paréntesis mientras traduce: un ( directamente tras 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 columna de array. Con eso y el arreglo del namespace, LibreOffice 26.2 evaluó correctamente las ocho fórmulas de prueba de arrays y uniones, incluidas INDEX y AREAS sobre uniones

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;

    // Escrito como of:=SUM([.A:.A]) desde v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Escrito como of:=[.A1]*[.$B$1]; los marcadores $ 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 a msoxl:= con el texto Excel sin cambios, razón por la que la declaración msoxl también importa. En el escritor actual ese camino 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 propia ida y vuelta mantiene la expresión intacta, pero cómo las trate otra aplicación queda fuera del control del escritor. 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 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 porta al revés. calcext es el namespace de extensión de LibreOffice, no 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 de ninguna clase. HotXLS escribe ahora ambas formas. La mitad style:map usa la gramática de condiciones del esquema OpenDocument (ODF 1.3 Parte 3), con las ortografías exactas que Excel 16 y LibreOffice 26.2 producen ambos 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>

La pega del 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 sencillamente no cubre esa celda en Excel. HotXLS copia el estilo de formato existente de cada celda, añade 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 escritor además extiende la tabla escrita hasta el rango de la regla, lo que significa que las filas de cola vacías dentro de una regla se emiten en lugar 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 objetivo

La ortografía calcext que LibreOffice acepta de verdad

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 costaron a HotXLS una versión, porque las ortografías equivocadas producen una regla que importa sin error y luego iguala las celdas equivocadas

El primer error fue un atributo calcext:operator junto a calcext:value. Se lee natural, pero es un invento: LibreOffice no conoce ese atributo, así que importaba cada regla de valor como "igual a 0". El segundo fue meter is-true-formula(...), la ortografía de style:map, en una condición calcext, que LibreOffice importaba también como comparación de valor de celda con 0. El arreglo de fórmulas llegó en v2.384.66 y el 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 hace 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 enteras y marcadores $ salen en las formas descritas arriba. Por el lado Delphi añade reglas exactamente igual que 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 calcext value ">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 en Delphi

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

• calcext viejo y nuevo. Los archivos con un atributo calcext:operator, incluidos los ODS escritos por HotXLS antes de v2.384.69, siguen pasando por el parseo heredado. Las condiciones de fórmula se reconocen como formula-is(...) o is-true-formula(...)
• La ortografía 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 ponen ambos el map de las celdas vacías en el estilo por defecto de la columna en lugar de en una celda, así que el lector resuelve los estilos por defecto de columna para celdas repetidas antes de recolectar los maps
• Reconstrucción de regiones. Los maps se recolectan por celda, así que tras leer una hoja el lector 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 por tramos de columna coincidentes, y descarta cualquier regla ya leída de calcext

El arreglo de v2.384.72 concierne a los estilos de número, no a las reglas. Excel 16 y LibreOffice 26.2 escriben ambos el formato General como un estilo de número cuyo elemento number:number no lleva number:decimal-places, típicamente <number:number number:min-integer-digits="1"/>. El lector de HotXLS trataba la cuenta ausente como dos decimales fijos, así que todo valor del estilo Default importaba con 0.00 y 1.5 se mostraba como 1.50. Desde v2.384.72 un elemento de número plano sin decimales, sin mínimo de decimales, sin agrupación y con como mucho 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 de Sheets es basado en 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 lugar 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 pasaría a AddCondFormatExpression, así que una regla escrita por HotXLS se lee de vuelta como la cadena idéntica. Para el cuadro más amplio de lo que el camino de importación ODS conserva y descarta, vea la guía de ida y vuelta de apertura y guardado ODS de HotXLS; para cómo se expanden al importar las filas repetidas de Excel y LibreOffice, vea filas repetidas ODS como tramos de altura de fila

¿Cuáles son los límites del interop de formato condicional ODS de HotXLS?

El enfoque de doble marcado 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:

• Escalas de color y barras de datos se escriben solo como elementos calcext, así que LibreOffice las muestra y Excel no
• Otros tipos de regla, como icon sets, reglas de texto, top-N, sobre la media y de duplicados, no tienen salida ODS en el escritor actual. Una regla de texto normalmente puede reformularse como regla de fórmula, por ejemplo ISNUMBER(SEARCH("late",B2)) sobre B2:B200, que entonces llega a ambas aplicaciones
• Reglas de columna entera y fila entera como C:C se tienden solo sobre el área de tabla realmente escrita, en lugar 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 en 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 lector 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 idas y vueltas que escribían ODS y lo releían con HotXLS, y algunos también habrían pasado una comprobación manual en la aplicación equivocada: las fórmulas de columna entera 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 por el lado del libro

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 referencias como [.A1], conserve cada $, y escriba columnas y filas enteras 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 en línea
• Escriba cada regla de valor o de fórmula como <style:map> en el estilo de cada celda cubierta para Excel, y como 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 en Excel y en LibreOffice, jamás en solo uno de ellos

HotXLS es una biblioteca de hojas de cálculo nativa para Delphi y C++Builder que lee y escribe XLS, XLSX y ODS sin Excel ni LibreOffice instalados; el fuente completo, la lista de características y las licencias están en la página del componente de hojas de cálculo Delphi HotXLS