

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)

<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.13</version> </dependency> 1 2 3 4 5 <dependency> <groupId> org.apache.poi </groupId> <artifactId> poi-ooxml </artifactId> <version> 3.13 </version> </dependency>

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

poi-ooxml-3.13.jar poi-3.13.jar commons-codec-1.9.jar poi-ooxml-schemas-3.13.jar xmlbeans-2.6.0.jar star-api-1.0.1.jar 1 2 3 4 5 6 poi - ooxml - 3.13.jar poi - 3.13.jar commons - codec - 1.9.jar poi - ooxml - schemas - 3.13.jar xmlbeans - 2.6.0.jar star - api - 1.0.1.jar

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 WorkBook //XSSFWorkbook is used to generate files with .xlsx extension. //You can use HSSFWorkbook if you want to generate file with .xls extension. Workbook wb = new XSSFWorkbook(); FileOutputStream fileOut = new FileOutputStream("C:\\POI_Tutorial.xlsx"); //Code to add content is added here wb.write(fileOut); fileOut.close(); 1 2 3 4 5 6 7 8 //Create WorkBook //XSSFWorkbook is used to generate files with .xlsx extension. //You can use HSSFWorkbook if you want to generate file with .xls extension. Workbook wb = new XSSFWorkbook ( ) ; FileOutputStream fileOut = new FileOutputStream ( "C:\\POI_Tutorial.xlsx" ) ; //Code to add content is added here wb . write ( fileOut ) ; fileOut . close ( ) ;

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 ‘:’, ‘\’, ‘*’, ‘?’, ‘/’, ‘[‘, ‘]’.

Sheet sheet1 = wb.createSheet("ShoppingList - Sheet1"); Sheet sheet2 = wb.createSheet("SriniChekuri - Sheet2"); 1 2 Sheet sheet1 = wb . createSheet ( "ShoppingList - Sheet1" ) ; Sheet sheet2 = wb . createSheet ( "SriniChekuri - Sheet2" ) ;

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

//Creating sheets //There are few restrictions on creating a sheet name //Eg: name can't exceed 31 chars, Chars like ':', '\', '*', '?', '/', '[', ']' can't be used. //Use WorkbookUtil.createSafeSheetName() to make sure that sheet name is compitable //Restricted character ':' will be removed and will be named 'ShoppingList Sheet1' and 'SriniChekuri Sheet2' Sheet sheet1 = wb.createSheet(WorkbookUtil.createSafeSheetName("ShoppingList : Sheet1")); Sheet sheet2 = wb.createSheet(WorkbookUtil.createSafeSheetName("SriniChekuri : Sheet2")); 1 2 3 4 5 6 7 8 //Creating sheets //There are few restrictions on creating a sheet name //Eg: name can't exceed 31 chars, Chars like ':', '\', '*', '?', '/', '[', ']' can't be used. //Use WorkbookUtil.createSafeSheetName() to make sure that sheet name is compitable //Restricted character ':' will be removed and will be named 'ShoppingList Sheet1' and 'SriniChekuri Sheet2' Sheet sheet1 = wb . createSheet ( WorkbookUtil . createSafeSheetName ( "ShoppingList : Sheet1" ) ) ; Sheet sheet2 = wb . createSheet ( WorkbookUtil . createSafeSheetName ( "SriniChekuri : Sheet2" ) ) ;

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.

//Creating Cells // Create a row and put some cells in it. Rows are 0 based. CreationHelper createHelper = wb.getCreationHelper(); Row row = sheet1.createRow((short)0); // Create a cell and put a value in it. Cell cell = row.createCell(0); cell.setCellValue(1); //Create cells in row and set values row.createCell(1).setCellValue(1000); row.createCell(2).setCellValue(createHelper.createRichTextString("http://www.srinichekuri.com")); row.createCell(3).setCellValue(true); 1 2 3 4 5 6 7 8 9 10 11 12 //Creating Cells // Create a row and put some cells in it. Rows are 0 based. CreationHelper createHelper = wb . getCreationHelper ( ) ; Row row = sheet1 . createRow ( ( short ) 0 ) ; // Create a cell and put a value in it. Cell cell = row . createCell ( 0 ) ; cell . setCellValue ( 1 ) ; //Create cells in row and set values row . createCell ( 1 ) . setCellValue ( 1000 ) ; row . createCell ( 2 ) . setCellValue ( createHelper . createRichTextString ( "http://www.srinichekuri.com" ) ) ; row . createCell ( 3 ) . setCellValue ( true ) ;

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.

