net.sf.jasperreports.export.xls.pattern to export time format to Excel

[soake]

Hi all,

 

I am trying to use the net.sf.jasperreports.export.xls.pattern.{arbitrary_pattern} in Jasper studio pro 7.1 to set the time format for a cell that I export to excel.  Mt textfield code looks like this:

            <textField pattern="h:mm a">                <reportElement x="625" y="18" width="100" height="16" uuid="bcea4688-0afe-4091-880e-f52b375c7a95">                    <property name="com.jaspersoft.studio.unit.height" value="px"/>                    <property name="com.jaspersoft.studio.unit.rightIndent" value="px"/>                    <property name="com.jaspersoft.studio.unit.leftIndent" value="px"/>                </reportElement>                <textElement textAlignment="Center" verticalAlignment="Bottom">                    <font size="6"/>                    <paragraph leftIndent="2" rightIndent="2"/>                </textElement>                <textFieldExpression><![CDATA[new java.util.Date()]]></textFieldExpression>            </textField>[/code]

I have set the pattern in the following ways:

    <property name="net.sf.jasperreports.export.xls.pattern.h:mm a=h:mm AM/PM"/>[/code]

or 

    <property name="net.sf.jasperreports.export.xls.pattern.h:mm a=h:mm AM/PM"/>[/code]

In both cases, when I export to excel, my time in the cell looks like

13:43 a

and when I look in the formula window, I see 5/31/2019  1:43:26 PM. Can someone please tell me what I am doing incorrect?

[qianyd]

I had the same issue recently. It has been solved by adding <property name="net.sf.jasperreports.export.xls.pattern" value="M/d/yyyy h:mm AM/PM"/>  to the cell that need to be formatted.

[qianyd]

I had the same issue recently. It has been solved by adding <property name="net.sf.jasperreports.export.xls.pattern" value="M/d/yyyy h:mm AM/PM"/>  to the cell that need to be formatted.

qianyd

[davidfritch]

In my case, I needed to display timestamps based on locale. Adding onto qianyd's answer above, I selected the element that needed formatting, went to properties, searched for the "net.sf.jasperreports.export.xls.pattern" property  and used a Pattern Expression for that property with this value

$P{REPORT_LOCALE}.equals(Locale.forLanguageTag("en-gb")) ? "dd/MM/yyyy HH:mm" : $P{REPORT_LOCALE}.equals(Locale.forLanguageTag("fr-ca")) ? "yy-MM-dd HH:mm" :  "M/d/yy h:mm AM/PM"[/code]

[hello_34]

 I had the same issue which which converts date format Apr 14, 2023, 09:29:00 PM to Oct 23, 2022, 02:34:00 a when export report to excel with jasper report version 8.

 I was able to fix it by making following property inside bean id="xlsExportParameters" in applicationContext.xml (WEB-INF/applicationContext.xml) and restart the server.

```<property name="detectCellType" value="false"/>```