سریال‌های تاریخ اکسل در دلفی: 1900 در مقابل 1904 و numFmt

یک صفحه گسترده باز کنید، روی سلولی که 2026-06-19 را نشان می‌دهد کلیک کنید، و نوار فرمول هنوز یک تاریخ را می‌خواند. همان سلول را از دلفی بخوانید و عدد 46192 را دریافت می‌کنید. هر دو دیدگاه درست هستند، زیرا اکسل هرگز تاریخی را در آن سلول ذخیره نکرده است. اکسل یک شماره سریال، یک شمارش روز ذخیره کرده و یک قالب عدد به آن پیوست کرده است که به صفحه نمایش می‌گوید این شمارش را به عنوان یک تاریخ تقویمی ارائه دهد. هیچ نوع تاریخی در مقدار سلول وجود ندارد. یک عدد و یک قاعده نمایش وجود دارد، و قاعده نمایش تنها چیزی است که یک تاریخ را از یک کمیت ساده متمایز می‌کند

این جدایی ریشه هر باگ تاریخی است که یک کتابخانه صفحه گسترده باید از آن دوری کند. یک سریال به تنهایی نمی‌گوید چه روزی است، زیرا نمی‌گوید روز صفر چه روزی بوده است. یک عدد یکسان به معنای دو تاریخ با چهار سال فاصله است که به یک پرچم کارپوشه بستگی دارد. و عددی که باید به عنوان یک تاریخ خوانده شود، به عنوان یک کمیت خام خوانده می‌شود مگر اینکه چیزی قالب آن را بررسی کند و الگوی تاریخ را تشخیص دهد. اینگونه است که مدل تاریخ در HotXLS ساخته می‌شود، و به همین دلیل باید اینگونه باشد

یک سلول تاریخ، یک عدد به اضافه یک قالب است

اکسل یک تاریخ را به عنوان تعداد روزهای پس از یک مبدأ، با زمان روز در بخش اعشاری ذخیره می‌کند. ظهر در یک سریال دارای .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;

دو مبدأ، با فاصله 1462 روز

سیستم تاریخ پیش‌فرض، سیستمی که هر کارپوشه ویندوز از آن استفاده می‌کند، از اواخر 1899 می‌شمارد، به طوری که سریال 1 در روز اول سال 1900 می‌افتد. سیستم دیگر به مکینتاش اولیه برمی‌گردد و از ابتدای سال 1904 می‌شمارد، بنابراین سریال 1 آن چهار سال و یک روز بعد است. یک کارپوشه در یک پرچم ثبت می‌کند که از کدام سیستم استفاده می‌کند. در یک بسته OOXML این پرچم date1904 در بخش کارپوشه است؛ HotXLS آن را به عنوان ویژگی Date1904 کارپوشه نمایان می‌کند

فاصله بین دو مبدأ دقیقاً 1462 روز است. این چهار سال تقویمی است، سه سال 365 روزه و یکی 366 روزه، که در مجموع 1461 می‌شود، به اضافه یکی دیگر برای جابجایی روز-و-کمی بین دو قرارداد روز-صفر. این عدد ثابت است و می‌توانید آن را در ذهن خود نگه دارید. اهمیت آن در این است که صفر نیست. سریالی که از یک کارپوشه 1904 کپی شده و تحت قوانین 1900 تفسیر می‌شود، یا برعکس، هر تاریخ را 1462 روز جابجا می‌کند، که به عنوان تاریخ‌هایی که دقیقاً بیش از چهار سال اشتباه هستند ظاهر می‌شود و به راحتی با داده‌های خراب اشتباه گرفته می‌شود

از آنجا که TDateTime خود دلفی به قرارداد 1900 لنگر انداخته است، کتابخانه‌ای که سریال‌های اکسل را به TDateTime نگاشت می‌کند باید هر زمان که کارپوشه دارای پرچم 1904 باشد، با 1462 روز در هر دو جهت جابجا شود. خواندن یک سریال 1904، قبل از رفتار با آن به عنوان TDateTime، 1462 را کم کنید؛ نوشتن یک TDateTime در یک کارپوشه 1904، 1462 را از سریال کم کنید تا اکسل روزی را که منظور شما بود ارائه دهد. HotXLS هنگامی که مقادیر تاریخ را برای کارپوشه‌ای که Date1904 آن تنظیم شده است سریال‌سازی می‌کند، این جابجایی را در داخل اعمال می‌کند، بنابراین مقداری که به عنوان TDateTime اختصاص می‌دهید در یک سفر رفت و برگشت به همان روز تقویمی در صفحه نمایش تبدیل می‌شود

