Para produzir um arquivo ODS que o Excel e o LibreOffice leiam corretamente, o HotXLS grava toda fórmula em sintaxe OpenFormula sob um namespace of: declarado, e grava toda formatação condicional de valor ou fórmula duas vezes: como um <style:map> no estilo de cada célula coberta, que é a única forma que o Excel 16 lê, e como um bloco calcext:conditional-formats, que é a forma em que o LibreOffice confia. Cada aplicativo ignora a metade destinada ao outro, então um arquivo que aparece correto num deles não prova nada sobre o outro
Essa última frase é a lição inteira do interop ODS: um arquivo que um writer lê de volta perfeitamente ainda pode ser um que qualquer uma das aplicações alvo interprete errado. O que segue é o que cada aplicação de fato aceita, a marcação que satisfaz as duas, e as chamadas de API do HotXLS que a produzem a partir do Delphi
Por que um arquivo ODS parece bom num aplicativo e quebrado no outro?
Um arquivo ODS parece bom num aplicativo e quebrado no outro porque o Excel e o LibreOffice leem partes diferentes do mesmo pacote. O OpenDocument dá a fórmulas e formatos condicionais mais de uma grafia legal, o LibreOffice adiciona o próprio namespace de extensão dele por cima, e cada consumidor escolhe o subconjunto que implementa. Um writer testado contra um só consumidor converge feliz para marcação que o outro lê errado em silêncio
Nenhuma das duas aplicações reporta erro. O LibreOffice mostra #VALUE! nas células cujas fórmulas não conseguiu parsear; o Excel abre o workbook com os formatos condicionais simplesmente ausentes, ou com uma fórmula reescrita em algo que avalia para #NAME? ou a constante 0. Um writer que faz round trip do próprio output nunca vê nada disso. O namespace de fórmulas é o caso clássico: um reader que casa o prefixo of: como texto plain passa em todo round trip consigo mesmo enquanto o LibreOffice mostra #VALUE! em toda célula de fórmula
| Funcionalidade | O que o Excel 16 lê | O que o LibreOffice 26.2 lê |
|---|---|---|
Coluna inteira gravada como A:A | Lida como A:(A) | Tolerada |
Coluna inteira gravada como [.A:.A] | Sim | Sim |
Formatos condicionais em <style:map> | Sim, a única forma que ele lê | Ignorados quando calcext está presente |
Formatos condicionais em calcext:conditional-formats | Ignorados | Sim, preferidos |
Regra de valor calcext com atributo calcext:operator | Ignorada | Importada como "igual a 0" |
Regra de fórmula calcext escrita is-true-formula(...) | Ignorada | Importada como comparação de valor com 0 |
OpenFormula em ODS: declare o namespace, depois acerte a sintaxe
Uma célula de fórmula em ODS só é legível pelo LibreOffice quando o prefixo of: no table:formula resolve para um namespace XML declarado. O prefixo não é decoração. of: mapeia para urn:oasis:names:tc:opendocument:xmlns:of:1.2, e msoxl:, o prefixo que o HotXLS usa para fórmulas que o tradutor OpenFormula dele não modela, mapeia para http://schemas.microsoft.com/office/excel/formula. Use qualquer um dos prefixos sem declará-lo na raiz do content.xml e o LibreOffice não consegue identificar a gramática da fórmula de forma alguma
<!-- Errado: prefixo usado, nunca declarado; o LibreOffice mostra #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"/>
<!-- Certo: os dois namespaces de fórmula declarados na raiz -->
<office:document-content
xmlns:of="urn:oasis:names:tc:opendocument:xmlns:of:1.2"
xmlns:msoxl="http://schemas.microsoft.com/office/excel/formula" ...>
Com o namespace declarado, a expressão em si ainda precisa ser OpenFormula válida, como definido no OpenDocument 1.3 Part 4. As armadilhas são os lugares em que a sintaxe do Excel e a OpenFormula parecem parecidas mas não são a mesma:
- Referências de célula vêm entre colchetes e com ponto na frente, e os marcadores
$fazem parte da referência:[.$A$1]e[.A$1:.$B2]são OpenFormula válidos. Um tradutor que descarta o$transforma referências absolutas em relativas, e o dano só aparece quando alguém copia a célula - Colunas e linhas inteiras precisam da forma com colchetes
[.A:.A],[.$A:.$B],[.1:.1],[.$1:.$2]. Umof:=SUM(A:A)nu é tolerado pelo LibreOffice, mas o Excel 16 abre como=SUM(A:(A))com#NAME?, e transforma referências de linha e$A:$Bna constante 0. O HotXLS escreve a forma com colchetes - Argumentos de função são separados por
;, não por, - Unions de referência usam o operador
~: oAREAS((A1,B2))do Excel viraAREAS(([.A1]~[.B2])). Traduzir essa vírgula para;em vez disso transforma um argumento union em dois argumentos - Arrays inline separam colunas com
;e linhas com|: o{1,2;3,4}do Excel vira{1;2|3;4}. Perca o separador de linha e você leva{1;2;3;4}, uma única linha de quatro valores
A vírgula é a parte difícil, porque um caractere do Excel carrega três significados. O writer do HotXLS acompanha uma pilha de parênteses enquanto traduz: um ( direto depois de um nome abre uma chamada de função, cujas vírgulas viram ;; qualquer outro ( é um parêntese de agrupamento, cujas vírgulas viram ~; e vírgulas dentro de {} são separadores de coluna de array. Com isso e o namespace declarado, o LibreOffice 26.2 avalia fórmulas de array e union corretamente, incluindo INDEX e AREAS sobre unions
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;
// Grava como of:=SUM([.A:.A])
Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
// Grava como of:=[.A1]*[.$B$1]; os marcadores $ sobrevivem
Sheet.Cells[2, 4].Formula := 'A1*$B$1';
Book.SaveAsODS('orders.ods');
finally
Book.Free;
end;
end;
Fórmulas que o tradutor não modela caem no msoxl:= com o texto do Excel sem mudança, e é por isso que a declaração msoxl também importa. Esse caminho inclui referências qualificadas por sheet como Sheet2!A1 e structured table references. O HotXLS lê fórmulas msoxl: de volta na importação, então o round trip dele mantém a expressão intacta, mas como outra aplicação as trata está fora do controle do writer. Se uma fórmula de que seus consumidores dependem sai com o prefixo msoxl:, abra o arquivo nas duas aplicações antes de embarcar
Por que o Excel não vê formatos condicionais gravados só como calcext?
O Excel 16 não vê formatos condicionais calcext porque lê os formatos condicionais de ODS exclusivamente dos filhos <style:map> dos estilos de célula e ignora o bloco calcext:conditional-formats por completo. O experimento que resolve a questão é curto: pegue um ODS salvo pelo LibreOffice, apague os elementos style:map, e o Excel lê zero regras; apague o bloco calcext em vez disso, e o Excel ainda lê todas. O LibreOffice se comporta ao contrário. O calcext é o namespace de extensão do LibreOffice, não faz parte do padrão ODF, e quando uma regra calcext está presente o LibreOffice a toma e ignora o style:map
Escreva só calcext e um arquivo ODS com highlight perfeitamente bom abre no Excel sem nenhuma regra de valor e nenhuma regra de fórmula, então o HotXLS escreve as duas formas. A metade style:map usa a gramática de condições do schema OpenDocument (ODF 1.3 Part 3), com as grafias exatas que o Excel 16 e o LibreOffice 26.2 produzem quando salvam ODS:
<!-- Simplificado. Style de suporte para cada célula de A1:A50 (duas regras 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>
<!-- Style de suporte para cada célula de C1:C50 (uma regra 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>
A pegadinha do style:map é que ele vive em estilos de célula, então é por célula. Toda célula no range da regra precisa carregar um estilo que tenha o map, células vazias incluídas, senão a regra simplesmente não cobre aquela célula no Excel. O HotXLS copia o estilo de formatação existente de cada célula, anexa os maps e deduplica estilos de suporte pelo par de estilo original e texto do map, então um range de 500 células com formatação idêntica ainda produz um estilo. O writer também estende a tabela escrita até o range da regra, o que significa que linhas de cauda vazias dentro de uma regra são emitidas em vez de descartadas. O styles.xml também carrega um estilo de célula Default vazio, então style:apply-style-name="Default" sempre tem um alvo
A grafia calcext que o LibreOffice realmente aceita
O LibreOffice só aceita uma calcext value rule quando o operador de comparação faz parte do texto de valor, como >3 ou between(1,10), e uma formula rule só quando é grafada como formula-is(...). Os dois pontos são fáceis de errar, porque as grafias erradas produzem uma regra que importa sem erro e depois casa as células erradas
A primeira armadilha é um atributo calcext:operator ao lado do calcext:value. Ele lê naturalmente, mas é inventado: o LibreOffice não conhece esse atributo, então importa toda value rule como "igual a 0". A segunda é pôr is-true-formula(...), a grafia do style:map, numa condição calcext, que o LibreOffice importa como comparação de valor de célula com 0 também:
<!-- Errado: LibreOffice ignora calcext:operator e importa "igual a 0" -->
<calcext:condition calcext:apply-style-name="CF_Hit"
calcext:operator="greater-than" calcext:value="100"/>
<!-- Certo: o operador viaja dentro do 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"/>
<!-- Certo: regras de fórmula usam formula-is, refs relativas ancoradas na célula base -->
<calcext:condition calcext:apply-style-name="CF_Dup"
calcext:value="formula-is(COUNTIF([.$C:.$C];[.C1])>1)"
calcext:base-cell-address=".C1"/>
A célula base é o que dá significado às referências relativas. O HotXLS ancora toda regra na célula do canto superior esquerdo da primeira área de intervalo dela, então uma fórmula escrita para C1 avalia como C2, C3 e assim por diante pelo intervalo, exatamente como na formatação condicional do próprio Excel. A expressão da regra passa pelo mesmo tradutor das fórmulas de célula, então arrays, uniões, colunas inteiras e marcadores $ saem nas formas descritas acima. Do lado Delphi você adiciona regras exatamente como faria num arquivo .xlsx
uses
lxHandleX;
procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
Idx: Integer;
Opts: TODSExportOptions;
begin
// Regras de valor: style:map cell-content()>100 mais calcext value ">100"
Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpGreaterThan, '100');
Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00C0C0FF); // BGR: vermelho claro
Idx := Sheet.AddConditionalFormat('A1:A50', xlsxCfOpBetween, '1', '10');
Sheet.ConditionalFormats[Idx].Style.SetFontBold(True);
// Regra de fórmula em sintaxe Excel (separadores vírgula, relativo a C1):
// style:map is-true-formula(...) mais calcext formula-is(...)
Idx := Sheet.AddCondFormatExpression('C1:C50', 'COUNTIF($C:$C,C1)>1');
Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($00CCFFFF); // BGR: amarelo claro
Opts := TODSExportOptions.Create;
try
Opts.Generator := 'OrderExport 3.1';
Book.SaveAsODS('orders.ods', Opts);
finally
Opts.Free;
end;
end;
Ler ODS do Excel e do LibreOffice de volta para o Delphi
Quando o HotXLS abre um arquivo ODS, o reader dele aceita os dois dialetos de formato condicional e as duas grafias calcext, e não conta uma regra duas vezes quando o arquivo a traz nas duas formas. Arquivos reais vêm de três writers, cada um com os próprios hábitos:
- Calcext velho e novo. Arquivos com um atributo
calcext:operatorainda passam pelo parse legado. Condições de fórmula são reconhecidas tanto comoformula-is(...)quanto comois-true-formula(...) - A grafia style:map do Excel. O Excel prefixa condições com
of:, como emof:cell-content-is-between(1,10), e omite a célula base em value rules. Ambos são aceitos - Células vazias. O Excel e o LibreOffice põem o map de células vazias no estilo default da coluna em vez de numa célula, então o reader resolve estilos default de coluna para células repetidas antes de coletar maps
- Reconstrução de regiões. Maps são coletados por célula, então depois que uma sheet é lida o reader mescla células que compartilham a mesma condição e célula base de volta em ranges, primeiro por cada linha e depois descendo por spans de coluna correspondentes, e descarta qualquer regra já lida do calcext
Number styles precisam do mesmo cuidado que regras. O Excel 16 e o LibreOffice 26.2 escrevem o formato General como um number style cujo elemento number:number não tem number:decimal-places, tipicamente <number:number number:min-integer-digits="1"/>. Leia a contagem ausente como dois decimais fixos e todo valor no estilo Default importa com 0.00, então 1.5 aparece como 1.50. O HotXLS mapeia um elemento number plain sem casas decimais, sem mínimos de decimais, sem grouping e com no máximo um dígito inteiro para General, e um General solitário deixa a célula sem number format algum. Texto em volta é mantido, como em General" kg", e números agrupados não são mapeados para General porque o Excel não tem 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]; // o indexador de Sheets é 1-based
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;
// Uma célula no estilo General do Excel lê de volta sem number format,
// não '0.00'
Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
finally
Book.Free;
end;
end;
Fórmulas de regras voltam em sintaxe Excel com separadores vírgula, a mesma forma que você passaria ao AddCondFormatExpression, então uma regra gravada pelo HotXLS relê como a string idêntica. Para o quadro mais amplo do que o caminho de importação ODS mantém e descarta, veja o guia de round-trip de abrir e salvar ODS do HotXLS; para como linhas repetidas do Excel e do LibreOffice são expandidas na importação, veja linhas repetidas ODS como runs de altura de linha
Quais kinds de formato condicional chegam às duas aplicações?
A abordagem de marcação dupla cobre regras de comparação de valor e regras de fórmula, os dois kinds que as duas aplicações leem. Os outros kinds seguem regras simples:
- Color scales e data bars são escritos como elementos calcext, a forma que o LibreOffice lê, então planeje o LibreOffice como o consumidor ODS deles
- Outros kinds de regra, como icon sets, regras de texto, top-N, acima da média e duplicados, o melhor é reescrevê-las como formula rules antes de um export ODS sempre que a condição permitir. Uma regra de texto, por exemplo, vira
ISNUMBER(SEARCH("late",B2))sobreB2:B200, que então chega às duas aplicações - Regras de coluna inteira e linha inteira como
C:Csão dispostas só sobre a área de tabela que de fato é escrita, em vez de sobre todas as 1.048.576 linhas, então o Excel vê essas regras só nas células que existem no arquivo - Arquivos só com style:map. Quando um arquivo não tem bloco calcext, o HotXLS interpreta referências relativas em formula rules a partir do canto superior esquerdo do range reconstruído, não deslocando da célula base declarada
- Regras sobrepostas vindas do LibreOffice. Quando uma célula é coberta por várias regras, o LibreOffice escreve nela só o map da primeira regra. Arquivos assim não podem ser lidos por completo a partir de
style:mapsozinho, o que é mais um motivo para o reader preferir calcext quando ambos existem
A regra de processo importa mais que qualquer uma destas. Toda armadilha deste artigo passa por um round trip que escreve ODS e o lê de volta com a mesma library, e várias também passam numa checagem manual na aplicação errada: fórmulas de coluna inteira funcionam no LibreOffice enquanto o Excel mostra #NAME?, e formula rules escritas só como calcext funcionam no LibreOffice enquanto o Excel não mostra regra nenhuma. Se interop ODS é requisito, o teste de aceitação é abrir o arquivo no Excel e no LibreOffice e comparar o que cada um mostra. A mesma disciplina vale para os styles para os quais as regras apontam; o artigo de formatação condicional e styles do HotXLS cobre como highlight styles são definidos no lado do workbook
Referência rápida: ODS que os dois aplicativos leem
- Declare
xmlns:ofexmlns:msoxlna raiz docontent.xml, ou o LibreOffice mostra#VALUE!para toda fórmula - Escreva referências como
[.A1], mantenha todo$, e escreva colunas e linhas inteiras como[.A:.A]e[.1:.1] - Use
;para argumentos,~para unions de referência e|entre linhas de array inline - Escreva cada regra de valor ou fórmula como um
<style:map>no estilo de cada célula coberta para o Excel, e como uma condição calcext para o LibreOffice - No calcext, ponha o operador no valor (
>3,between(1,10)) e grafe formula rules comoformula-is(...)com uma célula base - Espere um number style General sem
number:decimal-placesna importação; o HotXLS lê como General - Verifique todo perfil novo de export abrindo o arquivo no Excel e no LibreOffice, nunca em só um deles
O HotXLS é uma biblioteca de planilha nativa Delphi e C++Builder que lê e grava XLS, XLSX e ODS sem Excel ou LibreOffice instalados; código-fonte completo, lista de funcionalidades e licenciamento estão na página do componente de planilha HotXLS para Delphi