Ανοίξτε ένα υπολογιστικό φύλλο, κάντε κλικ σε ένα κελί που δείχνει 2026-06-19, και η γραμμή τύπων εξακολουθεί να διαβάζει μια ημερομηνία. Διαβάστε το ίδιο κελί από το Delphi και λαμβάνετε τον αριθμό 46192. Και οι δύο προβολές είναι σωστές, επειδή το Excel δεν αποθήκευσε ποτέ μια ημερομηνία σε αυτό το κελί. Αποθήκευσε έναν σειριακό αριθμό (serial number), μια μέτρηση ημερών, και επισύναψε μια μορφή αριθμού (number format) που λέει στην οθόνη να αποδώσει τη μέτρηση ως ημερομηνία ημερολογίου. Δεν υπάρχει τύπος ημερομηνίας στην τιμή του κελιού. Υπάρχει ένας αριθμός και ένας κανόνας εμφάνισης, και ο κανόνας εμφάνισης είναι το μόνο πράγμα που διακρίνει μια ημερομηνία από μια απλή ποσότητα.
Αυτός ο διαχωρισμός είναι η ρίζα κάθε σφάλματος ημερομηνίας που πρέπει να αποφύγει μια βιβλιοθήκη υπολογιστικών φύλλων. Ένας σειριακός αριθμός από μόνος του δεν λέει ποια ημέρα είναι, επειδή δεν λέει ποια ήταν η ημέρα μηδέν. Ο ίδιος αριθμός σημαίνει δύο ημερομηνίες με διαφορά τεσσάρων ετών, ανάλογα με μια μεμονωμένη σημαία του βιβλίου εργασίας. Και ένας αριθμός που θα έπρεπε να διαβαστεί ως ημερομηνία θα διαβαστεί ως απλή ποσότητα, εκτός εάν κάτι επιθεωρήσει τη μορφή του και αναγνωρίσει ένα μοτίβο ημερομηνίας. Αυτός είναι ο τρόπος με τον οποίο έχει κατασκευαστεί το μοντέλο ημερομηνιών στο HotXLS, και ο λόγος για τον οποίο έπρεπε να γίνει έτσι.
Ένα κελί ημερομηνίας είναι ένας αριθμός συν μια μορφή
Το Excel αποθηκεύει μια ημερομηνία ως τον αριθμό των ημερών από μια εποχή (epoch), με την ώρα της ημέρας στο δεκαδικό μέρος. Το μεσημέρι σε έναν σειριακό αριθμό μεταφέρει το .5. Το ακέραιο μέρος είναι η μέτρηση των ημερών. Τίποτα στην αποθηκευμένη τιμή δεν την επισημαίνει ως χρονική. Αυτό που την επισημαίνει είναι η μορφή αριθμού του κελιού: το ECMA-376 το ονομάζει αυτό numFmt, και ένα κελί του οποίου ο κώδικας μορφής περιγράφει ένα μοτίβο ημερομηνίας ή ώρας εμφανίζεται ως ημερομηνία. Αφαιρέστε τη μορφή και το ίδιο κελί δείχνει έναν αριθμό. Η υποκείμενη τιμή δεν άλλαξε ποτέ.
Αυτός είναι ο λόγος για τον οποίο η ανάγνωση μιας τιμής κελιού σάς δίνει μια Variant που μπορεί να είναι varDate ή Plain Double, και γιατί η μορφή αριθμού στο ίδιο κελί είναι το σήμα που αποφασίζει τι εννοούσε ένας τρίτος. Όταν το HotXLS ανοίγει ένα αρχείο XLSX, ένα κελί μεταφέρει τόσο την Value του όσο και το NumberFormatIndex του στο TXLSXCell, και ο δείκτης μορφής είναι αυτό που συμβουλεύεστε για να μάθετε αν ο αριθμός είναι ημερομηνία.
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;Δύο εποχές, 1462 ημέρες διαφορά
Το προεπιλεγμένο σύστημα ημερομηνιών, αυτό που χρησιμοποιεί κάθε βιβλίο εργασίας των Windows, μετράει από το τέλος του 1899, έτσι ώστε ο σειριακός αριθμός 1 να πέφτει στην πρώτη ημέρα του 1900. Το άλλο σύστημα ανιχνεύεται στο πρώιμο Macintosh και μετράει από την αρχή του 1904, οπότε ο σειριακός αριθμός 1 είναι τέσσερα χρόνια και μία ημέρα αργότερα. Ένα βιβλίο εργασίας καταγράφει ποιο σύστημα χρησιμοποιεί σε μία σημαία. Σε ένα πακέτο OOXML αυτή η σημαία είναι η date1904 στο workbook part. Το HotXLS την εμφανίζει ως την ιδιότητα Date1904 του βιβλίου εργασίας.
Το χάσμα μεταξύ των δύο εποχών είναι ακριβώς 1462 ημέρες. Πρόκειται για τέσσερα ημερολογιακά έτη, τρία των 365 ημερών και ένα των 366 ημερών, συνολικά 1461 ημέρες, συν μία ακόμη για τη διαφορά της μίας ημέρας και κάτι μεταξύ των δύο συμβάσεων της ημέρας μηδέν. Ο αριθμός είναι σταθερός και μπορείτε να τον έχετε στο μυαλό σας. Η σημασία του είναι ότι δεν είναι μηδέν. Ένας σειριακός αριθμός που αντιγράφεται από ένα βιβλίο εργασίας του 1904 και ερμηνεύεται υπό τους κανόνες του 1900, ή το αντίστροφο, τοποθετεί κάθε ημερομηνία 1462 ημέρες μακριά, γεγονός που παρουσιάζει ημερομηνίες λάθος κατά λίγο περισσότερο από τέσσερα χρόνια και είναι εύκολο να εκληφθεί ως κατεστραμμένα δεδομένα.
Επειδή το TDateTime του ίδιου του Delphi είναι αγκυρωμένο στη σύμβαση του 1900, μια βιβλιοθήκη που χαρτογραφεί σειριακούς αριθμούς Excel σε TDateTime πρέπει να εφαρμόζει μετατόπιση κατά 1462 ημέρες και προς τις δύο κατευθύνσεις όποτε το βιβλίο εργασίας φέρει τη σημαία 1904. Διαβάζοντας έναν σειριακό αριθμό του 1904, αφαιρέστε 1462 πριν τον χειριστείτε ως TDateTime. Γράφοντας ένα TDateTime σε ένα βιβλίο εργασίας του 1904, αφαιρέστε 1462 από τον σειριακό αριθμό ώστε το Excel να αποδώσει την ημέρα που εννοούσατε. Το HotXLS εφαρμόζει αυτήν τη μετατόπιση εσωτερικά όταν σειριοποιεί τιμές ημερομηνίας για ένα βιβλίο εργασίας του οποίου η ιδιότητα Date1904 είναι ενεργοποιημένη, ώστε η τιμή που εκχωρείτε ως TDateTime να επιστρέφει στην ίδια ημερολογιακή ημέρα στην οθόνη.
Η σκόπιμη ιδιοτροπία του δίσεκτου έτους 1900
Υπάρχει μια διάσημη παραξενιά στο σύστημα του 1900. Το Excel αντιμετωπίζει το 1900 ως δίσεκτο έτος και δέχεται την 29η Φεβρουαρίου 1900 ως πραγματική ημερομηνία, σειριακό αριθμό 60. Το έτος 1900 δεν ήταν δίσεκτο έτος, επειδή τα έτη των αιώνων είναι δίσεκτα μόνο όταν διαιρούνται με το 400, και το 1900 δεν διαιρείται. Αυτή η ανύπαρκτη ημέρα είναι μια σκόπιμη συμπεριφορά συμβατότητας που κληρονομήθηκε από ένα πρώιμο υπολογιστικό φύλλο που κυκλοφόρησε με το συγκεκριμένο σφάλμα, η οποία διατηρήθηκε έκτοτε ώστε η σειριακή αριθμητική να παραμένει πανομοιότυπη σε βάθος δεκαετιών αρχείων.
Η πρακτική συνέπεια είναι μικρή αλλά υπαρκτή: για οποιαδήποτε ημερομηνία κατά ή μετά την 1η Μαρτίου 1900, ο σειριακός αριθμός είναι κατά ένα μεγαλύτερος από αυτόν που θα έδινε μια αυστηρά σωστή μέτρηση ημερών, επειδή η μη υπαρκτή 29η Φεβρουαρίου κατανάλωσε έναν αριθμό. Μια βιβλιοθήκη υπολογιστικών φύλλων αναπαράγει την παραξενιά αντί να τη διορθώνει, επειδή το να ταιριάζει ακριβώς η αριθμητική του Excel είναι ολόκληρη η δουλειά της. Η διόρθωσή της θα έθετε κάθε σύγχρονη ημερομηνία μία ημέρα εκτός από αυτήν που δείχνει το Excel, το οποίο είναι χειρότερο αποτέλεσμα από το να μεταφέρεται ένα σφάλμα off-by-one ηλικίας σαράντα χιλιάδων ημερών που καμία πραγματική ημερομηνία σε επιχειρηματική χρήση δεν αγγίζει ποτέ. Το σύστημα του 1904 δεν έχει αντίστοιχη ανύπαρκτη ημέρα, που είναι ένας λόγος για τον οποίο μερικές επιχειρήσεις ιστορικά το προτιμούσαν.
Ανίχνευση ημερομηνίας από το numFmt
Όταν ένας αριθμός φτάνει από ένα αρχείο που έγραψε κάποια άλλη εφαρμογή, η μορφή του είναι η μόνη απόδειξη ότι πρόκειται για ημερομηνία. Το ECMA-376 εκχωρεί ένα μπλοκ ενσωματωμένων αναγνωριστικών μορφής (format ids) των οποίων η σημασία είναι σταθερή από την προδιαγραφή, και οι μορφές ημερομηνίας και ώρας καταλαμβάνουν γνωστά εύρη. Τα αναγνωριστικά 14 έως 22 είναι οι μορφές ημερομηνίας και ώρας γενικής τοποθεσίας (general-locale), οι γνωστές m/d/yyyy, h:mm και οι συγγενείς τους. Τα αναγνωριστικά 45 έως 47 είναι οι μορφές παρόδου χρόνου (elapsed-time). Δύο επιπλέον ζώνες, 27 έως 36 και 50 έως 58, είναι οι ειδικές για την τοποθεσία μορφές ημερομηνίας και ώρας που χρησιμοποιούνται για τα ημερολόγια CJK, όπως ορίζονται στο ECMA-376 18.8.30. Ένα κελί του οποίου το αναγνωριστικό μορφής αριθμού εμπίπτει σε οποιοδήποτε από αυτά τα εύρη είναι κελί ημερομηνίας ή ώρας.
Τα ενσωματωμένα αναγνωριστικά καλύπτουν τις συνήθεις περιπτώσεις αλλά όχι τις προσαρμοσμένες. Όταν ένα βιβλίο εργασίας ορίζει τον δικό του κώδικα μορφής, για παράδειγμα μια μη τυπική σειρά ή ένα εντοπισμένο όνομα μήνα, το αναγνωριστικό είναι πάνω από το ενσωματωμένο εύρος και δείχνει στον πίνακα μορφών αριθμών του βιβλίου εργασίας. Για αυτά, η αναγνώριση μιας ημερομηνίας σημαίνει την ανάγνωση της συμβολοσειράς κώδικα μορφής και την αναζήτηση διακριτικών ημερομηνίας (date tokens). Το HotXLS συνδυάζει και τους δύο ελέγχους σε ένα εσωτερικό κατηγόρημα, το XlsxNumFmtIsDate, το οποίο επιστρέφει αληθές αμέσως για τα ενσωματωμένα εύρη ημερομηνιών και διαφορετικά αναλύει τον κώδικα προσαρμοσμένης μορφής μέσω του XlsxFormatCodeIsDate. Η δημόσια πλευρά αυτού του ελέγχου είναι η συμβολοσειρά NumberFormat του κελιού και το NumberFormatIndex του, που σας δίνουν τόσο τον επιλυμένο κώδικα μορφής όσο και το αναγνωριστικό για δοκιμή.
Γιατί ο αναλυτής μορφής δεν μπορεί απλώς να σαρώνει για d και m
Η ανάλυση ενός κώδικα μορφής για διακριτικά ημερομηνίας φαίνεται απλή μέχρι να θυμηθείτε τι άλλο μπορεί να υπάρχει σε μια μορφή αριθμού. Μια απλή αναζήτηση για τα γράμματα που σχηματίζουν ημερομηνίες, τα d, m, y, h, και s της ημέρας, του μήνα, του έτους, της ώρας και του δευτερολέπτου, θα αποτύχει σε δύο δομές που δεν είναι καθόλου διακριτικά ημερομηνίας.
Η πρώτη είναι η κυριολεκτική τιμή συμβολοσειράς σε εισαγωγικά. Μια μορφή αριθμού μπορεί να ενσωματώσει κυριολεκτικό κείμενο σε διπλά εισαγωγικά, έτσι ώστε μια χρηματοοικονομική μορφή όπως η #,##0 "MM" να προσαρτά τους χαρακτήρες M και M σε έναν αριθμό χωρίς καμία απολύτως χρονική σημασία. Ένας σαρωτής που μετράει τα γράμματα μέσα στα εισαγωγικά ως διακριτικά μήνα θα επισήμανε εσφαλμένα αυτή τη μορφή νομίσματος ως ημερομηνία. Η δεύτερη είναι η ενότητα αγκυλών. Οι μορφές αριθμών φέρουν οδηγίες σε αγκύλες, ονόματα χρωμάτων όπως [Red], συνθήκες σύγκρισης όπως [>1000], ετικέτες τοποθεσίας και τους δείκτες παρόδου χρόνου [h] και [mm]. Κάποιο περιεχόμενο αγκυλών κρατά γράμματα ημερομηνίας και κάποιο όχι, και η αντιμετώπιση του κειμένου σε αγκύλες με τον ίδιο τρόπο όπως το σώμα της μορφής οδηγεί τόσο σε ψευδώς θετικά όσο και σε χαμένα αποτελέσματα.
Ο σωστός αναλυτής διασχίζει τον κώδικα μορφής χαρακτήρα προς χαρακτήρα, παρακολουθώντας αν βρίσκεται μέσα σε κυριολεκτικό κείμενο εισαγωγικών και πόσο βαθιά βρίσκεται μέσα σε φωλιασμένες αγκύλες, και επίσης τιμά τη διαφυγή της ανάστροφης κάθετου (backslash escape) που εισάγει έναν μεμονωμένο επόμενο χαρακτήρα. Μόνο ένα γράμμα ημερομηνίας χωρίς χαρακτήρα διαφυγής που βρίσκεται εκτός οποιουδήποτε κυριολεκτικού εισαγωγικών και εκτός οποιασδήποτε ενότητας αγκυλών μετράει ως πραγματικό διακριτικό ημερομηνίας. Αυτός είναι ακριβώς ο τρόπος με τον οποίο σαρώνει η XlsxFormatCodeIsDate: ένα εισαγωγικό αλλάζει μια κατάσταση in-literal που καταστέλλει την ανίχνευση διακριτικών μέχρι το κλείσιμο των εισαγωγικών, μια ανάστροφη κάθετος παρακάμπτει τον επόμενο χαρακτήρα, και ένας μετρητής βάθους αγκυλών καταστέλλει την ανίχνευση μέσα σε τμήματα [...]. Το όφελος είναι ότι η μορφή #,##0 "MM" διαβάζεται σωστά ως μορφή αριθμού, ενώ ένας σύντομος προσαρμοσμένος κώδικας που περιέχει μόνο ένα m ή d έξω από εισαγωγικά εξακολουθεί να αναγνωρίζεται σωστά ως ημερομηνία.
Ανάγνωση ημερομηνιών από αρχεία τρίτων
Όλα τα παραπάνω συγκλίνουν σε μία ροή εργασίας: τη μετατροπή ενός αριθμού που έγραψε κάποια άλλη εφαρμογή πίσω σε μια ημερομηνία που μπορείτε να εμπιστευτείτε. Ο σειριακός αριθμός σάς δίνει τη μέτρηση των ημερών, η σημαία Date1904 του βιβλίου εργασίας σάς λέει από ποια εποχή μετριέται, και το αναγνωριστικό μορφής αριθμού του κελιού ή ο προσαρμοσμένος κώδικας είναι η μοναδική απόδειξη ότι ο αριθμός προοριζόταν εξ αρχής για ημερομηνία. Παραλείψτε οποιοδήποτε από τα τρία και λαμβάνετε μια εύλογη λάθος απάντηση αντί για ένα ορατό σφάλμα.
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;Η παλιά πλευρά BIFF έχει μια επιπλέον παγίδα που αξίζει να αναφερθεί. Σε μια παλαιότερη ροή .xls, μια σειρά γειτονικών αριθμητικών κελιών μπορεί να συσκευαστεί σε μια μεμονωμένη εγγραφή πολλαπλών κελιών, τη MULRK, η οποία αποθηκεύει πολλές τιμές με τις αναφορές μορφής τους σε μία δομή. Τα κελιά ημερομηνίας που αποθηκεύονται με αυτόν τον τρόπο δεν παύουν να είναι ημερομηνίες επειδή είναι συσκευασμένα, οπότε η ίδια δοκιμή αναγνωριστικού μορφής πρέπει να εφαρμόζεται ανά κελί, και η μετατόπιση του 1904 εξακολουθεί να διέπει κάθε σειριακό αριθμό που παράγει. Ένας αναγνώστης που επιθεωρεί μόνο μεμονωμένες εγγραφές αριθμών, και παρακάμπτει τις συσκευασμένες, θα μετατρέψει σιωπηλά μια στήλη ημερομηνιών σε στήλη ακεραίων.
Χαρτογράφηση σειριακών αριθμών σε TDateTime στην πράξη
Μόλις ο έλεγχος μορφής επιβεβαιώσει μια ημερομηνία και η σημαία Date1904 είναι γνωστή, η μετατροπή είναι μηχανική. Μια τιμή που το HotXLS επιστρέφει ήδη ως varDate είναι ένα TDateTime που μπορείτε να χρησιμοποιήσετε απευθείας. Μια τιμή που φτάνει ως απλό Double, κάτι που συμβαίνει όταν η πηγή έγραψε έναν σειριακό αριθμό χωρίς αναγνωρισμένη μορφή ημερομηνίας, μετατρέπεται διαβάζοντάς την ως μέτρηση ημερών στον άξονα του 1900 και, για ένα βιβλίο εργασίας 1904, αφαιρώντας πρώτα τη μετατόπιση των 1462 ημερών ώστε οι εποχές να ευθυγραμμιστούν. Αντίστροφα, η εκχώρηση ενός TDateTime σε ένα κελί αποθηκεύει τον σειριακό αριθμό με βάση το 1900, και το HotXLS εφαρμόζει την ίδια μετατόπιση των 1462 ημερών κατά την αποθήκευση όταν η σημαία Date1904 του βιβλίου εργασίας είναι ενεργοποιημένη, ώστε το αποθηκευμένο αρχείο να δείχνει την ημερομηνία που θέλατε αντί για μία ημερομηνία τέσσερα χρόνια μακριά.
Ορίστε τη σημαία σκόπιμα όταν δημιουργείτε ένα βιβλίο εργασίας. Η προεπιλογή αφήνει την ιδιότητα Date1904 ψευδή, που ταιριάζει με το Excel για Windows και είναι σχεδόν πάντα αυτό που θέλετε. Ορίστε την ως αληθή μόνο όταν αναπαράγετε ένα βιβλίο εργασίας προέλευσης Mac ή ένα κατάντη σύστημα αναμένει συγκεκριμένα τον άξονα του 1904. Ο μόνος κανόνας που αποτρέπει ολόκληρη την κατηγορία των σφαλμάτων τεσσάρων ετών είναι η συνέπεια: επιλέξτε την εποχή μία φορά ανά βιβλίο εργασίας, γράψτε κάθε ημερομηνία υπό αυτήν, και διαβάστε κάθε σειριακό αριθμό πίσω υπό τη σημαία που πραγματικά φέρει το αρχείο.
Οι ημερομηνίες είναι μία στήλη σε μια ευρύτερη ιστορία σχετικά με το τι πραγματικά κρατά ένα κελί. Το γειτονικό επίπεδο μεταδεδομένων, ο τίτλος, ο συγγραφέας και οι χρονικές σημάνσεις που συνοδεύουν το πλέγμα, καλύπτεται στο άρθρο μας σχετικά με τα μεταδεδομένα βιβλίου εργασίας και τις ιδιότητες εγγράφου, όπου οι ίδιες τιμές Created and Modified αποθηκεύονται ως TDateTime με την ίδια σύμβαση unset-equals-zero. Όταν μια ημερομηνία είναι το αποτέλεσμα ενός υπολογισμού και όχι μια αποθηκευμένη τιμή, οι κανόνες αξιολόγησης στο άρθρο μας σχετικά με τη μηχανή τύπων και τις προσαρμοσμένες συναρτήσεις καθορίζουν τον σειριακό αριθμό που στη συνέχεια αποδίδει η μορφή. Και τα δύο λειτουργούν πάνω στο ίδιο μοντέλο ημερομηνιών που παρέχεται στο HotXLS Component για Delphi και C++Builder, το οποίο διαβάζει και γράφει ημερομηνίες XLS και XLSX χωρίς αυτοματοποίηση του Excel.