خواندن مقدار کش‌شده فرمول Excel در Delphi بدون محاسبه مجدد

HotXLS، کتابخانه بومی Excel برای Delphi و C++Builder، مقداری را که Excel پیش‌تر کنار فرمول ذخیره کرده از طریق TryGetCachedFormulaValue و IXLSFormulaCacheReader می‌خواند. هیچ‌کدام از این دو مدخل موتور محاسبه را صدا نمی‌زند، توکن‌های فرمول را دی‌کامپایل نمی‌کند، وضعیت dirty را به‌روز نمی‌کند یا چیزی به مدل نمی‌نویسد، پس کتاب‌کاری که فقط آن را می‌خوانید دقیقاً همان‌طور که بازش کردید می‌ماند

سناریویی که پشت این قابلیت است کسل‌کننده و به‌شدت رایج است. یک job شبانه چند صد کتاب‌کار ساخته‌ی دیگران را باز می‌کند، از هر کدام یک ستون جمع کل بیرون می‌کشد و اعداد را به یک انبار داده تلیل می‌کند. جمع‌کل‌ها همین حالا داخل فایل‌ها نشسته‌اند — Excel آنها را محاسبه و ذخیره کرده است. اما همان لحظه‌ای که job از یک سلول فرمول مقدارش را می‌پرسد، کتابخانه‌ای که برای آن پرسش فقط یک جواب دارد یک گراف وابستگی می‌سازد و کل شیت را ارزیابی می‌کند، و jobای که باید مقید به I/O می‌بود به یک بنچمارک محاسبه تبدیل می‌شود

چرا خواندن یک سلول فرمول هزینه یک محاسبه مجدد کامل دارد؟

چون یک value getter روی سلول فرمول درخواست تولید یک مقدار است، و تنها راه همه‌جا درست برای تولید آن ارزیابی فرمول است. این پیش‌فرض درست برای برنامه‌ای است که کتاب‌کارها را ویرایش می‌کند و پیش‌فرض غلط برای پایپ‌لاینی که آنها را استخراج می‌کند. بدتر اینکه ارزیابی از عوارض جانبی خالی نیست: نتیجه‌ها را داخل سلول‌ها می‌نویسد، فلگ‌های dirty را برعکس می‌کند، و وقتی تابعی پشتیبانی نشده باشد یا ارجاع خارجی شکسته باشد می‌تواند متفاوت از برنامه تولیدکننده حل شود. jobای که به تیم عملیات‌تان فقط‌خواندنی معرفی‌اش کرده بودید بی‌سروصدا کتاب‌کاری تولید می‌کند که دیگر با کتاب‌کار روی دیسک مطابقت ندارد، و اگر بعداً چیزی آن را ذخیره کند، فایل روی دیسک هم عوض می‌شود

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

سه واقعیت متعامد درباره یک سلول

اول نتیجه‌گیری: یک مقدار کش‌شده فرمول سه واقعیت مستقل حمل می‌کند و فروکاستن آنها در یک Variant تنها اطلاعاتی را که نیاز دارید از بین می‌برد. TXLSFormulaCacheInfo آنها را به‌شکل State و Kind و Value جدا نگه می‌دارد. TXLSFormulaCacheState خاستگاه را در پنج حالت ثبت می‌کند — xlfcsNotFormula و xlfcsMissing و xlfcsLoaded و xlfcsCalculated و xlfcsInvalidated — در حالی که TXLSFormulaCacheValueKind بار داده را به xlfcvBlank و xlfcvNumber و xlfcvDateTime و xlfcvString و xlfcvBoolean یا xlfcvError دسته‌بندی می‌کند. همین جداسازی است که اجازه می‌دهد وجود داشتن صادقانه گزارش شود: یک blank کش‌شده، یک رشته خالی کش‌شده، یک False کش‌شده، یک صفر کش‌شده و یک خطای کش‌شده همه مقدارهای واقعی‌اند، پس وجود هیچ‌گاه از VarIsEmpty یا VarIsNull استنباط نمی‌شود. TryGetCachedFormulaValue فقط برای xlfcsLoaded و xlfcsCalculated مقدار True برمی‌گرداند و وقتی False برمی‌گرداند هم همچنان یک state قابل تشخیص پر می‌کند

var
  Book: TXLSXWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSXWorkbook.Create;
  try
    Book.Open('quarterly-model.xlsx');
    // SheetIndex و Row و Col این‌جا همه یک-مبنایند
    if Book.TryGetCachedFormulaValue(1, 12, 5, Info) then
      Writeln('cached value: ', VarToStr(Info.Value))
    else
      Writeln('no usable cache, state ordinal ', Ord(Info.State));
  finally
    Book.Free;
  end;
end;

چرا مقدار کش‌شده غایب است؟

