Home » TechBlog » Generate and Send an Excel file from J2EE WebApplication

Generate and Send an Excel file from J2EE WebApplication

Generating Excel file in J2EE Server.

 

This article is written to describe a simple way of generating an Excel file in Java. We will be using an open source Java Excel API  ( jxl http://jexcelapi.sourceforge.net/ ) to dynamically generate an excel file and reading and modifying an existing excel file.

 

Using this technology we can directly generate an Excel file on the fly on a user request.

In a typical web application a user can request for an excel sheet based report from a web based application.

 

With the proper integration of this API and the server side backend data layer, we can transform the fetched data into an excel sheet. Later this excel sheet can be sent to client side using proper http response header information.

 

To begin with the application we need to download the jxl.jar file and put it under the project folder.  To download go to the URL: http://sourceforge.net, and under File section you have a link to download the required jar files.

 

 

Code side view:

We may need following classes depending on our need:

1)

import jxl.Workbook;

import jxl.format.Colour;

import jxl.format.UnderlineStyle;

import jxl.write.Label;

import jxl.write.Number;

import jxl.write.WritableCellFormat;

import jxl.write.WritableFont;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

 

 

2) Create workflow object:

WritableWorkbook  wrtWorkbook = Workbook.createWorkbook(new

File(“MyExcel.xls”));

WritableSheet sheet1 ;

 

This function will create a writable workBook object. We can specify the location of the Excel file otherwise it will create in the current running folder. Later using defined API’s we can add worksheets on this workbook.

Note in case on reading excel sheet, we need to create WorkBook class

Object not WritableWorkBook.

 

 

3) Create worksheet :

sheet1 = this.wrtWorkbook.createSheet(“Sheet1”,0);

params: sheetname and location.

Here we have created a worksheet for the workbook, minimum one worksheet is required for a workbook to write some data in it. Like an Excel file one workbook can also contain more that one Sheet.

 

4) Adding cells :

sheet1.addCell(number);

Once the sheet object is created, we will use following API to write data in the specified cells. Here we are giving very simple demonstration but if require it also supports various kind of formatting.

To add number

Number number = new Number(colIndex, rowIndex, num);

sheet1.addCell(number);

 

To add string

Label label = new Label(colIndex, rowIndex, str);

sheet1.addCell(label);

 

5) Formatted Data

We can also specify specific format by using defined format classes:

WritableFont times10font = new WritableFont(WritableFont.TIMES, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BROWN);

 

WritableCellFormat times10format = new WritableCellFormat

Label label = new Label(colIndex, rowIndex, str, times10format );

 

 

6) Closing Worksheet

wrtWorkbook.write(); this function will write sheet into workBook

wrtWorkbook.close();

 —————————————————————–

  

Sending an Excel file along with the HTTP Response.

 

In a typical J2EE web application an http Request is processed in doGet\doPost method and a response is generated and send back to the client. We need to set response header to provide specific information about the content it carrying, so that browser can automatically detect the compatible application to open that.

resp.setHeader(“Cache-Control”, “max-age=30”);

resp.setContentType(“application/vnd.ms-excel”);

 

application/vnd.ms-excel defines that it is an excel spreadsheet application.

We can defile to send content in inline format or as an attachment.

resp.setHeader(“Content-disposition”,“inline; filename=MyExcel.xls);

 

here using java.io API’s we will write Excel file content in the response object.

 

ServletOutputStream sos = response.getOutputStream();

FileInputStream fio = new FileInputStream(“./” + “MyExcel.xls”);

 

while((c = fio.read()) != -1) {

          sos.write(c);

}

 

And that’s all. Its all set and Please go and receive the Excel file in the client’s browser.

 

Regards

Manjul  

 

 

Advertisements

4 Comments

  1. Benjamin says:

    This is a nice article.
    Users often need an excel output. I think this is one good way to do that.
    Thanks!

  2. I have used apache POI libraries in the past and it was easy to use.

  3. kumar says:

    this one is simplere to use compare to apache poi.

  4. Priya says:

    How can we write data into existing excel doc, without creating a new document. I want to append data into existing excel.

Leave a Reply to Priya Cancel reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: