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 toda la lección del interop ODS: un archivo que un escritor relee a la perfección puede seguir siendo uno que cualquiera de las dos aplicaciones de destino lee 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. El namespace de fórmulas es el caso de manual: un lector que casa el prefijo of: como texto plano pasa todas las idas y vueltas consigo mismo mientras LibreOffice muestra #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. Use cualquiera de los dos prefijos sin declararlo en la raíz de content.xml y LibreOffice no podrá identificar la gramática de fórmulas en absoluto
<!-- Mal: 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"/>
<!-- Bien: 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 declarado, 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. Un traductor que suelte los$convierte referencias absolutas en relativas, y el daño solo se ve cuando alguien copia la celda - Columnas y filas enteras deben usar la forma entre corchetes
[.A:.A],[.$A:.$B],[.1:.1],[.$1:.$2]. Unof:=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:$Ben la constante 0. HotXLS escribe la forma entre corchetes - Argumentos de función se separan con
;, no con, - Uniones de referencias usan el operador
~: el ExcelAREAS((A1,B2))se convierte enAREAS(([.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}. Perder el separador de filas da{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. 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 namespace declarado, LibreOffice 26.2 evalúa correctamente las fórmulas 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])
Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
// Escrito como of:=[.A1]*[.$B$1]; los marcadores $ sobreviven
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. 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
Escriba solo calcext y un archivo ODS con un resaltado perfectamente bueno se abre en Excel sin reglas de valor ni reglas de fórmula de ninguna clase, así que HotXLS escribe 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()>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])>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. 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 son fáciles de estropear, porque las ortografías equivocadas producen una regla que importa sin error y luego iguala las celdas equivocadas
La primera trampa es un atributo calcext:operator junto a calcext:value. Se lee natural, pero es un invento: LibreOffice no conoce ese atributo, así que importa cada regla de valor como "igual a 0". La segunda es meter is-true-formula(...), la ortografía de style:map, en una condición calcext, que LibreOffice importa también como comparación de valor de celda con 0:
<!-- 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=">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])>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:operatorsiguen pasando por el parseo heredado. Las condiciones de fórmula se reconocen comoformula-is(...)ois-true-formula(...) - La ortografía style:map de Excel. Excel prefija las condiciones con
of:, como enof: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
Los estilos de número necesitan el mismo cuidado que 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"/>. Lea la cuenta ausente como dos decimales fijos y todo valor del estilo Default importa con 0.00, así que 1.5 se muestra como 1.50. HotXLS mapea un elemento de número plano sin decimales, sin mínimo de decimales, sin agrupación y con como mucho un dígito entero 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 no se mapean a General 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,
// no '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
¿Qué tipos de formato condicional llegan a ambas aplicaciones?
El enfoque de doble marcado cubre las reglas de comparación de valor y las reglas de fórmula, los dos tipos que ambas aplicaciones leen. Los demás tipos siguen reglas sencillas:
- Escalas de color y barras de datos se escriben como elementos calcext, la forma que lee LibreOffice, así que cuente con LibreOffice como su consumidor ODS
- Los demás tipos de regla, como icon sets, reglas de texto, top-N, sobre la media y de duplicados, conviene replantearlos como reglas de fórmula antes de una exportación ODS siempre que la condición lo permita. Una regla de texto, por ejemplo, se convierte en
ISNUMBER(SEARCH("late",B2))sobreB2:B200, que entonces llega a ambas aplicaciones - Reglas de columna entera y fila entera como
C:Cse 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:mapsolo, que es una razón más para que el lector prefiera calcext cuando ambos existen
La regla de proceso importa más que cualquiera de estas. Cada trampa de este artículo pasa una ida y vuelta que escribe ODS y lo relee con la misma librería, y varias pasan además una comprobación manual en la aplicación equivocada: las fórmulas de columna entera funcionan en LibreOffice mientras Excel muestra #NAME?, y las reglas de fórmula escritas solo como calcext funcionan en LibreOffice mientras Excel no muestra regla alguna. 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:ofyxmlns:msoxlen la raíz decontent.xml, o LibreOffice muestra#VALUE!para cada fórmula - Escriba referencias como
[.A1], conserve cada$, y escriba columnas y filas enteras como[.A:.A]y[.1:.1] - 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 - En calcext, ponga el operador en el valor (
>3,between(1,10)) y escriba las reglas de fórmulaformula-is(...)con una celda base - Espere un estilo de número General sin
number:decimal-placesal importar; HotXLS lo lee como General - 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