How to read and write XLSX file in java

In this article , we will learn how to read and write excel file (.xlsx) from java code. Since, JDK doesn’t provide direct API to read and write XLSX file, we would have to rely on third party library to do the job. Fortunately, there are bunch of library availabe for us to play with, the best is Apache POI.

Why Apache POI ?

Apache POI is a popular API that allows programmers to create, modify, and display MS Office files using Java programs. It is an open source library developed and distributed by Apache Software Foundation to design or modify Microsoft Office files using Java program.

Not only excel file, POI can do much:

  • XSSF (XML SpreadSheet Format) – Used to reading and writting Open Office XML (XLSX) format files.
  • HSSF (Horrible SpreadSheet Format) – Use to read and write Microsoft Excel (XLS) format files.
  • HWPF (Horrible Word Processor Format) – to read and write Microsoft Word 97 (DOC) format files.
  • HSMF (Horrible Stupid Mail Format) – pure Java implementation for Microsoft Outlook MSG files
  • HDGF (Horrible DiaGram Format) – One of the first pure Java implementation for Microsoft Visio binary files.
  • HPSF (Horrible Property Set Format) – For reading “Document Summary” information from Microsoft Office files.
  • HSLF (Horrible Slide Layout Format) – a pure Java implementation for Microsoft PowerPoint files.
  • HPBF (Horrible PuBlisher Format) – Apache’s pure Java implementation for Microsoft Publisher files.
  • DDF (Dreadful Drawing Format) – Apache POI package for decoding the Microsoft Office Drawing format.

Getting Started with Apache POI

The following steps will guide you through reading and writing on excel(.XLSX) files.

1. Create a Spring boot Application

You can either create it from https://start.spring.io/ or use an IDE to create application, we will be adding dependency on the go.

2. Add necessary dependecies

Open pom.xml, then add the following dependencies :

if you are not using Maven, then add  the following JAR files in your Java program’s classpath

  • poi-3.11-beta2.jar
  • commons-codec-1.9.jar
  • poi-ooxml-3.11-beta2.jar
  • poi-ooxml-schemas-3.11-beta2.jar
  • xmlbeans-2.6.0.jar
  • stax-api-1.0.1.jar

3. How to read Excel file (XLSX) in java

What we will be reading :

Method to read XLSX file in java :

The first two lines are very common to understand, they are to read file from the system, the main code starts from fourth line where we pass a binary Input Stream to create an instance of XSSFWorkBook class, which represents an Excel workbook.

The next line gives us a worksheet from where we start iterating each row and then each column. Cell represents each block in a sheet. This is where we read or write data.

A cell can be any type e.g  String, numeric, boolean, etc, so we need to check the type of cell before reading,  for that we have a switch case which checks the type before reading the specific cell value by calling getStringValue(), getNumericValue(), etc.

This is how exactly you can read XLSX file in java.

4. How to write to XLSX file

Writing is also similar to reading, I have created another method to write to XLSX file. The workbook and worksheet classes will remain same.

Method to write XLSX file :

We have  created a map, to store data, which we need to write  on excel file. Then iterated the map, and started inserting the value with the help of setCellValue method , before that we need to know the last edited row number and type of the cell value.

Finally, we need to open an Output Stream to save written data into XLSX file.

Output:

Conclusion

In this article , we learned how to read and write XLSX file in java. I really hoped you enjoy reading the blog.

 

 

Comments