JExcelApi allows Java developers to read Excel spreadsheets and to generate Excel spreadsheets dynamically. In addition, it contains a mechanism which allows java applications to read in a spreadsheet, modify some cells and write out the new spreadsheet.

This API allows non Windows operating systems to run pure Java applications which can both process and deliver Excel spreadsheets. Because it is Java, this API may be invoked from within a servlet, thus giving access to Excel functionality over internet and intranet web applications.

Download JExcelApi JAR files from

http://jexcelapi.sourceforge.net/

Reading Spreadsheets

Here is the sample program which uses the javaexcel

Program to read the Excel.

package com;

import java.io.File;

import java.io.IOException;

import jxl.*;

import jxl.read.biff.BiffException;

public class JavaExcel {

public static void main(String args[]) {

readExcel();

}

public static void readExcel() {

try {

Workbook workbook = Workbook.getWorkbook(new File(“Res.xls”));

Sheet sheet = workbook.getSheet(0);

Cell a1 = sheet.getCell(1, 13);

String stringa1 = a1.getContents();

System.out.println(stringa1);

workbook.close();

} catch (BiffException e) {

e.printStackTrace();

} catch (IOException e) {

e.printStackTrace();

}

}

}

Input excel file (place it in the classpath):

Following is the Output of the program.

Deepak

Dinesh

Fundamentals

JExcelApi can read an Excel spreadsheet from a file stored on the local file system or from some input stream. The first step when reading a spreadsheet from a file or input stream is to create a Workbook. The code fragment below illustrates creating a workbook from a file on the local file system.

Workbook workbook = Workbook.getWorkbook(new File(“myfile.xls”));

(NOTE: when creating a spreadsheet from a ServletInputStream you must remove the HTTP header information before creating the Workbook object.)

Once you have accessed the workbook, you can use this to access the individual sheets. These are zero indexed – the first sheet being 0, the second sheet being 1, and so on. (You can also use the API to retrieve a sheet by name).

Sheet sheet = workbook.getSheet(0);

Once you have a sheet, you can then start accessing the cells. You can retrieve the cell’s contents as a string by using the convenience method getContents(). In the example code below, A1 is a text cell, B2 is numerical value and C2 is a date. The contents of these cells may be accessed as follows

Cell a1 = sheet.getCell(0,0);
Cell b2 = sheet.getCell(1,1);
Cell c2 = sheet.getCell(2,1);

String stringa1 = a1.getContents();
String stringb2 = b2.getContents();
String stringc2 = c2.getContents();

// Finished – close the workbook and free up memory
workbook.close();

Writing Spreadsheets

Sample program to write contents in the Excel.

package com;

import java.io.File;

import java.io.IOException;

import jxl.*;

import jxl.write.Label;

import jxl.write.Number;

import jxl.write.WritableSheet;

import jxl.write.WritableWorkbook;

import jxl.write.WriteException;

import jxl.write.biff.RowsExceededException;

public class JavaExcelWr {

public static void main(String args[]) {

writeExcel();

}

public static void writeExcel() {

try {

WritableWorkbook wrworkbook = Workbook.createWorkbook(new File(

“output.xls”));

WritableSheet wrsheet = wrworkbook.createSheet(“First Sheet”, 0);

Label label = new Label(0, 0, “label record 1″);

wrsheet.addCell(label);

label = new Label(0, 1, “label record 2″);

wrsheet.addCell(label);

label = new Label(0, 2, “label record 3″);

wrsheet.addCell(label);

label = new Label(0, 3, “label record 4″);

wrsheet.addCell(label);

Number number = new Number(3, 4, 3.1459);

wrsheet.addCell(number);

wrworkbook.write();

wrworkbook.close();

} catch (IOException e) {

e.printStackTrace();

} catch (RowsExceededException e) {

e.printStackTrace();

} catch (WriteException e) {

e.printStackTrace();

}

}

}

Following is the Output of the program.

Fundamentals

Similarly to reading a spreadsheet, the first step is to create a writable workbook using the factory method on the Workbook class.

WritableWorkbook workbook = Workbook.createWorkbook(new File(“output.xls”));

This creates the workbook object. The generated file will be located in the current working directory and will be called “output.xls”. The API can also be used to send the workbook directly to an output stream eg. from a web server to the user’s browser. If the HTTP header is set correctly, then this will launch Excel and display the generated spreadsheet.

The next stage is to create sheets for the workbook. Again, this is a factory method, which takes the name of the sheet and the position it will occupy in the workbook. The code fragment below creates a sheet called “First Sheet” at the first position.

WritableSheet sheet = workbook.createSheet(“First Sheet”, 0);

Now all that remains is to add the cells into the worksheet. This is simply a matter of instantiating cell objects and adding them to the sheet. The following code fragment puts a label in cell A3, and the number 3.14159 in cell D5.

Label label = new Label(0, 2, “A label record”);
sheet.addCell(label);

Number number = new Number(3, 4, 3.1459);
sheet.addCell(number);

There are a couple of points to note here. Firstly, the cell’s location in the sheet is specified as part of the constructor information. Once created, it is not possible to change a cell’s location, although the cell’s contents may be altered.

The other point to note is that the cell’s location is specified as (column, row). Both are zero indexed integer values – A1 being represented by (0,0), B1 by (1,0), A2 by (0,1) and so on.

Once you have finished adding sheets and cells to the workbook, you call write() on the workbook, and then close the file. This final step generates the output file (output.xls in this case) which may be read by Excel. If you call close() without calling write() first, a completely empty file will be generated.

// All sheets and cells added. Now write out the workbook
workbook.write();
workbook.close();

Download JAR files from

http://jexcelapi.sourceforge.net/

FAQ has details on how to read an Excel File uploaded through a Web browser to a Servlet and also about how to Output an Excel file from a Servlet.

FAQ link is http://jexcelapi.sourceforge.net/resources/faq/