توقف محاسبهٔ پنهانی فرمول‌ها هنگام save در XLS در Delphi

HotXLS، همان کتابخانهٔ بومی اکسل برای Delphi و C++Builder، یک workbook کلاسیک BIFF8 با پسوند .xls را cache-first ذخیره می‌کند: TXLSWorksheet.WriteFormula از TXLSWorkbook.TryGetCachedFormulaValue مقداری را می‌پرسد که اکسل کنار هر فرمول ذخیره کرده بود و فقط وقتی آن cache گم شده یا invalidate شده باشد evaluator را صدا می‌زند. workbookی که باز کرده‌ای و هرگز دستش نزده‌ای همان اعداد را برمی‌گرداند، و نتیجه‌های تازه یک فراخوانی صریح Recalculate می‌خواهند نه این‌که عارضهٔ جانبی پنهان SaveAs باشند

باگی که این قرارداد را به زور علنی کرد به‌شکل خجالت‌آوری کوچک بود. یک فایل corpus به نام nested-subtotals.xls یک جمع کل در R2C4 دارد که مقدار cacheشده‌اش 37 است. با HotXLS بازش کن، TryGetCachedFormulaValue را برای آن سلول بپرس، 37 بگیر. بدون تغییر هیچ سلولی saveش کن، نسخهٔ ذخیره‌شده را باز کن، همان سؤال را بپرس، 67 بگیر. از API خواسته نشده بود هیچ‌چیز را حساب کند، ولی یک عدد در فایل دقیقاً 30 جابه‌جا شده بود — و 30 تصادفاً مجموع دو جمع گروهی، 10 و 20، است که داخل بازه‌ای می‌نشینند که جمع کل پوشش می‌دهد

چرا save کردن یک فایل XLS مقدار یک فرمول را عوض می‌کند؟

برای اینکه آن 37 به 67 تبدیل شود باید دو نقص مستقل با هم هم‌تراز می‌شدند، و fix کردن هر یک به‌تنهایی دیگری را پنهان می‌کرد. اولی ساختاری بود: نویسندهٔ کلاسیک هر فرمول را در هر save دوباره حساب می‌کرد. دومی یک بررسی نوع بود که هرگز نمی‌توانست برای فرمولی که از دیسک بارگذاری شده درست باشد، و همین باعث می‌شد evaluator سلول‌های SUBTOTAL تودرتو را دوبار بشمارد. فایل corpus صرفاً اولین ورودی‌ای بود که در آن یک محاسبهٔ دوباره در زمان save جوابی متفاوت از اکسل می‌داد و کسی دو تا را با هم مقایسه کرد. نقص ساختاری ساده است: پیش از v2.382.3، TXLSWorksheet.WriteFormula و خواهر shared formulaش WriteFormulaWithTExp فیلد هشت-بایتی FormulaValue هر رکورد Formula را با صدا زدن TXLSWorkbook.GetFormulaValue به دست می‌آوردند، که همان evaluator است. cacheی که ParseFormula در زمان load با دقت از فایل مبدأ decode کرده بود هرگز در مسیر بیرون مشورت نمی‌شد. در عمل هر save یک محاسبهٔ دوبارهٔ کامل با دور زدن API محاسبهٔ سطح-workbook بود، پس هیچ‌چیز که روی workbook ست کنی جلویش را نمی‌گرفت. هر جایی که evaluator مربوط به HotXLS با اکسل اختلاف داشت، چه یک تابع به‌درستی پشتیبانی‌نشده و چه یک باگ ساده، به یک تغییر پنهان داده در save تبدیل می‌شد

