Complex Excel formula

[sluppo_it]

Hi All,

I need to generate an XLS report wich contains a formula in cell.. but I'm having troubles with ", / and & .. formula is  something like this

HYPERLINK(CONCATENATE("http://somedomain.com/script/blahblah.blah?v1=MOD00&v2=_&first_gr=",$F{TRN},"&last_gr=",$F{TRN},"&time=",CONCATENATE(TEXT(HOUR(NOW()),"00"),TEXT(MINUTE(NOW()),"00"),TEXT(SECOND(NOW()),"00")),"&list=0,SNL,_,_,_,all,_,_,_,_,_,",$F{TRN},",_,_,_,_,_,P1&posRepMag=&posRepMin="),"TRACK")

Thanks

Sergio

[marianol]

I assume you already have looked at the docs and samples on how JasperReports exported works with formulas.

From what you are saying I think it may be an character escaping issue, it will be usefull to see the JRXML but blindly I'll say that you may not be using an expression, i.e.: if I want to do a A$P{param} + A5 your JRXML should look like this:

    <reportElement key="textField-4" x="0" y="219" width="280" height="23"> 
      <propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["A" + $P{param} + "-A3"]]></propertyExpression> 
    </reportElement> 

[sluppo_it]

Thank you for your answer and sorry for my terrible English...

 

Basically I want to obtain a report that contains some values, and in the last column an hyperlink built by concatenation of fixed strings and the content of one column.

 

In your opinion may be simpliest built hyperlink in a Jasper variable and so pass to xls function only his content?

Thanks a lot!

 

[zh3ntil]

add before " characters in string which you try to generate and preview the report to see if the output is OK.

[sluppo_it]

I already tried, but the results is Null instead of an hyperling.

I'm thinking to simplify formula to reduce the number of quotes and make debug more simple. Tomorrow I can make the test and I'll update forum about result.

Thanks! 

[zh3ntil]

Actually ı tried it first and see it is working. Are you sure output is null when add the before " chars?

"=HYPERLINK(CONCATENATE("http://somedomain.com/script/blahblah.blah?v1=MOD00&v2=_&first_gr=",$F{TRN},"&last_gr=",$F{TRN},"&time=",CONCATENATE(TEXT(HOUR(NOW()),"00"),TEXT(MINUTE(NOW()),"00"),TEXT(SECOND(NOW()),"00")),"&list=0,SNL,_,_,_,all,_,_,_,_,_,",$F{TRN},",_,_,_,_,_,P1&posRepMag=&posRepMin="),"TRACK")"[/code]

 

[sluppo_it]

There is something that I dont't urdestand..

Using sample db, I made the following simple report, but on HYPERLINK columns, exporting to xls, i have "null" in place of an hyperlink

 

<?xml version="1.0" encoding="UTF-8"?>

<!-- Created with Jaspersoft Studio version 6.1.1.final using JasperReports Library version 6.1.1  -->

<!-- 2015-09-21T23:09:56 -->

<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="Blank_A4_Landscape" pageWidth="842" pageHeight="595" orientation="Landscape" columnWidth="802" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="03222832-d2dc-430f-9fec-49214f004fa8">

<property name="com.jaspersoft.studio.data.sql.tables" value=""/>

<property name="net.sf.jasperreports.export.xls.detect.cell.type" value="TRUE"/>

<queryString>

<![CDATA[select * from address]]>

</queryString>

<field name="ID" class="java.lang.Integer"/>

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

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

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

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

<variable name="TEST" class="java.lang.String">

<variableExpression><![CDATA[$F{FIRSTNAME}]]></variableExpression>

</variable>

<background>

<band splitType="Stretch"/>

</background>

<columnHeader>

<band height="30" splitType="Stretch">

<staticText>

<reportElement x="0" y="0" width="137" height="30" uuid="64da8aa2-f052-4917-bb21-246c01a8bc9a"/>

<text><![CDATA[iD]]></text>

</staticText>

<staticText>

<reportElement x="137" y="0" width="133" height="30" uuid="da044e3e-c75d-4401-bfd3-cac251db747e"/>

<text><![CDATA[FIRSTNAME]]></text>

</staticText>

<staticText>

<reportElement x="270" y="0" width="133" height="30" uuid="44cbdb55-ca3c-4976-b647-e5494e907f98"/>

<text><![CDATA[LASTNAME]]></text>

</staticText>

<staticText>

<reportElement x="403" y="0" width="133" height="30" uuid="8c9cf871-3013-4f3d-8704-11ceac838412"/>

<text><![CDATA[sTREET]]></text>

</staticText>

<staticText>

<reportElement x="536" y="0" width="133" height="30" uuid="f1264b75-bee7-4ffd-8214-fdbfa94e68e9"/>

<text><![CDATA[CITY]]></text>

</staticText>

</band>

</columnHeader>

<detail>

<band height="30" splitType="Stretch">

<textField>

<reportElement x="0" y="0" width="137" height="30" uuid="c1ac2883-35b8-4461-b4a9-f58cc472fa52"/>

<textFieldExpression><![CDATA[$F{ID}]]></textFieldExpression>

</textField>

<textField>

<reportElement x="137" y="0" width="133" height="30" uuid="3bf1b00b-80c8-499f-a2ab-a4f15a800d68"/>

<textFieldExpression><![CDATA[$F{FIRSTNAME}]]></textFieldExpression>

</textField>

<textField>

<reportElement x="270" y="0" width="133" height="30" uuid="18507a8d-f701-4e64-bfae-c5a6957a96b7"/>

<textFieldExpression><![CDATA[$F{LASTNAME}]]></textFieldExpression>

</textField>

<textField>

<reportElement x="403" y="0" width="133" height="30" uuid="f3c7ec42-6027-4f64-a820-2ab42f1f8296"/>

<textFieldExpression><![CDATA[$F{STREET}]]></textFieldExpression>

</textField>

<textField>

<reportElement x="536" y="0" width="133" height="30" uuid="98a02c06-f0f2-48b0-89ea-64233b06e862"/>

<textFieldExpression><![CDATA[$F{CITY}]]></textFieldExpression>

</textField>

<textField>

<reportElement x="670" y="0" width="133" height="30" uuid="978aa876-9f4b-4a25-a31d-c3d416dbe4c8">

<propertyExpression name="net.sf.jasperreports.export.xls.formula"><![CDATA["HYPERLINK("SERIO72.ALTERVISTA.ORG";"test")"]]></propertyExpression>

</reportElement>

</textField>

</band>

</detail>

</jasperReport>

[sluppo_it]

No more ideas?

Tnx