پیچیدگی عمدی سال کبیسه 1900

یک پیچیدگی معروف در سیستم 1900 وجود دارد. اکسل با سال 1900 به عنوان یک سال کبیسه رفتار می‌کند و 29 فوریه 1900 را به عنوان یک تاریخ واقعی، سریال 60 می‌پذیرد. سال 1900 یک سال کبیسه نبود، زیرا سال‌های قرن تنها زمانی سال‌های کبیسه هستند که بر 400 بخش‌پذیر باشند، و 1900 اینطور نیست. روز خیالی یک رفتار سازگاری عمدی است که از یک صفحه گسترده اولیه به ارث رسیده است که با باگ ارسال شده بود، و از آن زمان نگه داشته شده است تا محاسبات سریال در طول دهه‌ها فایل‌ها یکسان بماند

نتیجه عملی کوچک اما واقعی است: برای هر تاریخی در 1 مارس 1900 یا پس از آن، سریال یکی بیشتر از یک شمارش روز کاملاً صحیح است، زیرا 29 فوریه ناموجود یک عدد مصرف کرده است. یک کتابخانه صفحه گسترده این پیچیدگی را به جای رفع آن بازتولید می‌کند، زیرا مطابقت دقیق محاسبات اکسل تمام کار است. اصلاح آن هر تاریخ مدرن را یک روز از آنچه اکسل نشان می‌دهد دور می‌کند، که نتیجه بدتری نسبت به حمل یک خطای چهل‌هزار روزه یکی-خارج از-محدوده است که هیچ تاریخ واقعی در استفاده تجاری هرگز آن را لمس نمی‌کند. سیستم 1904 هیچ روز خیالی معادلی ندارد، که یکی از دلایلی است که در گذشته برخی فروشگاه‌ها آن را ترجیح می‌دادند

تشخیص یک تاریخ از numFmt

هنگامی که یک عدد از فایلی می‌رسد که شخص دیگری نوشته است، قالب آن تنها مدرک برای تاریخ بودن آن است. ECMA-376 یک بلوک از شناسه‌های قالب داخلی را اختصاص می‌دهد که معنای آن‌ها توسط مشخصات ثابت شده است، و قالب‌های تاریخ و زمان محدوده‌های شناخته شده‌ای را اشغال می‌کنند. شناسه‌های 14 تا 22 قالب‌های تاریخ و زمان زبان-عمومی هستند، آشنا m/d/yyyy، h:mm و نزدیکان آن‌ها. شناسه‌های 45 تا 47 قالب‌های زمان-سپری‌شده هستند. دو باند دیگر، 27 تا 36 و 50 تا 58، قالب‌های تاریخ و زمان مخصوص زبان هستند که برای تقویم‌های CJK استفاده می‌شوند و در ECMA-376 18.8.30 تعریف شده‌اند. سلولی که شناسه قالب عدد آن در هر یک از این محدوده‌ها قرار می‌گیرد، یک سلول تاریخ یا زمان است

شناسه‌های داخلی موارد رایج را پوشش می‌دهند اما موارد سفارشی را نه. وقتی یک کارپوشه کد قالب خود را تعریف می‌کند، مثلاً یک ترتیب غیر استاندارد یا یک نام ماه محلی، شناسه بالاتر از محدوده داخلی است و به جدول قالب-عدد کارپوشه اشاره می‌کند. برای آن دسته، تشخیص یک تاریخ به معنای خواندن رشته کد-قالب و جستجوی نشانه‌های تاریخ است. HotXLS هر دو بررسی را در یک گزاره داخلی ادغام می‌کند، XlsxNumFmtIsDate، که فوراً برای محدوده‌های تاریخ داخلی true برمی‌گرداند و در غیر این صورت کد قالب سفارشی را از طریق XlsxFormatCodeIsDate تجزیه می‌کند. جنبه عمومی آن رشته NumberFormat سلول و NumberFormatIndex آن است، که هم کد قالب حل شده و هم شناسه را برای آزمایش به شما می‌دهد

چرا تجزیه‌گر قالب نمی‌تواند به سادگی به دنبال d و m بگردد

