Åpne et regneark, klikk på en celle som viser 2026-06-19, og formellinjen viser fortsatt en dato. Les den samme cellen fra Delphi, og du får tallet 46192. Begge visninger er riktige, fordi Excel aldri lagret en dato i denne cellen. Den lagret et serienummer, en telling av dager, og la til et tallformat som forteller skjermen at den skal tegne tellingen som en kalenderdato. Det finnes ingen datotype i celleverdien. Det finnes et tall og en visningsregel, og visningsregelen er det eneste som skiller en dato fra en vanlig mengde.
Dette skillet er kilden til enhver datofeil et regnearkbibliotek må unngå. En datoeserie alene forteller ikke hvilken dag det er, fordi den ikke forteller hva dag null var. Det samme tallet betyr to datoer med fire års mellomrom, avhengig av et enkelt flagg i arbeidsboken. Og et tall som burde leses inn igjen som en dato, vil leses inn som en ren mengde med mindre noe inspiserer formatet og gjenkjenner et datomønster. Det er slik datomodellen i HotXLS er bygget, og hvorfor den må være det.
En datocelle er et tall pluss et format
Excel lagrer en dato som antall dager siden en epoke, med klokkeslettet i den desimale delen. Midt på dagen på en datoeserie bærer .5. Heltallsdelen er dagtellingen. Ingenting i den lagrede verdien markerer den som tidsmessig. Det som markerer den, er cellens tallformat: ECMA-376 kaller dette en numFmt, og en celle hvis formatkode staver ut et dato- eller klokkeslettmønster vises som en dato. Fjern formatet, og den samme cellen viser et tall; den underliggende verdien endret seg aldri.
Dette er grunnen til at lesing av en celleverdi gir deg en Variant som kan være en varDate eller en vanlig Double, og hvorfor tallformatet på cellen er signalet som bestemmer hva en tredjepart mente. Når HotXLS åpner en XLSX-fil, en celle bærer med seg både sin Value og sin NumberFormatIndex inn i TXLSXCell, og formatindeksen er det du sjekker for å finne ut om tallet er en dato.
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;To epoker, med 1462 dagers mellomrom
Standard datosystem, det som alle Windows-arbeidsbøker bruker, teller fra slutten av 1899, slik at serieverdi 1 faller på den første dagen i 1900. Det andre systemet sporer tilbake til tidlig Macintosh og teller fra starten av 1904, så dets serieverdi 1 er fire år og én dag senere. En arbeidsbok registrerer hvilket system den bruker i et enkelt flagg. I en OOXML-pakke er dette flagget date1904 i arbeidsbokdelen; HotXLS eksponerer det som Date1904-egenskapen til arbeidsboken.
Gapet mellom de to epokene er nøyaktig 1462 dager. Det er fire kalenderår, tre på 365 dager og ett på 366, totalt 1461, pluss én dag ekstra for forskyvningen mellom de to dag-null-konvensjonene. Tallet er fast, og du kan huske det i hodet. Det viktige er at det ikke er null. En datoeserie kopiert ut av en 1904-arbeidsbok og tolket under 1900-regler, eller omvendt, plasserer hver dato 1462 dager feil. Dette fremstår som datoer som er feil med litt over fire år, og er lett å forveksle med korrupte data.
Fordi Delphis egen TDateTime er forankret til 1900-konvensjonen, må et bibliotek som mapper Excel-serier til TDateTime forskyve med 1462 i begge retninger hver gang arbeidsboken er flagget med 1904. Ved lesing av en 1904-serie, trekk fra 1462 før du behandler den som en TDateTime; ved skriving av en TDateTime til en 1904-arbeidsbok, trekk fra 1462 fra serien slik at Excel viser dagen du mente. HotXLS bruker denne forskyvningen internt når den serialiserer datoverdier for en arbeidsbok der Date1904 er satt, slik at verdien du tildeler som en TDateTime round-tripper til samme kalenderdag på skjermen.
Den tilsiktede skuddårsfeilen i 1900
Det finnes en kjent særegenhet i 1900-systemet. Excel behandler 1900 som et skuddår og godtar 29. februar 1900 som en ekte dato, serieverdi 60. Året 1900 var ikke et skuddår, fordi århundreår bare er skuddår når de er delelige med 400, og 1900 er ikke det. Denne fantomdagen er en bevisst oppførsel for kompatibilitet, arvet fra et tidlig regneark som ble levert med feilen, beholdt siden den gang slik at seriell aritmetikk forblir identisk på tvers av tiår med filer.
Den praktiske konsekvensen er liten, men reell: for enhver dato på eller etter 1. mars 1900, er serieverdien én høyere enn hva en strengt korrekt dagtelling ville gitt, fordi den ikke-eksisterende 29. februar forbrukte et tall. Et regnearkbibliotek gjenskaper denne særegenheten i stedet for å rette den, fordi det å matche Excels aritmetikk nøyaktig er hele jobben. Å korrigere den ville plassert enhver moderne dato én dag feil fra hva Excel viser, noe som er et dårligere resultat enn å bære med seg en førti tusen dager gammel avvik-med-én-feil som ingen ekte dato i forretningsbruk berører. 1904-systemet har ingen tilsvarende fantomdag, noe som er en grunn til at noen virksomheter historisk sett foretrakk det.
Detektere en dato fra numFmt
ECMA-376 tildeler en blokk med innebygde format-ID-er hvis betydning er fastsatt i spesifikasjonen, og dato- og klokkeslettformatene opptar kjente områder. ID-ene 14 til 22 er de generelle dato- og klokkeslettformatene, de velkjente m/d/yyyy, h:mm og lignende. ID-ene 45 til 47 er formatene for forløpt tid. To ytterligere bånd, 27 til 36 og 50 til 58, er de lokasjonsspesifikke dato- og klokkeslettformatene som brukes for CJK-kalendere, definert i ECMA-376 18.8.30. En celle hvis tallformat-ID faller innenfor noen av disse områdene, er en dato- eller klokkeslettcelle.
Innebygde ID-er dekker de vanlige tilfellene, men ikke egendefinerte. Når en arbeidsbok definerer sin egen formatkode, for eksempel en ikke-standard rekkefølge eller et lokalisert månedsnavn, ligger ID-en over det innebygde området og peker inn i arbeidsbokens tallformattabell. For disse betyr det å gjenkjenne en dato å lese formatkodestrengen og se etter datotegn. HotXLS slår sammen begge sjekkene til ett internt predikat, XlsxNumFmtIsDate, som returnerer sann umiddelbart for de innebygde datoområdene, og ellers tolker den egendefinerte formatkoden via XlsxFormatCodeIsDate. Den offentlige siden av dette er cellens NumberFormat-streng og dens NumberFormatIndex, som gir deg både den løste formatkoden og ID-en som skal testes.
Hvorfor formattolkeren ikke bare kan skanne etter d og m
Å tolke en formatkode etter datotegn ser enkelt ut helt til du husker hva annet som finnes i et tallformat. Et naivt søk etter bokstavene som staver datoer (d, m, y, h og s for dag, måned, år, time og sekund), vil slå feil på to strukturer som ikke er datotegn i det hele tatt.
Det første er den siterte strengen. Et tallformat kan bygge inn bokstavelig tekst i doble anførselstegn, så et økonomisk format som #,##0 "MM" legger til bokstavene M og M til et tall uten noen tidsmessig betydning i det hele tatt. En skanner som teller bokstavene inne i anførselstegnene som månedstegn, ville feilaktig merke dette valutaformatet som en dato. Det andre er brakettseksjonen. Tallformater bærer direktiver i hakebraketter, fargenavn som [Red], sammenligningsbetingelser som [>1000], lokasjonstagger, og markørene for forløpt tid [h] og [mm]. Noe brakettinnhold inneholder datobokstaver og noe gjør det ikke, og å behandle braketteksten på samme måte som formatets kropp fører til både falske positiver og tapte tilfeller.
Den administrator riktige tolkeren går gjennom formatkoden tegn for tegn, sporer om den er inne i en sitert tekst og hvor dypt den er i hakebraketter, og den respekterer også backslash-escapen som siterer et enkelt påfølgende tegn. Bare en u-escapet datobokstav funnet utenfor enhver sitert tekst og utenfor enhver brakettseksjon teller som et ekte datotegn. Det er akkurat slik XlsxFormatCodeIsDate skanner: Et anførselstegn snur en tilstand for sitert tekst som undertrykker tegndeteksjon frem til det avsluttende anførselstegnet, en backslash hopper over neste tegn, og en teller for brakettdybde undertrykker deteksjon inne i [...]-kjøringer. Gevinsten er at #,##0 "MM" leses riktig som et tallformat, mens en kort egendefinert kode som ikke inneholder annet enn en enkelt m eller d utenfor anførselstegn, fortsatt gjenkjennes riktig som en dato.
Lese datoer ut av tredjepartsfiler
Alt over samler seg i én arbeidsflyt: Å gjøre et tall som et annet program skrev om til en dato du kan stole på. Serien gir deg dagtellingen, arbeidsbokens Date1904-flagg forteller deg hvilken epoke tellingen måles fra, og cellens tallformat-ID eller egendefinerte kode er det eneste beviset på at tallet var ment som en dato i utgangspunktet. Utelat én av de tre, og du får et plausibelt feil svar i stedet for en synlig feil.
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');Det eldre BIFF-sporet har en ekstra felle verdt å nevne. I en eldre .xls-strøm kan en serie med tilstøtende numeriske celler pakkes inn i en enkelt multi-celle-post, MULRK, som lagrer flere verdier med deres formatreferanser i én struktur. Datoceller lagret på den måten er ikke mindre datoer selv om de er pakket, så den samme tallformatsjekken må gjøres per celle, og 1904-forskyvningen gjelder fortsatt for hver serie den gir. En leser som bare inspiserer frittstående tallposter, og hopper over de pakkede, vil i det stille gjøre en kolonne med datoer om til en kolonne med heltall.
Mapping av datoeserier til TDateTime i praksis
Når formatsjekken bekrefter en dato og Date1904-flagget er kjent, er konverteringen mekanisk. En verdi som HotXLS allerede gir tilbake som en varDate er en TDateTime du kan bruke direkte. En verdi som ankommer som en rå Double, noe som skjer når kilden skrev en datoeserie uten et gjenkjent datoformat, konverteres ved å lese den som en dagtelling på 1900-aksen og, for en 1904-arbeidsbok, trekke fra 1462-dagers forskyvning først slik at epokene samsvarer. Går man den andre veien, lagrer tildeling av en TDateTime til en celle den 1900-baserte serien, og HotXLS bruker den samme 1462-dagers forskyvningen ved lagring når arbeidsboken er flagget med 1904, slik at den lagrede filen viser datoen du mente i stedet for en dato som er fire år feil.
Sett flagget bevisst når du genererer en arbeidsbok. Standardverdien lar Date1904 forbli usann, noe som samsvarer med Excel for Windows og nesten alltid er det du ønsker; sett den til sann bare når du gjenskaper en Mac-arbeidsbok eller et nedstrøms system spesifikt forventer 1904-aksen. Den eneste reglen som forhindrer hele klassen med fireårsfeil er konsistens: Velg epoke én gang per arbeidsbok, skriv hver dato under den, og les hver datoeserie tilbake under flagget filen faktisk har.
Datoer er én kolonne i en bredere historie om hva en celle faktisk inneholder. Det tilstøtende metadatalaget, tittelen, forfatteren og tidsstemplene som følger med rutenettet, er dekket i vår artikkel om metadata for arbeidsbøker og dokumentegenskaper, der de samme Created- og Modified-verdiene lagres som TDateTime med den samme regelen om at u-satt er lik null. Når en dato er resultatet av en beregning i stedet for en lagret verdi, bestemmer evalueringsreglene i vår artikkel om formelmotoren og egendefinerte funksjoner datoeserien som formatet deretter viser. Begge arbeider over den samme datomodellen som leveres i HotXLS-komponenten for Delphi og C++Builder, som leser og skriver XLS- og XLSX-datoer uten Excel-automatisering.