Conditional Formatting (6)
Data bars are a type of conditional formatting Microsoft Excel offers for visualizing the values in Excel cells. They can help you compare the values quickly because a cell with a longer bar represents a larger value, while a cell with a shorter bar represents a smaller value. This article will introduce how to add data bars in a range of cells using Spire.XLS for Java.
Install Spire.XLS for Java
First of all, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.
<repositories> <repository> <id>com.e-iceblue</id> <name>e-iceblue</name> <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url> </repository> </repositories> <dependencies> <dependency> <groupId>e-iceblue</groupId> <artifactId>spire.xls</artifactId> <version>14.3.2</version> </dependency> </dependencies>
Add Data Bars in Excel
The following are steps to add data bars in a range of Excel cells through conditional formatting:
- Create a Workbook instance.
- Load an Excel file using Workbook.loadFromFile() method.
- Get the worksheets collection using Workbook.getWorksheets() method, and then get the first worksheet using WorksheetsCollection.get() method.
- Get a specific cell range using Worksheet.getCellRange() method.
- Add a new conditional formatting to the cell range using ConditionalFormats. addCondition(), and then set the type of the new conditional formatting to DataBar using ConditionalFormatWrapper.setFormatType() method.
- Set the color of the data bar using DataBar.setBarColor() method.
- Save the document to another file using Workbook.saveToFile() method.
- Java
import com.spire.xls.*; import com.spire.xls.core.*; import java.awt.*; public class applyDataBars { public static void main(String[] args) { //Create a Workbook instance Workbook workbook = new Workbook(); //Load an Excel file workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\test.xlsx"); //Get the first worksheet. Worksheet sheet = workbook.getWorksheets().get(0); //Get the specific cell range CellRange range = sheet.getCellRange("B2:B13"); //Add the conditional formatting of data bars in the cell range IConditionalFormat format = range.getConditionalFormats().addCondition(); format.setFormatType( ConditionalFormatType.DataBar); //Set color for the data bars format.getDataBar().setBarColor( Color.GREEN); //Save to file workbook.saveToFile("ApplyDataBars.xlsx", ExcelVersion.Version2013); } }
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
This article will demonstrate how to add the traffic lights icons in Java applications by using Spire.XLS for Java.
import com.spire.xls.*; import com.spire.xls.core.IConditionalFormat; import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats; import java.awt.*; public class setTrafficLightsIcons { public static void main(String[] args) { //Create a workbook Workbook workbook = new Workbook(); //Add a worksheet Worksheet sheet = workbook.getWorksheets().get(0); //Add some data to the cell range and set the format for them sheet.getCellRange("A1").setText("Traffic Lights"); sheet.getCellRange("A2").setNumberValue(0.95); sheet.getCellRange("A2").setNumberFormat("0%"); sheet.getCellRange("A3").setNumberValue(0.5); sheet.getCellRange("A3").setNumberFormat("0%"); sheet.getCellRange("A4").setNumberValue(0.1); sheet.getCellRange("A4").setNumberFormat("0%"); sheet.getCellRange("A5").setNumberValue(0.9); sheet.getCellRange("A5").setNumberFormat("0%"); sheet.getCellRange("A6").setNumberValue(0.7); sheet.getCellRange("A6").setNumberFormat("0%"); sheet.getCellRange("A7").setNumberValue(0.6); sheet.getCellRange("A7").setNumberFormat("0%"); //Set the height of row and width of column for Excel cell range sheet.getAllocatedRange().setRowHeight(20); sheet.getAllocatedRange().setColumnWidth(25); //Add a conditional formatting XlsConditionalFormats conditional = sheet.getConditionalFormats().add(); conditional.addRange(sheet.getAllocatedRange()); IConditionalFormat format1 = conditional.addCondition(); //Add a conditional formatting of cell range and set its type to CellValue format1.setFormatType(ConditionalFormatType.CellValue); format1.setFirstFormula("300"); format1.setOperator(ComparisonOperatorType.Less); format1.setFontColor(Color.black); format1.setBackColor(Color.lightGray); //Add a conditional formatting of cell range and set its type to IconSet conditional.addRange(sheet.getAllocatedRange()); IConditionalFormat format = conditional.addCondition(); format.setFormatType(ConditionalFormatType.IconSet); format.getIconSet().setIconSetType(IconSetType.ThreeTrafficLights1); //Save to file String result = "output/setTrafficLightsIcons_result.xlsx"; workbook.saveToFile(result, ExcelVersion.Version2013); } }
Effective screenshot of traffic lights icons on Excel worksheet:
Java: Highlight Values Above or Below Average in Excel
2022-09-30 09:04:00 Written by support iceblueIf you have a lot of data in Excel, finding the special values can be challenging. In this situation, you can use the conditional formatting to automatically highlight the cells that contain the value that meets certain criteria. This article introduces how to highlight values that are above or below the average using conditional formatting in Java, using Spire.XLS for Java.
Install Spire.XLS for Java
First, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.
<repositories> <repository> <id>com.e-iceblue</id> <name>e-iceblue</name> <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url> </repository> </repositories> <dependencies> <dependency> <groupId>e-iceblue</groupId> <artifactId>spire.xls</artifactId> <version>14.3.2</version> </dependency> </dependencies>
Highlight Values Above or Below Average Excel in Java
Below are the steps to highlight values above or below average in Excel using Spire.XLS for Java.
- Create a Workbook object.
- Load an Excel file using Workbook.loadFromFile() method.
- Get a specific worksheet from the workbook using Workbook.getWorksheets.get(index) method.
- Add a conditional formatting to the worksheet using Worksheet.getConditionalFormats().add() method and return an object of XlsConditionalFormats class.
- Set the cell range where the conditional formatting will be applied using XlsConditionalFormats.AddRange() method.
- Add an average condition using XlsConditionalFormats.addAverageCondition() method, specify the average type to above and change the background color of the cells that meet the condition to yellow.
- Add another average condition to change the background color of the cells that contain the value below average to light gray.
- Save the workbook to an Excel file using Workbook.saveToFile() method.
- Java
import com.spire.xls.AverageType; import com.spire.xls.ExcelVersion; import com.spire.xls.Workbook; import com.spire.xls.Worksheet; import com.spire.xls.core.IConditionalFormat; import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats; import java.awt.*; public class HighlightValuesAboveAndBelowAverage { public static void main(String[] args) { //Create a Workbook object Workbook workbook = new Workbook(); //Load an Excel file workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx"); //Get the first worksheet Worksheet sheet = workbook.getWorksheets().get(0); //Add a conditional format to the worksheet XlsConditionalFormats format = sheet.getConditionalFormats().add(); //Set the range where the conditional format will be applied format.addRange(sheet.getRange().get("F2:F14")); //Add a condition to highlight values above average with yellow IConditionalFormat condition1 = format.addAverageCondition(AverageType.Above); condition1.setBackColor(Color.yellow); //Add a condition to highlight values below average with light gray IConditionalFormat condition2 = format.addAverageCondition(AverageType.Below); condition2.setBackColor(Color.lightGray); //Get the count of values below average sheet.getRange().get("F17").setFormula("=COUNTIF(F2:F14,\"<\"&AVERAGE(F2:F14))"); //Get the count of values above average sheet.getRange().get("F18").setFormula("=COUNTIF(F2:F14,\">\"&AVERAGE(F2:F14))"); //Save the workbook to an Excel file workbook.saveToFile("output/HighlightValues.xlsx", ExcelVersion.Version2016); } }
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
This article demonstrates how to highlight the highest and lowest value in a cell rang through conditional formatting. You can also highlight the top 5 or bottom 5 values by passing 5 to setRank() method in the code snippet below.
import com.spire.xls.*; import java.awt.*; public class HighlightTopBottom { public static void main(String[] args) { //Create a Workbook object Workbook workbook = new Workbook(); //Load the sample Excel file workbook.loadFromFile("G:\\360MoveData\\Users\\Administrator\\Desktop\\sales report.xlsx"); //Get the first worksheet Worksheet sheet = workbook.getWorksheets().get(0); //Apply conditional formatting to range "B2:E5" to highlight the highest value ConditionalFormatWrapper format1 = sheet.getCellRange("B2:E5").getConditionalFormats().addCondition(); format1.setFormatType(ConditionalFormatType.TopBottom); format1.getTopBottom().setType(TopBottomType.Top); format1.getTopBottom().setRank(1); format1.setBackColor(Color.red); //Apply conditional formatting to range "B2:E5" to highlight the lowest value ConditionalFormatWrapper format2 = sheet.getCellRange("B2:E5").getConditionalFormats().addCondition(); format2.setFormatType(ConditionalFormatType.TopBottom); format2.getTopBottom().setType(TopBottomType.Bottom); format2.getTopBottom().setRank(1); format2.setBackColor(Color.yellow); //Save the document workbook.saveToFile("output/HighestLowestValue.xlsx", ExcelVersion.Version2016); } }
Highlight Dulicate and Unique Values in Excel in Java
2020-04-08 02:00:56 Written by support iceblueThis article demonstrates how to highlight the duplicate and unique values in a selected range through conditional formatting using Spire.XLS for Java.
import com.spire.xls.*; import java.awt.*; public class HighlightDuplicates { public static void main(String[] args) { //Create a Workbook instance Workbook workbook = new Workbook(); //Load a sample Excel file workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx"); //Get the first worksheet Worksheet sheet = workbook.getWorksheets().get(0); //Use conditional formatting to highlight duplicate values in the range "A2:A11" with red ConditionalFormatWrapper format1 = sheet.getCellRange("A2:A11").getConditionalFormats().addCondition(); format1.setFormatType(ConditionalFormatType.DuplicateValues); format1.setBackColor(Color.red); //Use conditional formatting to highlight unique values in the range "A2:A11" with yellow ConditionalFormatWrapper format2 = sheet.getCellRange("A2:A11").getConditionalFormats().addCondition(); format2.setFormatType(ConditionalFormatType.UniqueValues); format2.setBackColor(Color.yellow); //Save the document workbook.saveToFile("HighlightDuplicates.xlsx", ExcelVersion.Version2016); } }
Java: Apply Color to Alternate Rows in Excel Using Conditional Formatting
2022-09-14 07:45:00 Written by support iceblueApplying different background colors to alternate rows of Excel can improve the readability of data and make the spreadsheet appear more professional. There many ways to set row color, among which using conditional formatting is a good choice. It can not only set colors in batches, but also define more flexible rules, such as alternating every three rows. In this article, you will learn how to alternate row color in Excel using conditional formatting using Spire.XLS for Java.
Install Spire.XLS for Java
First, you're required to add the Spire.Xls.jar file as a dependency in your Java program. The JAR file can be downloaded from this link. If you use Maven, you can easily import the JAR file in your application by adding the following code to your project's pom.xml file.
<repositories> <repository> <id>com.e-iceblue</id> <name>e-iceblue</name> <url>https://repo.e-iceblue.com/nexus/content/groups/public/</url> </repository> </repositories> <dependencies> <dependency> <groupId>e-iceblue</groupId> <artifactId>spire.xls</artifactId> <version>14.3.2</version> </dependency> </dependencies>
Apply Color to Alternate Rows in Excel Using Conditional Formatting
The following are the steps to add color to alternative rows in Excel using Spire.XLS for Java.
- Create a Workbook object.
- Load an Excel file using Workbook.loadFromFile() method.
- Get a specific worksheet from the workbook using Workbook.getWorsheets().get(index) method.
- Add a conditional formatting to the worksheet using Worksheet.getConditionalFormats().add() method and return an object of XlsConditionalFormats class.
- Set the cell range where the conditional formatting will be applied using XlsConditionalFormats.addRange() method.
- Add a condition using XlsConditionalFormats.addCondition() method, then set the conditional formula and the cell color of even rows.
- Add another condition to change the format of the cells of odd rows.
- Save the workbook to an Excel file using Workbook.saveToFile() method.
- Java
import com.spire.xls.ConditionalFormatType; import com.spire.xls.ExcelVersion; import com.spire.xls.Workbook; import com.spire.xls.Worksheet; import com.spire.xls.core.IConditionalFormat; import com.spire.xls.core.spreadsheet.collections.XlsConditionalFormats; import java.awt.*; public class AlternateRowColors { public static void main(String[] args) { //Create a Workbook object Workbook workbook = new Workbook(); //Load an Excel file workbook.loadFromFile("C:\\Users\\Administrator\\Desktop\\sample.xlsx"); //Get the first worksheet Worksheet sheet = workbook.getWorksheets().get(0); //Add a conditional format to the worksheet XlsConditionalFormats format = sheet.getConditionalFormats().add(); //Set the range where the conditional format will be applied format.addRange(sheet.getRange().get(2,1,sheet.getLastRow(),sheet.getLastColumn())); //Add a condition to change the format of the cells based on formula IConditionalFormat condition1 = format.addCondition(); condition1.setFirstFormula("=MOD(ROW(),2)=0"); condition1.setFormatType(ConditionalFormatType.Formula); condition1.setBackColor(Color.YELLOW); //Add another condition to change the format of the cells based on formula IConditionalFormat condition2 = format.addCondition(); condition2.setFirstFormula("=MOD(ROW(),2)=1"); condition2.setFormatType(ConditionalFormatType.Formula); condition2.setBackColor(new Color(32,178, 170)); //Save the workbook to an Excel file workbook.saveToFile("output/AlternateRowColors.xlsx", ExcelVersion.Version2016); } }
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.