Support Article
Excel Format issue when sending as an attachment in Email
SA-32486
Summary
User need to upload the Excel file which has specific column formats, for example, Date, Number and so on from UI.
Once the upload complete Page need to send the same excel as an attachment via email in the same uploaded format to the specified Email Id.
Error Messages
User is not able to see the specified column format once he/she received the Email with attachment.
Steps to Reproduce
1. Upload an excel file with column format as Date, Number, Text and so on from UI.
2. System is parsing the uploaded excel file using MSOParseExcelFile activity.
3. Call SendEmailWithAttachments activity by passing parameters pyName as ".XLS" and pyData as "parsed data" to send email with Attachment in Excel.
Root Cause
An issue in the custom application code or rules due to wrong implementation.
Resolution
High level design assistance for Importing and Parsing excel file is required.
Excel stores dates and times as a number representing the number of days since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt .
This is called a serial date, or serial date-time.
For example:
42370 stands for 2016-01-01 (January 1st this year) - but it is a date, not a time/date value.
Unfortunately, the way Microsoft translates date values in Excel into numbers is a bit strange, compared with other approaches.
See here<http://www.cpearson.com/excel/datetime.htm>.
As long as the format of the column is provided to the cell, not to the contents, it is not relevant, no matter if it is dd-mmm-yyyy or yy/dd/mmmm.
Internally, it is always a number, like that 42370 for the date 2016-01-01 or with a fraction for a time/date.
Based on that, a work around could be to handle the column as Number, to convert the value to milliseconds since the beginning of the time period and from there to a java.util.Date.
After getting the data from the MSOParseExcelFile, one must use function addtodate() with parameters 31st December 1899 as the start date and the number of days returned from the parse activity and other parameters as zero.
MSOParseExcelFile is used to parse the files with extension .xlsx files only.
Read the number (the 'Date') as an (EXCEL Decimal Format) Number: and then convert that using a Function - which user must probably need to write.
Example: For the number of days specified in the summary, use @addtodate("18991231T000000.000 GMT", <DateFieldProperty>,0,0,0) which returns 1/11/2016.
Below are the implementation details to configure an application to bulk upload data by using an Excel spreadsheet.
https://pdn.pega.com/configuring-application-bulk-upload-data-using-excel-spreadsheet.
PDN articles:
<https://pdn.pega.com/sites/pdn.pega.com/files/help_v719/procomhelpmain.htm#basics/v6portal/landingpages/application/editinexcel.htm>
Generating excel and sending it as an attachment:-
- Once the data is available in the application in the mapped Page list property, as the data below. TempPage(code-pega-list). TempPage.pxResults() - q-w-e-r-t-y class
TempPage.pxResults(1).Test1 = "X1";
TempPage.pxResults(2).Test1 ="X2";
TempPage.pxResults(3).Test1 ="X3".
Create a HTML rule GenerateExcelStructure with the below content:
<html><body>
<table border="1"><tr><th>Test1</th>
</tr>
<pega:forEach name="TempPage.pxResults"><pega:withEmbedded name="$THIS"><tr><%
String Test1= tools.getStepPage().getString("Test1"); tools.appendString("<td wrap>" + Test1+ "</td>"); %></tr></pega:withEmbedded>
</pega:forEach></table></body></html>
Create an activity "AttachExcelToMail" with below content:
1. Property-set-HTLM. param.pyFileData = "GenerateExcelStructure"
2. Java
String strFileData = getParameterValue("pyFileData");//getPrimaryPage().getValue("pySrc");
Page objHeadersPage = getProperty("pxRequestor.pyHTTPResponseHeaders").getAsPage();
if (objHeadersPage.isBound())
{
objHeadersPage.setValue("contentType" ,"application/vnd.ms-excel");
sendData("", strFileData);
}
Published January 24, 2017 - 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.