Last Updated:

Creating an Excel file in Java using Apache POI

Apache POI
 

If you are not already familiar with Apache POI, I recommend that you briefly familiarize yourself with its features and how to connect it to the project at this link.

Creating a new Excel file in Java

Attention, the code is not very beautiful and optimized. I just wanted to demonstrate the capabilities of this handy library.

First, let's create a simple xls file and write some data to it. And then we will apply styles to it and add formulas.

For convenient work with the data, we will need an additional class, which will be a data model that we will write to the file:

As you can see, this is a simple class with fields for the name, surname, city and salary of some person.

The following is a listing of the class in which the Excel file itself is created:

Note that we used try with resources, one of the features of Java 7. This means that we don't have to worry about closing the file manually. In Java 7, the try-catch-finally construct can be abandoned because it has been replaced by try with resources, which itself closes open files or threads without your intervention.

After running the above program, a file named Apache POI Excel File.xls will be created in the root of the project. Let's take a look at its contents:

The excel file sheet is called "Just Sheet", as we called it, and the data is positioned correctly.

Adding styles to an Excel document in Java

Now let's practice updating the file, namely adding styles. For example, let's bold the column name from the first row.

To do this, we'll need another setBoldStyle() method:

As you can see, we just go around all the cells of the first row and apply the BOLD style to it.

The output of this code is as follows:

With Apache POI, this is done quickly and conveniently.

Add formulas to an Excel document in Java

Now let's try to figure out how to add formulas using Apache POI.

Apache POI does not allow you to set values in cells with a sign equal to: "="..

Example:

You can do this:

and like this:

But if you try to write like this:

then an exception will crash with a message about an invalid operation. Apache POI does not allow you to work with formulas in this way.

And so, in theory we are savvy, now let's add a simple formula. To do this, let's write another setFormula() method:

In it, we open the file, add to it another line with a cell to calculate the amount of the salary of physicists. And yes, we highlight it in red.

 

The tutorial on creating a new Excel file in Java using Apache POI has come to an end. You can download the working project at this link.