Tuesday, December 6, 2011

Set Font Style in an Excel File Using Java and POI

To create an Excel file and style a cell's font 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 sent the font style of a cell's content, a HSSFFont object is created, customized, and applied to a HSSFCellStyle object which in turn is applied to a cell in the spreadsheet. 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.HSSFFont;
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 font in a cell.

public class StyleExcelFileFont {

    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 Font in Cell 2B
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        cellStyle = workbook.createCellStyle();
        HSSFFont hSSFFont = workbook.createFont();
        hSSFFont.setFontName(HSSFFont.FONT_ARIAL);
        hSSFFont.setFontHeightInPoints((short) 16);
        hSSFFont.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
        hSSFFont.setColor(HSSFColor.GREEN.index);
        cellStyle.setFont(hSSFFont);
        cell2B.setCellStyle(cellStyle);

        FileOutputStream fileOutputStream = null;
        try {
            fileOutputStream = new FileOutputStream(new File("/Temp/Test5.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!!!

4 comments:

Unknown said...

You rock!! Exactly what I needed (changing the font size), but took so long to find it. Thanks a million!

Anonymous said...

nice, thanks just in time

Anonymous said...

Thank for post it helped really a lot
I have created more cell but cant format them only A1 (my first postion) cell is getting formatted. Suggest something for multiple formating.

Muhammed Ali Khan said...

Thanks. It works.