Getting the JasperReports into Excel format

[wadha alketbi]

Hi


I have this doPost method in my Servlet class which is generate the report as PDF format, Now I want to generate this report as an Excel format?

protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		ServletOutputStream servletOutputStream = response.getOutputStream();

		String reportName = "/Reports/Report.jasper";

		Map<String, Object> paramters = new HashMap<String, Object>();
		Date paramtersDate= (Date) request.getAttribute("paramtersDate");
		paramters.put("PARAMTER_DATE", paramtersDate);
		
		System.out.println("parameter value is: "+paramtersDate);


		InputStream reportStream = null;

		try {

			reportStream = getServletConfig().getServletContext().getResourceAsStream(reportName);

			response.setContentType("application/pdf");

			response.setHeader("Content-Disposition", "inline; filename=reportName.pdf;");

			JasperRunManager.runReportToPdfStream(reportStream, servletOutputStream, paramters, getConnection());

			response.setHeader("Cache-Control", "no-cache");

			response.setHeader("Pragma", "no-cache");
			
			servletOutputStream.flush();

			servletOutputStream.close();
		} catch (Exception e) {
			e.printStackTrace();

			throw new RuntimeException("Error on Reports Controller !!!");
		}
	}

Thank you in advance,

[K. Tsang]

Have you try saving the jasper report in excel format in ireport software? Does the layout need changing or such? If not then you can use JRXlsExporter to export to XLS.

Here shows a sample using Jasper 5. The method calls may be outdated/deprecated in Jasper 6.

[wadha alketbi]

Thank you for your help

What you said is right, I'm searching and I found that I have to use JRXlsExporter.

I added the following but it dose not work:

InputStream reportStream = null;
JasperPrint jasperPrint = null;

		try {

			reportStream = getServletConfig().getServletContext().getResourceAsStream(reportName);

			JasperDesign jasperDesign = JRXmlLoader.load(reportStream);

			JasperReport jasperReport = JasperCompileManager.compileReport(jasperDesign);

			jasperPrint = JasperFillManager.fillReport(jasperReport, paramters, getConnection());

			JRXlsExporter exporter = new JRXlsExporter();

			exporter.setParameter(JRExporterParameter.JASPER_PRINT, jasperPrint);
			exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, "report.xls");
			exporter.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.TRUE);
			exporter.setParameter(JRXlsExporterParameter.IS_WHITE_PAGE_BACKGROUND, Boolean.FALSE);
			exporter.setParameter(JRXlsExporterParameter.IS_REMOVE_EMPTY_SPACE_BETWEEN_ROWS, Boolean.TRUE);

			exporter.exportReport();

			servletOutputStream.flush();

			servletOutputStream.close();
		}

Any comments!
Thank you in advance,

[Paul Clapham]

wadha alketbi wrote:Any comments!

My only comment is that "Does not work" is a very unhelpful problem description. Have a look at our FAQ entry ItDoesntWorkIsUseless (<-- that's a link, click it) for help on how to describe a problem in such a way that other people can discuss it.

[wadha alketbi]

Hi,

I have this error when I entered the date:

INFO: Overriding bean definition for bean 'ganttType': replacing [Generic bean: class [org.springframework.beans.factory.config.FieldRetrievingFactoryBean]; scope=singleton; abstract=false; lazyInit=false; autowireCandidate=true; autowireMode=0; dependencyCheck=0; factoryBeanName=null; factoryMethodName=null; initMethodName=null; destroyMethodName=null] with [Generic bean: class [org.springframework.beans.factory.config.FieldRetrievingFactoryBean]; scope=singleton; abstract=false; lazyInit=false; autowireCandidate=true; autowireMode=0; dependencyCheck=0; factoryBeanName=null; factoryMethodName=null; initMethodName=null; destroyMethodName=null]
net.sf.jasperreports.engine.JRException: com.sun.org.apache.xerces.internal.impl.io.MalformedByteSequenceException: Invalid byte 1 of 1-byte UTF-8 sequence.
at net.sf.jasperreports.engine.xml.JRXmlLoader.loadXML(JRXmlLoader.java:251)
at net.sf.jasperreports.engine.xml.JRXmlLoader.loadXML(JRXmlLoader.java:230)
at net.sf.jasperreports.engine.xml.JRXmlLoader.load(JRXmlLoader.java:218)

Caused by: com.sun.org.apache.xerces.internal.impl.io.MalformedByteSequenceException: Invalid byte 1 of 1-byte UTF-8 sequence.

SEVERE: Servlet.service() for servlet ControllerExcel threw exception
java.lang.RuntimeException: Error on Reports Controller !!!

I modified my code and now I have this one, but it dose not work (report does not exporting to Excel)

protected void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {

		ServletOutputStream servletOutputStream = response.getOutputStream();

		String reportName = "/Reports/Report.jasper";

		Map<String, Object> paramters = new HashMap<String, Object>();
		Date paramtersDate= (Date) request.getAttribute("paramtersDate");
		paramters.put("PARAMTER_DATE", paramtersDate);

		System.out.println("parameter value is: " + paramtersDate);

		InputStream reportStream = null;
		JasperPrint jasperPrint = null;

		try {

			reportStream = getServletConfig().getServletContext().getResourceAsStream(reportName);

			JasperDesign jasperDesign = JRXmlLoader.load(reportStream);
			JasperReport jasperReport = JasperCompileManager.compileReport(jasperDesign);
			jasperPrint = JasperFillManager.fillReport(jasperReport, paramters, getConnection());

			// OutputStream outputStream = new FileOutputStream(new
			// File("catalogExxcelReport.xls"));
			ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();

			JRXlsExporter exporter = new JRXlsExporter();
			exporter.setParameter(JRExporterParameter.INPUT_FILE_NAME, jasperPrint);
			exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, byteArrayOutputStream);
			exporter.exportReport();
			byteArrayOutputStream.writeTo(servletOutputStream);

			servletOutputStream.flush();
			servletOutputStream.close();
		} catch (Exception e) {
			e.printStackTrace();

			throw new RuntimeException("Error on Reports Controller !!!");

		}
	}