How to convert number stored as text : Using Jrxml /jasper

I use jrxml jasper to export my content to .xls file . I am having a filed named PollutantQuantity which is persisted as a string in DB.

I am fetching the same and giving those values to .jrxml . I can see the values getting populated correctly without any issue . Now my customer wants to do some SUM,MULTIPLY functions directly from the exported sheet .

In this case ,since is rendered as text , I can't do any manipultaions.

My Jrxml code snippet is like

<property name="net.sf.jasperreports.export.detect.cell.type" value="true"/>
<field name="pollutantQty" class="java.lang.String" />
<textField>    
  <reportElement x="0" y="0" width="100" height="20" isRemoveLineWhenBlank="true" />
<box leftPadding="10"><pen lineColor="#000000" /><topPen lineWidth="0.5" /><leftPen lineWidth="0.5" /><bottomPen lineWidth="0.5" /><rightPen lineWidth="0.5" />
  </box>                                    
  <textFieldExpression ><![CDATA[$F{pollutantQty}]]></textFieldExpression>
</textField>

I am using the property field and also my field emissionQty is declared as string. How can I convert that so that in output excel the emissionQty is interpreted as Number.

Solution

Define field as a Number es. java.lang.Double

<field name="pollutantQty" class="java.lang.Double" />

Use pattern in report to display it as you like

<textField pattern='###,##0.00'>    
   <reportElement x="0" y="0" width="100" height="20" isRemoveLineWhenBlank="true" />
  <box leftPadding="10"><pen lineColor="#000000" /><topPen lineWidth="0.5" /><leftPen lineWidth="0.5" /><bottomPen lineWidth="0.5" /><rightPen lineWidth="0.5" />
  </box>                                  
  <textFieldExpression ><![CDATA[$F{pollutantQty}]]></textFieldExpression>
</textField>

If you can't declare it as a Number (its a String in database), you need to convert the String to a Number es.

<textFieldExpression><![CDATA[Double.parseDouble($F{pollutantQty})]]></textFieldExpression>

In this case it can be wise to put a printWhenExpression using for example regex, to avoid errors.

<printWhenExpression><![CDATA[$F{pollutantQty}.matches("-?\\d+(\\.\\d+)?")]]></printWhenExpression>

or as if statement in textFieldExpression