Откройте электронную таблицу, выберите ячейку с датой 2026-06-19, и в строке формул отобразится дата. Прочитайте эту же ячейку в программе Delphi, и вы получите число 46192. Оба значения верны, так как Excel никогда не хранил дату в этой ячейке. Он записал порядковый номер (количество дней) и применил числовой формат, указывающий на необходимость отображения этого числа как календарной даты. Значение ячейки не имеет типа даты: в ней записано число и правило отображения, которое и отличает дату от обычного количества
Это разделение является причиной всех ошибок при работе с датами, которые приходится обходить библиотекам электронных таблиц. Само по себе число не указывает на конкретный день, поскольку точка начала отсчета неизвестна. Одно и то же число может означать две даты с разницей в четыре года в зависимости от одного флага книги. И дата будет считываться как обычное число, пока библиотека не проанализирует формат ячейки и не распознает шаблон даты. Именно так устроена модель дат в HotXLS
Ячейка даты представляет собой число с форматом отображения
Excel хранит дату как количество дней с начала эпохи отсчета, при этом время суток записывается в дробной части (например, полдень выражается как .5). Целая часть указывает на количество дней. Никакие признаки внутри значения не указывают на его отношение ко времени. Назначение определяет числовой формат ячейки (ECMA-376 называет его numFmt): ячейка отображается как дата, если ее код формата содержит шаблон даты или времени. Если удалить этот формат, ячейка покажет обычное число, при этом внутреннее значение останется неизменным
Поэтому чтение значения ячейки возвращает тип Variant, который может быть датой varDate или числом Double, а числовой формат служит маркером для определения намерений создателя документа. Когда HotXLS открывает файл XLSX, ячейка считывает свойства Value и NumberFormatIndex в объект TXLSXCell, и этот индекс формата указывает на то, является ли число датой
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;Две эпохи отсчета с разницей в 1462 дня
Стандартная система дат, используемая всеми книгами Windows, ведет отсчет с самого конца 1899 года, поэтому число 1 соответствует первому дню 1900 года. Другая система берет начало на ранних компьютерах Macintosh и считает с начала 1904 года, из-за чего значение 1 наступает на четыре года и один день позже. Книга хранит сведения об используемой системе в специальном флаге (в пакете OOXML это флаг date1904 в части книги). HotXLS предоставляет к нему доступ через свойство книги Date1904
Разница между двумя эпохами отсчета составляет ровно 1462 дня. Это четыре календарных года (три по 365 дней и один високосный в 366 дней, всего 1461 день) плюс еще один день смещения между началом отсчета в обеих системах. Это число фиксировано, его легко запомнить. Его важность обусловлена тем, что оно не равно нулю. Копирование числа из книги 1904 года и его чтение по правилам 1900 года (или наоборот) смещает дату на 1462 дня, что выражается в ошибке примерно в четыре года и может быть ошибочно принято за повреждение данных
Поскольку собственный тип TDateTime в Delphi привязан к соглашению 1900 года, библиотека, сопоставляющая числа Excel с TDateTime, должна сдвигать значение на 1462 дня в обоих направлениях при наличии флага 1904. При чтении значения 1904 вычтите 1462 перед обработкой в качестве TDateTime. При записи TDateTime в книгу 1904 вычтите 1462 из числа, чтобы Excel отобразил нужный день. HotXLS применяется этот сдвиг автоматически при сериализации значений дат для книг со свойством Date1904, гарантируя соответствие дат на экране исходным значениям TDateTime
Намеренная ошибка високосного 1900 года
В системе 1900 года есть известная особенность. Excel считает 1900 год високосным и принимает дату 29 февраля 1900 года как корректную с порядковым номером 60. На самом деле 1900 год не был високосным, так как вековые годы являются високосными только тогда, когда они делятся на 400. Этот фантомный день оставлен для совместимости с первыми электронными таблицами, содержавшими эту ошибку, чтобы математика дат оставалась неизменной во всех файлах
Практические последствия этого незначительны: для любой даты начиная с 1 марта 1900 года порядковый номер на единицу больше реального количества прошедших дней, так как несуществующее 29 февраля заняло номер. Библиотека воспроизводит эту особенность, а не пытается ее исправить, так как ее задача состоит в полном соответствии расчетам Excel. Попытка исправить ошибку привела бы к сдвигу всех современных дат на один день относительно Excel. В системе 1904 года фантомного дня нет, что служило причиной выбора этой системы некоторыми компаниями в прошлом
Определение даты на основе numFmt
Когда число считывается из чужого файла, его формат служит единственным доказательством того, что это дата. Стандарт ECMA-376 резервирует блоки встроенных идентификаторов форматов, назначение которых фиксировано, при этом форматы даты и времени занимают определенные диапазоны. Идентификаторы с 14 по 22 описывают стандартные форматы даты и времени (такие как m/d/yyyy, h:mm и подобные). Идентификаторы с 45 по 47 относятся к форматам прошедшего времени. Еще два блока (с 27 по 36 и с 50 по 58) описывают региональные форматы даты и времени для календарей CJK, определенные в ECMA-376 18.8.30. Ячейка с идентификатором числового формата из этих диапазонов является ячейкой даты или времени
Встроенные идентификаторы покрывают стандартные случаи, но не индивидуальные настройки. Когда в книге объявляется пользовательский код формата (например, нестандартный порядок или локализованное имя месяца), его идентификатор лежит выше встроенного диапазона и указывает на таблицу числовых форматов книги. В таких случаях для распознавания даты нужно проанализировать строку кода формата на предмет наличия признаков даты. HotXLS объединяет эти проверки во внутреннем предикате XlsxNumFmtIsDate, который возвращает истину для встроенных диапазонов и анализирует пользовательские форматы через функцию XlsxFormatCodeIsDate. Снаружи эти параметры доступны через свойства ячейки NumberFormat и NumberFormatIndex
Почему анализатор формата не может просто искать символы d и m
Анализ кода формата на наличие маркеров даты кажется простым, пока вы не вспомните о других элементах числового формата. Простой поиск букв дат (d, m, y, h и s для дня, месяца, года, часа и секунды) даст ложные срабатывания на двух структурах, не имеющих отношения к датам
Первой структурой является текстовый литерал в кавычках. Числовой формат может включать текст в двойных кавычках, поэтому финансовый формат вроде #,##0 "MM" добавляет символы M и M к числу без какого-либо временного значения. Простой поиск посчитал бы буквы в кавычках маркерами месяца и ошибочно определил бы финансовый формат как дату. Вторая структура представляет собой блоки в квадратных скобках. Форматы чисел содержат директивы в квадратных скобках: названия цветов вроде [Red], условия сравнения вроде [>1000], региональные теги и маркеры прошедшего времени [h] и [mm]. Часть содержимого скобок может содержать буквы дат, а часть нет, поэтому одинаковая обработка текста в скобках и тела формата ведет к ошибкам
Правильный парсер последовательно обходит символы кода формата, отслеживая состояние внутри кавычек, уровень вложенности квадратных скобок и экранирование символов обратным слэшем. Настоящими маркерами даты считаются только неэкранированные буквы дат вне литералов и квадратных скобок. Именно так работает функция XlsxFormatCodeIsDate: кавычки переключают состояние литерала, подавляя распознавание токенов до закрывающей кавычки, обратный слэш пропускает следующий символ, а счетчик квадратных скобок отключает проверку внутри блоков [...]. Это позволяет правильно определять #,##0 "MM" как числовой формат, но при этом распознавать дату в лаконичном коде, содержащем только одиночные символы m или d вне кавычек
Чтение дат из сторонних файлов
Все описанное сводится к одной задаче: преобразованию числа, записанного другим приложением, в корректную дату. Порядковое число дает количество дней, флаг книги Date1904 указывает на эпоху отсчета, а идентификатор числового формата или пользовательский код ячейки служат единственным подтверждением того, что число изначально задумывалось как дата. Уберите любую из этих деталей, и вы получите неверную дату вместо явной ошибки
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;На пути устаревшего формата BIFF есть еще одна ловушка. В старых потоках .xls последовательность соседних числовых ячеек может упаковываться в одну запись для нескольких ячеек, а именно MULRK, хранящую значения и ссылки на форматы в одной структуре. Содержащиеся в ней ячейки дат остаются датами, поэтому проверка формата должна выполняться для каждой ячейки внутри структуры, а сдвиг 1904 года по-прежнему применяется к каждому значению. Программа чтения, проверяющая только отдельные числовые записи и игнорирующая упакованные структуры, автоматически превратит колонку дат в обычные целые числа
Практика сопоставления порядковых чисел с TDateTime
После подтверждения формата даты и определения флага Date1904 преобразование выполняется автоматически. Значение, возвращаемое HotXLS как varDate, представляет собой TDateTime, готовый к использованию. Значение, полученное как Double (когда источник записал число без распознаваемого формата даты), преобразуется путем считывания его как количества дней по оси 1900 года с предварительным вычитанием сдвига в 1462 дня для книг 1904 года. При записи в обратном направлении присвоение TDateTime ячейке сохраняет значение по оси 1900 года, а HotXLS применяет сдвиг в 1462 дня при сохранении книги с флагом 1904, чтобы файл отображал верную дату
Устанавливайте флаг осознанно при создании книги. По умолчанию свойство Date1904 отключено, что соответствует поведению Excel для Windows. Включайте его только при воссоздании файлов для Mac или при явных требованиях со стороны внешних систем. Единственным правилом для предотвращения ошибок сдвига на четыре года является согласованность: выберите эпоху один раз для книги, записывайте все даты в ней и считывайте порядковые номера с учетом флага открытого файла
Даты представляют собой лишь часть информации о содержимом ячеек. Сопутствующие метаданные (название, автор, временные метки) описаны в нашей статье о метаданных книг и свойствах документов, где значения создания и изменения хранятся как TDateTime. Когда дата является результатом вычисления, а не хранимым значением, правила из статьи о движке формул и пользовательских функциях определяют числовое значение для отображения. Обе функции используют единую модель дат, поставляемую в составе компонента HotXLS для Delphi и C++Builder для чтения и записи дат без автоматизации Excel