Problem with dynamic excel formula

[gal64]

Hi, all,

I have a problem with dynamic excel formula. Because my reports are highly dynamically built, I don't
use XML to design the report, but do it programmatically. For all columns with "normal" field values it's
ok, but I'm running into problems with Excel formulas (which are also dynamical).

I do the following: create a JRDesignTextField and add it to a detail band. To the textField I add a
property expression like that:


final JRPropertyExpression expression = new JRPropertyExpression() {

@Override
public void setName(String name) {
// TODO
}

@Override
public JRExpression getValueExpression() {
final JRDesignExpression expr = new JRDesignExpression();
expr.setValueClass(java.lang.String.class);
expr.setText("\"![CDATA[" + someVariable + "]]\"");
return expr;
}

@Override
public String getName() {
// TODO
return JRAbstractExporter.PROPERTY_CELL_FORMULA;
}
};

where someVariable contains "teil(C1; 1; 3)";

This causes the following error:

Expression ID not found for expression <<"![CDATA[teil(C1; 1; 3)]]">>.
at net.sf.jasperreports.engine.base.JRBaseObjectFactory.getCollectedExpressionId(JRBaseObjectFactory.java:470)

If I change the line expr.setText("\"![CDATA[" + someVariable + "]]\"");
to
expr.setText("![CDATA[" + someVariable + "]]");

I get

illegal start of expression
value = (java.lang.String)(![CDATA["teil(C1; 1; 3)"]]); //$JR_EXPR_ID=12$

Without the "CDATA" part I get the same errors when using "\"" or not.
By debugging I found out, that some expressions are stored as a key in a Map and the value is an ID. But my JRPropertyExpression is not found in
this map, which causes the "ID not found" error. Has anyone an idea how to solve this problem?

I'm using JasperReports 3.6.0.

Thank you very much, Gerhard
 

[Giulio Toffoli]

 

1. Use JRDesignPropertyExpression

2. The expression text is a java expression. What you would like to achieve with:

("\"![CDATA[" + someVariable + "]]\"");

The correct expression is:

expr.setText("$V{someVariable}");
 

or (if someVariable is your stuff)  you must create a valid java expression.

Now, can you read this post? It's very important:

Thanks a lot

Giulio

 

[gal64]

Hello,

 

thanks for your reply. I changed to JRDesignPropertyExpression

as you told me. I set my textField like this:

 

 

 

final JRDesignPropertyExpression propertyExpression = new JRDesignPropertyExpression();

final JRDesignExpression expr = new JRDesignExpression();

expr.setValueClass(java.lang.String.class);

expr.setText("\"" + myExpression + "\"");

propertyExpression.setName(JRAbstractExporter.PROPERTY_CELL_FORMULA);

propertyExpression.setValueExpression(expr);

 

final JRDesignTextField textField = new JRDesignTextField();

textField.addPropertyExpression(propertyExpression);

return textField;

 

myExpression is a variable in my own class and contains the desired excel formula, something like

"teil(C2; 1; 3)". In the Eclipse debugger I can verify that the textField has a JRPropertiesMap

with the entry "net.sf.jasperreports.export.xls.formula=teil(C2; 1; 3)". And the textField is added

to the detail band. But the cell in the generated Excel file is still empty.

What is wrong here? Is there missing some step? Can you help?

 

Thank you very much, Gerhard

[Giulio Toffoli]

Hard to say. Try to post your question on the JasperReports forum which is much more appropriate for this kind of questions.

Thanks a lot

 

Giulio