JasperReports / iReport: How to Export Excel on a Single Sheet

Yes, its quite easy to generate a excel report using iReport . There are many blog guru's ,authors, developers has posted ample of solutions on this topic. But , still some time we are facing this problem.

Generating excel report is easy , but our requirement was all report should be come on a single sheet (not multiple sheet). We had few existing codes. But the problem was we were not about to find our exact solution. Finally after lots of testing & research got the solution.I hope it will help you.

The program code for generating Excel file from iReport :-

ByteArrayOutputStream baos = new ByteArrayOutputStream();
JRXlsExporter exporterXLS = new JRXlsExporter();

// Input and output
exporterXLS.setParameter(JRXlsExporterParameter.JASPER_PRINT, print);
exporterXLS.setParameter(JRXlsExporterParameter.OUTPUT_STREAM, baos);

// Keep everything on a single sheet
exporterXLS.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.FALSE);

// Formatting optimizations
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.exportReport();

// Write to response
resp.setContentType("application/vnd.ms-excel");
resp.setHeader("Content-Disposition", "attachment; filename=" + outputFileName + ".xls");
ServletOutputStream sos = resp.getOutputStream();
sos.write(baos.toByteArray());
sos.flush();
sos.close();

 Now , you just simple remove the below line which has setParameter().When you are setting parameter for your JRXLSReporter.

The Parameter is JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.FALSE

The line i removed is //exporterXLS.setParameter(JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET, Boolean.FALSE);