نقص دوم در callback مربوط به subtotal تودرتو بود که evaluator استفاده می‌کند. اکسل هر شکل SUBTOTAL را طوری تعریف می‌کند که سلول‌هایی که فرمول خودشان یک SUBTOTAL دیگر است را نادیده بگیرد، پس calculator در lxCalc.pas حین aggregation مقدار FIgnoreSubtotalCells را مسلح می‌کند و از workbook، از طریق TXLSWorkbook.GetClassicIsSubtotalCell، می‌پرسد که آیا هر سلول در بازه یکی از آن‌هاست. آن callback متن فرمول را به‌صورت یک Variant می‌گرفت و با VarType(f) = varOleStr آزمایشش می‌کرد. متن از GetUnCompiledFormula به‌صورت یک String در Delphi برمی‌گردد، و یک String که به یک Variant نسبت داده شود varUString است، هرگز varOleStr. این predicate برای هر سلول در هر فایل بارگذاری‌شده نادرست بود، جمع‌های گروهی بار دوم داخل جمع کل ریخته می‌شدند، و در saveی که همه‌چیز را دوباره حساب می‌کرد، 10 + 20 + 7 شد 67

// HotXLS 2.381 و پیش‌تر: یک Variant فرمول ساخته‌شده از یک String
// مقدار varUString است، پس این مقایسه هرگز موفق نمی‌شد
Result := (VarType(f) = varOleStr) and
  (SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
   SameText(Copy(f, 1, 10), '=SUBTOTAL('));

// HotXLS 2.382.0: VarIsStr مقادیر varString و varOleStr و varUString را می‌پذیرد،
// و AGGREGATE مثل اکسل از subtotalهای محیطی مستثنا می‌شود
if VarIsStr(f) then
  Result := SameText(Copy(f, 1, 9), 'SUBTOTAL(') or
    SameText(Copy(f, 1, 10), '=SUBTOTAL(') or
    SameText(Copy(f, 1, 10), 'AGGREGATE(') or
    SameText(Copy(f, 1, 11), '=AGGREGATE(');

نسخهٔ v2.382.0 fix مربوط به VarIsStr را منتشر کرد و در همان تابع به callback یاد داد که سلول‌های AGGREGATE هم از subtotalهای محیطی مستثنا هستند. همین به‌تنهایی assertion مربوط به corpus را پاس کرد، چون 37 دوباره محاسبه‌شده حالا با 37 بارگذاری‌شده مطابقت داشت. ولی کتابخانه را صادق نکرد: save باز هم دوباره حساب می‌کرد، و تست فقط سبز بود چون evaluator تصادفاً روی همان فایل خاص با اکسل توافق داشت. قواعد مربوط به اینکه SUBTOTAL و AGGREGATE کدام سلول‌ها را رد می‌کنند، با سطرهای مخفی هم، در مقالهٔ سطرهای مخفی در SUBTOTAL و AGGREGATE پوشش داده شده؛ نکتهٔ این‌جا این است که هیچ evaluatory نباید روی فایلی که از او نخواسته‌ای حسابش کند حق رأی داشته باشد

اکسل دربارهٔ مقدارهای cache‌شده هنگام save چه تضمینی می‌دهد؟

اکسل یک save را مثل یک snapshot می‌بیند، نه یک رویداد محاسبه. مقداری که در فیلد FormulaValue یک رکورد Formula نوشته می‌شود ([MS-XLS] §2.4.127، چیدمان در §2.5.133) همانی است که سلول در آن لحظه نمایش می‌دهد، که در حالت محاسبهٔ دستی ممکن است سال‌ها کهنه باشد، و اکسل باز هم صادقانه می‌نویسدش. محاسبهٔ دوباره یک عملیات جداگانه با محرک خودش است. HotXLS حالا برای saveهای کلاسیک از همان قاعده پیروی می‌کند: WriteFormula و WriteFormulaWithTExp اول TryGetCachedFormulaValue را صدا می‌زنند، وقتی وضعیت xlfcsLoaded یا xlfcsCalculated است CacheInfo.Value را می‌گیرند، و فقط برای xlfcsMissing و xlfcsInvalidated به GetFormulaValue می‌افتند. نیمهٔ سمت-خواندن این قرارداد، شامل اینکه هر وضعیت یعنی چه و چرا یک خالی یا False cacheشده باز هم یک مقدار شمرده می‌شود، در خواندن مقدارهای cacheشدهٔ فرمول در Delphi بدون Recalculate توصیف شده

