Отворете електронна таблица, щракнете върху клетка, която показва 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, а 1900 не се дели. Фантомният ден е умишлено поведение за съвместимост, наследено от ранна електронна таблица, която е доставена с този бъг, и е запазено оттогава, за да може серийната аритметика да остане идентична в продължение на десетилетия.
Практическото следствие е малко, но реално: за всяка дата на или след 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. Клетка, чийто идентификатор на формат на число попада в някой от тези диапазони, е клетка с дата или час.
Вградените идентификатори покриват общите случаи, но не и персонализираните. Когато работна книга дефинира свой собствен код за формат (например нестандартна подредба или локализирано име на месец), идентификаторът е над вградения диапазон и сочи към таблицата с числови формати на работната книга. За тях разпознаването на дата означава четене на низа с кода на формата и търсене на маркери (tokens) за дата. HotXLS обединява двете проверки в един вътрешен предикат XlsxNumFmtIsDate, който връща true веднага за вградените диапазони за дати, а в противен случай анализира персонализирания код на формата чрез XlsxFormatCodeIsDate. Публичната страна на това е низът NumberFormat на клетката и нейният NumberFormatIndex, които ви дават както разрешения код на формата и идентификатора за тестване.
Защо анализаторът на формати не може просто да сканира за d и m
Анализирането на код на формат за маркери за дата изглежда тривиално, докато не си спомните какво друго съществува в числовия формат. Едно наивно търсене на буквите, които изписват дати – d, m, y, h и s за ден, месец, година, час и секунда – ще се провали при две структури, които изобщо не са маркери за дати.
Първата е цитираният низ литерал. Числовият формат може да вгражда литерален текст в двойни кавички, така че финансов формат като #,##0 "MM" добавя знаците M и M към число без никакво времево значение. Скенер, който брои буквите в кавичките като маркери за месец, погрешно би маркирал този валутен формат като дата. Втората е секцията в квадратни скоби. Числовите формати носят директиви в квадратни скоби – имена на цветове като [Red], условия за сравнение като [>1000], локални маркери и маркери за изминало време [h] и [mm]. Някои съдържания в скоби поддържат букви за дати, а други не, и третирането на съдържанието в скоби по същия начин като тялото на формата води както до фалшиви положителни резултати, така и до пропуснати случаи.
Правилният анализатор обхожда кода на формата знак по знак, проследявайки дали се намира в цитиран литерал и колко дълбоко е вложен в скоби, като същевременно зачита наклонената черта (backslash), която цитира един следващ знак. Само нецитирана буква за дата, намерена извън какъвто и да е низ литерал и извън всяка секция в скоби, се счита за реален маркер за дата. Точно така сканира 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 и, за работна книга 1904, първо се изважда отместването от 1462 дни, за да се изравнят епохите. В обратната посока – присвояването на TDateTime към клетка съхранява серийния номер на базата на 1900 г., а HotXLS прилага същата 1462-дневна промяна при записване, когато работната книга е маркирана с флаг 1904, така че записаният файл да показва датата, която сте имали предвид, а не отместена с четири години.
Задавайте флага умишлено, когато генерирате работна книга. Стойността по подразбиране оставя Date1904 false, което съответства на Excel за Windows и почти винаги е това, което искате; задайте го на true само когато възпроизвеждате работна книга с произход от Mac или когато система по веригата изрично очаква оста 1904. Единственото правило, което предотвратява целия клас грешки с четири години разлика, е последователността: изберете епохата веднъж за работна книга, записвайте всяка дата под нея и четете всеки сериен номер обратно под флага, който файлът действително носи.
Датите са една колона в по-широката история за това какво всъщност съдържа клетката. Съседният метаслой – заглавието, авторът и клеймото за време, които се движат заедно с мрежата, е разгледан в нашата статия за метаданни на работна книга и свойства на документа, където същите стойности Created и Modified се съхраняват като TDateTime със същата конвенция за незададено равенство на нула. Когато датата е резултат от изчисление, а не от съхранена стойност, правилата за оценка в нашата статия за механизъм за формули и персонализирани функции определят серийния номер, който форматът след това изобразява. И двете работят над същия модел за дати, който се доставя в HotXLS Component за Delphi и C++Builder, който чете и записва дати в XLS и XLSX без автоматизация на Excel.