Question
JPMorgan Chase & Co.
IN
Last activity: 13 Jan 2017 14:53 EST
Excel Format issue when sending as an attachment in Email
Hi, We are facing the excel format issue as mentioned below. could you please help us to resolve this issue.
Description
Hi, We are facing the excel format issue as mentioned below. could you please help us to resolve this issue.
Description
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.
***Moderator Edit: Vidyaranjan| Updated Categories; added SR details***
EPAM Systems, Inc.
ES
Adding research details from SR and redirected this design/implementation related query to PSC for larger audience to assist
- Importing the excel file in Pega and parse the desired content. Verify that the desired data is correctly mapped in to the clipboard, especially date fields.
- Generating Excel file and sending it as an attachment with email. I suspect that the parsing is not happening properly and date format is lost while importing.
Import and Parsing:-
- I have explained you regarding the date format in excel. 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. Refer this link for details :- http://www.cpearson.com/excel/datetime.htm
For example:-
Adding research details from SR and redirected this design/implementation related query to PSC for larger audience to assist
- Importing the excel file in Pega and parse the desired content. Verify that the desired data is correctly mapped in to the clipboard, especially date fields.
- Generating Excel file and sending it as an attachment with email. I suspect that the parsing is not happening properly and date format is lost while importing.
Import and Parsing:-
- I have explained you regarding the date format in excel. 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. Refer this link for details :- http://www.cpearson.com/excel/datetime.htm
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. 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 you will probably need to write yourself)
Ex: - 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://docs-previous.pega.com/configuring-application-bulk-upload-data-using-excel-spreadsheet.
- Similar kind of implementation(though it’s exactly similar to yours but it could helpful in understanding the generating of excel), where user is trying to export the content to excel and then editing it and upload it back to PEGA is doable in PRPC.
Refer this Flow action as reference : PegaAccel-Task-ImportExport-Requirements • pzUploadRequirementsExcel Pega-AppDefinition:07-10-01 which is calling pzExcelValidateAndUploadRequirement activity as post action and in this activity step 9 we are having a call to MSOParseExcelFile activity which is validating and uploading the excel.
Also please see this articles:-
https://community.pega.com/sites/default/files/help_v719/procomhelpmain.htm
- Additionally, parsing excel in java can also be implemented as a Java step in activity. Reference :- Write/Read Excel files in Java Example | Examples Java Code Geeks - 2016.
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);
}
else
{
sendData("","Error. Can't get \"pxRequestor.pyHTTPResponseHeaders\" page");
}
3. Page-New AttachmentLinksOpened page (code-pega-list)
4. Property-Set - AttachmentLinksOpened.pxResults(<APPEND>).pxObjClass = 'Data-WorkAttach-File'
AttachmentLinksOpened.pxResults(<LAST>).pyAttachStream = PegaFunction Base64Encode(param.strFileData).
5. Page-New AttachmentPage ( Data-EmailAttachments)
6. Property-Set
AttachmentPage.pyAttachment(<APPEND>).pyDecode == True
AttachmentPage.pyAttachment(<APPEND>).pyName == "ExportReport.xls"
AttachmentPage.pyAttachment(<APPEND>).pyReference == AttachmentLinksOpened.pxResults(1).pyAttachStream
AttachmentPage.pyAttachment(<APPEND>).pyRemoveXML == False
EPAM Systems, Inc.
ES
Refer this as well, it wil be helpful in generating excel file.
Pegasystems Inc.
US
Hi Srikanth!
Upon further review, the teams here believe this would be best handled by going through our Professional Services team to assist you. Please contact your Account Executive for next steps.
Thank you!
Marissa | Community Moderator | Pegasystems Inc.