Buka spreadsheet, klik sel yang menampilkan 2026-06-19, dan bilah rumus (formula bar) tetap membaca tanggal. Baca sel yang sama dari Delphi dan Anda mendapatkan angka 46192. Kedua tampilan tersebut benar, karena Excel tidak pernah menyimpan tanggal di sel tersebut. Excel menyimpan nomor serial, hitungan hari, dan melampirkan format angka yang memberi tahu layar untuk merender hitungan tersebut sebagai tanggal kalender. Tidak ada tipe tanggal dalam nilai sel. Yang ada adalah angka dan aturan tampilan, dan aturan tampilan adalah satu-satunya hal yang membedakan tanggal dari kuantitas biasa.
Pemisahan tersebut adalah akar dari setiap bug tanggal yang harus dihindari oleh pustaka spreadsheet. Nomor serial saja tidak menyatakan hari apa itu, karena ia tidak menyatakan kapan hari ke-nol berada. Angka yang sama berarti dua tanggal terpaut empat tahun tergantung pada satu flag workbook tunggal. Dan angka yang seharusnya dibaca kembali sebagai tanggal akan dibaca kembali sebagai kuantitas biasa kecuali ada sesuatu yang memeriksa formatnya dan mengenali pola tanggal. Beginilah cara model tanggal di HotXLS dibangun, dan mengapa harus demikian.
Sel tanggal adalah angka ditambah format
Excel menyimpan tanggal sebagai jumlah hari sejak suatu epoch, dengan waktu hari di bagian pecahan. Tengah hari pada nomor serial membawa nilai .5. Bagian integer adalah hitungan hari. Tidak ada apa pun dalam nilai tersimpan yang menandainya sebagai temporal. Yang menandainya adalah format angka sel: ECMA-376 menyebutnya sebagai numFmt, dan sel yang kode formatnya mengeja pola tanggal atau waktu ditampilkan sebagai tanggal. Hapus format tersebut maka sel yang sama akan menunjukkan angka; nilai dasar di bawahnya tidak pernah berubah.
Inilah sebabnya mengapa membaca nilai sel memberi Anda Variant yang mungkin berupa varDate or mungkin berupa Double biasa, dan mengapa format angka pada sel yang sama adalah sinyal yang menentukan apa yang dimaksud oleh pihak ketiga. Ketika HotXLS membuka file XLSX, sel membawa Value dan NumberFormatIndex-nya ke dalam TXLSXCell, dan indeks format adalah apa yang Anda rujuk untuk mempelajari apakah angka tersebut adalah tanggal.
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;Dua epoch, terpaut 1462 hari
Sistem tanggal default, yang digunakan oleh setiap workbook Windows, menghitung dari akhir tahun 1899, sehingga nomor serial 1 jatuh pada hari pertama tahun 1900. Sistem lainnya merujuk ke Macintosh awal dan menghitung dari awal tahun 1904, sehingga nomor serial 1-nya adalah empat tahun dan satu hari kemudian. Workbook mencatat sistem mana yang digunakannya dalam satu flag. Dalam paket OOXML flag tersebut adalah date1904 pada bagian workbook; HotXLS memunculkannya sebagai properti Date1904 dari workbook.
Kesenjangan antara kedua epoch adalah tepat 1462 hari. Itu adalah empat tahun kalender, tiga tahun berumur 365 hari dan satu tahun berumur 366 hari, dengan total 1461 hari, ditambah satu hari lagi untuk selisih hari-dan-sedikit di antara dua konvensi hari-nol. Angka tersebut tetap dan Anda dapat mengingatnya di luar kepala. Pentingnya adalah angka tersebut bukan nol. Nomor serial yang disalin dari workbook 1904 dan ditafsirkan di bawah aturan 1900, atau sebaliknya, menempatkan setiap tanggal meleset 1462 hari, yang muncul sebagai tanggal yang salah lebih dari empat tahun dan mudah disalahartikan sebagai data yang rusak.
Karena TDateTime milik Delphi sendiri ditambatkan ke konvensi 1900, pustaka yang memetakan nomor serial Excel ke TDateTime harus mengimbangi (offset) sebesar 1462 di kedua arah setiap kali workbook ditandai dengan flag 1904. Membaca nomor serial 1904, kurangi 1462 sebelum memperlakukannya sebagai TDateTime; menulis TDateTime ke dalam workbook 1904, kurangi 1462 dari nomor serial sehingga Excel merender hari yang Anda maksud. HotXLS menerapkan pergeseran ini secara internal saat menserialisasikan nilai tanggal untuk workbook yang Date1904-nya disetel, sehingga nilai yang Anda tetapkan sebagai TDateTime kembali ke hari kalender yang sama di layar.
Keunikan tahun kabisat 1900 yang disengaja
Ada keunikan terkenal dalam sistem 1900. Excel memperlakukan 1900 sebagai tahun kabisat dan menerima 29 Februari 1900 sebagai tanggal nyata, nomor serial 60. Tahun 1900 bukan merupakan tahun kabisat, karena tahun abad hanya merupakan tahun kabisat jika habis dibagi 400, sedangkan 1900 tidak. Hari fiktif tersebut sengaja dipertahankan sebagai perilaku kompatibilitas yang diwarisi dari program spreadsheet awal yang dikirimkan dengan bug tersebut, tetap dipertahankan sejak saat itu agar aritmatika serial tetap identik di seluruh dekade file.
Konsekuensi praktisnya kecil tetapi nyata: untuk tanggal apa pun pada atau setelah 1 Maret 1900, nomor serialnya satu tingkat lebih tinggi daripada yang akan diberikan oleh hitungan hari yang benar secara ketat, karena 29 Februari yang sebenarnya tidak ada telah menghabiskan satu nomor. Pustaka spreadsheet mereproduksi keunikan tersebut daripada memperbaikinya, karena mencocokkan aritmatika Excel secara tepat adalah tugas utamanya. Memperbaikinya akan menempatkan setiap tanggal modern meleset satu hari dari apa yang ditampilkan Excel, yang merupakan hasil yang lebih buruk daripada membawa kesalahan meleset-satu (off-by-one) berusia empat puluh ribu hari yang tidak pernah disentuh oleh tanggal nyata dalam penggunaan bisnis. Sistem 1904 tidak memiliki hari fiktif serupa, yang merupakan salah satu alasan beberapa pihak secara historis lebih menyukainya.
Mendeteksi tanggal dari numFmt
Ketika angka tiba dari file yang ditulis orang lain, formatnya adalah satu-satunya bukti bahwa itu adalah tanggal. ECMA-376 menetapkan blok ID format bawaan yang artinya ditentukan oleh spesifikasi, dan format tanggal serta waktu menempati rentang yang diketahui. ID 14 hingga 22 adalah format tanggal dan waktu lokal umum, m/d/yyyy, h:mm yang sudah dikenal, dan kerabatnya. ID 45 hingga 47 adalah format waktu yang telah berlalu (elapsed-time). Dua pita lebih lanjut, 27 hingga 36 dan 50 hingga 58, adalah format tanggal dan waktu khusus lokal yang digunakan untuk kalender CJK, yang didefinisikan dalam ECMA-376 18.8.30. Sel yang ID format angkanya masuk dalam rentang ini adalah sel tanggal atau waktu.
ID bawaan mencakup kasus umum tetapi tidak untuk kasus kustom. Ketika workbook menentukan kode formatnya sendiri, katakanlah pengurutan non-standar atau nama bulan yang dilokalkan, ID berada di atas rentang bawaan dan mengarah ke tabel format angka workbook. Untuk hal tersebut, mengenali tanggal berarti membaca string kode format dan mencari token tanggal. HotXLS melipat kedua pemeriksaan ini menjadi satu predikat internal, XlsxNumFmtIsDate, yang segera mengembalikan nilai true untuk rentang tanggal bawaan dan sebaliknya mengurai kode format kustom melalui XlsxFormatCodeIsDate. Sisi publik dari hal tersebut adalah string NumberFormat sel dan NumberFormatIndex-nya, yang memberi Anda kode format yang diselesaikan dan ID untuk diuji.
Mengapa parser format tidak dapat sekadar memindai huruf d dan m
Mengurai kode format untuk token tanggal terlihat sepele sampai Anda mengingat apa lagi yang ada di dalam format angka. Pencarian naif untuk huruf-huruf yang mengeja tanggal, yaitu d, m, y, h, dan s untuk hari, bulan, tahun, jam, dan detik, akan salah sasaran pada dua struktur yang sama sekali bukan token tanggal.
Pertama adalah string literal yang dikutip. Format angka dapat menyematkan teks literal dalam tanda kutip ganda, sehingga format keuangan seperti #,##0 "MM" menambahkan karakter M dan M ke angka tanpa arti temporal sama sekali. Pemindai yang menghitung huruf di dalam tanda kutip sebagai token bulan akan salah menandai format mata uang tersebut sebagai tanggal. Kedua adalah bagian tanda kurung siku. Format angka membawa direktif dalam tanda kurung siku, nama warna seperti [Red], kondisi perbandingan seperti [>1000], tag lokal, dan penanda waktu berlalu [h] serta [mm]. Beberapa konten tanda kurung menampung huruf tanggal dan beberapa tidak, dan memperlakukan teks dalam tanda kurung siku sama dengan badan format menyebabkan hasil positif palsu (false positive) dan kasus yang terlewat.
Parser yang benar berjalan menelusuri kode format karakter demi karakter, melacak apakah ia berada di dalam string literal yang dikutip dan seberapa dalam ia berada di dalam kurung siku bersarang, dan ia juga menghormati escape backslash yang mengutip satu karakter berikutnya. Hanya huruf tanggal tanpa escape yang ditemukan di luar string literal apa pun dan di luar bagian kurung siku apa pun yang dihitung sebagai token tanggal nyata. Persis seperti itulah cara XlsxFormatCodeIsDate memindai: tanda kutip membalikkan status dalam-literal yang menekan deteksi token hingga tanda kutip penutup, backslash melewati karakter berikutnya, dan penghitung kedalaman kurung siku menekan deteksi di dalam rentang [...]. Hasilnya adalah #,##0 "MM" dibaca dengan benar sebagai format angka, sementara kode kustom singkat yang tidak berisi apa pun selain m atau d tunggal di luar tanda kutip tetap dikenali dengan benar sebagai tanggal.
Membaca tanggal dari file pihak ketiga
Semua hal di atas menyatu pada satu alur kerja: mengubah angka yang ditulis oleh aplikasi lain kembali menjadi tanggal yang dapat Anda percayai. Serial memberi Anda hitungan hari, flag Date1904 workbook memberi tahu Anda dari epoch mana hitungan tersebut diukur, dan format angka atau kode kustom sel adalah satu-satunya bukti bahwa angka tersebut dimaksudkan sebagai tanggal sejak awal. Lewatkan salah satu dari ketiganya dan Anda mendapatkan jawaban salah yang masuk akal, bukan kesalahan yang terlihat.
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;Sisi warisan BIFF memiliki satu jebakan ekstra yang layak disebut. Dalam stream .xls yang lebih lama, rangkaian sel numerik yang berdekatan dapat dikemas ke dalam satu record multi-sel, MULRK, yang menyimpan beberapa nilai dengan referensi formatnya dalam satu struktur. Sel tanggal yang disimpan dengan cara itu tetap merupakan tanggal meskipun dikemas, sehingga pengujian ID format yang sama harus menjangkau ke dalam record multi-sel dan diterapkan per sel, dan offset 1904 tetap mengatur setiap serial yang dihasilkannya. Pembaca yang hanya memeriksa record angka mandiri, dan melewati record yang dikemas, secara diam-diam akan mengubah kolom tanggal menjadi kolom integer.
Memetakan serial ke TDateTime dalam praktik
Setelah pemeriksaan format mengonfirmasi tanggal dan flag Date1904 diketahui, konversi bersifat mekanis. Nilai yang dikembalikan HotXLS sebagai varDate adalah TDateTime yang dapat Anda gunakan secara langsung. Nilai yang tiba sebagai Double biasa, yang terjadi ketika sumber menulis serial tanpa format tanggal yang dikenali, dikonversi dengan membacanya sebagai hitungan hari pada sumbu 1899 dan, untuk workbook 1904, mengurangi offset 1462 hari terlebih dahulu sehingga epoch sejajar. Sebaliknya, menetapkan TDateTime ke sel menyimpan serial berbasis 1899, dan HotXLS menerapkan pergeseran 1462 hari yang sama pada penyimpanan ketika workbook ditandai dengan flag 1904, sehingga file yang disimpan menunjukkan tanggal yang Anda maksud daripada tanggal yang meleset empat tahun.
Setel flag secara sengaja saat Anda membuat workbook. Defaultnya membiarkan Date1904 bernilai false, yang cocok dengan Excel untuk Windows dan hampir selalu merupakan hal yang Anda inginkan; setel ke true hanya ketika Anda mereproduksi workbook asal Mac atau sistem hilir mengharapkan sumbu 1904 secara spesifik. Satu-satunya aturan yang mencegah seluruh kelas kesalahan empat tahun adalah konsistensi: pilih epoch sekali per workbook, tulis setiap tanggal di bawahnya, dan baca setiap serial kembali di bawah flag yang benar-benar dibawa file.
Tanggal adalah satu kolom dalam cerita yang lebih luas tentang apa yang sebenarnya disimpan oleh sel. Lapisan metadata tetangga, judul dan penulis serta stempel waktu yang berjalan di samping kisi, dibahas dalam artikel kami tentang metadata workbook dan properti dokumen, di mana nilai Created dan Modified yang sama disimpan sebagai TDateTime dengan konvensi unset-sama-dengan-nol yang sama. Ketika tanggal adalah hasil perhitungan dan bukan nilai tersimpan, aturan evaluasi dalam artikel kami tentang mesin formula dan fungsi kustom menentukan serial yang kemudian dirender oleh format. Keduanya bekerja di atas model tanggal yang sama yang dikirimkan dalam HotXLS Component untuk Delphi dan C++Builder, yang membaca dan menulis tanggal XLS dan XLSX dates tanpa otomatisasi Excel.