Nyisson meg egy táblázatot, kattintson egy cellára, amely a 2026-06-19 értéket mutatja, és a képletsor továbbra is dátumként olvassa azt. Olvassa be ugyanezt a cellát Delphiből, és a 46192 számot kapja. Mindkét nézet helyes, mert az Excel soha nem tárolt dátumot abban a cellában. Egy sorozatszámot tárolt, a napok számát, és csatolt hozzá egy számformátumot (number format), amely arra utasítja a képernyőt, hogy a számlálót naptári dátumként renderelje le. A cellaértékben nincs dátum típus. Van egy szám és egy megjelenítési szabály, és a megjelenítési szabály az egyetlen dolog, ami megkülönbözteti a dátumot a sima mennyiségtől.
Ez a szétválasztás a gyökere minden dátumhibának, amelyet a táblázatkezelő könyvtárnak el kell kerülnie. A sorozatszám önmagában nem mondja meg, milyen nap van, mert nem mondja meg, mikor volt a nulladik nap. Ugyanaz a szám két, egymástól négy évre lévő dátumot jelent egyetlen munkafüzet-flagtől függően. Egy dátumként visszaolvasandó szám pedig sima mennyiségként fog megjelenni, hacsak valami meg nem vizsgálja a formátumát és fel nem ismeri a dátummintát. Így épül fel a dátummodell a HotXLS-ben, és ennek megvan a maga oka.
A dátumcella egy szám plusz egy formátum
Az Excel a dátumot az egy adott korszak óta eltelt napok számaként tárolja, a napszakot pedig a tizedes részben. A dél a sorozatszámon .5 értéket hordoz. Az egész rész a napok száma. A tárolt értékben semmi sem utal az időbeli jellegre. Ami megjelöli, az a cella számformátuma: az ECMA-376 ezt numFmt-nek nevezi, és az a cella, amelynek formátumkódja dátum- vagy időmintát ír le, dátumként jelenik meg. Húzza le a formátumot, és ugyanaz a cella számot fog mutatni; a mögöttes érték soha nem változott.
Ezért van az, hogy a cellaérték beolvasása olyan Variant-ot ad vissza, amely lehet varDate vagy sima Double, és ezért a cellán lévő számformátum az a jelzés, amely eldönti, mit értett alatta a harmadik fél. Amikor a HotXLS megnyit egy XLSX fájlt, a cella a Value és a NumberFormatIndex értékét is beviszi a TXLSXCell-be, a formátumindex pedig az a jelzés, amelyet megvizsgálva megtudhatja, hogy a szám dátum-e.
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;Két korszak, 1462 napos eltéréssel
Az alapértelmezett dátumrendszer, amelyet minden Windows munkafüzet használ, 1899 legvégétől számol, így az 1-es sorozatszám 1900 első napjára esik. A másik rendszer a korai Macintosh-ig nyúlik vissza, és 1904 kezdetétől számol, így az 1-es sorozatszáma négy évvel és egy nappal későbbre esik. A munkafüzet egyetlen flagben rögzíti, melyik rendszert használja. Egy OOXML csomagban ez a flag a date1904 a munkafüzet részen; a HotXLS ezt a munkafüzet Date1904 tulajdonságaként hozza a felszínre.
A két korszak közötti különbség pontosan 1462 nap. Ez négy naptári év (három 365 napos és egy 366 napos, összesen 1461), plusz még egy nap a két nap-nulla konvenció közötti eltérés miatt. Ez a szám fix, fejben tartható. Jelentősége az, hogy nem nulla. Egy 1904-es munkafüzetből kimásolt és 1900-as szabályok szerint értelmezett sorozatszám (vagy fordítva) minden dátumot 1462 nappal eltol, ami éppen négy évnél valamivel nagyobb hibás dátumként jelenik meg, és könnyű sérült adatnak vélni.
Mivel a Delphi saját TDateTime értéke az 1900-as konvencióhoz van rögzítve, az Excel sorozatszámait TDateTime-re leképező könyvtárnak minden alkalommal 1462-vel el kell tolnia az értékeket mindkét irányban, amikor a munkafüzet 1904-re van állítva. Egy 1904-es sorozatszám beolvasásakor vonjon le 1462-t, mielőtt TDateTime-ként kezelné; ha TDateTime értéket ír egy 1904-es munkafüzetbe, vonjon le 1462-t a sorozatszámból, hogy az Excel a kívánt napot renderelje le. A HotXLS ezt az eltolást belsőleg alkalmazza, amikor dátumértékeket szerializál egy olyan munkafüzethez, amelynek Date1904 beállítása aktív, így a TDateTime-ként hozzárendelt érték ugyanarra a naptári napra fog visszaállni a képernyőn.
A szándékos 1900-as szökőév-sajátosság
Létezik egy híres furcsaság az 1900-as rendszerben. Az Excel szökőévként kezeli 1900-at, és valós dátumként fogadja el 1900. február 29-ét, a 60-as sorozatszámon. Az 1900-as év nem volt szökőév, mert a százados évek csak akkor szökőévek, ha oszthatók 400-zal, és 1900 nem az. A fantom-nap egy szándékos kompatibilitási viselkedés, amelyet egy korai táblázatkezelőtől örököltünk, amely ezzel a hibával szállított, és azóta is megmaradt, hogy a sorozatszám-aritmetika évtizedeken át azonos maradjon a fájlok között.
A gyakorlati következmény kicsi, de valós: minden 1900. március 1-jén vagy azután lévő dátum esetében a sorozatszám eggyel magasabb, mint amit egy szigorúan helyes napszámlálás adna, mert a nem létező február 29. elfogyasztott egy számot. A táblázatkezelő könyvtár inkább reprodukálja ezt a furcsaságot ahelyett, hogy javítaná, mert az Excel aritmetikájának pontos egyezése a teljes feladat. A javítása minden modern dátumot egy nappal eltolna attól, amit az Excel mutat, ami rosszabb eredmény, mint egy negyvenezer napos, elavult off-by-one hiba cipelése, amelyet az üzleti életben egyetlen valós dátum sem érint. Az 1904-es rendszernek nincs megfelelő fantom-napja, amiért egyes műhelyek történelmilegek preferálták azt.
Dátum észlelése a numFmt-ből
Amikor egy szám érkezik egy más alkalmazás által írt fájlból, annak formátuma az egyetlen bizonyíték arra, hogy dátumnak szánták. Az ECMA-376 beépített formátum-azonosítók blokkját rendeli hozzá, amelyek jelentése fix a specifikációban, a dátum- és időformátumok pedig ismert tartományokat foglalnak el. A 14-től 22-ig terjedő azonosítók az általános területi dátum- és időformátumok, az ismert m/d/yyyy, h:mm és rokonaik. A 45-től 47-ig terjedők az eltelt idő formátumai. További két sáv, a 27-től 36-ig és az 50-től 58-ig terjedők a CJK naptárakhoz használt területi-specifikus dátum- és időformátumok, amelyeket az ECMA-376 18.8.30 határoz meg. Az a cella, amelynek számformátum-azonosítója ezen tartományok valamelyikébe esik, dátum- vagy időcella.
A beépített azonosítók lefedik a gyakori eseteket, de az egyedieket nem. Amikor egy munkafüzet saját formátumkódot határoz meg, például egy nem szabványos sorrendet vagy honosított hónapnevet, az azonosító a beépített tartomány felett van, és a munkafüzet számformátum-táblájára mutat. Ezeknél a dátum felismerése a formátumkód karakterláncának beolvasását és dátum-tokenek keresését jelenti. A HotXLS mindkét ellenőrzést egyetlen belső predikátumba, az XlsxNumFmtIsDate függvénybe fogja össze, amely azonnal true értéket ad vissza a beépített dátumtartományokra, egyébként pedig a XlsxFormatCodeIsDate segítségével elemzi az egyedi formátumkódot. Ennek nyilvános oldala a cella NumberFormat karakterlánca és a NumberFormatIndex tulajdonsága, amelyek a feloldott formátumkódot és a tesztelni kívánt azonosítót is megadják.
Miért nem tudja a formátumelemző csak leolvasni a d és m karaktereket
A formátumkód elemzése dátum-tokenek után egyszerűnek tűnik, amíg vissza nem emlékszik arra, mi más él még egy számformátumban. A dátumokat betűző karakterek (a nap, hónap, év, óra és másodperc d, m, y, h és s karakterei) naiv keresése félre fog menni két olyan struktúrán, amelyek egyáltalán nem dátum-tokenek.
Az első az idézőjeles karakterlánc-literál. A számformátum beágyazhat szöveges literált idézőjelek között, így egy olyan pénzügyi formátum, mint a #,##0 "MM" az M és M karaktereket fűzi hozzá a számhoz anélkül, hogy bármilyen időbeli jelentése lenne. Az a kereső, amely az idézőjelek közötti betűket hónap-tokennek számolja, tévesen dátumként jelölné meg ezt a pénznemformátumot. A második a szögletes zárójeles rész. A számformátumok irányelveket hordoznak szögletes zárójelek között, színneveket, mint a [Red], összehasonlítási feltételeket, mint a [>1000], területi beállítások címkéit és az eltelt idő jelölőit: [h] és [mm]. Egyes zárójeles tartalmak tartalmaznak dátumbetűket, mások nem, és a zárójeles szöveg ugyanolyan kezelése, mint a formátum törzsének, téves pozitívokhoz és kihagyott esetekhez is vezet.
A helyes elemző karakterenként halad végig a formátumkódon, nyomon követve, hogy idézőjeles literálon belül van-e, és milyen mélyen van a szögletes zárójelek egymásba ágyazásában, valamint tiszteletben tartja a visszaper (backslash) escape karaktert is, amely idézi a következő karaktert. Csak az idézőjeles literálon kívüli és a zárójeles részen kívüli, nem escape-elt dátumbetű számít valós dátum-tokennek. Pontosan így szkennel a XlsxFormatCodeIsDate: az idézőjel átvált egy literálon belüli állapotot, amely elnyomja a token-észlelést a záró idézőjelig, a visszaper átugorja a következő karaktert, a zárójel-mélység számláló pedig elnyomja az észlelést a [...] futásokon belül. A nyereség az, hogy a #,##0 "MM" helyesen számformátumként olvasható be, míg a szigorú egyedi kód, amely csak egyetlen m vagy d karaktert tartalmaz az idézőjeleken kívül, továbbra is helyesen dátumként ismerhető fel.
Dátumok beolvasása harmadik féltől származó fájlokból
A fentiek mindegyike egyetlen munkafolyamatban konvergál: a más alkalmazás által írt szám visszaalakítása olyan dátummá, amelyben megbízhat. A sorozatszám megadja a napok számát, a munkafüzet Date1904 flagje megmondja, melyik korszakból mérjük a számlálást, a cella számformátum-azonosítója vagy egyedi kódja pedig az egyetlen bizonyíték arra, hogy a számot eleve dátumnak szánták. Hagyja el a három közül bármelyiket, és hihető rossz választ kap a látható hiba helyett.
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;Az örökölt BIFF oldalnak van egy extra csapdája, amelyet érdemes megnevezni. Egy régebbi .xls adatfolyamban a szomszédos numerikus cellák sora egyetlen többcellás rekordba csomagolható, a MULRK-ba, amely több értéket tárol a formátumhivatkozásokkal együtt egyetlen struktúrában. Az így tárolt dátumcellák nem kevésbé dátumok attól, hogy be vannak csomagolva, így ugyanazt a formátum-azonosító tesztet kell alkalmazni cellánként, és az 1904-es eltolás továbbra is irányít minden általa hozott sorozatszámot. Az az olvasó, amely csak a különálló számrekordokat vizsgálja meg, és kihagyja a csomagoltakat, csendben egész számok oszlopává változtatja a dátumok oszlopát.
Sorozatszámok leképezése TDateTime-ra a gyakorlatban
Amint a formátumellenőrzés megerősíti a dátumot és a Date1904 flag ismert, a konverzió mechanikus. Az az érték, amelyet a HotXLS már varDate-ként ad vissza, egy közvetlenül használható TDateTime. Az az érték, amely sima Double-ként érkezik (ami akkor fordul elő, ha a forrás sorozatszámot írt felismert dátumformátum nélkül), úgy konvertálódik, hogy napok számaként olvassuk le a 1900-as tengelyen, 1904-es munkafüzet esetén pedig előbb levonjuk az 1462 napos eltolást, hogy a korszakok egy vonalba kerüljenek. A másik irányba haladva, a TDateTime hozzárendelése a cellához az 1900-as alapú sorozatszámot tárolja el, és a HotXLS mentéskor alkalmazza ugyanezt az 1462 napos eltolást, ha a munkafüzet 1904-esként van megjelölve, így a mentett fájl a kívánt naptári napot mutatja a négy évvel eltolt helyett.
Állítsa be a flaget szándékosan, amikor munkafüzetet generál. Az alapértelmezett beállítás a Date1904-et false értéken hagyja, ami megegyezik a Windows Excel alapértelmezésével, és szinte mindig ezt szeretné; csak akkor állítsa true-ra, ha Mac-eredetű munkafüzetet reprodukál, vagy egy downstream rendszer kifejezetten az 1904-es tengelyt várja. Az egyetlen szabály, amely megakadályozza a négyéves hibák egész osztályát, a következetesség: válassza ki a korszakot egyszer munkafüzetenként, írjon be minden dátumot alatta, és olvasson vissza minden sorozatszámot a fájl által ténylegesen hordozott flag alatt.
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.