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 java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;

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.setFillForegroundColor(HSSFColor.YELLOW.index);
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        cellStyle.setBorderTop((short) 1); // single line border
        cellStyle.setBorderBottom((short) 1); // single line border
        cell2B.setCellStyle(cellStyle);

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

Here is a screenshot of the generated Excel File:


Piece of cake!!!

2 comments:

Anonymous said...

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

Unknown said...

nice example...