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.
Excel Format issue when sending as an attachment in Email.
Requirement: User need to upload the Excel file which has specific column formats (Eg: Date, Number etc..) 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.
Steps to Reproduce
1. Upload an excel file with column format as Date, Number, Text etc.. 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.
User is not able to see the specified column format once he/she received the Email with attachment.
***Moderator Edit: Vidyaranjan| Updated Categories; added SR details***
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
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
- 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.