//Working with Dates row.createCell(4).setCellValue(new Date()); //A new CellStyle has to be created for date to make sure we don't effect styling of all cells in Excel CellStyle cellStyle = wb.createCellStyle(); cellStyle.setDataFormat(createHelper.createDataFormat().getFormat("m/d/yy h:mm")); cell = row.createCell(5); cell.setCellValue(new Date()); cell.setCellStyle(cellStyle); //you can also set date as java.util.Calendar cell = row.createCell(6); cell.setCellValue(Calendar.getInstance()); cell.setCellStyle(cellStyle); 1 2 3 4 5 6 7 8 9 10 11 12 13 14 //Working with Dates row . createCell ( 4 ) . setCellValue ( new Date ( ) ) ; //A new CellStyle has to be created for date to make sure we don't effect styling of all cells in Excel CellStyle cellStyle = wb . createCellStyle ( ) ; cellStyle . setDataFormat ( createHelper . createDataFormat ( ) . getFormat ( "m/d/yy h:mm" ) ) ; cell = row . createCell ( 5 ) ; cell . setCellValue ( new Date ( ) ) ; cell . setCellStyle ( cellStyle ) ; //you can also set date as java.util.Calendar cell = row . createCell ( 6 ) ; cell . setCellValue ( Calendar . getInstance ( ) ) ; cell . setCellStyle ( cellStyle ) ;

Performance Considerations

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

Styling:



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 Cell cell = row.createCell(0); cell.setCellValue(1); CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(CellStyle.ALIGN_CENTER); cellStyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); cell.setCellStyle(cellStyle); 1 2 3 4 5 6 Cell cell = row . createCell ( 0 ) ; cell . setCellValue ( 1 ) ; CellStyle cellStyle = wb . createCellStyle ( ) ; cellStyle . setAlignment ( CellStyle . ALIGN_CENTER ) ; cellStyle . setVerticalAlignment ( CellStyle . VERTICAL_CENTER ) ; cell . setCellStyle ( cellStyle ) ;

We can set HorizontalAlignment and VerticalAlignment in a call by setting and in respectively. Valid Horizontal Alignment values are 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 Cell cell = row.createCell(1); CellStyle style = wb.createCellStyle(); style.setBorderBottom(CellStyle.BORDER_THIN); style.setBottomBorderColor(IndexedColors.BLUE.getIndex()); style.setBorderLeft(CellStyle.BORDER_THIN); style.setLeftBorderColor(IndexedColors.BLUE.getIndex()); style.setBorderRight(CellStyle.BORDER_THIN); style.setRightBorderColor(IndexedColors.BLUE.getIndex()); style.setBorderTop(CellStyle.BORDER_THIN); style.setTopBorderColor(IndexedColors.BLUE.getIndex()); cell.setCellStyle(style); 1 2 3 4 5 6 7 8 9 10 11 Cell cell = row . createCell ( 1 ) ; CellStyle style = wb . createCellStyle ( ) ; style . setBorderBottom ( CellStyle . BORDER_THIN ) ; style . setBottomBorderColor ( IndexedColors . BLUE . getIndex ( ) ) ; style . setBorderLeft ( CellStyle . BORDER_THIN ) ; style . setLeftBorderColor ( IndexedColors . BLUE . getIndex ( ) ) ; style . setBorderRight ( CellStyle . BORDER_THIN ) ; style . setRightBorderColor ( IndexedColors . BLUE . getIndex ( ) ) ; style . setBorderTop ( CellStyle . BORDER_THIN ) ; style . setTopBorderColor ( IndexedColors . BLUE . getIndex ( ) ) ; cell . setCellStyle ( style ) ;

