Условное форматирование: акценты в данных

Q: Почему Format в правиле требует именно NewConditionalStyle?

Условное форматирование ведёт отдельную таблицу дифференциальных стилей: идентификатор из NewStyle указывает в обычную таблицу и в правиле не применяется. Создайте стиль через NewConditionalStyle и передайте в Format по адресу — Format: &styleID.

Q: Можно ли прочитать файл и узнать, какие ячейки красные?

Напрямую нет: правила в файле есть, а раскраску выполняет Excel, и excelize не вычисляет, какие ячейки под правило попадают. GetConditionalFormats отдаст список правил с диапазонами — логику условия придётся пересчитать в Go по значениям.

Q: Правило и обычный стиль на одной ячейке — кто победит?

При выполнении условия поверх обычного оформления показывается условный стиль, а вне условия ячейка возвращается к обычному. Поэтому зебру и форматы чисел держат обычным стилем, а акценты отдают условному форматированию — они не спорят за одни и те же поля.

Стили из седьмого урока красят безоговорочно: диапазон получил заливку — заливка там стоит, пока её не заменят. Для акцентов в данных это неудобно: колонка из двадцати строк с тремя отрицательными значениями либо красится вся, либо по ячейкам в цикле, либо Go считает условие и красит выборочно — третий вариант из урока про зебру работает, но цвет прибит к данным на момент генерации. Пользователь правит число с минусом на положительное, а красная заливка остаётся: файл смотрит на вас враньём, и рано или поздно кто-то принимает решение по устаревшему цвету. Условное форматирование переворачивает механику: в файл кладётся правило вида «крась B2:B21 красным там, где значение меньше нуля», и Excel пересматривает его при каждом открытии и каждой правке. Правило переживает правки данных, потому что смотрит не на снимок, а на условие, и одна строка кода заменяет цикл с проверками. В этом уроке — NewConditionalStyle, SetConditionalFormat, цветовые шкалы и полосы данных, чтение и снятие правил, а также вопрос, который встаёт на второй день использования: где на самом деле живёт цвет и кто его наносит.

SetConditionalFormat: правила условного форматирования excelize#

Правило собирается из двух частей. Первая — стиль: f.NewConditionalStyle(&excelize.Style{...}) описывает оформление по тем же Font, Fill и Alignment, что и обычный NewStyle, и возвращает пару (int, error) — идентификатор в специальной таблице дифференциальных стилей. Вторая — SetConditionalFormat с тремя аргументами: лист, диапазон и срез правил ConditionalFormatOptions. Каждое правило — структура с полями Type, Criteria, Value и Format: Type "cell_is" означает сравнение значения ячейки, Criteria — знак вроде "<" или ">=", Value — порог строкой, а Format — указатель на идентификатор стиля из NewConditionalStyle. Диапазон пишется в A1-нотации: "B2:B21" накроет колонку данных, шапку лучше не включать — правило посчитает и заголовок. На один диапазон можно повесить несколько правил: красное для минуса и жёлтое для нуля живут в одном срезе и срабатывают независимо. Запрос conditional formatting go на стороне excelize всегда выглядит этой связкой: условный стиль — затем срез правил на диапазон.

Красим отрицательные остатки

negative, err := f.NewConditionalStyle(&excelize.Style{
    Font: &excelize.Font{Color: "#9A0404"},
    Fill: excelize.Fill{Type: "pattern", Pattern: 1, Color: []string{"#FDE7E7"}},
})
if err != nil {
    panic(err)
}

if err := f.SetConditionalFormat("Отчёт", "C2:C21", []excelize.ConditionalFormatOptions{
    {Type: "cell_is", Criteria: "<", Value: "0", Format: &negative},
}); err != nil {
    panic(err)
}

Стиль negative описан обычными Font и Fill, правило cell_is с Criteria меньше нуля прикладывает его к попавшим ячейкам колонки C. Обратите внимание на Format: поле принимает указатель, поэтому идентификатор берут по адресу — Format: &negative.

Стиль нужен условный, а не обычный

Идентификатор из NewStyle в поле Format правила не работает: условное форматирование ведёт свою таблицу стилей, и стиль для него создаёт только NewConditionalStyle. Вторая ловушка — тип поля: Format в ConditionalFormatOptions принимает *int, поэтому в коде пишут Format: &styleID, а не Format: styleID. И третья: правила ссылаются на диапазон строкой, и опечатка в адресе не вернёт ошибку — правило просто ничего не покрасит.

Цветовые шкалы: color_scale и пороги#

