Create and edit excel documents using apache poi library

Create and edit excel documents using apache poi library

Apache POI is an open source Java library for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft’s OLE 2 Compound Document format (OLE2). In short, you can read and write MS Excel files, MS Word and MS PowerPoint files using Java. Apache POI is your Java Excel solution (for Excel 97-2008). In this post we will see how to Create and edit excel documents using Apache poi library.

Apache POI Project Components

Apache POI contains Classes to work on files based on Microsoft’s OLE 2 Compound Document Format.

Below are some of the important components of Apache  POI project.

  • HSSF (Horrible SpreadSheet Format) – can be used to read and write Microsoft Excel (.xls) format files.
  • XSSF (XML SpreadSheet Format) – can be used to read and write Office Open XML (.xlsx) format files
  • HWPF (Horrible Word Processor Format) – can be used to read and write Microsoft Word 97 (.doc) format files.
  • XWPF (XML Word Processor Format) – can be used to read and write Microsoft Word (.docx) format files.
  • HSLF (Horrible Slide Layout Format) – can be used to read, create and edit Microsoft PowerPoint presentations files.

 

Both HWPF and XWPF could be described as “moderately functional” meaning it isn’t complete.

In the post we will see the usage of HSSF and XSSF components to work with Excel documents.

Apache POI setup

You can download the latest stable version of Apache POI from the below link,

Apache POI – Download

This post is written on Apache poi-3.14. When you extract the downloaded zip file, you will see a directory structure as below,

apache poi library

Below are the required libraries that should be added to the Java build path

  • poi-3.14-20160307.jar
  • poi-ooxml-3.14-20160307.jar
  • poi-ooxml-schemas-3.14-20160307.jar
  • xmlbeans-2.6.0.jar
  • Earlier versions like poi-3.9 requires an additional jar called dom4j-xx.jar.

 

The xmlbeans-2.6.0.jar will  be present under \poi-bin-3.14.zip\poi-3.14\ooxml-lib folder as shown in the image.

HSSFWorkbook

HSSFWorkbook is the class used to work with Microsoft Excel .xls files. It is a high level representation of a workbook. This is the first object to be created when reading or writing a workbook. It is also the top level object for creating new spread sheets.

Create an excel file using HSSFWorkbook

Here is a sample program showing the usage of the above class to create a Microsoft Excel 97-2003 Worksheet(.xls) file

When you run the above program you will see an Excel (.xls) file created in the D drive with name HSSFWorkBook. Below is a snapshot of the file created by the above program.

HSSFWorkBook create excel

Open an existing excel file using HSSFWorkbook

Here is a sample program to open an existing excel file (.xls) and read its content using HSSFWorkbook. We will use the file created by the previous example.

Below is the output of running the above program.

XSSFWorkbook

XSSFWorkbook is the class used to work with Microsoft Excel .xlsx files. It is the high level representation of a SpreadsheetML workbook. This is the first object to be created when reading or writing an xlsx workbook.

Create an excel file using XSSFWorkbook

Here is a sample program showing the usage of the above class to create a Microsoft Excel Worksheet(.xlsx) file.

When you run the above program you will see an Excel (.xlsx) file created in the D drive with name XSSFWorkBook. Below is a snapshot of the file created by the above program.

XSSFWorkBook create excel

Open an existing excel file using XSSFWorkbook

Here is a sample program to open an existing excel file (.xlsx) and read its content using XSSFWorkbook. We will use the file created by the previous example.

Below is the output of running the above program.

XSSFCell

Any data that we enter in the excel sheet is contained inside cells. The XSSFCell  class represents a cell in a row of a spreadsheet. We can create different type of cells such as,

  • CELL_TYPE_BLANK – defines a blank cell
  • CELL_TYPE_BOOLEAN – defines a Boolean cell (true or false)
  • CELL_TYPE_ERROR – defines an error value cell
  • CELL_TYPE_FORMULA – defines a formula result cell
  • CELL_TYPE_NUMERIC – defines a numeric data cell
  • CELL_TYPE_STRING – defines a string (text) cell

 

Cells can be numeric, formula-based or string-based (text). The cell type defines the type of the cell to be created. String cells cannot contain numbers and numeric cells cannot contain strings.

Below program shows how to create different types of cells in a spreadsheet.

When you run the above program you will see an Excel (.xlsx) file created in the D drive with name XSSFCellTypes. Below is a snapshot of the file created by the above program.

Apache poi Cell types

Note the value in the formula cell. It is the value calculated by the formula B5*2 which is nothing but the value in the cell B5 multiplied by 2.

Reading values from different type of cells

In our earlier examples we have used getStringCellValue() method to read the value of the cell. The same method cannot be used to read the values of other type of cells such as numeric cell, boolean cell etc. If you try to read a numeric cell using getStringCellValue() method, an exception will be thrown. Here is a program to read values of different type of cells. We will read from the excel sheet created in the previous example.

Here is the output of running the above program.

Cell Styles

Here we will see program to apply different styles such as cell alignment, borders, cell coloring, cell merging etc.

Here is the snapshot of the file created by the above program.

Apache poi cell styles

Apache POI – Fonts and Text Direction

The contents of a cell can be displayed using different fonts (Arial, Times New Roman, etc.) and styles like italic, bold, strike through, underline etc. Contents are usually displayed horizontally, from left to right, and at 00 angle, but it is possible to rotate the text direction, if required. Here is a program showing usage of fonts, font styles and text direction.

Here is a snapshot of the file created by the above program.

apache poi font styles

Note the font setting highlighted in the image. It reflects the values set in the program.

Apache POI – Hyperlink

It is possible to add hyperlinks to the contents in a cell. Here is a program which shows how to create hyperlink on cells.

Here is a snapshot of the file created by the above program.

Apache poi hyperlink

Hope this post helped you to learn how to create and edit excel documents using Apache poi library. Much more can be done with the Apache poi library but this post will surely serve as a starting point.

If you have any comments, post it in the comments section.

The following two tabs change content below.
Working as a Java developer since 2010. Passionate about programming in Java. I am a part time blogger.
One comment

Add Comment

Required fields are marked *. Your email address will not be published.