jasper-reports

How to export to multiple sheets in excel from jasper report


I am working on a report with iReport that have many subreports.

I want to have two other sheets in the excel file when generating the report from my application.

When searching the internet I found answers about creating a break in the report, having the option "Ignore pagination " in the subreports "true" , but it is still not clear for me.

What are my options to control how and when a new sheet is created


Solution

  • In jasper report there are different ways to achieve new sheet both in jrxml and in java code. The default behavior is to create a new sheet for every page. I will illustrated the 3 most common ways with relative problem in using them.

    Ignore pagination and break element

    Method

    set isIgnorePagination="true" on the jasperReport tag and add

    <break>
      <reportElement x="0" y="0" width="100" height="1" uuid="c5371aa4-2eb4-4ab9-8cae-39f50da3317b"/>
    </break>
    

    when you need a new sheet.

    Problem: The report will not be beautiful if you export also to pdf (since its ignoring pagination)

    Use the jrxml properties

    Method To avoid creating new sheet on every new page, set property

    net.sf.jasperreports.export.xls.one.page.per.sheet="false"
    

    And when you want it to create a new sheet before or after an reportElement add relative property:

    net.sf.jasperreports.export.xls.break.before.row="true"
    net.sf.jasperreports.export.xls.break.after.row="true"
    

    Problem: The columns on every sheet will be the same and this can result in ugly colspan on different sheet's

    Use java and controll the sheet's as you like (loading different reports)

    Method

    List<JasperPrint> sheets = new ArrayList<JasperPrint>();
    for (int i=1;i<=8;i++){
       JasperPrint print = JasperFillManager.fillReport("subReport_" + i + ".jasper", paramMap, connection);
       sheets.add(print); 
    }
    JRXlsxExporter exporter = new JRXlsxExporter();
    exporter.setExporterInput(SimpleExporterInput.getInstance(sheets));
    exporter.setExporterOutput(new SimpleOutputStreamExporterOutput(new File("text.xlxs"));
    SimpleXlsxReportConfiguration configuration = new SimpleXlsxReportConfiguration();
    configuration.setSheetNames(sheetNames): //sheets names is an array of the different names.
    configuration.setOnePagePerSheet(false); //remove that it break on new page
    configuration.setDetectCellType(true);
    exporter.setConfiguration(configuration);
    exporter.exportReport();
    

    Problem: You can not use this method if you are using jasper report server.