Dynamic Sheet Names For XLS Export

[rlfisherpbtgroup.com]

Hello -

I am struggling to get names dynamically applied to my worksheets.  I have been successful in getting the multiple worksheets, but the labels are just Page 1, Page 2, etc.  I have read many posts on this, and it appears that I am doing everything correclty, but it still is not working.

I have set property name="net.sf.jasperreports.export.xls.one.page.per.sheet" value="true">/property>

I have set propertyexpression name="net.sf.jasperreports.export.xls.sheet.name">!--[CDATA[$F{course_name}]]-->/propertyexpression> in a variety of elements, including to the subreport that actually generates the spreadsheet, but I am only getting "Page n" for the labels.

(omitted opening angle brackets for this post)

I am probably missing something obvious here - can somebody please give me a boost?

Thank you

[szaharia]

Hi,

 

It might be a matter of JR version here.

The net.sf.jasperreports.export.xls.sheet.name property is available for Excel 2003 (XLS) output starting with JR 4.1.3

For Excel 2007/2010 (XLSX) output, the property was enabled in JR 4.6.0

 

To identify other possible causes of the issue, please post a test sample that reproduce the problem.

 

Thank you,

sanda

[rlfisherpbtgroup.com]

Thank you, Sanda. I should have mentioned that I have tried this with JR-L 4.5.0 from both iReport and JR-S to XLS 2003 export. I have uploaded a saple that illustrates the issue - I get the multiple worksheet tabs, but the custom tab labels are ignored.

[szaharia]

Try to place a hidden line element with the sheet name property in the group header:

<line>

<reportElement x="0" y="-1" width="1" height="1">

<propertyExpression name="net.sf.jasperreports.export.xls.sheet.name"><![CDATA[$F{course_name}]]></propertyExpression>

</reportElement>

</line>

 

 

 

 

 

[rlfisherpbtgroup.com]

Thank you again for your helpful replies. I had early-on tried the line element idea (from a post I found somewhere) and it didn't work. I tried it again now, with a simplified example report, and the tab names appeared.

 

What I have determined is that the property appears to be ignored when the group contains a subreport (which is the whole notion desired around separate worksheets).

[szaharia]

Subreport elements are "consumed" during the report filling, so they cannot be used to preserve export-time properties. Elements that can be properly used for this purpose are: line, rectangle, ellipse, static text, textfield, image, frame and generic element. Don't try this with subreports, breaks or crosstabs.

For instance, another good solution is to place the subreport in a frame and set the sheet name property on the frame element.

Kind regards,

sanda

[rlfisherpbtgroup.com]

I mean that if my main report group header has line (with propertyexpression), textfield, break then the worksheet name works, but if the group header has line (with propertyexpression), subreport, break then I only get Page 1, Page 2, Page 3. Thank you for your patient help.

[szaharia]

Hi,

While testing I couldn't reproduce this behavior:

"but if the group header has line (with propertyexpression), subreport, break then I only get Page 1, Page 2, Page 3"

I tested it on a local sample (very similar to the attached sample.jrxml file) that also comes with a combination of isIgnorePagination="true", subreport and break element, and problem just got solved by adding a hidden line element, as previously shown. I also used JR-4.5.0 for testing

On the other hand, you mentioned that when you used a simplified sample, it worked.

In this case, could you post your initial sample here, the one that didn't work? Maybe we could find out the root cause of the issue.

Thanks,

sanda

[rlfisherpbtgroup.com]

Sanda, thanks again for your patient help. I have updated the file attachments - which have no dependencies on our data. In sample.jrxml, if I comment out the subreport reference, the tabs are labeled correctly. When the subreport reference is present, I still get Page 1, Page 2, Page 3.

[szaharia]

Well, didn't test it yet, but at a glance I noticed that both line and subreport are positioned at x=0 and y=0 in the new sample.jrxml. That means they are overlapping elements and could lead to unexpected results when exported to a grid. Just try to set y=-1 for the line element, and see if the problem got solved.

I hope this helps,

sanda

[rlfisherpbtgroup.com]

JACKPOT! Since we make a lot of effort to get our grids to line up, I am a little embarrassed that I didn't earlier notice that. Your solution works, but adds an extra (hidden) row 1 in the spreadsheet. We can probably live with that. Thank you so much for your patient help to get this resolved.

[joseph.madhu]

Try to place a hidden line or Text element with the sheet name property in the group header: 

<line> 
<reportElement x="0" y="-1" width="1" height="1"> 
<propertyExpression name="net.sf.jasperreports.export.xls.sheet.name"><![CDATA[$F{course_name}]]></propertyExpression> 
</reportElement> 
</line>