How can I remove pagination from Excel/CSV export

1

I have many reports written in JasperReports and deployed on the JasperServer.  I need the html version onscreen to paginate and the excel/CSV exports to not paginate.  Pagination in excel exports doesn't make any sense to me.  Is there an option to turn off pagination for excel exports on the JasperServer?  My users want to use the excel exports for analysis and with the page headers embeded in the output it makes that impossible without going through and removing all the headers.

 

I have looked around in the code and can see where I could possibly add a property to the xmlExporterBean and then pass that along to the excel export logic.  If there is no way to turn this off out-of-the-box any help/insight into making the code changes would be appriciated.

 

I have attached a sample export.

Cheers,

 

Joe

jhopkins@jorbe.com's picture
Joined: Feb 20 2008 - 2:29am
Last seen: 11 years 6 months ago

18 Answers:

0

hi,

You can change the setting in $JASPER_ROOT/WEB-INF/applicationContext.xml

- set "onePagePerSheet" to "false"

Further details can be found in my blog: www.raj2u.net

 

Code:
 <bean id="xlsExportParameters" class="com.jaspersoft.jasperserver.api.engine.jasperreports.common.XlsExportParametersBean">
        <property name="detectCellType" value="true"/>
        <property name="onePagePerSheet" value="false"/>
        <property name="removeEmptySpaceBetweenRows" value="false"/>
        <property name="removeEmptySpaceBetweenColumns" value="true"/>
        <property name="whitePageBackground" value="false"/>
        <property name="ignoreGraphics" value="false"/>
        <property name="collapseRowSpan" value="true"/>
        <property name="ignoreCellBorder" value="true"/>
        <property name="fontSizeFixEnabled" value="true"/>
        <property name="maximumRowsPerSheet" value="0"/>
        <property name="xlsFormatPatternsMap" ref="formatPatternsMap"/>
    </bean>
</td></tr></tbody></table>
anandharaj's picture
Joined: Oct 18 2006 - 5:02pm
Last seen: 5 years 10 months ago

This works for xls & xlsx , but what will the fix for CSV be ?

andre.erasmus - 6 years 1 month ago
1

Ths solution worked along with these settings in my jasperreports.properties file.


net.sf.jasperreports.query.executer.factory.sql=com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuterFactory

#limit the crosstab bucket/measure count to prevent out of memory errors
net.sf.jasperreports.crosstab.bucket.measure.limit=100000

# suppress page headers and footers when exporting to XLS
net.sf.jasperreports.export.exclude.origin.band.1=pageHeader
net.sf.jasperreports.export.exclude.origin.band.2=pageFooter

# this gets rid of the repeating column headers and footers in excel exports
net.sf.jasperreports.export.xls.exclude.origin.band.1=columnHeader
net.sf.jasperreports.export.xls.exclude.origin.band.2=pageFooter

# keep the first column header on export
net.sf.jasperreports.export.xls.exclude.origin.keep.first.band.1=columnHeader

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

# use the workaround for the Sun JDK Glyph synchronization bug
net.sf.jasperreports.text.measurer.factory=net.sf.jasperreports.engine.util.JdkGlyphFixTextMeasurerFactory

# the fast/single line text measurer factory
net.sf.jasperreports.text.measurer.factory.single.line=com.jaspersoft.jasperserver.api.engine.jasperreports.util.SingleLineTextMeasurerFactory

# other excel options
#net.sf.jasperreports.export.xls.remove.empty.space.between.rows=true
#net.sf.jasperreports.export.xls.remove.empty.space.between.columns=true
#net.sf.jasperreports.export.xls.detect.cell.type=true
#net.sf.jasperreports.export.xls.create.custom.palette=true

 

Thanks,

 

Joe




 

jhopkins@jorbe.com's picture
Joined: Feb 20 2008 - 2:29am
Last seen: 11 years 6 months ago
2

How do you remove pagination from CSV exports?  I got it working in Excel from your suggestions but I still get a reapeating footer when exporting to CSV.

Here are my jasperreports.properties

#Jdbc Timezone execution factory
net.sf.jasperreports.query.executer.factory.sql=com.jaspersoft.jasperserver.api.engine.jasperreports.util.JRTimezoneJdbcQueryExecuterFactory

#limit the crosstab bucket/measure count to prevent out of memory errors
net.sf.jasperreports.crosstab.bucket.measure.limit=100000

# suppress page headers and footers when exporting to XLS
net.sf.jasperreports.export.exclude.origin.band.1=pageHeader
net.sf.jasperreports.export.exclude.origin.band.2=pageFooter

