Artigo Técnico

Interop ODS no HotXLS: fórmulas e regras que o Excel lê

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

FuncionalidadeO que o Excel 16 lêO que o LibreOffice 26.2 lê
Coluna inteira gravada como A:ALida como A:(A)Tolerada
Coluna inteira gravada como [.A:.A]SimSim
Formatos condicionais em <style:map>Sim, a única forma que ele lêIgnorados quando calcext está presente
Formatos condicionais em calcext:conditional-formatsIgnoradosSim, preferidos
Regra de valor calcext com atributo calcext:operatorIgnoradaImportada como "igual a 0"
Regra de fórmula calcext escrita is-true-formula(...)IgnoradaImportada 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]. Um of:=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:$B na 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 ~: o AREAS((A1,B2)) do Excel vira AREAS(([.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

Diagrama do HotXLS da pilha de parênteses que traduz vírgulas do Excel para OpenFormula: um parêntese logo depois de um nome abre uma chamada de função cujas vírgulas viram ponto e vírgula, qualquer outro parêntese é agrupamento cujas vírgulas viram o operador de união til, e vírgulas dentro de chaves são separadores de coluna de array, como em AREAS da união de A1 e B2
A vírgula carrega três significados na sintaxe do Excel, e só a pilha de parênteses em execução os distingue; traduzir uma vírgula de união para ponto e vírgula e um argumento silenciosamente vira dois
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

Diagrama de canal duplo do HotXLS para formatos condicionais ODS: toda regra de valor ou fórmula é gravada como um style map no estilo de cada célula coberta, a única forma que o Excel 16 lê, e como um bloco de formatos condicionais calcext com o operador dentro do valor, a forma que o LibreOffice prefere, enquanto cada aplicativo ignora em silêncio a grafia do outro
O Excel lê style maps e ignora calcext, o LibreOffice prefere calcext e descarta os maps, e nenhum mostra erro; gravar as duas grafias numa única chamada do HotXLS é o único jeito de o arquivo passar nos dois

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()&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>

<!-- 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])&gt;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="&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"/>

<!-- 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])&gt;1)"
                   calcext:base-cell-address=".C1"/>
Diagrama do HotXLS contrastando grafias erradas e certas de condições calcext: um atributo calcext operator é inventado e importa toda value rule como igual a 0, o operador pertence ao interior do valor como em maior que 100 ou entre 1 e 10, e formula rules precisam dizer formula-is ancorada numa célula base em vez da grafia is-true-formula do style map
As duas grafias erradas importam sem erro e depois casam as células erradas, uma regra que lê como igual a 0 não destaca nada do que você queria; a grafia certa é o operador no valor e formula-is para expressões

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:operator ainda passam pelo parse legado. Condições de fórmula são reconhecidas tanto como formula-is(...) quanto como is-true-formula(...)
  • A grafia style:map do Excel. O Excel prefixa condições com of:, como em of: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)) sobre B2:B200, que então chega às duas aplicações
  • Regras de coluna inteira e linha inteira como C:C sã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:map sozinho, 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:of e xmlns:msoxl na raiz do content.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 como formula-is(...) com uma célula base
  • Espere um number style General sem number:decimal-places na 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