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