تجزیه یک کد قالب برای نشانه‌های تاریخ تا زمانی که به یاد بیاورید چه چیز دیگری در یک قالب عدد زندگی می‌کند، بی‌اهمیت به نظر می‌رسد. جستجوی ساده برای حروفی که تاریخ‌ها را می‌نویسند، d، m، y، h، و s برای روز، ماه، سال، ساعت، و ثانیه، در دو ساختار که اصلاً نشانه‌های تاریخ نیستند، اشتباه عمل می‌کند

اولین مورد، نماد متنی نقل قول شده است. یک قالب عدد می‌تواند متن کلمه به کلمه را در گیومه‌های دوتایی جاسازی کند، بنابراین یک قالب مالی مانند #,##0 "MM" کاراکترهای M و M را به عددی بدون هیچ معنای زمانی اضافه می‌کند. یک اسکنر که حروف داخل گیومه‌ها را به عنوان نشانه‌های ماه می‌شمارد، به اشتباه آن قالب ارز را به عنوان تاریخ پرچم‌گذاری می‌کند. دومین مورد بخش براکت است. قالب‌های عدد دستورالعمل‌هایی را در براکت‌های مربع حمل می‌کنند، نام رنگ‌ها مانند [Red]، شرایط مقایسه مانند [>1000]، برچسب‌های محلی، و نشانگرهای زمان سپری شده [h] و [mm]. برخی از محتوای براکت حروف تاریخ را نگه می‌دارند و برخی نه، و رفتار یکسان با متن در براکت و بدنه قالب منجر به موارد مثبت کاذب و موارد از دست رفته می‌شود

تجزیه‌گر صحیح کد قالب را کاراکتر به کاراکتر می‌پیماید، و ردیابی می‌کند که آیا داخل یک نماد نقل قول شده است و چقدر در داخل تودرتوی براکت قرار دارد، و همچنین به فرار بک‌اسلش (backslash escape) که یک کاراکتر منفرد بعدی را نقل می‌کند، احترام می‌گذارد. تنها یک حرف تاریخ فرار نکرده (unescaped) که در خارج از هر نماد رشته و خارج از هر بخش براکت یافت شود، به عنوان یک نشانه تاریخ واقعی حساب می‌شود. این دقیقاً همان روشی است که 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 در هنگام ذخیره، زمانی که کارپوشه دارای پرچم 1904 است، همان شیفت 1462 روزه را اعمال می‌کند، بنابراین فایل ذخیره شده به جای تاریخی که چهار سال جابجا شده باشد، تاریخی را نشان می‌دهد که شما در نظر داشتید

هنگام تولید کارپوشه این پرچم را آگاهانه تنظیم کنید. حالت پیش‌فرض Date1904 را false می‌گذارد، که با اکسل برای ویندوز مطابقت دارد و تقریباً همیشه همان چیزی است که می‌خواهید؛ آن را تنها زمانی روی true تنظیم کنید که در حال بازتولید کارپوشه‌ای از مکینتاش هستید یا یک سیستم پایین‌دستی به طور خاص انتظار محور 1904 را دارد. تنها قاعده‌ای که از کل کلاس خطاهای چهار ساله جلوگیری می‌کند، سازگاری است: مبدأ را یک بار در هر کارپوشه انتخاب کنید، هر تاریخ را تحت آن بنویسید، و هر سریال را تحت پرچمی که فایل در واقع آن را حمل می‌کند بازخوانی کنید

تاریخ‌ها یک ستون در داستان وسیع‌تری در مورد آنچه که یک سلول واقعاً در خود دارد هستند. لایه ابرداده مجاور، عنوان و نویسنده و مهر زمانی که در کنار شبکه قرار می‌گیرند، در مقاله ما در مورد ابرداده کارپوشه و ویژگی‌های سند پوشش داده شده است، جایی که همان مقادیر Created و Modified به عنوان TDateTime با همان قرارداد تنظیم‌نشده‌برابر‌با‌صفر ذخیره می‌شوند. هنگامی که یک تاریخ نتیجه یک محاسبه باشد نه یک مقدار ذخیره شده، قوانین ارزیابی در مقاله ما در مورد موتور فرمول و توابع سفارشی سریالی را تعیین می‌کنند که قالب سپس ارائه می‌دهد. هر دو بر روی یک مدل تاریخ کار می‌کنند که در کامپوننت HotXLS برای Delphi و C++Builder عرضه می‌شود، که تاریخ‌های XLS و XLSX را بدون اتوماسیون اکسل می‌خواند و می‌نویسد