Сравнение по порогу — не единственный тип правил, и для рядов вроде выручки по дням он грубоват: один максимум и всё остальное. Color_scale растягивает градиент по значениям диапазона: у правила задают MinType, MidType и MaxType (минимум, середина и максимум) и три цвета MinColor, MidColor, MaxColor. Типы порогов бывают num (число), percent (процент диапазона) и percentile (процентиль) — шкала без MidType превращается в двухцветную, с ним — в классический красный-жёлтый-зелёный светофор данных. Цвета берут с решёткой и в hex, как в обычных стилях: #63BE7B для хорошего, #F8696B для плохого — палитра светофора узнаваема глазами бухгалтерии: этой тройкой цветов Excel красит условные шкалы по умолчанию. Полосы данных, Type "data_bar", рисуют внутри ячейки горизонтальный бар с цветом BarColor: длина пропорциональна значению, и таблица обзаводится мини-графиком без одной диаграммы. Наборы иконок, Type "icon_set", вешают на ячейку стрелки или светофоры по порогам — для статусов заказов этого хватает чаще, чем кажется. Все эти виды объединяет одно: excelize пишет правило в файл, а визуализацию выполняет Excel при рендере.

Шкала и полоса данных

if err := f.SetConditionalFormat("Отчёт", "D2:D21", []excelize.ConditionalFormatOptions{
    {
        Type:     "color_scale",
        MinType:  "min",
        MidType:  "percentile",
        MaxType:  "max",
        MinColor: "#F8696B",
        MidColor: "#FFEB84",
        MaxColor: "#63BE7B",
    },
}); err != nil {
    panic(err)
}

if err := f.SetConditionalFormat("Отчёт", "E2:E21", []excelize.ConditionalFormatOptions{
    {Type: "data_bar", MinType: "min", MaxType: "max", BarColor: "#638EC6"},
}); err != nil {
    panic(err)
}

Две колонки получили разные правила: D — трёхцветная шкала с серединой на 50-м процентиле, E — полоса данных с синим баром. MidType percentile делит диапазон по рангу значений, а не по числу — выброс справа не сместит середину градиента.

Правила поверх стилей: кто кого перекрасит#

Условное форматирование лежит поверх обычного: ячейка со стилем из NewStyle, попав под условие, покажет условное оформление, а после выхода за условие вернётся к обычному. Именно в этом отличие от краски из седьмого урока: заливка в цикле прибита к данным на момент генерации и не реагирует на правки, правило же пересматривается при каждом открытии файла. Отсюда практическое разделение труда: базу оформляет обычный стиль — шрифт, зебра, NumFmt из восьмого урока; акценты держит условное форматирование, и правки данных их не ломают. Один нюанс стоит помнить при наложении: условный стиль перекрывает только те свойства, которые описал, и если в нём нет NumFmt, формат числа останется от обычного стиля — слои дополняют друг друга, а не затирают целиком, как SetCellStyle.

Чтение и снятие правил: GetConditionalFormats#

Живые правила читаются через GetConditionalFormats: метод вернёт карту диапазонов со списком правил, и по чужому файлу можно понять, что именно там подсвечивается — без открытия файла глазами. Снимается правило UnsetConditionalFormat с тем же листом и диапазоном: приём для перегенерации — старые правила снимают, новые кладут, и файл не копит мусор от повторных прогонов генератора. Для парсера данных раскраска безразлична: GetCellValue отдаёт значения, и никакие правила на текст ответа не влияют — условное форматирование excelize видит только на стороне Excel, как и положено правилу отображения. Если машине нужно отличить плохие значения от хороших, дублируйте условие в Go: правило для человека, тот же порог в коде для машины — иначе раскраска и данные разъедутся по разным интерпретациям.

Типы правил ConditionalFormatOptions#

Type	Ключевые поля	Для чего берут
cell_is	Criteria, Value, Format	порог: отрицательные, нули, выход за план
color_scale	MinType, MidType, MaxType, цвета	градиент по ряду значений
data_bar	MinType, MaxType, BarColor	полосы внутри ячеек: доля и объём
icon_set	IconSet вроде 3TrafficLights1	статусы стрелками и светофорами
top_10	Value — сколько позиций	топ значений диапазона
above_average	Format	выше среднего по диапазону

Цвет живёт на стороне Excel

В xlsx хранятся правила и условные стили, а раскраску выполняет Excel при рендере: excelize не умеет ни вычислить, какие ячейки покрасятся, ни отдать цвет пикселем. Скриншот листа библиотека не сделает; для картинко-отчётов это значит, что условное форматирование честно работает только на экране пользователя.

Чек-лист условного форматирования#

NewConditionalStyle создаёт условный стиль: Format в правиле ждёт указатель на его идентификатор.
SetConditionalFormat принимает лист, диапазон и срез правил; на диапазон можно повесить несколько.
cell_is — порог, color_scale — градиент, data_bar — полосы, icon_set — стрелки и светофоры.
Шапку в диапазон правила не включают: заголовок попадёт под условие наравне с данными.
GetConditionalFormats читает правила, UnsetConditionalFormat снимает по диапазону.
Правила живут в файле, красит Excel: значения GetCellValue от раскраски не меняются.

В песочнице файлов нет

