Export from Jasper reports to Excel

[azzam othman]

Dear Valued Community Team,

We migrated from Adempiere 3.6 to iDempiere 9.0 . We used Jasper 3.7.3 for our reports in our old Adempiere ERP . After migration to iDempiere  Jasper reports are running . But, exporting to Excel is not working properly. Cells are like PDF format, and we cannot use Excel commands and functions in exported reports .

Your advice will be greatly appreciated .

Thank you all.

Azzam

[Yogan Naidoo]

Hi,

Here are some of my old notes for exporting Jasper reports to excel. Hope it helps.

To improve the export to excel from jasper,  Ex.  export only the first header,  not the headers per page.  Remove blank lines etc.

1.  Add your column headers in the column header band

2.  Add the following properties to your report.  I find it best to just add to the xml file.

<property name="net.sf.jasperreports.export.xls.exclude.origin.band.1" value="pageHeader"/>
    <property name="net.sf.jasperreports.export.xls.exclude.origin.band.2" value="pageFooter"/>
    <property name="net.sf.jasperreports.export.xls.exclude.origin.keep.first.band.1" value="columnHeader"/>
    <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.columns" value="true"/>
    <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/>
    <property name="net.sf.jasperreports.export.xls.collapse.row.span" value="true"/>
    <property name="net.sf.jasperreports.export.xls.white.page.background" value="false"/>
    <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>
    <property name="net.sf.jasperreports.page.break.no.pagination" value="apply"/>

Good resources: https://www.youtube.com/channel/UCNI54-ZwNAjSMknRCfdyRCQ/featured
https://community.jaspersoft.com/wiki/tips-exporting-excel

[azzam othman]

Hi Yogan Naidoo,

We are very thankful for your valued reply, and we apologize for the delay in replying. Our technical team solved it in this way as in the attachment .

Thank you, and have a great day.

Azzam

Why it happened?

The new Engine of JasperSoft needs some properties of exporting excel to be set, which is not the case in our reports.

Solution

In the jrxml file we need to add the missing properties of exporting excel. Here is the list of properties :

 

<property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/>

<property name="net.sf.jasperreports.export.xls.remove.empty.space.between.columns" value="true"/>

<property name="net.sf.jasperreports.export.xls.white.page.background" value="false"/>

<property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/>

<property name="net.sf.jasperreports.export.xls.ignore.graphics" value="false"/>

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

<property name="net.sf.jasperreports.exports.xls.font.size.fix.enabled" value="false"/>

<property name="net.sf.jasperreports.print.keep.full.text" value="true"/>

 

Brief explanation of each property

 

·      <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.rows" value="true"/> →  This property gets rid of one row with a very large row height at the end of the data tab.

 

·      <property name="net.sf.jasperreports.export.xls.remove.empty.space.between.columns" value="true"/> → This property gets rid of empty columns, such as caused by margins, or by poor layout.

·      <property name="net.sf.jasperreports.export.xls.white.page.background" value="false"/> → This property shows the Excel grid lines, otherwise you get solid white.

 

·      <property name="net.sf.jasperreports.export.xls.detect.cell.type" value="true"/> → This property tells Excel to treat dates as dates, numbers as numbers, instead of all being treated as text.

 

·      <property name="net.sf.jasperreports.export.xls.ignore.graphics" value="false"/> → This propery tells Excel to not delete the company logo graphic

 

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

 

·      <property name="net.sf.jasperreports.print.keep.full.text" value="true"/> → This property keeps JS from truncating values wider than the explicitly set column widths.

 

·      <property name="net.sf.jasperreports.exports.xls.font.size.fix.enabled" value="false"/> → This property keeps JS from reducing the font size by 1 when exporting to Excel.