Uma pasta de trabalho salva pelo Excel 4.0 em 1992 começa com os bytes 09 04 06 00, enquanto todo .xls do Excel 5.0 em diante começa com a assinatura de compound file D0 CF 11 E0. Um leitor que confere essa assinatura rejeita o arquivo mais velho antes de ler uma única célula, e um leitor que pula a checagem e assume BIFF8 parseia o primeiro registro como algo que ele não é. As duas falhas vêm da mesma suposição: que todo .xls tem um contêiner. Arquivos do Excel 2.0, 3.0 e 4.0 não têm nenhum, então o próprio stream de registros tem que dizer qual geração o escreveu, e os registros de célula têm que ser decodificados com o layout dessa geração
Esses arquivos ainda aparecem, e essa é a única razão de qualquer isso importar. Arquivos de engenharia, acervos de retenção de registros, exportações de laboratório de instrumentos cujo software de controle data do começo dos anos 1990 e sistemas de contabilidade de longa duração todos deixaram pastas de trabalho BIFF2 a BIFF4 para trás. Builds atuais do Excel bloqueiam vários desses formatos por padrão pelas configurações File Block, então o conjunto de dados frequentemente senta em uma pasta que ninguém consegue abrir com as ferramentas da mesa
Como uma pasta de trabalho pré-OLE se anuncia
O HotXLS reconhece uma pasta de trabalho pré-OLE pelo primeiro registro, dentro da mesma chamada TXLSWorkbook.Open que carrega todo outro .xls. Todo .xls do Excel 5.0 em diante é um arquivo composto OLE2, um pequeno sistema de arquivos dentro de um arquivo, com a pasta de trabalho vivendo em um stream chamado Workbook ou Book; parsear um começa por parsear esse contêiner, como descrito em o formato binário de compound file em Pascal
BIFF2 a BIFF4 pulam tudo isso. O arquivo começa direto com um registro BOF, e o número de registro desse BOF codifica a geração: $0009 para BIFF2, $0209 para BIFF3 e $0409 para BIFF4. A palavra de versão dentro do corpo, da qual formatos posteriores dependem, é ignorada por essas gerações, então o id de registro é o único sinal confiável. Antes de se comprometer com a rota crua, a camada de streams do HotXLS confere mais duas coisas: o corpo do BOF deve ter de quatro a seis bytes, e a palavra de tipo de substream deve ser $0010 para planilha, $0020 para gráfico ou $0040 para aba de macro. Qualquer outra coisa faz o Open devolver um código de erro
Essas checagens importam mais do que parecem. Dois bytes de $09 $00 no offset zero não são raros em dados binários arbitrários, e uma pasta de quarentena de arquivos .xls não identificados tende a conter exportações de texto renomeadas, cópias truncadas e o arquivo ocasional de um programa completamente diferente. Três campos independentes todos concordando é um sinal muito mais forte que um
Three generations, three record layouts
Os registros de célula são onde as gerações divergem de forma mais visível. O BIFF2 ocupa um bloco contíguo de números de registro baixos, $0001 a $0005, para células vazias, inteiras, numéricas, de rótulo e booleano-ou-erro. Cada um desses corpos carrega um campo de atributos de três bytes onde versões posteriores põem um índice de formato estendido de dois bytes, então todo valor senta um byte mais fundo no registro. O registro INTEGER do BIFF2 é uma peculiaridade própria: um valor sem sinal de 16 bits sem parte fracionária, um tipo de registro que o BIFF3 abandonou em favor do RK
BIFF3 e BIFF4 abandonam os números baixos e reusam os ids e layouts de registro do BIFF5: $0201, $0203, $0204, $0205 e $027E, cada um com um índice XF de dois bytes. Esse reuso causa uma falha específica e facilmente diagnosticada errado. Um registro LABEL de BIFF3 ou BIFF4 é estruturalmente idêntico ao seu correspondente BIFF5: linha, coluna, índice de formato, então uma contagem de caracteres de dois bytes no offset 6. Aplique esse layout a um rótulo BIFF2 e a contagem é montada a partir do último byte de atributos e do comprimento real de um byte, então um rótulo de cinco caracteres alega pelo menos 1.280 caracteres e o leitor corre para além do fim do registro. Vá na outra direção, layout BIFF2 em um registro BIFF3, e o comprimento é lido do byte alto da contagem, que é zero para qualquer string curta. Nada lança exceção em nenhuma direção. A pasta de trabalho volta com rótulos vazios ou lixo plausível, e o HotXLS evita ambos despachando pelo id de registro junto com a geração tirada do BOF
O campo de atributos do BIFF2 esconde mais uma armadilha. Seu primeiro byte guarda o índice de formato nos seis bits baixos, e o valor $3F significa "o índice real é grande demais para seis bits, olhe o registro IXFE precedente". O HotXLS rastreia o IXFE mais recente e resolve o escape antes de atribuir o estilo à célula, então uma célula cujo índice de formato precisa de mais que seis bits ainda recebe sua própria formatação
Texto em todas as três gerações é uma byte string pura sem Unicode em lugar nenhum. A codificação vem do registro CODEPAGE do arquivo, que nesses arquivos senta dentro do único stream de planilha porque não há stream no nível da pasta de trabalho para guardá-lo. O HotXLS decodifica rótulos e resultados de string por essa página de código e cai para Windows-1252 quando o registro está ausente, o que cobre o padrão ocidental com que a maioria desses arquivos foi escrita. Uma pasta de trabalho japonesa ou da Europa Central que não tenha registro CODEPAGE vai portanto decodificar como texto latino, e o único conserto é saber de onde o arquivo veio
Abrindo um a partir do Delphi
A detecção acontece dentro do Open, então o código chamador é o mesmo de qualquer pasta de trabalho. O loop abaixo percorre o used range e imprime cada célula duas vezes quando ela contém uma fórmula: a expressão traduzida e o valor como o Excel o exibiria pelo formato de número da célula
uses
SysUtils, Variants, lxHandle;
var
Book: TXLSWorkbook;
Sheet: TXLSWorksheet;
Cell: IXLSRange;
R, C: Integer;
begin
Book := TXLSWorkbook.Create;
try
try
if Book.Open('archive\1992-inventory.xls') <> 1 then
begin
Writeln('not a readable workbook - quarantine for manual review');
Exit;
end;
except
// Open lança exceção quando o arquivo na verdade é um pacote ZIP,
// tipicamente um .xlsx que alguém renomeou para .xls
on E: Exception do
begin
Writeln('rejected: ', E.Message);
Exit;
end;
end;
Sheet := Book.Sheets[1]; // Sheets[] é base 1
for R := Sheet.UsedRange.FirstRow + 1 to Sheet.UsedRange.LastRow + 1 do
for C := Sheet.UsedRange.FirstCol + 1 to Sheet.UsedRange.LastCol + 1 do
begin
Cell := Sheet.Cells[R, C];
if VarIsEmpty(Cell.Value) or VarIsNull(Cell.Value) then
Continue;
if Cell.HasFormula then
Writeln(Format('R%dC%d %s -> %s',
[R, C, VarToStr(Cell.Formula), Cell.FormattedText]))
else
Writeln(Format('R%dC%d %s', [R, C, Cell.FormattedText]));
end;
finally
Book.Free;
end;
end;
Cuidado com a aritmética de índices. Os limites do UsedRange são base zero enquanto tanto a coleção de planilhas quanto o acesso a células são base 1, uma inconsistência que a API mantém por compatibilidade com código existente. Esqueça o ajuste e o loop audita o retângulo errado sem reportar nada de estranho. Os arquivos que essa rota aceita carregam um único substream de planilha, gráfico ou aba de macro, então Sheets[1] é a única planilha que você vai encontrar; uma pasta bound do Excel 4.0 (BOF tipo $0100) fica fora desse conjunto e o Open devolve um código de erro. Checagens baratas que evitam carregar um arquivo de forma alguma estão cobertas em inspeção leve de pastas de trabalho
Arquivos protegidos por senha seguem a mesma regra de stream único. O registro FILEPASS aparece logo depois do BOF no stream da planilha, onde o HotXLS o pega e liga a desofuscação XOR legada que essas gerações usavam; passe a senha pela sobrecarga Open(FileName, APassWord)
O que acontece com fórmulas em um arquivo BIFF2 a BIFF4?
O HotXLS lê as duas metades de uma fórmula legada: o resultado em cache que o Excel guardou quando calculou pela última vez, e o stream de tokens, que é traduzido para o modelo de cálculo atual durante o carregamento. Registros de fórmula compartilham uma numeração paralela pelas três gerações, $0006, $0206 e $0406, mas os corpos diferem exatamente nos lugares onde um parser BIFF8 tropeçaria
O resultado em cache começa no byte 7 no BIFF2, por causa do campo de atributos, e no byte 6 no BIFF3 e no BIFF4. Resultados não numéricos usam uma palavra marcadora de $FFFF nos dois últimos bytes daquele slot de oito bytes, com um discriminador no primeiro byte: 0 para string, 1 para booleano, 2 para código de erro, 3 para vazio. Um resultado string não cabe no slot de forma alguma, então o texto chega no registro seguinte, $0007 no BIFF2 com comprimento de um byte ou $0207 no BIFF3 e no BIFF4 com comprimento de dois bytes
O stream de tokens é onde uma implementação ingênua produz nonsense. O BIFF2 prefixa a expressão com um comprimento de um byte no offset 16 e começa os tokens no 17; BIFF3 e BIFF4 usam um comprimento de dois bytes e começam no 18. Dentro do stream, tokens de função carregam um índice de função de um byte antes do BIFF5 e um índice de dois bytes do BIFF5 em diante. Alimente tokens BIFF4 a um tradutor BIFF8 sem mudanças e toda chamada de função consome um byte a mais, então o resultado não é uma fórmula levemente errada mas uma sem relação. O tradutor do HotXLS recebe a versão BIFF como parâmetro e lê cada token com as larguras daquela geração
Ter as duas metades levanta uma pergunta prática: qual número vai para a pasta de trabalho migrada? O valor em cache é o que os usuários do arquivo viram, imprimiram e assinaram em 1992. Um recálculo em um motor moderno computa o que a fórmula significa hoje, e o comportamento de funções não ficou parado por três décadas. Para uma migração de acervo o valor em cache é o registro, então guarde-o e rode um recálculo só como verificação cruzada que diz quais células merecem um olhar humano:
// Verificação cruzada: compare os resultados guardados com um recálculo fresco.
// Este helper e seu array CachedText são código seu
procedure CompareWithRecalc(Book: TXLSWorkbook; Sheet: TXLSWorksheet);
var
CachedText: array of array of string;
Rows, Cols, R, C: Integer;
begin
Rows := Sheet.UsedRange.LastRow + 1;
Cols := Sheet.UsedRange.LastCol + 1;
SetLength(CachedText, Rows + 1, Cols + 1);
for R := 1 to Rows do
for C := 1 to Cols do
if Sheet.Cells[R, C].HasFormula then
CachedText[R, C] := VarToStr(Sheet.Cells[R, C].Value);
Book.Recalculate;
for R := 1 to Rows do
for C := 1 to Cols do
if Sheet.Cells[R, C].HasFormula and
(VarToStr(Sheet.Cells[R, C].Value) <> CachedText[R, C]) then
Writeln(Format('R%dC%d stored=%s now=%s formula=%s',
[R, C, CachedText[R, C], VarToStr(Sheet.Cells[R, C].Value),
VarToStr(Sheet.Cells[R, C].Formula)]));
end;
Rode isso em uma cópia, ou antes do save, já que o Recalculate substitui os resultados guardados na memória. Um relatório vazio é o desfecho que você quer; um curto geralmente aponta para funções voláteis como NOW() ou para referências que o autor original deixou penduradas
Formatação, datas e a armadilha do formato General
O HotXLS converte os registros de formatação dessas gerações para o mesmo modelo de pasta de trabalho que um .xls moderno produz. Registros FONT (com o registro FONTCOLOR separado do BIFF2 incorporado), as strings FORMAT e os registros XF de cada geração, com seus padrões de preenchimento, estilos de borda e índices de cor legados, viram fontes, formatos de número e estilos de célula comuns que você pode ler e editar por Font, NumberFormat e as outras propriedades de intervalo
Datas se beneficiam direto. Quando uma célula numérica carrega um formato de número de data, o leitor também aplica a flag DATEMODE da pasta de trabalho, então uma pasta no sistema 1904 aterrissa na mesma escala TDateTime que uma no sistema 1900, e o FormattedText mostra a data do jeito que o Excel mostrava. As regras por trás do offset de 1462 dias estão cobertas em números seriais de data, o sistema 1904 e formatos de número
A armadilha é a coluna que guarda datas sob formato General. Muitas pastas dos anos 1990 foram preenchidas por macros ou importações que gravaram números seriais sem se dar ao trabalho de formatá-los, e o arquivo não dá sinal de que 33604 significa um dia em janeiro de 1992 em vez de um número de peça. Essas células voltam exatamente como gravadas, sem correção pelo sistema de datas. Decida por coluna, a partir do que você sabe sobre a origem, e converta essas colunas você mesmo:
// ColumnHoldsDates é sua própria busca, construída do conhecimento do
// sistema de origem; o arquivo legado não responde essa pergunta para você.
// Serial é um Double local; Cell e C vêm da primeira listagem
if ColumnHoldsDates(C) and (Pos('d', LowerCase(Cell.NumberFormat)) = 0) then
begin
// Serial com formato General: nenhuma correção de sistema de data aplicada ainda
Serial := Cell.Value;
if Book.Date1904 then
Serial := Serial + 1462;
Writeln(DateToStr(TDateTime(Serial)))
end
else
Writeln(Cell.FormattedText);
O teste Pos('d', ...) é deliberadamente grosseiro e serve só para pular células que já carregam um token de dia em seu formato; uma checagem mais estrita contra a sua própria lista de formatos de data conhecidos vale as linhas extras se o acervo for grande
Gravando a cópia migrada
Uma pasta de trabalho aberta de um arquivo BIFF2 a BIFF4 é salva pelos escritores BIFF5/BIFF8 ou XLSX, já que não há razão para produzir arquivos novos em um formato que o Excel atual bloqueia. Book.SaveAs('migrated\1992-inventory.xls', xlExcel97) grava uma cópia BIFF8 que carrega os valores, as fórmulas traduzidas e os estilos convertidos, e toda ferramenta posterior no pipeline consegue abri-la sem tratamento especial. Guarde o original ao lado: se uma célula levantar uma pergunta daqui a dois anos, os bytes de 1992 são a única autoridade
Leitura legada, gravação moderna, recálculo e formatação vivem em uma biblioteca só para Delphi e C++Builder; a cobertura de formatos está listada na página do componente de planilha HotXLS para Delphi