Thursday, February 21, 2008

JasperReport Export to Excel Snippet code

Sample code that will export JasperReport(.jasper) to excel, pay attention to the bold code
that's all the tricks. The sample servlet will accept two parameter the date and the name of the report.


/*
* JasperToExcel.java
*/

import bean.report.RowStatistics;
import java.io.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Collection;
import java.util.HashMap;
import java.util.Iterator;
import javax.servlet.*;
import javax.servlet.http.*;
import net.sf.jasperreports.engine.JRDataSource;
import net.sf.jasperreports.engine.JRException;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperRunManager;
import net.sf.jasperreports.engine.data.JRMapCollectionDataSource;
import net.sf.jasperreports.engine.export.JExcelApiExporter;
import net.sf.jasperreports.engine.export.JRXlsExporterParameter;

/**
*
* @author amontejo
* @version
*/
public class JasperToExcel extends HttpServlet {

public static final String REPORT_DIRECTORY = "/reports";

protected void processRequest(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
HttpSession session = request.getSession();
String reportName = request.getParameter("reportname");
String selectedyear = request.getParameter("SelectedYear");
InputStream reportStream = getServletConfig().getServletContext().getResourceAsStream("/" + REPORT_DIRECTORY + "/" + reportName + ".jasper");

ServletContext context = this.getServletConfig().getServletContext();
JasperPrint jasperPrint = null;
HashMap parameterMap = new HashMap();

parameterMap.put("parayear", new String(selectedyear));

try {
ServletOutputStream servletOutputStream = response.getOutputStream();
JRDataSource dataSource = createReportDataSource(request, selectedyear, reportName);
jasperPrint = JasperFillManager.fillReport(reportStream, new HashMap(), dataSource);
generateXLSOutput(reportName, jasperPrint, response);
} catch (Exception e) {
}
}

private String tagreport(String string) {
java.util.Calendar calendar = java.util.Calendar.getInstance();
return string + calendar.get(calendar.MONTH) + calendar.get(calendar.DAY_OF_MONTH) + calendar.get(calendar.YEAR);
}

private void generateXLSOutput(String reportname,
JasperPrint jasperPrint,
HttpServletResponse resp)
throws IOException, JRException {
String reportfilename = tagreport(reportname) + ".xls";
JExcelApiExporter exporterXLS = new JExcelApiExporter();

exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT, jasperPrint);
exporterXLS.setParameter(JRXlsExporterParameter.IS_DETECT_CELL_TYPE, Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);
exporterXLS.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);
exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, resp.getOutputStream());
resp.setHeader("Content-Disposition", "inline;filename=" + reportfilename);
resp.setContentType("application/vnd.ms-excel");

exporterXLS.exportReport();
}


//
/** Handles the HTTP GET method.
* @param request servlet request
* @param response servlet response
*/
protected void doGet(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}

/** Handles the HTTP POST method.
* @param request servlet request
* @param response servlet response
*/
protected void doPost(HttpServletRequest request, HttpServletResponse response)
throws ServletException, IOException {
processRequest(request, response);
}

/** Returns a short description of the servlet.
*/
public String getServletInfo() {
return "Short description";
}
//

}

15 comments:

  1. i am also using same JExcelApiExporter but it gives exception


    Caused by: java.lang.NoClassDefFoundError: jxl/JXLException

    ReplyDelete
  2. Download this jar: jxl-2.4.2.jar

    ReplyDelete
  3. Thanks Allen, that code worked nice to me.
    Marcio Belo, Brazil.

    ReplyDelete
  4. Thanks Allen, easy clean and fast what more?

    ;D
    Lucho Boreno, Spain.

    ReplyDelete
  5. Hi Allen,
    Can you please guide me how to create jasper report using hibernate for a web application using servlet.
    Thanks in advance.

    Anoj

    ReplyDelete
  6. hi Anoj,

    thanks for your comment. okay i will make a blog on that of your request.

    thanks,
    Allen Montejo

    ReplyDelete
  7. Hi Allen,

    I'm doing an assignment for school and need to locate 5 companies with websites that include Java Servlets or JSPs. How can I find these? How do I determine if a site is using servlets or JSPs?

    Thanks, Julie

    ReplyDelete
  8. Very helpful indeed. But Aspose is also offering some products for JasperReports developers to export their reports in PowerPoint (PPT, PPS) and Word document (DOC, DOCX, HTML and TXT) formats. Another export format Excel (XLS) is about to be supported in near future. I hope this reference URL will help http://www.aspose.com/categories/jasperreports-exporters/default.aspx

    ReplyDelete
  9. Do you know how to generate a protected excel report with jasperreports? If you do, can you tell me how?

    ReplyDelete
  10. 谢谢!我得到了很大帮助!

    ReplyDelete
  11. HI,
    any one tell me how to set width & height dynamically to excel sheet cells.
    I am generating the xls from JasperReports (DynamicJasper).

    thanks
    tirumala

    ReplyDelete