April 20, 2014
Hot Topics:
RSS RSS feed Download our iPhone app

Using Java to Import and Manipulate Microsoft Excel Documents

  • February 6, 2009
  • By Aleksey Shevchenko
  • Send Email »
  • More Articles »

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;
   }
}




Page 1 of 2



Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 


Sitemap | Contact Us

Rocket Fuel