how to create a multi worksheet excel jasper repor

1

I want to create excel report which has multiple sheet is it posible to create using iReport?

megmir04's picture
Joined: Apr 9 2010 - 7:56am
Last seen: 7 years 4 months ago

7 Answers:

2

Yes it is possible.  In iReport set the report property  net.sf.jasperreports.export.xls.one.page.per.sheet  to true.  Then the problem is how to make sure that page breaks occur only where you want to start a new worksheet.

What I have done is create a subreport for each worksheet.  These subreports have the Ignore pagination property checked.  The master report also has Ignore pagination checked.  Each subreport goes in its own group header and I put a Page Break element after each subreport element.  Then each subreport ends up appearing in its own Excel sheet.

 

 

markbarnard's picture
Joined: Nov 19 2009 - 3:01pm
Last seen: 7 years 9 months ago
0

Also see the net.sf.jasperreports.export.xls.break.before.row and net.sf.jasperreports.export.xls.break.after.row properties at http://jasperreports.sourceforge.net/config.reference.html

Regards,

Lucian

lucianc's picture
6604
Joined: Jul 17 2006 - 1:10am
Last seen: 5 days 16 min ago
0

I have a report created that breaks to a new page on a new group.  When I use the excel preview option, I get one page per sheet, as I want.

However, when I uploaded the report to the jaspersever repository and ran it there with the export set to xls, the resulting report was ONE sheet, and all the page headers were gone, it was just all the rows from the first page, then immediately all the rows from the second, etc.

Why is it doing this ?  Do I have to configure something else? I set the export property mentioned above in iReport, but it doesn't appear that it transfers to the repository.  How can I correct it so that I get a multi-worksheet excel file for my output when I run from jasperserver?

jrot's picture
24
Joined: Apr 8 2010 - 12:30pm
Last seen: 7 years 4 months ago
0

On JasperServer, in WEB-INF/classes/jasperreports.properties, do you have:

# report exporter hints override export parameters
net.sf.jasperreports.export.parameters.override.report.hints=false

The "report exporter hints" are the properties you set in the report.  You want "false" so these report properties aren't overridden.

You'll find the export parameters (parameters to the exporter classes) defined in WEB-INF/applicationContext.xml

 


 

 

markbarnard's picture
Joined: Nov 19 2009 - 3:01pm
Last seen: 7 years 9 months ago
0
Hi, I am using ireports . I have been trying to apply above solution to split a report into multiple sheet when exporting to excel. However when I apply a page break elememt, it's splitting the main report data also into multiple sheet. How can I avoid this?
pallabibiswas's picture
Joined: Apr 27 2015 - 8:53pm
Last seen: 1 year 9 months ago
0

 

Hello I modified the XML file in iReport of my report, I add the property net.sf.jasperreports.export.xls.one.page.per.sheet and I set it to true like this 

<?xml version="1.0" encoding="UTF-8"?>
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="report2" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="83797c14-49fe-467a-97ad-c4f02395acf3">
<property name="ireport.zoom" value="1.0"/>
<property name="ireport.x" value="0"/>
<property name="ireport.y" value="22"/>
 <property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/>
<style name="Title" fontName="Times New Roman" fontSize="50" isBold="true" pdfFontName="Times-Bold"/>
<style name="SubTitle" forecolor="#736343" fontName="Arial" fontSize="18"/>
<style name="Column header" forecolor="#666666" fontName="Arial" fontSize="12" isBold="true"/>
<style name="Detail" fontName="Arial" fontSize="12"/>
<style name="Row" mode="Transparent">

And when I test it in my JSF proyect it works.

 

 

 

tiuh22's picture
2
Joined: Jun 27 2016 - 7:25pm
Last seen: 1 year 1 month ago
0

This worked for me:

Have "Ignore Pagination" set to true or checked at the report level. Have the following two properties set in your report jrxml:

<property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true"/>

<property name="net.sf.jasperreports.page.break.no.pagination" value="apply"/>

Then whenever you want a new worksheet in your report you define a page break at that location.

 

 

jduin's picture
2
Joined: Jun 27 2017 - 12:24pm
Last seen: 1 month 2 weeks ago
Feedback