Otvorte tabuľkový procesor, kliknite na bunku, ktorá zobrazuje 2026-06-19, a riadok vzorcov stále ukazuje dátum. Prečítajte rovnakú bunku z Delphi a dostanete číslo 46192. Oba pohľady sú správne, pretože Excel do tejto bunky dátum nikdy neuložil. Uložil sériové číslo, počet dní a pripojil formát čísla, ktorý obrazovke hovorí, aby vykreslila počet ako kalendárny dátum. V hodnote bunky nie je žiadny typ dátumu. Existuje číslo a pravidlo zobrazenia a pravidlo zobrazenia je jediná vec, ktorá odlišuje dátum od obyčajného množstva.
Toto oddelenie je koreňom každej chyby s dátumom, ktorej sa knižnica tabuľkového procesora musí vyhnúť. Samotné sériové číslo nehovorí, aký je deň, pretože nehovorí, čo bolo dňom nula. Rovnaké číslo znamená dva dátumy vzdialené štyri roky v závislosti od jediného príznaku zošita. A číslo, ktoré by sa malo prečítať ako dátum, sa prečíta ako čisté množstvo, pokiaľ niečo neskontroluje jeho formát a nerozpozná vzor dátumu. Takto je postavený model dátumov v HotXLS a preto taký musí byť.
Bunka dátumu je číslo plus formát
Excel ukladá dátum ako počet dní od epochy, pričom čas dňa je v desatinnej časti. Poludnie na sériovom čísle nesie .5. Celá časť je počet dní. Nič v uloženej hodnote ju neoznačuje ako časovú. Čo ju označuje, je formát čísla bunky: ECMA-376 to nazýva numFmt a bunka, ktorej formátový kód vyjadruje vzor dátumu alebo času, sa zobrazuje ako dátum. Odstráňte formát a tá istá bunka ukáže číslo; podkladová hodnota sa nikdy nezmenila.
To je dôvod, prečo čítanie hodnoty bunky vracia Variant, ktorý môže byť varDate alebo obyčajný Double, a prečo formát čísla na tej istej bunke je signálom, ktorý určuje, čo tretia strana zamýšľala. Keď HotXLS otvorí súbor XLSX, bunka nesie svoju hodnotu Value aj index formátu čísla NumberFormatIndex do TXLSXCell, and the format index is what you consult to learn whether the number is a date.
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;Dve epochy vzdialené 1462 dní
Predvolený systém dátumov, ten, ktorý používa každý zošit na systéme Windows, sa počíta od samotného konca roku 1899, takže sériové číslo 1 spadá na prvý deň roku 1900. Druhý systém siaha k raným počítačom Macintosh a počíta od začiatku roku 1904, takže jeho sériové číslo 1 je o štyri roky a jeden deň neskôr. Zošit zaznamenáva, ktorý systém používa, v jednom príznaku. V balíku OOXML je týmto príznakom date1904 na časti zošita; HotXLS ho sprístupňuje ako vlastnosť Date1904 zošita.
Rozdiel medzi týmito dvoma epochami je presne 1462 dní. To sú štyri kalendárne roky (tri s 365 dňami a jeden s 366 dňami, celkovo 1461 dní) plus jeden ďalší deň pre posun medzi dvoma konvenciami dňa nula. Číslo je pevné a môžete ho nosiť v hlave. Jeho dôležitosť spočíva v tom, že nie je nula. Sériové číslo skopírované zo zošita 1904 a interpretované podľa pravidiel 1900 (alebo naopak) posunie každý dátum o 1462 dní, čo sa prejavuje ako dátumy chybné o niečo viac ako štyri roky a dá sa ľahko pomýliť s poškodenými dátami.
Pretože vlastný typ TDateTime v Delphi je ukotvený ku konvencii 1900, knižnica mapujúca sériové čísla Excelu na TDateTime ich musí posunúť o 1462 v oboch smeroch, kedykoľvek je zošit označený príznakom 1904. Pri čítaní sériového čísla 1904 odčítajte 1462 predtým, ako s ním budete zaobchádzať ako s TDateTime; pri zápise TDateTime do zošita 1904 odčítajte 1462 od sériového čísla, aby Excel vykreslil deň, ktorý ste zamýšľali. HotXLS aplikuje tento posun interne pri serializácii hodnôt dátumu pre zošit, ktorý má nastavené Date1904, so the value you assign as a TDateTime round-trips to the same calendar day on the screen.
Zámerná anomália priestupného roku 1900
V systéme 1900 existuje známa anomália. Excel považuje rok 1900 za priestupný rok a prijíma 29. február 1900 ako skutočný dátum, sériové číslo 60. Rok 1900 nebol priestupný rok, pretože storočné roky sú priestupné iba vtedy, ak sú deliteľné 400, a 1900 nie je. Tento fantómový deň je zámerným správaním pre kompatibilitu zdedeným po ranom tabuľkovom procesore, ktorý sa dodával s touto chybou, a odvtedy sa zachoval, aby sériová aritmetika zostala identická naprieč desaťročiami súborov.
Praktický dôsledok je malý, ale reálny: pre akýkoľvek dátum od 1. marca 1900 je sériové číslo o jedno vyššie, než by poskytol presný počet dní, pretože neexistujúci 29. február spotreboval číslo. Knižnica tabuľkového procesora túto anomáliu reprodukuje namiesto toho, aby ju opravovala, pretože presná zhoda s aritmetikou Excelu je celou úlohou. Jej oprava by posunula každý moderný dátum o jeden deň oproti tomu, čo ukazuje Excel, čo je horší výsledok než prenášanie štyridsaťtisíc dní starej chyby o jednotku, ktorej sa žiadny reálny dátum v obchodnom styku nedotkne. Systém 1904 nemá ekvivalentný fantómový deň, čo je jeden z dôvodov, prečo ho niekoľko firiem v minulosti uprednostňovalo.
Detekcia dátumu z numFmt
Keď číslo prichádza zo súboru, ktorý napísal niekto iný, jeho formát is jediným dôkazom, že ide o dátum. ECMA-376 priraďuje blok vstavaných ID formátov, ktorých význam je pevne stanovený špecifikáciou, a formáty dátumu a času zaberajú známe rozsahy. ID 14 až 22 sú formáty dátumu a času pre všeobecné lokálne nastavenie (všeobecne známe m/d/yyyy, h:mm a ich príbuzné). ID 45 až 47 sú formáty uplynutého času. Dva ďalšie pásma, 27 až 36 a 50 až 58, sú formáty dátumu a času špecifické pre konkrétne lokálne nastavenia používané pre CJK kalendáre, definované v ECMA-376 18.8.30. Bunka, ktorej ID formátu čísla patrí do niektorého z týchto rozsahov, je bunkou dátumu alebo času.
Vstavané ID pokrývajú bežné prípady, ale nie vlastné. Keď zošit definuje vlastný formátový kód (napríklad neštandardné poradie alebo lokalizovaný názov mesiaca), ID je nad vstavaným rozsahom a ukazuje do tabuľky formátov čísiel zošita. Pre tieto prípady rozpoznanie dátumu znamená prečítanie reťazca kódu formátu a hľadanie tokenov dátumu. HotXLS spája obe kontroly do jedného interného predikátu XlsxNumFmtIsDate, ktorý sa okamžite vráti ako pravdivý pre vstavané rozsahy dátumov a inak analyzuje vlastný kód formátu cez XlsxFormatCodeIsDate. Verejnou stranou tohto procesu je reťazec bunky NumberFormat a jej index NumberFormatIndex, which give you both the resolved format code and the id to test.
Prečo parser formátu nemôže len hľadať znaky d a m
Analýza kódu formátu na tokeny dátumu vyzerá triviálne, kým si nespomeniete, čo všetko formát čísla obsahuje. Naivné hľadanie písmen, ktoré tvoria dátumy (d, m, y, h a s pre deň, mesiac, rok, hodinu a sekundu), zlyhá na dvoch štruktúrach, ktoré vôbec nie sú tokenmi dátumu.
Prvou je textový literál v úvodzovkách. Formát čísla môže vložiť literálny text do dvojitých úvodzoviek, takže finančný formát ako #,##0 \"MM\" pripája znaky M a M k číslu bez akéhokoľvek časového významu. Skener, ktorý by počítal písmená vnútri úvodzoviek ako tokeny mesiaca, by nesprávne označil tento formát meny za dátum. Druhou je časť v hranatých zátvorkách. Formáty čísel nesú direktívy v hranatých zátvorkách – názvy farieb ako [Red], porovnávacie podmienky ako [>1000], lokálne tagy a značky uplynutého času [h] a [mm]. Niektorý obsah v zátvorkách obsahuje písmená dátumu a niektorý nie, a zaobchádzanie s textom v zátvorkách rovnako ako s telom formátu vedie k falošným pozitívnym výsledkom aj k prehliadnutým prípadom.
Správny parser prechádza kód formátu znak po znaku, sleduje, či sa nachádza vnútri literálu v úvodzovkách a ako hlboko je vnútri vnorených zátvoriek, a rešpektuje aj únikový znak (backslash escape), ktorý cituje jeden nasledujúci znak. Iba neuniknuté písmeno dátumu nájdené mimo akéhokoľvek textového literálu a mimo akejkoľvek sekcie zátvoriek sa počíta ako skutočný token dátumu. Presne takto skenuje XlsxFormatCodeIsDate: úvodzovka prepína stav v literáli, ktorý potláča detekciu tokenov až po uzatváraciu úvodzovku, spätná lomka preskočí nasledujúci znak a počítadlo hĺbky zátvoriek potláča detekciu vnútri sekvencií [...]. Výsledkom je, že #,##0 \"MM\" je správne prečítané ako formát čísla, zatiaľ čo strohý vlastný kód, ktorý neobsahuje nič okrem jediného m alebo d mimo úvodzoviek, je stále správne rozpoznaný ako dátum.
Čítanie dátumov zo súborov tretích strán
Všetko vyššie uvedené konverguje k jednému pracovnému postupu: premeneniu čísla, ktoré zapísala iná aplikácia, späť na dátum, ktorému môžete dôverovať. Sériové číslo vám dáva počet dní, príznak Date1904 zošita hovorí, od ktorej epochy sa počet počíta, a ID formátu čísla alebo vlastný kód bunky je jediným dôkazom, že číslo bolo vôbec myslené ako dátum. Vynechajte ktorýkoľvek z týchto troch prvkov a dostanete vierohodnú nesprávnu odpoveď namiesto viditeľnej chyby.
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;Staršia strana BIFF nesie jednu pascu navyše, ktorú stojí za to spomenúť. V staršom streame .xls môže byť rad susediacich číselných buniek zabalený do jedného viac-bunkového záznamu (MULRK), ktorý ukladá niekoľko hodnôt s ich odkazmi na formát v jednej štruktúre. Bunky dátumov uložené týmto spôsobom nie sú o nič menej dátumami kvôli tomu, že sú zabalené, takže rovnaký test ID formátu musí dosiahnuť do vnútra viac-bunkového záznamu a aplikovať sa na každú bunku, pričom posun 1904 stále riadi každé sériové číslo, ktoré vyprodukuje. Čítačka, ktorá kontroluje iba samostatné záznamy čísel a preskočí those packed ones, will silently turn a column of dates into a column of integers.
Mapovanie sériových čísiel na TDateTime v praxi
Akonáhle kontrola formátu potvrdí dátum a príznak Date1904 je známy, konverzia je mechanická. Hodnota, ktorú HotXLS už odovzdáva späť ako varDate, je TDateTime, ktoré môžete použiť priamo. Hodnota, ktorá prichádza ako čistý Double (čo sa stáva, keď zdroj zapísal sériové číslo bez rozpoznaného formátu dátumu), sa skonvertuje prečítaním ako počtu dní na osi 1900 a v prípade zošita 1904 odčítaním posunu 1462 dní ako prvého kroku, aby sa epochy zarovnali. V opačnom smere priradenie TDateTime do bunky uloží sériové číslo založené na 1900 a HotXLS aplikuje rovnaký posun o 1462 dní pri ukladaní, ak je zošit označený príznakom 1904, takže uložený súbor zobrazuje dátum, ktorý ste zamýšľali, a nie dátum o štyri roky posunutý.
Nastavte príznak zámerne pri generovaní zošita. Predvolená hodnota ponecháva Date1904 ako false, čo zodpovedá Excelu pre Windows a je to takmer vždy to, čo chcete; nastavte ho na true iba vtedy, keď reprodukujete zošit pôvodom z Macu alebo keď nadväzujúci systém špecificky očakáva os 1904. Jediným pravidlom, ktoré zabraňuje celej triede štvorročných chýb, je konzistentnosť: vyberte epochu raz pre zošit, zapíšte každý dátum pod nou a prečítajte každé sériové číslo späť pod príznakom, ktorý súbor skutočne nesie.
Dátumy sú len jedným stĺpcom v širšom príbehu o tom, čo bunka skutočne obsahuje. Susedná vrstva metadát (názov, autor a časové pečiatky, ktoré sprevádzajú mriežku) je pokrytá v našom článku o metadátach zošita a vlastnostiach dokumentu, kde sú rovnaké hodnoty Created a Modified uložené ako TDateTime s rovnakou konvenciou nenastavené-rovná-sa-nula. Keď je dátum výsledkom výpočtu a nie uloženou hodnotou, pravidlá vyhodnocovania v našom článku o engine vzorcov a vlastných funkciách určujú sériové číslo, ktoré potom formát vykreslí. Obe témy pracujú nad rovnakým modelom dátumov, ktorý sa dodáva v HotXLS Component pre Delphi a C++Builder, ktorý číta a zapisuje dátumy XLS a XLSX bez automatizácie Excelu.