ارزیابی قالب‌بندی شرطی Excel در Delphi با HotXLS

HotXLS یک کامپوننت صفحه‌گسترده بومی برای Delphi و C++Builder است، و از نسخه 2.209.0 می‌تواند به سؤالی پاسخ دهد که Excel معمولاً برای خودش نگه می‌دارد: برای این سلول دقیق، کدام قوانین قالب‌بندی شرطی فعال می‌شوند، و به چه fill، فونت، data bar یا icon حل می‌شوند. آن پاسخ دقیقاً همان چیزی است که در لحظه‌ای که خروجی شما یک گزارش HTML، یک PDF، یا یک grid که خودتان رنگ می‌کنید نیاز دارید

این مسئله‌ای متفاوت از ساختن قوانین است. دو یادداشت قبلی سمت نویسندگی را پوشش می‌دهند: قالب‌بندی شرطی و استایل‌های rich text با اتصال قوانین و فرمت‌های تفاضلی به یک بازه سروکار دارد، و پارتیشن‌بندی قالب‌بندی شرطی لنگرشده با اینکه چه اتفاقی برای بازه یک قانون هنگام درج یا حذف ردیف و ستون رخ می‌دهد سروکار دارد. هر دو ساختاری‌اند. این یکی درباره معناست: با داشتن یک workbook که از قبل قوانین حمل می‌کند، برجستگی را محاسبه کنید

چرا فرمت فایل نمی‌گوید کدام سلول‌ها روشن می‌شوند

پاسخ کوتاه این است که ECMA-376 و ISO 29500-1 ذخیره‌سازی را تعریف می‌کنند، نه ارزیابی را. یک عنصر conditionalFormatting (§18.3.1.18) یک sqref و فهرستی از فرزندان cfRule (§18.3.1.10) حمل می‌کند، و هر قانون یک type، یک operator اختیاری، یک priority، یک flag stopIfTrue، یک یا دو فرزند formula، و برای خانواده‌های بصری مجموعه‌ای از آستانه‌های cfvo حمل می‌کند. هر یک از این‌ها دقیقاً چیزی را که کاربر پیکربندی کرده توصیف می‌کند، و هیچ‌کدام یک الگوریتم نیست. برای نیمی از انواع قانون آن شکاف اهمیت ندارد: cellIs با operator="greaterThan" یعنی بزرگ‌تر از، و containsText یعنی زیررشته حاضر است. شکاف روی خانواده‌های تجمیعی باز می‌شود. یک قانون top10 با rank="10" و percent="1" روی ۲۷ سلول عددی پرشده چند سلول را برجسته می‌کند؟ دو و هفت دهم یک عدد نیست. گرد کردن، floor یا ceiling — مشخصات ساکت است، و انتخاب اشتباه یعنی PDF شما با workbookای که مشتری کنارش باز دارد اختلاف پیدا می‌کند

قوانین تک‌سلولی و جایی که TCondFormatRule.Evaluate متوقف می‌شود

HotXLS ابتدا نیمه ارزان را برداشت. TCondFormatRule.Evaluate در lxCondFormat.pas، اضافه‌شده در 2.199.0، پاسخ می‌دهد آیا یک قانون برای یک سلول فعال می‌شود بدون دانستن چیزی درباره باقی بازه. این تابع هشت عملگر مقایسه BIFF پشت cellIs (between، notBetween، equal، notEqual، greater، less، greaterEqual، lessEqual)، قوانین آزادفرم expression ارزیابی‌شده در سلول تا ارجاعات نسبی درست پایه‌مجدد شوند، چهار predicate متنی، و predicateهای خالی و خطا را مدیریت می‌کند. آستانه‌ها از FFormula1 و FFormula2 می‌آیند که از طریق TXLSCalculator.GetRangeValue در موقعیت سلول حل می‌شوند، و مرزهای معکوس‌شده به‌جای رد شدن جابه‌جا می‌شوند

var
  I: Integer;
  Rule: TCondFormatRule;
  Value: Variant;
begin
  Value := Sheet.Cells[Row, Col].Value;
  for I := 0 to CondFormat.RuleCount - 1 do
  begin
    Rule := CondFormat.Rule(I);
    // Single-cell verdict only. Aggregate and visual kinds answer False.
    if Rule.Evaluate(Calculator, SheetIndex, Row, Col, Value) then
      ApplyHighlight(Row, Col, Rule.Style);
  end;