Border can be set by , , , methods in CellStyle. Also Border Color can be set by , , , methods. Valid Border Styles are ForegroundColor: setFillForegroundColor(colorIndex) and setFillPattern in can be used for this feature. cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex()); cellStyle.setFillPattern(CellStyle.SOLID_FOREGROUND); 1 2 cellStyle . setFillForegroundColor ( IndexedColors . GREY_25_PERCENT . getIndex ( ) ) ; cellStyle . setFillPattern ( CellStyle . SOLID_FOREGROUND ) ;

Merging of cells

Cells can be merged by using addMergedRegion() method in Sheet .



//Merge Cells Row row = sheet1.createRow((short) 0); Cell cell = row.createCell(0); cell.setCellValue("Expense report"); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1)); 1 2 3 4 5 //Merge Cells Row row = sheet1 . createRow ( ( short ) 0 ) ; Cell cell = row . createCell ( 0 ) ; cell . setCellValue ( "Expense report" ) ; sheet . addMergedRegion ( new CellRangeAddress ( 0 , 0 , 0 , 1 ) ) ;

Using New lines:

We can use new line character (‘

’) to show text in new line and set setWrapText 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.



//Wrapping of content in a cell row = sheet1.createRow((short)1); CellStyle cellWrapStyle = wb.createCellStyle(); cellWrapStyle.setWrapText(true); cell = row.createCell(1); cell.setCellValue("This is an

example for cell wrapping."); cell.setCellStyle(cellWrapStyle); 1 2 3 4 5 6 7 //Wrapping of content in a cell row = sheet1 . createRow ( ( short ) 1 ) ; CellStyle cellWrapStyle = wb . createCellStyle ( ) ; cellWrapStyle . setWrapText ( true ) ; cell = row . createCell ( 1 ) ; cell . setCellValue ( "This is an

example for cell wrapping." ) ; cell . setCellStyle ( cellWrapStyle ) ;

Set Print Area:

Print Area can be set up using setPrintArea

wb.setPrintArea(0, //Sheet index 0, //start Column 1, //end Column 0, // start row 7 //end row ); 1 2 3 4 5 6 wb . setPrintArea ( 0 , //Sheet index 0 , //start Column 1 , //end Column 0 , // start row 7 //end row ) ;

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 ‘=’.

// Create a cell and put a value in it. Cell cellItem = row.createCell(0); cellItem.setCellValue("Total Cost"); Cell cellValue = row.createCell(1); cellValue.setCellFormula("SUM(B2:B5)"); 1 2 3 4 5 // Create a cell and put a value in it. Cell cellItem = row . createCell ( 0 ) ; cellItem . setCellValue ( "Total Cost" ) ; Cell cellValue = row . createCell ( 1 ) ; cellValue . setCellFormula ( "SUM(B2:B5)" ) ;

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

Workbook wb = WorkbookFactory.create(inputStream); for (Sheet sheet : wb ) { //............ for (Row row : sheet) { //............ for (Cell cell : row) { // .............. } } } 1 2 3 4 5 6 7 8 9 10 Workbook wb = WorkbookFactory . create ( inputStream ) ; for ( Sheet sheet : wb ) { //............ for ( Row row : sheet ) { //............ for ( Cell cell : row ) { // .............. } } }

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.

for (Cell cell : row) { switch(cell.getCellType()){ case Cell.CELL_TYPE_STRING: System.out.println(cell.getRichStringCellValue().getString()); break; case Cell.CELL_TYPE_NUMERIC: System.out.println(cell.getNumericCellValue()); break; case Cell.CELL_TYPE_FORMULA: System.out.println(cell.getCellFormula()); break; default: System.out.println("Not Valid CellType"); }; } 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 for ( Cell cell : row ) { switch ( cell . getCellType ( ) ) { case Cell . CELL_TYPE_STRING : System . out . println ( cell . getRichStringCellValue ( ) . getString ( ) ) ; break ; case Cell . CELL_TYPE_NUMERIC : System . out . println ( cell . getNumericCellValue ( ) ) ; break ; case Cell . CELL_TYPE_FORMULA : System . out . println ( cell . getCellFormula ( ) ) ; break ; default : System . out . println ( "Not Valid CellType" ) ; } ; }

3. Examples