Row row = sheet.getRow(rowNumber) returns java.lang.nullpointer exception even when row exist in Excel sheet. I am using Apache POI

Question

EDIT: I am using this code to read rows and columns of an excel sheet.But the line marked with double star throws exception(java.lang.nullpointer exception).Apart from this i also want to convert null columns found in between to blank columns.Please help.

There are 2 loops one for row and inner one for column as obvious. Niehter the row is getting read nor the blank cell issue is getting resolved.

public void read(String filePath) throws NullPointerException {

                    try {
                    FileInputStream file = new FileInputStream(new File(filePath));
                    System.out.println("going to create Workbook object to read excel row wise");// Create Workbook instance holding reference to .xlsx file
                    XSSFWorkbook workbook = new XSSFWorkbook(file);
                    System.out.println("going to create object sheet to read excel row wise");
                    XSSFSheet sheet = workbook.getSheetAt(0);  //extracts the first sheet of excel
                    System.out.println("Running row iterator to read excel row wise");

                    for (int rowNumber = sheet.getFirstRowNum(); rowNumber <= sheet.getLastRowNum(); rowNumber++) {
                        if (rowNumber == sheet.getFirstRowNum())// this row was skipped as it was header row.
                        {
                        System.out.println("skipping first row");
                        continue;
                        }
                        this.rowconcat = ""; // all values of perticular row will be concatinated in this variable.
                        this.flag = 0;

                        if (sheet.getRow(rowNumber) == null) {
                            System.out.println("row detected null");
                        } else {
                            // The row has data
                            System.out.println("row no inside else==="+rowNumber);
                            **Row row = sheet.getRow(rowNumber);**  //This line here throws null pointer exception.
                        for (int cellNumber = row.getFirstCellNum(); cellNumber <= row.getLastCellNum(); cellNumber++) {
                            Cell cell = row.getCell(cellNumber);
                            if (cell == null || cell.getCellType() == Cell.CELL_TYPE_BLANK) {
                                this.flag = 1; // set to 1 to indicate blank cell but this also doesn't work
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                cell.setCellValue(" "); //inserting space in cell after converting its type to String.
                                this.rowconcat = this.rowconcat + cell.getStringCellValue() + "~";
                            } else {
                                cell.setCellType(Cell.CELL_TYPE_STRING);
                                this.rowconcat = this.rowconcat + cell.getStringCellValue() + "~";
                                }
                            }
                        }
                    }
                }//try block closure
               catch(Exception e){
               //autogenerated
                }
            } // function closure

Answer 1

I recommend JXL library better the appache one, I have worked with it sinc elong time. Its only minor problem is that it works only with xls not xlsx (if no updates have taken place till now!).

Have a small example for that:

File xlsFile;
public void manageData ()
{
        Workbook w;
        try {
            w = Workbook.getWorkbook(xlsFile);
            // Get the first sheet
            Sheet sheet = w.getSheet(0);
            int j = 1;
            //Begin from the second row
            while (j < sheet.getColumns()) {
                //staff
                Cell infoCell = sheet.getCell(j, 4);
                System.out.println(infoCell.getContents());
            }
        catch (Exception e)
        {
        }
}