Incorrect date format seen for JRXML and Ad Hoc Report Exports in XLSX/XLS

Problem:
In JasperReports Server 8.0.2 when exporting JRXML reports and Ad Hoc Reports to XLSX/XLS, the date format seen in the export output file is incorrect with 'a' appended and the dates are converted from 12hr to 24hr format, like:
Feb 16, 2023, 15:57:03 a

Cause:
This was a bug in JasperReports Library since introduction of the newer exporter configuration API. A non null Map object was preventing the deprecated parameter value of the exporter to be read.

Solution:
The date format mappings configuration of our JasperReports Server can be seen in the applicationContext.xml file (<jasperserver-pro>/WEB-INF/) under the formatPatternsMap map id, like:

 

<util:map id="formatPatternsMap">
<!-- entry key="$ #,##0.00" value="$ #,##0.00"/-->

<entry key="MMM d, yyyy h:mm:ss a" value="MMM d, yyyy h:mm:ss AM/PM"/>
<entry key="h:mm:ss a" value="h:mm:ss AM/PM"/>
<entry key="h:mm a" value="h:mm AM/PM"/>
<entry key="h:mm:ss a z" value="h:mm:ss AM/PM"/>
</util:map>

A bug report with internal ticket number JS-64895 was filed to resolve this issue and this has been addressed with a hotfix. The hotfix - hotfix_JRSPro8.0.2_cumulative_20221206_1040.zip - or a later cumulative hotfix can be applied to the JasperReports Server installation to fix this issue.

Please also note that we need to include the below property with the required date format pattern for XLSX export in the JRXML file, in addition to defining the date pattern for the text field.

For example,

Report Level property required to be used:
 

<property name="net.sf.jasperreports.export.xls.pattern.MMM d, yyyy h:mm:ss a" value="d,mmm,yyyy h:mm AM/PM"/>

Text Field date pattern expression definition:
 

<textField>
<reportElement x="0" y="0" width="111" height="30" uuid="a8a0511e-31f4-4a75-a23a-bfebc16b2514">
<property name="com.jaspersoft.studio.spreadsheet.connectionID" value="c04e1bee-c79a-41e4-9ee1-eef28cbc2a89"/>
</reportElement>
<textFieldExpression><![CDATA[$F{date_start}]]></textFieldExpression>
<patternExpression><![CDATA["MMM d, yyyy h:mm:ss a"]]></patternExpression>
</textField>

______________________________________________________________________________________________________________________________________________________________
Ref. Case #02148238