Deschideți o foaie de calcul, faceți clic pe o celulă care arată 2026-06-19, iar bara de formule afișează în continuare o dată. Citiți aceeași celulă din Delphi și obțineți numărul 46192. Ambele vizualizări sunt corecte, deoarece Excel nu a stocat niciodată o dată în acea celulă. A stocat un număr serial, un număr de zile și a atașat un format de număr care spune ecranului să redea contorul ca pe o dată din calendar. Nu există un tip de dată în valoarea celulei. Există un număr și o regulă de afișare, iar regula de afișare este singurul lucru care distinge o dată de o simplă cantitate.
Această separare este rădăcina fiecărui bug de dată pe care o bibliotecă de foi de calcul trebuie să îl evite. Un număr serial singur nu spune ce zi este, deoarece nu spune ce zi a fost ziua zero. Același număr înseamnă două date la patru ani distanță, în funcție de un singur indicator de registru de lucru. Iar un număr care ar trebui citit înapoi ca o dată va fi citit ca o simplă cantitate, dacă ceva nu inspectează formatul său și recunoaște un model de dată. Acesta este modul în care este construit modelul de dată în HotXLS și motivul pentru care trebuie să fie așa.
O celulă de tip dată este un număr plus un format
Excel stochează o dată ca numărul de zile de la o epocă, cu ora din zi în partea fracționară. Amiaza pe o valoare serială poartă .5. Partea întreagă este numărul de zile. Nimic din valoarea stocată nu o marchează ca fiind temporală. Ceea ce o marchează este formatul de număr al celulei: ECMA-376 numește acest lucru un numFmt, iar o celulă al cărei cod de format definește un model de dată sau de timp este afișată ca dată. Eliminați formatul și aceeași celulă arată un număr; valoarea de bază nu s-a schimbat niciodată.
De aceea, citirea unei valori de celulă vă oferă un Variant care poate fi un varDate sau poate fi un simplu Double, și de aceea formatul de număr de pe aceeași celulă este semnalul care decide ce a vrut să spună un terț. Când HotXLS deschide un fișier XLSX, o celulă își transportă atât Value, cât și NumberFormatIndex în TXLSXCell, iar indexul de format este cel pe care îl consultați pentru a afla dacă numărul este o 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;Două epoci, la o distanță de 1462 de zile
Sistemul de date implicit, cel pe care îl folosește fiecare registru de lucru Windows, numără de la sfârșitul anului 1899, astfel încât valoarea serială 1 cade în prima zi a anului 1900. Celălalt sistem își are originea în primele computere Macintosh și numără de la începutul anului 1904, astfel încât numărul său serial 1 este patru ani și o zi mai târziu. Un registru de lucru înregistrează sistemul pe care îl folosește într-un singur indicator. Într-un pachet OOXML, acel indicator este date1904 pe componenta registrului de lucru; HotXLS îl expune ca proprietate Date1904 a registrului de lucru.
Decalajul dintre cele două epoci este de exact 1462 de zile. Aceasta înseamnă patru ani calendaristici, trei de 365 de zile și unul de 366, însumând 1461, plus încă unul pentru decalajul de o zi și ceva între cele două convenții de zi zero. Numărul este fix și îl puteți reține. Importanța sa este că nu este zero. Un număr serial copiat dintr-un registru de lucru 1904 și interpretat conform regulilor din 1900, sau invers, plasează fiecare dată la o distanță de 1462 de zile, ceea ce se prezintă ca date greșite cu puțin peste patru ani și este ușor de confundat cu date corupte.
Deoarece tipul propriu al Delphi TDateTime este ancorat la convenția din 1900, o bibliotecă ce mapează numerele seriale Excel la TDateTime trebuie să le decalaze cu 1462 în ambele direcții ori de câte ori registrul de lucru este marcat cu 1904. Citind un număr serial 1904, scădeți 1462 înainte de a-l trata ca TDateTime; scriind un TDateTime într-un registru de lucru 1904, scădeți 1462 din numărul serial, astfel încât Excel să redea ziua pe care ați dorit-o. HotXLS aplică această schimbare intern când serializează valorile de dată pentru un registru de lucru al cărui indicator Date1904 este setat, astfel încât valoarea pe care o atribuiți ca TDateTime revine pe ecran în aceeași zi din calendar.
Particularitatea deliberată a anului bisect 1900
Există o particularitate faimoasă în sistemul din 1900. Excel tratează anul 1900 ca pe un an bisect și acceptă 29 februarie 1900 ca pe o dată reală, cu numărul serial 60. Anul 1900 nu a fost un an bisect, deoarece anii de secol sunt ani bisecți doar atunci când sunt divizibili cu 400, iar 1900 nu este. Ziua fantomă este un comportament de compatibilitate deliberat moștenit de la un program de calcul timpuriu care a fost livrat cu acest bug, păstrat de atunci astfel încât aritmetica serială să rămână identică de-a lungul deceniilor de fișiere.
Consecința practică este mică, dar reală: pentru orice dată la sau după 1 martie 1900, numărul serial este cu unu mai mare decât ar rezulta dintr-un contor strict corect de zile, deoarece ziua inexistentă de 29 februarie a consumat un număr. O bibliotecă de foi de calcul reproduce anomalia în loc să o corecteze, deoarece potrivirea exactă cu aritmetica Excel este întreaga sarcină. Corectarea acesteia ar decala fiecare dată modernă cu o zi față de ceea ce arată Excel, ceea ce este un rezultat mai rău decât păstrarea unui decalaj vechi de patruzeci de mii de zile pe care nicio dată reală din mediul de afaceri nu o atinge. Sistemul din 1904 nu are o zi fantomă echivalentă, ceea ce reprezintă unul dintre motivele pentru care unele companii l-au preferat istoric.
Detectarea unei date din numFmt
Când un număr provine dintr-un fișier scris de altcineva, formatul său este singura dovadă că reprezintă o dată. ECMA-376 alocă un bloc de ID-uri de format încorporate a căror semnificație este fixată de specificație, iar formatele de dată și oră ocupă intervale cunoscute. ID-urile de la 14 la 22 sunt formatele de dată și oră pentru setările regionale generale, cunoscutele m/d/yyyy, h:mm și rudele lor. ID-urile de la 45 la 47 sunt formatele de timp scurs. Două benzi suplimentare, de la 27 la 36 și de la 50 la 58, sunt formatele de dată și oră specifice setărilor regionale folosite pentru calendarele CJK, definite în ECMA-376 18.8.30. O celulă al cărei ID de format de număr se încadrează în oricare dintre aceste intervale este o celulă de dată sau oră.
ID-urile încorporate acoperă cazurile comune, dar nu și pe cele personalizate. Când un registru de lucru își definește propriul cod de format, să zicem o ordonare nestandardizată sau un nume de lună localizat, ID-ul se află peste intervalul încorporat și indică spre tabelul de formate de numere al registrului de lucru. Pentru acestea, recunoașterea unei date înseamnă citirea șirului codului de format și căutarea elementelor (tokens) de dată. HotXLS reunește ambele verificări într-un singur predicat intern, XlsxNumFmtIsDate, care returnează true imediat pentru intervalele de date încorporate și, în caz contrar, analizează codul de format personalizat prin XlsxFormatCodeIsDate. Partea publică a acestui proces este șirul NumberFormat al celulei și proprietatea sa NumberFormatIndex, care vă oferă atât codul de format rezolvat, cât și ID-ul de testat.
De ce parserul de format nu poate doar să caute caracterele d și m
Analiza unui cod de format pentru elemente de dată pare simplă până când vă amintiți ce altceva mai conține un format de număr. O căutare naivă a literelor care definesc datele, d, m, y, h și s pentru zi, lună, an, oră și secundă, va eșua pe două structuri care nu sunt deloc elemente de dată.
Prima este șirul literal între ghilimele. Un format de număr poate încorpora text literal între ghilimele duble, astfel încât un format financiar precum #,##0 "MM" adăugă caracterele M și M la un număr fără nicio semnificație temporală. Un scaner care numără literele din ghilimele ca elemente de lună ar marca în mod eronat acel format de monedă ca dată. A doua este secțiunea dintre paranteze pătrate. Formatele de numere conțin directive în paranteze pătrate, nume de culori precum [Red], condiții de comparare precum [>1000], etichete locale și marcaje de timp scurs [h] și [mm]. Unele conținuturi din paranteze conțin litere de dată, iar altele nu, iar tratarea textului din paranteze la fel ca restul formatului duce atât la rezultate fals pozitive, cât și la cazuri ratate.
Analizorul corect parcurge codul de format caracter cu caracter, urmărind dacă se află în interiorul unui literal între ghilimele și cât de adânc se află în parantezele pătrate imbricate, și respectă de asemenea caracterul de evadare backslash care protejează un singur caracter următor. Numai o literă de dată neprotejată găsită în afara oricărui șir literal și în afara oricărei secțiuni de paranteze pătrate contează ca un element de dată real. Exact așa scanează XlsxFormatCodeIsDate: ghilimelele comută o stare în-literal care suprimă detectarea elementelor până la ghilimelele de închidere, un backslash omite următorul caracter, iar un contor al adâncimii parantezelor suprimă detectarea în interiorul secvențelor [...]. Câștigul este că #,##0 "MM" este citit corect ca format de număr, în timp ce un cod personalizat scurt care nu conține nimic altceva decât un singur m sau d în afara ghilimelelor este recunoscut corect ca dată.
Citirea datelor din fișiere terțe
Everything above converges on one workflow: turning a number that some other application wrote back into a date you can trust. The serial gives you the day count, the workbook's Date1904 flag tells you which epoch the count is measured from, and the cell's number format id or custom code is the single piece of evidence that the number was meant as a date in the first place. Drop any one of the three and you get a plausible wrong answer rather than a visible error.
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;Partea BIFF veche are o capcană suplimentară care merită menționată. Într-un flux .xls mai vechi, o serie de celule numerice adiacente poate fi împachetată într-o singură înregistrare multi-celulă, MULRK, care stochează mai multe valori cu referințele lor de format într-o singură structură. Celulele de dată stocate în acest mod nu sunt mai puțin date din cauză că sunt împachetate, așa că același test de ID de format trebuie să ajungă în interiorul înregistrării multi-celulă și să se aplice per celulă, iar decalajul 1904 guvernează în continuare fiecare număr serial pe care îl produce. Un cititor care inspectează doar înregistrările de numere independente și le omite pe cele împachetate va transforma în mod silențios o coloană de date într-o coloană de întregi.
Maparea numerelor seriale la TDateTime în practică
Odată ce verificarea formatului confirmă o dată și indicatorul Date1904 este cunoscut, conversia este mecanică. O valoare pe care HotXLS o returnează deja ca varDate este un TDateTime pe care îl puteți folosi direct. O valoare care sosește ca un simplu Double, ceea ce se întâmplă atunci când sursa a scris un număr serial fără un format de dată recunoscut, este convertită citind-o ca un număr de zile pe axa 1900 și, pentru un registru de lucru 1904, scăzând mai întâi decalajul de 1462 de zile, astfel încât epocile să se alinieze. În sens invers, atribuirea unui TDateTime unei celule stochează numărul serial bazat pe 1900, iar HotXLS aplică aceeași deplasare de 1462 de zile la salvare când registrul de lucru este marcat cu 1904, astfel încât fișierul salvat arată data pe care ați dorit-o, nu una decalată cu patru ani.
Setați indicatorul în mod deliberat atunci când generați un registru de lucru. Valoarea implicită lasă Date1904 setat pe false, ceea ce corespunde Excel pentru Windows și este aproape întotdeauna ceea ce doriți; setați-l pe true numai atunci când reproduceți un registru de lucru provenit de pe Mac sau un sistem din aval așteaptă în mod specific axa 1904. Singura regulă care previne întreaga clasă de erori de patru ani este consecvența: alegeți epoca o singură dată per registru de lucru, scrieți fiecare dată sub ea și citiți fiecare număr serial sub indicatorul pe care fișierul îl poartă de fapt.
Datele sunt o singură coloană dintr-o poveste mai largă despre ce conține cu adevărat o celulă. Stratul de metadate învecinat, titlul, autorul și marcajele de timp care însoțesc grila, sunt acoperite în articolul nostru despre metadatele registrului de lucru și proprietățile documentului, unde aceleași valori Created și Modified sunt stocate ca TDateTime cu aceeași convenție în care nesetat este egal cu zero. Când o dată este rezultatul unui calcul, mai degrabă decât o valoare stocată, regulile de evaluare din articolul nostru despre motorul de formule și funcțiile personalizate determină numărul serial pe care formatul îl redă apoi. Ambele funcționează pe baza aceluiași model de dată livrat în HotXLS Component pentru Delphi și C++Builder, care citește și scrie date XLS și XLSX fără automatizarea Excel.