Applying 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.11.0</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.
