Logo Questions Linux Laravel Mysql Ubuntu Git Menu

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

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)?

like image 546
Jacob Schoen Avatar asked Oct 20 '10 12:10

Jacob Schoen

People also ask

How do you export excel with multiple sheets?

By default, Excel will only export the active worksheet. If you have multiple worksheets and want to save all of them in the same PDF file, click Options in the Save As dialog box. The Options dialog box will appear. Select Entire workbook, then click OK.

How do I export multiple sheets?

Using the Move or Copy command will help you export or copy one or several worksheets to a new workbook quickly. Step 1: Select the worksheet names in tab bar. You can select multiple with holding down Ctrl key or shift key. Step 2: Right click the worksheet name, and click the Move or Copy from context menu.

How do I make a two page Jasper report?

To add a new page go to Ireport and "Add Report Group" ,by adding report group you can add as many pages you want. Save this answer.

2 Answers

Thanks to this thread it really was easier for me to create an Excel export with multiple sheets. What I found out was that you could use the following:

ArrayList<JasperPrint> list = new  ArrayList<JasperPrint>();
list.add(jp1); list.add(jp2);
exporter.setParameter(JRXlsExporterParameter.JASPER_PRINT_LIST, list);

and the exporter will automatically use every JasperPrint object to construct each sheet; also the name of the Jasper report (as specified in the jrxml file) is used as the name of each sheet.

Currently this solution works on my local project, so I just wanted to let you know.

like image 135
andrei Avatar answered Oct 10 '22 02:10


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

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.


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

instead of


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:


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

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

like image 24
Jacob Schoen Avatar answered Oct 10 '22 03:10

Jacob Schoen