Apache POI is an open-source java library to create and manipulate various file formats based on Microsoft Office. Using POI, one should be able to perform create, modify and display/read operations on the following file formats. For Example, Java doesn’t provide built-in support for working with excel files, so we need to look for open-source APIs for the job. In this article, we are going to discuss how to fill the background colors and foreground colors of the cells in Excel using the Apache POI. First, we will create an Excel file and enter the cell values and add the colors for each cell. Apache POI is providing the createcellstyle method in XSSFCellStyle.
XSSFCellStyle style=workbook.createCellStyle();
Pre-Requisite
To work with the Apache POI, we need the following software in our system
- Check that your system has Java JDK
- Install the Eclipse IDE for Java developers from here.
- Create a Maven project
For creating a maven project Check this How to Create a Selenium Maven Project with Eclipse to Open Chrome Browser?
- Add the Apache POI and Apache-ooxml dependency into the POM.xml file
After installing all the dependencies, we are now ready to write the code, it is styled using the creatcellstyle method. The style method provides many designs such as setFillBackgroundColor, setFillForegroundColor and setFillPattern.
setFillgroundColor:
This method enables us to set the background color of the cell, the Apache POI dependency provides us with the Indexed color class that has all the colors.
style.setFillForegroundColor(IndexedColors.”COLOR.”getIndex());
setFillForegroundColor:
This method is also similar to the setBackgroundColor.
style.setFillForegroundColor(IndexedColors.“COLOR”.getIndex());
setFillPattern:
This method provides various types of design patterns like Big spots, Briks, Diamond, Fine Dots, etc.
style.setFillPattern(FillPatternType.DIAMONDS);
Program for fill background and foreground colors in Excel using Apache POI
Java
package GFG_Maven.GFG_MAven;import org.testng.annotations.Test; import java.io.FileOutputStream;import java.io.IOException; import org.apache.poi.ss.usermodel.Cell;import org.apache.poi.ss.usermodel.FillPatternType;import org.apache.poi.ss.usermodel.IndexedColors;import org.apache.poi.ss.usermodel.Row;import org.apache.poi.ss.usermodel.WorkbookFactory;import org.apache.poi.xssf.usermodel.XSSFCell;import org.apache.poi.xssf.usermodel.XSSFCellStyle;import org.apache.poi.xssf.usermodel.XSSFRow;import org.apache.poi.xssf.usermodel.XSSFSheet;import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class Geeks { @Test public void geekforgeeks() throws IOException{ XSSFWorkbook workbook=new XSSFWorkbook(); XSSFSheet sheet=workbook.createSheet("sheet1"); XSSFRow row=sheet.createRow(1); // Background color XSSFCellStyle style=workbook.createCellStyle(); style.setFillBackgroundColor(IndexedColors.BRIGHT_GREEN.getIndex()); style.setFillPattern(FillPatternType.DIAMONDS); XSSFCell cell=row.createCell(1); cell.setCellValue("welcome"); cell.setCellStyle(style); // foreground color style=workbook.createCellStyle(); style.setFillForegroundColor(IndexedColors.YELLOW.getIndex()); style.setFillPattern(FillPatternType.FINE_DOTS); cell=row.createCell(2); cell.setCellValue("Geeks"); cell.setCellStyle(style); FileOutputStream file = new FileOutputStream("C:\\Users\\ADMIN\\Desktop\\style.xlsx"); workbook.write(file); file.close(); System.out.println("Style Created"); }} |
Code Explanation
- Create a sheet and row for the created sheet.
- After that create the style for the workbook
XSSFCellStyle style=workbook.createCellStyle();
- Then style the background and foreground by setFillBackgroundColor and setFillPattern, then give the cell value and cell style.
cell.setCellValue(“Geeks”);
cell.setCellStyle(style);
- After the styles are done now create the File output stream
- Write the cell data into the output stream.
Output
Now run the code in the eclipse, we can get the output as style is created and we can notice that an Excel sheet is created in the specified location with the cell styles.
