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 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.

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

I used LibreOffice Calc to access the <code>sample.xls</code> 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.

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

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 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.

Listing 1. Writing and reading a simple spreadsheet


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


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");
      Number number = new Number(3, 4, 3.1459);

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

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

Assuming success, execute the following command to run JExcelAPIDemo:

java -cp jxl.jar;. JExcelAPIDemo

You should observe the following output:

A label record

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

Figure 3. The solitary sheet displays two cell values

The solitary sheet displays two cell values.

What's next?

Next time, I present a set of puzzlers that address the evolution of Java libraries. These puzzlers focus on source and binary code compatibility between client programs and the libraries that these programs use.

Get the source code for this post's applications. Created by Jeff Friesen for JavaWorld

The following software was used to develop the post's code:

  • 64-bit JDK 7u6
  • JExcelAPI 2.6.12

The post's code was tested on the following platform(s):

  • JVM on 64-bit Windows 7 SP1

This story, "Reading and writing Excel spreadsheets" was originally published by JavaWorld.


Copyright © 2013 IDG Communications, Inc.