Excel Sheets dynamic renaming

[aschinch]

I am working on Excel report displaying month name for each sheet. I know we can add  jrxlsexporterparameter.sheet_names parameter from java code to change the names of the excel sheets. But I want to change the name of sheet dynamically based on the month. Also can this be done while previewing from iReport? I have checked export options under the options menu, but could not find it.

[szaharia]

A new set of report custom properties (exporter hints) was added, in order to solve this requirement. They will be available with the next JasperReports release.

These properties should start with the "net.sf.jasperreports.export.xls.sheet.names." prefix, and should end wit any arbitrary suffix. Such a property can hold one or more sheet names, separated by the slash ("/") separator. The values are collected in the same order as they appear in the jrxml template, so one have to be careful with that order when editing a jrxml. The ending suffix is not important, but the order is very.

For instance, for a document containing three sheets one can define the property below:

<property name="net.sf.jasperreports.export.xls.sheet.names.all" value="First sheet/Second sheet/Third sheet" />
or, equivalently, one can define 3 independent properties in a given order:
<property name="net.sf.jasperreports.export.xls.sheet.names.sheet1" value="First sheet" />
<property name="net.sf.jasperreports.export.xls.sheet.names.simple.sheet.name" value="Second sheet" />

<property name="net.sf.jasperreports.export.xls.sheet.names.3" value="Third sheet" />

Then, resulting sheets will be named as follows:

First sheet
Second sheet
Third sheet

If dynamic definition is needed, do use the <propertyExpression /> instead of <property />.

And a final note: if the user sets less sheet names (let's say x) than the number of sheets, the exporter will use the custom user sheet names for the first x sheets in the document, and the remaining sheets will be named by default as "Page y", where y is the current page number.

Post Edited by shertage at 05/27/2009 11:04

[aschinch]

Thanks for your suggestions. I could overcome this issue by switching over to "iReport-nb-3.5.1".

This newer version of iReport has option called "Sheet Names" within export options for excel.

The names will be assigned to the sheets.

 

[fbarajas]

Can you give me a more info on how to use the net.sf.jasperreports.export.xls.sheet.names.all with the <propertyExpression /> tag?

 

I can't seem to figure it out.

 

thanks!

[aschinch]

 

 

 

Frankly, I have not used propertyExpression tag.

1) I have used following code to rename the sheets.

String[] sheetNames ={"Sheet1","Sheet2","Sheet3"};

JExcelApiExporter exporter = new JExcelApiExporter();

exporter.setParameter(JRXlsExporterParameter.

sheetNames);

 

SHEET_NAMES

 2) Another option is to use iReport for ranaming sheets. There is a property called "Sheet Names" in export options.

 

 

 

[szaharia]

Usually the <propertyExpression/> tag is very helpful when someone is using dynamic data.

For example, let's consider that a customer stored the expression "one/two/three/etc" representing sheet names for a 4-sheets document, in a database column named SheetNames. With an appropriate query, this field value can be retrieved and stored in a related jrxml field element:

<field name="SheetNames" class="java.lang.String"/>

Now, all is prepared for using the <propertyExpression/> tag:

<propertyExpression name="net.sf.jasperreports.export.xls.sheet.names.all">

  <![CDATA[$F{SheetNames}]]>

</propertyExpression>

Hope this helps.

sanda

[fbarajas]

Thanks Sanda for the quick reply.

I tried your suggestion above but its rejecting the propertyExpression. Here's the error message:

org.xml.sax.SAXParseException: cvc-complex-type.2.4.a: Invalid content was found starting with element 'propertyExpression'. One of '{"http://jasperreports.sourceforge.net/jasperreports":field, "http://jasperreports.sourceforge.net/jasperreports":sortField, "http://jasperreports.sourceforge.net/jasperreports":variable, "http://jasperreports.sourceforge.net/jasperreports":filterExpression, "http://jasperreports.sourceforge.net/jasperreports":group, "http://jasperreports.sourceforge.net/jasperreports":background, "http://jasperreports.sourceforge.net/jasperreports":title, "http://jasperreports.sourceforge.net/jasperreports":pageHeader, "http://jasperreports.sourceforge.net/jasperreports":columnHeader, "http://jasperreports.sourceforge.net/jasperreports":detail, "http://jasperreports.sourceforge.net/jasperreports":columnFooter, "http://jasperreports.sourceforge.net/jasperreports":pageFooter, "http://jasperreports.sourceforge.net/jasperreports":lastPageFooter, "http://jasperreports.sourceforge.net/jasperreports":summary, "http://jasperreports.sourceforge.net/jasperreports":noData}' is expected.

 

