sluppo_it Posted September 18, 2015 Share Posted September 18, 2015 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 thisHYPERLINK(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")ThanksSergio Link to comment Share on other sites More sharing options...
marianol Posted September 18, 2015 Share Posted September 18, 2015 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> Link to comment Share on other sites More sharing options...
sluppo_it Posted September 18, 2015 Author Share Posted September 18, 2015 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! Link to comment Share on other sites More sharing options...
zh3ntil Posted September 19, 2015 Share Posted September 19, 2015 add before " characters in string which you try to generate and preview the report to see if the output is OK. Link to comment Share on other sites More sharing options...
sluppo_it Posted September 20, 2015 Author Share Posted September 20, 2015 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! Link to comment Share on other sites More sharing options...
zh3ntil Posted September 20, 2015 Share Posted September 20, 2015 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] Link to comment Share on other sites More sharing options...
sluppo_it Posted September 21, 2015 Author Share Posted September 21, 2015 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> Link to comment Share on other sites More sharing options...
sluppo_it Posted September 23, 2015 Author Share Posted September 23, 2015 No more ideas?Tnx Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now