# this gets rid of the repeating column headers and footers in excel exports
net.sf.jasperreports.export.xls.exclude.origin.band.1=columnHeader
net.sf.jasperreports.export.xls.exclude.origin.band.2=pageFooter

# keep the first column header on export to XLS
net.sf.jasperreports.export.xls.exclude.origin.keep.first.band.1=columnHeader

# this gets rid of the repeating column headers and footers in CVS exports
net.sf.jasperreports.export.cvs.exclude.origin.band.1=columnHeader
net.sf.jasperreports.export.cvs.exclude.origin.band.2=pageFooter

# keep the first column header on export to CVS
net.sf.jasperreports.export.cvs.exclude.origin.keep.first.band.1=columnHeader

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

# use the workaround for the Sun JDK Glyph synchronization bug
net.sf.jasperreports.text.measurer.factory=net.sf.jasperreports.engine.util.JdkGlyphFixTextMeasurerFactory

# the fast/single line text measurer factory
net.sf.jasperreports.text.measurer.factory.single.line=com.jaspersoft.jasperserver.api.engine.jasperreports.util.SingleLineTextMeasurerFactory

# other excel options (applicationContext.xml overrides these settings)
#net.sf.jasperreports.export.xls.remove.empty.space.between.rows=true
#net.sf.jasperreports.export.xls.remove.empty.space.between.columns=true
#net.sf.jasperreports.export.xls.detect.cell.type=true
#net.sf.jasperreports.export.xls.create.custom.palette=true

 

Thanks,

 

Joe

 

jhopkins@jorbe.com's picture
Joined: Feb 20 2008 - 2:29am
Last seen: 11 years 6 months ago
0

I'm not sure if those options work, they aren't listed here: http://jasperforge.org/website/jasperreportswebsite/trunk/config.reference.html?group_id=252

Also, maybe you have a spelling error, you have "cvs" instead of "csv" in what you paste from your properties file.

carminez's picture
Joined: Oct 2 2008 - 12:06pm
Last seen: 10 years 10 months ago
0
Oops.. I meant .csv. in the above code sample. :-) # this gets rid of the repeating column headers and footers in CSV exports net.sf.jasperreports.export.csv.exclude.origin.band.1=columnHeader net.sf.jasperreports.export.csv.exclude.origin.band.2=pageFooter # keep the first column header on export to CSV net.sf.jasperreports.export.csv.exclude.origin.keep.first.band.1=columnHeader
jhopkins@jorbe.com's picture
Joined: Feb 20 2008 - 2:29am
Last seen: 11 years 6 months ago
0
Thanks for the link...that's exactly what I need...
jhopkins@jorbe.com's picture
Joined: Feb 20 2008 - 2:29am
Last seen: 11 years 6 months ago
1
Ok, that worked once I fixed my typing errors. Same properties as Excel just replace xls with csv and pagination is removed on export. Thanks, Joe
jhopkins@jorbe.com's picture
Joined: Feb 20 2008 - 2:29am
Last seen: 11 years 6 months ago
0

Any ideas on how to remove the Page Header from the CSV?

 

I really only need the column headings in the CSV, having the Page Header display requires the user to have to manipulate the CSV to import the data.

carminez's picture
Joined: Oct 2 2008 - 12:06pm
Last seen: 10 years 10 months ago
0

I noticed that if there is a sub-report in the band being excluded, the band will not get excluded. Does anyone know why this is, is there any way around it?

jasons's picture
5
Joined: Dec 16 2008 - 2:57pm
Last seen: 10 years 8 months ago
0

jasons
Wrote:

I noticed that if there is a sub-report in the band being excluded, the band will not get excluded. Does anyone know why this is, is there any way around it?

That's mainly a design bug of JasperReports.  The next JR release will make possible a workaround for this which will require the subreport to be nested inside a frame in the master report.  We are also investigating proper solutions to this for the long term.

Regards,

Lucian

lucianc's picture
7075
Joined: Jul 17 2006 - 1:10am
Last seen: 1 week 4 days ago
0

Can you offer any more details on this solution?  A number of JR versions have been released since this post.  Is there a workaround for this subreport problem?



Post Edited by cdeal at 08/12/2009 17:27
cdeal's picture
5
Joined: Nov 27 2007 - 1:05am
Last seen: 11 years 9 months ago
0

I used the properties below to

# this gets rid of the repeating column headers and footers in excel exports
net.sf.jasperreports.export.xls.exclude.origin.band.1=columnHeader
net.sf.jasperreports.export.xls.exclude.origin.band.2=pageFooter

# keep the first column header on export to XLS
net.sf.jasperreports.export.xls.exclude.origin.keep.first.band.1=columnHeader

Can anybody please tell me how do I keep last single footer for the complete exported

