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
18 Answers:
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> |
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
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
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.
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
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.
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
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.
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
Post Edited by jogrey at 10/13/2010 10:03
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
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
This works for xls & xlsx , but what will the fix for CSV be ?