Creating Spreadsheets in Java via Apache POI (HSSF): Java, Excel, Spreadsheets, Apache POI, POI, HSSF, .xls, xls, MS Excel, create spreadsheets

Are you developing a Java application and want to create Excel Spreadsheets? You have come to the right place, this article will describe how you can create Excel Spreadsheets from a Java Application.

For the purposes of this article, I will be using NetBeans 6.9. If you want to follow along with this tutorial, open NetBeans and create a new "Java Application" called something like "ExcelCreator".
If you don't have NetBeans you can get it here: http://netbeans.org/downloads/index.html. Make sure you get the Java bundle:

javaBundle.png

33 KB
NetBeans Java Bundle

What we're going to do is create a simple Java Application to create a spreadsheet. We're going to have a text box, an "Add" button, a JList, and an "Export to Excel" button. When the "Add" button is pressed, the inputted text will be added to the JList. When the "Export to Excel" button is pressed, the application with take the values in the JList and create an Excel Spreadsheet with those values in the "B" column and a counter variable in the "A" column. For example sake, we'll also center the values in the cells. So if there are the following 5 values in the JList ('item a', 'item b', 'item c', 'item d', 'item e') the Excel Spreadsheet will look like this:

spreadsheet-example.png

22 KB
Example Spreadsheet

Once you have created a new "Java Application", create a new JFrame Form called "GUI" in the <default package> under "Source Packages". Add the text box, JList, and two buttons to you form to make it look like this:

GUI-example.png

59 KB
GUI Example

Be sure to create meaningful variable names for your text box and JList especially as you'll use them in the code later. I called my text box "txtInput" and my JList I called "jlValues".

Let's create code for the "Add" button so that when the "Add" button is pressed, the inputted text will be added to the JList and the text box empties for a new value to be entered. Simply double-click on the "Add" to create an event for it. Inside the ActionPerformed code block, put the following code (read the comments for code explanation!):

private void btnAddActionPerformed(java.awt.event.ActionEvent evt) {
    // TODO add your handling code here:
    Object[] values = new Object[jlValues.getModel().getSize() + 1]; // creates an array that holds the values in the JList
    for (int i = 0; i < values.length; i++) { // retreive all the pre-existing values in the JList
        if (i != values.length - 1) { // make sure you get add the inputted text at the end
            values[i] = jlValues.getModel().getElementAt(i);
        } else {
            values[i] = txtInput.getText(); // sets the inputted text at the end of the list
            txtInput.setText(""); // clears the text box to allow for faster entry
        }
    }
    jlValues.setListData(values); // set the values to the array that holds the previous values plus the new entry
}

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:

Select all Open in new window

Now we are ready to download the POI and add it as a library to our project. Pay close attention to the following steps as they are crucial to our desired functionality.

1: Downloading the POI

Go to this URL to download the POI: http://poi.apache.org/download.html#POI-3.7
Click on this one here:

download-link.png

18 KB
Download_Link Screen-Shot

Choose any mirror and save it to your desktop.
Unzip the folder to any directory that you choose, I put mine in "C:/Program Files/java/3rd_Party_APIs"

Here is where mine is located:

dir.png

110 KB
POI Directory

2: Add the POI JAR as a Project Library

We need to add the highlighted JAR file to our projects library list. To do that, right click on the Project Name in the "Projects Window" and select "Properties". In the "Categories" panel on the left, select "Libraries". On the right, press the "Add Jar/Folder" button. Find your extracted folder that you downloaded in Step 1 and double click on the jar that I highlighted in the last screen-shot. If the window looks like the following, click okay at the bottom and you'll have successfully set up the library.

lib-setup.png

59 KB
Library Setup Window

3: Add Import Statement

Go to the top of your GUI.java class and add the following import lines at the very top just before the class declaration:

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;

public class GUI extends javax.swing.JFrame {
.....

1:
2:
3:
4:
5:

Select all Open in new window

Now we are ready to write the code that actually creates the Excel Spreadsheet with the values from the database. Double-click on the "Export to Excel" button and add the code snippets by following these next set of steps:

Code to Create the Excel Spreadsheet

1: Create the WorkBook Object

The first thing that we need to do is create the WorkBook object itself and Create a Sheet in the Workbook:

Workbook wb = new HSSFWorkbook();
CreationHelper createHelper = wb.getCreationHelper(); // allows us to create cell values that are String rather than simply numbers
Sheet sheet = wb.createSheet("Values"); // name the sheet whatever you like

1:
2:
3:

Select all Open in new window

2: Create Cells for Each Value in the JList

// Loop through the values in the JList and create the cells for them and the counter
for (int i = 0; i < this.jlValues.getModel().getSize(); i++) {
    Row row = sheet.createRow((short) i); // creates a new row for each value in the JList
    // creates a cellStyle for centering the values in the cells
    CellStyle cellStyle = wb.createCellStyle();
    cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    // create the counter and put it in the column A
    Cell counterCell = row.createCell(0); // 0 = column A
    counterCell.setCellValue(i + 1); // the counter is not zero based
    counterCell.setCellStyle(cellStyle); // uses the center-aligned cell style
    // create the cells that hold the values in the JList and put them in column B
    Cell valueCell = row.createCell(1); // 1 = column B
    valueCell.setCellValue(createHelper.createRichTextString(this.jlValues.getModel().getElementAt(i).toString()));
    valueCell.setCellStyle(cellStyle); // uses the center-aligned cell style
}

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:

Select all Open in new window

3: Create the Excel File via FileOutputStream and Open it for Viewing

try {
    java.io.FileOutputStream fileOut = new java.io.FileOutputStream("src/values.xls"); // creates the Excel file and places it in the <default package>
    wb.write(fileOut);
    fileOut.close(); // ends writing the Stream
    java.awt.Desktop.getDesktop().open(new java.io.File("src/values.xls")); //opens the Excel Spreadsheet for viewing
} catch (java.io.FileNotFoundException fnfe) {
    javax.swing.JOptionPane.showMessageDialog(this, "The Excel Spreadsheet cannot be found  :(");
} catch (java.io.IOException ioe) {
    javax.swing.JOptionPane.showMessageDialog(this, "There was an error creating the Excel Spreadsheet  :(");
}

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:

Select all Open in new window

That's it! You've created an Excel Spreadsheet with your Java Application!!!

There are a lot of other things you can do with the Apache POI. For more information and code examples, look here: http://poi.apache.org/spreadsheet/quick-guide.html

Also, my example program "GUI.java" is attached.

Happy Excel Creating :)

Creating Spreadsheets in Java via Apache POI (HSSF)

Downloading the POI

Add the POI JAR as a Project Library

Add Import Statement

Create the WorkBook Object

Create Cells for Each Value in the JList

Create the Excel File via FileOutputStream and Open it for Viewing

Java

Excel

Spreadsheets

Apache POI

POI

HSSF

.xls

xls

MS Excel

create spreadsheets

Java Programming Language

About The Author

More Articles From patsmitty

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top Java Experts