this is how my xml is structured:

<jasperReport>
  <propertyExpression
 

 

[szaharia]

Well, there is a problem here, indeed. That's because report properties are defined before any other jasperReport child element (parameter, field, variable, etc), and there is no related expression to be evaluated here. That's why a <propertyExpression /> tag cannot be found at this position in the document.

The only possibility at the moment is to use the SHEET_NAMES export parameter.

Thank you for your observation.

sanda

 

[tmochan]

Tried using this in iReport 3.5.2 and it seems to have no effect.

 

Can you confirm that it is a setable property in 3.5.2?

 

Thanks.

 

Tony

[szaharia]

The sheet names property is available for JasperReports 3.5.2 and it should be setable in iReport 3.5.2.

[shivasakthi18]

 

 

Post Edited by shivasakthi18 at 08/11/2011 09:54

[shivasakthi18]

Hi Sanda,

It seems great to hear now its possible to name the excel sheet dynamically, Could you please drop a example JRXML for the same which will help 100's of people who is looking for this requirement....!

Thanks,

sivasakthi.

Post Edited by shivasakthi18 at 08/11/2011 09:55

[szaharia]

Hi,

Properties defined at report or global level cannot be set dynamically (there is no available <property expression/> tag for them). This is the case of the sheet names too. For sheet names one can use the SHEET_NAMES export parameter to set these values dynamically.

Regards,

sanda

[shivasakthi18]

Hi,

It great to see you reply, Is that possible to write a scriptlet code to give sheet name dynamically passing to array... I hope its possible.. But some one hands on java can try it out and give me that solution.

Thanks,

Sivsakthi.

[szaharia]

Hi,

The problem is that a scriptlet code is called at fill time and it only affects the JasperPrint object itself, and not the JRXlsExporter. So, i think the SHEET_NAMES export parameter cannot be set from within a scriptlet.

Unlike the SHEET_NAMES parameter, the net.sf.jasperreports.export.xls.sheet.names... properties are all available at fill time, but again I'm not sure that the report properties map could be accessed from within a scriptlet. Please share with us if you succeeded.

Regards,

sanda

[gsalas]

  I'm glad to see I'm not the only one looking for a solution to this.

is there a plan to add expressions to report level properties in future releases of iReport?

[szaharia]

Hi,

Thank you for your interest. Please log a RFE on the tracker site:

http://jasperforge.org/projects/jasperreports/tracker

Regards,

sanda

 

[sainiakash01]

 hi 

I have been using ireport 4.1.3

Here is an example to demonstrate the use of EXPRESSIONS for linking (xls.sheet.name) property present at element level i.e. for elements present in detail, page header band etc.

1. Create a Field.

2. Map it to some dynamic value coming from datasource

Create a Textfield .

Add to it a expression in its properties  and link it to the above created Field.

Then add a properties to Textfield i.e net.sf.jasperreports.export.xls.sheet.name

Check box of use expression

Use the expression (such as Field you have created in Step 1.)

Now you can save it. and preview.

[tpoingt]

Hello sainiakash01,

Your solution is almost good, it renames dynamically the sheets, but I have one page shift. The name of my first sheet is on my second sheet, and the second on my third on so on...

It seems the name of the current sheet is evaluated before the evaluation of the detail band, so that's why i have a shift.

I don't know how to prevent it.

Thanks

[kmkoch]

 I am struggling with this same issue -- dynamic sheet naming is working well using the net.sf.jasperreports.export.xls.sheet.name property, setting its expression value to include a Field reference -- but I also get a one-page shift on the Field value that seems to be related to evaluation time.

My second sheet gets the sheet name that should be assigned to the first sheet; my third sheet gets the sheet name that should be assigned to the second sheet; and so on.  My first sheet gets the sheet name that should be assigned to the last sheet.

I am using version 4.6.0 of iReport/JasperReports.

Any insight or help would be greatly appreciated!

[sameerghaie]

Hi,

 

I need to know if we can rename the sheets in excel using iReport 3.0.

I have a requirement wherein we need to rename the sheets to some meaningful names.

Only iReport3.0 version is supported on the platform i am working on.

Kindly help.

 

Thanks

Sameer

 

Post Edited by sameerghaie at 08/22/2012 09:18

[cjasper]

Hi Sanda.This solution is working very fine for .xls files but it is not working when i am exporting it to .xlsx file.Can you please suggest something.

Thanks

chandra