In the previous tutorial, I have explained about How to add Formulas in Excel in Java. In this tutorial, I will explain How to customize the style of cells in Excel in Java using Apache POI.
I’m using Apache POI to write data to the excel file. To download and install Apache POI, refer here.
If you are using maven, then you need to add below dependency in pom.xml.
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
To know more about various interfaces and classes for managing Excel, please refer to this tutorial.
In the below example, I have an existing excel with the name of “EmployeeDetails.xlsx”.
Steps to Customize the style in Excel
Step 1 – Create a blank work and then a sheet with name Write_TestData.
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.createSheet("Write_TestData");
Step 2- XSSFWorkbook and XSSFSheet are imported from the below consecutive packages.
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFSheet;
Step 3 – Write data to the sheet. To know more about this part, refer this link.
Step 4 – Set the auto Size for column 0.
sheet.autoSizeColumn(0);
Step 5 – To create a cell style, we only need a reference to the excel workbook:
CellStyle style = workbook.createCellStyle();
Step 6 – CellStyle is imported from package mentioned below.
import org.apache.poi.ss.usermodel.CellStyle;
Step 7 – To create font style and add attributes to this font
XSSFFont font = workbook.createFont();
Step 8 – XSSFFont is imported from below package
import org.apache.poi.xssf.usermodel.XSSFFont;
Step 9 – To set the Background Colour of the cell.
style.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
Step 10 – BackgroundColor and FillPattern are imported from the below consecutive packages.
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.FillPatternType;
Step 11 – Write data to an OutputStream. Use the below code to write output stream.
FileOutputStream out = new FileOutputStream(new File("Styled_EmployeeDetails.xlsx"));
workbook.write(out);
Below is the complete program to show various cell setyles for Row 0 (Header Row).
import java.io.File;
import java.io.FileOutputStream;
import java.util.ArrayList;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFFont;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelStyleExample {
public static void main(String[] args) {
try {
// create blank workbook
XSSFWorkbook workbook = new XSSFWorkbook();
// Create a blank sheet
XSSFSheet sheet = workbook.createSheet("Write_TestData");
ArrayList<Object[]> data = new ArrayList<Object[]>();
data.add(new String[] { "Name", "Id", "Salary" });
data.add(new Object[] { "Jim Lawrence", "001A", 10000 });
data.add(new Object[] { "Jack", "1001B", 40000 });
data.add(new Object[] { "Tim", "2001C", 20000 });
data.add(new Object[] { "Gina", "1004S", 30000 });
// Iterate over data and write to sheet
int rownum = 0;
for (Object[] employeeDetails : data) {
// Create Row
XSSFRow row = sheet.createRow(rownum++);
int cellnum = 0;
for (Object obj : employeeDetails) {
// Create cell
XSSFCell cell = row.createCell(cellnum++);
// Set value to cell
if (obj instanceof String)
cell.setCellValue((String) obj);
else if (obj instanceof Double)
cell.setCellValue((Double) obj);
else if (obj instanceof Integer)
cell.setCellValue((Integer) obj);
// Set Column width to the maximum for Column 0
sheet.autoSizeColumn(0);
if (rownum == 1) {
CellStyle style = null;
// Creating a font
XSSFFont font = workbook.createFont();
font.setFontHeightInPoints((short) 10);
font.setFontName("Verdana");
font.setColor(IndexedColors.BLACK.getIndex());
font.setBold(true);
font.setItalic(false);
// Creating cell Style
style = workbook.createCellStyle();
// Setting Foreground Colour
style.setFillForegroundColor(IndexedColors.BLUE_GREY.getIndex());
style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
// Setting Alignment of font
style.setAlignment(HorizontalAlignment.CENTER);
// Setting font to style
style.setFont(font);
// Setting cell style
cell.setCellStyle(style);
}
}
}
// Write the workbook in file system
FileOutputStream out = new FileOutputStream(new File("Styled_EmployeeDetails.xlsx"));
workbook.write(out);
System.out.println("Style of Excel is updated successfully");
out.close();
// Close workbook
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
QA Automation Expert 