Split Excel output of Jasper Report into multiple sheets

Posted on by By Nikhilesh, in Business Intelligence, Jaspersoft, Open Source Business Intelligence | 1

Hi Jasperians …!!!!

A friend posted an interesting query on LinkedIn asking 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…

Greetings..!!! Here are the steps to follow…!!!

Make data easy with Helical Insight.
Helical Insight is world’s best open source business intelligence tool.

Grab The Free Trail

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 give it to a name and save it to your fav location.

Step 2:
Write the query in query designer area

Step 3:
Remove all the bands from report 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
Report 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 quotes 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.

Make data easy with Helical Insight.
Helical Insight is world’s best open source business intelligence tool.

Click Here to Free Download

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

OUTPUT in the first sheet:


OUTPUT in the second sheet:




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

Thanks for reading this document.

Queries are welcome @    sadakar@helicaltech.com

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://community.jaspersoft.com/questions/533232/how-create-multi-worksheet-excel-jasper-repor

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

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

SADAKAR.P

Software Engineer in BI – Jaspersoft, Pentaho & Talend.

Helical IT Solutions Pvt. Ltd.

Hyderabad, INDIA.

logo

Best Open Source Business Intelligence Software Helical Insight is Here

logo

A Business Intelligence Framework

0 0 votes
Article Rating
Subscribe
Notify of
1 Comment
Oldest
Newest Most Voted
Inline Feedbacks
View all comments

I created the jar with the fonts but when I went up the apliacption on a linux server, can not generate a report. It gives the following error:12:47:07,248 ERROR [stderr] (http 0.0.0.0-8443-2) Caused by: net.sf.jasperreports.engine.JRRuntimeException: java.io.IOException: Problem reading font data.12:47:07,249 ERROR [stderr] (http 0.0.0.0-8443-2) at net.sf.jasperreports.engine.fonts.SimpleFontFace.(SimpleFontFace.java:109)12:47:07,250 ERROR [stderr] (http 0.0.0.0-8443-2) at net.sf.jasperreports.engine.fonts.SimpleFontFace.(SimpleFontFace.java:129)12:47:07,250 ERROR [stderr] (http 0.0.0.0-8443-2) at net.sf.jasperreports.engine.fonts.SimpleFontFace.getInstance(SimpleFontFace.java:68)12:47:07,251 ERROR [stderr] (http 0.0.0.0-8443-2) at net.sf.jasperreports.engine.fonts.SimpleFontFamily.setNormal(SimpleFontFamily.java:99)12:47:07,251 ERROR [stderr] (http 0.0.0.0-8443-2) at net.sf.jasperreports.engine.fonts.SimpleFontExtensionHelper.parseFontFamily(SimpleFontExtensionHelper.java:261)12:47:07,252 ERROR [stderr] (http 0.0.0.0-8443-2) at net.sf.jasperreports.engine.fonts.SimpleFontExtensionHelper.parseFontFamilies(SimpleFontExtensionHelper.java:232)12:47:07,253 ERROR [stderr] (http 0.0.0.0-8443-2) at net.sf.jasperreports.engine.fonts.SimpleFontExtensionHelper.loadFontFamilies(SimpleFontExtensionHelper.java:193)12:47:07,256 ERROR [stderr] (http 0.0.0.0-8443-2) at net.sf.jasperreports.engine.fonts.SimpleFontExtensionHelper.loadFontFamilies(SimpleFontExtensionHelper.java:162)12:47:07,256 ERROR [stderr] (http 0.0.0.0-8443-2) at net.sf.jasperreports.engine.fonts.FontExtensionsRegistry.getExtensions(FontExtensionsRegistry.java:56)12:47:07,257 ERROR [stderr] (http 0.0.0.0-8443-2) at net.sf.jasperreports.extensions.DefaultExtensionsRegistry.getExtensions(DefaultExtensionsRegistry.java:110)12:47:07,261 ERROR [stderr] (http 0.0.0.0-8443-2) at net.sf.jasperreports.engine.util.JRStyledTextParser.(JRStyledTextParser.java:83)12:47:07,261 ERROR [stderr] (http 0.0.0.0-8443-2) 36 more