Обчислення умовного форматування Excel у Delphi з HotXLS

HotXLS — нативний компонент для роботи з електронними таблицями для Delphi та C++Builder, і з версії 2.209.0 він може відповісти на питання, яке Excel зазвичай тримає при собі: для цієї точної клітинки, які правила умовного форматування спрацьовують, і до якої заливки, шрифту, гістограми даних чи піктограми вони розв'язуються. Ця відповідь потрібна вам у той момент, коли ваш вивід — це звіт HTML, PDF чи сітка, яку ви малюєте самі

Це інша проблема, ніж створення правил. Дві попередні нотатки охоплюють бік авторства: умовне форматування та стилі багатого тексту стосується прикріплення правил і диференційних форматів до діапазону, а партиціонування прив'язаного умовного форматування стосується того, що відбувається з діапазоном правила, коли рядки й стовпці вставляються чи видаляються. Обидві — структурні. Ця стаття про семантику: маючи книгу, що вже несе правила, обчислити підсвітку

Чому формат файлу не повідомляє, які клітинки підсвічуються

Коротка відповідь у тому, що ECMA-376 та ISO 29500-1 визначають зберігання, а не обчислення. Елемент conditionalFormatting (§18.3.1.18) несе sqref і список дочірніх cfRule (§18.3.1.10), і кожне правило несе type, опційний operator, priority, прапорець stopIfTrue, один чи два дочірні formula, а для візуальних родин — набір порогів cfvo. Кожен з них правдиво описує те, що налаштував користувач, і жоден з них не є алгоритмом. Для половини типів правил цей розрив не має значення: cellIs з operator="greaterThan" означає більше, а containsText означає, що підрядок присутній. Розрив розкривається на агрегатних родинах. Правило top10 з rank="10" та percent="1" над 27 заповненими числовими клітинками підсвічує скільки клітинок? Два цілих і сім десятих — не число. Округлити, взяти floor чи ceiling — специфікація мовчить, і обрати неправильно означає, що ваш PDF розходиться з книгою, яку клієнт має відкритою поруч

Однорядкові правила і де зупиняється TCondFormatRule.Evaluate

HotXLS спочатку взявся за дешеву половину. TCondFormatRule.Evaluate у lxCondFormat.pas, доданий у 2.199.0, відповідає, чи спрацьовує одне правило для однієї клітинки, нічого не знаючи про решту діапазону. Він обробляє вісім операторів порівняння BIFF за cellIs (between, notBetween, equal, notEqual, greater, less, greaterEqual, lessEqual), довільні правила expression, обчислені на клітинці, тож відносні посилання правильно перебазовуються, чотири текстові предикати та предикати blanks і errors. Пороги беруться з 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 з іншої причини: вони взагалі не видають булеве значення, вони видають вантаж рендерингу, і булевий тип повернення для них — неправильна форма

Як обчислювач рівня аркуша уникає повторного сканування аркуша?

Обчислюючи кожну спільну величину один раз, при конструюванні, і ніколи потім. TXLSXConditionalFormatEvaluator у lxHandleX.pas — незмінний знімок для одного аркуша, побудований через TXLSXWorksheet.CreateConditionalFormatEvaluator, і весь його дизайн — це захист від наївної реалізації, де кожна пофарбована клітинка запускає повне сканування діапазону

У конструкторі відбувається чотири речі. Кожен окремий багатоділянковий sqref розбирається рівно один раз у TXlsxCfRangeSnapshot, тож десять правил, що ділять один діапазон, ділять один розбір і один прохід статистики. Цей прохід стрімить середнє, популяційне відхилення, мінімум і максимум над заповненими клітинками за один обхід, і зберігає впорядкований числовий масив лише коли правило Top/Bottom чи процентиль справді потребує порядкової статистики. Ключі дублікатів та унікальних значень будуються Unicode-безпечно й пакетно сортуються один раз замість на кожен пошук. Потім вісь рядків розрізається на смуги на кожній межі ділянки, тож EvaluateCell виконує бінарний пошук смуги й відвідує лише правила, чиї діапазони можуть досягти цього рядка

Четверте — найважливіше в масштабі. Відносна формула правила на кшталт =A1>AVERAGE($A$1:$A$100) означає різне в кожній клітинці домену, і очевидна реалізація компілює свіже синтаксичне дерево на клітинку. TXlsxCfRulePlan компілює його один раз і повторно обчислює те саме дерево через оборотні координатні зсуви, що зберігає поведінку якорів Excel без виділення синтаксичного дерева на клітинку. Правила потім шаруються за priority, і збіг за правилом, чий StopIfTrue встановлено, розриває цикл, точнісінько як Excel коротко замикає

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 вручну побудованими книгами й читанням того, які клітинки застосунок підсвічував. HotXLS реалізує саме це: кількість рангу — Floor(Count * Min(Rank, 100) / 100), піднята до 1, коли опиняється на нулі, обрізана до заповненої кількості, а значення відсічення потім порівнюється з >=, тож кожна клітинка, рівна межі, підсвічується, навіть коли це перевищує запитану кількість. Двадцять сім значень і правило 10 відсотків підсвічують дві клітинки, плюс будь-які подальші клітинки з нічиєю з другою

