ممیزی کش فرمول‌های Excel با Deep Recalc در HotXLS

HotXLS به سؤالی جواب می‌دهد که هر خط لوله صفحه‌گسترده بالاخره مجبور است بپرسد: آیا عددهای ذخیره‌شده در یک workbook هنوز با فرمول‌هایی که تولیدشان کرده‌اند جورند. CalculateAndVerify کل گراف وابستگی را در یک overlay ایزوله محاسبه مجدد می‌کند، هر نتیجه را با مقدار کش‌شده موجود در سلول مقایسه می‌کند و ناهماهنگی‌ها را گزارش می‌دهد. به‌صورت پیش‌فرض هیچ چیزی را تغییر نمی‌دهد

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

چرا یک مقدار کش‌شده که با فرمولش مخالف است این‌قدر خطرناک است؟

چون در هر مسیر خواندن معمولی نامرئی است. فایل را در یک viewer باز کنید، سلول را از طریق API بخوانید، به CSV یا PDF اکسپورتش کنید، و همان عدد کش‌شده را می‌گیرید. فرمول همان‌جا در همان سلول نشسته، و هیچ‌کس آن‌ها را مقایسه نمی‌کند. ناهماهنگی فقط وقتی سر باز می‌کند که کسی workbook را در Excel باز کند، که زیر بیشتر تنظیمات موقع لود دوباره محاسبه می‌کند، و ناگهان گزارشی که فصل قبل امضای نهایی خورده بود جمع‌های متفاوتی نشان می‌دهد

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

var
  Book: TXLSWorkbook;
  Options: TXLSRecalcAuditOptions;
  Report: TXLSCalculationAuditReport;
  I: Integer;
begin
  Book := TXLSWorkbook.Create(nil);
  try
    Book.LoadFromFile('quarterly-close.xls');
    Options := TXLSRecalcAuditOptions.Default;
    Options.MaxIssues := 500;
    Report := Book.CalculateAndVerify(Options);
    try
      for I := 0 to Report.Count - 1 do
        if Report[I].Kind = xlcaiCacheMismatch then
          Writeln(Report[I].SheetName, '!',
                  Report[I].Row, ':', Report[I].Col, '  ',
                  Report[I].Formula,
                  '  cached=', VarToStr(Report[I].Actual),
                  '  recomputed=', VarToStr(Report[I].Expected));
      if Report.Truncated then
        Writeln('issue budget reached, raise MaxIssues');
    finally
      Report.Free;
    end;
  finally
    Book.Free;
  end;
end;

سه overload هست و هرکدام به سؤال متفاوتی جواب می‌دهند. CalculateAndVerify بدون پارامتر یک شمار ناهماهنگی برمی‌گرداند، که همه چیزِ یک health check همین است. overload با آرایه out از ناهماهنگی‌ها سلول‌ها را می‌دهد. overloadی که TXLSRecalcAuditOptions می‌گیرد یک TXLSCalculationAuditReport کامل برمی‌گرداند، که هر وقت لازم است بدانید نه فقط یک مقدار مخالف است بلکه چرا ممیزی نتوانست چیزی را ارزیابی کند، به سراغ همان می‌روید

overlay، و اینکه چرا ممیزی نمی‌نویسد

هر مقدار بازمحاسبه‌شده در یک overlay فرود می‌آید نه در کش سلول، و overlay درست در ابتدای callback خواندن سلول در هر دو موتور workbook تزریق می‌شود. همین جای‌گیری است که ممیزی را خودسازگار می‌کند: وقتی B1 بازمحاسبه می‌شود و C1 به B1 وابسته است، C1 مقدار این گذر ممیزی را می‌بیند نه مقدار کش‌شده کهنه را. بدون این، یک خطای بالادستی یک بار گزارش و بعد جذب می‌شد، و هر سلول پایین‌دستی به نظر می‌رسید با یک ورودی غلط موافقت می‌کند

سلول‌هایی که مقدار بازمحاسبه‌شده‌شان با کش می‌خواند اصلاً وارد overlay نمی‌شوند. این یک میکرو-بهینه‌سازی نیست، همان چیزی است که ممیزی را مقرون‌به‌صرفه نگه می‌دارد. یک workbook تمیز با صد هزار فرمول صفر نوشتن overlay انجام می‌دهد و گذر زیر بودجه 1.35 برابری نسبت به یک محاسبه مجدد کامل می‌ماند، که تفاوت بین چیزی است که می‌شود روی هر دریافت اجرا کرد و چیزی است که یک بار در فصل اجرا می‌شود

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