مسیر fallback عمداً نگه داشته شده، نه حذف. فرمولی که در همین نشست از طریق Cells[Row, Col].Formula assign کرده‌ای بدون cache می‌آید، و فرمولی که روی یک سلول بارگذاری‌شده جایگزین کرده‌ای توسط _SetCompiledFormula با xlfcsInvalidated علامت می‌خورد؛ هر دو موقع save دقیقاً مثل قبل ارزیابی می‌شوند، پس یک workbook تولیدشده باز هم با عدد داخلش در اکسل باز می‌شود. وقتی حتی evaluator هم نمی‌تواند مقداری تولید کند، نویسنده یک payload صفر بیرون می‌دهد و fAlwaysCalc را ست می‌کند (بیت 0 در grbit در §2.4.127) تا اکسل موقع open خودش سلول را از نو حساب کند به جای اینکه به placeholder اعتماد کند

procedure RoundTripWithoutRecalc(const Source, Target: string);
var
  Book: TXLSWorkbook;
  Before, After: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open(Source);
    // sheet و سطر و ستون بر مبنای 1: یعنی R2C4 روی اولین sheet
    if not Book.TryGetCachedFormulaValue(1, 2, 4, Before) then
      raise Exception.Create('R2C4 carries no usable cache');
    Book.SaveAs(Target);        // هیچ evaluatory برای سلول‌های cacheشده دخالت نمی‌کند
  finally
    Book.Free;
  end;

  Book := TXLSWorkbook.Create;
  try
    Book.Open(Target);
    Book.TryGetCachedFormulaValue(1, 2, 4, After);
    // Before.Value = After.Value = 37 for nested-subtotals.xls
    // یک save که دوباره حساب می‌کرد این‌جا 67 می‌نوشت
  finally
    Book.Free;
  end;
end;

سلول ریشهٔ shared formula در BIFF مقدار cacheشده‌اش را کجا نگه می‌دارد؟

در رکورد Formula خودش، مثل هر سلول فرمول‌دار دیگری، و دقیقاً همین باعث شد سلول ریشهٔ یک گروه shared formula همان جایی باشد که save کردن cache-first باز هم می‌باخت. یک shared formula در BIFF8 به‌صورت یک رکورد ShrFmla ([MS-XLS] §2.4.260) ذخیره می‌شود که بعد از رکورد Formula سلول بالا-چپ می‌آید، و هر سلول عضو، با ریشه، یک rgce حمل می‌کند که از یک توکن تکی PtgExp ساخته شده (§2.5.198): بایت اول عبارت پارس‌شده $01 است، و بعدش سطر و ستون سلول ریشه. سلول‌های پیرو خودبسنده‌اند — HotXLS FormulaValue هر کدام را می‌خواند و عبارت را با نگاه کردن به فرمول کامپایل‌شدهٔ ریشه حل می‌کند. سلول ریشه فرق دارد، چون وقتی رکورد Formulaاش پارس می‌شود عبارت هنوز وجود ندارد؛ یک رکورد بعدتر می‌رسد

همان شکاف یک-رکوردی همان جایی است که cache رفت. TXLSReader.ParseFormula مقدار cacheشده را decode می‌کند و با دیدن یک PtgExp که مختصاتش با مختصات خود سلول برابر است، سلول را در FSharedFormulaRow و FSharedFormulaCol به خاطر می‌سپارد و cache را به سلول منتشر می‌کند. وقتی رکورد ShrFmla ($04BC) می‌رسد، ParseSharedFormula عبارت را کامپایل می‌کند و با _SetCompiledFormula نصبش می‌کند، و _SetCompiledFormula همان کاری را می‌کند که برای هر تغییر فرمول باید بکند: FCachedFormulaValue را پاک می‌کند و وضعیت را به xlfcsMissing برمی‌گرداند. پس 37 بارگذاری‌شدهٔ ریشه پیش از آنکه کسی بتواند بخواندش دور انداخته می‌شد، TryGetCachedFormulaValue ریشه را بدون cache گزارش می‌کرد، و نویسندهٔ cache-first با کمال وظیفه‌شناسی برای دقیقاً همان سلولی که همه داشتند نگاهش می‌کردند به evaluator برمی‌گشت. رکورد Array (§2.4.4) همین ترتیب را دارد و همان حفره را داشت