دقیقاً چهار دلیل وجود دارد که TryGetCachedFormulaValue مقدار False تحویل می‌دهد، و state به شما می‌گوید کدام‌یک مصداق پیدا کرده. xlfcsNotFormula یعنی سلول یک literal دارد یا هیچ، و مختصاتِ بیرون از بازه هم به همان جواب خلاصه می‌شود. xlfcsMissing یعنی سلول واقعاً فرمول است اما تولیدکننده برای آن هیچ بار مقداری ذخیره نکرده — نتیجه‌ای رایج وقتی یک جنریتور فرمول‌ها را می‌نویسد و پر شدن نتیجه‌ها را در اولین باز شدن به Excel می‌سپارد. xlfcsInvalidated یعنی متن فرمول بعد از لود جایگزین شده، پس مقداری که قبلاً آن‌جا بود عبارتی را توصیف می‌کند که دیگر وجود ندارد. xlfcsCalculated در مقابل، حالتی موفق است: مقداری را علامت می‌زند که کد خودتان یا ارزیاب HotXLS در همین نشست تولید کرده، برخلاف xlfcsLoaded که از فایل آمده

صداقت درباره یک کش غایب مهم‌تر از لکه‌گیری روی آن است. HotXLS از اختراع یک مقدار خودداری می‌کند، و در ذخیره به همان اندازه سخت‌گیر است — فقط xlfcsLoaded و xlfcsCalculated مقدار کش‌شده منتشر می‌کنند، در حالی که xlfcsMissing و xlfcsInvalidated فرمول را به‌تنهایی می‌نویسند به‌جای اینکه یک عدد کهنه را داخل فایل منجمد کنند. این برایتان سه پاسخ عاقلانه در پایپ‌لاین باقی می‌گذارد: رد کردن ردیف و ثبت شکاف، محاسبه مجدد عمدی همان یک کتاب‌کار و پذیرفتن هزینه‌اش، یا ارزیابی و تطبیق. اگر عدد ارزیابی‌شده با آنچه برنامه تولیدکننده می‌نوشت نمی‌خواند، tracer ارزیابی فرمول ابزار پیدا کردن این است که این دو محاسبه کجا از هم واگرا می‌شوند، نه حدس زدن از روی نتیجه

یک reader مشترک میان موتورهای کلاسیک و OOXML و ODF

پایپ‌لاین نباید اهمیت دهد فایلی که همین حالا باز کرده BIFF بوده یا OOXML یا ODF. IXLSFormulaCacheReader تک مدخل فقط‌خواندنی برای هر سه است: هم TXLSWorkbook.CreateFormulaCacheReader و هم TXLSXWorkbook.CreateFormulaCacheReader آداپتور سبکی روی همان جست‌وجوی اسپارس سلول که هر موتور همین حالا استفاده می‌کند برمی‌گردانند، با مختصات sheet و ردیف و ستون یک-مبنای یکسان. کلاس‌های کتاب‌کار عمداً خودشان اینترفیس را پیاده نمی‌کنند — یک ارجاع اینترفیسی به کتاب‌کار معناشناسی مالکیتش را عوض می‌کرد و به فراخوان‌ها اجازه می‌داد از lease طول عمر رد شوند. به‌جایش، نابود کردن کتاب‌کار اشاره‌گر خام داخل آن lease را پاک می‌کند، و هر reader‌ای که هنوز کد شما نگهش داشته در پرسش بعدی‌اش EXLSFormulaCacheReaderInvalidated پرتاب می‌کند به‌جای dereference کردن حافظه آزادشده. این بررسی fail-fast طول عمر است، نه تضمین همروندی

var
  Reader: IXLSFormulaCacheReader;
  Info: TXLSFormulaCacheInfo;
  Row, Missing, Errors: Integer;
  Total: Double;
begin
  Reader := Book.CreateFormulaCacheReader;
  Total := 0;
  Missing := 0;
  Errors := 0;
  for Row := 2 to LastRow do
    if Reader.TryGetCachedFormulaValue(1, Row, 7, Info) then
    begin
      case Info.Kind of
        xlfcvNumber: Total := Total + Double(Info.Value);
        xlfcvError:  Inc(Errors);
      end;
    end
    else if Info.State = xlfcsMissing then
      Inc(Missing);
  // هیچ موتور محاسبه‌ای اجرا نشد، هیچ فلگ dirty جابه‌جا نشد، Book بدون تغییر است
end;

بایت‌های کش‌شده واقعاً کجا زندگی می‌کنند

