使用 Aspose.Cells 对电子表格应用条件格式
条件格式有助于突出显示电子表格中的数据，从而使用户更容易阅读。本文展示了如何使用 Aspose.Cells for Java 以编程方式将条件格式应用于 Microsoft Excel 文件。
引言
各地的公司都在使用 Microsoft Excel 电子表格。它们被用于从创建简单的发票到构建复杂的模型等各种用途。使 Microsoft Excel 如此有吸引力的功能之一是条件格式。条件格式允许用户将格式应用于单元格或单元格范围，并使该格式根据单元格的值或公式的值而变化。例如，他们可以使单元格中的文本仅在单元格的值大于 100 时才显示为粗体。
本文介绍如何使用 Aspose.Cells for Java 通过应用条件格式在电子表格中创建一个简单的仪表板。
Aspose.Cells for Java 是一个 Microsoft Excel 电子表格 API，允许 Java 开发人员创建、操作和转换 Excel 文件。 Aspose.Cells for Java 提供了各种易于集成和使用的功能。使其如此灵活的一个特点是它独立于 Microsoft Office Automation – 它不需要在系统上安装 Microsoft Excel 才能工作。
应用程序概述
此应用程序的场景是一个销售团队，每个成员每周都会收到一个包含产品销售额的电子表格。为了让销售人员了解他们的表现，电子表格包含一个仪表板以及每个产品的销售数字。仪表板将本周的业绩与上周的业绩、预测和目标进行比较。当达到目标或超过预测时，仪表板显示绿色。
Data
就本应用程序而言，我们正在使用预定义的每周销售数据。（预定义的数据可以很容易地被来自数据源的输入替换。）使用的示例数据是
- 产品：一系列产品
- 价格：产品的单价
- 单位：上周售出的单位数量
- 小计：价格 * 单位
- 本周总计：=Sum（小计行）
- 上周总计：上周的总计。
- 目标：销售团队商定的本周总销售目标。
- 预测：趋势分析建议的本周总销售额。
条件格式
填充数据后，应用程序执行以下操作
- 将条件格式应用于小计行，以突出显示表现最好的和最差的五个产品。
- 创建一个显示以下内容的仪表板
- 本周：当前和前几周销售额的比较。 应用条件格式以突出显示性能。 如果本周的销售额高于上周，则单元格为绿色； 如果较低，则为红色。
- 目标：本周的实际销售额与目标销售额的比较。 如果实际销售额高于目标，则单元格为绿色； 如果较低，则为红色。
- 预测：实际销售额与预测销售额的比较。 如果实际销售额大于预测销售额，则单元格为绿色； 如果较低，则为红色。
为了说明如何应用条件格式，我们使用一个非常简单的场景。在实际应用中，规则可能更复杂，并且每个数据项都将从数据源计算或导入。
下面的屏幕截图显示了输出 XLS 文件，其中已按照定义的规则应用了条件格式。
仪表板为销售人员提供了一种快速评估其表现的方式，并且包含原始数据，以便他们可以进行详细的分析。
下面的屏幕截图显示了使用 Aspose.Cells for Java 应用的条件格式规则在 Microsoft Excel 中的外观。
将条件格式转换为代码
下面是一段代码，显示了用于设置仪表板并使用 Aspose.Cells for Java 将条件格式应用于总和列的方法。
// Apply CellValue Conditional Formatting on cells C5, F5, and I5
// If cell value is greater than C18, apply green color
int idx = worksheet.getConditionalFormattings().add();
FormatConditionCollection fcc = worksheet.getConditionalFormattings()
.get(idx);
// Add area C5
CellArea ca = CellArea.createCellArea("C5", "C5");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
FormatCondition fc = fcc.get(idx);
fc.setFormula1("=$C$18");
fc.setOperator(OperatorType.GREATER_THAN);
fc.getStyle().setBackgroundColor(Color.fromArgb(198, 239, 206));
fc.getStyle().getFont().setColor(Color.fromArgb(0, 97, 0));
// Apply CellValue Conditional Formatting on cells C5, F5 and I5
// If cell value is lesser or equal than C18, apply red color
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
// Add area C5
ca = CellArea.createCellArea("C5", "C5");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
fc = fcc.get(idx);
fc.setFormula1("=$C$18");
fc.setOperator(OperatorType.LESS_OR_EQUAL);
fc.getStyle().setBackgroundColor(Color.fromArgb(255, 199, 206));
fc.getStyle().getFont().setColor(Color.fromArgb(156, 0, 6));
// Apply CellValue Conditional Formatting on cells C5, F5 and I5
// If cell value is greater than C18, apply green color
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
// Add area F5
ca = CellArea.createCellArea("F5", "F5");
fcc.addArea(ca);
// Add area I5
ca = CellArea.createCellArea("I5", "I5");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
FormatCondition fc2 = fcc.get(idx);
fc2.setFormula1("=$C$5");
fc2.setOperator(OperatorType.LESS_THAN);
fc2.getStyle().setBackgroundColor(Color.fromArgb(198, 239, 206));
fc2.getStyle().getFont().setColor(Color.fromArgb(0, 97, 0));
// Apply CellValue Conditional Formatting on cells C5, F5 and I5
// If cell value is lesser or equal than C18, apply red color
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
// Add area F5
ca = CellArea.createCellArea("F5", "F5");
fcc.addArea(ca);
// Add area I5
ca = CellArea.createCellArea("I5", "I5");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
fc2 = fcc.get(idx);
fc2.setFormula1("=$C$5");
fc2.setOperator(OperatorType.GREATER_OR_EQUAL);
fc2.getStyle().setBackgroundColor(Color.fromArgb(255, 199, 206));
fc2.getStyle().getFont().setColor(Color.fromArgb(156, 0, 6));
// Apply Top10 conditional formatting on range C17:AA17
// We need to show Top 5 products
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
// Add area C17:AA17
ca = CellArea.createCellArea("C17", "AA17");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.TOP_10);
fc = fcc.get(idx);
fc.getStyle().setBackgroundColor(Color.fromArgb(195, 214, 155));
fc.getTop10().setRank(5);
// Apply Top10 conditional formatting on range C17:AA17
// We need to show Bottom 5 products
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
// Add area C17:AA17
ca = new CellArea();
ca = CellArea.createCellArea("C17", "AA17");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.TOP_10);
fc = fcc.get(idx);
fc.getStyle().setBackgroundColor(Color.fromArgb(217, 150, 148));
fc.getTop10().setRank(5);
fc.getTop10().setBottom(true);
}
完整的应用程序代码
在下面，您将找到应用程序的完整源代码，其中包含用于创建销售 Excel 文件的所有设计和条件格式方法。它经过了广泛的注释，可引导您完成输出文件的创建过程。
import com.aspose.cells.*;
public class ConditionalFormatting {
public static void main(String[] args) {
try {
//Instantiate a new Workbook.
Workbook workbook = new Workbook();
// Set workbook default style
Style style = workbook.getDefaultStyle();
style.getFont().setName("Calibri");
style.getFont().setSize(11);
workbook.setDefaultStyle(style);
//Get Collection of Worksheets
WorksheetCollection worksheets = workbook.getWorksheets();
//Accessing the first worksheet in the Excel file
Worksheet sheet = worksheets.get(0);
//Getting Cells collection
Cells cells = sheet.getCells();
//Populate Data
PopulateData(cells);
//Create Dashboard Design
createDashboardDesign(workbook,cells);
//Calculate all the formulas
workbook.calculateFormula();
//Apply Conditional formatting
ApplyConditionalFormattings(sheet);
//Set Autofit columns
sheet.autoFitColumns();
//Set Worksheet Name
sheet.setName("Sales");
//Save the excel file.
workbook.save("c:\\data\\Sales.xls");
}
catch(Exception exp)
{
System.out.print(exp.getMessage());
}
}
public static void createDashboardDesign(Workbook workbook, Cells cells)
{
//Dashboard Title
cells.get("B2").setValue("Dashboard");
//Range in Dashboard
Range resultRange = cells.createRange("B2", "K8");
resultRange.setOutlineBorders(CellBorderType.MEDIUM,Color.getBlack());
//Range for Sales Details
Range detailRange = cells.createRange("B11", "AA19");
//Setting Titles
createRangeWithBorders(cells,"C4:D4",false);
cells.get("C4").setValue("This Week");
createRangeWithBorders(cells, "F4:G4", false);
cells.get("F4").setValue("Target");
createRangeWithBorders(cells, "I4:J4", false);
cells.get("I4").setValue("Projection");
cells.get("C5").setFormula("=SUM(C17:AA17)");
cells.get("B11").setValue("Sales Numbers");
cells.get("B14").setValue("Product");
cells.get("B15").setValue("Price");
cells.get("B16").setValue("Unit");
cells.get("B17").setValue("Sub Total");
cells.get("B18").setValue("Last Week");
//This Week’s Sales Formula
cells.get("C5").setFormula("=SUM(C17:AA17)");
//Settings Style for titles
setStyleForCells(cells,false,14,"B11");
setStyleForCells(cells,true,11,"B14");
setStyleForCells(cells,true,11,"B15");
setStyleForCells(cells,true,11,"B16");
setStyleForCells(cells,true,11,"B17");
setStyleForCells(cells,false,11,"B18");
setStyleForCells(cells,false,16,"B2");
setStyleForCells(cells, false, 14, "C4");
setStyleForCells(cells, false, 14, "F4");
setStyleForCells(cells, false, 14, "I4");
//Setting the Style of Sales Data Row
Style NumStyle = workbook.createStyle();
FormatDataRows(cells, NumStyle);
createRangeWithBorders(cells,"F5:G6",true);
createRangeWithBorders(cells,"C5:D6",true);
createRangeWithBorders(cells,"I5:J6",true);
setStyleForCells(cells,false,14,"C5");
setStyleForCells(cells,false,14,"F5");
setStyleForCells(cells,false,14,"I5");
SetRangeStyle(resultRange, workbook.createStyle(),Color.getWhite());
SetRangeStyle(detailRange, workbook.createStyle(), Color.fromArgb(242, 242, 242));
}
public static void PopulateData(Cells cells)
{
//Sales Data Parameters
double LastWeekValue = 8000.00;
double TargetValue = 9000.00;
double ProjectionValue = 7500.00;
//Sales Data
String[] ProductNames=new String[]{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","Y","Z"};
double[] Price = { 12.50,12.50,9.50,7.50,3.50,25.00,10.00,9.33,12.89,8.00,12.50,12.50,9.50,7.50,3.50,25.00,10.00,9.33,12.89,8.00,12.50,12.50,9.50,7.50,3.50 };
int[] Unit = {24,34,9,50,28,45,98,11,2,11,47,32,68,5,98,43,15,3,1,23,45,27,21,43,25};
//Import Sales Data to Worksheet
cells.importArray(ProductNames,13,2,false);
cells.importArray(Price,14,2,false);
cells.importArray(Unit,15,2,false);
//Create formula for first Subtotal field
cells.get("C17").setFormula("=C15*C16 ");
//Apply the formula in the to all subtotal fields
for (int i = 3; i <= 26; i++)
{
cells.get(16, i).copy(cells.get(16, 2));
}
//Set Dashboard Values
cells.get("F5").setValue(TargetValue);
cells.get("I5").setValue(ProjectionValue);
cells.get("C18").setValue(LastWeekValue);
}
public static void createRangeWithBorders(Cells cells, String range, boolean border)
{
//Create a Range
Range range1 = cells.createRange(range);
//Merge Cells in Range
range1.merge();
//Set Border for range
if(border)
{
range1.setOutlineBorders(CellBorderType.MEDIUM,Color.getBlack());
}
}
public static void setStyleForCells(Cells cells, boolean fontBold, int size,String cell)
{
//Create a Style object to fetch the Style of C6 Cell.
Style style = cells.get(cell).getStyle();
//Create a Font object
Font font = style.getFont();
//Set the name.
font.setName("Calibri");
//Set the font size.
font.setSize(size);
//Bold the text
font.setBold(fontBold);
//Apply the Style to Cell.
cells.get(cell).setStyle(style);
}
private static void FormatDataRows(Cells cells, Style style)
{
Range Alignment = cells.createRange("C14", "AA17");
Range Price = cells.createRange("C15", "AA15");
Range Total = cells.createRange("C17", "AA17");
//Create style flag, we only want to set number format
StyleFlag sf = new StyleFlag();
style.setHorizontalAlignment(TextAlignmentType.CENTER);
style.setVerticalAlignment(TextAlignmentType.CENTER);
sf.setHorizontalAlignment(true);
sf.setVerticalAlignment(true);
Alignment.applyStyle(style,sf);
style.setNumber(7);
sf.setNumberFormat(true);
//Create your desired range and apply style to it
Price.applyStyle(style, sf);
Total.applyStyle(style, sf);
cells.get("C5").setStyle(style);
cells.get("F5").setStyle(style);
cells.get("I5").setStyle(style);
cells.get("C18").setStyle(style);
style.setBorder(BorderType.TOP_BORDER, CellBorderType.THIN,
Color.getBlack());
StyleFlag flag = new StyleFlag();
flag.setNumberFormat(true);
flag.setTopBorder(true);
Total.applyStyle(style, flag);
}
public static void ApplyConditionalFormattings(Worksheet worksheet)
{
// Apply CellValue Conditional Formatting on cells C5, F5 and I5
// If cell value is greater than C18, apply green color
int idx = worksheet.getConditionalFormattings().add();
FormatConditionCollection fcc = worksheet.getConditionalFormattings()
.get(idx);
// Add area C5
CellArea ca = CellArea.createCellArea("C5", "C5");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
FormatCondition fc = fcc.get(idx);
fc.setFormula1("=$C$18");
fc.setOperator(OperatorType.GREATER_THAN);
fc.getStyle().setBackgroundColor(Color.fromArgb(198, 239, 206));
fc.getStyle().getFont().setColor(Color.fromArgb(0, 97, 0));
// Apply CellValue Conditional Formatting on cells C5, F5 and I5
// If cell value is lesser or equal than C18, apply red color
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
// Add area C5
ca = CellArea.createCellArea("C5", "C5");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
fc = fcc.get(idx);
fc.setFormula1("=$C$18");
fc.setOperator(OperatorType.LESS_OR_EQUAL);
fc.getStyle().setBackgroundColor(Color.fromArgb(255, 199, 206));
fc.getStyle().getFont().setColor(Color.fromArgb(156, 0, 6));
// Apply CellValue Conditional Formatting on cells C5, F5 and I5
// If cell value is greater than C18, apply green color
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
// Add area F5
ca = CellArea.createCellArea("F5", "F5");
fcc.addArea(ca);
// Add area I5
ca = CellArea.createCellArea("I5", "I5");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
FormatCondition fc2 = fcc.get(idx);
fc2.setFormula1("=$C$5");
fc2.setOperator(OperatorType.LESS_THAN);
fc2.getStyle().setBackgroundColor(Color.fromArgb(198, 239, 206));
fc2.getStyle().getFont().setColor(Color.fromArgb(0, 97, 0));
// Apply CellValue Conditional Formatting on cells C5, F5 and I5
// If cell value is lesser or equal than C18, apply red color
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
// Add area F5
ca = CellArea.createCellArea("F5", "F5");
fcc.addArea(ca);
// Add area I5
ca = CellArea.createCellArea("I5", "I5");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.CELL_VALUE);
fc2 = fcc.get(idx);
fc2.setFormula1("=$C$5");
fc2.setOperator(OperatorType.GREATER_OR_EQUAL);
fc2.getStyle().setBackgroundColor(Color.fromArgb(255, 199, 206));
fc2.getStyle().getFont().setColor(Color.fromArgb(156, 0, 6));
// Apply Top10 conditional formatting on range C17:AA17
// We need to show Top 5 products
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
// Add area C17:AA17
ca = CellArea.createCellArea("C17", "AA17");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.TOP_10);
fc = fcc.get(idx);
fc.getStyle().setBackgroundColor(Color.fromArgb(195, 214, 155));
fc.getTop10().setRank(5);
// Apply Top10 conditional formatting on range C17:AA17
// We need to show Bottom 5 products
idx = worksheet.getConditionalFormattings().add();
fcc = worksheet.getConditionalFormattings().get(idx);
// Add area C17:AA17
ca = new CellArea();
ca = CellArea.createCellArea("C17", "AA17");
fcc.addArea(ca);
idx = fcc.addCondition(FormatConditionType.TOP_10);
fc = fcc.get(idx);
fc.getStyle().setBackgroundColor(Color.fromArgb(217, 150, 148));
fc.getTop10().setRank(5);
fc.getTop10().setBottom(true);
}
private static void SetRangeStyle(Range range, Style style, Color color)
{
style.setForegroundColor(color);
style.setPattern(BackgroundType.SOLID);
StyleFlag flag = new StyleFlag();
flag.setCellShading(true);
range.applyStyle(style,flag);
}
}
摘要
在本示例中，我们使用了 Aspose.Cells for Java，这是一个专门开发的 API，旨在使在 Java 应用程序中使用 Microsoft Excel 电子表格尽可能简单。它提供了 Microsoft Excel 中提供的相同条件格式功能，并且无需在开发机器上安装 Excel 即可工作。
条件格式是 Microsoft Excel 用户依赖的许多工具之一，可以使电子表格更易于阅读。上面的应用程序说明以编程方式应用条件格式并不一定很困难。借助正确的工具，Java 开发人员可以创建和格式化复杂的电子表格，从而有效地使用条件格式。