How To handle Null Row using Apache POI?

Question

I am using Apache POI to read xlsx file, it works well. I have question to you when row is found null, how I'm able to handle it? My file contain 500 row, but it show 105667 row, rest of row found null.

used code:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.logging.Level;
import java.util.logging.Logger;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 *
 * @author SAMEEK
 */
public class readXLSXFile {
public int getNumberOfColumn(String fileName, int sheetIndex) throws FileNotFoundException, IOException {
    File inputFile = null;
    FileInputStream fis = null;
    XSSFWorkbook workbook = null;
    XSSFSheet sheet = null;
    XSSFRow row = null;
    int lastRowNum = 0;
    int lastCellNum = 0;


    // Open the workbook
    inputFile = new File(fileName);
    fis = new FileInputStream(inputFile);
    workbook = new XSSFWorkbook(fis);
    sheet = workbook.getSheetAt(sheetIndex);
    lastRowNum = sheet.getLastRowNum();

    for (int i = 0; i < lastRowNum; i++) {

        row = sheet.getRow(i);
        if (row != null) {
            if (row.getLastCellNum() > lastCellNum) {
                lastCellNum = row.getLastCellNum();
            }
        }
    }

    return lastCellNum;
}

public int getNumberOfRow(String fileName, int sheetIndex) throws FileNotFoundException, IOException {
    File inputFile = null;
    FileInputStream fis = null;
    XSSFWorkbook workbook = null;
    XSSFSheet sheet = null;
    int lastRowNum = 0;

    // Open the workbook
    inputFile = new File(fileName);
    fis = new FileInputStream(inputFile);
    workbook = new XSSFWorkbook(fis);
    sheet = workbook.getSheetAt(sheetIndex);
    lastRowNum = sheet.getLastRowNum();
    return lastRowNum;
}

public String[] getSheetName(String fileName) throws FileNotFoundException, IOException {
    int totalsheet = 0;
    int i = 0;
    String[] sheetName = null;
    File inputFile = null;
    FileInputStream fis = null;
    XSSFWorkbook workbook = null;

    // Open the workbook
    inputFile = new File(fileName);
    fis = new FileInputStream(inputFile);
    workbook = new XSSFWorkbook(fis);
    totalsheet = workbook.getNumberOfSheets();
    sheetName = new String[totalsheet];
    while (i < totalsheet) {
        sheetName[i] = workbook.getSheetName(i);
        i++;
    }

    return sheetName;
}

public int getNumberOfSheet(String fileName) throws FileNotFoundException, IOException {
    int totalsheet = 0;
    File inputFile = null;
    FileInputStream fis = null;
    XSSFWorkbook workbook = null;
    XSSFSheet sheet = null;
    int lastRowNum = 0;

    // Open the workbook
    inputFile = new File(fileName);
    fis = new FileInputStream(inputFile);
    workbook = new XSSFWorkbook(fis);
    totalsheet = workbook.getNumberOfSheets();
    return totalsheet;
}

public String[][] getSheetData(String fileName, int sheetIndex) throws FileNotFoundException, IOException, InvalidFormatException {
    String[][] data = null;
    int i = 0;
    int j = 0;Cell cell=null;
    long emptyrowcount = 0;
    InputStream inputStream = new FileInputStream(
            fileName);
    // Create a workbook object.
    Workbook wb = WorkbookFactory.create(inputStream);
    wb.setMissingCellPolicy(Row.CREATE_NULL_AS_BLANK);
    Sheet sheet = wb.getSheetAt(sheetIndex);
    // Iterate over all the row and cells
    int noOfColumns = getNumberOfColumn(fileName, sheetIndex);
    System.out.println("noOfColumns::" + noOfColumns);
    int noOfRows = getNumberOfRow(fileName, sheetIndex) + 1;
    System.out.println("noOfRows::" + noOfRows);
    data = new String[noOfRows][noOfColumns];

    for (int k = 0; k < noOfRows; k++) {
        Row row = sheet.getRow(k);
        if (row == null) {


        } else {
            j = 0;
            for (int l = 0; l < noOfColumns; l++) {
                // Cell cell = cit.next();
                cell = row.getCell(j);


                if (cell.getCellType() == cell.CELL_TYPE_BLANK) {
                    cell = row.getCell(j, Row.CREATE_NULL_AS_BLANK);
                }

                data[i][j] = getCellValueAsString(cell);
                j++;

            }
            i++;

        }
    }

    return data;
}

/**
 * This method for the type of data in the cell, extracts the data and
 * returns it as a string.
 */
public static String getCellValueAsString(Cell cell) {
    String strCellValue = null;
    if (cell != null) {
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_STRING:
                strCellValue = cell.toString();
                break;
            case Cell.CELL_TYPE_NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    SimpleDateFormat dateFormat = new SimpleDateFormat(
                            "dd/MM/yyyy");
                    strCellValue = dateFormat.format(cell.getDateCellValue());
                } else {
                    Double value = cell.getNumericCellValue();
                    Long longValue = value.longValue();
                    strCellValue = new String(longValue.toString());
                }
                break;
            case Cell.CELL_TYPE_BOOLEAN:
                strCellValue = new String(new Boolean(
                        cell.getBooleanCellValue()).toString());
                break;
            case Cell.CELL_TYPE_BLANK:
                strCellValue = "";
                break;

        }
    }

    return strCellValue;
}

