Otevřete tabulku, klikněte na buňku zobrazující 2026-06-19 a řádek vzorců bude stále ukazovat datum. Načtěte stejnou buňku z Delphi a získáte číslo 46192. Oba pohledy jsou správné, protože Excel do této buňky datum nikdy neuložil. Uložil sériové číslo (počet dní) a připojil formát čísla, který říká obrazovce, aby tento počet vykreslila jako kalendářní datum. V hodnotě buňky neexistuje žádný typ data. Je tam číslo a pravidlo zobrazení, a pravidlo zobrazení je jedinou věcí, která odlišuje datum od obyčejného množství.
Toto oddělení je kořenem každé chyby s daty, které se tabulková knihovna musí vyhnout. Samotné sériové číslo neříká, o jaký den jde, protože neříká, co bylo dnem nula. Stejné číslo znamená dvě data vzdálená od sebe čtyři roky v závislosti na jediném příznaku sešitu. A číslo, které by se mělo načíst jako datum, se načte jako obyčejné množství, pokud něco nezkontroluje jeho formát a nerozpozná vzor data. Tímto způsobem je postaven model data v HotXLS, a to z jasných důvodů.
Buňka data je číslo plus formát
Excel ukládá datum jako počet dní od epochy, přičemž čas dne se nachází v desetinné části. Poledne na sériovém čísle nese hodnotu .5. Celočíselná část je počet dní. Nic v uložené hodnotě ji neoznačuje jako časovou. To, co ji označuje, je formát čísla buňky. ECMA-376 jej nazývá numFmt a buňka, jejíž formátovací kód vyjadřuje vzor data nebo času, se zobrazuje jako datum. Odstraňte formát a stejná buňka ukáže číslo. Hodnota na pozadí se nikdy nezměnila.
Proto vám načtení hodnoty buňky dává Variant, který může být typu varDate nebo obyčejný Double, a formát čísla u stejné buňky je signálem, který rozhoduje o tom, co měl autor na mysli. Když HotXLS otevírá soubor XLSX, buňka přenáší jak svou Value, tak NumberFormatIndex do objektu TXLSXCell a index formátu je tím, co konzultujete, abyste zjistili, zda je číslo datem.
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;Dvě epochy vzdálené 1462 dní
Výchozí systém dat (ten, který používá každý sešit na Windows) počítá od samotného konce roku 1899, takže sériové číslo 1 připadá na první den roku 1900. Druhý systém má původ u raných počítačů Macintosh a počítá od začátku roku 1904, takže jeho sériové číslo 1 je o čtyři roky a jeden den později. Sešit zaznamenává použitý systém v jediném příznaku. V balíčku OOXML je tímto příznakem date1904 v části sešitu. HotXLS jej zpřístupňuje jako vlastnost sešitu Date1904.
Rozdíl mezi oběma epochami je přesně 1462 dní. Jedná se o tři roky po 365 dnech a jeden rok s 366 dny (celkem 1461 dní) plus jeden den navíc pro posun mezi oběma konvencemi dne nula. Tato hodnota je pevně daná a můžete ji nosit v hlavě. Její význam spočívá v tom, že není nulová. Sériové číslo zkopírované ze sešitu se systémem 1904 a interpretované podle pravidel 1900 (nebo naopak) posune každé datum o 1462 dní, což se projevuje jako data chybná o něco málo přes čtyři roky a lze to snadno zaměnit za poškozená data.
Vzhledem k tomu, že vlastní typ TDateTime v Delphi je ukotven ke konvenci 1900, knihovna mapující sériové hodnoty Excelu na TDateTime musí provést posun o 1462 v obou směrech, kdykoli je sešit označen příznakem 1904. Při čtení hodnoty ze systému 1904 odečtěte 1462 předtím, než s ní budete zacházet jako s TDateTime. Při zápisu TDateTime do sešitu se systémem 1904 odečtěte 1462 od sériové hodnoty, aby Excel zobrazil den, který jste měli na mysli. HotXLS aplikuje tento posun interně při serializaci hodnot data pro sešit s nastaveným Date1904, takže hodnota přiřazená jako TDateTime se na obrazovce vrátí na stejný kalendářní den.
Záměrná anomálie s přestupným rokem 1900
V systému 1900 existuje známá anomálie. Excel považuje rok 1900 za přestupný rok a přijímá 29. únor 1900 jako skutečné datum se sériovým číslem 60. Rok 1900 přitom přestupný nebyl, protože roky zakončující století jsou přestupné pouze tehdy, jsou-li dělitelné 400, což 1900 není. Tento fiktivní den je záměrným chováním pro zajištění kompatibility převzatým z raného tabulkového procesoru, který tuto chybu obsahoval. Od té doby se zachovává, aby sériová aritmetika zůstala shodná u souborů napříč dekádami.
Praktický důsledek je malý, ale reálný: pro jakékoli datum od 1. března 1900 je sériové číslo o jedničku vyšší, než by odpovídalo striktně správnému počtu dní, protože neexistující 29. únor spotřeboval jedno číslo. Tabulková knihovna tuto anomálii spíše reprodukuje, než aby ji opravovala, protože přesná shoda s aritmetikou Excelu je jejím hlavním úkolem. Její oprava by posunula každé moderní datum o den oproti tomu, co ukazuje Excel, což je horší výsledek než zachování čtyřicet tisíc dní staré chyby o jedničku, se kterou se žádné reálné obchodní datum nikdy nesetká. Systém 1904 žádný ekvivalentní fiktivní den nemá, což byl v minulosti jeden z důvodů, proč mu některé organizace dávaly přednost.
Detekce data z formátu numFmt
Když číslo pochází ze souboru zapsaného jinou aplikací, jeho formát je jediným důkazem, že se jedná o datum. Specifikace ECMA-376 přiděluje blok předdefinovaných ID formátů, jejichž význam je dán specifikací, a formáty data a času zaujímají známé rozsahy. ID 14 až 22 jsou formáty data a času pro obecná prostředí, jako známé m/d/yyyy, h:mm a jejich příbuzné. ID 45 až 47 jsou formáty uplynulého času. Dva další rozsahy, 27 až 36 a 50 až 58, jsou lokálně specifické formáty data a času používané pro kalendáře CJK definované v ECMA-376 18.8.30. Buňka, jejíž ID formátu čísla spadá do některého z těchto rozsahů, je buňkou data nebo času.
Předdefinovaná ID pokrývají běžné případy, nikoli však ty uživatelské. Pokud sešit definuje svůj vlastní formátovací kód (například nestandardní pořadí nebo lokalizovaný název měsíce), ID leží nad předdefinovaným rozsahem a odkazuje do tabulky formátů čísel sešitu. V těchto případech znamená rozpoznání data přečtení řetězce formátovacího kódu a vyhledání tokenů data. HotXLS spojuje obě kontroly do jednoho interního predikátu XlsxNumFmtIsDate, který vrací true okamžitě pro předdefinované rozsahy dat a jinak analyzuje uživatelský formátovací kód přes XlsxFormatCodeIsDate. Veřejnou stranou této logiky je řetězec NumberFormat buňky a její NumberFormatIndex, které vám dávají jak vyřešený formátovací kód a ID k otestování.
Proč analyzátor formátu nemůže jen hledat písmena d a m
Analýza formátovacího kódu na přítomnost tokenů data vypadá triviálně, dokud si nevzpomenete, co dalšího se ve formátu čísla nachází. Naivní hledání písmen vyjadřujících data (d, m, y, h a s pro den, měsíc, rok, hodinu a sekundu) selže na dvou strukturách, které vůbec tokeny data nejsou. První je textový literál v uvozovkách. Formát čísla může obsahovat doslovný text v uvozovkách, takže finanční formát typu #,##0 "MM" připojuje znaky M a M k číslu bez jakéhokoli časového významu. Skener, který počítá písmena uvnitř uvozovek jako tokeny měsíce, by takový finanční formát chybně označil za datum. Druhou strukturou je sekce v hranatých závorkách. Formáty čísel nesou v hranatých závorkách direktivy, názvy barev jako [Red], podmínky porovnání jako [>1000], značky prostředí (locale) a markery uplynulého času [h] a [mm]. Některý obsah v závorkách obsahuje písmena spojená s datem a některý ne. Pokud se s textem v závorkách zachází stejně jako s tělem formátu, vede to k falešně pozitivním výsledkům i k přehlédnutým případům.
Správný parser prochází formátovací kód znak po znaku, sleduje, zda se nachází uvnitř textového literálu, jak hluboko je ve vnořených závorkách, a respektuje únikový znak (zpětné lomítko), který uvozuje jeden následující znak. Pouze neescapované písmeno data nalezené mimo jakýkoli textový literál a mimo jakoukoli sekci v závorkách se počítá jako skutečný token data. Přesně takto skenuje XlsxFormatCodeIsDate: uvozovka přepíná stav uvnitř literálu, který potlačuje detekci tokenů až do uzavírací uvozovky, zpětné lomítko přeskakuje další znak a počítadlo hloubky závorek potlačuje detekci uvnitř bloků [...]. Výsledkem je, že #,##0 "MM" se správně načte jako formát čísla, zatímco stručný uživatelský kód, který neobsahuje nic než jedno m nebo d mimo uvozovky, je stále správně rozpoznán jako datum.
Čtení dat ze souborů třetích stran
Vše výše uvedené směřuje k jednomu pracovnímu postupu: převodu čísla, které zapsala jiná aplikace, zpět na datum, kterému můžete důvěřovat. Sériové číslo vám dává počet dní, příznak sešitu Date1904 vám říká, od které epochy se počet měří, a ID formátu čísla buňky nebo uživatelský kód je jediným důkazem, že číslo mělo být v první řadě datem. Vynechejte kterýkoli z těchto tří prvků a získáte věrohodnou, ale chybnou odpověď namísto zjevné 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ší strana BIFF obsahuje jednu past navíc, kterou stojí za to pojmenovat. Ve starším streamu .xls může být řada sousedních číselných buněk zabalena do jediného vícebuněčného záznamu MULRK, který ukládá několik hodnot s jejich referencemi na formát v jedné struktuře. Buňky data uložené tímto způsobem nejsou o nic méně daty jen proto, že jsou zabalené. Stejný test na ID formátu tak musí nahlédnout dovnitř vícebuněčného záznamu a aplikovat se na každou buňku. Posun 1904 stále řídí každou sériovou hodnotu, kterou poskytne. Čtečka, která kontroluje pouze samostatné záznamy čísel a přeskakuje ty zabalené, tiše promění sloupec dat ve sloupec celých čísel.
Mapování sériových čísel na TDateTime v praxi
Jakmile kontrola formátu potvrdí datum a příznak Date1904 je znám, převod je mechanický. Hodnota, kterou HotXLS vrací jako varDate, je přímo použitelný typ TDateTime. Hodnota přicházející jako samotný Double (což se stává, když zdroj zapsal sériové číslo bez rozpoznaného formátu data) se převede jeho načtením jako počtu dní na ose 1900 a u sešitu 1904 nejprve odečtením posunu 1462 dní, aby se epochy srovnaly. Při opačném postupu přiřazení TDateTime do buňky uloží sériové číslo založené na 1900. HotXLS aplikuje stejný posun o 1462 dní při uložení, pokud má sešit příznak 1904, takže uložený soubor ukáže datum, které jste chtěli, a nikoli datum o čtyři roky posunuté.
Příznak nastavujte záměrně při generování sešitu. Výchozí nastavení ponechává Date1904 na hodnotě false, což odpovídá Excelu pro Windows a je to téměř vždy to, co chcete. Nastavte jej na true pouze tehdy, když reprodukujete sešit pocházející z Macu nebo když navazující systém výslovně očekává osu 1904. Jediným pravidlem, které předchází celé třídě chyb se čtyřletým posunem, je konzistence: zvolte epochu jednou pro celý sešit, zapisujte pod ní každé datum a načítejte každou sériovou hodnotu zpět s ohledem na příznak, který soubor skutečně nese.
Data jsou jedním sloupcem v širším příběhu o tom, co buňka skutečně obsahuje. Sousední vrstva metadat, tedy název, autor a časová razítka provázející mřížku, je popsána v našem článku o metadatech sešitu a vlastnostech dokumentu, kde jsou stejné hodnoty Created a Modified uloženy jako TDateTime se stejnou konvencí, že nenastavená hodnota se rovná nule. Pokud datum je výsledkem výpočtu a nikoli uloženou hodnotou, pravidla vyhodnocování v našem článku o stroji vzorců a uživatelských funkcích určují sériové číslo, které pak formát vykreslí. Oba postupy pracují nad stejným modelem data, který je dodáván v komponentě HotXLS pro Delphi a C++Builder, která čte a zapisuje data XLS a XLSX bez automatizace Excelu.