[FIXED] Apache Poi excel remove blank Rows Within range

Issue

When I try to upload this file to my application, It shows an error in row 4. When I try int totalRows = worksheet.getPhysicalNumberOfRows(); This shows incorrect number of rows(like 26,306). But this error only occurs in some excel files. I want to add records to my application which contain in excel file. How to delete this empty records?

This is my code

        List<NewLocationFile> newLocationList = new ArrayList<>();
        
        StringBuilder columnBuffer = new StringBuilder();
        String comma = "";
        List<NewLocationFile> updatedLocationList = new ArrayList<>();
        
        try (Workbook workbook = new XSSFWorkbook(inputStream);) {

            Sheet worksheet = workbook.getSheetAt(0);
            int totalRows = worksheet.getPhysicalNumberOfRows();
            worksheet.removeRow(worksheet.getRow(0));// remove header
            
            LOGGER.info("readNewLocationFileRequest:traceId={}|totalRows={}",traceId,totalRows);


            if (totalRows <= 1) {
                throw new PSException(ErrorCode.INVALID_INPUT_PROVIDED, "Empty excel sheet ");
            } 
            else {
                newLocationList.addAll(locationDetails(worksheet, traceId));
            }
            
                

private List<NewLocationFile> locationDetails(Sheet worksheet String traceId) {
        
        
        List<NewLocationFile> newLocationList = new ArrayList<>();
        int j = 0;
        for (Row row : worksheet) {
            j++;
            int excelSheetRow = j + 1;

            newLocationList.add(returnLocations(row, excelSheetRow, userBrn,traceId));
        }
        
        String converToString = CommonUtil.convertToString(newLocationList);
        return newLocationList;
    }
private NewLocationFile returnLocations(Row row,int excelSheetRow,String traceId)
{

    String productCategory = null;
    //initiate all values to null here


if (dataFormatter.formatCellValue(row.getCell(13)).trim().length() > 0) {
            productCategory = CommonUtil.getWorkSheetCellStringValue(row.getCell(13)).toUpperCase();
        } else {
            throw new PostSaleModificationException(ErrorCode.INVALID_PRODUCT_TYPE,
                    "Invalid product category in row :" + excelSheetRow);
        }

//All validations listed here 

newLocation.setComplexProduct(complexProduct);
//set all values here

}

But Error message pop-up is displayed "Invalid product category in row 4" But this sheet has only 3 rows.

Solution

I found this solution ;)

    

        List<NewLocationFile> newLocationList = new ArrayList<>();
        
        StringBuilder columnBuffer = new StringBuilder();
        String comma = "";
        List<NewLocationFile> updatedLocationList = new ArrayList<>();
        
        try (Workbook workbook = new XSSFWorkbook(inputStream);) {

            Sheet worksheet = workbook.getSheetAt(0);
            int totalRows = worksheet.getPhysicalNumberOfRows();
            worksheet.removeRow(worksheet.getRow(0));// remove header

        removeEmptyRows(worksheet);
            
            LOGGER.info("readNewLocationFileRequest:traceId={}|totalRows={}",traceId,totalRows);


            if (totalRows <= 1) {
                throw new PSException(ErrorCode.INVALID_INPUT_PROVIDED, "Empty excel sheet ");
            } 
            else {
                newLocationList.addAll(locationDetails(worksheet, traceId));
            }
            




private Sheet removeEmptyRows(Sheet worksheet) {
        
         boolean stop = false;
         boolean nonBlankRowFound;
         short c;
         XSSFRow lastRow = null;
         XSSFCell cell = null;
         
         while (!stop) {
             nonBlankRowFound = false;
             lastRow = (XSSFRow) worksheet.getRow(worksheet.getLastRowNum());
             for (c = lastRow.getFirstCellNum(); c <= lastRow.getLastCellNum(); c++) {
                 cell = lastRow.getCell(c);
                 if (cell != null && lastRow.getCell(c).getCellType() != CellType.BLANK) {
                     nonBlankRowFound = true;
                 }
             }
             if (nonBlankRowFound == true) {
                 stop = true;
             } else {
                 worksheet.removeRow(lastRow);
             }
         }
         
         return worksheet;
        
    }

Answered By - Nisha
Answer Checked By - Clifford M. (JavaFixing Volunteer)