یک صفحه گسترده را باز کنید، روی سلولی که مقدار 2026-06-19 را نشان میدهد کلیک کنید، و نوار فرمول همچنان یک تاریخ را میخواند. همان سلول را از Delphi بخوانید و عدد 46192 را دریافت میکنید. هر دو نما درست هستند، زیرا Excel هرگز تاریخی را در آن سلول ذخیره نکرده است. بلکه یک شماره سریال، یعنی شمارش روزها را ذخیره کرده و یک قالب عدد را به آن متصل ساخته است که به صفحه نمایش میگوید شمارش را به عنوان یک تاریخ تقویمی رندر کند. هیچ نوع تاریخی در مقدار سلول وجود ندارد. یک عدد و یک قانون نمایش وجود دارد، و قانون نمایش تنها چیزی است که یک تاریخ را از یک مقدار ساده متمایز میکند
این جداسازی ریشه هر باگ تاریخی است که یک کتابخانه صفحه گسترده باید از آن دوری کند. یک سریال به تنهایی نمیگوید چه روزی است، زیرا نمیگوید روز صفر چه زمانی بوده است. همان عدد بسته به یک پرچم کتاب کار، به معنای دو تاریخ با چهار سال فاصله است. و عددی که باید به عنوان تاریخ خوانده شود، به عنوان یک مقدار ساده خوانده میشود مگر اینکه چیزی قالب آن را بازرسی کرده و الگوی تاریخ را تشخیص دهد. مدل تاریخ در HotXLS به این صورت ساخته شده است و دلیل وجود آن نیز همین است
یک سلول تاریخ، شامل یک عدد به علاوه یک قالب است
نرمافزار Excel تاریخ را به عنوان تعداد روزهای گذشته از یک مبدا ذخیره میکند که زمان روز در بخش اعشاری آن قرار دارد. ظهر در یک سریال مقدار .5 را حمل میکند. بخش صحیح شمارش روز است. هیچ چیز در مقدار ذخیرهشده آن را به عنوان یک مقدار زمانی علامتگذاری نمیکند. آنچه آن را مشخص میکند قالب عدد سلول است: استاندارد ECMA-376 این را یک numFmt مینامد و سلولی که کد قالب آن الگوی تاریخ یا زمان را بیان میکند، به عنوان تاریخ نشان داده میشود. قالب را بردارید تا همان سلول یک عدد را نشان دهد؛ مقدار اصلی هرگز تغییر نکرده است
به همین دلیل است که خواندن مقدار یک سلول به شما یک Variant میدهد که ممکن است یک varDate یا یک 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;دو مبدا با ۱۴۶۲ روز فاصله
سیستم تاریخ پیشفرض، یعنی همان سیستمی که هر کتاب کار ویندوز از آن استفاده میکند، از پایان سال ۱۸۹۹ شمارش میکند، به طوری که سریال 1 در اولین روز سال ۱۹۰۰ قرار میگیرد. سیستم دیگر به مکینتاش اولیه بازمیگردد و از ابتدای سال ۱۹۰۴ شمارش میکند، بنابراین سریال 1 آن چهار سال و یک روز بعد است. یک کتاب کار ثبت میکند که از کدام سیستم در یک پرچم استفاده مینماید. در یک بسته OOXML آن پرچم date1904 در بخش کتاب کار است؛ HotXLS آن را به عنوان ویژگی Date1904 کتاب کار ارائه میدهد
فاصله بین این دو مبدا دقیقاً ۱۴۶۲ روز است. این یعنی چهار سال تقویمی، سه سال ۳۶۵ روزه و یک سال ۳۶۶ روزه که در مجموع ۱۴۶۱ روز میشود، به علاوه یک روز دیگر برای اختلاف روز و خردهای بین دو قرارداد روز صفر. این عدد ثابت است و میتوانید آن را در ذهن خود بسپارید. اهمیت آن در این است که صفر نیست. یک سریال کپیشده از کتاب کار ۱۹۰۴ و تفسیرشده تحت قوانین ۱۹۰۰، یا برعکس، هر تاریخی را ۱۴۶۲ روز جابهجا میکند که به صورت تاریخهایی ظاهر میشود که کمی بیش از چهار سال اشتباه هستند و به راحتی با دادههای خراب اشتباه گرفته میشوند
از آنجا که TDateTime خود Delphi به قرارداد ۱۹۰۰ متصل است، کتابخانهای که سریالهای Excel را به TDateTime نگاشت میکند، باید هر زمان که کتاب کار با پرچم ۱۹۰۴ علامتگذاری شده، مقدار ۱۴۶۲ را در هر دو جهت آفست کند. هنگام خواندن یک سریال ۱۹۰۴، قبل از برخورد با آن به عنوان یک TDateTime، مقدار ۱۴۶۲ را کم کنید؛ هنگام نوشتن یک TDateTime در کتاب کار ۱۹۰۴، مقدار ۱۴۶۲ را از سریال کم کنید تا Excel روز مورد نظر شما را رندر کند. نرمافزار HotXLS این تغییر را به طور داخلی زمانی که مقادیر تاریخ را برای کتاب کاری که Date1904 آن تنظیم شده سریالسازی میکند، اعمال مینماید، بنابراین مقداری که به عنوان یک TDateTime اختصاص میدهید به همان روز تقویمی روی صفحه نمایش بازمیگردد
رفتار عجیب و عمدی سال کبیسه ۱۹۰۰
یک مشکل مشهور در سیستم ۱۹۰۰ وجود دارد. نرمافزار Excel با سال ۱۹۰۰ به عنوان یک سال کبیسه برخورد میکند و ۲۹ فوریه ۱۹۰۰ را به عنوان یک تاریخ واقعی با سریال 60 میپذیرد. سال ۱۹۰۰ یک سال کبیسه نبود، زیرا سالهای قرن تنها زمانی کبیسه هستند که بر ۴۰۰ بخشپذیر باشند و ۱۹۰۰ اینطور نیست. این روز خیالی یک رفتار سازگاری عمدی است که از یک صفحه گسترده قدیمی که با این باگ عرضه شده بود به ارث رسیده و از آن زمان حفظ شده است تا محاسبات سریال در طول دههها فایل یکسان باقی بماند
نتیجه عملی کوچک اما واقعی است: برای هر تاریخی در تاریخ ۱ مارس ۱۹۰۰ یا پس از آن، شماره سریال یک واحد بالاتر از شمارش روز کاملاً صحیح است، زیرا ۲۹ فوریه غیرموجود یک عدد را مصرف کرده است. یک کتابخانه صفحه گسترده این رفتار عجیب را به جای رفع آن بازتولید میکند، زیرا مطابقت دقیق با محاسبات Excel کل وظیفه آن است. اصلاح آن باعث میشود که هر تاریخ مدرن یک روز با آنچه Excel نشان میدهد تفاوت داشته باشد، که نتیجه بدتری نسبت به حمل یک خطای off-by-one با قدمت چهل هزار روز است که هیچ تاریخ واقعی در کسب و کار هرگز به آن دست نمیزند. سیستم ۱۹۰۴ هیچ روز خیالی معادلی ندارد، که یکی از دلایلی است که برخی از مجموعهها از نظر تاریخی آن را ترجیح میدادند
تشخیص تاریخ از روی numFmt
وقتی عددی از فایلی که شخص دیگری نوشته است میرسد، قالب آن تنها مدرکی است که نشان میدهد این عدد یک تاریخ است. استاندارد ECMA-376 بلوکی از شناسههای قالب داخلی را اختصاص میدهد که معنای آنها توسط مشخصات فنی ثابت شده است و قالبهای تاریخ و زمان محدودههای شناختهشده را اشغال میکنند. شناسههای ۱۴ تا ۲۲ قالبهای تاریخ و زمان عمومی هستند، مانند قالبهای آشنای m/d/yyyy، h:mm و موارد مشابه. شناسههای ۴۵ تا ۴۷ قالبهای زمان سپریشده هستند. دو باند دیگر، ۲۷ تا ۳۶ و ۵۰ تا ۵۸، قالبهای تاریخ و زمان خاص منطقه هستند که برای تقویمهای CJK استفاده میشوند و در بخش ۱۸.۸.۳۰ استاندارد ECMA-376 تعریف شدهاند. سلولی که شناسه قالب عدد آن در هر یک از این محدودهها قرار گیرد، یک سلول تاریخ یا زمان است
شناسههای داخلی موارد معمول را پوشش میدهند اما موارد سفارشی را شامل نمیشوند. وقتی یک کتاب کار کد قالب خود را تعریف میکند، مثلاً یک ترتیب غیراستاندارد یا نام ماه محلیشده، شناسه بالاتر از محدوده داخلی است و به جدول قالب عدد کتاب کار اشاره دارد. برای این موارد، تشخیص تاریخ به معنای خواندن رشته کد قالب و جستجوی نشانههای (Tokens) تاریخ است. نرمافزار HotXLS هر دو بررسی را در یک گزاره داخلی به نام XlsxNumFmtIsDate تترکیب میکند که برای محدودههای تاریخ داخلی بلافاصله مقدار true را برمیگرداند و در غیر این صورت کد قالب سفارشی را از طریق XlsxFormatCodeIsDate تجزیه میکند. بخش عمومی آن رشته NumberFormat سلول و NumberFormatIndex آن است که هم کد قالب حلشده و هم شناسه را برای آزمایش به شما میدهد
چرا تجزیهکننده قالب نمیتواند فقط d و m را اسکن کند
تجزیه یک کد قالب برای نشانههای تاریخ ساده به نظر میرسد تا زمانی که به یاد آورید چه چیز دیگری در یک قالب عدد زندگی میکند. جستجوی ساده برای حروف نشاندهنده تاریخ یعنی حروف d، m، y، h و s برای روز، ماه، سال، ساعت و ثانیه، در دو ساختار که اصلاً نشانه تاریخ نیستند، اشتباه خواهد کرد
اولین مورد، رشته متنی ثابت در داخل علامت نقل قول است. یک قالب عدد میتواند متن ثابت را در علامت نقل قول دوتایی جاسازی کند، بنابراین یک قالب مالی مانند #,##0 "MM" نویسههای M و M را بدون هیچ معنای زمانی به یک عدد اضافه میکند. اسکنری که حروف داخل نقل قول را به عنوان نشانههای ماه میشمارد، به اشتباه آن قالب ارز را به عنوان تاریخ علامتگذاری میکند. دومین مورد بخش براکت است. قالبهای عدد دستورالعملهایی را در براکتهای مربع حمل میکنند، نامهای رنگ مانند [Red]، شرایط مقایسه مانند [>1000]، برچسبهای منطقه و نشانگرهای زمان سپریشده [h] و [mm]. برخی از محتویات براکت شامل حروف تاریخ هستند و برخی دیگر نه، و برخورد یکسان با متن براکتدار با بدنه قالب منجر به موارد مثبت کاذب و موارد از دست رفته میشود
تجزیهکننده صحیح کد قالب را کاراکتر به کاراکتر پیمایش میکند، و پیگیری مینماید که آیا در داخل یک رشته نقلقولشده قرار دارد و چقدر در داخل ساختار تو در توی براکت عمیق است، و همچنین به فرار بکاسلش (Backslash escape) که کاراکتر بعدی را نقلقول میکند احترام میگذارد. فقط یک حرف تاریخ بدون علامت فرار که در خارج از هر رشته ثابت و خارج از هر بخش براکت یافت میشود، به عنوان یک نشانه تاریخ واقعی به حساب میآید. این دقیقاً نحوه اسکن XlsxFormatCodeIsDate است: یک علامت نقل قول وضعیت داخل رشته را تغییر میدهد که تشخیص نشانه را تا زمان نقل قول بسته سرکوب میکند، یک بکاسلش از کاراکتر بعدی عبور مینماید و شمارنده عمق براکت تشخیص را در بخشهای [...] سرکوب میکند. نتیجه این است که قالب #,##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 بستهبندی کرد که چندین مقدار را با مراجع قالب آنها در یک ساختار ذخیره میکند. سلولهای تاریخ ذخیرهشده به این روش با وجود بستهبندی شدن همچنان تاریخ هستند، بنابراین همان تست شناسه قالب باید به داخل رکورد چندسلولی برسد و برای هر سلول اعمال شود، و آفست ۱۹۰۴ همچنان بر هر سریالی که تولید میکند حاکم است. خوانندهای که فقط رکوردهای عددی مستقل را بازرسی میکند و رکوردهای بستهبندیشده را نادیده میگیرد، به طور بیصدا یک ستون تاریخ را به یک ستون از اعداد صحیح تبدیل خواهد کرد
نگاشت سریالها به TDateTime در عمل
هنگامی که بررسی قالب وجود تاریخ را تأیید کرد و پرچم Date1904 مشخص شد، تبدیل مکانیکی است. مقداری که HotXLS قبلاً به عنوان یک varDate تحویل داده است، یک TDateTime است که میتوانید مستقیماً از آن استفاده کنید. مقداری که به صورت یک Double ساده میرسد، که وقتی منبع یک سریال را بدون قالب تاریخ شناختهشده نوشته باشد اتفاق میافتد، با خواندن آن به عنوان شمارش روز در محور ۱۹۰۰ و برای یک کتاب کار ۱۹۰۴، با کم کردن اولیه آفست ۱۴۶۲ روز تبدیل میشود تا مبداها تراز شوند. در جهت دیگر، اختصاص یک TDateTime به یک سلول، سریال مبتنی بر ۱۹۰۰ را ذخیره میکند و HotXLS همان تغییر ۱۴۶۲ روزه را در هنگام ذخیره زمانی که کتاب کار با پرچم ۱۹۰۴ علامتگذاری شده اعمال میکند، بنابراین فایل ذخیرهشده تاریخی را نشان میدهد که مد نظر داشتید نه تاریخی با چهار سال انحراف
هنگام ایجاد یک کتاب کار، پرچم را به طور عمدی تنظیم کنید. پیشفرض Date1904 را روی false قرار میدهد که با Excel تحت ویندوز مطابقت دارد و تقریباً همیشه همان چیزی است که میخواهید؛ آن را فقط زمانی روی true تنظیم کنید که در حال بازتولید یک کتاب کار با منشأ مک هستید یا یک سیستم پاییندست به طور خاص انتظار محور ۱۹۰۴ را دارد. تنها قانونی که از کل کلاس خطاهای چهار ساله جلوگیری میکند ثبات است: انتخاب مبدا یک بار در هر کتاب کار، نوشتن هر تاریخ تحت آن، و خواندن هر سریال به عقب تحت پرچمی که فایل در واقع حمل میکند
تاریخها یک ستون از داستان گستردهتری درباره محتوای واقعی یک سلول هستند. لایه متادیتای مجاور، عنوان و نویسنده و مهرهای زمانی که در کنار شبکه قرار میگیرند، در مقاله ما درباره متادیتای کتاب کار و ویژگیهای سند پوشش داده شده است، جایی که همان مقادیر Created و Modified به عنوان TDateTime با همان قرارداد نامشخص-برابر-با-صفر ذخیره میشوند. زمانی که یک تاریخ نتیجه یک محاسبات است نه یک مقدار ذخیرهشده، قوانین ارزیابی در مقاله ما درباره موتور فرمول و توابع سفارشی شماره سریالی را تعیین میکنند که قالب سپس آن را رندر میکند. هر دو روی همان مدل تاریخی کار میکنند که در کامپوننت HotXLS برای Delphi و C++Builder عرضه میشود، که تاریخهای XLS و XLSX را بدون اتوماسیون Excel میخواند و مینویسد