Odprite preglednico, kliknite celico, ki prikazuje 2026-06-19, in vrstica za formule bo še vedno prikazovala datum. Preberite isto celico iz Delphija in prejeli boste število 46192. Oba pogleda sta pravilna, saj Excel v to celico ni nikoli shranil datuma. Shranil je zaporedno številko (serial number), to je število dni, in priložil številsko oblikovanje, ki zaslonu naroči, naj število izriše kot koledarski datum. V vrednosti celice ni podatkovnega tipa za datum. Na voljo sta število in pravilo prikaza, pravilo prikaza pa je edina stvar, ki loči datum od navadne količine.
Ta ločitev je vir vsake napake z datumi, ki se ji mora knjižnica za preglednice izogniti. Zaporedna številka sama po sebi ne pove, kateri dan je, saj ne pove, kaj je bil dan nič. Isti podatek pomeni dva datuma, ki sta štiri leta narazen, odvisno od ene same zastavice delovnega zvezka. In število, ki bi ga morali prebrati kot datum, se bo prebralo kot navadna količina, razen če nekaj pregleda njegovo oblikovanje in prepozna vzorec datuma. Tako je zgrajen datumski model v HotXLS in to z razlogom.
Celica z datumom je število plus oblikovanje
Excel shranjuje datum kot število dni od določene epohe, čas dneva pa v ulomku. Opoldne na zaporedni številki nosi vrednost .5. Celi del predstavlja število dni. Nič v shranjeni vrednosti ne označuje, da gre za časovni podatek. Tisto, kar ga označuje, je številsko oblikovanje celice: standard ECMA-376 to imenuje numFmt, celica, katere koda oblikovanja določa vzorec datuma ali časa, pa se prikaže kot datum. Odstranite oblikovanje in ista celica bo prikazala število; osnovna vrednost se ni nikoli spremenila.
Zato vam branje vrednosti celice vrne tip Variant, ki je lahko varDate ali pa navaden Double, številsko oblikovanje iste celice pa je signal, ki določa, kaj je avtor želel povedati. Ko HotXLS odpre datoteko XLSX, celica prenese tako svojo Value kot svoj NumberFormatIndex v TXLSXCell, indeks oblikovanja pa je tisto, kar preverite, da ugotovite, ali je število 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 epohi, 1462 dni narazen
Privzeti datumski sistem, ki ga uporablja vsak delovni zvezek v sistemu Windows, šteje od samega konca leta 1899, tako da zaporedna številka 1 pade na prvi dan leta 1900. Drugi sistem izvira iz zgodnjega Macintosha in šteje od začetka leta 1904, zato je njegova številka 1 štiri leta in en dan kasneje. Delovni zvezek zabeleži, kateri sistem uporablja, v eni zastavici. V paketu OOXML je ta zastavica date1904 na delu delovnega zvezka; HotXLS jo izpostavi kot lastnost Date1904 delovnega zvezka.
Razlika med obema epohama je natanko 1462 dni. To so štiri koledarska leta (tri po 365 dni in eno s 366 dnevi, skupaj 1461 dni) plus še en dan za odmik med obema konvencijama dneva nič. Številka je fiksna in jo lahko nosite v glavi. Njen pomen je v tem, da ni enaka nič. Zaporedna številka, kopirana iz delovnega zvezka 1904 in interpretirana pod pravili leta 1900 (ali obratno), postavi vsak datum za 1462 dni stran, kar se kaže kot datumi, ki so napačni za nekaj več kot štiri leta, in to je enostavno zamenjati za poškodovane podatke.
Ker je Delphijev lastni TDateTime usidran v konvencijo leta 1900, mora knjižnica, ki preslikuje zaporedne številke Excela v TDateTime, dodati odmik 1462 v obeh smereh, kadar koli je delovni zvezek označen s sistemom 1904. Pri branju podatkov 1904 odštejte 1462, preden jih obravnavate kot TDateTime; pri zapisovanju TDateTime v delovni zvezek 1904 pa odštejte 1462 od zaporedne številke, da Excel izriše dan, ki ste ga želeli. HotXLS ta zamik uporabi interno, ko serializira datumske vrednosti za delovni zvezek z nastavitvijo Date1904, tako da se vrednost, ki jo dodelite kot TDateTime, krožno preoblikuje v isti koledarski dan na zaslonu.
Namerna posebnost prestopnega leta 1900
V sistemu 1900 obstaja slavna posebnost. Excel obravnava leto 1900 kot prestopno leto in sprejme 29. februar 1900 kot resničen datum z zaporedno številko 60. Leto 1900 ni bilo prestopno leto, saj so stoletna leta prestopna le, če so deljiva s 400, 1900 pa ni. Fantomski dan je namerno obnašanje za združljivost, podedovano iz zgodnjega programa za preglednice, ki je prišel s tem hroščem, in se ohranja do danes, da zaporedna aritmetika ostane enaka skozi desetletja datotek.
Praktična posledica je majhna, a resnična: za kateri koli datum na ali po 1. marcu 1900 je zaporedna številka za ena višja od tiste, ki bi jo dalo strogo pravilno štetje dni, saj je neobstoječi 29. februar porabil eno številko. Knjižnica za preglednice to posebnost poustvari, namesto da bi jo popravila, saj je natančno ujemanje z Excelovo aritmetiko celotna naloga. Popravek bi postavil vsak sodoben datum za en dan stran od tistega, kar prikazuje Excel, kar je slabši rezultat kot prenašanje štirideset tisoč dni starega odmika za ena, ki se ga noben resničen datum v poslovni rabi sploh ne dotakne. Sistem 1904 nima enakovrednega fantomskega dneva, kar je bil eden od razlogov, zakaj so nekatera podjetja zgodovinsko dajala prednost temu sistemu.
Zaznavanje datuma iz numFmt
Ko število pride iz datoteke, ki jo je napisal nekdo drug, je njegovo oblikovanje edini dokaz, da gre za datum. Standard ECMA-376 dodeli blok vgrajenih id-jev oblikovanja, katerih pomen določa specifikacija, formati datuma in časa pa zasedajo znana območja. Id-ji od 14 do 22 so formati datuma in časa za splošne lokalne nastavitve, znani kot m/d/yyyy, h:mm in njihovi sorodniki. Id-ji od 45 do 47 so formati pretečenega časa. Dva dodatna pasova (od 27 do 36 ter od 50 do 58) so specifični lokalni formati datuma in časa, ki se uporabljajo za koledarje CJK, opredeljeni v ECMA-376 18.8.30. Celica, katere id številskega oblikovanja pade v katero koli od teh območij, je celica z datumom ali časom.
Vgrajeni id-ji pokrivajo običajne primere, ne pa tudi prilagojenih. Ko delovni zvezek definira svojo lastno kodo oblikovanja, recimo nestandardni vrstni red ali lokalizirano ime meseca, je id nad vgrajenim obsegom in kaže v tabelo številskih oblikovanj delovnega zvezka. Za te prepoznavanje datuma pomeni branje niza kode oblikovanja in iskanje žetonov datuma. HotXLS združi oba pregleda v en notranji predikat XlsxNumFmtIsDate, ki takoj vrne true za vgrajena datumska območja, sicer pa razčleni prilagojeno kodo oblikovanja skozi XlsxFormatCodeIsDate. Javna stran tega sta niz celice NumberFormat in njen NumberFormatIndex, ki vam dajeta tako razrešeno kodo oblikovanja kot id za testiranje.
Zakaj razčlenjevalnik oblikovanja ne more le iskati d in m
Preprosto iskanje črk, ki črkujejo datume (d, m, y, h in s za dan, mesec, leto, uro in sekundo), bo zgrešilo pri dveh strukturah, ki sploh nista žetona datuma.
Prva je niz pod narekovaji (quoted string literal). Številsko oblikovanje lahko vgradi dobesedno besedilo v dvojnih narekovajih, zato finančni format, kot je #,##0 "MM", doda črki M in M k številu brez kakršnega koli časovnega pomena. Optični bralnik (scanner), ki bi črke znotraj narekovajev štel kot žetone meseca, bi napačno označil to denarno oblikovanje kot datum. Druga je del v oglatih oklepajih. Številski formati prenašajo direktive v oglatih oklepajih, imena barv, kot je [Red], pogoje primerjave, kot je [>1000], lokalne značke in oznake pretečenega časa [h] ter [mm]. Nekatere vsebine v oklepajih vsebujejo datumske črke, nekatere pa ne, obravnava besedila v oklepajih na enak način kot telesa formata pa vodi do lažno pozitivnih rezultatov in izpuščenih primerov.
Pravilen razčlenjevalnik hodi skozi kodo oblikovanja znak za znakom in sledi temu, ali se nahaja znotraj dobesednega niza in kako globoko je znotraj gnezdenja oklepajev, prav tako pa upošteva ubežni znak z poševnico nazaj (backslash), ki citira en naslednji znak. Le neubežna datumska črka, najdena zunaj katerega koli dobesednega niza in zunaj katerega koli dela v oklepajih, šteje kot dejanski žeton datuma. Natanko tako deluje XlsxFormatCodeIsDate: narekovaj preklopi stanje "znotraj niza", kar zavre zaznavanje žetonov do zaklepnega narekovaja, poševnica nazaj preskoči naslednji znak, števec globine oklepajev pa zavre zaznavanje znotraj delov [...]. Rezultat je, da se #,##0 "MM" pravilno prebere kot številski format, medtem ko se kratka koda po meri, ki zunaj narekovajev ne vsebuje ničesar drugega kot en sam m ali d, še vedno pravilno prepozna kot datum.
Branje datumov iz datotek tretjih oseb
Ko delovni zvezek pride iz datoteke, ki jo je napisal nekdo drug, je njegovo oblikovanje edini dokaz, da gre za datum. Zaporedna številka vam da število dni, zastavica Date1904 delovnega zvezka vam pove, od katere epohe se meri štetje, id številskega oblikovanja celice ali koda po meri pa je edini dokaz, da je bo število sploh mišljeno kot datum. Opustite katerega koli od teh treh elementov in prejeli boste prepričljiv napačen odgovor namesto vidne napake.
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;Stran dednega formata BIFF ima še eno dodatno past, ki jo je vredno omeniti. V starejšem toku .xls se lahko zaporedje sosednjih številskih celic stisne v en sam zapis z več celicami (MULRK), ki v eni strukturi shranjuje več vrednosti s sklici na njihovo oblikovanje. Datumske celice, shranjene na ta način, niso nič manj datumi, zato mora isto preverjanje format-id doseči notranjost zapisa z več celicami in se uporabiti na celico, odmik 1904 pa še vedno vlada vsaki zaporedni številki, ki jo vrne. Bralnik, ki pregleduje le samostojne zapise številk in preskoči stisnjene, bo stolpec datumov potihoma spremenil v stolpec celih števil.
Preslikava zaporednih številk v TDateTime v praksi
Ko preverjanje oblikovanja potrdi datum in je znana zastavica Date1904, je pretvorba mehanska. Vrednost, ki jo HotXLS že vrne kot varDate, je TDateTime, ki jo lahko uporabite neposredno. Vrednost, ki pride kot goli Double, kar se zgodi, ko je vir zapisal zaporedno številko brez prepoznanega oblikovanja datuma, se pretvori tako, da se prebere kot število dni na osi 1900, pri delovnem zvezku 1904 pa se najprej odšteje odmik 1462 dni, da se epohi poravnata. V obratni smeri dodelitev TDateTime celici shrani zaporedno številko na osi 1900, HotXLS pa ob shranjevanju uporabi isti zamik 1462 dni, ko je delovni zvezek označen s sistemom 1904, tako da shranjena datoteka prikazuje datum, ki ste ga želeli, in ne tistega, ki je zamaknjen za štiri leta.
Zastavico nastavite namerno, ko ustvarjate delovni zvezek. Privzeto pusti Date1904 napačno (false), kar ustreza Excelu za Windows in je skoraj vedno tisto, kar želite; nastavite jo na resnično (true) le, ko poustvarjate delovni zvezek z Maca ali ko naslednji sistem izrecno pričakuje os 1904. Edino pravilo, ki preprečuje celoten razred štiriletnih napak, je doslednost: izberite epoho enkrat na delovni zvezek, zapišite vsak datum pod njo in preberite vsako zaporedno številko nazaj pod zastavico, ki jo datoteka dejansko prenaša.
Datumi so en stolpec v širši zgodbi o tem, kaj celica dejansko vsebuje. Sosednja plast metapodatkov, to so naslov, avtor in časovni žigi, ki spremljajo mrežo, je obravnavana v našem članku o metapodatkih delovnih zvezkov in lastnostih dokumentov, kjer so iste vrednosti Created in Modified shranjene kot TDateTime z enako konvencijo neaktivnosti enako nič. Ko je datum rezultat izračuna in ne shranjena vrednost, pravila ocenjevanja v našem članku o motorju za formule in funkcijah po meri določajo zaporedno številko, ki jo nato izriše oblikovanje. Oboje deluje nad istim datumskim modelom, ki se dostavlja v komponenti HotXLS Component za Delphi in C++Builder, ki bere in zapisuje datume XLS in XLSX brez avtomatizacije Excela.