fix در v2.382.3 یک فیلد سوم به نام FSharedFormulaCachedValue کنار مختصات ریشهٔ در انتظار اضافه می‌کند. ParseFormula وقتی ریشه‌ای را تشخیص بدهد، cache decodeشده را همان‌جا دپو می‌کند، و هر دو ParseSharedFormula و ParseArrayFormula بلافاصله بعد از نصب عبارت کامپایل‌شده آن را از طریق _SetCellCachedFormulaValue بازپخش می‌کنند و بعد دپو را به Unassigned برمی‌گردانند. نسخهٔ String این cache از همهٔ این‌ها تأثیر نمی‌گیرد چون payloadش در یک رکورد String جداگانه می‌آید و با مختصات سلول مسیریابی می‌شود، نه با ترتیب رکورد. اگر با سمت OOXML همین مفهوم کار می‌کنی، مقالهٔ گسترش si در shared formulaهای XLSX توضیح می‌دهد چرا فرمت بسته مسئلهٔ ترتیبی معادلی ندارد ولی دام‌های گسترش خودش را دارد

چرا پیروهای shared formula به یک جابه‌جایی نسبی نیاز دارند؟

چون عبارتی که در ShrFmla ذخیره می‌شود نسبت به سلول ریشه نوشته شده، و پیرویی که عیناً بازاستفاده‌اش کند ارجاع‌های ریشه را ارزیابی می‌کند نه ارجاع‌های خودش. reader قدیمی روی هر پیرو Value.GetCopy() نصب می‌کرد، یک کپی عمیق بدون هیچ جابه‌جایی، پس گروهی که ریشه‌اش B1 با =A1*3 بود به هر پیرو هم =A1*3 می‌داد. save کردن cache-first در واقع این را برای فایل‌های بارگذاری‌شده می‌پوشاند، چون پیروها FormulaValue خودشان را داشتند و برای درست save شدن هرگز به عبارت نیاز نداشتند؛ همان لحظه که چیزی دوباره محاسبه شود رو می‌شود. حالا reader TXLSCompiledFormula.GetCopy(row - srow, col - scol) را نصب می‌کند، که درخت نحو را می‌پیماید و هر ارجاع نسبی را به اندازهٔ فاصلهٔ پیرو از ریشه جابه‌جا می‌کند، پس پیرو در B2 یک =A2*3 واقعی در اختیار دارد

تست رگرسیونی که هر دو رفتار را تثبیت می‌کند ارزش خواندن دارد چون اجازه نمی‌دهد یک تصادف پاس شود. یک workbook با =A1*3 و =A2*3 روی ورودی‌های 2 و 4 می‌سازد، بعد cacheهای عمداً غلط 999 و 888 را از طریق _SetCellCachedFormulaValue تزریق می‌کند، یک بار با UseSharedFormulas روشن و یک بار خاموش. بعد از یک save و reload، هر دو سلول باید باز هم 999 و 888 گزارش کنند — اثبات این‌که save به نه cache ریشه دست زده و نه cache پیرو. فقط بعد از یک Recalculate صریح باید 6 و 12 شوند، اثبات این‌که عبارت جابه‌جاشدهٔ پیرو درست است. تستی که مقدارهای درست را می‌کاشت زیر نویسندهٔ قدیمی هم پاس می‌شد، و همین کل نکتهٔ کاشتن مقدارهای غلط است

var
  Book: TXLSWorkbook;
  Info: TXLSFormulaCacheInfo;