NewConditionalStyle и SetConditionalFormat требуют книги на диске, поэтому в браузерной песочнице не запускаются: там доступны fmt, strings, strconv, math, sort, errors и time без файлового ввода-вывода. Логику порогов потренировать можно: отсортируйте срез чисел sort'ом, посчитайте среднее math'ем и напечатайте, какие элементы попали под условие — это и есть то, что Excel проверит сам.

Кейс про склад, который красили в цикле. Отчёт остатков собирался на тридцать тысяч позиций, и отрицательные строки красились обычным стилем в цикле: Go проверял число и прикладывал красный через SetCellStyle. Выглядело хорошо до первой правки руками: кладовщик исправил минус, отправил файл наверх — и красная строка осталась, потому что заливка была константой. Переход на условное форматирование занял полдня: цикл с условием выбросили, один условный стиль и одно правило cell_is на весь диапазон закрыли вопрос: правило пересчитывается при каждом открытии, поэтому правки больше не ломают раскраску. Вторым заходом добавили цветовую шкалу на колонку дней просрочки: чем дольше долг, тем краснее ячейка, и сортировка по колонке стала необязательной. Спорная сторона осталась в версии для загрузчика в базу: машине раскраска безразлична, а файл с правилами стал на пару килобайт тяжелее, поэтому для машинной выгрузки правила не ставят.

Проверка: идентификатор для правила

Какой идентификатор ожидает поле Format в ConditionalFormatOptions?

Проверка: кто раскрашивает

После SetConditionalFormat в свежем файле что произойдёт, когда пользователь откроет его в Excel?

Что вернёт чтение из покрашенной колонки?

Прочитайте код и выберите, что он выведет:

f.SetCellValue("Sheet1", "B2", -150)

f.SetConditionalFormat("Sheet1", "B2:B21", []excelize.ConditionalFormatOptions{
    {Type: "cell_is", Criteria: "<", Value: "0"},
})

v, _ := f.GetCellValue("Sheet1", "B2")
fmt.Println(v)

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

Допишите создание условного стиля, применение правила и тип сравнения.

style, err := f.(&excelize.Style{ Font: &excelize.Font{Color: "#9A0404"}, }) f.("Отчёт", "C2:C21", []excelize.ConditionalFormatOptions{ {Type: "", Criteria: "<", Value: "0", Format: &style}, })

Соберите акценты отчёта

Расставьте строки в порядке выполнения: создать условный стиль, проверить ошибку, красить минусы, добавить шкалу на соседнюю колонку, вывести правила, снять правило со шкалы.

Строки

Ваша программа

Нажимайте на строки слева, чтобы собрать программу

Карточки: условное форматирование

Термин

NewConditionalStyle

нажмите, чтобы перевернуть

Определение

Создаёт условный стиль в своей таблице и возвращает (int, error). Обычный NewStyle в правилах не работает.

1 / 6

Акценты поставлены: минусы красятся правилом, шкала растянулась по просрочке, снятие и чтение правил под контролем. Дальше данные перестанут быть только таблицей: в уроке про диаграммы AddChart превратит колонку значений в график рядом с данными, а в уроке про картинки в шапку встанет логотип. С условными стилями рядом стоит и обычный из урока про стили ячеек — базовое оформление держит он, акценты держит условное, и спорят они редко: слои дополняют друг друга. Обе техники сходятся в шаблоне экспорта отчёта из базы, а разбор того, где оформление вредит данным, лежит в уроке про типичные ошибки excelize. Если вопрос про тип поля Format кажется знакомым — в седьмом уроке та же история с указателями в структуре Style. Домашнее задание — правило для минусов и шкала на свою колонку данных.

Песочница урока

Редактируйте код и запускайте его прямо здесь — интерпретатор работает в браузере, ничего устанавливать не нужно. За каждый запуск — +2 XP.

Вывод

Запустите код, чтобы увидеть вывод.

Коротко: что запомнить

SetConditionalFormat прикладывает срез правил к диапазону в A1-нотации.
Стиль для правила создаёт NewConditionalStyle; Format ждёт указатель на идентификатор.
cell_is — порог по значению, color_scale — градиент, data_bar — полосы, icon_set — статусы.
Правила живут в файле, красит Excel при открытии и правках данных.
GetConditionalFormats читает правила, UnsetConditionalFormat снимает по диапазону.
GetCellValue отдаёт значения и ничего не знает о цвете: парсер не видит раскраску.

Домашнее задание

Возьмите свой отчёт: одним условным стилем покрасьте отрицательные значения колонки данных, на соседнюю колонку поставьте трёхцветную шкалу с серединой на процентиле, добавьте полосу данных на третью колонку. Проверьте GetConditionalFormats, что правил три, затем снимите шкалу UnsetConditionalFormat и убедитесь в Excel, что правка числа меняет раскраску сама.

Вопросы и ответы по уроку

Почему Format в правиле требует именно NewConditionalStyle?