How do you export a JasperReport to an Excel file with multiple worksheets?

Question

We have a report that the customer would like to have exported to an excel format where it has multiple worksheets. Essentially the two queries share the same parameters, but everything else is different.

In jasper-reports how do you export to an excel file with multiple worksheets (ideally from different data sources)?

Answer 1

Here you have a code example.

HTH!

PS: as the code is large, I am not posting it here. Please post your solution once you cleaned up the excedent :)

Answer 2

Thanks to belisarius link we seem to have figured it out. The basics of how to do it is create your JasperPrint objects for each sheet as you normally would. So you have:

JasperPrint firstWorkSheet = ...;
JasperPrint secondWorkSheet = ...;

The JasperPrint objects are already filled with the datasource at this point. Then you do:

List<JRPrintPage> pages = new ArrayList<JRPrintPage>(secondWorkSheet.getPages());
int i = firstWorkSheet.getPages().size();
for (int count = 0; count < pages.size(); count++) {
    firstWorkSheet.addPage(i, (JRPrintPage) pages.get(count));
    i++;
}

What this does it sets i to the number of pages currently in the firstWorkSheet (which should be one). Then it loops thourgh the pages in the secondWorkSheet and adds them to the firstWorkSheet.

Make sure in you jasperReport you have it set to print as one page for each of the work sheet jrxml files and you should be good to go. I will come update this if anything changes, but this should work.

UPDATE:

Discovered you need to use net.sf.jasperreports.engine.export.ooxml.JRXlsxExporter

instead of

net.sf.jasperreports.engine.export.JRXlsExporter

as there seems to be an issue when exporting to multiple work sheets.

Also the setting in the jrxml file for isIgnorePagination needs to be:

isIgnorePagination="true"

so that each jrxml file is exported as a single page.

Then you need to set JRXlsExporterParameter.IS_ONE_PAGE_PER_SHEET paremeter to true so it breaks out each page to a seperate worksheet.