Відкрийте електронну таблицю, клацніть комірку із значенням 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 дні, що дає похибку трохи більше ніж у чотири роки і що легко прийняти за пошкодження даних.
Оскільки власний тип Delphi TDateTime прив'язаний до конвенції 1900 року, бібліотека, яка відображає серійні номери Excel на TDateTime, повинна застосовувати зміщення на 1462 в обох напрямках, коли книга позначена як 1904. При зчитуванні серійного номера 1904 відніміть 1462 перед його обробкою як TDateTime; при записі TDateTime в книгу 1904 відніміть 1462 із серійного номера, щоб Excel відобразив потрібний вам день. HotXLS застосовує це зміщення всередині під час серіалізації значень дат для книги зі встановленим Date1904, завдяки чому значення, яке ви призначаєте як TDateTime, повертається на екран як той самий календарний день.
Навмисна особливість високосного 1900 року
У системі 1900 року є відома особливість. Excel розглядає 1900 рік як високосний і приймає 29 лютого 1900 року як реальну дату із серійним номером 60. Але 1900 рік не був високосним, оскільки столітні роки є високосними лише тоді, коли діляться на 400, а 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, яка негайно повертає true для вбудованих діапазонів дат, а в інших випадках аналізує користувацький код формату через 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 все одно управляє кожним отриманим серійним номером. Зчитувач, який перевіряє лише окремі записи чисел і пропускає упаковані, непомітно перетворить стовпець дат на стовпець цілих чисел.
Mapping serials to TDateTime in practice
Після того як перевірка формату підтверджує дату, а прапорець Date1904 стає відомим, перетворення стає механічним. Значення, яке HotXLS вже повертає як varDate, є типом TDateTime, який ви можете використовувати безпосередньо. Значення, яке повертається як звичайний Double (що відбувається, коли джерело записує серійний номер без розпізнаного формату дати), конвертується шляхом його читання як кількості днів на осі 1900 року, а для книги 1904 року - спочатку з відніманням зміщення на 1462 дні, щоб епохи збіглися. При зворотному процесі призначення TDateTime комірці зберігає серійний номер 1900, а HotXLS застосовує таке ж зміщення на 1462 дні при збереженні для книги з прапорцем 1904, щоб збережений файл показував обрану вами дату, а не зміщену на чотири роки.
Встановлюйте прапорець свідомо під час створення книги. Стандартно властивість Date1904 є false, що відповідає Excel для Windows і майже завжди є найкращим рішенням; встановлюйте її в true лише тоді, коли відтворюєте книгу Macintosh або коли наступна система очікує саме вісь 1904. Єдине правило, що запобігає появі чотирирічних помилок, - послідовність: виберіть епоху один раз для книги, записуйте кожну дату за нею та зчитуйте кожен серійний номер назад за прапорцем, який книга реально містить.
Дати - це один із стовпців у загальній історії про те, чим є значення комірки. Суміжний шар метаданих - заголовок, автор та мітки часу, які супроводжують сітку, - описано в нашій статті про метадані книги та властивості документа, де ті самі значення Created та Modified зберігаються як TDateTime із тією ж конвенцією неініціалізованого нуля. Коли дата є результатом обчислення, а не збереженого значення, правила обчислення в статті про рушій формул та користувацькі функції визначають серійний номер, який потім відтворює формат. Обидва рішення працюють над тією ж моделлю дат, що постачається в складі компонента HotXLS для Delphi та C++Builder, який зчитує та записує дати XLS та XLSX без автоматизації Excel.