Split Excel output into multiple sheets

Hi Jasperians ...!!!!

A friend posted an intersting thing Over LinkedIn that how to split Excel output into multiple sheets... !!! Just worked out and sharing it with you guys..!!!

It's a sample one.. No parameters, No headers are used... There might be getting issues when we use all the stuff..If we work out we can also overcome them.. Though I just want to share how we can split excel output into multiple sheets..!!

Greetings..!!! Here are the stpes to follow...!!!

Aim: To split the output into multiple sheets in Excel exporting
Solution Way: Printing data in one sheet & a bar chart in another sheet.

I have used the follwoing.
i) iReport Designer : 5.0.4 (Community)
ii) PostgreSQL : 9.2 (foodmart database)

Query I have used:
select  * from employee limit 15

Step 1: 
Open iReport desinger and save it to your fav location.

Step 2:
Write the query in query designer area

Step 3:
Remove all the bands from design area except
Column header, Detail & summury band.

Step 4:
Drag and drop your fav columns to detail band as shown in below figure
Drag and drop a bar chart to the summary band as shown in below figure

Step 5 :
We need to set properties at two places.

i) In the report properties
ii) In settings

i) In the report properties
Rport Inspector->ReportName(report1)->Right Click->Properties
Now add the propertey.
net.sf.jasperreports.export.xls.one.page.per.sheet
and value is set to be "true" do not use double quoutes for giving value but in xml code it automatically goes into double quotes.
Find the below image.

ii) In the report Settings
Tools->Options-> Click on export Settings -> In the list of export options click on Excel
It opens Excel Export Parameters
In common tab ---> Check One page per sheet.
Find the image below

NOTE:
Do not forget to place page break on the place from where you want to split the page.

In this example I used page break above the chart so that data comes in one sheet and chart comes in another sheet.

That's it... Save the report and see the preview.
I'm not seeing the preview in Server but previwing internally in the iReport.


OUTPUT
The output would be something like as follows. Find the images of excel sheet.

OUTPUT in first sheet:

OUTPUT in second sheet:

Thanks for posting a good idea to work out on LinkedIn.

Thanks for reading this document.

Simlar posts on various sites:

1) http://stackoverflow.com/questions/3977658/how-do-you-export-a-jasperreport-to-an-excel-file-with-multiple-worksheets

2) http://community.jaspersoft.com/questions/531936/multiple-sheets-excel

3) http://jasperreports.sourceforge.net/config.reference.html

4) http://jasperreports.sourceforge.net/config.reference.html#net.sf.jasperreports.export.xls.one.page.per.sheet

5)http://community.jaspersoft.com/questions/533232/how-create-multi-worksheet-excel-jasper-repor

6) http://community.jaspersoft.com/questions/540310/unable-split-excel-output-multiple-sheets

7) http://stackoverflow.com/questions/8753401/exporting-a-report-with-multiple-sheets-from-jasperserver-to-excel


SADAKAR.P
Software Engineer in BI - Jaspersoft, Pentaho & Talend.
Hyderabad, INDIA.