برای فایل‌های .xls کلاسیک، کش همان فیلد FormulaValue رکورد Formula است، هشت بایتی که [MS-XLS] §2.5.133 توصیفش می‌کند. وقتی high word برابر $FFFF باشد بار داده یک double از IEEE 754 نیست بلکه یک variant برچسب‌دار است، و چیدمانش به‌راحتی به‌شکل ظریفی غلط فهمیده می‌شود: نوع variant در val[0] می‌نشیند و بار boolean یا BErr در val[2]، با val[1] تعریف‌نشده. HotXLS قبلاً بار داده را از val[1] می‌خواند، که همان جنس off-by-one است که فقط روی فایل‌های خاصی که به‌جای عدد یک boolean یا خطا کش می‌کنند ظاهر می‌شود. reader و نویسنده فرمول اشتراکی حالا روی همان آفست‌ها توافق دارند، پس یک TRUE کش‌شده از لود و ذخیره سالم عبور می‌کند به‌جای اینکه به نویز تبدیل شود

وفاداری نوعی در قالب‌های پکیجی مشکل جداگانه‌ای است با تله خودش. در OOXML مقدار کش‌شده به‌شکل <v> به عنصر c آویزان است، با اتریبیوت t که طبق ECMA-376 Part 1 §18.3.1.4 نوع را نام می‌برد. HotXLS مقدار t="e" را مستقیم داخل یک Variant از varError می‌خواند و در ذخیره به متن خطای استانداردش برمی‌گرداند، پس خطاها هرگز خودشان را به‌شکل اعداد صحیح معمولی جا نمی‌زنند — اما RTL دلفی این‌جا به شما کمک نمی‌کند، چون VarAsType(Integer, varError) یک استثنای تبدیل پرتاب می‌کند. ساختمانی که کار می‌کند TVarData.VType و TVarData.VError را مستقیماً ست می‌کند. تاریخ‌ها همان نظم را در جهت مخالف دنبال می‌کنند: t="d" و نوع مقدار تاریخ ODF اعلام نوع صریح‌اند و به varDate تبدیل می‌شوند، در حالی که کش عددی BIFF اصلاً هیچ فلگ تاریخی حمل نمی‌کند و پس یک Double می‌ماند. HotXLS هرگز از فرمت عددی سلول تاریخ حدس نمی‌زند، چون فرمت عددی presentation است و کش داده است. ODF یک مورد دیگر هم اضافه می‌کند که دانستنش می‌ارزد — office:value-type="void" کشی را بیان می‌کند که حاضر است اما هیچ مقداری حمل نمی‌کند، و چون ODF نوع مقدار خطا ندارد، متنی که شبیه خطاست به‌عنوان متن حفظ می‌شود به‌جای اینکه به خطا ارتقا یابد

function DescribeCache(const Info: TXLSFormulaCacheInfo): string;
begin
  case Info.State of
    xlfcsNotFormula:  Result := 'not a formula cell';
    xlfcsMissing:     Result := 'formula stored with no cached value';
    xlfcsInvalidated: Result := 'formula replaced since load';
  else
    case Info.Kind of
      xlfcvError:    Result := 'error code ' + IntToStr(TVarData(Info.Value).VError);
      xlfcvDateTime: Result := DateTimeToStr(VarToDateTime(Info.Value));
      xlfcvBoolean:  Result := BoolToStr(Info.Value, True);
      xlfcvNumber:   Result := FloatToStr(Double(Info.Value));
      xlfcvString:   Result := VarToStr(Info.Value);
    else
      Result := 'present but blank';
    end;
  end;
end;

آیا فرمول‌های اشتراکی مقدارهای کش‌شده‌شان را هم به اشتراک می‌گذارند؟

نه، و فرض خلاف آن همان چیزی است که یک sweep را به گزارش یک عدد برای یک ستون کامل می‌رساند. فرمول اشتراکی OOXML فقط عبارت فرمول و بهینه‌سازی ذخیره را به اشتراک می‌گذارد؛ هر سلول عضو همچنان <v> خودش را دارد. HotXLS پس هرگز کش عضو ریشه را به دنباله‌ای که بدون مقدار رسیده منتشر نمی‌کند، و دنباله‌ای که به‌شکل xlfcsMissing لود شده بعد از ذخیره و باز کردن دوباره همچنان xlfcsMissing گزارش می‌کند. اگر دارید بررسی می‌کنید گروه در اصل چطور ذخیره و بسط داده می‌شود، مکانیک اتریبیوت si فرمول اشتراکی و بسط آن جداگانه پوشش داده شده؛ برای خواندن کش، قاعده به یک خط خلاصه می‌شود — از هر سلول بپرسید، به هیچ چیزی که ازش نپرسیده‌اید اعتماد نکنید

خواندن مقدار کش‌شده، reader یکپارچه بین‌موتوری و موتور محاسبه مجددی که می‌توانید انتخاب کنید صدایش نزنید، همه در HotXLS Delphi Spreadsheet Component استاندارد برای Delphi و C++Builder ارائه می‌شوند، بدون هیچ وابستگی به Excel یا هر سرور اتوماسیون OLE؛ صفحه محصول مرجع کامل API برای مدخل‌های کتاب‌کار و reader نشان‌داده‌شده در این‌جا را حمل می‌کند