Avaa taulukkolaskentatiedosto, napsauta solua, joka näyttää arvon 2026-06-19, ja kaavarivi lukee edelleen päivämäärän. Lue sama solu Delphistä, ja saat luvun 46192. Molemmat näkymät ovat oikein, koska Excel ei koskaan tallentanut päivämäärää kyseiseen soluun. Se tallensi sarjanumeron eli päivien määrän, ja liitti siihen numeromuotoilun, joka käskee näytön renderöidä määrän kalenteripäivämääränä. Solun arvossa ei ole päivämäärätyyppiä. Siinä on luku ja näyttösääntö, ja näyttösääntö on ainoa asia, joka erottaa päivämäärän tavallisesta määrästä.
Tämä erottelu on juurena jokaiselle päivämäärävirheelle, jota taulukkolaskentakirjaston on väistettävä. Sarjanumero yksin ei kerro mikä päivä on kyseessä, koska se ei kerro mikä oli päivä nolla. Sama luku tarkoittaa kahta päivämäärää neljän vuoden erolla riippuen yhdestä työkirjan lipusta. Ja luku, joka pitäisi lukea takaisin päivämääränä, luetaan takaisin pelkkänä määränä, ellei jokin tarkasta sen muotoilua ja tunnista päivämääräkuviota. Näin HotXLS-komponentin päivämäärämalli on rakennettu, ja miksi sen on oltava.
Päivämääräsolu on luku plus muotoilu
Excel tallentaa päivämäärän päivien määränä tietystä aikakaudesta (epoch) alkaen, vuorokaudenajan ollessa murto-osassa. Keskipäivä sarjassa kantaa arvoa .5. Kokonaislukuosa on päivien määrä. Mikään tallennetussa arvossa ei merkitse sitä ajalliseksi. Sen merkitsee solun numeromuotoilu: ECMA-376 kutsuu tätä nimellä numFmt, ja solu, jonka muotoilukoodi ilmaisee päivämäärä- tai aikakuvion, näytetään päivämääränä. Poista muotoilu, ja sama solu näyttää luvun; taustalla oleva arvo ei koskaan muuttunut.
Tästä syystä solun arvon lukeminen antaa sinulle Variant-tyypin, joka voi olla varDate tai tavallinen Double, ja miksi saman solun numeromuotoilu on signaali, joka päättää, kumpaa kolmas osapuoli tarkoitti. Kun HotXLS avaa XLSX-tiedoston, solu kantaa sekä Value- että NumberFormatIndex-arvonsa TXLSXCell-olioon, ja muotoiluindeksi on se, josta katsot, onko luku päivämäärä.
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;Kaksi aikakautta, 1462 päivän erolla
Oletusarvoinen päivämääräjärjestelmä, jota jokainen Windows-työkirja käyttää, laskee vuoden 1899 lopusta alkaen, joten sarja 1 osuu vuoden 1900 ensimmäiselle päivälle. Toinen järjestelmä juontaa juurensa varhaiseen Macintoshiin ja laskee vuoden 1904 alusta alkaen, joten sen sarja 1 on neljä vuotta ja päivää myöhemmin. Työkirja tallentaa käyttämänsä järjestelmän yhteen lippuun. OOXML-paketissa tämä lippu on date1904 työkirjan osassa; HotXLS tuo sen esiin työkirjan Date1904-ominaisuutena.
Kahden aikakauden välinen ero on täsmälleen 1462 päivää. Se on neljä kalenterivuotta - kolme 365 päivän vuotta ja yksi 366 päivän vuosi, yhteensä 1461 päivää - plus yksi lisäpäivä kahden päivän nolla-sopimuksen välisestä siirtymästä. Luku on kiinteä ja voit pitää sen mielessäsi. Sen merkitys on siinä, että se ei ole nolla. 1904-työkirjasta kopioitu sarja, joka tulkitaan 1900-sääntöjen mukaan, tai päinvastoin, asettaa jokaisen päivämäärän 1462 päivää sivuun, mikä näyttää reilun neljän vuoden virheeltä ja on helppo luulla vioittuneeksi dataksi.
Koska Delphin oma TDateTime on ankkuroitu 1900-sopimukseen, kirjaston, joka kartoittaa Excel-sarjoja TDateTime-tyypille, on siirrettävä arvoa 1462 päivällä molempiin suuntiin aina, kun työkirja on liputettu arvolla 1904. Luettaessa 1904-sarjaa, vähennä 1462 ennen sen käsittelemistä TDateTime-arvona; kirjoitettaessa TDateTime 1904-työkirjaan, vähennä 1462 sarjasta, jotta Excel renderöi tarkoittamasi päivän. HotXLS soveltaa tätä siirtymää sisäisesti, kun se serialisoi päivämääräarvoja työkirjalle, jonka Date1904 on asetettu, joten TDateTime-arvona määrittämäsi arvo round-trippaa samaksi kalenteripäiväksi näytöllä.
Tahallinen vuoden 1900 karkausvuosi-omituisuus
1900-järjestelmässä on kuuluisa ryppy. Excel kohtelee vuotta 1900 karkausvuotena ja hyväksyy 29. helmikuuta 1900 todelliseksi päivämääräksi sarjalla 60. Vuosi 1900 ei ollut karkausvuosi, koska vuosisadat ovat karkausvuosia vain silloin, kun ne ovat jaettavissa 400:lla, eikä 1900 ole. Haamupäivä on tahallinen yhteensopivuuskäyttäytyminen, joka on peritty varhaisesta taulukkolaskentaohjelmasta, jossa oli tämä virhe, ja se on säilytetty siitä lähtien, jotta sarja-aritmetiikka pysyy identtisenä vuosikymmenten tiedostojen yli.
Käytännön seuraus on pieni mutta todellinen: mille tahansa päivämäärälle 1. maaliskuuta 1900 tai sen jälkeen sarjanumero on yhtä suurempi kuin mitä tiukasti oikea päivien määrä antaisi, koska olemassaolematon 29. helmikuuta kulutti yhden numeron. Taulukkolaskentakirjasto toistaa tämän omituisuuden sen korjaamisen sijaan, koska Excelin aritmetiikan täsmällinen vastaaminen on koko tehtävä. Sen korjaaminen asettaisi jokaisen modernin päivämäärän yhden päivän sivuun siitä, mitä Excel näyttää, mikä on huonompi lopputulos kuin kantaa neljäkymmentätuhatta päivää vanhaa off-by-one-virhettä, johon mikään todellinen liikekäytössä oleva päivämäärä ei koskaan koske. 1904-järjestelmässä ei ole vastaavaa haamupäivää, mikä on yksi syy siihen, miksi jotkut yritykset historiallisesti pitivät siitä enemmän.
Päivämäärän havaitseminen numFmt-arvosta
Kun luku saapuu jonkun toisen kirjoittamasta tiedostosta, sen muotoilu on ainoa todiste siitä, että se on päivämäärä. ECMA-376 määrittää lohkon sisäänrakennettuja muotoilu-id:itä, joiden merkitys on kiinteä määrityksissä, ja päivämäärä- sekä aikamuodot miehittävät tunnettuja alueita. ID:t 14-22 ovat yleisen lokaalin päivämäärä- ja aikamuotoja, tuttuja m/d/yyyy, h:mm ja niiden sukulaisia. ID:t 45-47 ovat kuluneen ajan muotoja. Kaksi muuta aluetta, 27-36 ja 50-58, ovat lokaalikohtaisia päivämäärä- ja aikamuotoja, joita käytetään CJK-kalentereissa, määriteltynä kohdassa ECMA-376 18.8.30. Solu, jonka numeromuotoilun id osuu johonkin näistä alueista, on päivämäärä- tai aikasolu.
Sisäänrakennetut id:t kattavat yleiset tapaukset, mutta eivät mukautettuja. Kun työkirja määrittelee oman muotoilukoodinsa, vaikkapa epästandardin järjestyksen tai lokalisoidun kuukauden nimen, id on sisäänrakennetun alueen yläpuolella ja osoittaa työkirjan numeromuotoilutaulukkoon. Niiden kohdalla päivämäärän tunnistaminen tarkoittaa muotoilukoodimerkkijonon lukemista ja päivämäärämerkkien (tokens) etsimistä. HotXLS yhdistää molemmat tarkistukset yhdeksi sisäiseksi predikaatiksi, XlsxNumFmtIsDate, joka palauttaa toden heti sisäänrakennetuille päivämääräalueille ja muuten jäsentää mukautetun muotoilukoodin XlsxFormatCodeIsDate-metodin kautta. Tämän julkinen puoli on solun NumberFormat-merkkijono ja sen NumberFormatIndex, jotka antavat sinulle sekä selvitetyn muotoilukoodin että testattavan id:n.
Miksi muotoilujäsennin ei voi vain etsiä d- ja m-merkkejä
Muotoilukoodin jäsentäminen päivämäärämerkkien varalta näyttää vähäpätöiseltä, kunnes muistat mitä muuta numeromuotoilussa elää. Naiivi haku kirjaimille, jotka muodostavat päivämäärät - kuten päivän, kuukauden, vuoden, tunnin ja sekunnin d, m, y, h ja s - epäonnistuu kahdessa rakenteessa, jotka eivät ole lainkaan päivämäärämerkkejä.
Ensimmäinen on lainausmerkeissä oleva merkkijonoliteraali. Numeromuotoilu voi upottaa literaalitekstiä kaksoislainausmerkkeihin, joten talousmuotoilu kuten #,##0 "MM" liittää merkit M ja M lukuun ilman mitään ajallista merkitystä. Skanneri, joka laskee lainausmerkkien sisällä olevat kirjaimet kuukausimerkeiksi, liputtaisi virheellisesti kyseisen valuuttamuodon päivämääräksi. Toinen on hakasulku-osio. Numeromuotoilut kantavat direktiivejä hakasulkeissa: värinimiä kuten [Red], vertailuehtoja kuten [>1000], lokaalitunnisteita sekä kuluneen ajan merkintöjä [h] ja [mm]. Osa hakasulkujen sisällöstä sisältää päivämääräkirjaimia ja osa ei, ja hakasulkutekstin kohtelu samalla tavalla kuin muotoilun rungon johtaa sekä vääriin hälytyksiin että ohi menneisiin tapauksiin.
Oikea jäsennin käy muotoilukoodia läpi merkki merkiltä, seuraten onko se lainatun literaalin sisällä ja kuinka syvällä hakasulkupesässä se on, ja se kunnioittaa myös takakeno-pakomerkkiä (backslash escape), joka pakottaa yhden seuraavan merkin. Vain pakenematon päivämääräkirjain, joka löytyy minkä tahansa merkkijonoliteraalin ulkopuolelta ja minkä tahansa hakasulkuosion ulkopuolelta, lasketaan todelliseksi päivämäärämerkiksi. Juuri näin XlsxFormatCodeIsDate skannaa: lainausmerkki kääntää literaalitilan, joka estää merkkien havaitsemisen sulkeutuvaan lainausmerkkiin asti, takakeno ohittaa seuraavan merkin ja hakasulkujen syvyyslaskuri estää havaitsemisen [...]-ajojen sisällä. Tuloksena on, että #,##0 "MM" luetaan oikein numeromuodoksi, kun taas suppea mukautettu koodi, joka ei sisällä muuta kuin yksittäisen m- tai d-merkin lainausmerkkien ulkopuolella, tunnistetaan silti oikein päivämääräksi.
Päivämäärien lukeminen kolmansien osapuolten tiedostoista
Kaikki yllä oleva tiivistyy yhteen työnkulkuun: jonkin toisen sovelluksen kirjoittaman luvun muuttaminen takaisin päivämääräksi, johon voit luottaa. Sarja-arvo antaa sinulle päivien määrän, työkirjan Date1904-lippu kertoo, mistä aikakaudesta määrä on mitattu, ja solun numeromuotoilun id tai mukautettu koodi on ainoa todiste siitä, että luku oli alun perin tarkoitettu päivämääräksi. Jätä mikä tahansa näistä kolmesta pois, ja saat uskottavan väärän vastauksen näkyvän virheen sijaan.
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;Vanhalla BIFF-puolella on yksi nimeämisen arvoinen lisäansa. Vanhemmassa .xls-virrassa vierekkäisten numeeristen solujen sarja voidaan pakata yhteen monisolutietueeseen (MULRK), joka tallentaa useita arvoja muotoilujärjestelmineen yhdessä rakenteessa. Tällä tavalla tallennetut päivämääräsolut eivät ole vähemmän päivämääriä pakkauksesta huolimatta, joten saman format-id-testin on ulotuttava monisolutietueen sisään ja pädettävä solukohtaisesti, ja 1904-siirtymä hallitsee edelleen jokaista sen tuottamaa sarja-arvoa. Lukija, joka tarkastaa vain erilliset lukutietueet ja ohittaa pakatut, muuttaa hiljaa päivämääräsarakkeen kokonaislukujen sarakkeeksi.
Sarjojen kartoittaminen TDateTime-tyypille käytännössä
Kun muotoilutarkistus vahvistaa päivämäärän ja Date1904-lippu on tiedossa, muunnos on mekaaninen. Arvo, jonka HotXLS jo palauttaa varDate-tyyppinä, on TDateTime, jota voit käyttää suoraan. Arvo, joka saapuu pelkkänä Double-tyyppinä - mikä tapahtuu, kun lähde kirjoitti sarjan ilman tunnistettua päivämäärämuotoilua - muunnetaan lukemalla se päivien määränä 1900-akselilla ja 1904-työkirjalle vähentämällä ensin 1462 päivän siirtymä, jotta aikakaudet linjautuvat. Toisinpäin mentäessä TDateTime-arvon asettaminen soluun tallentaa 1900-pohjaisen sarjan, ja HotXLS soveltaa samaa 1462 päivän siirtymää tallennettaessa, kun työkirja on liputettu arvolla 1904, joten tallennettu tiedosto näyttää tarkoittamasi päivämäärän sen sijaan, että se olisi neljä vuotta sivuun.
Aseta lippu harkitusti, kun luot työkirjan. Oletusarvo jättää Date1904-lipun epätodeksi, mikä vastaa Windowsin Exceliä ja on melkein aina sitä mitä haluat; aset se todeksi vain silloin, kun toistat Mac-alkuista työkirjaa tai jokin alavirran järjestelmä erityisesti odottaa 1904-akselia. Sääntö, joka estää koko neljän vuoden virheluokan, on johdonmukaisuus: valitse aikakausi kerran työkirjaa kohden, kirjoita jokainen päivämäärä sen alle ja lue jokainen sarja takaisin sen lipun alla, jota tiedosto todellisuudessa kantaa.
Päivämäärät ovat yksi sarake laajemmassa tarinassa siitä, mitä solu todellisuudessa sisältää. Naapuruston metatietokerros - eli ruudukon rinnalla kulkevat otsikko, tekijä ja aikaleimat - käsitellään our article on workbook metadata and document properties, joissa samat Created- ja Modified-arvot tallennetaan TDateTime-arvoina samalla asettamaton-yhtä kuin-nolla-sopimuksella. Kun päivämäärä on laskennan tulos tallennetun arvon sijaan, arviointisäännöt our article on the formula engine and custom functions määrittävät sarjan, jonka muotoilu sitten renderöi. Molemmat toimivat saman päivämäärämallin päällä, joka toimitetaan Delphi- ja C++Builder-ohjelmille tarkoitetussa HotXLS Component -komponentissa, joka lukee ja kirjoittaa XLS- ja XLSX-päivämääriä ilman Excel-automaatiota.