http://www.developer.com/

Back to article

Using Java to Import and Manipulate Microsoft Excel Documents


February 6, 2009

About the JExcel API

JExcel is an API that allows manipulation of Excel spreadsheets from Java applications. JExcel API is a stable open source product that has been around since 2003. The API is very simple to use and yet very powerful. Aside from basic features such as reading, writing, and modifying Excel spreadsheets, the product offers more complex features such as reading and writing formulas, supporting fonts, number and date formatting, supporting shading, bordering, and coloring of cells, internationalization, copying of charts, and support for insertion and copying of images into spreadsheets.

The JExcelApi home page can be found at http://jexcelapi.sourceforge.net/.

The JExcelApi JAR, jxl.jar, can be downloaded at http://www.java2s.com/Code/JarDownload/jxl.jar.zip.

How to Use the API

Without much ado, I will go through two examples. The first example will demonstrate how to 1) import an Excel spreadsheet and 2) read and manipulate its data. The second example will demonstrate how to export the spreadsheet back to the client.

Example 1: Importing and Manipulating Excel Spreadsheets

For the purpose of simplifying file access operations, I have decided to use Struts in my example.

This example will work with the following spreadsheet that can be found here and at the end of the article. The following spreadsheet will be imported into the example, modified, and returned back to the client.

Name Age
Michele 10
Alan 8
Terry 12
Marry 4
Kyle 5
Mark 7
Alex 8
Ana 4
Maria 3

1. Create a File Upload Form

The first step is to create a JSP that will allow the client to select the spreadsheet for uploading to the server (see Listing 1).

Listing 1: Excel File Upload Form

<%@ taglib uri="/WEB-INF/tlds/struts-html.tld"
           prefix="html"%>
<html>
<head>
<title>Struts File Upload</title>
<html:base />
</head>
<html:form action="/uploadExcel" method="post"
           enctype="multipart/form-data">
   <table>
      <tr>
         <td align="left" colspan="3"><font color="red">
            <html:errors /></font>
         </td>
      </tr>
      <tr>
         <td align="right">Select Microsoft Excel File :  </td>
         <td>
            <html:file property="excelFile"/>
         </td>
         <td>
            <html:submit>Upload File</html:submit>
         </td>
      </tr>
   </table>
</html:form>
</body>
</html>

A Struts <html:file> tag allows you to map to the data type org.apache.struts.upload.FormFile (see the next step).

Form content type "multipart/form-data" is used for submitting forms that contain files, non-ASCII data, and binary data. To get more information on this content type, please refer to http://www.w3.org/TR/html401/interact/forms.html#h-17.13.4.2.

2. Create a Struts Action Form

The next step is to create an Action Form that will hold the uploaded file (see Listing 2).

Listing 2: StrutsUploadForm action form

package test.excel.form;

import org.apache.struts.action.*;
import org.apache.struts.upload.FormFile;

public class StrutsUploadForm extends ActionForm {
   private FormFile excelFile;

   public FormFile getExcelFile() {
      return excelFile;
   }

   public void setExcelFile(FormFile excelFile) {
      this.excelFile = excelFile;
   }
}

3. Struts Action Code

Struts action will have code to get the file from the StrutsUploadForm action form, check content type, and pass File's input stream to the Workbook class. Workbook is a JExcelApi class that represents a Workbook. The class contains the various factory methods and provides a variety of accessors that provide access to the work sheets (see Listing 3).

Listing 3: Excerpt from ExcelUploadAction.java struts action class

...
StrutsUploadForm uploadForm = (StrutsUploadForm)form;;
FormFile myFile = uploadForm.getExcelFile();
Workbook workbook =
   Workbook.getWorkbook(myFile.getInputStream());
...

Now that you have the spreadsheet read into the Workbook class, you can start iterating through its rows. The following code will get the first sheet from the workbook, get the number of rows and columns, and iterate thought each row, displaying the content of each cell (see Listing 4).

Listing 4: Excerpt from ExcelUploadAction.java struts action class

...
Sheet sheet = workbook.getSheet(0);
int numberOfRows = sheet.getRows();
int numberOfColumns = sheet.getColumns();
for (int row = 0; row < numberOfRows; row ++ ) {
   for (int column = 0; column < numberOfColumns; column ++ ) {
            Cell cell = sheet.getCell(column,row);
            System.out.print(cell.getContents() + " | ");
   }
   System.out.println();
}
...

In the following code snippet, you will fetch all values under the column name "Age" and calculate an average age.

Listing 5: Calculating average Age in Java

...
LabelCell labelCell = sheet.findLabelCell("Age");
int ageColumnNumber = labelCell.getColumn();
double ageSum = 0;
for (int row = 1; row < numberOfRows; row ++ ) {
Cell cell = sheet.getCell(ageColumnNumber,row);
   if (CellType.NUMBER.equals(cell.getType())){
      ageSum = ageSum + Integer.parseInt(cell.getContents());
   }
}

double averageAge =  ageSum / (numberOfRows - 1);
System.out.println("Sum Age : " + ageSum);
System.out.println("Average Age : " + averageAge);
...

Example 2: Output an Excel File from a Servlet

In this example, you will create an Excel spreadsheet and return it back to the browser.

As you will see from the example below, creating an Excel spreadsheet using JExcelApi is a fairly simple process. In your doPost() method, set HTTP Response content type to "ms-excel" and set Content-Disposition to "attachment" and provide the attachment's file name.

Listing 6: Output an Excel file from a Servlet

...
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition",
   "attachment; filename=sampleName.xls");
WritableWorkbook writableWorkbook =
   Workbook.createWorkbook(response.getOutputStream());
WritableSheet writableSheet =
   writableWorkbook.createSheet("Demo", 0);
writableSheet.addCell(new Label(0, 0, "Hello World"));
writableWorkbook.write();
writableWorkbook.close();
...

The output of this example creates a spreadsheet "sampleName.xls" that contains one worksheet that contains "Hello World" text in cell A1.

Download the Code

The Source Code for this article can be downloaded here.

Reference

About the Author

Aleksey Shevchenko has been working with object-oriented languages for eight years. For the past four years, he has served as a technical lead and a project manager. Aleksey has been implementing Enterprise IT solutions for Wall Street and the manufacturing and publishing industries.

Sitemap | Contact Us

Thanks for your registration, follow us on our social networks to keep up-to-date