Условно форматиране, Rich Text и стилове на клетки в Delphi с HotXLS

Условното форматиране в OOXML се състои от две отделни части. Условието (сравнение, формула или съвпадение) определя кои клетки се класифицират. Външният вид (диференциален стил, или dxf съгласно стандарта ECMA-376) определя как изглеждат те. Диалоговият прозорец на Excel скрива това разделение, като ви кара да попълните и двете едновременно. HotXLS обаче изисква изричното им задаване. Ако създадете правило cellIs от Delphi и пропуснете стила, правилото ще бъде валидно, диапазонът ще е коректен, формулата ще се изчислява правилно, но клетките няма да променят цвета си, тъй като инструкцията е била „при истина, не променяй нищоâ€? Разликата между условието и резултата обяснява защо някои правила изглеждат правилно конфигурирани, но не маркират нищо.

HotXLS записва условно форматиране директно в BIFF8 .xls и OOXML .xlsx файлове, като поддържа също форматиран текст (rich text runs) и споделени стилове за клетки. Тези функции споделят обща логическа структура и несъответствията обикновено възникват в точките на свързване между тях.

Условието изисква стил: dxf формат

В XLSX работния лист сравнителните правила се създават чрез AddConditionalFormat, който приема диапазон, оператор от TXLSXCfOperator и формула или литерал, и връща индекса на новото правило в колекцията ConditionalFormats. Обектът на правилото на съответния индекс съдържа свойството Style, което определя оцветяването. Задайте цвят за запълване и клетките ще го приемат; оставете го празен и ще получите невидимото правило, описано по-горе.

var

  Book: TXLSXWorkbook;

  Sheet: TXLSXWorksheet;

  Idx: Integer;

begin

  Book := TXLSXWorkbook.Create;

  try

    Book.Open('kpi.xlsx');

    Sheet := Book.Sheets[0];



    // Negative variance: light red fill

    Idx := Sheet.AddConditionalFormat('D2:D200', xlsxCfOpLessThan, '0');

    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);



    // Duplicate order IDs get flagged the same way

    Idx := Sheet.AddCondFormatDuplicateValues('A2:A200');

    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFEB9C);



    // Custom formula rule: highlight rows where actual misses 90% of target

    Idx := Sheet.AddCondFormatExpression('B2:B200', '$C2<$B2*0.9');

    Sheet.ConditionalFormats[Idx].Style.SetFillBgColor($FFFFC7CE);



    Book.SaveAs('kpi-flagged.xlsx');

  finally

    Book.Free;

  end;

end;

Цветовете тук са 32-битови ARGB стойности, така че $FFFFC7CE съответства на познатия от Excel светлочервен цвят за маркиране, с непрозрачен алфа байт. Всеки тип правило, което се задейства при условие за клетка, следва същата логика на създаване и последващо стилизиране. Правилата за съвпадение на текст (AddCondFormatContainsText, AddCondFormatBeginsWith, AddCondFormatEndsWith) връщат индекс, който се форматира допълнително, както и методите AddCondFormatTop10, AddCondFormatAboveAverage или тези за откриване на празни клетки и грешки.

Лентите с данни, цветовите скали и иконите се оформят сами

Визуалните правила работят по обратния начин. Те съдържат информацията за външния си вид в самото си определение и игнорират свойството Style. Задаването на запълване на правило за лента с данни (data bar) няма ефект, тъй като AddCondFormatDataBar приема цвета на лентата като директен аргумент. Цветовите скали с две и три точки приемат цветовете за крайните точки, а AddCondFormatIconSet избира един от 26 типа набори от икони (например icsTrafficLights3). Тук няма отделен стил, който да бъде пропуснат.

Важен параметър при тези извиквания са крайните стойности, представени от TXLSCfValueKind. Крайната точка на лентата или скалата може да бъде минимумът или максимумът на диапазона, конкретно число, процент, персентил или резултат от формула. Стойностите по подразбиране (минимум и максимум на диапазона) работят добре при тестови данни, но водят до грешки при реални данни с големи отклонения, една извънредно голяма стойност свива останалите ленти до малки черти. Когато таблото трябва да се сравнява в различни периоди, задайте крайните точки към фиксирани числа или персентили, за да може половин лента през март да означава същото количество като половин лента през април. Автоматично мащабираната лента е съпоставима само със себе си.

Записът на XLS поддържа само четири типа правила

Традиционният формат BIFF8 поддържа само част от тези функции. XLS фасадата може да създава точно четири типа условни правила: ленти с данни, двуцветни скали, трицветни скали и набори от икони, записани като CF12 записи. Тя няма API за създаване на правила от тип cellIs, формула или съвпадение на текст. Вече съществуващите правила от тези видове в отворен файл се запазват и записват без промяна, така че презаписването на клиентски .xls файл не поврежда форматирането му. Не можете обаче да генерирате правила за съпоставка на прагове от нулата в .xls. Решението е да ги симулирате чрез обикновено запълване на клетки в Pascal кода или да изберете формат .xlsx, където цялата гама от правила е налична.

Това ограничение трябва да се вземе предвид при избора на файлов формат, особено за отчети тип информационни табла. Изборът на .xls за съвместимост и едновременното изискване за сложни прагове за маркиране са несъвместими решения, което е по-добре да се разбере в началото на разработката.

