Reading and Writing Excel Spreadsheets

The open source community has created many open source Java projects, which range from charting software to game frameworks to word processors. In this post, I introduce an open source library project for reading and writing Microsoft Excel spreadsheets.

Recommending a SpreadSheet Library

Q: I've been asked to extend my company's Java-based spreadsheet software to read and write Excel spreadsheets. Can you recommend an open source Java library that will help me with this task?

A: You might want to check out JExcelAPI, which is a mature, Java-based open source library that lets you read, write, and modify Excel spreadsheets. Here are a few of its many features:

  • Reads data from Excel 95, 97, 2000, XP, and 2003 workbooks
  • Reads and writes formulas (Excel 97 and later only)
  • Generates spreadsheets in Excel 2000 format
  • Supports font, number, and date formatting
  • Supports cell shading, cell bordering, and cell coloring
  • Modifies existing worksheets
  • Supports chart copying
  • Supports the insertion and copying of images into spreadsheets

JExcelAPI was developed by Andrew Kahn and was released under the GNU Lesser General Public License.

Downloading the JExcelAPI Library

Q: How do I download JExcelAPI?

A: Complete the following steps to download JExcelAPI:

  1. Point your browser to JExcelAPI's SourceForge site.
  2. Click the jexcelapi link.
  3. On the resulting page, click one of the folder links. For example, I clicked the 2.6.12 link.
  4. On the resulting page, click the distribution archive filename. For example, I clicked the jexcelapi_2_6_12.zip link.
  5. After a short delay, your browser should prompt you to save this file. Go ahead and save the file.

Following the download, unarchive this file. You should observe a jexcelapi home directory within a jexcelapi_2_6_12 directory.

Demonstrating the JExcelAPI Library

Q: Does the JExcelAPI library contain any demos?

A: JExcelAPI's jexcelapi home directory contains a jxl.jar file that contains demos for reading, writing, and copying spreadsheets.

The read demo reads an existing spreadsheet, converting it to comma-separated value (CSV) or XML format via the -csv or -xml command-line option. Consider the following examples:

java -jar jxl.jar -csv budget.xls
java -jar jxl.jar -xml budget.xls

These examples read budget.xls and output its contents in CSV and XML format to standard output. When neither -csv nor -xml is specified, -csv is assumed.

The write demo creates a sample spreadsheet that includes formulae, borders, images, and more. This spreadsheet is generated by specifying the -write command-line option, as demonstrated below:

java -jar jxl.jar -write sample.xls

Figure 1 shows part of the resulting sample.xls spreadsheet.

I used LibreOffice Calc to access the <code>sample.xls</code> spreadsheet.

Figure 1: I used LibreOffice Calc to access the sample.xls spreadsheet.

The copy demo copies sample spreadsheet jxlrwtest.xls, which is stored in the same directory as jxl.jar, to a new spreadsheet. In the resulting spreadsheet, the first sheet (original) is unchanged whereas the second sheet (modified) contains modified values.

This demo is generated by specifying the -rw command-line option followed by jxlrwtest.xls and the name of the output spreadsheet. Consider the following command line:

java -jar jxl.jar -rw jxlrwtest.xls copy.xls

This command line copies jxlrwtest.xls to copy.xls. Figure 2 shows the second (modified) sheet in LibreOffice Calc.

Click the original and modified tabs to view the original and modified sheets.

Figure 2: Click the original and modified tabs to view the original and modified sheets.

Including JExcelAPI for Compilation and Execution

Q: How do I include JExcelAPI when compiling source code and running an application?

A: To include JExcelAPI when compiling source code and running an application, do one of the following:

  • Add the jexcelapi home directory's jxl.jar file to your CLASSPATH environment variable.
  • Include jxl.jar via the javac and java program's -cp command-line option.

Programming with JExcelAPI

Q: How do I create Java programs that leverage JExcelAPI?

A: The jexcelapi home directory includes a tutorial.html file that presents a basic tutorial on programming with JExcelAPI. The tutorial shows you how to read, write, and copy spreadsheets. The tutorial also discusses formatting.

jexcelapi also includes a docs subdirectory, which provides access to extensive API documentation. Point your Web browser to this directory's index.html file and you can explore the types in this library's four documented packages:

  • jxl: the main package's types
  • jxl.demo: types for the various demos
  • jxl.format: types related to formatting
  • jxl.write: types for writing to a spreadsheet

Note that this list isn't exhaustive. Additional packages such as jxl.read are present but are not documented. To learn about additional packages, execute jar tvf jxl.jar and examine the package information in the resulting JAR listing.

To help you get started with JExcelAPI, I've created a simple JExcelAPIDemo application that demonstrates creating a new spreadsheet that is saved in output.xls and then reading and outputting the contents of this spreadsheet. Check out Listing 1.

import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

import jxl.read.biff.BiffException;

import jxl.write.Label;
import jxl.write.Number;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

public class JExcelAPIDemo
{
   public static void main(String[] args) 
      throws BiffException, IOException, WriteException
   {
      WritableWorkbook wworkbook;
      wworkbook = Workbook.createWorkbook(new File("output.xls"));
      WritableSheet wsheet = wworkbook.createSheet("First Sheet", 0);
      Label label = new Label(0, 2, "A label record");
      wsheet.addCell(label);
      Number number = new Number(3, 4, 3.1459);
      wsheet.addCell(number);
      wworkbook.write();
      wworkbook.close();

      Workbook workbook = Workbook.getWorkbook(new File("output.xls"));
      Sheet sheet = workbook.getSheet(0);
      Cell cell1 = sheet.getCell(0, 2);
      System.out.println(cell1.getContents());
      Cell cell2 = sheet.getCell(3, 4);
      System.out.println(cell2.getContents());
      workbook.close();
   }
}

Listing 1: Writing and reading a simple spreadsheet

Listing 1 first creates a writable workbook by invoking one of Workbook's factory methods. A writable sheet is then created for this workbook, and then a label and a number are added as the sheet's two cell values. The workbook is then written and closed.

Listing 1 continues by getting a workbook associated with output.xls and reading its contents. The getSheet() method provides access to the first sheet within this workbook. Its getCell() method is called to access the two cells, whose contents are then output.

Assuming that jxl.jar is located in the current directory, execute the following command to compile Listing 1:

javac -cp jxl.jar JExcelAPIDemo.java

Assuming success, execute the following command to run JExcelAPIDemo:

java -cp jxl.jar;. JExcelAPIDemo

You should observe the following output:

A label record
3.146

Figure 3 shows you output.xls in a LibreOffice context.

The solitary sheet displays two cell values.

Figure 3: The solitary sheet displays two cell values.

Code

You can download this post's code here. Code was developed and tested with JDK 7u6 on a Windows 7 platform.

How much do you know about the Java language and platform? I've developed a 150-question quiz to help you assess your Java knowledge level. Below is an example question:

Which one of the following is not a Javadoc tag?

(a) @author

(b) {@link}

(c) @serialField

(d) @arg

This quiz is organized into interleaved true/false and multiple choice categories, and focuses on Java Platform, Standard Edition (SE) 1 through Java SE 7.

The quiz is available for Amazon Kindle devices only. Check out Amazon's The BIG Java Quiz to obtain a copy.

Recommended
Join the discussion
Be the first to comment on this article. Our Commenting Policies
See more