formatting.rule【書式ルール】モジュール

from openpyxl import Workbook
from openpyxl.formatting import Rule
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import CellIsRule
from openpyxl.styles import PatternFill, fills
from openpyxl.styles import Font

# データ設定
data = [1, 10, 3, 8, 5, 6, 7, 4, 9, 2]
def set_data(row, col):
    for value in data:
        ws.cell(row, col, value)
        row += 1


# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active
font = Font(bold=True, color='FFFFFFFF')
fill = PatternFill(fills.FILL_SOLID, 'FF0000FF', 'FF0000FF')
dxf = DifferentialStyle(
    font=font,
    fill=fill,
)

# 通常生成 (次の値以上：即値)
ws['B2'] = '次の値以上 (即値)'
ws['B3'] = '通常生成'
rule = Rule(
    type='cellIs',
    operator='greaterThanOrEqual',
    formula=[5],
    dxf=dxf,
)
ws.conditional_formatting.add('B5:B14', rule)
set_data(5, 2)

# 簡易生成 (次の値以上：即値)
ws['C3'] = '簡易生成'
rule = CellIsRule(
    operator='>=',
    formula=[5],
    font=font,
    fill=fill,
)
ws.conditional_formatting.add('C5:C14', rule)
set_data(5, 3)

# セル参照
CELL_VALUE_1 = '$D$4'
CELL_VALUE_2 = '$E$4'
NAME_VALUE_2 = 'value_2'
ws[CELL_VALUE_1] = 3
ws[CELL_VALUE_2] = 6
wb.create_named_range(NAME_VALUE_2, ws, CELL_VALUE_2)

# 通常生成 (次の値の間：絶対セルアドレス・名前定義)
ws['D2'] = '次の値の間 (セル参照)'
ws['D3'] = '通常生成'
rule = Rule(
    type='cellIs',
    operator='between',
    formula=[CELL_VALUE_1, NAME_VALUE_2],
    dxf=dxf,
)
ws.conditional_formatting.add('D5:D14', rule)
set_data(5, 4)
print(rule)
# 出力：
# <openpyxl.formatting.rule.Rule object>
# Parameters:
# (一部省略)
# type='cellIs',
# priority=3,
# operator='between',
# formula=['$D$4', 'value_2']

# 簡易生成 (次の値以上：即値)
ws['E3'] = '簡易生成'
rule = CellIsRule(
    operator='between',
    formula=[CELL_VALUE_1, NAME_VALUE_2],
    font=font,
    fill=fill,
)
ws.conditional_formatting.add('E5:E14', rule)
set_data(5, 5)
print(rule)
# 出力：
# <openpyxl.formatting.rule.Rule object>
# Parameters:
# (一部省略)
# type='cellIs',
# priority=4,
# operator='between',
# formula=['$D$4', 'value_2']

# ワークブック保存
wb.save(WORKBOOK)

from openpyxl import Workbook
from openpyxl.formatting import Rule
from openpyxl.styles.differential import DifferentialStyle
from openpyxl.formatting.rule import FormulaRule
from openpyxl.styles import PatternFill, fills
from openpyxl.styles import Font
from openpyxl.utils import get_column_letter

# データ設定
data = [1, 10, 2, 9, 3, 8, 4, 7, 5, 6]
def set_data(row, col, vertical=True):
    for value in data:
        ws.cell(row, col, value)
        if vertical:
            row += 1
        else:
            col += 1


# ワークブック作成
WORKBOOK = 'sample.xlsx'
wb = Workbook()
ws = wb.active
font = Font(bold=True, color='FFFFFFFF')
fill = PatternFill(fills.FILL_SOLID, 'FF0000FF', 'FF0000FF')
dxf = DifferentialStyle(
    font=font,
    fill=fill,
)
for col in range(2, 12):
    ws.column_dimensions[get_column_letter(col)].width = 5

# 通常生成 (数式)
ws['B2'] = '通常生成 (数式：３の倍数)'
ws['B3'] = '行全体'
rule = Rule(
    type='expression',
    formula=['MOD($D4,3)=0'],
    dxf=dxf,
)
ws.conditional_formatting.add('B4:F13', rule)
set_data(4, 4)

# 簡易生成 (数式)
ws['B16'] = '簡易生成 (数式：３の倍数)'
ws['B17'] = '列全体'
rule = FormulaRule(
    formula=['MOD(B$20,3)=0'],
    font=font,
    fill=fill,
)
ws.conditional_formatting.add('B18:K22', rule)
set_data(20, 2, False)

# ワークブック保存
wb.save(WORKBOOK)