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.


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.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);


/** 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";



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

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

  2. Download this jar: jxl-2.4.2.jar

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

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

    Lucho Boreno, Spain.

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


  6. hi Anoj,

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

    Allen Montejo

  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

  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

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

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

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