Complex Excel formula

0

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

sluppo_it's picture
Joined: Sep 18 2015 - 3:57am
Last seen: 2 months 2 days ago

7 Answers:

0

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
5378
Joined: Sep 13 2011 - 8:04am
Last seen: 1 year 12 months ago
0
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
Joined: Sep 18 2015 - 3:57am
Last seen: 2 months 2 days ago
0

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

zh3ntil's picture
539
Joined: Nov 19 2014 - 11:02pm
Last seen: 4 years 2 months ago
0
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
Joined: Sep 18 2015 - 3:57am
Last seen: 2 months 2 days ago
0

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
539
Joined: Nov 19 2014 - 11:02pm
Last seen: 4 years 2 months ago
0

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
Joined: Sep 18 2015 - 3:57am
Last seen: 2 months 2 days ago
0

No more ideas?

Tnx

sluppo_it's picture
Joined: Sep 18 2015 - 3:57am
Last seen: 2 months 2 days ago
Feedback
randomness