Mở một bảng tính, nhấp vào một ô hiển thị 2026-06-19, và thanh công thức vẫn đọc được một ngày tháng. Đọc cùng một ô đó từ Delphi và bạn nhận được số 46192. Cả hai góc nhìn đều chính xác, vì Excel không bao giờ lưu trữ một ngày tháng trong ô đó. Nó lưu trữ một số tuần tự (serial number), một số đếm số ngày, và đính kèm một định dạng số để thông báo cho màn hình hiển thị số đếm đó dưới dạng một ngày lịch. Không có kiểu ngày tháng trong giá trị ô. Chỉ có một con số và một quy tắc hiển thị, và quy tắc hiển thị là điều duy nhất phân biệt một ngày tháng với một số lượng đơn thuần.
Sự phân tách đó là gốc rễ của mọi lỗi ngày tháng mà một thư viện bảng tính phải né tránh. Riêng một số tuần tự không thể cho biết đó là ngày nào, vì nó không cho biết ngày số không (day zero) là ngày nào. Cùng một con số biểu diễn hai ngày tháng cách nhau bốn năm tùy thuộc vào một cờ sổ làm việc duy nhất. Và một con số đáng lẽ phải được đọc ra dưới dạng ngày tháng sẽ chỉ được đọc ra dưới dạng số lượng trần trụi trừ khi có thứ gì đó kiểm tra định dạng của nó và nhận diện được một mẫu ngày tháng. Đây là cách mô hình ngày tháng trong HotXLS được xây dựng, và là lý do tại sao nó phải như vậy.
Một ô ngày tháng là một con số cộng với một định dạng
Excel lưu trữ một ngày dưới dạng số lượng ngày kể từ một kỷ nguyên (epoch), với thời gian trong ngày nằm ở phần phân số. Giờ trưa trên một số tuần tự mang giá trị .5. Phần số nguyên là số đếm ngày. Không có gì trong giá trị được lưu trữ đánh dấu nó mang tính thời gian. Điều đánh dấu nó là định dạng số của ô: ECMA-376 gọi đây là numFmt, và một ô có mã định dạng biểu diễn một mẫu ngày hoặc giờ sẽ hiển thị dưới dạng ngày tháng. Gỡ bỏ định dạng đó và chính ô đó hiển thị một con số; giá trị cơ sở bên dưới chưa bao giờ thay đổi.
Đây là lý do tại sao việc đọc một giá trị ô trả về cho bạn một biến kiểu Variant vốn có thể là một varDate hoặc có thể là một kiểu số thực Double thông thường, và tại sao định dạng số trên cùng một ô là tín hiệu quyết định ý định của ứng dụng bên thứ ba. Khi HotXLS mở một tệp XLSX, một ô mang cả giá trị Value và chỉ mục định dạng số NumberFormatIndex của nó vào lớp TXLSXCell, và chỉ mục định dạng là thứ bạn tham khảo để biết liệu con số đó có phải là một ngày tháng.
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;Hai kỷ nguyên, lệch nhau 1462 ngày
Hệ thống ngày tháng mặc định, hệ thống mà mọi sổ làm việc trên Windows sử dụng, bắt đầu tính từ thời điểm cuối cùng của năm 1899, do đó số tuần tự 1 rơi vào ngày đầu tiên của năm 1900. Hệ thống còn lại bắt nguồn từ dòng máy Macintosh thế hệ đầu tiên và tính từ đầu năm 1904, do đó số tuần tự 1 của nó muộn hơn bốn năm và một ngày. Một sổ làm việc ghi nhận hệ thống nào được sử dụng thông qua một cờ duy nhất. Trong một gói OOXML, cờ đó là thuộc tính date1904 trên phần workbook; HotXLS hiển thị nó dưới dạng thuộc tính Date1904 của sổ làm việc.
Khoảng cách giữa hai kỷ nguyên chính xác là 1462 ngày. Đó là bốn năm dương lịch, ba năm có 365 ngày và một năm có 366 ngày, tổng cộng là 1461 ngày, cộng thêm một ngày cho độ lệch của hai quy ước ngày không (day-zero). Con số này là cố định và bạn có thể ghi nhớ nó. Tầm quan trọng của nó là nó khác không. Một số tuần tự được sao chép từ một sổ làm việc phiên bản 1904 và được diễn giải theo quy tắc năm 1900, hoặc ngược lại, sẽ khiến mọi ngày tháng bị lệch đi 1462 ngày, biểu hiện dưới dạng các ngày bị sai lệch hơn bốn năm và rất dễ bị nhầm lẫn với dữ liệu bị hỏng.
Bởi vì kiểu dữ liệu TDateTime của chính Delphi được neo vào quy ước năm 1900, một thư viện ánh xạ các số tuần tự Excel sang TDateTime bắt buộc phải dịch chuyển một lượng 1462 ngày theo cả hai hướng bất cứ khi nào sổ làm việc được gắn cờ 1904. Khi đọc một số tuần tự 1904, hãy trừ đi 1462 trước khi xử lý nó như một TDateTime; khi ghi một giá trị TDateTime vào một sổ làm việc 1904, hãy trừ đi 1462 khỏi số tuần tự để Excel hiển thị đúng ngày bạn muốn. HotXLS áp dụng sự dịch chuyển này nội bộ khi nó tuần tự hóa các giá trị ngày tháng cho một sổ làm việc có thiết lập Date1904, do đó giá trị bạn chỉ định dưới dạng TDateTime sẽ khứ hồi khớp đúng với cùng một ngày lịch hiển thị trên màn hình.
Hành vi kỳ lạ về năm nhuận 1900 có chủ ý
Có một điểm kỳ lạ nổi tiếng trong hệ thống năm 1900. Excel coi năm 1900 là một năm nhuận và chấp nhận ngày 29 tháng 2 năm 1900 là một ngày thực tế, số tuần tự 60. Nhưng thực tế năm 1900 không phải là năm nhuận, vì các năm thế kỷ chỉ là năm nhuận khi chia hết cho 400, và 1900 thì không. Ngày ảo này là một hành vi tương thích có chủ ý được kế thừa từ một bảng tính thời kỳ đầu vốn có lỗi này, được giữ lại kể từ đó để các phép toán số tuần tự nhất quán qua nhiều thập kỷ tệp tin.
Hậu quả thực tế là rất nhỏ nhưng có thật: đối với bất kỳ ngày nào vào hoặc sau ngày 1 tháng 3 năm 1900, số tuần tự sẽ cao hơn một đơn vị so với số đếm ngày chính xác nghiêm ngặt, vì ngày 29 tháng 2 không tồn tại đã tiêu thụ mất một con số. Một thư viện bảng tính sẽ tái tạo lại điểm kỳ lạ này thay vì sửa đổi nó, vì mục tiêu duy nhất là khớp chính xác với các phép toán của Excel. Việc sửa đổi nó sẽ làm cho mọi ngày tháng hiện đại bị lệch một ngày so với những gì Excel hiển thị, đây là một kết quả tồi tệ hơn việc chấp nhận một lỗi lệch một đơn vị đã có từ bốn mươi nghìn ngày trước mà không ngày tháng thực tế nào trong hoạt động kinh doanh chạm tới. Hệ thống năm 1904 không có ngày ảo tương đương, đó là một lý do khiến một số doanh nghiệp trước đây ưa thích nó hơn.
Phát hiện một ngày tháng từ numFmt
Khi một con số đi tới từ một tệp tin do một ứng dụng khác ghi, định dạng của nó là bằng chứng duy nhất cho thấy nó là một ngày tháng. ECMA-376 chỉ định một khối các id định dạng tích hợp sẵn có ý nghĩa được cố định bởi đặc tả, và các định dạng ngày giờ chiếm giữ các phạm vi đã biết. Các id từ 14 đến 22 là các định dạng ngày và giờ theo ngôn ngữ chung, định dạng quen thuộc m/d/yyyy, h:mm và họ hàng của chúng. Các id từ 45 đến 47 là định dạng thời gian trôi qua (elapsed-time). Hai dải định dạng tiếp theo, từ 27 đến 36 và từ 50 đến 58, là các định dạng ngày và giờ cụ thể theo vùng miền được sử dụng cho lịch CJK, được định nghĩa trong ECMA-376 18.8.30. Một ô có id định dạng số rơi vào bất kỳ phạm vi nào trong số này đều là ô ngày hoặc giờ.
Các id tích hợp sẵn bao quát các trường hợp phổ biến nhưng không phải các trường hợp tùy chỉnh. Khi một sổ làm việc xác định mã định dạng riêng của nó, ví dụ như một thứ tự không chuẩn hoặc tên tháng được bản địa hóa, id sẽ nằm ngoài phạm vi tích hợp sẵn và trỏ vào bảng định dạng số của sổ làm việc. Đối với những trường hợp đó, việc nhận diện một ngày tháng đồng nghĩa với việc đọc chuỗi mã định dạng và tìm kiếm các khóa ngày tháng (date tokens). HotXLS gộp cả hai kiểm tra thành một vị từ nội bộ duy nhất, XlsxNumFmtIsDate, trả về true ngay lập tức cho các dải ngày tích hợp sẵn và ngược lại phân tích mã định dạng tùy chỉnh thông qua XlsxFormatCodeIsDate. Mặt công khai của quá trình đó là các thuộc tính NumberFormat và chỉ mục NumberFormatIndex của ô, cung cấp cho bạn cả mã định dạng đã giải quyết và id để kiểm tra.
Tại sao bộ phân tích định dạng không thể chỉ quét d và m
Việc phân tích một mã định dạng để tìm các khóa ngày tháng trông có vẻ đơn giản cho đến khi bạn nhớ ra những thành phần khác tồn tại trong một định dạng số. Việc tìm kiếm thô sơ các chữ cái cấu thành ngày tháng, các ký tự d, m, y, h và s của ngày, tháng, năm, giờ và giây, sẽ bị kích hoạt nhầm trên hai cấu trúc không phải là khóa ngày tháng chút nào.
Thứ nhất là chuỗi ký tự văn bản nằm trong dấu ngoặc kép. Một định dạng số có thể nhúng văn bản thô trong dấu ngoặc kép, do đó một định dạng tài chính như #,##0 "MM" sẽ thêm các ký tự M và M vào một con số mà không mang bất kỳ ý nghĩa thời gian nào. Một bộ quét đếm các chữ cái bên trong dấu ngoặc kép là các khóa tháng sẽ đánh dấu sai định dạng tiền tệ đó là ngày tháng. Thứ hai là phần nằm trong ngoặc vuông. Các định dạng số mang các chỉ thị trong ngoặc vuông, như tên màu sắc [Red], các điều kiện so sánh như [>1000], các thẻ vùng miền, và các ký hiệu thời gian trôi qua [h] and [mm]. Một số nội dung trong ngoặc vuông chứa các chữ cái ngày tháng và số khác thì không, và việc xử lý văn bản trong ngoặc vuông giống như phần thân của định dạng dẫn đến cả các cảnh báo sai lẫn các trường hợp bị bỏ sót.
Bộ phân tích cú pháp chính xác phải duyệt qua mã định dạng theo từng ký tự, theo dõi xem nó có đang nằm trong một chuỗi văn bản trích dẫn hay không và nằm sâu mức nào trong cặp ngoặc vuông lồng nhau, và nó cũng phải tôn trọng dấu gạch chéo ngược (backslash escape) vốn trích dẫn một ký tự đơn lẻ theo sau. Chỉ một chữ cái ngày tháng không được thoát hiểm nằm ngoài bất kỳ chuỗi văn bản nào và ngoài bất kỳ phần ngoặc vuông nào mới được tính là khóa ngày tháng thực tế. Đó chính xác là cách XlsxFormatCodeIsDate quét dữ liệu: một dấu ngoặc kép đảo ngược trạng thái trong-chuỗi để ngăn chặn việc phát hiện khóa cho đến khi gặp dấu ngoặc kép đóng, một dấu gạch chéo ngược bỏ qua ký tự tiếp theo, và một bộ đếm độ sâu ngoặc vuông ngăn chặn phát hiện khóa bên trong các dải [...]. Kết quả là định dạng #,##0 "MM" được đọc chính xác là định dạng số, trong khi một mã tùy chỉnh ngắn gọn chỉ chứa một ký tự m hoặc d duy nhất ngoài dấu ngoặc kép vẫn được nhận diện chính xác là ngày tháng.
Đọc ngày tháng từ các tệp của bên thứ ba
Mọi thứ ở trên hội tụ về một luồng công việc: chuyển đổi một con số do một ứng dụng khác ghi trở lại thành một ngày tháng bạn có thể tin cậy. Số tuần tự cung cấp số đếm ngày, cờ Date1904 của sổ làm việc cho bạn biết kỷ nguyên nào được sử dụng để đo số đếm đó, và id định dạng số hoặc mã tùy chỉnh của ô là bằng chứng duy nhất cho thấy con số đó ban đầu được hiểu là ngày tháng. Bỏ sót bất kỳ điều nào trong ba yếu tố và bạn sẽ nhận lại một kết quả sai trông có vẻ hợp lý thay vì một lỗi có thể nhìn thấy.
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;Phía BIFF kế thừa có một bẫy bổ sung đáng lưu ý. Trong một luồng .xls cũ hơn, một dải các ô số liền kề có thể được đóng gói vào một bản ghi đa ô duy nhất, MULRK, lưu trữ nhiều giá trị cùng các tham chiếu định dạng của chúng trong một cấu trúc. Các ô ngày tháng được lưu trữ theo cách đó vẫn là ngày tháng, do đó phép kiểm tra id định dạng tương tự phải áp dụng cho từng ô riêng lẻ bên trong bản ghi đa ô, và độ lệch 1462 ngày vẫn kiểm soát mỗi số tuần tự nó mang lại. Một trình đọc chỉ kiểm tra các bản ghi số độc lập và bỏ qua các bản ghi được đóng gói sẽ âm thầm biến một cột ngày tháng thành một cột chứa các số nguyên.
Ánh xạ các số tuần tự sang TDateTime trong thực tế
Một khi phép kiểm tra định dạng xác nhận đó là ngày tháng và cờ Date1904 được xác định, việc chuyển đổi chỉ mang tính cơ học. Một giá trị mà HotXLS đã trả về dưới dạng varDate là một TDateTime bạn có thể sử dụng trực tiếp. Một giá trị đi tới dưới dạng kiểu thực Double trần trụi, vốn xảy ra khi tệp nguồn ghi một số tuần tự không đi kèm một định dạng ngày được nhận diện, được chuyển đổi bằng cách đọc nó dưới dạng số đếm ngày trên trục 1900 và, đối với một sổ làm việc 1904, trừ đi độ lệch 1462 ngày trước để căn chỉnh các kỷ nguyên. Đi theo chiều ngược lại, việc gán một TDateTime cho một ô sẽ lưu trữ số tuần tự dựa trên trục 1900, và HotXLS áp dụng cùng một sự dịch chuyển 1462 ngày khi lưu khi sổ làm việc được gắn cờ 1904, để tệp được lưu hiển thị đúng ngày bạn dự định chứ không phải một ngày bị lệch đi bốn năm.
Đặt cờ này một cách có chủ ý khi bạn tạo ra một sổ làm việc. Giá trị mặc định để trống thuộc tính Date1904 là false, khớp với Excel trên Windows và hầu như luôn là thứ bạn muốn; chỉ đặt nó thành true khi bạn đang tái tạo một sổ làm việc có nguồn gốc từ máy Mac hoặc một hệ thống hạ nguồn cụ thể yêu cầu trục 1904. Quy tắc duy nhất giúp ngăn chặn toàn bộ nhóm lỗi lệch bốn năm là sự nhất quán: chọn kỷ nguyên một lần cho mỗi sổ làm việc, ghi mọi ngày dưới kỷ nguyên đó, và đọc mọi số tuần tự ngược lại dưới cờ mà tệp thực tế mang theo.
Ngày tháng là một cột trong câu chuyện rộng lớn hơn về những gì một ô thực sự chứa. Lớp siêu dữ liệu lân cận, tiêu đề, tác giả và dấu thời gian đi kèm sổ làm việc, được đề cập trong biết viết về siêu dữ liệu sổ làm việc và thuộc tính tài liệu, nơi các giá trị Created và Modified tương tự được lưu trữ dưới dạng TDateTime với cùng quy ước giá trị không thiết lập bằng không. Khi một ngày tháng là kết quả của một phép tính thay vì một giá trị được lưu trữ, các quy tắc đánh giá trong bài viết của chúng tôi về trình tính toán công thức và hàm tùy chỉnh sẽ xác định số tuần tự mà định dạng sau đó hiển thị. Cả hai đều hoạt động trên cùng một mô hình ngày tháng đi kèm trong HotXLS Component dành cho Delphi và C++Builder, vốn đọc và ghi các ngày tháng XLS và XLSX không cần tự động hóa Excel.