Monday, March 17, 2008

Generate Excel Reports with JXL.jar

import java.io.File;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Vector;

import jxl.Workbook;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;


public class ExcelReport {

public void GenerateReport(HashMap hmHdrs, Vector vecData)throws Exception {

WritableWorkbook workbook = Workbook.createWorkbook(new File("output.xls"));
WritableSheet sheet = workbook.createSheet("WRMS Report", 0);
WritableFont arialbld=new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, true);
WritableFont arialfnt = new WritableFont(WritableFont.ARIAL, 10);

Vector keyset=new Vector();
Iterator itr=hmHdrs.keySet().iterator();
int i=0;
WritableCellFormat exelwrtfrmt = new WritableCellFormat (arialbld);
while (itr.hasNext()) {
keyset.add(itr.next().toString());
}

int recs=vecData.size();
int cols=keyset.size();
for(int index=0;index<cols;index++){
String value=hmHdrs.get(keyset.get(index).toString()).toString();
Label labl = new Label(index,0, value, exelwrtfrmt);
sheet.addCell(labl);
}
exelwrtfrmt = new WritableCellFormat (arialfnt);
for(int indx=0;indx<recs;indx++){
HashMap rec=(HashMap)vecData.get(indx);
for(int index=0;index<cols;index++){
String value=rec.get(keyset.get(index).toString()).toString();
Label labl = new Label(index,indx+2, value, exelwrtfrmt);
sheet.addCell(labl);
}
}
workbook.write();
workbook.close();
}
}

To download through JSP use
response.setContentType("application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment; filename=sampleName.xls");
WritableWorkbook w = Workbook.createWorkbook(response.getOutputStream());