end;

بخش صادقانه آن متد چیزی است که از حدس زدن آن امتناع می‌کند. top10، aboveAverage، belowAverage، duplicateValues و uniqueValues False برمی‌گردانند، نه چون سخت‌اند بلکه چون از یک سلول واحد غیرقابل‌تصمیم‌گیری‌اند — هر یک به یک آمار روی کل دامنه نیاز دارد. چهار خانواده بصری، dataBar، colorScale2، colorScale3 و iconSet، به دلیلی متفاوت False برمی‌گردانند: آن‌ها اصلاً یک boolean تولید نمی‌کنند، یک payload رندر تولید می‌کنند، و یک نوع بازگشتی Boolean شکل اشتباهی برایشان است

یک ارزیاب سطح کاربرگ چگونه از اسکن مجدد صفحه اجتناب می‌کند؟

با محاسبه هر مقدار مشترک یک‌بار، در زمان ساخت، و هرگز دوباره. TXLSXConditionalFormatEvaluator در lxHandleX.pas یک snapshot تغییرناپذیر برای یک کاربرگ است، ساخته‌شده از طریق TXLSXWorksheet.CreateConditionalFormatEvaluator، و کل طراحی آن یک دفاع در برابر پیاده‌سازی ساده‌لوحانه‌ای است که در آن هر سلول رنگ‌شده یک اسکن کامل بازه را ماشه می‌کند

چهار چیز در constructor رخ می‌دهد. هر sqref چندناحیه‌ای متمایز دقیقاً یک‌بار در یک TXlsxCfRangeSnapshot parse می‌شود، پس ده قانون که یک بازه را به اشتراک می‌گذارند یک parse و یک پاس آمار را به اشتراک می‌گذارند. آن پاس میانگین، انحراف جمعیت، کمینه و بیشینه را روی سلول‌های پرشده در یک پیمایش واحد جریان می‌دهد، و فقط یک آرایه عددی مرتب‌شده را نگه می‌دارد وقتی یک قانون Top/Bottom یا صدک واقعاً به آمار مرتبه نیاز دارد. کلیدهای تکراری و یکتا Unicode-safe ساخته و یک‌بار به‌صورت دسته‌ای مرتب می‌شوند به‌جای هر جستجو. سپس محور ردیف به باندهایی در هر مرز ناحیه بریده می‌شود، پس EvaluateCell یک باند را binary-search می‌کند و فقط قوانینی که بازه‌هایشان ممکن است به آن ردیف برسد را بازدید می‌کند

چهارمی در مقیاس بزرگ بیشترین اهمیت را دارد. یک فرمول قانون نسبی مانند =A1>AVERAGE($A$1:$A$100) در هر سلول از دامنه چیز متفاوتی معنا می‌دهد، و پیاده‌سازی بدیهی به‌ازای هر سلول یک درخت syntax تازه کامپایل می‌کند. TXlsxCfRulePlan آن را یک‌بار کامپایل می‌کند و همان درخت را از طریق offsetهای مختصات برگشت‌پذیر دوباره ارزیابی می‌کند، که رفتار anchor اکسل را بدون یک تخصیص درخت syntax به‌ازای هر سلول حفظ می‌کند. قوانین سپس با priority لایه‌بندی می‌شوند، و یک تطبیق روی قانونی که StopIfTrue آن تنظیم شده حلقه را می‌شکند، دقیقاً همان‌طور که Excel short-circuit می‌کند

var
  Evaluator: TXLSXConditionalFormatEvaluator;
  Res: TXLSXCfCellResult;
begin
  Evaluator := Sheet.CreateConditionalFormatEvaluator;
  try
    if Evaluator.EvaluateCell(Row, Col, Res) then
    begin
      if Res.HasFillColor then
        Canvas.Brush.Color := TColor(Res.FillColor);
      if Res.HasIcon then
        // IconIndex is zero-based inside Res.IconSetType
        DrawIcon(Res.IconSetType, Res.IconIndex, Res.IconCount);
      if Res.HasDataBar then
        // DataBarAxis and DataBarEnd are normalised to 0..1
        DrawBar(Res.DataBarAxis, Res.DataBarEnd, Res.DataBarColor);
      if not Res.ShowCellValue then
        Exit;  // showValue="0" on the rule hides the number
    end;
  finally
    Evaluator.Free;
  end;
