Abrir e Guardar Folhas de Cálculo ODS em Delphi com HotXLS

Um backend de relatórios em Delphi que emite ficheiros .xlsx há anos depara-se com um novo requisito: as regras de contratação pública de um cliente exigem outputs no formato OpenDocument Spreadsheet, e os analistas desse cliente enviam as suas edições de volta como ficheiros .ods guardados a partir do LibreOffice. Assim, o mesmo código tem agora de escrever e ler ODS. O HotXLS, a biblioteca nativa de folhas de cálculo em Object Pascal da losLab para Delphi e C++Builder, lida com ambas as direções sem necessidade de ter o Excel ou o LibreOffice instalados. O que não faz é tornar as duas direções simétricas. A exportação transporta muito mais informação do que a importação recupera, e uma equipa que assuma o contrário verá fórmulas e formatações evaporarem-se algures entre a revisão do cliente e o relatório seguinte, sem qualquer erro que aponte o problema.

O suporte a ODS reside na fachada XLSX, não na XLS

O HotXLS disponibiliza duas hierarquias de classes independentes num único pacote: TXLSWorkbook na unit lxHandle para ficheiros binários BIFF8 .xls, e TXLSXWorkbook na unit lxHandleX para pacotes OOXML .xlsx. Todos os pontos de entrada OpenDocument - OpenODS, SaveAsODS, GetODSSheetNames - dependem de TXLSXWorkbook. Esta localização não é arbitrária. Um pacote ODS, conforme especificado no ODF 1.3 da OASIS, é um arquivo zip contendo um membro mimetype, um manifesto e um corpo content.xml, o que o torna um primo estrutural do zip OOXML; o BIFF8 é um fluxo de registos binários dos anos 90 com o qual nada tem em comum.

Esta organização tem um aspeto prático: um livro legacy .xls não se pode tornar num .ods numa única chamada. Primeiro, faz a ponte do conteúdo BIFF para o modelo XLSX com SaveXLSWorkbookAsXLSX a partir da unit lxXlsxExport, reabre o resultado através de TXLSXWorkbook e depois exporta a partir daí. Esta transição não é isenta de perdas, e convém conhecer as lacunas antes de construir sobre ela. Copia valores, fórmulas, formatos numéricos, tipos de letra, preenchimentos e larguras de colunas. Descarta margens, intervalos unidos, comentários, gráficos e formatação condicional. Uma origem .xls com formatação pesada chegará ao ODS com um aspeto mais simples do que o original, e isso é uma propriedade da ponte de conversão, não do escritor ODS.

A deteção no lado da importação é automática. O método simples Open reconhece um pacote ODS pelo seu membro mimetype, recorrendo a uma verificação do content.xml de nível superior quando esse membro está ausente, pelo que um caminho de código genérico de 'abrir o que quer que o utilizador tenha carregado' não necessita de fazer análises à extensão do ficheiro. Após a abertura, a propriedade SourceFormat indica qual ramificação foi acionada.

Exportar para ODS com TODSExportOptions

A chamada de exportação em si resume-se a uma linha; o objeto de opções em seu redor contém as decisões sobre as quais um revisor irá perguntar mais tarde:

var
  Book: TXLSXWorkbook;
  Opts: TODSExportOptions;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('quarterly-report.xlsx');
    Opts := TODSExportOptions.Create;        // caller owns and frees this
    try
      Opts.Generator := 'ReportService 4.2'; // meta:generator override
      Opts.IncludeCharts := True;
      Opts.IncludeImages := True;
      Book.SaveAsODS('quarterly-report.ods', Opts);
    finally
      Opts.Free;
    end;
  finally
    Book.Free;
  end;
end;

O objeto de opções pertence ao chamador. O HotXLS não o irá libertar, razão pela qual o bloco try..finally interno é necessário e não opcional. As duas propriedades que alteram o output, em vez de apenas o etiquetar, merecem uma análise mais detalhada. Definir IncludeCharts := False faz mais do que ocultar gráficos: remove do pacote os subdocumentos de gráficos e as suas entradas de manifesto, o que é exatamente o pretendido quando o consumidor é um pipeline de dados que falharia ao encontrá-los. O Generator substitui a string ODF meta:generator, que de outra forma seria HotXLS/<versão>; altere-a quando as ferramentas a jusante identificam os produtores do ficheiro para encaminhamento de suporte. Se nada disto se aplicar, ignore completamente o objeto de opções. Chamar SaveAs(FileName, xlsxOpenDocumentSpreadsheet) é o mesmo que chamar SaveAsODS com as definições padrão, e as sobrecargas de fluxo (stream) em ambos permitem escrever o pacote diretamente numa resposta HTTP sem criar ficheiros temporários.

O que o caminho de importação lê, e o que ignora deliberadamente

Leia esta parte com atenção antes de prometer fidelidade de round-trip a alguém. A importação de ODS no HotXLS é deliberadamente um caminho leve. Preserva os valores de células escalares e o resultado em cache que cada fórmula continha no momento da gravação, e expande linhas e colunas repetidas ao longo da grelha. Não importa estilos, expressões de fórmulas ODS ou desenhos.

