Complex Excel formula

Posted on September 18, 2015 at 4:38am

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

Jaspersoft® Studio
sluppo_it's picture
sluppo_it
14
Joined: Sep 18 2015 - 3:57am
Last seen: 1 year 5 months ago

7 Answers:

Posted on September 18, 2015 at 8:57am

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> 

marianol's picture
marianol
15596
Joined: Sep 13 2011 - 8:04am
Last seen: 4 years 5 months ago
Posted on September 18, 2015 at 2:29pm
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!

 

sluppo_it's picture
sluppo_it
14
Joined: Sep 18 2015 - 3:57am
Last seen: 1 year 5 months ago
Posted on September 18, 2015 at 10:19pm

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

zh3ntil's picture
zh3ntil
3207
Joined: Nov 19 2014 - 11:02pm
Last seen: 6 years 8 months ago
Posted on September 20, 2015 at 1:10pm
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! 

sluppo_it's picture
sluppo_it
14
Joined: Sep 18 2015 - 3:57am
Last seen: 1 year 5 months ago
Posted on September 20, 2015 at 2:08pm

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\")"

zh3ntil's picture
zh3ntil
3207
Joined: Nov 19 2014 - 11:02pm
Last seen: 6 years 8 months ago
Posted on September 21, 2015 at 2:13pm

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's picture
sluppo_it
14
Joined: Sep 18 2015 - 3:57am
Last seen: 1 year 5 months ago
Posted on September 23, 2015 at 1:08pm

No more ideas?

Tnx

sluppo_it's picture
sluppo_it
14
Joined: Sep 18 2015 - 3:57am
Last seen: 1 year 5 months ago
Feedback