Otvorite tabelu, kliknite na ćeliju koja prikazuje 2026-06-19 i traka za formule i dalje prikazuje datum. Pročitajte istu ćeliju iz Delphi-ja i dobićete broj 46192. Oba prikaza su ispravna, jer Excel nikada nije sačuvao datum u toj ćeliji. Sačuvao je serijski broj, broj dana i priložio format broja koji govori ekranu da prikaže taj broj kao kalendarski datum. U vrednosti ćelije ne postoji tip datuma. Postoji broj i pravilo prikaza, a pravilo prikaza je jedina stvar koja razlikuje datum od obične količine.
To razdvajanje je koren svakog baga sa datumima koji biblioteka za tabele mora da izbegne. Sam serijski broj ne govori koji je dan, jer ne govori šta je bio nulti dan. Isti broj označava dva datuma sa razlikom od četiri godine u zavisnosti od jedne zastavice radne sveske. A broj koji bi trebalo pročitati kao datum biće pročitan kao obična količina osim ako nešto ne pregleda njegov format i prepozna šablon datuma. Ovako je izgrađen model datuma u HotXLS-u, i to sa razlogom.
Ćelija datuma je broj plus format
Excel čuva datum kao broj dana od epohe, sa vremenom u delu iza decimalnog zareza. Sredina dana na serijskom broju nosi .5. Celi deo je broj dana. Ništa u sačuvanoj vrednosti ne označava da je ona vremenska. Ono što je označava je format broja ćelije: ECMA-376 ovo naziva numFmt, a ćelija čiji format koda prikazuje šablon datuma ili vremena prikazuje se kao datum. Uklonite format i ista ćelija prikazuje broj; osnovna vrednost se nikada nije promenila.
Zbog toga čitanje vrednosti ćelije daje Variant koji može biti varDate ili običan Double, i zašto je format broja na istoj ćeliji signal koji odlučuje šta je treća strana mislila. Kada HotXLS otvori XLSX datoteku, ćelija prenosi i svoju vrednost Value i svoj NumberFormatIndex u TXLSXCell, a indeks formata je ono što konsultujete da biste saznali da li je broj datum.
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 epohe, sa razlikom od 1462 dana
Podrazumevani sistem datuma, onaj koji koristi svaka radna sveska na Windows-u, računa se od samog kraja 1899. godine, tako da serijski broj 1 pada na prvi dan 1900. godine. Drugi sistem vodi poreklo sa ranog Macintosh-a i računa od početka 1904. godine, tako da je njegov serijski broj 1 četiri godine i jedan dan kasnije. Radna sveska beleži koji sistem koristi u jednoj zastavici. U OOXML paketu ta zastavica je date1904 na delu radne sveske; HotXLS je izlaže kao svojstvo Date1904 radne sveske.
Razmak između dve epohe je tačno 1462 dana. To su četiri kalendarske godine, tri od 365 dana i jedna od 366, što ukupno čini 1461, plus još jedan dan za ofset između dve konvencije nultog dana. Broj je fiksan i možete ga zapamtiti. Njegova važnost leži u tome što nije nula. Serijski broj kopiran iz radne sveske iz 1904. godine i interpretiran pod pravilima iz 1900. godine, ili obrnuto, dovodi do toga da svaki datum odstupa za 1462 dana, što se predstavlja kao datumi koji su pogrešni za nešto više od četiri godine i to je lako zameniti sa oštećenim podacima.
Pošto je Delphi-jev sopstveni TDateTime usidren na konvenciju iz 1900. godine, biblioteka koja mapira Excel serijske brojeve u TDateTime mora da napravi ofset za 1462 u oba smera kad god je radna sveska označena zastavicom 1904. Čitajući serijski broj iz 1904. godine, oduzmite 1462 pre nego što ga tretirate kao TDateTime; upisujući TDateTime u radnu svesku iz 1904. godine, oduzmite 1462 od serijskog broja tako da Excel prikaže dan koji ste želeli. HotXLS primenjuje ovaj pomak interno kada serijalizuje vrednosti datuma za radnu svesku čiji je Date1904 postavljen, tako da se vrednost koju dodelite kao TDateTime uspešno učitava i čuva (round-trip) na isti kalendarski dan na ekranu.
Namerna specifičnost prestupne godine iz 1900
Postoji čuvena specifičnost u sistemu iz 1900. godine. Excel tretira 1900. godinu kao prestupnu i prihvata 29. februar 1900. kao stvarni datum, pod serijskim brojem 60. Godina 1900. nije bila prestupna, jer su sekularne godine prestupne samo kada su deljive sa 400, a 1900. to nije. Fantomski dan je namerno ponašanje radi kompatibilnosti nasleđeno iz rane tabele koja je isporučena sa tim bagom, i zadržano je od tada kako bi serijska aritmetika ostala identična kroz decenije datoteka.
Praktična posledica je mala ali stvarna: za bilo koji datum na dan 1. marta 1900. ili nakon njega, serijski broj je za jedan veći nego što bi to dalo strogo tačno brojanje dana, jer je nepostojeći 29. februar potrošio broj. Biblioteka za tabele reprodukuje ovu specifičnost umesto da je popravlja, jer je potpuno poklapanje sa Excel-ovom aritmetikom čitav posao. Njegovo ispravljanje bi postavilo svaki moderni datum jedan dan u raskorak sa onim što Excel prikazuje, što je lošiji ishod od prenošenja četrdeset hiljada dana starog odstupanja za jedan koje nijedan stvarni datum u poslovnoj upotrebi nikada ne dotiče. Sistem iz 1904. godine nema ekvivalentan fantomski dan, što je jedan od razloga zašto su ga neke kompanije istorijski preferirale.
Detektovanje datuma iz numFmt
Kada broj stigne iz datoteke koju je neko drugi napisao, njegov format je jedini dokaz da se radi o datumu. ECMA-376 dodeljuje blok ugrađenih ID-ova formata čije je značenje fiksirano specifikacijom, a formati datuma i vremena zauzimaju poznate opsege. ID-ovi od 14 do 22 su opšti lokalni formati datuma i vremena, poznati m/d/yyyy, h:mm i njihovi srodnici. ID-ovi od 45 do 47 su formati proteklog vremena. Još dva opsega, od 27 do 36 i od 50 do 58, su lokalni formati datuma i vremena koji se koriste za CJK kalendare, definisani u ECMA-376 18.8.30. Ćelija čiji ID formata broja spada u bilo koji od ovih opsega je ćelija datuma ili vremena.
Ugrađeni ID-ovi pokrivaju uobičajene slučajeve ali ne i prilagođene. Kada radna sveska definiše sopstveni kod formata, recimo nestandardni redosled ili lokalizovani naziv meseca, ID je iznad ugrađenog opsega i ukazuje na tabelu formata brojeva radne sveske. Za njih, prepoznavanje datuma znači čitanje stringa koda formata i traženje tokena datuma. HotXLS sklapa obe provere u jedan interni predikat, XlsxNumFmtIsDate, koji odmah vraća true za ugrađene opsege datuma, a u suprotnom analizira prilagođeni kod formata kroz XlsxFormatCodeIsDate. Javna strana toga su string NumberFormat ćelije i njen NumberFormatIndex, koji vam daju i razrešeni kod formata i ID za testiranje.
Zašto parser formata ne može jednostavno da skenira slova d i m
Analiziranje koda formata za tokene datuma izgleda trivijalno sve dok se ne setite šta još živi u formatu broja. Naivna pretraga slova koja označavaju datume, d, m, y, h i s za dan, mesec, godinu, sat i sekundu, pogrešiće na dve strukture koje uopšte nisu tokeni datuma.
Prva je string literal pod navodnicima. Format broja može da ugradi literalni tekst u dvostruke navodnike, tako da finansijski format poput #,##0 "MM" dodaje karaktere M i M broju bez ikakvog vremenskog značenja. Skener koji broji slova unutar navodnika kao tokene meseca pogrešno bi označio taj valutni format kao datum. Druga je odeljak u zagradama. Formati brojeva nose direktive u uglastim zagradama, nazive boja kao što su [Red], uslove poređenja poput [>1000], lokalne oznake i markere proteklog vremena [h] i [mm]. Neki sadržaj zagrada drži slova datuma, a neki ne, i tretiranje teksta u zagradama isto kao i tela formata dovodi do lažno pozitivnih rezultata i propuštenih slučajeva.
Ispravan parser prolazi kroz kod formata karakter po karakter, prateći da li se nalazi unutar literala pod navodnicima i koliko duboko je unutar ugnežđenih zagrada, a takođe poštuje escape backslash koji citira jedan prateći karakter. Samo ne-escapovano slovo datuma pronađeno van bilo kog string literala i van bilo kog odeljka u zagradama računa se kao stvarni token datuma. To je upravo način na koji XlsxFormatCodeIsDate skenira: navodnik menja stanje u-literalu koje potiskuje detekciju tokena do zatvarajućeg navodnika, backslash preskače sledeći karakter, a brojač dubine zagrada potiskuje detekciju unutar [...] opsega. Rezultat je to da se #,##0 "MM" ispravno čita kao format broja, dok se kratak prilagođeni kod koji ne sadrži ništa osim jednog m ili d van navodnika i dalje ispravno prepoznaje kao datum.
Čitanje datuma iz datoteka trećih strana
Sve gore navedeno se spaja u jedan tok rada: pretvaranje broja koji je neka druga aplikacija upisala nazad u datum kojem možete verovati. Serijski broj vam daje broj dana, zastavica Date1904 radne sveske vam govori od koje epohe se meri broj, a ID formata broja ćelije ili prilagođeni kod je jedini dokaz da je broj uopšte bio zamišljen kao datum. Izostavite bilo koji od ova tri i dobićete uverljiv pogrešan odgovor umesto vidljive greške.
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 1900 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;Nasleđena BIFF strana ima jednu dodatnu zamku vrednu pomena. U starijem .xls toku, niz susednih numeričkih ćelija može se spakovati u jedan višecelijski zapis, MULRK, koji čuva nekoliko vrednosti sa njihovim formatnim referencama u jednoj strukturi. Ćelije datuma sačuvane na taj način nisu manje datumi zbog toga što su spakovane, pa isti test ID-a formata mora da dopre unutar višecelijskog zapisa i da se primeni po ćeliji, a ofset iz 1904. godine i dalje upravlja svakim serijskim brojem koji on daje. Čitač koji proverava samo samostalne zapise brojeva, a preskače spakovane, tiho će pretvoriti kolonu datuma u kolonu celih brojeva.
Mapiranje serijskih brojeva u TDateTime u praksi
Jednom kada provera formata potvrdi datum i kada je poznata zastavica Date1904, konverzija je mehanička. Vrednost koju HotXLS već vraća kao varDate je TDateTime koji možete koristiti direktno. Vrednost koja stigne kao običan Double, što se dešava kada je izvor upisao serijski broj bez prepoznatog formata datuma, konvertuje se tako što se čita kao broj dana na osi 1900, a za radnu svesku iz 1904. godine, prvo se oduzima ofset od 1462 dana kako bi se epohe poravnale. Idući u drugom smeru, dodeljivanje TDateTime vrednosti ćeliji čuva serijski broj zasnovan na 1900. godini, a HotXLS primenjuje isti pomak od 1462 dana prilikom čuvanja kada je radna sveska označena zastavicom 1904, tako da sačuvana datoteka prikazuje datum koji ste nameravali, umesto onog koji odstupa za četiri godine.
Postavite zastavicu namerno kada generišete radnu svesku. Podrazumevana vrednost ostavlja Date1904 na false, što odgovara programu Excel za Windows i to je skoro uvek ono što želite; postavite je na true samo kada reprodukujete radnu svesku poreklom sa Mac-a ili kada nizvodni sistem specifično očekuje osu 1904. Jedino pravilo koje sprečava čitavu klasu četvorogodišnjih grešaka jeste doslednost: izaberite epohu jednom po radnoj svesci, upisujte svaki datum pod njom i čitajte svaki serijski broj pod zastavicom koju datoteka stvarno nosi.
Datumi su jedna kolona u široj priči o tome šta ćelija stvarno drži. Susedni sloj metapodataka, naslov, autor i vremenske oznake koje idu uz mrežu, pokriveni su u našem članku o metapodacima radne sveske i svojstvima dokumenta, gde se iste vrednosti Created i Modified čuvaju kao TDateTime sa istom konvencijom nepodešeno-jednako-nula. Kada je datum rezultat proračuna a ne sačuvana vrednost, pravila evaluacije u našem članku o mehanizmu formula i prilagođenim funkcijama određuju serijski broj koji format zatim renderuje. Oba funkcionišu nad istim modelom datuma koji se isporučuje u HotXLS komponenti za Delphi i C++Builder, koja čita i upisuje XLS i XLSX datume bez Excel automatizacije.