شکست‌ها طبقه‌بندی می‌شوند، در یک کوله نمی‌روند

سلولی که ممیزی نمی‌تواند ارزیابی‌اش کند یافته‌ای متفاوت از سلولی است که مقدارش مخالف است، و TXLSCalculationAuditIssueKind رده‌ها را جدا نگه می‌دارد. xlcaiCacheMismatch ناهماهنگی مقدار است. xlcaiMissingFunction و xlcaiMissingName می‌گویند ارزیاب به چیزی برخورده که پیاده نکرده یا نمی‌تواند حلش کند. xlcaiUnsupportedArguments شکل‌های آرگومان بیرون از زیرمجموعه پشتیبانی‌شده را پوشش می‌دهد. xlcaiExternalReferenceDenied و xlcaiExternalReferenceMissing یک رد سیاستی را از یک workbook غایب جدا می‌کنند. xlcaiCircularReference، xlcaiDataTableSkipped، xlcaiParseFailure، xlcaiCancelled و xlcaiInternalFailure مجموعه را کامل می‌کنند

یک تمایز ارزش بیان دارد چون یک فرض رایج را برعکس می‌کند. یک کد خطای مثبت Excel یک نتیجه است نه یک شکست. سلولی که مشروعانه به #DIV/0! ارزیابی می‌شود درست محاسبه کرده، پس ممیزی آن خطا را در overlay ذخیره می‌کند و مثل هر مقدار دیگری با کش مقایسه‌اش می‌کند. workbookی پر از سلول‌های خطای عمدی صفر یافته تولید می‌کند، و workbookی که از وقتی مقادیر کش شده یک خطا ظاهر شده یا غیب شده دقیقاً یافته‌هایی تولید می‌کند که می‌خواهید

ارجاع‌های حلقوی درمان خودشان را دارند. گره‌های درون یک cycle هرگز وارد ترتیب توپولوژیک نمی‌شوند، پس هرکدام جداگانه به‌صورت xlcaiCircularReference گزارش می‌شوند، و ممیزی حل‌کننده تکراری را اجرا نمی‌کند. این یک قرارداد عمدی فقط-خواندنی است: فعال‌بودن iteration بر اینکه کد نتیجه چطور باید تفسیر شود اثر می‌گذارد، نه بر اینکه ممیزی چه می‌کند. مکانیک ارزیابی تکراری جداگانه در محاسبه تکراری و ارجاع‌های حلقوی پوشش داده شده

خواندن یک زنجیره شکست

وقتی یک فرمول از ارزیابی شکست می‌خورد، دانستن اینکه کدام سلول شکست خورد به‌ندرت کافی است، چون شکست معمولاً سه سطح پایین‌تر در یک زنجیره ارجاع است. پس هر یافته یک رشته Stack حمل می‌کند که بیرونی‌ترین قاب اول رندر می‌شود، به شکل Sheet1!A1 > Sheet1!B2 > Data!C7، تا گزارش به سلولی اشاره کند که واقعاً شکسته نه سلولی که اتفاقاً به آن نگاه می‌کردید

ثبت‌کننده محدوددامنه است. MaxStackFrames پیش‌فرضش 64 است با کف 8، و عمیق‌ترین زنجیره شکست‌خورده همان است که نگه داشته می‌شود: یک قاب درونی زنجیره را وقتی شکست آنجا سرچشمه گرفته ثبت می‌کند، و قاب‌های بیرونی که بعداً باز می‌شوند آن را بازنویسی نمی‌کنند. اگر زنجیره‌ای از بودجه فراتر رفت، Report.StackTruncated ست می‌شود، که تفاوت بین یک زنجیره کوتاه و زنجیره‌ای را که کاملش را ندیدید می‌گوید

// پیش‌فرض فقط-خواندنی. ApplyResults فقط بعد از یک ممیزی کاملاً موفق
// overlay را commit می‌کند، زیر یک گارد نوشتن که commit را رد می‌کند
// اگر ساختار workbook حین اجرای ممیزی تغییر کرده باشد
Options := TXLSRecalcAuditOptions.Default;
Options.ApplyResults := True;
Options.AbsoluteTolerance := 0;     // مقایسه دقیق، drift را آشکار می‌کند
Options.RelativeTolerance := 0;
Options.OnProgress := HandleProgress;

