Support Article
Date Format varies when exporting to Excel from report defintion
SA-78792
Summary
When exporting a report to Excel from the Report browser, the Date format in Excel varies from the Date format in the Report browser.
Error Messages
Not Applicable
Steps to Reproduce
- Launch any portal.
- Click Reports.
- Open any report.
- Click Actions.
- Select Export to Excel.
Root Cause
When the default out-of-the-box DateTime format was provided in the report definition and exported to Excel, the Excel changed the Date format to the system Date format.
Resolution
- Apply HFix-41497.
- Create a new Date control using the below code and provide this control in the report definition at the field level.
<div class="excel-date" style="vnd.ms-excel.dd-MMM-yyyy:@">
<%
ClipboardProperty curProp = tools.getActive();
if ((curProp.getType() == PropertyInfo.TYPE_DATE || curProp.getType() == PropertyInfo.TYPE_DATETIME)&& curProp.size() > 0 && curProp.hasValidValue() && curProp.toDate()!=null) {
String sDatetime="";
String type = tools.getParamValue("DateTimeFormat");
String strTimeZone = tools.findPage("pxRequestor").getString(".pyUseTimeZone");
sDatetime = PRDateFormat.format(null, strTimeZone, "dd/MM/yyyy", curProp.toDate());
tools.putSaveValue("result",sDatetime);
}
else {
tools.putSaveValue("result",tools.getActiveValue());
}
%>
<pega:reference name="$save(result)" />
</div>
Published August 15, 2019 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.