Excelling in Excel with Java

Learn how to use the Jakarta POI

By Elango Sundaram, JavaWorld.com, 03/22/04

Whether you have balance sheets, account information downloads, tax calculations, or pay slips, they all tend to come in Microsoft Excel. Non-IT professionals feel comfortable using Microsoft Excel as a data exchange technology. The Jakarta POI (Poor Obfuscation Implementation) API is a fantastic way for Java programmers to access Microsoft document formats. The most mature API from Jakarta POI is the HSSF (Horrible Spreadsheet Format) API, which accesses Microsoft Excel documents.

In this article, I walk you through the steps for creating and reading Excel documents, and for using fonts and cell styling—all using Java.

Note: You can download the source code for all the examples in this article from Resources.

POI terminology

The key terms associated with Jakarta POI are as follows:

POIFS (Poor Obfuscation Implementation File System): Java APIs for reading and writing OLE (Object Linking and Embedding) 2 compound document formats
HSSF (Horrible Spreadsheet Format): Java API to read Microsoft Excel
HDF (Horrible Document Format): Java API to read and write Microsoft Word 97
HPSF (Horrible Property Set Format): Java API for reading property sets using (only) Java

Create an Excel document

The Jakarta POI API can be used to create an Excel document programmatically. The important steps involved are:

Create a workbook: HSSFWorkbook workbook = new HSSFWorkbook();
Create a new worksheet in the workbook and name the worksheet "Java Excels": HSSFSheet sheet = workbook.createSheet("Java Excels");
Create a new row in the sheet: HSSFRow row = sheet.createRow((short)0);
Create a cell in the row: HSSFCell cell = row.createCell((short) 0);
Put some content in the cell: cell.setCellValue("Have a Cup of XL");
Write the workbook into the filesystem: workbook.write(fileOutputStream);

Read data from the Excel document

In this example, you'll see how to read values from an Excel document.

Let's assume this is our Excel sheet:

Employee Name	Specialization	Designation
Anbu	Programming	Senior Programmer
Jason	Banking Industry	Business Analyst
Ramesh	Databases	DBA
MackyB	Accounting	Delivery Head

The key steps in reading the Excel sheet are as follows:

Create a new Excel document reference: HSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(fileToBeRead));.
Refer to the sheet: By default, the first sheet in the Excel document is at reference 0: HSSFSheet sheet = workbook.getSheetAt(0);. A sheet can also be referred to by name. Let's assume that the Excel sheet has the default name "Sheet1". It can be referred to as follows: HSSFSheet sheet = workbook.getSheet("Sheet1");.
Refer to a row: HSSFRow row = sheet.getRow(0);.
Refer to a cell in the row: HSSFCell cell = row.getCell((short)0);.
Get the values in that cell: cell.getStringCellValue();.

A practical example

Now let's assume that we want to see the list of all declared methods and member variables in a jar file. It would be ideal to have a consolidated list of all information in one single file. We would like to view the information so that the class names are in the first column, declared fields in the second column, and declared methods in the third column, with the column headings appearing in red.

1 | 2 | Next >

Discuss

Start a new discussion or jump into one of the threads below:

Subject	Replies	Last post
Protecting excel spreadsheet reads from bad data. By ddyer	0	05/13/08 12:25 PM by ddyer
Read Excel file with java application By Armel	18	04/11/08 01:08 PM by Meghna
Java class to write data to excel By Anonymous	7	09/22/07 11:28 PM by liqdok
embedding objects in excel sheet ` By akshayjain7983	0	06/07/07 03:10 AM by akshayjain7983
download By enigma ( 1 2 all )	25	05/22/07 06:46 AM by nehacredo
Cactus information By vidhyap	0	11/20/06 08:17 PM by Anonymous
read file excel with graphic By antonella	1	11/17/06 04:58 AM by Anonymous
jxcell By jxcell	2	10/29/06 06:43 AM by Anonymous
Comments on Jakarta Apis for Excel. By Jean Pierre	3	10/29/06 04:45 AM by Anonymous
How to get range coordinates By kvdg	2	10/29/06 03:53 AM by Anonymous
Excelling in Excel with Java By bcappel	5	10/29/06 03:42 AM by Anonymous
xml to xls By Anonymous	2	10/29/06 02:23 AM by Anonymous
Sheet By Anonymous	3	10/29/06 02:17 AM by Anonymous
Multiple Sheets By Joe P	1	10/29/06 02:16 AM by Anonymous
How can only one heading constitute two columns? By Anonymous	1	10/29/06 02:06 AM by Anonymous
How to hyperlink any label in excel sheet through By javadigaddi	0	10/29/06 01:18 AM by Anonymous
how to read cell defined by name ? By Quan	4	10/29/06 01:16 AM by Anonymous
Excellent Article... By AnandKumar	2	10/29/06 01:18 AM by Anonymous
Useful for desktop apps By Martin Bromley	2	10/29/06 01:17 AM by Anonymous
Does POI API support 'Data->Group & Outline' featu By Anonymous	1	10/28/06 10:17 PM by Anonymous