end;

Excel واقعاً چگونه یک قانون Top 10 percent را گرد می‌کند؟

floor می‌کند، با کمینه یک، و برابری‌ها را در نقطه برش شامل می‌کند. این جایی در ISO 29500-1 نوشته نشده — با کاوش Excel 16 با workbookهای دست‌ساز و بازخوانی اینکه برنامه کدام سلول‌ها را برجسته کرده پین شده. HotXLS دقیقاً همان را پیاده‌سازی می‌کند: تعداد rank برابر Floor(Count * Min(Rank, 100) / 100) است، به ۱ ارتقا می‌یابد وقتی روی صفر فرود بیاید، به تعداد پرشده clamp می‌شود، و سپس مقدار برش با >= مقایسه می‌شود پس هر سلول برابر با مرز برجسته می‌شود حتی وقتی این از تعداد درخواستی فراتر رود. بیست‌وهفت مقدار و یک قانون ۱۰ درصد دو سلول را برجسته می‌کند، به‌علاوه هر سلول بیشتری که با دومی برابر باشد

قوانین above-average یک ابهام دوم را پنهان می‌کردند: aboveAverage با stdDev="1" سلول‌های یک انحراف استاندارد بالاتر از میانگین را انتخاب می‌کند، اما انحراف نمونه و جمعیت با تصحیح Bessel متفاوت‌اند و روی بازه‌های کوچک محسوس اختلاف دارند، که دقیقاً همان‌جایی است که قالب‌بندی شرطی استفاده می‌شود. Excel 16 از انحراف جمعیت استفاده می‌کند، و HotXLS با آن مطابقت دارد، با flag equalAverage که مقایسه سخت‌گیرانه را فقط وقتی هیچ باند انحرافی در کار نیست شامل می‌کند. قوانین duplicate و unique روی هویت کلید تکیه می‌کنند. اگر یک سلول عدد 100 را نگه دارد و دیگری متن "100" را، Excel آن‌ها را به‌عنوان همان کلید تکراری در نظر می‌گیرد، پس HotXLS متن عددی را به فضای کلید عددی نرمال می‌کند به‌جای مقایسه رشته‌های خام. سلول‌های خالی حالت آینه هستند: یک سلول واقعاً خالی در شمارش بازه مشارکت می‌کند اما خودش استایل نمی‌گیرد، پس سلول‌های خالی یک ستون همه به‌عنوان تکراری یکدیگر روشن نمی‌شوند

مقیاس‌های رنگی و مجموعه‌های icon: قوانین درون‌یابی و مرزی

خانواده‌های بصری به اعداد آماده-رندر حل می‌شوند نه booleanها، و رفتار مرزی آن‌ها به همان روش پین شد. برای یک مقیاس رنگی با آستانه‌های عددی صریح، HotXLS کسر موقعیت را به بازه بسته صفر تا یک clamp می‌کند، سپس به‌ازای هر کانال با بریدن به‌جای گرد کردن درون‌یابی می‌کند — مقداری زیر توقف کمینه رنگ کمینه را می‌گیرد نه یک رنگ برون‌یابی‌شده، یک مقیاس سه‌توقف جفت خود را با مقایسه در برابر توقف میانه انتخاب می‌کند، و یک مقیاس تباهیده که دو انتهایش همان آستانه را حمل می‌کند به‌جای تقسیم بر صفر به رنگ بالا فروپاشی می‌کند. مجموعه‌های icon به نوع مراقبت مخالف نیاز داشتند، چون هر cfvo پس از اولی سخت‌گیری مقایسه خودش را حمل می‌کند: HotXLS ThresholdEqualsInclude را به‌ازای هر آستانه می‌خواند و >= یا > را طبق آن اعمال می‌کند، به‌سمت بالا پیمایش می‌کند پس بالاترین آستانه برآورده‌شده اندیس icon را می‌برد. یک مجموعه معکوس‌شده اندیس حل‌شده را برمی‌گرداند نه آستانه‌ها را، بازنویسی‌های به‌ازای هر icon می‌توانند یک نماد را از خانواده متفاوتی بگیرند، و هر آستانه نامعتبر قانون را لغو می‌کند به‌جای تولید یک icon اشتباه اما قابل‌قبول‌به‌نظر

