Java: Using POI library to read and write Excel data

Vietnam version

After reading the article title, you can easily guess the intentions of this post, right? Reading and writing excel data are the often actions in our jobs, Therefore, how can you read and write this data by the simplest way? This post is going to answer the above question.

Apache POI Project

The mission of Apache POI project is creating and maintaining the Java APIs which work on the different file format as Office Open XML (OOXML) standard and OLE 2 format of Microsoft corps. In simple terms, this project helps to read and write data from excel format in the Java programs. Moreover, you can read and write data wit MS Word and MS Powerpoint documents as well. You can get the details at here and get the library at this link.

Read and write

Before working with the Excel filé, we have to understand its structure and then we will read and write them. Of course, if you had had the base computing knowledge, you knew the structure of an Excel file, right? It contains the following components (click here to get more details):

1. Workbook: workbook is an Excel file. Now, we will call workbook instead Excel file.
2. Worksheet: we can call sheet. A workbook includes many worksheets. The names of them are different.
3. Row: a worksheet contains many Rows. The rows are numbered from 0 to end.
4. Cell: a row comprises many Cells. Each Cell will hold the data what we want to read and wire. a Cell is the smallest unit in a workbook.

In general, if you want to read and write excel data, you need to locate the Cell address.

Reading

Firstly, we will start with the easy task. Assume, we have a workbook which contains the information of the students in a class. In my example workbook (here), it has five columns (Code (String), Fullname (String), Birthday (Date), Gender (Integer) and Address (String)). Now, we will read these data. Let see the following snippet:

As you can see, we will focus the readStudent method (from line 37th to 103rd). I will explain some line what we need to consider:

• Line 42nd: POI library only allows creating a workbook from an instance of InputStream class. Therefore, we have to create an InputStream object from original file. 
• Line 44th: allows creating a workbook from the input stream.
• Line 49th: gets the first sheet in the workbook.
• Line 55th-85th: I will get the rows on the first sheet and get the data in each cell in these rows.

At the moment, I will comeback Test class and run it again. Surprise!, the information of the students showed on the console, right? In the next session, we will know how to write data into a workbook.

Write

Opposite reading, the order of writing is i) create a workbook, ii) create a worksheet, iii) create a row and iv) create a cell at last. The following snippet will store data into a workbook.

We will consider writeStudent method (from line 37th to 128th). I will analyse some lines as:

• Line 44th: create a workbook in temporary memory .
• Line 49th: create a worksheet named Students. This is the first sheet of this workbook.
• Line 54th: create a row in this sheet. This row is the header one.
• Line 78th - 108th: save the students' information in each row.

Now, let run Test again. Fortunately, a workbook will be created with your data.

Conclusion

• The writing and reading Excel data are the frequency jobs. So, this post is really useful.
• In fact, this post only uses the basic feature of Apache POI library. I will introduce the quality posts to you in the future.
• You can get the source code at  here.