Atidarykite skaičiuoklę, spustelėkite langelį, kuriame rodoma 2026-06-19, ir formulių juosta vis tiek rodys datą. Perskaitykite tą patį langelį Delphi aplinkoje, ir gausite skaičių 46192. Abu vaizdai yra teisingi, nes Excel niekada nesaugojo datos tame langelyje. Ji išsaugojo sekos numerį (dienų skaičių) ir priskyrė skaičių formatą, kuris nurodo ekranui atvaizduoti šį skaičių kaip kalendoriaus datą. Langelio reikšmėje nėra datos tipo. Yra skaičius ir rodymo taisyklė, o rodymo taisyklė yra vienintelis dalykas, kuris skiria datą nuo paprasto skaičiaus.
Šis atskyrimas yra kiekvienos datos klaidos, kurią turi apeiti skaičiuoklių biblioteka, priežastis. Sekos numeris pats savaime nepasako, kokia tai diena, nes jis nenurodo pradinio taško (dienos nulis). Tas pats skaičius reiškia dvi datas, besiskiriančias ketveriais metais, priklausomai nuo vienos darbaknygės žymos. Skaičius, kuris turėtų būti perskaitomas kaip data, bus nuskaitytas kaip paprastas skaičius, nebent kas nors patikrins jo formatą ir atpažins datos šabloną. Būtent taip sukonstruotas datos modelis HotXLS komponente.
Datos langelis yra skaičius plius formatas
Excel saugo datą kaip dienų skaičių nuo tam tikros epochos pradžios, o dienos laiką – trupmeninėje dalyje. Pavyzdžiui, vidurdienis sekos reikšmėje turi galūnę .5. Sveikoji dalis yra dienų skaičius. Nieko išsaugotoje reikšmėje nerodo, kad ji yra laikinė. Tai nurodo langelio skaičių formatas: ECMA-376 tai vadina numFmt, o langelis, kurio formato kodas aprašo datos ar laiko šabloną, rodomas kaip data. Pašalinkite formatą, ir tas pats langelis rodys paprastą skaičių – pati reikšmė niekada nepasikeitė.
Štai kodėl nuskaitant langelio reikšmę gaunamas Variant, kuris gali būti varDate arba paprastas Double, ir kodėl to paties langelio skaičių formatas yra signalas, nusakantis pradinę reikšmę. Kai HotXLS atidaro XLSX failą, langelis perduoda ir savo Value, ir savo NumberFormatIndex į TXLSXCell, o formato indeksas yra tai, ką tikrinate norėdami sužinoti, ar skaičius yra data.
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;Dvi epochos, besiskiriančios 1462 dienomis
Numatytoji datos sistema, kurią naudoja kiekviena Windows darbaknygė, skaičiuoja dienas nuo pat 1899 metų pabaigos, todėl sekos reikšmė 1 tenka pirmajai 1900 metų dienai. Kita sistema atkeliavo iš ankstyvųjų Macintosh kompiuterių ir skaičiuoja nuo 1904 metų pradžios, todėl jos sekos reikšmė 1 yra ketveriais metais ir viena diena vėliau. Darbaknygė įrašo naudojamą sistemą vienoje žymoje. OOXML pakete ši žyma yra date1904; HotXLS ją pateikia kaip darbaknygės savybę Date1904.
Skirtumas tarp dviejų epochų yra tiksliai 1462 dienos. Tai yra ketveri kalendoriaus metai (trys metai po 365 dienas ir vieni keliamieji metai iš 366 dienų, iš viso 1461 diena) plius viena papildoma diena dėl skirtumo tarp dviejų dienos nulis konvencijų. Šis skaičius yra fiksuotas ir jį lengva įsiminti. Jo svarba yra ta, kad jis nėra nulinis. Datos sekos reikšmė, nukopijuota iš 1904 metų darbaknygės ir interpretuojama pagal 1900 taisykles (arba atvirkščiai), nukrypsta 1462 dienomis, kas pasireiškia datomis, kurios yra neteisingos šiek tiek daugiau nei ketveriais metais, ir tai lengva supainioti su sugadintais duomenimis.
Kadangi pačios Delphi TDateTime yra susieta su 1900 konvencija, biblioteka, kuri susieja Excel sekos reikšmes su TDateTime, turi pritaikyti 1462 dienų poslinkį abiem kryptimis, kai darbaknygė pažymėta 1904 žyma. Nuskaitant 1904 sekos reikšmę, atimkite 1462 prieš naudodami ją kaip TDateTime; įrašydami TDateTime į 1904 darbaknygę, atimkite 1462 iš sekos reikšmės, kad Excel atvaizduotų jūsų pageidaujamą dieną. HotXLS pritaiko šis poslinkis viduje, kai serializuoja datos reikšmes darbaknygei su įjungta Date1904 savybe, todėl jūsų priskirta TDateTime reikšmė ekrane rodoma teisingai.
Sąmoninga 1900-ųjų keliamųjų metų anomalija
Excel 1900 metų sistemoje yra viena garsi keistenybė. Excel laiko 1900-uosius keliamaisiais metais ir priima 1900 m. vasario 29 d. kaip tikrą datą (sekos reikšmė 60). Iš tikrųjų 1900 metai nebuvo keliamieji, nes amžių metai yra keliamieji tik tada, kai dalijasi iš 400, o 1900 nesidalija. Ši išgalvota diena yra sąmoningas suderinamumo elgesys, paveldėtas iš senų skaičiuoklių, kad nesugriūtų dešimtmečių skaičiavimai.
Praktinė pasekmė nedidelė: bet kuriai datai nuo 1900 m. kovo 1 d. sekos skaičius yra vienu vienetu didesnis, nei turėtų būti. Skaičiuoklių biblioteka atkuria šią keistenybę, užuot ją taisiusi, nes tikslus atitikimas Excel skaičiavimams yra visas mūsų darbas. Jos taisymas pastatytų visas šiuolaikines datas viena diena vėliau, o tai būtų blogesnis rezultatas nei senos klaidos išlaikymas. 1904 metų sistema neturi tokios netikros dienos, todėl kai kurios įmonės anksčiau jai teikė pirmenybę.
Datos atpažinimas iš numFmt
Kai skaičius ateina iš failo, kurį parašė kita programa, jo formatas yra vienintelis įrodymas, kad tai data. ECMA-376 priskiria standartinius formatų ID, kurių reikšmė apibrėžta specifikacijoje, o datos ir laiko formatai užima žinomus rėžius. ID nuo 14 iki 22 yra bendrieji datos ir laiko formatai (m/d/yyyy, h:mm ir kt.). ID nuo 45 iki 47 yra praėjusio laiko formatai. Dvi papildomos grupės (27–36 ir 50–58) yra regioniniai datos ir laiko formatai, naudojami CJK kalendoriams, apibrėžti ECMA-376 18.8.30. Langelis su tokiu formato ID yra datos arba laiko langelis.
Standartiniai ID apima įprastus atvejus, bet ne pasirinktinius. Kai darbaknygė apibrėžia savo formato kodą, ID yra didesnis ir rodo į darbaknygės formatų lentelę. Norint atpažinti datą tokiuose formatuose, reikia perskaityti kodo eilutę ir ieškoti datos simbolių. HotXLS sujungia abu šiuos tikrinimus į vidinį predikatą XlsxNumFmtIsDate, kuris grąžina true standartiniams ID ir analizuoja pasirinktinį kodą per XlsxFormatCodeIsDate. Viešoji to pusė yra langelio NumberFormat eilutė ir NumberFormatIndex.
Kodėl formato parseris negali tiesiog ieškoti d ir m raidžių
Formato kodo analizavimas datos ieškojimui atrodo paprastas dalykas, kol neprisimename, kas dar gali būti skaičių formate.
Pirmasis dalykas yra tekstas kabutėse. Skaičių formatas gali turėti tekstą dvigubose kabutėse, pavyzdžiui, #,##0 "MM" prideda raides M ir M prie skaičiaus be jokios laiko reikšmės. Paprastas ieškiklis, kuris ieškotų raidės M, klaidingai pažymėtų šį valiutos formatą kaip datą. Antrasis yra skliaustų sekcija. Skaičių formatai turi nurodymus laužtiniuose skliaustuose, pavyzdžiui, spalvų pavadinimus [Red], palyginimo sąlygas [>1000] ar regionines žymas. Kai kuriose skliaustų dalyse yra datos raidės, o kai kuriose ne, todėl paprastas teksto skenavimas duotų daug klaidingų teigiamų rezultatų.
Teisingas parseris eina per kodo simbolius po vieną, sekdamas, ar jie yra kabutėse ir kokiame skliaustų gylyje randasi, bei atsižvelgia į pasvirusio brūkšnio (escape) simbolį. Tik pabėgęs datos simbolis už kabučių ir už laužtinių skliaustų ribų yra tikroji data. Būtent taip veikia XlsxFormatCodeIsDate: kabutės perjungia vidinį režimą, kuris blokuoja atpažinimą iki uždarymo kabutės, pasviręs brūkšnys praleidžia sekantį simbolį, o skliaustų skaitiklis blokuoja atpažinimą [...] viduje. Dėl to formatas #,##0 "MM" teisingai suprantamas kaip skaičiaus formatas, o trumpas kodas su viena m raide už kabučių vis tiek atpažįstamas kaip data.
Datų skaitymas iš trečiųjų šalių failų
Visi šie dalykai sueina į vieną procesą: skaičiaus, kurį įrašė kita programa, pavertimą data, kuria galite pasitikėti. Sekos skaičius duoda dienų skaičių, darbaknygės Date1904 žyma nurodo epochą, o langelio formato ID arba pasirinktinis kodas yra vienintelis įrodymas, kad skaičius buvo skirtas datai. Praleiskite bet kurį iš jų, ir gausite tikėtiną neteisingą atsakymą vietoj matomos klaidos.
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;Senoji BIFF formato pusė turi vieną papildomą spąstą. Senesniame .xls sraute gretimi skaitiniai langeliai gali būti supakuoti į vieną MULRK įrašą, kuris saugo kelias reikšmes su jų formato nuorodomis vienoje struktūroje. Datos langeliai, išsaugoti šiuo būdu, vis tiek yra datos, todėl tas pats formato ID testas turi pasiekti kiekvieną atskirą langelį MULRK įrašo viduje, o 1904 poslinkis vis tiek valdo kiekvieną sekos numerį. Skaitytuvas, kuris tikrina tik atskirus skaičių įrašus ir praleidžia supakuotus, tyliai pavers datų stulpelį sveikųjų skaičių stulpeliu.
Datos yra vienas stulpelis platesnėje istorijoje apie tai, ką iš tikrųjų talpina langelis. Susijęs metaduomenų sluoksnis, pavyzdžiui, autorius ir sukūrimo laikas, yra aprašytas mūsų straipsnyje apie darbaknygės metaduomenis ir dokumento savybes. Kai data yra skaičiavimo rezultatas, o ne tiesioginė reikšmė, vertinimo taisyklės aprašytos straipsnyje apie formulių variklį ir pasirinktines funkcijas. Abu šie sprendimai veikia tame pačiame datos modelyje, kurį naudoja HotXLS Component, skirtas Delphi ir C++Builder, leidžiantis skaityti bei rašyti XLS ir XLSX datas nenaudojant Excel automatizavimo.