学习Excel技术，关注微信公众号：

excelperfect

Excel支持3种不同类型的条件格式：内置、标准和自定义。内置条件格式将特定规则与预定义样式相组合。标准条件格式将特定规则与自定义格式相组合。此外，可以定义自定义公式来应用使用不同样式的自定义格式。

创建条件格式的基本语法为：

>>>fromopenpyxl.formatting importRule

>>>fromopenpyxl.styles importFont, PatternFill,Border

>>>fromopenpyxl.styles.differentialimportDifferentialStyle

>>>dxf = DifferentialStyle(font=Font(bold=True), fill=PatternFill(start_color='EE1111',end_color='EE1111'))

>>>rule = Rule(type='cellIs', dxf=dxf,formula=["10"])

内置格式

内置条件格式有：ColorScale（色阶）

IconSet（图标集）

DataBar（数据条）

内置格式包含一系列格式设置，这些设置将类型与整数组合以进行比较。可能的类型有：“数字”、“百分比”、“最大值”、“最小值”、“公式”、“百分点值”。

ColorScale（色阶）

可以使用2种或3种颜色的色阶。2个色阶产生从一种颜色到另一种颜色的渐变，3个色阶使用额外颜色产生2种颜色渐变。

创建色阶规则的完整语法是：

>>>fromopenpyxl.formatting.ruleimport ColorScale,FormatObject

>>>from openpyxl.styles import Color

>>>first = FormatObject(type='min')

>>>last = FormatObject(type='max')

>>>#颜色与格式对象匹配

>>>colors= [Color('AA0000'), Color('00AA00')]

>>>cs2 = ColorScale(cfvo=[first, last], color=colors)

>>>#三色阶将扩展序列

>>>mid = FormatObject(type='num', val=40)

>>>colors.insert(1, Color('00AA00'))

>>>cs3 = ColorScale(cfvo=[first, mid, last], color=colors)

>>>#创建使用色阶的规则

>>>fromopenpyxl.formatting.rule importRule

>>>rule = Rule(type='colorScale',colorScale=cs3)

下面是创建色阶规则的便利的方法：

>>>fromopenpyxl.formatting.rule importColorScaleRule

>>>rule = ColorScaleRule(start_type='percentile',

start_value=10,

start_color='FFAA0000',

mid_type='percentile',

mid_value=50,

mid_color='FF0000AA',

end_type='percentile',

end_value=90,

end_color='FF00AA00')

IconSet（图标）

从下列图标集中选择：“3Arrows三向箭头”、“3ArrowsGray三向箭头（灰色）”、“3Flags三色旗”、“3TrafficLights1三色交通灯1”、“3TrafficLights2三色交通灯2”、“3Signs三标志”、“3Symbols三个符号”、“3Symbols2三个符号2”、“4Arrows四向箭头”、“4ArrowsGray四向箭头（灰色）”、“4RedToBlack红黑渐变”、“4Rating四等级”、“4TrafficLights四色交通灯”、“5Arrows五向箭头”、“5ArrowsGray五向箭头（灰色）”、“5Rating五等级”、“5Quarters五象限图”。

创建图标规则的完整语法是：

>>>fromopenpyxl.formatting.rule importIconSet, FormatObject

>>>first = FormatObject(type='percent', val=0)

>>>second = FormatObject(type='percent', val=33)

>>>third = FormatObject(type='percent', val=67)

>>>iconset = IconSet(iconSet='3TrafficLights1',cfvo=[first, second, third], showValue=None, percent=None,reverse=None)

>>># 将图标集赋给规则

>>>fromopenpyxl.formatting.rule importRule

>>>rule= Rule(type='iconSet',iconSet=iconset)

下面是创建图标集规则的便利方法：

>>>fromopenpyxl.formatting.rule importIconSetRule

>>>rule = IconSetRule('5Arrows', 'percent', [10, 20, 30, 40, 50], showValue=None, percent=None, reverse=None)

DataBar（数据条）

当前，openpyxl支持原始规范中定义的数据条，在以后的扩展中将会添加边框和方向。

创建数据条规则的完整语法是：

>>>fromopenpyxl.formatting.ruleimport DataBar,FormatObject

