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 por trás de seis releases do HotXLS entre a v2.384.55 e a v2.384.72. Cada correção começou com um arquivo que o HotXLS gravava, relia perfeitamente, e um dos dois aplicativos alvo lia errado. O que segue é o que cada aplicativo realmente aceita, a marcação que satisfaz os dois, 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

Nenhum dos aplicativos 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 da própria saída nunca vê nada disso. O HotXLS caiu exatamente nessa armadilha com o namespace de fórmula: o reader dele casava o prefixo of: como texto puro, então todo round trip consigo mesmo passava enquanto o LibreOffice mostrava #VALUE! em cada 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: em 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. Antes da v2.384.56 a raiz do content.xml usava os dois prefixos sem declará-los, e o LibreOffice não conseguia identificar a gramática de fórmula de forma alguma

<!-- Antes da v2.384.56: prefixo usado, nunca declarado; 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"/>

<!-- Desde a v2.384.56: 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 consertado, a expressão em si ainda precisa ser OpenFormula válida, como definido no OpenDocument 1.3 Parte 4. As armadilhas são os lugares em que a sintaxe do Excel e a do OpenFormula parecem parecidas mas não são iguais:

• Referências de célula vêm entre colchetes com ponto, e os marcadores $ fazem parte da referência: [.$A$1] e [.A$1:.$B2] são OpenFormula válido. Antes da v2.384.55 o writer do HotXLS descartava todo $, então referências absolutas voltavam relativas e só davam errado quando alguém copiava a célula
• Colunas e linhas inteiras devem usar a forma entre colchetes [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Um of:=SUM(A:A) nu é tolerado pelo LibreOffice, mas o Excel 16 o abre como =SUM(A:(A)) com #NAME?, e transforma referências de linha e $A:$B na constante 0. O HotXLS grava a forma entre colchetes desde a v2.384.65
• Argumentos de função são separados por ;, não ,
• Uniões de referências usam o operador ~: o Excel AREAS((A1,B2)) vira AREAS(([.A1]~[.B2])). Traduzir essa vírgula para ; em vez disso transforma um argumento de união em dois argumentos
• Arrays inline separam colunas com ; e linhas com |: o Excel {1,2;3,4} vira {1;2|3;4}. Antes da v2.384.55 o HotXLS produzia {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. Desde a v2.384.55 o writer do HotXLS mantém 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 a correção do namespace, o LibreOffice 26.2 avaliou corretamente todas as oito fórmulas de sonda de array e união, INDEX e AREAS sobre uniões incluídas

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;

    // Gravado como of:=SUM([.A:.A]) desde a v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Gravado como of:=[.A1]*[.$B$1]; os marcadores $ sobrevivem desde a v2.384.55
    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 para msoxl:= com o texto Excel inalterado, e é por isso que a declaração msoxl também importa. No writer atual esse caminho inclui referências qualificadas por planilha como Sheet2!A1 e referências estruturadas de tabela. O HotXLS lê fórmulas msoxl: de volta na importação, então o próprio round trip dele mantém a expressão intacta, mas como outro aplicativo as trata está fora do controle do writer. Se uma fórmula de que os seus consumidores dependem sai com o prefixo msoxl:, abra o arquivo nos dois aplicativos antes de entregá-lo

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

Antes da v2.384.69 o HotXLS gravava só calcext, então um arquivo ODS com destaque perfeitamente bom abria no Excel sem regras de valor e sem regras de fórmula nenhuma. O HotXLS agora grava as duas formas. A metade style:map usa a gramática de condição do esquema OpenDocument (ODF 1.3 Parte 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 nos estilos de célula, então é por célula. Toda célula no intervalo da regra precisa carregar um estilo que contenha o map, células vazias incluídas, ou a regra simplesmente não cobre essa célula no Excel. O HotXLS copia o estilo de formatação existente de cada célula, anexa os maps, e deduplica styles de suporte pelo par de estilo original e texto do map, então um intervalo de 500 células com formatação idêntica ainda produz um estilo. O writer também estende a tabela gravada até o intervalo da regra, o que significa que linhas vazias no fim dentro de uma regra são emitidas em vez de descartadas. Desde a v2.384.69 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 aceita uma regra de valor calcext só quando o operador de comparação faz parte do texto do valor, como >3 ou between(1,10), e uma regra de fórmula só quando ela é escrita formula-is(...). Os dois pontos custaram um release ao HotXLS, porque as grafias erradas produzem uma regra que importa sem erro e depois casa as células erradas

O primeiro erro foi um atributo calcext:operator ao lado do calcext:value. Ele parece natural, mas é inventado: o LibreOffice não conhece esse atributo, então importava toda regra de valor como "igual a 0". O segundo foi pôr is-true-formula(...), a grafia do style:map, numa condição calcext, que o LibreOffice importava como uma comparação de valor de célula com 0 também. A correção de fórmula saiu na v2.384.66 e a de valor na v2.384.69:

<!-- 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"/>

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 antigo e novo. Arquivos com um atributo calcext:operator, incluindo ODS gravados pelo HotXLS antes da v2.384.69, 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 as condições com of:, como em of:cell-content-is-between(1,10), e omite a célula base nas regras de valor. Ambos são aceitos
• Células vazias. O Excel e o LibreOffice põem o map das células vazias no estilo padrão da coluna em vez de numa célula, então o reader resolve styles padrão de coluna para células repetidas antes de coletar os maps
• Reconstrução de região. Os maps são coletados por célula, então depois que uma planilha é lida o reader funde células que compartilham a mesma condição e célula base de volta em intervalos, primeiro ao longo de cada linha e depois descendo pelos trechos de coluna que casam, e descarta qualquer regra já lida do calcext

A correção da v2.384.72 trata de number styles, não de regras. O Excel 16 e o LibreOffice 26.2 gravam 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"/>. O reader do HotXLS tratava a contagem ausente como duas casas decimais fixas, então todo valor no estilo Default importava com 0.00 e 1.5 aparecia como 1.50. Desde a v2.384.72 um elemento de número puro sem casas decimais, sem mínimo de decimais, sem agrupamento e com no máximo um dígito inteiro mapeia para General, e um General solitário deixa a célula sem formato de número algum. Texto em volta é mantido, como em General" kg", e números agrupados mantêm o mapeamento anterior porque o Excel não tem um 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 Sheets é base um
    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 formato de número
    // desde a v2.384.72, em vez de '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 são os limites da interop de formato condicional ODS do HotXLS?

A abordagem de marcação dupla cobre regras de comparação de valor e regras de fórmula, e para aí. Todo o resto é de um lado só ou não é gravado:

• Color scales e data bars são gravados só como elementos calcext, então o LibreOffice os mostra e o Excel não
• Outros tipos de regra, como icon sets, regras de texto, top-N, acima da média e duplicatas, não têm saída ODS no writer atual. Uma regra de texto normalmente pode ser reescrita como regra de fórmula, por exemplo ISNUMBER(SEARCH("late",B2)) sobre B2:B200, que então alcança os dois aplicativos
• Regras de coluna e linha inteiras como C:C são postas só sobre a área da tabela que efetivamente está gravada, 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 com só style:map. Quando um arquivo não tem bloco calcext, o HotXLS interpreta referências relativas em regras de fórmula a partir do canto superior esquerdo do intervalo reconstruído, e não deslocando da célula base declarada
• Regras sobrepostas do LibreOffice. Quando uma célula é coberta por várias regras, o LibreOffice grava nela só o map da primeira regra. Arquivos assim não podem ser lidos por completo apenas do style:map, o que é mais um motivo para o reader preferir o calcext quando ambos existem

O limite de processo importa mais que qualquer um destes. Os defeitos por trás desses releases passaram por round trips que gravavam ODS e reliam com o HotXLS, e alguns também teriam passado numa checagem manual no aplicativo errado: fórmulas de coluna inteira funcionavam no LibreOffice enquanto o Excel mostrava #NAME?, e da v2.384.66 as regras de fórmula funcionavam no LibreOffice enquanto o Excel ainda não mostrava regra nenhuma até a v2.384.69. Se interop ODS é um 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 styles de destaque são definidos do 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 (HotXLS desde a v2.384.56)
• Escreva referências como [.A1], mantenha todo $, e escreva colunas e linhas inteiras como [.A:.A] e [.1:.1] (desde a v2.384.55 e v2.384.65)
• Use ; para argumentos, ~ para uniões de referências, e | entre linhas de array inline
• Grave 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 (desde a v2.384.69)
• No calcext, ponha o operador no valor (>3, between(1,10)) e escreva regras de fórmula formula-is(...) com uma célula base (desde a v2.384.66 e v2.384.69)
• Espere um number style General sem number:decimal-places na importação; o HotXLS o lê como General desde a v2.384.72
• Valide cada novo perfil de exportação 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