Abra uma folha de cálculo, clique numa célula que apresente 2026-06-19 e a barra de fórmulas continuará a indicar uma data. Leia a mesma célula a partir do Delphi e obterá o número 46192. Ambas as perspetivas estão corretas, porque o Excel nunca armazenou uma data nessa célula. Guardou sim um número sequencial, isto é, uma contagem de dias, e associou-lhe um formato de número que indica ao ecrã para renderizar essa contagem como uma data de calendário. Não existe um tipo de dados de data no valor da célula. Existe um número e uma regra de exibição, e a regra de exibição é o único fator que distingue uma data de uma quantidade vulgar.
Essa separação é a causa raiz de todos os erros de data que uma biblioteca de folhas de cálculo tem de evitar. Um valor sequencial isolado não indica que dia é, porque não esclarece qual foi o dia zero. O mesmo número representa duas datas com quatro anos de diferença, dependendo de uma única opção (flag) do livro. E um número que deveria ser lido como uma data será interpretado como uma quantidade simples se não houver um mecanismo que inspecione o seu formato e reconheça um padrão de data. É assim que o modelo de data do HotXLS está construído, e é por isso que tem de ser assim.
Uma célula de data é um número mais um formato
O Excel armazena uma data como o número de dias decorridos desde uma época (epoch), com a hora do dia na parte fracionária. O meio-dia num número sequencial contém .5. A parte inteira é a contagem de dias. Nada no valor guardado o sinaliza como temporal. O que o define é o formato de número da célula: a norma ECMA-376 designa-o por numFmt, e uma célula cujo código de formato represente um padrão de data ou hora é apresentada como uma data. Remova o formato e a mesma célula exibirá um número; o valor subjacente nunca se alterou.
Esta é a razão pela qual ler o valor de uma célula devolve um Variant que pode ser um varDate ou um Double simples, e porque o formato de número na mesma célula representa o sinalizador para decidir o que um terceiro pretendia. Quando o HotXLS abre um ficheiro XLSX, uma célula carrega o seu Value e o seu NumberFormatIndex para o TXLSXCell, e o índice de formato é o detalhe que 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 data predefinido, utilizado por cada livro no Windows, conta a partir do final de 1899, pelo que o valor sequencial 1 recai no primeiro dia de 1900. O outro sistema remonta aos primórdios do Macintosh e conta a partir do início de 1904, pelo que o seu valor sequencial 1 ocorre quatro anos e um dia mais tarde. Um livro regista o sistema utilizado numa opção específica. Num pacote OOXML, esse sinalizador é date1904 na secção do livro; o HotXLS expõe-no como a propriedade Date1904 do livro.
A diferença entre as duas épocas é de exatamente 1462 dias. Trata-se de quatro anos civis (três de 365 dias e um bissexto de 366, totalizando 1461 dias), mais um dia relativo ao desvio entre as duas convenções de dia zero. O número é fixo. A sua relevância prende-se com o facto de não ser zero. Um valor sequencial copiado de um livro de 1904 e interpretado sob as regras de 1900, ou o inverso, resulta numa data com 1462 dias de desvio (pouco mais de quatro anos), sendo fácil confundi-lo com dados corrompidos.
Como o TDateTime do próprio Delphi está ancorado à convenção de 1900, uma biblioteca que associe valores sequenciais do Excel a TDateTime tem de compensar com o desvio de 1462 em ambas as direções sempre que o livro estiver configurado para 1904. Ao ler um valor sequencial de 1904, subtraia 1462 antes de o tratar como TDateTime; ao escrever um TDateTime num livro de 1904, subtraia 1462 ao valor sequencial para que o Excel apresente o dia pretendido. O HotXLS aplica esta compensação internamente ao serializar valores de data para um livro cuja propriedade Date1904 esteja ativa, assegurando que o valor atribuído como TDateTime coincida com o mesmo dia do calendário no ecrã.
A especificidade deliberada do ano bissexto de 1900
Existe um pormenor conhecido no sistema de 1900. O Excel trata o ano 1900 como bissexto e aceita o dia 29 de fevereiro de 1900 como uma data real, com valor sequencial 60. Contudo, o ano 1900 não foi bissexto (dado que os anos centenários apenas são bissextos se forem divisíveis por 400). O dia fantasma é uma compatibilidade deliberada herdada de uma folha de cálculo antiga que continha o bug, mantida para assegurar que a aritmética sequencial permaneça idêntica ao longo de décadas de ficheiros.
A consequência prática é reduzida mas real: para qualquer data a partir de 1 de março de 1900, o valor sequencial é uma unidade superior ao que uma contagem rigorosa de dias indicaria, porque o inexistente 29 de fevereiro consumiu um número. Uma biblioteca de folhas de cálculo reproduz este comportamento em vez de o corrigir, porque o seu objetivo é replicar a aritmética do Excel com precisão. Corrigi-lo colocaria todas as datas modernas com um dia de desvio em relação ao que o Excel apresenta, o que seria pior do que manter um desvio com mais de quarenta mil dias de idade que nenhuma data de negócio real utiliza. O sistema de 1904 não apresenta um dia fantasma equivalente, razão pela qual algumas empresas historicamente o preferiram.
Detetar uma data a partir de numFmt
Quando um número provém de um ficheiro escrito por outra aplicação, o seu formato representa o único indício de que se trata de uma data. A norma ECMA-376 define intervalos de IDs de formatos integrados (built-in formats) cujos significados são fixos na especificação. Os formatos de data e hora ocupam intervalos conhecidos: os IDs de 14 a 22 correspondem aos formatos gerais de data e hora de cada localidade, como m/d/yyyy, h:mm e afins; os IDs de 45 a 47 referem-se a formatos de tempo decorrido. Dois intervalos adicionais, de 27 a 36 e de 50 a 58, destinam-se a formatos específicos utilizados para calendários CJK. Uma célula cujo ID de formato de número se situe nestas gamas constitui uma célula de data ou hora.
Os IDs integrados cobrem os casos comuns mas não os personalizados. Quando um livro define o seu próprio código de formato, por exemplo uma ordenação invulgar ou um nome de mês localizado, o ID situa-se acima dos integrados e aponta para a tabela de formatos de números do livro. Para estes, identificar uma data implica analisar o código do formato e procurar marcadores (tokens) de data. O HotXLS consolida ambas as verificações num predicado interno, XlsxNumFmtIsDate, que retorna true imediatamente para os intervalos de datas integrados e, caso contrário, analisa o código personalizado através de XlsxFormatCodeIsDate. O lado público desta lógica reside nas propriedades NumberFormat e NumberFormatIndex da célula, que disponibilizam o código de formato resolvido e o ID para teste.
Porque é que o analisador de formato não pode apenas procurar por d e m
Analisar um código de formato à procura de marcadores de data parece trivial até recordar o que mais reside num formato de número.
O primeiro elemento é o literal de string delimitado por aspas. Um formato de número pode incorporar texto literal entre aspas duplas, de modo a que um formato financeiro como #,##0 "MM" acrescente os caracteres M e M a um número sem qualquer significado temporal. Um analisador ingénuo que contasse essas letras como marcadores de mês classificaria incorretamente esse formato financeiro como uma data. O segundo elemento é a secção entre parênteses retos. Os formatos de número contêm diretivas entre parênteses retos, como nomes de cores (ex.: [Red]), condições de comparação (ex.: [>1000]), etiquetas de localidade e marcadores de tempo decorrido como [h] e [mm]. Alguns conteúdos nestas secções contêm caracteres de data e outros não, e tratá-los de forma idêntica ao corpo do formato resulta em falsos positivos e omissões.
O analisador correto percorre o código do formato caractere a caractere, rastreando se se encontra dentro de uma string literal ou a que profundidade está dentro de parênteses retos, respeitando também a barra invertida (backslash) que serve de escape ao caractere seguinte. Apenas um caractere de data sem escape e situado fora de literais ou parênteses retos conta como um marcador de data real. É exatamente assim que o XlsxFormatCodeIsDate analisa a informação: as aspas alternam um estado que suprime a deteção até às aspas de fecho, uma barra invertida ignora o caractere seguinte e um contador de parênteses retos silencia a deteção dentro de secções [...]. Graças a isto, o formato #,##0 "MM" é corretamente lido como numérico, enquanto um código personalizado que contenha apenas um m ou d fora de aspas é devidamente identificado como uma data.
Ler datas de ficheiros de terceiros
Tudo o que foi descrito converge num fluxo de trabalho: converter um número escrito por outra aplicação de volta numa data confiável. O valor sequencial fornece a contagem de dias, o sinalizador Date1904 do livro indica a partir de que época a contagem é medida, e o ID de formato ou código personalizado da célula constitui o único indício de que o número correspondia a uma data. Ignore qualquer um dos três fatores e obterá uma resposta incorreta 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 legado do BIFF apresenta uma armadilha adicional. Num fluxo .xls antigo, um conjunto de células numéricas adjacentes pode ser compactado num único registo multi-célula (o MULRK), que armazena vários valores com as suas referências de formato numa única estrutura. As células de data armazenadas dessa forma continuam a ser datas, pelo que o mesmo teste de ID de formato tem de ser aplicado célula a célula, e a compensação de 1904 continua a governar cada valor sequencial resultante. Um leitor que apenas inspecione registos numéricos independentes, ignorando os compactados, converterá silenciosamente uma coluna de datas numa coluna de inteiros.
Associar valores sequenciais a TDateTime na prática
Uma vez que a validação de formato confirma a data e o sinalizador Date1904 é conhecido, a conversão é mecânica. Um valor devolvido pelo HotXLS como varDate é um TDateTime utilizável diretamente. Um valor que chegue como um Double simples (o que ocorre quando a origem escreveu um valor sequencial sem um formato de data reconhecido) é convertido lendo-o como contagem de dias no eixo de 1900 e, no caso de um livro de 1904, subtraindo primeiro o desvio de 1462 dias. No sentido oposto, atribuir um TDateTime a uma célula armazena o valor sequencial baseado em 1900, e o HotXLS aplica o mesmo desvio de 1462 dias ao gravar se o livro estiver configurado para 1904, assegurando que o ficheiro gravado exiba a data pretendida e não uma data com quatro anos de atraso.
Defina o sinalizador deliberadamente ao gerar um livro. A predefinição deixa o Date1904 como falso, o que corresponde ao Excel para Windows e é quase sempre o pretendido; defina-o como verdadeiro apenas se estiver a replicar um livro com origem em Mac ou se o sistema a jusante exigir especificamente o eixo de 1904. A regra que previne erros de desvios de quatro anos é a consistência: escolha a época uma vez por livro, escreva cada data sob a mesma, e leia cada valor sequencial sob a opção (flag) que o ficheiro efetivamente possui.
Dates are one column in a wider story about what a cell really holds. The neighboring metadata layer, the title and author and timestamps that ride alongside the grid, is covered in our article on workbook metadata and document properties, where the same Created and Modified values are stored as TDateTime with the same unset-equals-zero convention. When a date is the result of a calculation rather than a stored value, the evaluation rules in our article on the formula engine and custom functions determine the serial that the format then renders. Both work over the same date model that ships in the HotXLS Component for Delphi and C++Builder, which reads and writes XLS and XLSX dates without Excel automation.