Thursday 10 October 2013

Export report output to multiple sheets of excel- Page breaking in jasper iReport or Studio

Hello Community,

NOTE : Find the update section : Updated On  14th April 2015 : Example JRXML

You can break the page where ever you want and export to excel in jasper server. 
For this you need to give  the properties  shown in image to jasper iReport  report.

Right  click on report Name from Reporot Insepctor > Properties -> report properties

net.sf.jasperreports.export.xls.one.page.per.sheet true
net.sf.jasperreports.export.xls.sheet.names.1 Pivot Remit
net.sf.jasperreports.export.xls.sheet.names.3 Detail
net.sf.jasperreports.export.xls.sheet.names.2 Summary
net.sf.jasperreports.page.break.no.pagination apply

 






As shown in the above picture use the page breaks where ever you want to break the report to a page when exporting to excel.


Update 14th April 2015 : Example JRXML
Click Me to Download JRXML Example

Click Me to Download the Sample Excel Output File of the Above JRXML

Quick Screenshot :


NOTE : The above example JRXML runs on PostgreSQL foodmart database.

Server file changes : 

add below line of property in jasperreports.properties file
net.sf.jasperreports.export.xls.one.page.per.sheet=true

Location of the file :
C:\Jaspersoft\jasperreports-server-6.0\apache-tomcat\webapps\jasperserver-pro\WEB-INF\classes

Quick Steps :
1) Keep the elements in User Defined Groups (bands)
2) Take Data sets to be used by elements kept on User Defined groups (bands)
3) Place the break element either at the end of elements.
4) Set Ignore Pagination= true for the report (If you use sub reports do the same).
5) Add net.sf.jasperreports.export.xls.one.page.per.sheet=true property in jasperreports.properties file.
6) set below shown properties for the report.

7) Save the report publish it the server and export the output to Excel.
Sample output image : 

:-)
Cheers..!!!

21 comments:

  1. i want beak the page based no row size and i am not using table component please reply me

    ReplyDelete
  2. Hi,

    I'm new in the Jasper and I followed your steps here but I'm not getting the result I want . The sheets have 'null' when exporting the report (I'm using subreports that are fully functional when running them individually).
    So could you please help ? Also , When creating the groups , do we use the 'report object ' or the 'expression' group by options . Could you please precise which one is used and what should we put exactly ?

    THanks

    ReplyDelete
  3. Hi,
    Can you download the example and explore how I configure the properties. ? (Make sure you are using the version compatible to open the JRXML- professional version is preferred).
    Grouping can be done on report object as well expressions. Its completely based on your requirement. For example, if you want to group by the report with STATE then $F{STATE} object is preferred. In case of dummy groups you can give expressions like "dummy1" .

    I hope it helps.!

    - Sadakar

    ReplyDelete
  4. Hi Sadakar,

    It helped me to take a look on your Jrxml file above . However, I was missing to map the main report parameter to the sub report one and that was the reason I have 'null' in the extract .

    Thanks a lot for your response .
    regards.

    ReplyDelete
  5. Hi ,

    As indicated in my last comment , I was able to export the report to an excel with multiple sheets using ireport . However, I have an issue doing the same thing when the report was executed on an application server . All the data are located in the same sheet . Is there any thing I missed ?
    Please find below the properties section of my main report .






    ReplyDelete
  6. Hi Syrine,
    Could you email me your sample JRXML ? I will have a look and provide you the solution.

    ReplyDelete
  7. Hi Sadakar,
    when i run my Report, i have more data wich can be stored on one page in jasperreport. i need for each group one Excel shhet. Did you have any idea?
    Thanks Christian

    ReplyDelete
  8. Group By bands or Group By in Table component or Row Group By in Cross tab component - I think we can not export grouped data to different sheets instead can split data page wise for exports.



    ReplyDelete
    Replies
    1. I'm really sorry for not answering when you need input at the hour. I might had missed notifications or disable to notify myself.

      Delete
  9. Hi Sadakar,
    i created a Report to Export to Excel. for each table i desinged a table. But when i run the Report, i get a second page for the 2nd.table and so a third excelsheet. What i want is for each table a excelsheet.
    Is their any Option i forgot?
    Thanks Christian

    ReplyDelete
    Replies
    1. I'm really sorry for not answering when you need input at the hour. I might had missed notifications or disable to notify myself.

      Delete
  10. This article is what exactly you are looking for. Instead of sub reports you can use table components in the example so your data of each table can be exported to each of the sheets in Excel.

    ReplyDelete
    Replies
    1. Hi But when i run the Report i get more then one Page for each table. Can I sent you my jrxml?

      Delete
    2. I'm really sorry for not answering when you need input at the hour. I might had missed notifications or disable to notify myself.

      Delete
  11. I can get the excel output to work properly but when I do a preview in Jaspersoft Studio the two pages are blank. Could you take a look and point me in the right direction. Jaspersoft is still very new to me.

    ReplyDelete
    Replies
    1. I'm really sorry for not answering when you need input at the hour. I might had missed notifications or disable to notify myself.

      Delete
  12. Hi, friends!
    i have a big problem... When i create new Report with two sheets (large, over 300.000 rows) i see repeated spaces (1 rows) every 20-30 rows (for other data - other value). By these empty spaces, each sheet is divided into two, three, and more sheets...
    I use "groups" for my lists (i create table), "break" between groups, and properties:







    Than i get report (xlsx) with more sheets and blank rows every 20-30 rows
    Can you help me, please?

    ReplyDelete
    Replies
    1. I'm really sorry for not answering when you need input at the hour. I might had missed notifications or disable to notify myself.

      Delete