A escolha relativa às fórmulas é a que tem maior probabilidade de causar problemas, e foi tomada de forma deliberada. Uma célula ODF armazena duas coisas lado a lado: a expressão da fórmula, escrita no dialeto OpenFormula definido no ODF 1.3 Parte 4, e o último valor que a aplicação geradora calculou para a mesma. Traduzir o OpenFormula para a sintaxe de fórmulas do Excel é um problema de conversão de dialeto próprio, com casos limite reais em torno de vocabulários de funções, sintaxe de referências e modelos de erro. Ler o valor em cache contorna toda essa classe de traduções incorretas e silenciosas, pelo que os números importados são exatamente os números que o remetente viu pela última vez. O custo é que chegam como números, não como as fórmulas ativas que os geraram.

O modo de falha a contornar decorre diretamente daqui: uma folha de cálculo cujos totais estavam corretos quando o LibreOffice a guardou pela última vez é importada com os números corretos, mas esses números são agora constantes. Se editar uma célula de input e recalcular, nada muda – a fórmula desapareceu, restando apenas o seu resultado final. Se o fluxo de trabalho exigir fórmulas ativas após a importação, restabeleça-as programaticamente a partir das suas próprias regras de negócio através de Cell.Formula, que na fachada XLSX aceita a expressão sem o sinal de igual no início.

Desenhar em torno do round-trip assimétrico

A exportação gera a partir de todo o modelo do livro em memória: valores, estilos e, se solicitado, gráficos e imagens. A importação devolve apenas valores. Portanto, a etapa de .xlsx para .ods é de alta fidelidade, mas a etapa de .ods para .xlsx traz de volta valores e resultados em cache, mas sem estilo e sem fórmulas ativas. Ao encadear as duas, a assimetria acentua-se. Um ciclo completo de .xlsx para .ods para .xlsx escreve tudo fielmente na saída e perde os estilos e fórmulas no regresso, muito embora nada tenha falhado em qualquer um dos passos.

Book := TXLSXWorkbook.Create;
try
  Book.Open('vendor-revision.ods');          // format auto-detected
  if Book.SourceFormat = xlsxOpenDocumentSpreadsheet then
  begin
    // Values and cached formula results are present after an ODS
    // import; styles and live formulas are not. Rebuild whatever
    // the downstream pipeline depends on before saving.
    Book.Sheets[0].Cells[2, 5].Formula := 'SUM(B2:D2)';
    Book.SaveAs('vendor-revision.xlsx');
  end;
finally
  Book.Free;
end;

O padrão de arquitetura que daqui resulta: trate os ficheiros .ods de entrada como feeds de dados, não como documentos para editar no local. Mantenha o livro de cálculo canónico em .xlsx, leia os valores a partir das revisões dos clientes e emita novos ODS sob procura a partir da cópia canónica. A verificação cabe a ambos os lados – abra os ficheiros exportados no LibreOffice Calc, o leitor ODF de referência, e no Excel, que lê ODS há anos mas diverge do LibreOffice nos limites do suporte a gráficos e estilos. A contagem de folhas, um punhado de células-chave e a presença de gráficos constituem um teste de validação suficiente por perfil de exportação.

Sondar um ficheiro ODS antes de avançar para a importação

Quando um endpoint aceita uploads, listar os nomes das folhas é muito mais barato do que uma análise completa e deteta surpresas estruturais logo no início:

Names := TStringList.Create;
Book := TXLSXWorkbook.Create;
try
  if Book.GetODSSheetNames('incoming.ods', Names) <= 0 then
    raise Exception.Create('not a readable ODS package');
  if Names.IndexOf('Data') < 0 then
    raise Exception.Create('revision is missing the Data sheet');
finally
  Book.Free;
  Names.Free;
end;

A convenção de retorno costuma confundir os programadores: as chamadas do HotXLS devolvem geralmente uma contagem positiva ou 1 em caso de sucesso e -1 em caso de falha, limpando a lista ao falhar, pelo que deve testar <= 0 em vez de comparar com um valor positivo específico. O GetODSSheetNames não reinicia nem preenche a instância do livro de cálculo, pelo que um único objeto de sondagem pode examinar um diretório inteiro de ficheiros recebidos. Verificações estruturais como esta detetam a falha mais comum no mundo real – um analista mudar o nome ou apagar uma folha antes de enviar a revisão de volta – logo no início, onde a mensagem de erro ainda pode identificar o ficheiro e a folha em falta, em vez de se manifestar como uma referência nula três níveis abaixo.

Se estiver a construir um pipeline de conversão mais amplo em torno disto, o artigo sobre auditoria de livros e bancada de conversão mostra como inventariar as funcionalidades de um ficheiro antes de escolher um formato de destino, e o guia de desempenho para grandes livros de cálculo mantém as exportações em lote dentro de limites de memória razoáveis.

O HotXLS é um biblioteca nativa de folhas de cálculo para Delphi e C++Builder com código fonte completo; a lista completa de funcionalidades e os detalhes de licenciamento encontram-se na página do Componente HotXLS.