Support Article
Report 'Date Time' format (custom dd MMM yyyy) wrong in Excel
SA-16129
Summary
A report definition is used to export date to Excel. The excel-date metadata that is passed to the HTML stream that is generated when exporting date to excel does not take in to consideration custom date formatting set on the report, but just always formats according to user locale. This causes Excel to misinterpret some of the dates passed, if the format given in report is different from the one coming from locale.
Error Messages
Not Applicable
Steps to Reproduce
1. Create a report definition.
2. Specify a date to be displayed with the Date Time control that has to be displayed as "2001/01/01 1:00:00 AM".
3. At run time, use the export to excel.
For instance, the date displayed at runtime on the report in the webpage is: "2014/09/22 10:52:03 AM"
The date displayed in Excel is "9/22/2014 10:52 AM" and does not correspond to the date format specified in the report definition.
It is also observed that the Excel file contains the following styling when opened in a text editor:
.excel-datetime{vnd.ms-excel.numberformat:M/d/yyyy h:mm AM/PM}),
Root Cause
Report Definition does not provide a specific control for exporting to Excel. Out-of-the-box, Report Definition exports date in short format using the user locale.
Resolution
PRPC provides the possibility to create custom control which allows specifying a different formatting when exporting to Excel compared to when displaying reports on web pages.
This relies on a parameter called "reportMode" which is equal to "Excel" when exporting to excel. This parameter can be used to export data.
Below is an approach for creating a custom control (with few associated rules) for configuring export to Excel according to your formatting requirement. This custom control also references an auto-generated control for the display of the report on the web page so that you can still benefit from the feature provided by these recommended controls.
NOTE: To get an excel date displayed as per your requirement, Excel must first be able tor recognize the date.
If the PRPC user local is not aligned with Excel local, then Excel will not recognize the date or misinterpret it. For instance, if the PRPC locale is US and Excel local is UK, then a 01/15/2012 (15th of January in US local) will not be recognized by Excel. This results in the cell not recognized as date and Excel formatting not applied. In this case, the values display left aligned.
To prevent this behavior, it is best to export date in an unambiguous format before it is styled by Excel; for instance: ISO 8601 formatting as in the sample code provided.
Once the date is recognized, specify the date formatting.
For example:
<div class="excel-datetime" style="vnd.ms-excel.numberformat:MMM/dd/yy h:mm AM/PM">
Custom control and associated rule solution steps:
1. Create a property in @baseclass
2. Create a section which is referencing the @baseclass property and where an auto-generated control is used:
NOTE how the “PARENT” keyword is used. This is used to save memory as the value is only saved once at the parent page (typically pyReportContentPage) rather than in each row returned by the report.
3. Create a custom control referencing the section and passing the appropriate value to the property.
The code below uses custom code for the export to excel while it references auto-generated code for the web page:
<%
if(tools.getParamValue("reportMode").equals("Excel")) {
%>
<div class="excel-datetime" style="vnd.ms-excel.numberformat:MMM/dd/yy h:mm AM/PM">
<%
String mPattern = tools.getParamValue("pattern");
ClipboardProperty curProp = tools.getActive();
if ( (curProp.getType() == PropertyInfo.TYPE_DATETIME || curProp.getType() == PropertyInfo.TYPE_DATE) && curProp.size() > 0 && curProp.hasValidValue() && curProp.toDate()!=null)
{
java.util.Date theDate = null;
try
{
String strValue = curProp.getStringValue();
theDate = PRDateFormat.parseAsDateTime(null,null,null,strValue);
} catch( com.pega.pegarules.pub.clipboard.InvalidValueException ive )
{
theDate = null;
} finally
{
if (theDate !=null)
{
tools.appendString(PRDateFormat.format(null,null, PRDateFormat.DEFAULT_DATETIME_ISO_8601, theDate));
}
else
{
tools.appendString(StringUtils.crossScriptingFilter(tools.getActiveValue()));
}
}
}
%>
</div>
<%
} else {
ClipboardPage myStepPage = tools.getStepPage();
String[] pRef_1 = new String[] { "PARENT", "","Xavpropertyforcontroldate", "" };
pega.setViaPropRef("PARENT.Xavpropertyforcontroldate", pRef_1,myStepPage, tools.getActiveValue(),"sMN", false, true);
%>
<pega:include name="Xavdatefix" type="Rule-HTML-Section" />
<%
}
%>
NOTE on the code: the current value of the cell in the report is copied to the property used in the section through the following lines:
pega.setViaPropRef("PARENT.Xavpropertyforcontroldate", pRef_1,myStepPage, tools.getActiveValue(),"sMN", false, true);
The custom section is referenced here:
<pega:include name="Xavdatefix" type="Rule-HTML-Section" />
Published January 31, 2016 - 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.