JExcel API

In my last assignment, i got requirment to write data into excel file.I got the solution JExcel API.I got solution in the form of JExcel.Today, i will try to demonstrate JExcel API.
JExcelApi allows developers to read Excel spreadsheets and to generate Excel spreadsheets dynamically.It also contains a mechanism which allows java applications to read in a spreadsheet, modify some cells and write out the new spreadsheet.

Download JExcelApi JAR files from

http://jexcelapi.sourceforge.net/

Benifit of this API is:
Any operating system which can run a Java virtual machine (i.e., not just Windows) can both process and deliver Excel

spreadsheets. Because it is Java, the API can be invoked from within a servlet, thus giving access to Excel spreadsheets over

internet and intranet web applications.

Demo Application:

imported packages are following:
import jxl.Workbook;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;


public ByteArrayOutputStream generateExcelReport() throws IOException, WriteException {
/* Stream containing excel data */
ByteArrayOutputStream outputStream = new ByteArrayOutputStream();

/* Create Excel WorkBook and Sheet */
WritableWorkbook workBook = Workbook.createWorkbook(outputStream);

or writing data in to generated xls
//Workbook workbook = Workbook.getWorkbook(new File(“Deepak.xls”));
WritableSheet sheet = workBook.createSheet("Project List", 0);

/* Generates Headers Cells */
WritableFont headerFont = new WritableFont(WritableFont.TAHOMA, 12, WritableFont.BOLD);
WritableCellFormat headerCellFormat = new WritableCellFormat(headerFont);
headerCellFormat.setBackground(Colour.PALE_BLUE);
sheet.addCell(new Label(1, 1, "Project Id", headerCellFormat));
sheet.addCell(new Label(2, 1, "Project Name", headerCellFormat));

/* Generates Data Cells */
WritableFont dataFont = new WritableFont(WritableFont.TAHOMA, 12);
WritableCellFormat dataCellFormat = new WritableCellFormat(dataFont);

int currentRow = 2;
for (User user : getUsers()) {
sheet.addCell(new Label(1, currentRow, user.getLastName(),dataCellFormat));
sheet.addCell(new Label(2, currentRow, user.getFirstName(),dataCellFormat));
currentRow++;
}

/* Write & Close Excel WorkBook */
workBook.write();
workBook.close();

return outputStream;
}

public List getUsers() {

try{
Class.forName("com.mysql.jdbc.Driver");
Connection

con=DriverManager.getConnection("jdbc:mysql://localhost/pmsdatabase2?user=root&password=mysql");
PreparedStatement ps=con.prepareStatement("select project_code,project_name from project");
ResultSet rs=ps.executeQuery();
while(rs.next()){
users.add(new User(rs.getString(1),rs.getString(2)));
}

}catch(Exception e){
System.out.println("Coming here!!"+e);
}
return users;
}

COde for User.java:

class User{

private String firstName;
private String lastName;
//getter and setter of firstName and lastName
public User(){}

public User(String lastName,String firstName){
this.firstName=firstName;
this.lastName=lastName;

}

}

Call this method by using any servlet.
A complete document is available on sourceforge or other sites.

Thanks!
Deepak