Excel report with a sheet per group

[macivic]

Hi all:

I need to export a report into excel format generating a sheet per group, but I don't want to split the group in various sheets.

If I set IS_ONE_PAGE_PER_SHEET property to false, the exporter ignore "start on a new page" group property and generate the entire report in a unique sheet.

If I set IS_ONE_PAGE_PER_SHEET property to true, the group splitted in various sheets.

Any suggestions?

Thanks in advance.

Miguel

[lucianc]

You can have explicit Excel sheet breaks by using the net.sf.jasperreports.export.xls.break.before.row and net.sf.jasperreports.export.xls.break.after.row properties on report elements.

HTH,

Lucian

[macivic]

Thank you Lucian,

Those properties works fine. The only restriction is they only can be use with JRPrintElement, and Group isn't an instance of this class.

I resolved it adding a line at group footer with line width 0 (invisible). I set the net.sf.jasperreports.export.xls.break.after.row property to true in the line and the report break in each group.

I don't know if it was the best solution or you have another suggestion.

Miguel

[lucianc]

I don't have any better suggestions, the properties can only be set for report elements.

Regards,

Lucian

[toomtooms]

Thank you for your post. I was able to generate a sheet per group using net.sf.jasperreports.export.xls.break.before.row.

However, I have another little issue : I don't know how to set the sheets' names.

I have 3 reports (3 jrxml) which generate an unique Excel file with 3 sheets. I tried setting the sheets' names with net.sf.jasperreports.export.xls.sheet.names.sheet1, net.sf.jasperreports.export.xls.sheet.names.sheet2 and net.sf.jasperreports.export.xls.sheet.names.sheet3 properties. The name of the first sheet is OK but the sheets 2 and 3 are named with the name of the first sheet following by 2 and 3.

For example, if the names are "toto", "titi" and "tutu", the sheets' names will be "toto", "toto 2" and "toto 3".

 

Are you able to define the sheets' names correctly ?

[Teodor Danciu]

Hi,

 

How did you export the 3 reports to the same XLS file? Using batch export (JASPER_PRINT_LIST export parameter)?

What exporter class did you use? The JRXlsExporter or the JExcelApiExporter?

Where did you set the 3 sheet name config properties? In a jasperreports.properties file or in the first report template?

 

Thank you,
Teodor

 

[toomtooms]

How did you export the 3 reports to the same XLS file? Using batch export (JASPER_PRINT_LIST export parameter)?

I use Java API :

        JasperPrint jasperPrintFeuilleTexte = JasperFillManager.fillReport(DirectoryLocator.getIntradocDir() + "custom/CustomizedContribution/resources/reports/stats-texte.jasper", paramsTexte, new JREmptyDataSource());        JasperPrint jasperPrintFeuilleStatsPDV = JasperFillManager.fillReport(DirectoryLocator.getIntradocDir() + "custom/CustomizedContribution/resources/reports/stats-pdv.jasper", new HashMap(), statsPdvDataSource);        JasperPrint jasperPrintFeuilleStatsCentrale = JasperFillManager.fillReport(DirectoryLocator.getIntradocDir() + "custom/CustomizedContribution/resources/reports/stats-centrale.jasper", new HashMap(), statsCentraleDataSource);        List pagesStatsPDV = new ArrayList(jasperPrintFeuilleStatsPDV.getPages());        for(int count = 0; count < pagesStatsPDV.size(); count++)        {            jasperPrintFeuilleTexte.addPage((JRPrintPage) pagesStatsPDV.get(count));        }        List pagesStatsCentrale = new ArrayList(jasperPrintFeuilleStatsCentrale.getPages());        for(int count = 0; count < pagesStatsCentrale.size(); count++)        {            jasperPrintFeuilleTexte.addPage((JRPrintPage)pagesStatsCentrale.get(count));        } 

What exporter class did you use? The JRXlsExporter or the JExcelApiExporter?

JRXlsExporter

Where did you set the 3 sheet name config properties? In a jasperreports.properties file or in the first report template?

 I tried in different locations :

- in the first report template

- in each report template

- in the JRXlsExporter parameters : exporterXLS.setParameter(JRXlsExporterParameter.SHEET_NAMES, new String[]{"toto", "titi", "tutu"});

I have the same results with all these locations.

 

