openpyxl给excel设置条件格式

设置渐变色

我的项目是，将excel表中，数据按照大小来上色。
对于表的格式，一共21列，13行，其中，第一列是模型名，第一行是指标名，因此，这部分内容不参与评估。
此外，一般的指标，数值越大，颜色越是浅，数值越小，颜色越是深蓝；对于一些指标越小越好的数据列（指标名中有small），则正好相反。

参考链接：
给excel一列设置条件格式：可以看颜色代码的网站，
官方文档：有条件的 格式化

设置两色渐变

效果图如下：

代码：

def set_condition_format():
    import openpyxl
    from openpyxl.formatting.rule import ColorScaleRule

    file_name = "C:/Users/Dell/Documents/PythonFiles/forTESTonly/2sel/base/changeDB1123.xlsx"
    wb = openpyxl.load_workbook(file_name)

    for ws in wb:
        for c in range(20):     # 一共有21列，其中，第一列不比较
            BT = chr(ord("B") + c)      
            if "small" in ws[f"{BT}1"].value:   # 判断列名，若是越小越好
                b, w =  'fefff9', '00046a'      # 依次是浅白，深蓝
            else:
                b, w =  '00046a','fefff9'
            ws.conditional_formatting.add(f'{BT}2:{BT}13',
                                          ColorScaleRule(start_type='max', start_color=b,
                                                         end_type='min', end_color=w))
    wb.save(file_name)
set_condition_format()

设置三色渐变

这里设置最佳指标为深绿色，最差指标为红色，效果图：

代码：

# 给数据一列设置单元格样式,这个是设置3色渐变
def set_condition_format():
    import openpyxl
    from openpyxl.formatting.rule import ColorScaleRule

    # file_name = "C:/Users/Dell/Documents/PythonFiles/forTESTonly/2sel/base/new_xlsx/changeDB318.xlsx"
    file_name = "C:/Users/Dell/Documents/PythonFiles/forTESTonly/2sel/base/new_xlsx/changeDB1123.xlsx"
    wb = openpyxl.load_workbook(file_name)

    for ws in wb:
        for c in range(20):     # 一共有21列，其中，第一列不比较
            BT = chr(ord("B") + c)
            if "small" in ws[f"{BT}1"].value:   # 判断列名，若是越小越好
                big, small = '27a500' ,'ff564e'       # 小的是绿色
            else:
                big, small = 'ff564e' , '27a500'    # 大的是绿色
            ws.conditional_formatting.add(f'{BT}2:{BT}13',
                                          ColorScaleRule(start_type='max', start_color=big,
                                                         mid_type='percentile', mid_value=50,  mid_color = 'FFFFFF',
                                                         end_type='min', end_color=small
                                                         )
                                        )
    wb.save(file_name)
set_condition_format()

试一下，如果把19行改为：

mid_type='percentile', mid_value=80,  mid_color = 'FFFFFF',

上面的出现一点异常，即E列，出现了大量绿色。纠结了一个小时后，发现这是因为，80%*BiggestNum被设置成了绿色。即这里的阈值，限制的是，数的大小的基准，而不是会自动倒过来，将大的80%设置为新基准。如果要生成跟其他一样的效果，这一行应该设成20%。

修改列高，列宽

参考文章：Python使用OpenPyXl设置Excel表格中的单元格大小（行高和列宽），用openpyxl把各个Sheet另存为单独的Excel

原图和效果图

以下分别是原图和效果图：

代码,直接可运行：

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
def setSHEEThw(ws,width,height):
    for i in range(1, ws.max_row + 1):
        ws.row_dimensions[i].height = height
    for i in range(1, ws.max_column + 1):
        ws.column_dimensions[get_column_letter(i)].width = width

def setHeightWidth(path,width,height):
    wb = load_workbook(path)
    sheetnames = wb.sheetnames

    for name in sheetnames:
        ws = wb.get_sheet_by_name(name)
        setSHEEThw(ws, width, height)

    wb.save(path)      # 另存为：原文件名3.xlsx

可以直接调用，示例如下：

path = "2selCELL/base/rank2.xlsx"
setHeightWidth(path,2.18,12)
print("ok")