>>>first = FormatObject(type='min')

>>>second= FormatObject(type='max')

>>>data_bar= DataBar(cfvo=[first, second], color="638EC6",showValue=None, minLength=None, maxLength=None)

>>> #将数据条赋给规则

>>>fromopenpyxl.formatting.rule import Rule

>>>rule= Rule(type='dataBar',dataBar=data_bar)

下面是创建数据条规则的便利方法：

>>>fromopenpyxl.formatting.rule importDataBarRule

>>>rule = DataBarRule(start_type='percentile',

start_value=10,

end_type='percentile',

end_value='90',

color="FF638EC6",

showValue="None",

minLength=None,

maxLength=None)

标准条件格式

标准条件格式有：平均值

百分比

唯一值或重复值

值

等级

示例

下面是在单元格区域中应用条件格式的示例代码：

fromopenpyxl import Workbook

from openpyxl.styles import Color, PatternFill, Font, Border

from openpyxl.styles.differential import DifferentialStyle

fromopenpyxl.formatting.rule import ColorScaleRule,CellIsRule, FormulaRule

from openpyxl.formatting import Rule

wb= Workbook()

ws= wb.active

#创建填充

redFill= PatternFill(start_color='EE1111',

end_color='EE1111',

fill_type='solid')

#添加二色色阶

#采用Excel'RRGGBB'样式的颜色

ws.conditional_formatting.add('A1:A10',

ColorScaleRule(start_type='min',

start_color='AA0000',

end_type='max',

end_color='00AA00')

)

#添加三色色阶

ws.conditional_formatting.add('B1:B10',

ColorScaleRule(start_type='percentile',

start_value=10,

start_color='AA0000',

mid_type='percentile',

mid_value=50,

mid_color='0000AA',

end_type='percentile',

end_value=90,

end_color='00AA00')

)

#基于单元格比较添加条件格式

# addCellIs(range_string, operator, formula, stopIfTrue, wb,font, border, fill)

#如果单元格小于'公式'则应用格式

ws.conditional_formatting.add('C2:C10',

CellIsRule(operator='lessThan',

formula=['C$1'],

stopIfTrue=True,

fill=redFill))

#如果单元格在'公式'之间则应用格式

ws.conditional_formatting.add('D2:D10',

CellIsRule(operator='between',

formula=['1','5'],

stopIfTrue=True,

fill=redFill))

#使用公式的格式

ws.conditional_formatting.add('E1:E10',

FormulaRule(formula=['ISBLANK(E1)'],

stopIfTrue=True,

fill=redFill))

#除了2个色阶和3个色阶外,格式规则还采用字体、边填和填充样式

myFont= Font()

myBorder= Border()

ws.conditional_formatting.add('E1:E10',

FormulaRule(formula=['E1=0'],

font=myFont,

border=myBorder,

fill=redFill))

#使用指定公式突出显示包含特定文本的单元格

red_text= Font(color="9C0006")

red_fill= PatternFill(bgColor="FFC7CE")

dxf= DifferentialStyle(font=red_text, fill=red_fill)

rule= Rule(type="containsText",operator="containsText",

text="highlight",dxf=dxf)

rule.formula= ['NOT(ISERROR(SEARCH("完美Excel",A1)))']

ws.conditional_formatting.add('A1:F40', rule)

wb.save("cftest.xlsx")

运行代码后的效果如下图1所示。

图1

问题描述

        当前场景是，从模版读取excel的信息和格式，新建一个excel，并设置格式和数据，但是在设置条件样式(条件格式)没有设置成功，这里记录一下解决办法。

从百度也看了好多文章，都试了也不行。接下来，说一下解决思路

解决思路

        POI中excel的条件格式是 “XSSFSheetConditionalFormatting” 这样的一个类，条件样式中有“条件”和“格式” 是两个不同的处理地方，也就是意味着，我获取了模版中的“条件”设置到新的excel之后，还需要获取模版中的“格式”设置到新的excel里。

        1、获取模版和新的excel中sheet页的“条件样式”对象(因为条件样式是针对sheet页来的)