begin
  Book := TXLSWorkbook.Create;
  try
    Book.Open('quarterly-model.xls');
    Book.Sheets[1].Cells[1, 1].Value := 5;   // تغییر یک ورودی

    // cacheهای بارگذاری‌شدهٔ فرمول‌های وابسته با یک ویرایش لفظی
    // invalidate نمی‌شوند، پس یک SaveAs ساده اعداد قدیمی را نگه می‌داشت
    // وقتی واقعاً نتیجهٔ تازه می‌خواهی، محاسبهٔ دوباره بخواه:
    Book.Recalculate;

    if Book.TryGetCachedFormulaValue(1, 1, 2, Info) then
      Writeln('B1 now ', VarToStr(Info.Value),
        ', state ordinal ', Ord(Info.State));   // xlfcsCalculated
    Book.SaveAs('quarterly-model-updated.xls');
  finally
    Book.Free;
  end;
end;

آن‌چه قرارداد cache-first برایت انجام نمی‌دهد

save کردن cache-first آنچه بارگذاری شده را حفظ می‌کند؛ ردیابی نمی‌کند که آیا آنچه بارگذاری شده هنوز درست است. تغییر یک مقدار لفظی که فرمولی به آن وابسته است گراف وابستگی را برای evaluator کثیف می‌کند، ولی cache xlfcsLoaded سلول وابسته را سر جایش می‌گذارد، و نویسندهٔ کلاسیک با کمال میل همان مقدار کهنه را می‌نویسد مگر اینکه Recalculate را صدا بزنی یا اول Value سلول را بخوانی، که حسابش می‌کند و وضعیت را به xlfcsCalculated می‌برد. این همان معامله‌ای است که اکسل در حالت محاسبهٔ دستی می‌کند، و برای pipelineی که فایل‌های شخص ثالث را باز می‌کند و چند برچسب را ویرایش می‌کند و save می‌کند معاملهٔ درستی است — ولی یعنی workbookی که ورودی‌ها را ویرایش می‌کند باید گام محاسبهٔ دوباره‌اش را صریحاً خودش مالک باشد. سیاست RecalcBeforeSave در نویسندهٔ XLSX با این کار تغییری نکرده و حالت دستی خودش را دارد که cacheها را با همان روحیه حفظ می‌کند. دو مرز کوچک‌تر از این نتیجه می‌شود: مسیر cache-first فقط به سلول‌هایی کمک می‌کند که وضعیتشان xlfcsLoaded یا xlfcsCalculated است؛ یک generator که فرمول می‌نویسد و هرگز ارزیابی‌شان نمی‌کند باز هم موقع save برای هر سلول یک ارزیابی می‌پردازد، دقیقاً مثل قبل. و fix مربوط به subtotal تودرتو تصحیح می‌کند که evaluator کدام سلول‌ها را رد کند، نه هر تابعی که evaluator پیاده کرده — فایلی که فرمول‌هایش را HotXLS نمی‌تواند عیناً مثل اکسل حساب کند حالا در حالت دست‌نخورده امن است، ولی یک Recalculate عمدی روی آن فایل باز هم جواب کتابخانه را می‌دهد نه جواب اکسل را، و باید قبل از اعتماد به یک save دوباره محاسبه‌شده آن دو را با هم مقایسه کنی

saveهای کلاسیک cache-first و cacheهای بازگردانده‌شدهٔ ریشهٔ shared و array formula و جابه‌جایی نسبی ارجاع‌ها برای پیروهای shared و قواعد اصلاح‌شدهٔ تودرتویی SUBTOTAL و AGGREGATE همه در کامپوننت HotXLS Delphi Spreadsheet استاندارد برای Delphi و C++Builder منتشر شده‌اند، بدون هیچ وابستگی به اکسل یا هر سرور automation مربوط به OLE؛ صفحهٔ محصول مرجع کامل API را برای workbook و reader مربوط به cache و نقاط ورود محاسبهٔ دوباره که این‌جا استفاده شدند دارد