How to apply style and format to Excel using Apache POI

This Example is in addition to our previous in which I explained how to create a Excel file using POI library in Java.

In this tutorial we will see how to change cell color or format Cell.

All look and Feel features are handled using CellStyle Class in POI. Cell background color can be changed by setting FillForegroundColor property in CellStyle Class as mentioned in below class.

We can apply different Cell formatting on cell as in WriteExcel.java Class.
Percentage formatting with 2 decimal formats can be applied as below


cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));


Similarly we can apply date formatting as below:



cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("m/d/yy h:mm"));




package com.mahendra.examples;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.CreationHelper;

public class WriteExcel {
 /**
  * @param args
  * @throws IOException 
  * @throws FileNotFoundException 
  */
 public static void main(String[] args) throws FileNotFoundException, IOException {
  String file = "WriteExcel.xls";

  FileOutputStream fos = new FileOutputStream(new File(file)); // Create FOS for File creation
  HSSFWorkbook workBook = new HSSFWorkbook();// Create new WorkBook
  HSSFSheet sheet = workBook.createSheet(); //Creates Sheet
  HSSFRow row1 = sheet.createRow(0);//Creates First Row

  sheet.setColumnWidth(0, 6000);
  HSSFCell cell1 = row1.createCell(0); //Creates Cell for 1st row.
  cell1.setCellValue("Cell 1 Value as String"); // Sets Cell value.
  HSSFCellStyle cellStyle = workBook.createCellStyle();
  cellStyle.setFillForegroundColor(HSSFColor.LIGHT_BLUE.index);//Sets Cell Colour.
  cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  cellStyle.setWrapText(true);// Wraps Text in Cell
  cell1.setCellStyle(cellStyle); //Apply new Cell Style to Cell

  cell1 = row1.createCell(1); //Creates Cell for 1st row.
  cell1.setCellValue(25); // Sets Cell value.
  cellStyle = workBook.createCellStyle();
  cellStyle.setFillForegroundColor(HSSFColor.GREY_25_PERCENT.index);//Sets Cell Colour.
  cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  cellStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));// Wraps Text in Cell
  cell1.setCellStyle(cellStyle); //Apply new Cell Style to Cell

  cell1 = row1.createCell(2); //Creates Cell for 1st row.
  cell1.setCellValue(new Date()); // Sets Cell value.
  cellStyle = workBook.createCellStyle();
  cellStyle.setFillForegroundColor(HSSFColor.TURQUOISE.index);//Sets Cell Colour.
  cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
  CreationHelper createHelper = workBook.getCreationHelper();
  cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm"));// Wraps Text in Cell
  cell1.setCellStyle(cellStyle); //Apply new Cell Style to Cell

  workBook.write(fos); //Write workbook to file.
  fos.flush();//Flush FileOutputStream to file.
  fos.close();
 }
}


Generated output will look as below:


Download code.

How to write in Excel file using Apache POI in Java


Apache POI is a java library for manipulating and reading Microsoft documents like excel etc. HSSF package in POI allows you to read, modify, and write Excel documents.

Let's create a small java Class to write some data to Excel file, for that first you need to download jar file from its website.

WriteExcel class creates new excel file named "WriteExcel.xls", it writes 5 columns in first row.

Code below:


package com.mahendra.examples;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

public class WriteExcel {
 /**
  * @param args
  * @throws IOException 
  * @throws FileNotFoundException 
  */
 public static void main(String[] args) throws FileNotFoundException, IOException {
  String file = "WriteExcel.xls";

  FileOutputStream fos = new FileOutputStream(new File(file));
  HSSFWorkbook workBook = new HSSFWorkbook();
  HSSFSheet sheet = workBook.createSheet();
  HSSFRow row = sheet.createRow(0);
  HSSFCell cell = null; 
  for(int counter = 0; counter < 5 ; counter++) {
   cell = row.createCell(counter);
   cell.setCellValue("Cell" + counter);
  }
  workBook.write(fos);
  fos.flush();
  fos.close();
 }
}

Output:


Download this code from link.

What is AutoBoxing and Unboxing in Java - Tutorial with examples

Boxing and Unboxing : Introduced in Java 1.5 version, Auto conversion of primitive data type to Wrapper class type is know as boxing and unboxing is exact reverse of this.

Earlier we used to manually do type conversion between wrapper and primitive.

There are few scenarios which we need to take care while doing autoboxing and unboxing.
1. Widening always take precedence than Boxing.
2. Widening always takes precedence than Varargs.
3. boxing always takes precedence than varargs.

I will keep on updating all these articles.

Please comment if you like this article.

All you need to know about ENUM in JAVA

Java enums are special java types used to define costants. Enum in Java is a keyword, which act as a special class. It can contain constants, methods etc. Enums were first introduced in java 1.5.

Example: 

public enum DayOfWeek{
SUNDAY,
MONDAY,
TUESDAY,
WEDNESDAY,
THURSDAY,
FRIDAY,
SATURDAY
}

you can refer to enum as below:

DayOfWeek  day = DayOfWeek.FRIDAY;

Here variable day is of type DayOfWeek, which can take one of the value DayOfWeek enum constant value.

Enums by default are final and static.