XSSFSheetConditionalFormatting oldFormatting = oldSheet.getSheetConditionalFormatting(); //获取模版的sheet中的条件样式对象
XSSFSheetConditionalFormatting newFormatting = newSheet.getSheetConditionalFormatting(); //获取新的excel的sheet中的条件样式对象

        2、获取模版中有多少“条件样式”

int numConditionalFormattings = oldFormatting.getNumConditionalFormattings(); //获取模版的sheet中有多少条件样式

        3、对模版中的条件样式进行循环，并获取模版中的每一条“条件样式”

XSSFConditionalFormatting conditionalFormattingAt = oldFormatting.getConditionalFormattingAt(i); //获取模版的sheet中的每一个条件样式

        4、获取模版中“条件样式”对应的单元格数组

CellRangeAddress[] formattingRanges = conditionalFormattingAt.getFormattingRanges(); //获取模版的sheet中的"条件样式"对应的单元格

        5、获取模版中“条件”的下标(我这里只取了一个，如果是多个就循环取)

int numberOfRules = conditionalFormattingAt.getNumberOfRules(); //获取模版的sheet中"条件"的下标

        7、获取模版中的“条件”对象，并设置到新的sheet里

XSSFConditionalFormattingRule rule = conditionalFormattingAt.getRule(numberOfRules-1); //通过下标获取模版的sheet中的"条件"
newFormatting.addConditionalFormatting(formattingRanges,rule); //设置新的sheet中的"条件样式"中的"条件"

        8、在新的sheet里创建“样式”对象

XSSFConditionalFormattingRule conditionalFormattingColorScaleRule = newFormatting.createConditionalFormattingColorScaleRule(); //创建新的sheet中的"条件样式"中的"样式"
XSSFPatternFormatting patternFormatting = conditionalFormattingColorScaleRule.createPatternFormatting(); //获取"样式"对象

        9、将模版的“样式”设置到新的sheet里

patternFormatting.setFillBackgroundColor(rule.getPatternFormatting().getFillBackgroundColorColor()); //设置新sheet中"条件样式"的背景

测试解析

        如果只设置到了第7步，就是下面的结果

       如果设置到了第9步，就是下面的结果

不扯没用的，直接上代码 

    /**
     * 将模版sheet中的条件样式设置到新的sheet中
     * @param newSheet
     * @param oldSheet
     */
    public static void setConditionStyle(XSSFSheet newSheet,XSSFSheet oldSheet){
        XSSFSheetConditionalFormatting oldFormatting = oldSheet.getSheetConditionalFormatting(); //获取模版的sheet中的条件样式对象
        XSSFSheetConditionalFormatting newFormatting = newSheet.getSheetConditionalFormatting(); //获取新的excel的sheet中的条件样式对象
        int numConditionalFormattings = oldFormatting.getNumConditionalFormattings(); //获取模版的sheet中有多少条件样式
        for (int i = 0; i < numConditionalFormattings; i++) {
            XSSFConditionalFormatting conditionalFormattingAt = oldFormatting.getConditionalFormattingAt(i); //获取模版的sheet中的每一个条件样式
            CellRangeAddress[] formattingRanges = conditionalFormattingAt.getFormattingRanges(); //获取模版的sheet中的"条件样式"对应的单元格
            int numberOfRules = conditionalFormattingAt.getNumberOfRules(); //获取模版的sheet中"条件"的下标
            XSSFConditionalFormattingRule rule = conditionalFormattingAt.getRule(numberOfRules-1); //通过下标获取模版的sheet中的"条件"
            newFormatting.addConditionalFormatting(formattingRanges,rule); //设置新的sheet中的"条件样式"中的"条件"

            XSSFConditionalFormattingRule conditionalFormattingColorScaleRule = newFormatting.createConditionalFormattingColorScaleRule(); //创建新的sheet中的"条件样式"中的"样式"
            XSSFPatternFormatting patternFormatting = conditionalFormattingColorScaleRule.createPatternFormatting(); //获取"样式"对象
            patternFormatting.setFillBackgroundColor(rule.getPatternFormatting().getFillBackgroundColorColor()); //设置新sheet中"条件样式"的背景

        }
    }

如果有什么疑问，或者指教 欢迎加QQ：1318623133

-----------------------------------------   I`am Joshua,may god bless you