Otwórz arkusz kalkulacyjny, kliknij komórkę pokazującą 2026-06-19, a pasek formuły nadal będzie wyświetlał datę. Przeczytaj tę samą komórkę z poziomu Delphi, a otrzymasz liczbę 46192. Oba widoki są poprawne, ponieważ program Excel nigdy nie zapisał daty w tej komórce. Zapisał numer seryjny (serial number), liczbę dni i dołączył format liczb, który instruuje ekran, aby wyrenderował tę liczbę jako datę kalendarzową. W wartości komórki nie ma typu daty. Istnieje liczba i reguła wyświetlania, a reguła wyświetlania jest jedyną rzeczą, która odróżnia datę od zwykłej ilości.
To rozdzielenie jest źródłem każdego błędu związanego z datami, jakiego musi unikać biblioteka arkuszy kalkulacyjnych. Sam numer seryjny nie mówi, jaki to dzień, ponieważ nie mówi, jaki dzień był dniem zerowym. Ta sama liczba oznacza dwie daty oddalone od siebie o cztery lata, w zależności od pojedynczej flagi skoroszytu. A liczba, która powinna zostać odczytana jako data, zostanie odczytana jako zwykła ilość, chyba że coś zbada jej format i rozpozna wzorzec daty. Tak właśnie zbudowany jest model dat w HotXLS i z tego powodu musi taki być.
Komórka z datą to liczba plus format
Excel przechowuje datę jako liczbę dni od pewnego punktu odniesienia (epoki), z porą dnia w części ułamkowej. Południe na liczbie seryjnej niesie ze sobą .5. Część całkowita to liczba dni. Nic w przechowywanej wartości nie oznacza jej jako czasowej. Oznacza ją format liczb komórki: ECMA-376 nazywa to numFmt, a komórka, której kod formatu określa wzorzec daty lub czasu, jest wyświetlana jako data. Usuń format, a ta sama komórka pokaże liczbę; podstawowa wartość nigdy się nie zmieniła.
Z tego powodu odczytanie wartości komórki daje typ Variant, który może być typu varDate lub zwykłym Double, i dlatego format liczb na tej samej komórce jest sygnałem, który decyduje, co autor miał na myśli. Gdy HotXLS otwiera plik XLSX, komórka przenosi zarówno swoją wartość Value, jak i indeks formatu NumberFormatIndex do obiektu TXLSXCell, a indeks formatu jest tym, co sprawdzasz, aby dowiedzieć się, czy liczba jest datą.
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;Dwie epoki, 1462 dni różnicy
Domyślny system dat, używany przez każdy skoroszyt w systemie Windows, liczy czas od samego końca 1899 roku, tak że seryjna jedynka 1 przypada na pierwszy dzień roku 1900. Drugi system wywodzi się z wczesnych komputerów Macintosh i liczy czas od początku 1904 roku, więc jego seryjna jedynka 1 przypada na cztery lata i jeden dzień później. Skoroszyt zapisuje system, którego używa, w jednej fladze. W pakiecie OOXML flaga ta to date1904 w części workbook; HotXLS udostępnia ją jako właściwość Date1904 skoroszytu.
Różnica między dwoma punktami odniesienia wynosi dokładnie 1462 dni. Są to cztery lata kalendarzowe (trzy po 365 dni i jeden mający 366 dni, co daje łącznie 1461 dni) plus jeden dzień z racji przesunięcia między dwiema konwencjami dnia zerowego. Liczba ta jest stała i można ją zapamiętać. Jej znaczenie polega na tym, że nie jest zerem. Liczba seryjna skopiowana ze skoroszytu z systemem 1904 i zinterpretowana według reguł systemu 1900 (lub odwrotnie) przesuwa każdą datę o 1462 dni, co objawia się datami błędnymi o nieco ponad cztery lata i jest łatwe do pomylenia z uszkodzeniem danych.
Ponieważ własny typ TDateTime środowiska Delphi jest zakotwiczony w konwencji roku 1900, biblioteka mapująca serie Excela na TDateTime musi wprowadzać przesunięcie o 1462 dni w obu kierunkach za każdym razem, gdy skoroszyt ma flagę 1904. Odczytując liczbę seryjną z systemu 1904, odejmij 1462 przed potraktowaniem jej jako TDateTime; zapisując TDateTime do skoroszytu z systemem 1904, odejmij 1462 od liczby seryjnej, aby Excel wyrenderował dzień, który miałeś na myśli. HotXLS stosuje to przesunięcie wewnętrznie podczas serializacji wartości dat dla skoroszytu, którego właściwość Date1904 jest ustawiona, dzięki czemu przypisywana wartość jako TDateTime wraca do tego samego dnia kalendarzowego na ekranie.
Celowa osobliwość roku przestępnego 1900
W systemie 1900 istnieje słynna usterka. Excel traktuje rok 1900 jako rok przestępny i akceptuje datę 29 lutego 1900 roku jako rzeczywistą datę o numerze seryjnym 60. Rok 1900 nie był rokiem przestępnym, ponieważ lata kończące stulecia są przestępne tylko wtedy, gdy dzielą się przez 400, a 1900 się nie dzieli. Ten widmowy dzień to celowe zachowanie zapewniające kompatybilność wsteczną, odziedziczone po wczesnym arkuszu kalkulacyjnym, który został dostarczony z tym błędem, i zachowane od tamtej pory, aby arytmetyka serii pozostała identyczna w plikach na przestrzeni dziesięcioleci.
Konsekwencja praktyczna jest niewielka, ale rzeczywista: dla każdej daty przypadającej na dzień 1 marca 1900 r. lub później liczba seryjna jest o jeden wyższa niż dałoby to ścisłe wyliczenie dni, ponieważ nieistniejący 29 lutego skonsumował jeden numer. Biblioteka arkuszy kalkulacyjnych odtwarza tę osobliwość zamiast ją naprawiać, ponieważ dokładne dopasowanie arytmetyki Excela to całe jej zadanie. Poprawienie tego błędu postawiłoby każdą współczesną datę o jeden dzień obok tego, co pokazuje Excel, co byłoby gorszym rezultatem niż niesienie ze sobą błędu sprzed czterdziestu tysięcy dni, którego żadna rzeczywista data biznesowa nigdy nie dotyka. System 1904 nie posiada odpowiednika tego widmowego dnia, co było jednym z powodów, dla których niektóre firmy historycznie go preferowały.
Wykrywanie daty na podstawie numFmt
Gdy liczba przychodzi z pliku zapisanego przez inną aplikację, jej format jest jedynym dowodem na to, że jest datą. ECMA-376 przypisuje blok wbudowanych identyfikatorów formatu, których znaczenie jest określone przez specyfikację, a formaty daty i czasu zajmują znane zakresy. Identyfikatory od 14 do 22 to ogólne formaty daty i czasu (znane m/d/yyyy, h:mm i pokrewne). Identyfikatory od 45 do 47 to formaty upływu czasu. Dwa kolejne pasma, od 27 do 36 oraz od 50 do 58, to specyficzne dla lokalizacji formaty daty i czasu używane w kalendarzach CJK, zdefiniowane w ECMA-376 18.8.30. Komórka, której identyfikator formatu liczb wpada w którykolwiek z tych zakresów, jest komórką daty lub czasu.
Wbudowane identyfikatory pokrywają typowe przypadki, ale nie niestandardowe. Gdy skoroszyt definiuje własny kod formatu (powiedzmy, niestandardową kolejność lub zlokalizowaną nazwę miesiąca), identyfikator znajduje się powyżej zakresu wbudowanego i wskazuje na tabelę formatów liczb skoroszytu. W takich przypadkach rozpoznanie daty oznacza przeczytanie ciągu kodu formatu i szukanie tokenów daty. HotXLS łączy oba te testy w jeden wewnętrzny predykat, XlsxNumFmtIsDate, który zwraca natychmiast prawda (true) dla wbudowanych zakresów dat, a w przeciwnym razie analizuje niestandardowy kod formatu za pomocą XlsxFormatCodeIsDate. Publiczną stroną tego mechanizmu są właściwości komórki NumberFormat oraz NumberFormatIndex, które dają zarówno rozwiązany kod formatu, jak i identyfikator do przetestowania.
Dlaczego parser formatu nie może po prostu szukać liter d i m
Analizowanie kodu formatu pod kątem tokenów daty wydaje się trywialne, dopóki nie przypomnisz sobie, co jeszcze kryje się w formacie liczb. Naiwne szukanie liter składających się na daty – d, m, y, h i s dla dnia, miesiąca, roku, godziny i sekundy – zadziała błędnie na dwóch strukturach, które wcale nie są tokenami daty.
Pierwszą z nich jest ujęty w cudzysłów literał ciągu znaków. Format liczb może zawierać dosłowny tekst w podwójnych cudzysłowach, więc format finansowy typu #,##0 "MM" dodaje znaki M i M do liczby bez żadnego znaczenia czasowego. Skaner, który liczyłby litery wewnątrz cudzysłowów jako tokeny miesiąca, błędnie oznaczyłby ten format walutowy jako datę. Drugą strukturą jest sekcja w nawiasach kwadratowych. Formaty liczb przenoszą instrukcje w nawiasach kwadratowych: nazwy kolorów, takie jak [Red], warunki porównania, takie jak [>1000], tagi lokalizacji oraz znaczniki upływu czasu [h] i [mm]. Niektóre zawartości nawiasów zawierają litery dat, a inne nie, i traktowanie tekstu w nawiasach tak samo jak korpusu formatu prowadzi zarówno do fałszywych alarmów, jak i do pominiętych przypadków.
Poprawny parser przechodzi przez kod formatu znak po znaku, śledząc, czy znajduje się wewnątrz literału w cudzysłowie oraz jak głęboko jest w nawiasach kwadratowych, a także honoruje ukośnik odwrotny (backslash) jako znak ucieczki, który usuwa specjalne znaczenie kolejnego znaku. Tylko nieucieczkowa litera daty znaleziona poza jakimkolwiek literałem tekstowym i poza sekcją nawiasów kwadratowych liczy się jako rzeczywisty token daty. Dokładnie tak skanuje funkcja XlsxFormatCodeIsDate: cudzysłów przełącza stan w-literale, który blokuje wykrywanie tokenów aż do cudzysłowu zamykającego, ukośnik odwrotny pomija kolejny znak, a licznik głębokości nawiasów kwadratowych blokuje wykrywanie wewnątrz bloków [...]. Korzyść jest taka, że format #,##0 "MM" jest poprawnie odczytywany jako format numeryczny, podczas gdy krótki niestandardowy kod zawierający pojedyncze m lub d poza cudzysłowami jest nadal poprawnie rozpoznawany jako data.
Odczytywanie dat z plików zewnętrznych
Wszystko powyższe zbiega się w jednym przepływie pracy: zamianie liczby zapisanej przez inną aplikację z powrotem w datę, której można zaufać. Liczba seryjna daje liczbę dni, flaga skoroszytu Date1904 mówi, od której epoki ta liczba jest mierzona, a indeks formatu liczb komórki lub kod niestandardowy to jedyny dowód na to, że liczba ta miała być w ogóle datą. Pomiń dowolny z tych trzech elementów, a otrzymasz wiarygodną złą odpowiedź zamiast widocznego błędu.
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;W przypadku starszego formatu BIFF istnieje jeszcze jedna pułapka warta wymienienia. W starszym strumieniu .xls seria sąsiadujących komórek numerycznych może być spakowana w pojedynczy rekord wielokomórkowy – rekord MULRK, który przechowuje wiele wartości wraz z ich referencjami do formatów w jednej strukturze. Komórki dat przechowywane w ten sposób są nie mniej datami z racji bycia spakowanymi, więc ten sam test identyfikatora formatu musi dotyczyć każdej komórki z osobna, a przesunięcie 1904 nadal rządzi każdą z nich. Czytnik badający tylko samodzielne rekordy liczb i pomijający te spakowane po cichu zamieni kolumnę dat w kolumnę liczb całkowitych.
Mapowanie serii na TDateTime w praktyce
Gdy sprawdzenie formatu potwierdzi datę, a flaga Date1904 jest znana, konwersja staje się mechaniczna. Wartość zwracana przez HotXLS jako varDate to obiekt TDateTime, którego można użyć bezpośrednio. Wartość przychodząca jako surowe Double (co zdarza się, gdy źródło zapisało serię bez rozpoznanego formatu daty) jest konwertowana przez odczytanie jej jako liczby dni na osi 1900 i – dla skoroszytu 1904 – uprzednie odjęcie przesunięcia o 1462 dni, aby epoki się pokryły. W drugą stronę, przypisanie TDateTime do komórki zapisuje liczbę seryjną opartą na 1900, a HotXLS stosuje to samo przesunięcie o 1462 dni przy zapisie, gdy flaga Date1904 skoroszytu jest ustawiona, dzięki czemu zapisany plik pokazuje datę, którą zamierzałeś, a nie przesuniętą o cztery lata.
Ustawiaj tę flagę celowo, gdy generujesz skoroszyt. Domyślne ustawienie pozostawia Date1904 jako fałsz (false), co odpowiada programowi Excel dla systemu Windows i jest niemal zawsze tym, czego chcesz; ustawiaj ją jako prawda (true) tylko wtedy, gdy odtwarzasz skoroszyt pochodzący z systemu Mac lub gdy system docelowy wyraźnie oczekuje osi 1904. Jedyną zasadą zapobiegającą całej klasie błędów czteroletnich jest spójność: wybierz epokę raz na skoroszyt, zapisuj każdą datę pod nią i czytaj każdą serię z powrotem pod flagą, którą plik faktycznie niesie.
Daty to tylko jedna kolumna w szerszej historii o tym, co komórka faktycznie przechowuje. Sąsiadująca warstwa metadanych (tytuł, autor i znaczniki czasu podróżujące obok siatki) została omówiona w naszym artykule na temat metadanych skoroszytu i właściwości dokumentu, gdzie te same wartości Created i Modified są przechowywane jako TDateTime z tą samą konwencją zero-oznacza-brak-wartości. Gdy data jest wynikiem obliczeń, a nie zapisaną wartością, reguły ewaluacji opisane w artykule na temat silnika formuł i funkcji niestandardowych określają serię, którą format następnie renderuje. Oba te rozwiązania działają na tym samym modelu dat, który jest dostarczany w komponencie HotXLS Component dla Delphi i C++Builder, który odczytuje i zapisuje daty XLS oraz XLSX bez automatyzacji Excela.