Monday, December 5, 2011

Set Background Color and Add Border to a Cell in an Excel File Using Java and POI

To create an Excel file and style a cell with a background color and a border in Java we can use Apache's POI library. First a HSSFWorkbook object is created and a HSSFSheet object is added. A HSSFRow is added to the sheet, and a HSSFCell is added to the row. To add a String to the cell, a HSSFRichTextString is used. To add a background color to the cell and add a border, a HSSFCellStyle object is applied to the cell. The following sample code was successfully tested with POI versions 3.6 and 3.7.


import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
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;

// The following example code demonstrates how to create an Excel  
// file using the org.apache.poi library and style the cell by setting
// its background color and adding a border.

public class StyleExcelFileCells {

    public static void main(String[] args) {

        HSSFWorkbook workbook = new HSSFWorkbook();

        HSSFSheet firstSheet = workbook.createSheet("Sheet 1");

        // Write a String in Cell 2B
        HSSFRow row1 = firstSheet.createRow(1);
        HSSFCell cell2B = row1.createCell(1);
        cell2B.setCellValue(new HSSFRichTextString("Sample String"));

        // Style Cell 2B
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle = workbook.createCellStyle();
        cellStyle.setBorderTop((short) 1); // single line border
        cellStyle.setBorderBottom((short) 1); // single line border

        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(new File("/Temp/Test4.xls"));
        } catch (IOException e) {
        } finally {
            if (fileOutputStream != null) {
                try {
                } catch (IOException e) {

Here is a screenshot of the generated Excel File:

Piece of cake!!!


Anonymous said...

Excellent, to the point. Just what I was searching for.

$URYA said...

nice example...