Export Timestamp to Excel xls

[Layla]

Using Jaspersoft Studio CE 6.3.1 with JasperReport Server CE 6.3.0

Field in Jaspersoft Studio is java.sql.Timestamp have tried with java.util.Date also.

Export to Excel (xls) and the field is converted to a text field.  We need to maintain the field as a DATETIME or TIMESTAMP.

Have set the property net.sf.jasperreports.export.xls.detect.cell.type = true in both the report and at the element level.

Any advise would be much appreciated.

Thank you

 

[hozawa]

First, it's better to find if the problem is with your report or with JasperReports Server setting.

Try exporting your report from Jaspersoft Studio. I'm using Jaspersoft Studio 6.3.0 and it's exporting timestamp as a date to xlsx. If this works, it's JasperReports Server setting.

Go to /webapps/jasperserver/WEB-INF/classes folder. There should be jasperreports.properties file. Set you jasperreports properties there and restart JasperReports Server and retry.

 

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

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

<!-- 2017-03-04T09:08:17 -->

<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="pdffont" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="20" rightMargin="20" topMargin="20" bottomMargin="20" uuid="8a331d21-2608-405b-a765-e33dc06ccc20">

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

<property name="com.jaspersoft.studio.data.defaultdataadapter" value="TestDataAdapter"/>

<queryString>

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

</queryString>

<field name="column2" class="java.sql.Timestamp"/>

<background>

<band splitType="Stretch"/>

</background>

<columnHeader>

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

</columnHeader>

<detail>

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

<property name="com.jaspersoft.studio.unit.height" value="pixel"/>

<textField pattern="MM/dd/yyyy">

<reportElement x="170" y="0" width="100" height="30" uuid="4cf11729-11f2-4dc8-8a8c-1e41a00dbcb6"/>

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

</textField>

</band>

</detail>

<pageFooter>

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

</pageFooter>

</jasperReport>

[Layla]

Thank you for your reply,

 

I was able to get to work by:

1) Including this property in the report

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

 

2) Adding this property to the textField and specifying the pattern needed

<property name="net.sf.jasperreports.export.xls.pattern" value="m/d/yyyy h:mm AM/PM"/>