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, оценявани при клетката, така че относителните референции да се пребазират правилно, четирите текстови предиката и предикатите за празни и грешки. Праговете идват от 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 по различна причина: те изобщо никога не произвеждат булева стойност, произвеждат payload за рендиране, а булев тип на връщане е грешната форма за тях
Как оценител на ниво работен лист избягва повторно сканиране на листа?
Като изчислява всяко споделено количество веднъж, при конструирането, и никога отново. 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?
Закръгля надолу, с минимум едно, и включва равенства при прага. Това не е записано никъде в 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, генериран днес, и такъв, генериран следващата седмица, което е правилно поведение и все пак тикет за поддръжка, чакащ да се случи, ако архивът ви се очаква да е байт стабилен. Третото е граматично, а не техническо: граматиката на формулите за условно форматиране забранява структурирани референции към таблица, така че правило не може да адресира колона на таблица по име по начина, по който формула на работен лист може, а това е ограничение на формата, не на имплементацията
Ако изграждате изход за отчети, pipeline за експорт или персонализирана мрежа, която трябва да съвпада с Excel клетка по клетка, същият разрешен резултат задвижва и персонализираната VCL мрежа за електронни таблици, описана другаде в този блог. Пълна документация на API-то, моделът на правилата и пробни версии за изтегляне за HotXLS Delphi компонент за електронни таблици са налични на страницата на продукта