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

Para produzir um ficheiro ODS que o Excel e o LibreOffice leiam corretamente, o HotXLS escreve todas as fórmulas em sintaxe OpenFormula sob um namespace of: declarado, e escreve toda a formatação condicional de valor ou de 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 aplicação ignora a metade destinada à outra, por isso um ficheiro que aparece correto numa delas não prova nada sobre a outra

Essa última frase é a lição por trás de seis versões do HotXLS entre a v2.384.55 e a v2.384.72. Cada correção começou com um ficheiro que o HotXLS escrevia, lia de volta perfeitamente, e uma das duas aplicações alvo lia mal. O que se segue é o que cada aplicação realmente aceita, a marcação que satisfaz ambas, e as chamadas à API do HotXLS que a produzem a partir de Delphi

Porque é que um ficheiro ODS parece bem numa aplicação e parte-se na outra?

Um ficheiro ODS parece bem numa aplicação e parte-se na outra 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 acrescenta o seu próprio namespace de extensão por cima, e cada consumidor escolhe o subconjunto que implementa. Um escritor testado contra um único consumidor vai felizmente convergir para marcação que o outro lê mal em silêncio

Nenhuma das aplicações reporta um erro. O LibreOffice mostra #VALUE! em células cujas fórmulas não conseguiu analisar; o Excel abre o livro com os formatos condicionais simplesmente ausentes, ou com uma fórmula reescrita em algo que avalia a #NAME? ou à constante 0. Um escritor que faz round-trip da própria saída nunca vê nada disto. O HotXLS caiu exatamente nessa armadilha com o namespace de fórmulas: o seu leitor casava o prefixo of: como texto simples, por isso todos os round trips consigo próprio passavam enquanto o LibreOffice mostrava #VALUE! em todas as células de fórmula

Funcionalidade	O Excel 16 lê	O LibreOffice 26.2 lê
Coluna inteira escrita como A:A	Mal lida como A:(A)	Tolerado
Coluna inteira escrita como [.A:.A]	Sim	Sim
Formatos condicionais em <style:map>	Sim, a única forma que lê	Ignorado quando calcext está presente
Formatos condicionais em calcext:conditional-formats	Ignorado	Sim, preferido
Regra de valor calcext com um atributo calcext:operator	Ignorado	Importada como "igual a 0"
Regra de fórmula calcext grafada is-true-formula(...)	Ignorado	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. O of: mapeia para urn:oasis:names:tc:opendocument:xmlns:of:1.2, e o msoxl:, o prefixo que o HotXLS usa para fórmulas que o seu tradutor OpenFormula não modela, mapeia para http://schemas.microsoft.com/office/excel/formula. Antes da v2.384.56 a raiz do content.xml usava ambos os prefixos sem os declarar, e o LibreOffice não conseguia identificar a gramática de fórmulas de todo

<!-- Antes da v2.384.56: 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"/>

<!-- Desde a v2.384.56: ambos os 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 corrigido, a expressão em si ainda tem de ser OpenFormula válida, como definido no OpenDocument 1.3 Parte 4. As armadilhas estão nos sítios onde a sintaxe Excel e a OpenFormula parecem semelhantes mas não são o mesmo:

