Um trabalho de normalização em lote de folhas de cálculo representa três problemas sob a mesma capa. Tem um arquivo de formatos mistos: .xls da era BIFF, .xlsx moderno, uma dispersão de ficheiros .ods de alguma experiência do LibreOffice, e um punhado de ficheiros que ninguém consegue abrir porque a palavra-passe partiu com um antigo funcionário. O objetivo é converter tudo para XLSX e CSV. A versão desse trabalho que a maioria das pessoas escreve é um ciclo que abre cada ficheiro e o guarda com uma nova extensão, e funciona até ao momento em que alguém pergunta quais os ficheiros que perderam os seus gráficos, descartaram as suas macros ou nunca chegaram a abrir. O ciclo não tem resposta, porque a conversão por si só não mantém qualquer registo. Uma bancada de trabalho sim: primeiro faz o inventário, segundo converte e terceiro verifica, e as três etapas têm de partilhar informações para que qualquer parte seja fiável.
Montar essa bancada de trabalho em Delphi ou C++Builder significa interligar quatro capacidades do HotXLS, nenhuma das quais necessita do Excel instalado em qualquer parte do fluxo. Existem dois motores nativos, uma fachada BIFF8 para .xls e uma fachada OOXML para .xlsx e .ods. Existem chamadas de sondagem de baixo custo que leem metadados sem analisar o ficheiro completo. Existem contadores de auditoria por folha que lhe dizem o que um livro de trabalho realmente contém. E existe uma matriz de conversão com um perfil de fidelidade documentado para cada rota. O trabalho consiste em saber onde cada uma delas tem uma aresta afiada, porque todas têm, e as arestas são exatamente o que transforma um lote noturno limpo num incidente de segunda-feira de manhã.
Sondar antes de carregar: nomes de folhas e deteção de encriptação
Abrir um livro de trabalho de 200 MB apenas para descobrir que está encriptado desperdiça minutos por ficheiro e, multiplicado por um arquivo grande, desperdiça dias. Ambas as fachadas expõem GetSheetNames, que lê os metadados das folhas sem preencher o livro de trabalho. A implementação BIFF analisa apenas os registos BoundSheet no início do fluxo; a implementação OOXML lê apenas o workbook.xml dentro do ficheiro zip. A par disso, CanReadEncrypted deteta um contentor de encriptação sem tentar a desencriptação:
var
Probe: TXLSXWorkbook;
Names: TStringList;
begin
Names := TStringList.Create;
Probe := TXLSXWorkbook.Create;
try
if Probe.CanReadEncrypted(FileName) then
begin
Writeln(FileName + ': encrypted container - route to manual handling');
Exit;
end;
if Probe.GetSheetNames(FileName, Names) <= 0 then
Writeln(FileName + ': unreadable - quarantine')
else
Writeln(Format('%s: %d sheet(s), first "%s"',
[FileName, Names.Count, Names[0]]));
finally
Probe.Free;
Names.Free;
end;
end;Dois detalhes operacionais tornam este ciclo de baixo custo. O método GetSheetNames não repõe nem preenche a instância do livro de trabalho, pelo que um único objeto de sondagem pode classificar milhares de ficheiros sem ser recriado. E a versão da fachada XLS da mesma chamada também compreende pacotes .xlsx, o que a torna uma sonda única e conveniente quando as extensões de ficheiro não são de confiança, como raramente são num arquivo tão antigo. A triagem antes do carregamento merece um tratamento próprio; a mecânica da inspeção leve está descrita no nosso artigo sobre listagem de folhas e inspeção leve de livros de trabalho.
Contar o que um livro de trabalho realmente contém
Assim que um ficheiro passa a triagem, o passo de auditoria decide a sua rota de conversão. A fachada XLSX expõe um contador para cada família de funcionalidades que influencia uma decisão de fidelidade: células unidas, gráficos, imagens, formatos condicionais, validações de dados, tabelas, hiperligações e comentários, além de sinalizadores ao nível do livro de trabalho para macros, proteção e formato de origem. A rota de conversão de um ficheiro depende quase inteiramente de quais destes valores regressam diferentes de zero.
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
I: Integer;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open(FileName) <> 1 then Exit;
for I := 0 to Book.Sheets.Count - 1 do
begin
Sheet := Book.Sheets[I];
Writeln(Format('%s: cells=%d merges=%d charts=%d cf=%d dv=%d protected=%s',
[Sheet.Name, Sheet.Cells.Count, Sheet.MergedCells.Count,
Sheet.Charts.Count, Sheet.ConditionalFormats.Count,
Sheet.DataValidations.Count, BoolToStr(Sheet.IsProtected, True)]));
end;
if Book.HasVbaProject then
Writeln(' contains VBA project - macro policy applies');
if Book.ExternalLinks.Count > 0 then
Writeln(Format(' %d external link(s)', [Book.ExternalLinks.Count]));
finally
Book.Free;
end;
end;Leia Cells.Count com uma ressalva em mente. O armazenamento de células é esparso, pelo que o número conta as células instanciadas, não a área retangular do intervalo utilizado. Uma folha com um valor em A1 e outro em ZZ9999 reporta duas células, e não o milhão e tal que se encontra entre elas. A análise equivalente no lado BIFF utiliza os limites de UsedRange juntamente com ForEachCell, e traz o erro de desvio por um que afeta quase toda a gente na primeira vez: UsedRange.FirstRow e os seus semelhantes são baseados em 0, enquanto Cells.Item[Row, Col] é baseado em 1. Uma travessia que se esqueça de adicionar um a cada limite audita o retângulo errado e nunca o assinala.
Duas opções reduzem o custo de uma passagem exclusiva de auditoria em ficheiros antigos de grande dimensão. Definir _DisableGraphics como true antes de abrir um .xls ignora completamente a análise da camada de desenho OfficeArt, o que poupa tempo real em livros de trabalho densos em formas. No entanto, é estritamente uma otimização de leitura: guardar a partir de uma instância aberta dessa forma descartaria os desenhos que nunca analisou, pelo que o sinalizador pertence apenas a caminhos que nunca escreverão o ficheiro de volta. Quando a auditoria necessita de conteúdo por célula em vez de contagens, a chamada de retorno ForEachCell percorre diretamente as células preenchidas e evita a sobrecarga de Variant por acesso que as propriedades de células indexadas pagam em cada leitura, o que se acumula rapidamente ao longo de milhões de células.
Normalizar os códigos de retorno inconsistentes atempadamente
As chamadas de E/S do HotXLS reportam erros através de resultados inteiros em vez de exceções, e as convenções não são uniformes em toda a API. A maioria das chamadas de abertura e gravação devolve 1 em caso de sucesso e -1 em caso de falha. O método GetSheetNames devolve a contagem de folhas ou -1 com a lista limpa. O método XLSX SaveAsHTML quebra novamente o padrão e devolve 0 para o sucesso, e -1 para um índice de folha fora do intervalo. Uma bancada de trabalho que teste = 1 em todo o lado irá classificar incorretamente e de forma silenciosa as chamadas que sinalizam o sucesso de outra forma, e uma que teste <> -1 irá ignorar as que falham com um código diferente.
A regra que sobrevive ao contacto com toda a API é mais estreita do que parece: tratar <= 0 como falha para chamadas que devolvem contagens, verificar o valor de sucesso documentado para cada rotina de gravação que realmente utiliza, e colocar ambos atrás de uma pequena função de verificação de resultados para que a convenção resida exatamente num único local. Os fluxos de trabalho em lote falham muito mais vezes devido a uma acumulação lenta de códigos de retorno não verificados do que devido a qualquer erro exótico do analisador, e o custo de errar nisto surge quarenta mil ficheiros mais tarde, quando já ninguém se lembra de quais as conversões que realmente funcionaram.
A matriz de conversão e onde cada caminho perde dados
As duas fachadas dividem o trabalho de conversão entre si. A classe TXLSXWorkbook abre XLSX, ODS e CSV, e guarda XLSX, ODS, CSV, HTML, RTF e XLSX encriptado com AES. A classe TXLSWorkbook abre e guarda BIFF, e exporta HTML, RTF e CSV. O aspeto útil é que cada caminho vem com um perfil de fidelidade documentado, não uma promessa vaga de correção, para que possa decidir com antecedência quais as rotas seguras para cada ficheiro.
A exportação para CSV escreve em UTF-8 com um BOM, quebras de linha CRLF e delimitação RFC 4180. O que não faz é avaliar as fórmulas: uma célula contendo =SUM(...) é exportada como o texto literal da fórmula, pelo que uma folha de fórmulas se transforma numa folha de cadeias de caracteres (strings), a menos que calcule os valores primeiro. A exportação para HTML produz uma única tabela, com colspan e rowspan a substituir as células unidas e estilos base integrados (inlined). A exportação para RTF tem um limite mais estrito: não consegue expandir células unidas através de colunas, pelo que as células de continuação de uma união surgem vazias. A importação de ODS é intencionalmente leve, conforme a própria documentação da biblioteca. Os valores escalares e os resultados de fórmulas em cache são importados; estilos, expressões de fórmulas ODF ativas e desenhos não são. Isso é importante no momento em que o arquivo contém ficheiros OpenDocument reais regidos pelo padrão OASIS ODF 1.3, onde qualquer conversão visualmente fiel necessita de mais do que este caminho de importação foi concebido para suportar, e a passagem de auditoria é o que lhe diz que esses ficheiros existem antes do lote os aplanar silenciosamente.
SaveXLSWorkbookAsXLSX é uma ponte de dados, não uma ponte de layout
A fachada BIFF não consegue escrever OOXML diretamente, pelo que a transição de .xls para .xlsx passa pela função SaveXLSWorkbookAsXLSX na unidade lxXlsxExport. A fidelidade dessa ponte merece ser enunciada claramente, pois o nome sugere mais do que realmente faz. Copia valores, fórmulas, formatos numéricos, cores de preenchimento, atributos essenciais de tipo de letra (font), larguras de coluna e definições de visualização como linhas de grelha. Não copia limites, intervalos unidos, comentários, gráficos ou formatação condicional. Para uma normalização ao nível dos dados, onde os sistemas a jusante analisarão o resultado e ninguém olhará para a formatação, isso é exatamente suficiente e nada do que é necessário é perdido. Para um relatório formatado destinado a ser lido por uma pessoa, não é suficiente, e é precisamente aqui que os contadores de auditoria conquistam o seu lugar: um ficheiro sinalizado pela auditoria como contendo gráficos e formatos condicionais deve ser encaminhado para uma fila manual, e não através de uma ponte que descartará ambos sem aviso.
var
Legacy: IXLSWorkbook; // interface reference: do not Free
Modern: TXLSXWorkbook;
begin
if SameText(ExtractFileExt(FileName), '.xls') then
begin
Legacy := TXLSWorkbook.Create;
if Legacy.Open(FileName) <= 0 then Exit;
if SaveXLSWorkbookAsXLSX(Legacy,
ChangeFileExt(FileName, '.xlsx')) <= 0 then
Writeln('bridge failed: ' + FileName);
end
else
begin
Modern := TXLSXWorkbook.Create;
try
Modern.StreamingWrite := True; // stream sheet XML into the zip
if Modern.Open(FileName) = 1 then
Modern.SaveAsCSV(ChangeFileExt(FileName, '.csv'), 0, ',');
finally
Modern.Free;
end;
end;
end;O ciclo acima também mostra o mecanismo de desempenho no lado OOXML. Definir StreamingWrite como true envia o XML da folha de cálculo diretamente para o pacote de saída em vez de o processar como uma cadeia de caracteres gigante na memória, o que faz a diferença entre uma execução estável e uma falha por falta de memória (out-of-memory) quando os ficheiros atingem centenas de milhares de linhas. O dimensionamento e o comportamento da memória para esse modo têm o seu próprio tratamento no nosso artigo sobre escritas em fluxo para tarefas em lote no servidor. Mais uma propriedade importa para um lote que queira utilizar todos os núcleos: nenhuma das fachadas é segura para execução simultânea (thread-safe), mas nenhuma partilha estado global, pelo que o padrão suportado para conversão paralela é uma instância de livro de trabalho por thread de trabalho, sem bloqueios entre elas.
Os ficheiros com palavra-passe e o que fazer com eles
Os ficheiros bloqueados do arquivo dividem-se claramente por formato, e essa divisão decide para onde vão. A encriptação antiga de .xls, seja RC4, RC4 sobre CryptoAPI ou a antiga ofuscação XOR, é legível: passe a palavra-passe para Open e o ficheiro é convertido como qualquer outro. Os pacotes .xlsx encriptados são uma história diferente. O HotXLS deteta-os com CanReadEncrypted mas não os consegue desencriptar, pelo que a única solução honesta é encaminhá-los para uma fila onde um humano abra e volte a guardar cada um no Excel antes de reentrar no fluxo. Vale a pena prever esta assimetria desde o início, porque os ficheiros XLSX encriptados são os que têm maior probabilidade de ser os registos com que alguém realmente se importa.
Fechar o ciclo com a verificação
A terceira etapa é a que costuma ser ignorada, e ignorá-la é o que transforma uma conversão em massa numa dor de cabeça. Nenhum caminho de gravação no HotXLS avalia fórmulas. O Excel recalcula quando abre um ficheiro, pelo que uma conversão de XLSX para XLSX permanece correta, mas um destino CSV recebe o texto da fórmula literalmente, a menos que o fluxo primeiro execute Calculate nas células e escreva os resultados de volta. Saber isso com antecedência é a diferença entre um CSV cheio de números e um CSV cheio de cadeias =SUM(...) que ninguém nota até que uma importação a jusante falhe com elas.
A verificação em si é suficientemente económica para que não haja desculpa para a deixar de fora. Reabra cada ficheiro convertido com a mesma biblioteca, execute novamente os contadores de auditoria e compare-os com os números pré-conversão que o passo de inventário já registou. Uma contagem de folhas que diminuiu, uma contagem de gráficos que foi para zero onde a origem tinha três, uma contagem de células que caiu drasticamente: cada um é uma perda silenciosa detetada pelo custo de uma segunda abertura. Além disso, inspecione visualmente uma amostra no Excel ou LibreOffice, e a combinação detetará a grande maioria dos danos de conversão antes de ser enviada. Esta é a razão pela qual a fase de inventário alimenta a fase de verificação. Sem os números anteriores, os números posteriores não provam nada.
Uma bancada de trabalho focada primeiro na auditoria transforma uma conversão em massa arriscada num processo mensurável com uma secção de quarentena para os ficheiros que não conseguem passar de forma limpa. Todas as chamadas de sondagem, contagem e conversão mostradas aqui fazem parte do Componente HotXLS, que as executa nativamente no processo sem automação do Excel.