Java 設(shè)置Excel條件格式（高亮條件值、應(yīng)用單元格值/公式/數(shù)據(jù)條等類型）

import?com.spire.xls.*;
import?com.spire.xls.core.IConditionalFormat;
import?com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;
import?com.spire.xls.core.spreadsheet.conditionalformatting.TimePeriodType;

import?java.awt.*;

public?class?AddConditionalFormat?{
????public?static?void?main(String[]?args)?{
????????//創(chuàng)建實(shí)例，加載測試文檔
????????Workbook?wb?=?new?Workbook();
????????wb.loadFromFile("test.xlsx");

????????//獲取第一個(gè)工作表
????????Worksheet?sheet?=?wb.getWorksheets().get(0);

????????//添加條件格式1并指定數(shù)據(jù)范圍
????????XlsConditionalFormats?format1?=?sheet.getConditionalFormats().add();
????????format1.addRange(sheet.getCellRange("A2:A12"));
????????//高亮低于平均數(shù)值的單元格
????????IConditionalFormat?cf1?=?format1.addAverageCondition(AverageType.Below);
????????cf1.setBackColor(new?Color(230,230,250));
????????//高亮高于平均數(shù)值的單元格
????????IConditionalFormat?cf2?=?format1.addAverageCondition(AverageType.Above);
????????cf2.setBackColor(new?Color(224,255,255));

????????//添加條件格式2并指定數(shù)據(jù)范圍
????????XlsConditionalFormats?format2?=?sheet.getConditionalFormats().add();
????????format2.addRange(sheet.getCellRange("B2:B12"));
????????//高亮最高值
????????IConditionalFormat?cf3?=?format2.addTopBottomCondition(TopBottomType.Top,?1);
????????cf3.setBackColor(new?Color(144,238,144));
????????//高亮最低值單元格
????????IConditionalFormat?cf4?=?format2.addTopBottomCondition(TopBottomType.Bottom,?1);
????????cf4.setBackColor(new?Color(221,160,221));

????????//添加條件格式3并指定數(shù)據(jù)范圍
????????XlsConditionalFormats?format3?=?sheet.getConditionalFormats().add();
????????format3.addRange(sheet.getCellRange("C2:C12"));
????????//高亮唯一值的單元格
????????IConditionalFormat?cf5?=?format3.addDuplicateValuesCondition();
????????cf5.setFormatType(ConditionalFormatType.UniqueValues);
????????cf5.setBackColor(new?Color(0,255,255));

????????//添加條件格式4并指定數(shù)據(jù)范圍
????????XlsConditionalFormats?format4?=?sheet.getConditionalFormats().add();
????????format4.addRange(sheet.getCellRange("D2:D12"));
????????//高亮重復(fù)數(shù)值的單元格
????????IConditionalFormat?cf6?=?format4.addDuplicateValuesCondition();
????????cf6.setFormatType(ConditionalFormatType.DuplicateValues);
????????cf6.setBackColor(new?Color(255,228,196));

????????//添加條件格式5并指定數(shù)據(jù)范圍
????????XlsConditionalFormats?format5?=?sheet.getConditionalFormats().add();
????????format5.addRange(sheet.getCellRange("E2:E12"));
????????//高亮本周日期的單元格
????????IConditionalFormat?cf7?=?format5.addTimePeriodCondition(TimePeriodType.ThisWeek);
????????cf7.setBackColor(new?Color(255,165,0));

????????//保存文檔
????????wb.saveToFile("AddConditionalFormat.xlsx",?ExcelVersion.Version2013);
????????wb.dispose();
????}
}

import?com.spire.xls.*;

import?java.awt.*;

public?class?AddConditionalFormat?{
????public?static?void?main(String[]?args)?{
????????//創(chuàng)建實(shí)例，加載測試文檔
????????Workbook?wb?=?new?Workbook();
????????wb.loadFromFile("sample.xlsx");

????????//獲取第一個(gè)工作表
????????Worksheet?sheet?=?wb.getWorksheets().get(0);

????????//獲取應(yīng)用條件格式的數(shù)據(jù)范圍
????????CellRange?range?=?sheet.getCellRange("A2:H27");

????????//添加條件格式1
????????ConditionalFormatWrapper?format1?=?range.getConditionalFormats().addCondition();
????????//條件格式類型1基于單元格值
????????format1.setFormatType(ConditionalFormatType.CellValue);
????????//將數(shù)值在60到90之間的單元格進(jìn)行字體加粗，并設(shè)置字體顏色為橙色
????????format1.setFirstFormula("90");
????????format1.setSecondFormula("100");
????????format1.setOperator(ComparisonOperatorType.Between);
????????format1.setFontColor(new?Color(30,144,255));
????????//format1.setBackColor(Color.orange);

????????//添加條件格式2
????????ConditionalFormatWrapper?format2?=?range.getConditionalFormats().addCondition();
????????format2.setFormatType(ConditionalFormatType.CellValue);
????????format2.setFirstFormula("60");
????????format2.setOperator(ComparisonOperatorType.Less);
????????format2.setFontColor(Color.red);
????????//format2.setBackColor(Color.red);
????????format2.isBold();
????????//添加邊框格式（邊框顏色、邊框類型）到條件格式2
????????format2.setLeftBorderColor(Color.red);
????????format2.setRightBorderColor(new?Color(0,0,139));
????????format2.setTopBorderColor(new?Color(123,104,238));
????????format2.setBottomBorderColor(new?Color(50,205,50));
????????format2.setLeftBorderStyle(LineStyleType.Medium);
????????format2.setRightBorderStyle(LineStyleType.Thick);
????????format2.setTopBorderStyle(LineStyleType.Double);
????????format2.setBottomBorderStyle(LineStyleType.Double);

????????//條件格式3的類型為公式
????????ConditionalFormatWrapper?format3?=?range.getConditionalFormats().addCondition();
????????format3.setFormatType(ConditionalFormatType.Formula);

????????//自定義公式將低于60的單元格所在的行填充背景色
????????format3.setFirstFormula("=OR($C2<60,$D2<60,$E2<60,$F2<60,$G2<60,$H2<60)");
????????format3.setBackColor(Color.lightGray);


????????//獲取第二個(gè)工作表
????????Worksheet?sheet2?=?wb.getWorksheets().get(1);

????????//獲取應(yīng)用條件格式的數(shù)據(jù)范圍
????????CellRange?range2?=?sheet2.getCellRange("B2:D7");

????????//添加條件類型4為data?bars
????????ConditionalFormatWrapper?format4?=?range2.getConditionalFormats().addCondition();
????????format4.setFormatType(ConditionalFormatType.DataBar);
????????format4.getDataBar().setBarColor(new?Color(152,251,152));

????????//保存文檔
????????wb.saveToFile("AddConditionalFormat2.xlsx",?ExcelVersion.Version2013);
????????wb.dispose();
????}
}

import?com.spire.xls.*;

public?class?RemoveConditionalFormat?{
????public?static?void?main(String[]?args)?{
????????Workbook?wb?=?new?Workbook();
????????wb.loadFromFile("AddConditionalFormat.xlsx");

????????//獲取第一個(gè)工作表
????????Worksheet?sheet?=?wb.getWorksheets().get(0);

????????//刪除指定單元格范圍中的條件格式
????????sheet.getCellRange("A5:H5").getConditionalFormats().removeAt(3);

????????//保存并打開文檔
????????wb.saveToFile("RemoveConditionalFormat.xlsx",?ExcelVersion.Version2010);
????????wb.dispose();
????}
}