Java POI: Is Intuitively Empty Check for Excel

1) Check if a cell is intuitively empty

public class CellUtil
{
    private CellUtil()
    {
    }

    /**
     * <p>
     * If cell is null or intuitively empty <br/>
     * NPE Safe<br/>
     * </p>
     * 
     * @param cell
     * @return
     */
    public static boolean isCellEmpty(Cell cell)
    {
        if (null != cell && Cell.CELL_TYPE_BLANK != cell.getCellType()
                && !StringUtil.isEmpty(ExcelUtil.getValue(cell, true)))
        {
            return false;
        }
        else
        {
            return true;
        }
    }
}

 

2. Check if a row is intuitively empty

public class RowUtil
{
    private static final int EMPTY_CELL_COUNT_THRESHOLD = 100;

    private RowUtil()
    {
    }

    /**
     * <p>
     * Get the intuitive last cell num in row <br/>
     * NPE Safe<br/>
     * </p>
     * <p>
     * Use the same check machanism with
     * {@link SheetUtil#getLastRowNumber(org.apache.poi.ss.usermodel.Sheet)}
     * </p>
     * 
     * @param row
     * @return
     */
    public static int getLastCellNum(Row row)
    {
        int lastCellNumber = 0;

        if (isRowEmpty(row))
        {
            return lastCellNumber;
        }
        for (int i = 0; i < row.getLastCellNum(); i++)
        {
            Cell cell = row.getCell(i);
            lastCellNumber = i;

            if (CellUtil.isCellEmpty(cell)
                    && isCellsAllEmpty(row, i, i + EMPTY_CELL_COUNT_THRESHOLD))
            {
                lastCellNumber = i - 1;
                break;
            }
        }
        return lastCellNumber;
    }

    /**
     * <p>
     * Whether all the cells from startCellNum to endCellNum are empty <br/>
     * NPE Safe <br/>
     * </p>
     * 
     * @param row
     * @param startCellNum
     * @param endCellNum
     * @return
     */
    public static boolean isCellsAllEmpty(Row row, int startCellNum,
            int endCellNum)
    {
        if (isRowEmpty(row))
        {
            return true;
        }
        for (int i = startCellNum; i <= endCellNum; i++)
        {
            Cell cell = row.getCell(i);
            if (!CellUtil.isCellEmpty(cell))
            {
                return false;
            }
        }
        return true;
    }

    /**
     * <p>
     * Judge if the row is empty when the last cell number is unknown <br/>
     * NPE Safe<br/>
     * </p>
     * 
     * @param row
     * @return
     */
    public static boolean isRowEmpty(Row row)
    {
        return (null == row) ? true : isRowEmpty(row, row.getLastCellNum());
    }

    /**
     * Judge if the row is empty when the last cell number is known
     * 
     * @param row
     * @param lastCellNum
     * @return
     */
    public static boolean isRowEmpty(Row row, int lastCellNum)
    {
        if (null == row)
        {
            return true;
        }

        if (-1 == row.getFirstCellNum())
        {
            return true;
        }
        for (int c = row.getFirstCellNum(); c <= lastCellNum; c++)
        {
            if (!CellUtil.isCellEmpty(row.getCell(c)))
            {
                return false;
            }
        }
        return true;
    }

    /**
     * <p>
     * If all cells are filled in current Row <br/>
     * NPE Safe<br/>
     * </p>
     * <p>
     * All the blank cells at the end of row will not be detected <br/>
     * </p>
     * 
     * @return
     */
    public static boolean isRowFullyFilled(Row row)
    {
        return RowUtil.isRowEmpty(row) ? false : isRowFullyFilled(row,
                RowUtil.getLastCellNum(row));
    }

    public static boolean isRowFullyFilled(Row row, int endColNum)
    {
        return RowUtil.isRowEmpty(row) ? false : isRowFullyFilled(row, 0,
                endColNum);
    }

    public static boolean isRowFullyFilled(Row row, int startColNum,
            int endColNum)
    {
        if (RowUtil.isRowEmpty(row))
        {
            return false;
        }

        boolean isAllFilled = true;

        for (int i = startColNum; i <= endColNum; i++)
        {
            Cell cell = ExcelUtil.getCell(row, i);
            if (CellUtil.isCellEmpty(cell))
            {
                isAllFilled = false;
            }
        }
        return isAllFilled;
    }
}

 

3) SheetUtil.

public class SheetUtil
{
    private final static Logger logger = Logger.getLogger(SheetUtil.class);

    private static final int EMPTY_ROW_COUNT_THRESHOLD = 100;

    /**
     * <p>
     * Get the intuitive last row number for the sheet
     * <p>
     * 
     * @param sheet
     * @return
     */
    public static int getLastRowNumber(Sheet sheet)
    {
        logger.info(String.format("Start getLastRowNumber, sheet: [%s]",
                sheet.getSheetName()));

        int lastRowNumber = 0;

        for (int i = 0; i <= sheet.getLastRowNum(); i++)
        {
            Row row = sheet.getRow(i);
            lastRowNumber = i;

            if (RowUtil.isRowEmpty(row)
                    && isRowsAllEmpty(sheet, i, i + EMPTY_ROW_COUNT_THRESHOLD))
            {
                lastRowNumber = i - 1;
                break;
            }
        }
        logger.info(String
                .format("Finished getLastRowNumber, lastRowNumber: [%d]",
                        lastRowNumber));
        return lastRowNumber;
    }

    /**
     * <p>
     * Whether all the rows from startRowNumber to endRowNumber are empty
     * </p>
     * 
     * @param sheet
     * @param firstEmptyRowNumber
     * @param emptyRowCountThreshold
     */
    public static boolean isRowsAllEmpty(Sheet sheet, int startRowNumber,
            int endRowNumber)
    {
        for (int i = startRowNumber; i <= endRowNumber; i++)
        {
            if (!RowUtil.isRowEmpty(sheet.getRow(i)))
            {
                return false;
            }
        }
        return true;
    }
}