Apache Poi Excel Remove Blank Rows Within Range

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.

2

1 Answer

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;
        
    }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Chloe Bennett

Chloe Bennett

Culture, Media & Entertainment Columnist

Chloe Bennett explores the intersection of pop culture, streaming entertainment, digital trends, and contemporary lifestyle. Her weekly commentary reaches thousands of culture enthusiasts.

Share this article
Twitter Facebook Pinterest