Excel – Exporting a report with multiple sheets from JasperServer to Excel

excelexportjasper-reportsjasperserver

I created a report using iReport and set the 'One Page per Sheet' option to true in the Excel Export parameters, this generates a report in iReport with multiple sheets.

I then loaded the report onto JasperServer, the report's output appears on multiple pages, exporting the report to Excel results in the data appearing on one sheet instead of multiple sheets.

Is there a way or an option that can be set so that the data appears on multiple sheets when exported from JasperServer?

Thank you.

Best Answer

You can use the net.sf.jasperreports.export.xls.one.page.per.sheet property.

The information about this property is here.

The property can be added to the jasperreports.properties file (in *jasperserver\WEB-INF\classes* folder):

net.sf.jasperreports.export.xls.one.page.per.sheet=true

The property can be added to the report template (jrxml file)

The sample:

<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" ...>
    <property name="ireport.zoom" value="1.0"/>
    <property name="ireport.x" value="0"/>
    <property name="ireport.y" value="0"/>
    <property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/>

Related Solutions

Excel – How to export a JasperReport to an Excel file with multiple worksheets

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.

R – Easy way to export multiple data.frame to multiple Excel worksheets

You can write to multiple sheets with the xlsx package. You just need to use a different sheetName for each data frame and you need to add append=TRUE:

library(xlsx)
write.xlsx(dataframe1, file="filename.xlsx", sheetName="sheet1", row.names=FALSE)
write.xlsx(dataframe2, file="filename.xlsx", sheetName="sheet2", append=TRUE, row.names=FALSE)

Another option, one that gives you more control over formatting and where the data frame is placed, is to do everything within R/xlsx code and then save the workbook at the end. For example:

wb = createWorkbook()

sheet = createSheet(wb, "Sheet 1")

addDataFrame(dataframe1, sheet=sheet, startColumn=1, row.names=FALSE)
addDataFrame(dataframe2, sheet=sheet, startColumn=10, row.names=FALSE)

sheet = createSheet(wb, "Sheet 2")

addDataFrame(dataframe3, sheet=sheet, startColumn=1, row.names=FALSE)

saveWorkbook(wb, "My_File.xlsx")

In case you might find it useful, here are some interesting helper functions that make it easier to add formatting, metadata, and other features to spreadsheets using xlsx: http://www.sthda.com/english/wiki/r2excel-read-write-and-format-easily-excel-files-using-r-software

Best Answer

Related Solutions

Excel – How to export a JasperReport to an Excel file with multiple worksheets

R – Easy way to export multiple data.frame to multiple Excel worksheets

Related Topic