使用 Java 在 Excel 工作表中应用条件格式:数据可视化与智能分析
在数据分析和报表制作过程中,快速识别关键数据、异常值和趋势是提高工作效率的关键。手动逐个检查数据不仅耗时,还容易遗漏重要信息。通过条件格式,可以根据数据特征自动应用不同的视觉样式,让重要数据一目了然。 本文将介绍如何使用 Java 程序化地在 Excel 工作表中应用各种条件格式,包括数值比较、数据条、色阶、图标集、重复值检测、平均值高亮等,实现数据的智能可视化分析。 本文使用的方法需要用到免费的 Free Spire.XLS for Java,可通过 Maven 或手动导入 JAR 包的方式集成到项目中。 在项目的 或者直接从官网下载 JAR 包并手动导入项目。 数值比较是最常用的条件格式类型,可以根据单元格值与指定值的关系自动应用格式。 说明: 此条件格式会自动高亮所有大于800的单元格,便于快速识别高值数据。 使用场景: 适用于识别低值数据、异常值或需要特别关注的小数值。 说明: 使用场景: 适用于识别异常值或超出正常范围的数据。 数据条通过在单元格中显示渐变填充的条形图,直观展示数值大小。 说明: 使用场景: 适用于快速比较数值大小,常用于销售数据、绩效指标等场景。 色阶使用双色或三色渐变来表示数值分布,直观展示数据的高低分布。 说明: 使用场景: 适用于展示数据分布趋势,如温度变化、销售趋势等。 图标集使用图标(如交通灯、箭头、星星等)来表示数据的不同等级。 说明: 使用场景: 适用于状态指示、绩效评估、风险等级划分等场景。 在数据清洗和验证过程中,识别重复值和唯一值非常重要。 说明: 使用场景: 数据去重、数据质量检查、唯一性验证等。 平均值条件格式可以快速识别高于或低于平均水平的数值。 说明: 使用场景: 绩效评估、销售分析、成绩排名等需要与平均水平对比的场景。 排名条件格式可以快速识别最高或最低的N个值。 说明: 使用场景: 识别最佳/最差表现、Top N 分析、异常值检测等。 公式条件格式提供了最大的灵活性,可以根据自定义公式应用格式。 说明: 使用场景: 复杂条件判断、跨列比较、自定义业务规则等。 日期条件格式可以高亮特定时间段的日期,如最近7天、上个月等。 说明: 使用场景: 项目进度跟踪、任务管理、时间敏感数据分析等。 除了颜色和图案,条件格式还可以设置边框样式。 说明: 使用场景: 需要突出显示但不改变背景色的场景,如打印报表。 通过本文示例,你已经了解如何使用 Java 在 Excel 工作表中应用各种条件格式。从基础的数值比较到高级的数据条、色阶、图标集,再到重复值检测、平均值高亮和排名分析,整个过程高度自动化,特别适用于数据分析、报表制作和数据质量检查场景。 相比手动设置条件格式,代码方式具有以下优势: 你可以在此基础上扩展更多能力,例如: 如果你正在处理数据分析、报表制作或数据质量检查相关需求,这种基于 Java 的条件格式方案将为你的工作带来显著提升。环境准备
Maven 依赖配置
pom.xml 文件中添加以下依赖:<repositories>
<repository>
<id>com.e-iceblue</id>
<name>e-iceblue</name>
<url>https://repo.e-iceblue.cn/repository/maven-public/</url>
</repository>
</repositories>
<dependency>
<groupId>e-iceblue</groupId>
<artifactId>spire.xls.free</artifactId>
<version>16.3.1</version>
</dependency>1. 基于数值比较的条件格式
1.1 高亮大于指定值的单元格
import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;
import java.awt.*;
public class HighlightGreaterThan {
public static void main(String[] args) throws Exception {
// 创建工作簿
Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);
// 添加示例数据
sheet.getCellRange("A1").setNumberValue(582);
sheet.getCellRange("A2").setNumberValue(234);
sheet.getCellRange("A3").setNumberValue(314);
sheet.getCellRange("A4").setNumberValue(50);
sheet.getCellRange("B1").setNumberValue(150);
sheet.getCellRange("B2").setNumberValue(894);
sheet.getCellRange("B3").setNumberValue(560);
sheet.getCellRange("B4").setNumberValue(900);
// 添加条件格式:大于800的值显示红色字体和灰色背景
XlsConditionalFormats xcfs = sheet.getConditionalFormats().add();
xcfs.addRange(sheet.getAllocatedRange());
IConditionalFormat format = xcfs.addCondition();
format.setFormatType(ConditionalFormatType.CellValue);
format.setFirstFormula("800");
format.setOperator(ComparisonOperatorType.Greater);
format.setFontColor(Color.RED);
format.setBackColor(Color.LIGHT_GRAY);
// 保存文件
workbook.saveToFile("output/HighlightGreaterThan.xlsx", ExcelVersion.Version2013);
workbook.dispose();
}
}ConditionalFormatType.CellValue 表示基于单元格值的条件格式ComparisonOperatorType.Greater 表示"大于"比较运算符setFirstFormula() 设置比较的基准值setFontColor() 和 setBackColor() 分别设置字体颜色和背景颜色1.2 高亮小于指定值的单元格
// 添加条件格式:小于300的值显示绿色字体和蓝色背景
XlsConditionalFormats xcfs2 = sheet.getConditionalFormats().add();
xcfs2.addRange(sheet.getAllocatedRange());
IConditionalFormat format2 = xcfs2.addCondition();
format2.setFormatType(ConditionalFormatType.CellValue);
format2.setFirstFormula("300");
format2.setOperator(ComparisonOperatorType.Less);
format2.setFontColor(Color.GREEN);
format2.setBackColor(Color.BLUE);1.3 高亮介于两个值之间的单元格
// 添加条件格式:介于300到500之间的值显示黄色背景
XlsConditionalFormats xcfs3 = sheet.getConditionalFormats().add();
xcfs3.addRange(sheet.getCellRange("A1:D4"));
IConditionalFormat format3 = xcfs3.addCondition();
format3.setFormatType(ConditionalFormatType.CellValue);
format3.setFirstFormula("300");
format3.setSecondFormula("500");
format3.setOperator(ComparisonOperatorType.Between);
format3.setBackColor(Color.YELLOW);setSecondFormula() 设置范围的上限值ComparisonOperatorType.Between 表示"介于...之间"的运算符1.4 高亮不在指定范围内的单元格
// 添加条件格式:不在100到200之间的值显示条纹图案
XlsConditionalFormats xcfs4 = sheet.getConditionalFormats().add();
xcfs4.addRange(sheet.getCellRange("A1:D4"));
IConditionalFormat format4 = xcfs4.addCondition();
format4.setFormatType(ConditionalFormatType.CellValue);
format4.setFirstFormula("100");
format4.setSecondFormula("200");
format4.setOperator(ComparisonOperatorType.NotBetween);
format4.setFillPattern(ExcelPatternType.ReverseDiagonalStripe);
format4.setColor(Color.LIGHT_GRAY);
format4.setBackColor(Color.BLACK);2. 数据条条件格式
import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;
import java.awt.*;
public class ApplyDataBars {
public static void main(String[] args) throws Exception {
Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);
// 添加示例数据
sheet.getCellRange("A1").setNumberValue(582);
sheet.getCellRange("A2").setNumberValue(234);
sheet.getCellRange("A3").setNumberValue(314);
sheet.getCellRange("A4").setNumberValue(50);
sheet.getCellRange("B1").setNumberValue(150);
sheet.getCellRange("B2").setNumberValue(894);
sheet.getCellRange("B3").setNumberValue(560);
sheet.getCellRange("B4").setNumberValue(900);
// 设置行高和列宽
sheet.getAllocatedRange().setRowHeight(15);
sheet.getAllocatedRange().setColumnWidth(17);
// 添加数据条条件格式
XlsConditionalFormats xcfs = sheet.getConditionalFormats().add();
xcfs.addRange(sheet.getAllocatedRange());
IConditionalFormat format = xcfs.addCondition();
format.setFormatType(ConditionalFormatType.DataBar);
format.getDataBar().setBarColor(Color.BLUE);
workbook.saveToFile("output/ApplyDataBars.xlsx", ExcelVersion.Version2013);
workbook.dispose();
}
}ConditionalFormatType.DataBar 表示数据条类型getDataBar().setBarColor() 设置数据条的颜色3. 色阶条件格式
import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;
public class ApplyColorScales {
public static void main(String[] args) throws Exception {
Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);
// 添加示例数据
sheet.getCellRange("A1").setNumberValue(582);
sheet.getCellRange("A2").setNumberValue(234);
sheet.getCellRange("A3").setNumberValue(314);
sheet.getCellRange("A4").setNumberValue(50);
sheet.getCellRange("B1").setNumberValue(150);
sheet.getCellRange("B2").setNumberValue(894);
sheet.getCellRange("B3").setNumberValue(560);
sheet.getCellRange("B4").setNumberValue(900);
// 添加色阶条件格式
XlsConditionalFormats xcfs = sheet.getConditionalFormats().add();
xcfs.addRange(sheet.getAllocatedRange());
IConditionalFormat format = xcfs.addCondition();
format.setFormatType(ConditionalFormatType.ColorScale);
workbook.saveToFile("output/ApplyColorScales.xlsx", ExcelVersion.Version2013);
workbook.dispose();
}
}ConditionalFormatType.ColorScale 表示色阶类型4. 图标集条件格式
import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;
public class ApplyIconSets {
public static void main(String[] args) throws Exception {
Workbook workbook = new Workbook();
Worksheet sheet = workbook.getWorksheets().get(0);
// 添加示例数据
sheet.getCellRange("A1").setNumberValue(582);
sheet.getCellRange("A2").setNumberValue(234);
sheet.getCellRange("A3").setNumberValue(314);
sheet.getCellRange("A4").setNumberValue(50);
sheet.getCellRange("B1").setNumberValue(150);
sheet.getCellRange("B2").setNumberValue(894);
sheet.getCellRange("B3").setNumberValue(560);
sheet.getCellRange("B4").setNumberValue(900);
// 添加图标集条件格式
XlsConditionalFormats xcfs = sheet.getConditionalFormats().add();
xcfs.addRange(sheet.getAllocatedRange());
IConditionalFormat format = xcfs.addCondition();
format.setFormatType(ConditionalFormatType.IconSet);
format.getIconSet().setIconSetType(IconSetType.ThreeTrafficLights1);
workbook.saveToFile("output/ApplyIconSets.xlsx", ExcelVersion.Version2013);
workbook.dispose();
}
}ConditionalFormatType.IconSet 表示图标集类型IconSetType.ThreeTrafficLights1 使用三色交通灯图标ThreeArrows、ThreeSymbols、FourRating、FiveQuarters 等5. 高亮重复值和唯一值
import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;
import java.awt.*;
public class HighlightDuplicateUnique {
public static void main(String[] args) throws Exception {
Workbook workbook = new Workbook();
workbook.loadFromFile("data/Template_Xls_6.xlsx");
Worksheet sheet = workbook.getWorksheets().get(0);
// 高亮重复值(红色背景)
XlsConditionalFormats xcfs1 = sheet.getConditionalFormats().add();
xcfs1.addRange(sheet.getCellRange("C2:C10"));
IConditionalFormat format1 = xcfs1.addCondition();
format1.setFormatType(ConditionalFormatType.DuplicateValues);
format1.setBackColor(Color.RED);
// 高亮唯一值(黄色背景)
XlsConditionalFormats xcfs2 = sheet.getConditionalFormats().add();
xcfs2.addRange(sheet.getCellRange("C2:C10"));
IConditionalFormat format2 = xcfs2.addCondition();
format2.setFormatType(ConditionalFormatType.UniqueValues);
format2.setBackColor(Color.YELLOW);
workbook.saveToFile("output/HighlightDuplicateUnique.xlsx", ExcelVersion.Version2013);
workbook.dispose();
}
}ConditionalFormatType.DuplicateValues 高亮重复出现的值ConditionalFormatType.UniqueValues 高亮只出现一次的值6. 高亮高于或低于平均值的单元格
import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;
import java.awt.*;
public class HighlightAverageValues {
public static void main(String[] args) throws Exception {
Workbook workbook = new Workbook();
workbook.loadFromFile("data/Template_Xls_6.xlsx");
Worksheet sheet = workbook.getWorksheets().get(0);
// 高亮低于平均值的单元格(蓝色背景)
XlsConditionalFormats xcfs1 = sheet.getConditionalFormats().add();
xcfs1.addRange(sheet.getCellRange("E2:E10"));
IConditionalFormat cf1 = xcfs1.addAverageCondition(AverageType.Below);
cf1.setBackColor(Color.BLUE);
// 高亮高于平均值的单元格(橙色背景)
XlsConditionalFormats xcfs2 = sheet.getConditionalFormats().add();
xcfs2.addRange(sheet.getCellRange("E2:E10"));
IConditionalFormat cf2 = xcfs2.addAverageCondition(AverageType.Above);
cf2.setBackColor(Color.ORANGE);
workbook.saveToFile("output/HighlightAverageValues.xlsx", ExcelVersion.Version2013);
workbook.dispose();
}
}addAverageCondition(AverageType.Below) 创建低于平均值的条件addAverageCondition(AverageType.Above) 创建高于平均值的条件7. 高亮排名前N或后N的值
import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;
import java.awt.*;
public class HighlightRankedValues {
public static void main(String[] args) throws Exception {
Workbook workbook = new Workbook();
workbook.loadFromFile("data/Template_Xls_6.xlsx");
Worksheet sheet = workbook.getWorksheets().get(0);
// 高亮前2名(红色背景)
XlsConditionalFormats xcfs1 = sheet.getConditionalFormats().add();
xcfs1.addRange(sheet.getCellRange("D2:D10"));
IConditionalFormat format1 = xcfs1.addTopBottomCondition(TopBottomType.Top, 2);
format1.setFormatType(ConditionalFormatType.TopBottom);
format1.setBackColor(Color.RED);
// 高亮后2名(绿色背景)
XlsConditionalFormats xcfs2 = sheet.getConditionalFormats().add();
xcfs2.addRange(sheet.getCellRange("E2:E10"));
IConditionalFormat format2 = xcfs2.addTopBottomCondition(TopBottomType.Bottom, 2);
format2.setFormatType(ConditionalFormatType.TopBottom);
format2.setBackColor(Color.GREEN);
workbook.saveToFile("output/HighlightRankedValues.xlsx", ExcelVersion.Version2013);
workbook.dispose();
}
}addTopBottomCondition(TopBottomType.Top, 2) 高亮前2名addTopBottomCondition(TopBottomType.Bottom, 2) 高亮后2名8. 基于公式的条件格式
import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;
public class FormulaConditionalFormat {
public static void main(String[] args) throws Exception {
Workbook workbook = new Workbook();
workbook.loadFromFile("data/Template_Xls_6.xlsx");
Worksheet sheet = workbook.getWorksheets().get(0);
// 获取第一列的范围
CellRange range = sheet.getColumns()[0];
// 添加公式条件格式:当A列值小于B列值时高亮
XlsConditionalFormats xcfs = sheet.getConditionalFormats().add();
xcfs.addRange(range);
IConditionalFormat conditional = xcfs.addCondition();
conditional.setFormatType(ConditionalFormatType.Formula);
conditional.setFirstFormula("=($A1<$B1)");
conditional.setBackKnownColor(ExcelColors.Yellow);
workbook.saveToFile("output/FormulaConditionalFormat.xlsx", ExcelVersion.Version2013);
workbook.dispose();
}
}ConditionalFormatType.Formula 表示基于公式的条件格式setFirstFormula() 设置条件公式,公式需要以等号开头setBackKnownColor() 使用预定义颜色设置背景9. 基于日期的条件格式
import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;
import java.awt.*;
public class DateConditionalFormat {
public static void main(String[] args) {
Workbook workbook = new Workbook();
workbook.loadFromFile("data/Template_Xls_6.xlsx");
Worksheet sheet = workbook.getWorksheets().get(0);
// 高亮最近7天的日期
XlsConditionalFormats xcfs = sheet.getConditionalFormats().add();
xcfs.addRange(sheet.getAllocatedRange());
IConditionalFormat conditionalFormat = xcfs.addTimePeriodCondition(TimePeriodType.Last7Days);
conditionalFormat.setBackColor(Color.ORANGE);
workbook.saveToFile("output/DateConditionalFormat.xlsx", ExcelVersion.Version2013);
workbook.dispose();
}
}addTimePeriodCondition(TimePeriodType.Last7Days) 创建最近7天的条件LastMonth、LastWeek、NextMonth、NextWeek、ThisMonth、ThisWeek、Today、Tomorrow、Yesterday 等10. 带边框样式的条件格式
import com.spire.xls.*;
import com.spire.xls.core.IConditionalFormat;
import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats;
import java.awt.*;
public class BorderConditionalFormat {
public static void main(String[] args) throws Exception {
Workbook workbook = new Workbook();
workbook.loadFromFile("data/ConditionalFormatRuntime.xlsx");
Worksheet sheet = workbook.getWorksheets().get(0);
// 添加条件格式:小于500的值显示蓝色边框
XlsConditionalFormats xcfs = sheet.getConditionalFormats().add();
xcfs.addRange(sheet.getCellRange("A2:D2"));
IConditionalFormat cf = xcfs.addCondition();
cf.setFormatType(ConditionalFormatType.CellValue);
cf.setFirstFormula("500");
cf.setOperator(ComparisonOperatorType.Less);
// 设置边框颜色和样式
cf.setLeftBorderColor(Color.BLUE);
cf.setRightBorderColor(Color.BLUE);
cf.setTopBorderColor(Color.GREEN);
cf.setBottomBorderColor(Color.GREEN);
cf.setLeftBorderStyle(LineStyleType.Medium);
cf.setRightBorderStyle(LineStyleType.Thick);
cf.setTopBorderStyle(LineStyleType.Double);
cf.setBottomBorderStyle(LineStyleType.Double);
workbook.saveToFile("output/BorderConditionalFormat.xlsx", ExcelVersion.Version2013);
workbook.dispose();
}
}setLeftBorderColor()、setRightBorderColor() 等设置各边框颜色setLeftBorderStyle()、setRightBorderStyle() 等设置各边框样式Thin、Medium、Thick、Double 等关键类与方法解析
核心类
类名 说明 WorkbookExcel 工作簿对象,用于创建、加载和保存 Excel 文件 WorksheetExcel 工作表对象,提供访问单元格和条件格式的功能 XlsConditionalFormats条件格式集合,用于管理和添加条件格式规则 IConditionalFormat条件格式接口,定义具体的格式规则和样式 CellRange单元格范围对象,表示一个或多个单元格 条件格式类型
类型 枚举值 说明 数值比较 ConditionalFormatType.CellValue基于单元格值与指定值的比较 数据条 ConditionalFormatType.DataBar在单元格中显示渐变条形图 色阶 ConditionalFormatType.ColorScale使用颜色渐变表示数值分布 图标集 ConditionalFormatType.IconSet使用图标表示数据等级 重复值 ConditionalFormatType.DuplicateValues高亮重复出现的值 唯一值 ConditionalFormatType.UniqueValues高亮只出现一次的值 公式 ConditionalFormatType.Formula基于自定义公式的条件 排名 ConditionalFormatType.TopBottom高亮前N或后N名 比较运算符
运算符 枚举值 说明 大于 ComparisonOperatorType.Greater单元格值 > 指定值 小于 ComparisonOperatorType.Less单元格值 < 指定值 介于 ComparisonOperatorType.Between指定值1 ≤ 单元格值 ≤ 指定值2 不介于 ComparisonOperatorType.NotBetween单元格值不在指定范围内 等于 ComparisonOperatorType.Equal单元格值 = 指定值 不等于 ComparisonOperatorType.NotEqual单元格值 ≠ 指定值 常用方法
方法 说明 addCondition()添加一个新的条件格式规则 addRange(CellRange)为条件格式添加应用范围 addAverageCondition(AverageType)添加平均值条件 addTopBottomCondition(TopBottomType, int)添加排名条件 addTimePeriodCondition(TimePeriodType)添加时间周期条件 setFormatType(ConditionalFormatType)设置条件格式类型 setFirstFormula(String)设置第一个公式或比较值 setSecondFormula(String)设置第二个公式或比较值(用于范围条件) setOperator(ComparisonOperatorType)设置比较运算符 setFontColor(Color)设置字体颜色 setBackColor(Color)设置背景颜色 setBackKnownColor(ExcelColors)使用预定义颜色设置背景 setFillPattern(ExcelPatternType)设置填充图案 getDataBar()获取数据条对象 getIconSet()获取图标集对象 总结
更多 Java Excel 文件操作技巧,请前往 Spire.XLS for Java 官方教程 查看。