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.
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.
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
SADAKAR.P
Software Engineer in BI – Jaspersoft, Pentaho & Talend.
Helical IT Solutions Pvt. Ltd.
Hyderabad, INDIA.
Best Open Source Business Intelligence Software Helical Insight is Here
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