Thank you

Thomas

[Teodor Danciu]

Hi,

 

What version of JasperReports are you using when trying to set the sheet names.

 

Thanks,

Teodor

 

[toomtooms]

I am using JasperReports 3.6.1.

 

Thomas

[Teodor Danciu]

Hi,

 

I was not able to reproduce this problem.

Download the JR 3.6.1 project distribution from Sourceforge.net and copy the attached file in the /demo/samples/batchexport sample.

Then run it with ant like this:

>ant clean javac compile fill xls

 

You'll notice the first three sheet names coming nicely. They are set using the SHEET_NAMES exporter parameter. But setting them with custom properties in the first of the reports (the other two do not matter anyway), works as well, provided that you no longer set the SHEET_NAMES, which would take precendence.

 

I hope this helps.

Teodor

 

Post Edited by teodord at 01/21/2010 11:13

[stopbugginme]

lucianc
Wrote:

You can have explicit Excel sheet breaks by using the net.sf.jasperreports.export.xls.break.before.row and net.sf.jasperreports.export.xls.break.after.row properties on report elements. HTH, Lucian

 

 

Hi.

could anybody paste the code to use this properties. I don't know how i should use them.

Thank's a lot

[zados]

stopbugginme
Wrote:

lucianc Wrote: You can have explicit Excel sheet breaks by using the net.sf.jasperreports.export.xls.break.before.row and net.sf.jasperreports.export.xls.break.after.row properties on report elements. HTH, Lucian     Hi. could anybody paste the code to use this properties. I don't know how i should use them. Thank's a lot

Hi everyone,

I tried to enable these two properties, doing this but it does not work:

               

Can someone help me please, i need it urgently.

thanks a lot.

Julien.

Code:

JRXlsExporter exporterXLS = new JRXlsExporter();                 exporterXLS.setParameter(JRXlsExporterParameter.is_break_before_row, Boolean.TRUE);         exporterXLS.exportReport();

[zados]

I am still wondering if there is someone who can help me find the answer to my questions.

Great thanks...

 

[zados]

is this forum still working /:

[lucianc]

The forum is working, but not necessarily in an "urgent" mode.

The properties I mentioned need to be set at element level, as documented.  So you'd have something like below in your report.

Regards,

Lucian

Code:

<textField>	<reportElement x="0" y="0" ...>		<property name="net.sf.jasperreports.export.xls.break.before.row" value="true"/>	</reportElement>

[taiko]

I tried a lot of ways. I checked "ignore pagination", then set "break.after.row" to true to some element.

 

It worked in ireport. But when i open the report in jasperserver, only one sheet is generated.

 

Could you please tell me why? I am using Jasperserver 3.7.1, ireport 4.0.0.

 

Do I have to upgrade jasperserver to 4.0?

 

Thanks

[ernestoo]

 Export time settings on iReport are not magically set in JasperServer - you'll have to find /WEB-INF/classes/jasperreports.properties and set them there.

[taiko]

It works!! I just add

net.sf.jasperreports.export.xls.break.after.row=true

in jasperreports.properties

 

Thanks a lot !

[ansulgupta]

1. Add this net.sf.jasperreports.export.xls.one.page.per.sheet and its value:true to the report's properties.

2. Add Group Band and give Group Expression in Group Header or in Group Footer.

3. Add a Page Brake in Page footer or Group footer or after the crosstab data or data.

4. Check on ignore pagination.

Result: You will find the Excel report with a sheet per group.

 

[cjasper]

thanks

[wolverto]

I have done everything mentioned within this thread and still have not been able to produce the desired results. Using Ireport 5.0

[manogna]

Did you have any luck in this,I am using 6.2.0 and have the same issue.

[johannes.held]

I checked “Start New Page” for my group header (and group footer). Therefore I got rid of the the page break.

I UNchecked the “Ignore Pagination” for the report. But mind, that the page height must be tall enough to hold the content for every sheet or you get “funny” sheets in between.

[prasannaj_1]

We need to below property to the line which we added in the table footer

net.sf.jasperreports.export.xls.sheet.name -- Use an expression to select the field name to you want to keep

 

 

[prasannaj_1]

After adding line in the footer section, go to the line advanced properties and add 

net.sf.jasperreports.export.xls.sheet.name - Use an expression