Systems of any scale are holding data in one form or the other (Database, files, images, videos etc). With more and more data being churned out every day, there is also a need to be able to export data from the Applications. The exported data should be made available in the format that users can easily view with bunch of other tools. Excel is one of those dominating tool that people love for its multipurpose application. In this article, we will learn how to leverage Apache POI to generate Excel sheet with various customization options.
Before we begin make sure that you have all the following set of softwares installed and configured within your system
- Open JDK 13
- Maven 3.5+
- IntelliJ IDEA (Community edition works)
Once you have all the above listed softwares ready, create a Mavenized Java project and add the following dependency.
<dependency> <groupid>org.apache.poi</groupid> <artifactid>poi</artifactid> <version>4.1.1</version> </dependency> <dependency> <groupid>org.apache.poi</groupid> <artifactid>poi-ooxml</artifactid> <version>4.1.1</version> </dependency>
The first dependency downloads Apache POI library while the second one downloads schemas required to create Office compatible files using Open XML format. This how Office Open XML has been described on the site.
Office Open XML, also known as OpenXML or OOXML, is an XML-based format for office documents, including word processing documents, spreadsheets, presentations, as well as charts, diagrams, shapes, and other graphical material. The specification was developed by Microsoft and adopted by ECMA International as ECMA-376 in 2006.– Office Open XML website
It is quite clear from the definition that, the specification itself is designed by the Microsoft. Without wasting any further time, lets jump directly into the code.
Creating a Workbook
Everything in Excel starts with a WorkBook. You can create a workbook using following API
Workbook wb = new XSSFWorkbook();
There are primarily two APIs available, either you can use – HSSF or XSSF. The first API (HSSF) is meant for generating Excel files compatible with 97 format while the later one creates file compatible with 2007 format.
Workbook object comes with various methods to embed file, add pictures, fonts, cell styles etc.
Next we proceed with creation of sheet in Workbook. This can be easily achieved by invoking createSheet method on Workbook instance.
The createSheet method takes sheet name as argument and returns an instance of org.apache.poi.ss.usermodel.Sheet
The sheet class has methods to customized the appearance of a specific Sheet, you can hide columns, set width, shift rows/columns, remove rows/columns etc.
Create a Simple Row with cells
We looked at the creation of Workbook and sheet, but it won’t be of any use unless we add some data in it. Lets proceed with creation of first row.
Row row = sheet.createRow(0);
The Sheet object has a method called createRow, which accepts an index at which the row has to be created. The index value starts at 0 which means the first row. The method returns a reference of org.apache.poi.ss.usermodel.Row.
To set the actual values inside the cell, first create an instance of Cell by calling createCell method.
Cell dataCell = row.createCell(colNum++);
Note the 2nd line which sets the value for Cell. You can also use Cell instance to customize the appearance of cell and add hyperlink. Finally, lets go ahead and save the Workbook in a disk.
FileOutputStream outputStream = new FileOutputStream(new File(“d:\\myfile.xlsx”));
Behind the scene, the entire Workbook representation is stored in memory and it is serialized to the given output stream instance. You can also pass an instance of Servlet Output Stream by invoking response.getOutputStream(). This is pretty useful for web based application having feature to export data from the system.
We have barely scratched surface of POI API from office document. In the upcoming articles we will explore more APIs.