Правила above-average приховували другу неоднозначність: aboveAverage з stdDev="1" обирає клітинки на одне стандартне відхилення вище середнього, але вибіркове й популяційне відхилення різняться поправкою Бесселя й помітно розходяться на малих діапазонах, а саме там і використовується умовне форматування. Excel 16 використовує популяційне відхилення, і HotXLS йому відповідає, з прапорцем equalAverage, що робить строге порівняння включним лише коли смуга відхилення не в грі. Правила дублікатів та унікальних значень натомість спираються на ідентичність ключа. Якщо одна клітинка містить число 100, а інша — текст "100", Excel трактує їх як той самий дублікатний ключ, тож HotXLS нормалізує числовий текст у числовий простір ключів замість порівняння сирих рядків. Порожні клітинки — дзеркальний випадок: справді порожня клітинка бере участь у підрахунку діапазону, але сама не стилізується, тож порожні клітинки в колонці не підсвічуються всі як дублікати одна одної

Кольорові шкали й набори піктограм: інтерполяція та граничні правила

Візуальні родини розв'язуються в готові до рендерингу числа замість булевих значень, і їхня крайова поведінка була зафіксована тим самим способом. Для кольорової шкали з явними числовими порогами HotXLS обрізає частку позиції до закритого інтервалу нуль-один, потім інтерполює по каналу з усіканням, а не округленням — значення нижче мінімуму отримує мінімальний колір, а не екстрапольований, трьохступенева шкала обирає свою пару порівнянням з середньою зупинкою, а вироджена шкала, чиї два кінці несуть той самий поріг, згортається до верхнього кольору замість ділення на нуль. Набори піктограм потребували протилежного роду обережності, бо кожен cfvo після першого несе власну строгість порівняння: HotXLS читає ThresholdEqualsInclude для кожного порогу й застосовує >= чи > відповідно, йдучи вгору так, що найвищий задоволений поріг вигравав індекс піктограми. Перевернутий набір перевертає розв'язаний індекс, а не пороги, перевизначення для окремих піктограм можуть брати гліф з іншої родини, а будь-який недійсний поріг перериває правило замість того, щоб видати правдоподібно виглядаючу неправильну піктограму

Живлення сітки, HTML-експорту та PDF одним результатом

Оскільки EvaluateCell повертає повністю розв'язаний TXLSXCfCellResult — диференційна заливка та колір шрифту з уже застосованим тонуванням теми, жирний, курсив, підкреслення, ідентифікатор формату числа, спрямовані позитивні й негативні простягання гістограми, позиція осі, родина й індекс піктограми — кожен споживач читає той самий запис, і жодному з них не потрібно розуміти внутрішні механізми правил. HotXLS використовує цей єдиний шлях для HTML-експорту, PDF-експорту та інтерактивного переглядача, що є єдиним практичним способом утримати три рендерери від розбіжності. Версія 2.210.0 підключила це до TXLSWorkbookViewer, який кешує один підготовлений обчислювач на активний аркуш і повторно використовує його при прокручуванні, виборі й перемальовуванні, звільняючи його при зміні книги чи аркуша — перебудова знімка на кожен Paint звела б нанівець весь дизайн часу конструювання. Саме тому й існує TXLSWorkbookViewer.RefreshConditionalFormats: знімок незмінний, тож якщо ви мутуєте прикріплену книгу на місці, сукупна статистика та розв'язані пороги застарілі, доки ви його не викличете

// 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, згенерованому сьогодні, і в тому, що згенерований наступного тижня, що є правильною поведінкою і все одно квитком підтримки, що чекає на свій час, якщо ваш архів має бути побайтово стабільним. Третя — граматична, а не технічна: граматика формул умовного форматування забороняє структуровані посилання на таблиці, тож правило не може адресувати стовпець таблиці за іменем так, як це робить формула аркуша, і це обмеження формату, а не реалізації

Якщо ви будуєте вивід звіту, конвеєр експорту чи власну сітку, яка має узгоджуватись з Excel клітинка в клітинку, той самий розв'язаний результат також живить власну сітку електронної таблиці VCL, описану деінде в цьому блозі. Повна документація API, модель правил і пробні завантаження для компонента електронних таблиць HotXLS Delphi доступні на сторінці продукту