report in CSV/XLS.

 

Thanks in advance.

shaily09's picture
Joined: Nov 25 2009 - 2:34am
Last seen: 9 years 9 months ago
0

cdeal
Wrote:

Can you offer any more details on this solution?  A number of JR versions have been released since this post.  Is there a workaround for this subreport problem?

The workaround is to place the subreport inside a frame element (in the master report).  There's no other solution at the moment.

Regards,

Lucian

lucianc's picture
7075
Joined: Jul 17 2006 - 1:10am
Last seen: 1 week 4 days ago
0

shaily09
Wrote:

Can anybody please tell me how do I keep last single footer for the complete exported

That's not currently supported.  Wouldn't the summary band work for you?

Regards,

Lucian

PS:  Please post new threads if your questions is not directly related to the original topic.  Discussing several items in a single thread is messy.

lucianc's picture
7075
Joined: Jul 17 2006 - 1:10am
Last seen: 1 week 4 days ago
0

 I have a quite similar problem, if i succeed correctly to remove from export page titles, and column headers, i'am not able to do the same with a group band.

 

I tried this with success :

 

 

net.sf.jasperreports.export.xls.exclude.origin.band.1=pageHeader

net.sf.jasperreports.export.xls.exclude.origin.band.2=pageFooter

 

And this without success (Product is the name of the group):

 

 

net.sf.jasperreports.export.xls.exclude.origin.band.3=columnHeader

net.sf.jasperreports.export.xls.exclude.origin.band.4=groupHeader

net.sf.jasperreports.export.xls.exclude.origin.group.5=columnHeader

net.sf.jasperreports.export.xls.exclude.origin.group.6=groupHeader

net.sf.jasperreports.export.xls.exclude.origin.band.7=Product

net.sf.jasperreports.export.xls.exclude.origin.group.8=Product

 

 
 
Column headers are always exported, is there something special for groups ?
 
Thank you.

 



Post Edited by jogrey at 10/13/2010 10:03
jogrey's picture
24
Joined: Jun 28 2007 - 7:17pm
Last seen: 12 years 2 months ago
0

 I found the solution :

 

net.sf.jasperreports.export.xls.exclude.origin.band.title=title

net.sf.jasperreports.export.xls.exclude.origin.band.pageHeader=pageHeader

net.sf.jasperreports.export.xls.exclude.origin.band.columnHeader=columnHeader

net.sf.jasperreports.export.xls.exclude.origin.band.columnFooter=columnFooter

net.sf.jasperreports.export.xls.exclude.origin.band.pageFooter=pageFooter

net.sf.jasperreports.export.xls.exclude.origin.band.lastPageFooter=lastPageFooter

 

#### the solution #####

net.sf.jasperreports.export.xls.exclude.origin.keep.first.band.Product=Product

net.sf.jasperreports.export.xls.exclude.origin.keep.first.band.Product=groupHeader

net.sf.jasperreports.export.xls.exclude.origin.keep.first.group.Product=Product

 

net.sf.jasperreports.export.xls.exclude.origin.band.Product=Product

net.sf.jasperreports.export.xls.exclude.origin.band.Product=groupHeader

net.sf.jasperreports.export.xls.exclude.origin.group.Product=Product

jogrey's picture
24
Joined: Jun 28 2007 - 7:17pm
Last seen: 12 years 2 months ago
0

Hi,

I was looking a solution for this problem and this post helped me, but I was working with Servlets and I didn't know how to apply these properties. Finally I found the way.

If anyone is looking for the same solution but exporting the Excel from a Servlet you can do this to remove pagination:

Code:
JasperReport jasperReport = (JasperReport)JRLoader.loadObject(reportFile.getPath());
jasperReport.setProperty("net.sf.jasperreports.export.xls.exclude.origin.band.1", "pageHeader");    
jasperReport.setProperty("net.sf.jasperreports.export.xls.exclude.origin.band.2", "pageFooter");    
jasperReport.setProperty("net.sf.jasperreports.export.xls.exclude.origin.band.3", "columnHeader");
 
//very useful to keep only the first column header
jasperReport.setProperty("net.sf.jasperreports.export.xls.exclude.origin.keep.first.band.1", "columnHeader");
 



Post Edited by pruebas3d at 10/28/2010 14:00
pruebas3d's picture
Joined: Oct 28 2010 - 4:16am
Last seen: 8 years 10 months ago
0

hi, i have similar problem, but not work if i used this solution, because i'm using table component for my report, and column header still repeat, please help..

 

dany

asepyudiyurdany's picture
Joined: Nov 10 2012 - 7:10am
Last seen: 5 years 8 months ago
Feedback