Abra uma planilha, clique em uma célula que exibe 2026-06-19, e a barra de fórmulas ainda lerá uma data. Leia a mesma célula a partir do Delphi e você receberá o número 46192. Ambas as visualizações estão corretas, porque o Excel nunca armazenou uma data naquela célula. Ele armazenou um número serial, uma contagem de dias, e associou um formato de número que informa à tela para renderizar a contagem como uma data do calendário. Não existe tipo de data no valor da célula. Há um número e uma regra de exibição, e a regra de exibição é a única coisa que distingue uma data de uma quantidade simples.
Essa separação é a origem de todo bug de data que uma biblioteca de planilhas precisa evitar. Um serial sozinho não diz que dia é, porque não diz qual foi o dia zero. O mesmo número representa duas datas com quatro anos de diferença, dependendo de uma única flag na pasta de trabalho. E um número que deveria ser lido como uma data será retornado como uma quantidade bruta, a menos que algo inspecione seu formato e reconheça um padrão de data. É assim que o modelo de datas no HotXLS é construído, e por que ele deve ser.
Uma célula de data é um número mais um formato
O Excel armazena uma data como a quantidade de dias desde uma época, com a hora do dia na parte fracionária. O meio-dia em um serial carrega .5. A parte inteira é a contagem de dias. Nada no valor armazenado o identifica como temporal. O que o identifica é o formato numérico da célula: o padrão ECMA-376 chama isso de numFmt, e uma célula cujo código de formato detalha um padrão de data ou hora é exibida como uma data. Remova o formato e a mesma célula exibirá um número; o valor subjacente nunca se alterou.
É por isso que ler o valor de uma célula retorna um Variant que pode ser um varDate ou um Double simples, e por que o formato numérico na mesma célula é o sinal que determina qual era a intenção de terceiros. Quando o HotXLS abre um arquivo XLSX, uma célula carrega tanto o seu Value quanto seu NumberFormatIndex no TXLSXCell, e o índice de formato é o que você consulta para saber se o número é uma data.
var
Book: TXLSXWorkbook;
Cell: TXLSXCell;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open('timesheet.xlsx') <> 1 then
raise Exception.Create('Cannot open workbook');
Cell := Book.Sheets[0].Cells[1, 1]; // row 1, col 1 (1-based)
// Value may arrive as varDate or as a plain numeric serial;
// the format index is the signal that tells them apart.
Writeln('raw value : ', VarToStr(Cell.Value));
Writeln('numFmt idx: ', Cell.NumberFormatIndex);
Writeln('format : ', Cell.NumberFormat);
finally
Book.Free;
end;
end;Duas épocas, com 1462 dias de diferença
O sistema de datas padrão, usado por toda pasta de trabalho do Windows, conta a partir do final de 1899, de modo que o serial 1 corresponde ao primeiro dia de 1900. O outro sistema remonta aos primeiros Macintosh e conta a partir do início de 1904, de modo que o serial 1 fica quatro anos e um dia depois. Uma pasta de trabalho registra qual sistema utiliza em uma flag. Em um pacote OOXML, essa flag é a date1904 na parte da pasta de trabalho; o HotXLS a expõe como a propriedade Date1904 da pasta de trabalho.
A diferença entre as duas épocas é de exatamente 1462 dias. Isso corresponde a quatro anos civis, três de 365 dias e um de 366, totalizando 1461 dias, mais um dia de deslocamento entre as duas convenções de dia zero. O número é fixo e você pode memorizá-lo facilmente. Sua importância é que ele não é zero. Um serial copiado de uma pasta de trabalho de 1904 e interpretado sob as regras de 1900, ou o inverso, desloca cada data em 1462 dias, o que se apresenta como datas incorretas em pouco mais de quatro anos e é fácil de confundir com dados corrompidos.
Como o TDateTime do próprio Delphi está ancorado na convenção de 1900, uma biblioteca que mapeia seriais do Excel para TDateTime precisa deslocar o valor em 1462 dias em ambas as direções sempre que a pasta de trabalho for marcada como 1904. Ao ler um serial de 1904, subtraia 1462 antes de tratá-lo como um TDateTime; ao gravar um TDateTime em uma pasta de trabalho de 1904, subtraia 1462 do serial para que o Excel renderize o dia planejado. O HotXLS aplica esse deslocamento internamente quando serializa valores de data para uma pasta de trabalho cujo Date1904 está ativo, para que o valor atribuído como um TDateTime retorne ao mesmo dia do calendário na tela.
A peculiaridade deliberada do ano bissexto de 1900
Há uma famosa peculiaridade no sistema de 1900. O Excel trata o ano de 1900 como um ano bissexto e aceita 29 de fevereiro de 1900 como uma data real, correspondente ao serial 60. O ano de 1900 não foi um ano bissexto, pois os anos de virada de século são bissextos apenas quando divisíveis por 400, e 1900 não é. O dia fantasma é um comportamento de compatibilidade deliberado herdado de uma planilha pioneira que foi lançada com o bug, mantido desde então para que a aritmética serial permaneça idêntica em décadas de arquivos.
A consequência prática é pequena, mas real: para qualquer data a partir de 1º de março de 1900, o serial é uma unidade maior do que uma contagem de dias estritamente correta daria, porque o inexistente 29 de fevereiro consumiu um número. Uma biblioteca de planilhas reproduz a peculiaridade em vez de corrigi-la, pois corresponder exatamente à aritmética do Excel é todo o trabalho. Corrigi-la colocaria cada data moderna com um dia de diferença em relação ao que o Excel exibe, o que é um resultado pior do que carregar um desvio por um com mais de quarenta mil dias de idade e que nenhuma data real em uso comercial chega a tocar. O sistema de 1904 não possui dia fantasma correspondente, um dos motivos pelos quais algumas empresas historicamente o preferiram.
Detectando uma data a partir de numFmt
Quando um número chega de um arquivo que outra pessoa gravou, seu formato é a única evidência de que se trata de uma data. O padrão ECMA-376 atribui um bloco de IDs de formato integrados cujo significado é fixado pela especificação, e os formatos de data e hora ocupam intervalos conhecidos. Os IDs de 14 a 22 são os formatos de data e hora para localidades gerais, os conhecidos m/d/yyyy, h:mm e correlatos. Os IDs de 45 a 47 são os formatos de tempo decorrido. Outras duas faixas, de 27 a 36 e de 50 a 58, são os formatos de data e hora específicos de localidade usados para calendários CJK, definidos no ECMA-376 18.8.30. Uma célula cujo ID de formato numérico se enquadra em qualquer uma dessas faixas é uma célula de data ou hora.
Os IDs integrados cobrem os casos comuns, mas não os personalizados. Quando uma pasta de trabalho define seu próprio código de formato, como uma ordenação não padrão ou um nome de mês localizado, o ID fica acima da faixa integrada e aponta para a tabela de formatos de números da pasta de trabalho. Para esses, reconhecer uma data significa ler a string do código de formato e procurar por tokens de data. O HotXLS reúne ambas as verificações em um único predicado interno, o XlsxNumFmtIsDate, que retorna verdadeiro imediatamente para as faixas de data integradas e, caso contrário, analisa o código de formato personalizado por meio de XlsxFormatCodeIsDate. O lado público disso são a string NumberFormat da célula e seu NumberFormatIndex, que fornecem tanto o código de formato resolvido quanto o ID a ser testado.
Por que o analisador de formato não pode simplesmente escanear por d e m
Analisar um código de formato em busca de tokens de data parece algo simples até você lembrar do que mais existe em um formato de número. Uma busca ingênua pelas letras que representam as datas, como o d, m, y, h e s de dia, mês, ano, hora e segundo, falhará em duas estruturas que não representam tokens de data de forma alguma.
A primeira é o literal de string entre aspas. Um formato de número pode incorporar texto literal em aspas duplas, de modo que um formato financeiro como #,##0 "MM" anexa os caracteres M e M a um número sem qualquer significado temporal. Um scanner que conta as letras dentro das aspas como tokens de mês sinalizaria incorretamente esse formato de moeda como uma data. A segunda é a seção de colchetes. Os formatos de números carregam diretivas em colchetes, nomes de cores como [Red], condições de comparação como [>1000], tags de localidade e os marcadores de tempo decorrido [h] e [mm]. Alguns conteúdos de colchetes contêm letras de data e outros não, e tratar o texto entre colchetes da mesma forma que o corpo do formato leva tanto a falsos positivos quanto a casos perdidos.
O analisador correto percorre o código de formato caractere por caractere, rastreando se está dentro de um literal entre aspas e a profundidade de aninhamento de colchetes em que se encontra, e também respeita o escape de barra invertida que protege um único caractere subsequente. Apenas uma letra de data sem escape encontrada fora de qualquer literal de string e fora de qualquer seção de colchetes conta como um token de data real. É exatamente assim que o XlsxFormatCodeIsDate faz o escaneamento: uma aspa altera o estado interno de literal, o que suprime a detecção de tokens até a aspa de fechamento, uma barra invertida pula o próximo caractere, e um contador de profundidade de colchetes suprime a detecção dentro de sequências [...]. A vantagem é que #,##0 "MM" é lido corretamente como um formato de número, enquanto um código personalizado curto que não contém nada além de um único m ou d fora das aspas ainda é corretamente reconhecido como uma data.
Lendo datas de arquivos de terceiros
Tudo o que foi exposto converge para um único fluxo de trabalho: converter um número que algum outro aplicativo gravou de volta em uma data na qual você possa confiar. O serial fornece a contagem de dias, a flag Date1904 da pasta de trabalho informa a partir de qual época a contagem é medida e o ID de formato numérico ou o código personalizado da célula é a única evidência de que o número deveria ser interpretado como uma data. Descarte qualquer um dos três e você obterá uma resposta incorreta plausível, em vez de um erro visível.
var
Book: TXLSXWorkbook;
Sheet: TXLSXWorksheet;
Cell: TXLSXCell;
r: Integer;
begin
Book := TXLSXWorkbook.Create;
try
if Book.Open('vendor-export.xlsx') <> 1 then
raise Exception.Create('Cannot open export');
// The 1904 flag is workbook-wide: read it once, apply it to
// every serial the workbook hands back.
if Book.Date1904 then
Writeln('workbook uses the 1904 date system')
else
Writeln('workbook uses the 1900 date system');
Sheet := Book.Sheets[0];
for r := 1 to 10 do
begin
Cell := Sheet.Cells[r, 1];
// A date is only a date when its format says so; the same numeric
// value with a plain format is just a quantity.
Writeln(Format('row %d value=%s numFmt=%d code="%s"',
[r, VarToStr(Cell.Value), Cell.NumberFormatIndex, Cell.NumberFormat]));
end;
finally
Book.Free;
end;
end;O lado do BIFF legado possui uma armadilha adicional que vale a pena mencionar. Em um fluxo .xls mais antigo, uma sequência de células numéricas adjacentes pode ser compactada em um único registro de múltiplas células, o MULRK, que armazena vários valores com suas referências de formato em uma estrutura. Células de datas armazenadas dessa forma não deixam de ser datas por estarem compactadas, de modo que o mesmo teste de ID de formato precisa alcançar o interior do registro de múltiplas células e ser aplicado por célula, e o deslocamento de 1904 ainda governa cada serial resultante. Um leitor que inspeciona apenas registros numéricos independentes, ignorando os compactados, silenciosamente transformará uma coluna de datas em uma coluna de inteiros.
Mapeando seriais para TDateTime na prática
Uma vez que a verificação de formato confirma uma data e a flag Date1904 é conhecida, a conversão é mecânica. Um valor que o HotXLS já retorna como um varDate é um TDateTime que você pode usar diretamente. Um valor que chega como um Double simples, o que acontece quando a origem grava um serial sem um formato de data reconhecido, é convertido lendo-o como uma contagem de dias no eixo de 1900 e, no caso de uma pasta de trabalho de 1904, subtraindo primeiro o deslocamento de 1462 dias para que as épocas se alinhem. Seguindo o caminho oposto, atribuir um TDateTime a uma célula armazena o serial baseado em 1900, e o HotXLS aplica o mesmo deslocamento de 1462 dias ao salvar quando a pasta de trabalho está marcada como 1904, de modo que o arquivo salvo exiba a data pretendida, em vez de uma data com quatro anos de diferença.
Defina a flag deliberadamente quando você gerar uma pasta de trabalho. O padrão deixa Date1904 falso, o que corresponde ao Excel para Windows e é quase sempre o que você deseja; defina-o como verdadeiro apenas quando estiver reproduzindo uma pasta de trabalho originária do Mac ou um sistema subsequente exigir especificamente o eixo de 1904. A única regra que evita toda a classe de erros de quatro anos é a consistência: escolha a época uma vez por pasta de trabalho, grave todas as datas sob ela e leia cada serial de volta sob a flag que o arquivo realmente carrega.
As datas são apenas uma coluna em uma história mais ampla sobre o que uma célula realmente armazena. A camada de metadados vizinha, o título, o autor e as marcações de tempo que acompanham a grade, são abordados em nosso artigo sobre metadados de pasta de trabalho e propriedades do documento, onde os mesmos valores Created e Modified são armazenados como TDateTime com a mesma convenção de valor não definido igual a zero. Quando uma data é o resultado de um cálculo em vez de um valor armazenado, as regras de avaliação em nosso artigo sobre o mecanismo de fórmulas e funções personalizadas determinam o serial que o formato renderiza. Ambos trabalham sobre o mesmo modelo de data fornecido no Componente HotXLS para Delphi e C++Builder, que lê e grava datas XLS e XLSX sem automação do Excel.