Report := Book.CalculateAndVerify(Options);
try
  if Report.Applied then
    Book.SaveToFile('quarterly-close-repaired.xls')
  else
    Writeln('not applied: ', Report.Count, ' issues blocked the commit');
finally
  Report.Free;
end;

procedure THarness.HandleProgress(ASender: TObject;
  ACurrent, ATotal: Integer; var ACancel: Boolean);
begin
  ACancel := FUserRequestedStop;   // ممیزی در مرز گره بعدی می‌ایستد
end;

کی باید بگذارید ممیزی workbook را تعمیر کند؟

فقط وقتی ممیزی کاملاً بدون یافته‌های رده-شکست برگشته باشد، که دقیقاً همان شرطی است که ApplyResults به‌جای شما اعمال می‌کند. commit بعد از یک گذر کاملاً موفق و بدون cancel انجام می‌شود و از یک گارد ساختاری رد می‌شود: موتور باینری شناسه تغییر workbook را زیر نظر دارد، موتور OOXML از نسلی از ساختار به‌ازای هر کاربرگ snapshot می‌گیرد. اگر چیزی حین اجرای ممیزی جابه‌جا شده باشد، نتیجه‌ها workbookی را توصیف می‌کنند که دیگر وجود ندارد و commit رد می‌شود

به عدم‌تقارن عمدی دقت کنید. ناهماهنگی‌های کش مانع اعمال نمی‌شوند، چون دقیقاً همان چیزی‌اند که commit برای تعمیرشان آنجاست. یافته‌های رده-شکست مانع می‌شوند، چون workbookی که بعضی فرمول‌هایش ارزیابی نشدند نیمه‌تعمیر می‌شد، و workbook نیمه‌تعمیرشده بدتر از workbook تعمیرنشده‌ای است که می‌دانید باید به آن بی‌اعتماد باشید

tolerance یک تصمیم سیاستی است نه یک پیش‌فرض

مقایسه پیش‌فرض یک tolerance مطلق 1E-6 با tolerance نسبی غیرفعال است، که رفتار کلاسیک را حفظ می‌کند و بی‌سروصدا یک انحراف 4E-7 را می‌پذیرد. این معمولاً درست است: تفاوت‌های ترتیب ارزیابی ممیز شناور بین هرچه فایل را تولید کرده و ارزیاب جاری روی جمع‌های بلند تفاوت‌هایی در همین ابعاد تولید می‌کنند، و گزارش‌دادن‌شان به‌عنوان یافته یکپارچگی نویز است

هر دو tolerance را وقتی صفر کنید که سؤال فرق دارد، وقتی می‌خواهید بفهمید آیا یک ارزیاب بین نسخه‌ها رفتارش عوض شده، یا اینکه یک ابزار ثالث مقادیر را به شکلی ظریف متفاوت بازنویسی می‌کند. در صفر، همان انحراف 4E-7 پیدا می‌شود، و بقیه چیزها هم. tolerance را بر اساس سؤالی که می‌پرسید انتخاب کنید و انتخاب را کنار گزارش ثبت کنید، چون گزارشی بدون tolerance‌اش قابل تفسیر نیست

دو قابلیت همسایه تصویر را کامل می‌کنند. وقتی می‌خواهید بدانید چرا یک فرمول منفرد مقداری که می‌دهد می‌دهد، نمای گام‌به‌گام در tracer ارزیابی فرمول ابزار درست است. وقتی عمداً می‌خواهید مقادیر کش‌شده بدون هیچ بازمحاسبه‌ای محترم شمرده شوند، مثلاً روی مسیر دریافتی که باید فایل را دقیقاً همان‌طور که رسیده بازتولید کند، آن حالت در خواندن مقادیر کش‌شده فرمول بدون بازمحاسبه توصیف شده. ممیزی همان چیزی است که بین این دو می‌نشیند: به شما می‌گوید آیا اعتماد به کش امن است. همراه HotXLS Delphi spreadsheet component برای هر دو موتور باینری و OOXML عرضه می‌شود