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
- Upload an Excel file with empty rows.
- 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");
Published December 2, 2021
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.