• Referências de células vão entre parênteses retos e com ponto prefixado, e os marcadores $ fazem parte da referência: [.$A$1] e [.A$1:.$B2] são OpenFormula válidos. Antes da v2.384.55 o escritor do HotXLS largava todos os $, por isso referências absolutas voltavam relativas e só saíam mal quando alguém copiava a célula
• Colunas e linhas inteiras têm de usar a forma entre parênteses retos [.A:.A], [.$A:.$B], [.1:.1], [.$1:.$2]. Um of:=SUM(A:A) nu é tolerado pelo LibreOffice, mas o Excel 16 abre-o como =SUM(A:(A)) com #NAME?, e torna referências de linha e $A:$B na constante 0. O HotXLS escreve a forma entre parênteses retos desde a v2.384.65
• Argumentos de funções separam-se por ;, não por ,
• Uniões de referências usam o operador ~: o Excel AREAS((A1,B2)) torna-se AREAS(([.A1]~[.B2])). Traduzir essa vírgula para ; em vez disso transforma um argumento de união em dois argumentos
• Matrizes inline separam colunas por ; e linhas por |: o Excel {1,2;3,4} torna-se {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 único carácter do Excel carrega três significados. Desde a v2.384.55 o escritor do HotXLS segue uma pilha de parênteses enquanto traduz: um ( diretamente a seguir a um nome abre uma chamada de função, cujas vírgulas se tornam ;; qualquer outro ( é um parêntese de agrupamento, cujas vírgulas se tornam ~; e vírgulas dentro de {} são separadores de colunas de matriz. Com isso e a correção do namespace, o LibreOffice 26.2 avaliou corretamente as oito fórmulas de teste de matrizes e uniões, INDEX e AREAS sobre uniões incluídos

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;

    // Gravada como of:=SUM([.A:.A]) desde a v2.384.65
    Sheet.Cells[1, 4].Formula := 'SUM(A:A)';
    // Gravada 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 no fallback msoxl:= com o texto Excel inalterado, razão pela qual a declaração msoxl também interessa. No escritor atual esse caminho inclui referências qualificadas por folha como Sheet2!A1 e referências de tabela estruturadas. O HotXLS lê fórmulas msoxl: de volta na importação, por isso o seu próprio round trip mantém a expressão intacta, mas a forma como outra aplicação as trata está fora do controlo do escritor. Se uma fórmula de que os seus consumidores dependem sair com o prefixo msoxl:, abra o ficheiro em ambas as aplicações antes de o enviar

Porque é que o Excel não vê formatos condicionais escritos só como calcext?

O Excel 16 não vê formatos condicionais calcext porque lê os formatos condicionais ODS exclusivamente de filhos <style:map> de estilos de células e ignora por inteiro o bloco calcext:conditional-formats. A experiência que o decide é curta: pegue num ODS gravado pelo LibreOffice, apague os elementos style:map, e o Excel lê zero regras; apague antes o bloco calcext, e o Excel continua a lê-las todas. O LibreOffice comporta-se 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 toma-a e ignora o style:map

Antes da v2.384.69 o HotXLS escrevia só calcext, por isso um ficheiro ODS com realces perfeitamente bons abria no Excel sem regras de valor e sem regras de fórmula nenhuma. O HotXLS agora escreve ambas as formas. A metade style:map usa a gramática de condições do esquema OpenDocument (ODF 1.3 Parte 3), com as grafias exatas que o Excel 16 e o LibreOffice 26.2 ambos produzem quando gravam ODS:

<!-- Simplificado. Estilo portador 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>

<!-- Estilo portador 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 dificuldade com o style:map é que ele vive em estilos de células, por isso é por célula. Cada célula no intervalo da regra tem de 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, acrescenta os maps, e desduplica estilos portadores pelo par de estilo original e texto do map, por isso um intervalo de 500 células com formatação idêntica ainda produz um estilo. O escritor também estende a tabela escrita até ao intervalo da regra, o que significa que linhas de cauda vazias dentro de uma regra são emitidas em vez de descartadas. Desde a v2.384.69 o styles.xml também transporta um estilo de célula Default vazio, para que style:apply-style-name="Default" tenha sempre um alvo

A grafia calcext que o LibreOffice realmente aceita

O LibreOffice só aceita uma regra de valor calcext 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 é grafada formula-is(...). Ambos os pontos custaram ao HotXLS uma versão, porque as grafias erradas produzem uma regra que importa sem erro e depois corresponde às células erradas

O primeiro erro foi um atributo calcext:operator ao lado de calcext:value. Lê-se naturalmente, mas é inventado: o LibreOffice não conhece esse atributo, por isso importava todas as regras de valor como "igual a 0". O segundo foi pôr is-true-formula(...), a grafia style:map, numa condição calcext, que o LibreOffice também importava como comparação do valor da célula com 0. A correção da fórmula saiu na v2.384.66 e a do valor na v2.384.69:

<!-- Errado: o 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 de 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 de base é o que dá significado às referências relativas. O HotXLS ancora cada regra na célula do canto superior esquerdo da sua primeira área de intervalo, por isso uma fórmula escrita para C1 avalia como C2, C3 e assim por diante ao longo do 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élulas, por isso matrizes, uniões, colunas inteiras e marcadores $ saem nas formas descritas acima. Do lado Delphi acrescenta regras exatamente como faria para um ficheiro .xlsx

uses
  lxHandleX;

procedure AddOrderHighlights(Book: TXLSXWorkbook; Sheet: TXLSXWorksheet);
var
  Idx: Integer;
  Opts: TODSExportOptions;
begin
  // Regras de valor: style:map cell-content()>100 mais valor calcext ">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, relativa 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 Delphi

Quando o HotXLS abre um ficheiro ODS, o seu leitor aceita ambos os dialetos de formato condicional e ambas as grafias calcext, e não conta uma regra duas vezes quando o ficheiro a transporta em ambas as formas. Ficheiros reais vêm de três escritores, cada um com os seus hábitos:

• Calcext antigo e novo. Ficheiros com um atributo calcext:operator, incluindo ODS escritos pelo HotXLS antes da v2.384.69, ainda passam pelo parse legado. Condições de fórmula são reconhecidas como formula-is(...) ou 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 de base nas regras de valor. Ambos são aceites
• Células vazias. O Excel e o LibreOffice ambos põem o map das células vazias no estilo por omissão da coluna em vez de numa célula, por isso o leitor resolve estilos por omissão de coluna para células repetidas antes de recolher maps
• Reconstrução de regiões. Os maps são recolhidos por célula, por isso depois de uma folha ser lida o leitor funde células que partilhem a mesma condição e célula de base de volta em intervalos, primeiro ao longo de cada linha e depois desça por extensões de coluna correspondentes, e larga qualquer regra já lida do calcext

A correção da v2.384.72 trata de estilos numéricos, não de regras. O Excel 16 e o LibreOffice 26.2 ambos escrevem o formato General como um estilo numérico cujo elemento number:number não tem number:decimal-places, tipicamente <number:number number:min-integer-digits="1"/>. O leitor do HotXLS tratava a contagem em falta como dois decimais fixos, por isso todos os valores no estilo Default importavam com 0.00 e 1.5 aparecia como 1.50. Desde a v2.384.72 um elemento numérico simples sem casas decimais, sem decimais mínimos, sem agrupamento e com no máximo um dígito inteiro mapeia para General, e um General sozinho deixa a célula sem formato numérico nenhum. Texto à 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 é de 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 numérico
    // desde a v2.384.72, em vez de '0.00'
    Writeln('A2 format: "', Sheet.Cells[2, 1].NumberFormat, '"');
  finally
    Book.Free;
  end;
end;

As fórmulas das regras voltam em sintaxe Excel com separadores vírgula, a mesma forma que passaria ao AddCondFormatExpression, por isso uma regra escrita pelo HotXLS lê de volta como a string idêntica. Para o panorama mais largo do que o caminho de importação ODS mantém e descarta, veja o guia HotXLS do round-trip de abertura e gravação ODS; para a forma 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 formatos condicionais 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 é unilateral ou não é escrito de todo:

• Color scales e data bars são escritos só como elementos calcext, por isso o LibreOffice mostra-os e o Excel não
• Outros tipos de regras, como icon sets, regras de texto, top-N, acima da média e regras de duplicados, não têm saída ODS no escritor atual. Uma regra de texto normalmente pode ser reformulada como regra de fórmula, por exemplo ISNUMBER(SEARCH("late",B2)) sobre B2:B200, que então chega a ambas as aplicações
• Regras de coluna inteira e linha inteira como C:C são estendidas só sobre a área da tabela realmente escrita, em vez de sobre todas as 1.048.576 linhas, por isso o Excel vê estas regras só nas células que existem no ficheiro
• Ficheiros só com style:map. Quando um ficheiro 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 a partir da célula de base declarada
• Regras sobrepostas do LibreOffice. Quando uma célula é coberta por várias regras, o LibreOffice escreve só o map da primeira regra sobre ela. Ficheiros assim não podem ser lidos por completo de style:map sozinho, o que é mais uma razão para o leitor preferir o calcext quando ambos existem

O limite do processo importa mais do que qualquer um destes. Os defeitos por trás destas versões passaram por round trips que escreviam ODS e o liam de volta com o HotXLS, e alguns também teriam passado numa verificação manual na aplicação errada: fórmulas de coluna inteira funcionavam no LibreOffice enquanto o Excel mostrava #NAME?, e desde a v2.384.66 as regras de fórmula funcionavam no LibreOffice enquanto o Excel continuava a não mostrar regras nenhuma até à v2.384.69. Se a interop ODS é um requisito, o teste de aceitação é abrir o ficheiro no Excel e no LibreOffice e comparar o que cada um mostra. A mesma disciplina aplica-se aos estilos para que as regras apontam; o artigo HotXLS sobre formatação condicional e estilos cobre como os estilos de realce são definidos do lado do livro

Referência rápida: ODS que ambas as aplicações leem

• Declare xmlns:of e xmlns:msoxl na raiz do content.xml, ou o LibreOffice mostra #VALUE! para cada fórmula (HotXLS desde a v2.384.56)
• Escreva referências como [.A1], mantenha todos os $, 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 matrizes inline
• Escreva cada regra de valor ou de 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 grafe regras de fórmula formula-is(...) com uma célula de base (desde a v2.384.66 e v2.384.69)
• Espere um estilo numérico General sem number:decimal-places na importação; o HotXLS lê-o como General desde a v2.384.72
• Verifique cada novo perfil de exportação abrindo o ficheiro tanto no Excel como no LibreOffice, nunca em apenas um deles

O HotXLS é uma biblioteca de folhas de cálculo nativa Delphi e C++Builder que lê e escreve XLS, XLSX e ODS sem Excel nem LibreOffice instalados; código fonte completo, lista de funcionalidades e licenciamento estão na página do componente de folhas de cálculo Delphi HotXLS