Apache POI
Apache POI is a Java API to read/write Microsoft documents (MS Excel, MS Word, MS PowerPoint and MS Outlook). In this post I will explain in-detail about the api to be used for MS Excel for various usecases with examples.

Before we start, we should know few terms adapted by Apache POI to understand their usage according to our needs.
HSSF is the POI Project’s pure Java implementation of the Excel ’97(-2007) file format. XSSF is the POI Project’s pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.

Office Open XML (OOXML or OpenXML) is a XML-based file format developed by Microsoft for representing spreadsheets, charts, presentation and word processing documents.

Before we get started we need to download the POI jars that are needed. Add below dependency to your POI. (Note: that I am using MS Office 2007 for this tutorial)

if you are not using Maven then include below jars in your classpath

Now Let’s get started on creating a Excel.

1. Create/Write Excel

Create Workbook
Workbook is an interface you will have to implment to create an Excel. XSSFWorkbook will have to be used for excels with ‘xlsx’ extension and HSSFWorkbook for ‘xls’ extension. Once a workbook is created, contents have to be written to an OutputStream to be able to stream output to your needs. In this example FileOutputStreamn is being used to be outputed to a file.

Create Sheets
There are few restrictions on naming a sheet. For example: Sheet’s name can’t be more than 31 chars and name can’t contains ‘:’, ‘\’, ‘*’, ‘?’, ‘/’, ‘[‘, ‘]’.

Apache POI Sheets

WorkbookUtil.createSafeSheetName() is a utility method that takes care of all restrictions. It strips out all restricted characters to replaces them with space

Create Rows and Cells and Set values
First we create a row and add cells to this row. Row is created by using createRow(rowNumber). createCell(cellNumber) and setCellValue(value) is used for creating a cell and setting value to the cell.

Row and Cells Apache POI

Dates and Styling of dates
Dates can be set as java.util.Date or java.util.Calendar. Also we need to add style to make sure we see a readable format.

Dates Apache POI

Performance Considerations
Opening a workbook as java.util.File is desirable over java.util.Inputstream as File consumes less memory over InputStream.

Styling:
Styling Apache POI

  • Alignment: We can set HorizontalAlignment and VerticalAlignment in a call by setting setAlignment and setVerticalAlignment in CellStyle respectively.
    Valid Horizontal Alignment values are

    • CellStyle.ALIGN_GENERAL
    • CellStyle.ALIGN_CENTER
    • CellStyle.ALIGN_CENTER_SELECTION
    • CellStyle.ALIGN_FILL
    • CellStyle.ALIGN_JUSTIFY
    • CellStyle.ALIGN_LEFT
    • CellStyle.ALIGN_RIGHT

    Valid Vertical Alignment values are

    • CellStyle.VERTICAL_TOP
    • CellStyle.VERTICAL_BOTTOM
    • CellStyle.VERTICAL_CENTER
    • CellStyle.VERTICAL_JUSTIFY

  • Border: Border can be set by setBorderBottom, setBorderLeft, setBorderRight, setBorderTop methods in CellStyle. Also Border Color can be set by setBottomBorderColor, setLeftBorderColor, setRightBorderColor, setTopBorderColor methods.
    Valid Border Styles are

    • CellStyle.BORDER_THIN
    • CellStyle.BORDER_MEDIUM_DASHED

  • ForegroundColor: setFillForegroundColor(colorIndex) and setFillPattern in can be used for this feature.

Merging of cells
Cells can be merged by using addMergedRegion() method in Sheet.
Merged Cells Apache POI

Using New lines:
We can use new line character (‘\n’) to show text in new line and setsetWrapText in CellSyle to true. Also we will have increase the Row height to accomodate the new line. setHeightInPoints in Row can be used for this purpose.
Wrap Text Apache POI

Set Print Area:
Print Area can be set up using setPrintArea

Note:
While executing the program, please make sure that you haven’t opened a excel created in prevous iteration. This will make the program throw error.

Formulas:
We can use Cell setCellFormula("formulaString") to add formula and getCellFormula to get formula. Formulas to be set are same as the ones we set in Excel except don’t prepend the formula with ‘=’.

2. Read Excel

Iterate to Sheet, Row, Cell: We can iterate through Workbook to sheet to Row to cell. A snippet is as shown below

Read Cell Values: We will have to know the CellType before we can read the cell value. If this not done according you will be hit with java.lang.IllegalStateException exception.

3. Examples