Support Article

Unable to read data from excel sheet when file type is .xls

SA-7799

Summary



Application requirement is to create workobject for each row present in the uploaded excel file. To achieve this MSOParseExcelFile activity is used. This is pega provided OOTB activity which will be used to parse the rows present in the uploaded excel file.
When user uploads excel file with extension .xlsx, then system is working correctly, however when user tries to upload file with extension .xls, system is throwing error.

Error Messages



The uploaded file template could not be recognized. Please upload a valid file. Refer to template UploadFXRates for details.

Steps to Reproduce



1) configure UploadFile control in section to upload an excel file at runtime.
2) Use MSOParseExcelFile activity to parse the data present in the uploaded excel file.
3. Run the functionality and try to upload .xls file which contains the data.
Observe the behaviour.

Root Cause



The root cause of this problem is a defect in Pegasystems’ code/rules.
MSOParseExcelFile is used to parse the files with extension .xlsx files only. It is the limitation of this activity.


Resolution



The explanation for this behavior is as follows:
To parse the excel files with extension .xls, Developer need to create custom activity with java code using ApachePOI jar files.

Sample code using HSSF format to calculate the number of sheets present in the uploaded excel files with extension .xls
This code need to extended further to parse all the data present in excel file.

java.io.ByteArrayInputStream bis=null; 
 try { 
            bis=new java.io.ByteArrayInputStream( (byte[]) fileBytes ); 
            com.pega.apache.poi.hssf.usermodel.HSSFWorkbook workbook=new com.pega.apache.poi.hssf.usermodel.HSSFWorkbook(bis); 
            tools.putParamValue("numberOfSheets", workbook.getNumberOfSheets() ); 

catch(Exception e) { 
    oLog.error("Error processing file", e); 
}
 

Published January 31, 2016 - Updated October 8, 2020


0% found this useful

Have a question? Get answers now.

Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.