Подреждане, приоритет и застъпващи се диапазони на правилата

Реалните отчети често съдържат повече от едно правило за даден диапазон. Колона с отклонения може да съдържа лента с данни за размер, правило cellIs за твърд праг и формула на ниво ред за ескалации. Всеки обект TXLSXConditionalFormat има свойство Priority и Excel прилага правилата в този ред. Когато две правила си оспорват оцветяването на една клетка, победителят се определя от зададеното число, а не от подредбата им в прозореца за управление на правилата.

Управлявайте приоритетите по същия начин, по който управлявате слоевете (z-order) в графична програма. Задавайте ги съзнателно при възможно застъпване на правила и оставяйте свободни номера между стойностите за бъдещи промени. Когато правилата не се застъпват (например лента в колона E и текст в колона G), редът на създаване е напълно достатъчен. Обърнете по-голямо внимание на границите на диапазоните, опасните грешки са свързани с диапазони като B2:B200 на отчет, който е нараснал до 350 реда, където допълнителните редове остават без форматиране и изглеждат като грешно попълнени. Генерирайте обхвата на правилата на базата на крайния брой редове, както правите за сериите на диаграмите и диапазоните за валидиране.

Полезен навик при разработка е проверката на генерирания файл в Excel: изберете форматирания диапазон и прегледайте правилата в прозореца Manage Rules. Условното форматиране се изобразява окончателно от приложението, което чете файла, така че успешният запис на XML структурата не гарантира автоматично правилно визуализиране в Excel. Един бърз поглед спестява бъдещи корекции.

Rich Text: множество формати в една клетка

Клетка с форматиран текст (rich-text) в модела на XLSX съдържа списък от сегменти (runs), всеки от които има собствен текст и шрифтови атрибути. Изграждате този списък като обект TXLSXRichText, добавяте сегменти към него и го присвоявате на клетката. Важно е да знаете, че присвояването на Cell.RichText прехвърля собствеността на обекта към клетката и тя го освобождава автоматично при своето унищожаване. Ако го освободите и ръчно, ще предизвикате грешка за двойно освобождаване (double-free), която често се проявява като срив на съвсем друго място по-късно.

var

  Rich: TXLSXRichText;

  Run: TXLSXRichTextRun;

begin

  Rich := TXLSXRichText.Create;

  Rich.AddRunText('Status: ');

  Run := Rich.AddRunText('OVERDUE');

  Run.Bold := True;

  Run.Color := $FFC00000;

  Run.ColorIsAuto := False;

  Run := Rich.AddRunText(' (escalated to regional manager)');

  Run.Italic := True;

  Sheet.Cells[2, 7].RichText := Rich;   // ownership moves to the cell: do not Free

end;

Инструкцията ColorIsAuto := False е задолжителна. Сегментът съдържа флаг за автоматичен цвят и присвояването на конкретен цвят се взема предвид само след изчистването на този флаг. Ако пропуснете ColorIsAuto, текстът ще се покаже удебелен, но твърдо черен. Сегментите поддържат също зачеркнат текст, различни видове подчертаване и вертикално подравняване за горен и долен индекс, а свойството PlainText обединява всички сегменти в един чист низ, когато имате нужда от експорт или сравнение.

Форматираният текст на ниво клетка се поддържа само в XLSX. XLS фасадата няма публичен API за директното му записване (въпреки че се поддържа за коментари и текстови полета през TextRuns), а форматираните низове, прочетени от съществуващ .xls файл, се запазват при обратно записване. Всичко, което изисква смесено форматиране в една клетка, трябва да се насочи към XLSX.

Стиловият пул и отместването с единица

Стилизирането на клетки в XLSX модела се управлява чрез споделени колекции на ниво работна книга. Методите Fonts.Add, Fills.AddSolid и Borders.Add регистрират съответните дефиниции и връщат техния индекс в пула, базиран на 0. Свойствата на клетките, които ги ползват (например FontIndex), обаче запазват стойността 0 за стила по подразбиране, поради което присвояваната стойност трябва да бъде индексът от пула, увеличен с единица:

HeaderFont := Book.Fonts.Add('Calibri', 11, True, False);  // pool index, 0-based

for Col := 1 to 6 do

  Sheet.Cells[1, Col].FontIndex := HeaderFont + 1;          // cell index, 1-based

Ако пропуснете + 1, заглавната част ще се покаже с шрифта по подразбиране без съобщение за грешка. Друга грешка е извикването на Fonts.Add за всеки отделен ред. Идентичните шрифтове се дедупликират автоматично, но това хаби ресурси, а колекцията за подравняване връща нов обект при всяко извикване. Създайте необходимите стилове веднъж преди цикъла и преизползвайте индексите им. При големи отчети това е важен фактор за производителността, разгледан в настройки за производителност на големи работни книги в HotXLS. Когато имате нужда от стандартен облик, можете да ползвате ApplyBuiltinStyle за диапазони, който прилага стиловете на Excel (Good, Bad, Neutral и др.) без работа с пуловете.

Условното форматиране, Rich Text и стиловете са финалната стъпка от оформянето на отчета. Предходните етапи са описани в генериране на отчети по шаблони с HotXLS. Пълното описание на правилата, сегментите и стиловете е достъпно на продуктовата страница за HotXLS Component.