تغذیه یک grid، یک export HTML و یک PDF از یک نتیجه

چون EvaluateCell یک TXLSXCfCellResult کاملاً حل‌شده برمی‌گرداند — رنگ fill و فونت تفاضلی با tint theme از قبل اعمال‌شده، bold، italic، underline، شناسه فرمت عدد، اندازه‌های bar مثبت و منفی جهت‌دار، موقعیت axis، خانواده و اندیس icon — هر مصرف‌کننده همان رکورد را می‌خواند و هیچ‌کدام لازم نیست جزئیات داخلی قانون را بفهمد. HotXLS از آن یک مسیر برای export HTML، export PDF و viewer تعاملی استفاده می‌کند، که تنها راه عملی برای جلوگیری از واگرایی سه رندرکننده از هم است. نسخه 2.210.0 آن را به TXLSWorkbookViewer وصل کرد، که یک ارزیاب آماده‌شده به‌ازای هر کاربرگ فعال را کش می‌کند و آن را در سراسر اسکرول، انتخاب و repaint دوباره استفاده می‌کند، و آن را وقتی workbook یا کاربرگ تغییر می‌کند آزاد می‌کند — بازسازی snapshot در هر Paint کل طراحی زمان-ساخت را شکست می‌داد. آن کش همچنین دلیل وجود TXLSWorkbookViewer.RefreshConditionalFormats است: snapshot تغییرناپذیر است، پس اگر workbook متصل را در جا تغییر دهید آمار تجمیعی و آستانه‌های حل‌شده تا زمانی که آن را فراخوانی نکنید کهنه می‌مانند

// Editing behind a live viewer: the cached snapshot must be invalidated.
Sheet := Viewer.XlsxWorkbook.Sheets[1];
Sheet.Cells[5, 2].Value := 4200;         // changes mean, min, max, ranking
Viewer.RefreshConditionalFormats;        // drop evaluator, repaint

چیزی که ارزیاب برای شما انجام نمی‌دهد

سه مرز ارزش گفتن صریح دارند. TCondFormatRule.Evaluate تک‌سلولی کلاسیک و TXLSXConditionalFormatEvaluator سطح کاربرگ سطوح متفاوتی با قابلیت‌های متفاوت هستند، و تک‌سلولی عمداً از خانواده‌های تجمیعی و بصری امتناع می‌کند به‌جای تقریب آن‌ها — اگر به Top/Bottom یا یک مقیاس رنگی نیاز دارید، ارزیاب را بسازید. دوره‌های تاریخ نسبی به ساعت ماشین در زمان ارزیابی وابسته‌اند، پس یک قانون timePeriod در یک PDF تولیدشده امروز و یکی که هفته بعد تولید می‌شود متفاوت رندر می‌شود، که رفتار درستی است و همچنان یک تیکت پشتیبانی در انتظار وقوع است اگر انتظار دارید آرشیو شما بایت-پایدار باشد. سومی گرامری است نه فنی: گرامر فرمول قالب‌بندی-شرطی ارجاعات جدول ساختاریافته را ممنوع می‌کند، پس یک قانون نمی‌تواند یک ستون جدول را با نام به‌همان‌روشی که یک فرمول کاربرگ می‌تواند آدرس‌دهی کند، و آن یک محدودیت فرمت است نه پیاده‌سازی

اگر دارید خروجی گزارش، یک pipeline export یا یک grid سفارشی که باید سلول‌به‌سلول با Excel مطابقت داشته باشد می‌سازید، همان نتیجه حل‌شده همچنین grid صفحه‌گسترده VCL سفارشی که در جای دیگری از این وبلاگ شرح داده شده را می‌راند. مستندات کامل API، مدل قانون و دانلودهای trial برای کامپوننت صفحه‌گسترده HotXLS Delphi روی صفحه محصول در دسترس‌اند