Support Article

pxParseExcelFile (MSOParseExcel) reads empty rows in excel

SA-106740

Summary



On uploading an Excel file with empty rows, the pxParseExcelFile (MSOParseExcel) reads the empty rows in the Excel.


Error Messages



Not Applicable


Steps to Reproduce

  1. Upload an Excel file with empty rows.
  2. Parse the Excel file.


Root Cause



An issue in the custom application code or rules.


Resolution



Perform the following local-change:

Use below the code in the Java step before invoking the pxParseExcelFile activity,

try{
java.io.InputStream is = new PRInputStream(tools.getProperty("pxRequestor.pyFileUpload").getStringValue());  
org.apache.poi.ss.usermodel.Workbook wb = new org.apache.poi.xssf.usermodel.XSSFWorkbook(is);
org.apache.poi.ss.usermodel.Workbook wb2 = new org.apache.poi.xssf.usermodel.XSSFWorkbook(); 

for (int i = 0; i < wb.getNumberOfSheets(); i++){
  org.apache.poi.ss.usermodel.Sheet inputSheet = wb.getSheetAt(i);
  org.apache.poi.ss.usermodel.Sheet outSheet = wb2.createSheet(inputSheet.getSheetName());
  for(int j = 0; j < inputSheet.getLastRowNum(); j++){
    org.apache.poi.ss.usermodel.Row inputRow = inputSheet.getRow(j);
    org.apache.poi.ss.usermodel.Row outRow = outSheet.createRow(j);
    boolean isEmptyRow = true;
    for(int cellNum = inputRow.getFirstCellNum(); cellNum < inputRow.getLastCellNum(); cellNum++){
      org.apache.poi.ss.usermodel.Cell cell = inputRow.getCell(cellNum);
      org.apache.poi.ss.usermodel.Cell outcell = outRow.createCell(cellNum);
      if(cell != null && cell.getCellType() != org.apache.poi.ss.usermodel.CellType.BLANK && org.apache.commons.lang3.StringUtils.isNotBlank(cell.toString())){
            isEmptyRow = false;
       }
      outcell.setCellType(cell.getCellType());
      if(cell != null){
      switch (cell.getCellType()) {
                                case BLANK:
                                    outcell.setCellValue("");
                                    break;

                                case BOOLEAN:
                                    outcell.setCellValue(cell.getBooleanCellValue());
                                    break;

                                case ERROR:
                                    outcell.setCellErrorValue(cell.getErrorCellValue());
                                    break;

                                case FORMULA:
                                    outcell.setCellFormula(cell.getCellFormula());
                                    break;

                                case NUMERIC:
                                    if (org.apache.poi.ss.usermodel.DateUtil.isCellDateFormatted(outcell)) {                            
                                      outcell.setCellValue(cell.getDateCellValue());
                                    } else {                             
                                      outcell.setCellValue(cell.getNumericCellValue());
                                    }
                                    break;

                                case STRING:
                                    outcell.setCellValue(cell.getStringCellValue());
                                    break;
                                default:
                                    outcell.setCellFormula(cell.getCellFormula());
                        }
      }
    }
    if(isEmptyRow){
        outSheet.removeRow(outRow);
        break;
    }
}
}
PROutputStream os = new PROutputStream(new PRFile(tools.getProperty("pxRequestor.pyFileUpload").getStringValue())); 
wb2.write(os); 
}catch(Exception e){
  oLog.error("error removing empty rows");
Suggest Edit

Published April 15, 2020 - Updated June 29, 2020

Have a question? Get answers now.

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