public static void main(String s[]) {
    try {
        readXLSXFile readXLSxFile = new readXLSXFile();
        String[][] sheetData = readXLSxFile.getSheetData("F:/work.xlsx", 0);

        int columnLength = 0;
        columnLength = readXLSxFile.getNumberOfColumn("F:/work.xlsx", 0);
        int rowLength = 0;
        rowLength = readXLSxFile.getNumberOfRow("F:/work.xlsx", 0);


        int h = 0;
        int j = 0;
        while (j < rowLength) {
            h = 0;
            while (h < columnLength) {
                System.out.print("\t     " + sheetData[j][h]);
                h++;
            }
            System.out.println("");
            j++;
        }

    } catch (InvalidFormatException ex) {
        Logger.getLogger(readXLSFile.class.getName()).log(Level.SEVERE, null, ex);
    } catch (FileNotFoundException ex) {
        Logger.getLogger(readXLSFile.class.getName()).log(Level.SEVERE, null, ex);
    } catch (IOException ex) {
        Logger.getLogger(readXLSFile.class.getName()).log(Level.SEVERE, null, ex);


       }
    }
}

Please help me how to handle null row in excel sheet?

Answer 1

In case your .xlsx file contains any of the formatting for the blank cells, the poi reading is not treating it as null, however if you want to print it's value, it will give NullPointerException. To understand it I have created a sheet and mark the first columns boundary with to "All Border" for 10 rows, but not given any value to it. now applying following piece of code is showing output sheet.lastRowNum() as 10, while the RowCountWithNullValue is 990, and RowCountWithoutNullValue is 10. However the sheet is completely blank. If you uncomment the print statement, it will show NullPointerException.

public class Rough {
public static void main(String args[]) throws IOException{
    public static void main(String args[]) throws IOException{
    FileInputStream fin = new FileInputStream(AddressOfxlsxFile);
    XSSFWorkbook wb = new XSSFWorkbook(fin);
    XSSFSheet sheet = wb.getSheetAt(1);
    int RowCountWithNullValue=0, RowCountWithoutNullValue=0;
    for (int i=0;i<1000;i++){
        if (sheet.getRow(i)==null)
            RowCountWithNullValue++;
        else{
            RowCountWithoutNullValue++;
        //  System.out.println(sheet.getRow(0).getCell(0));
        }
    }
    System.out.println(sheet.getLastRowNum());
    System.out.println(RowCountWithNullValue+","+RowCountWithoutNullValue);
  }
}

I am not sure if the same is happening on your end or not, but if you are saying your file contain 500 row, but it show 105667 row, this may be one of the cause.

Answer 2

If you fetch a row, and get back null, then that means there is no data stored in the file for that row - it's completely blank.

POI by default gives you what's in the file. With Cells, you can set a MissingCellPolicy to control how missing and blank cells are handled. There's some examples of using this in the Apache POI docs. With rows, they're either there or not, so you need to check for nulls when fetching a row.