null pointer exception apache poi

11
votes

hi we've been reading xls and xlsx file using apache poi ing our java program, the problem is we are getting null pointer exception with two reasons.. the first 1 is the blank cell which we already solved and the other one is when we are choosing a certain column that doesn't have any record..

our program ask for the path of the excel file then the specific sheet number of the file and the specific column number of the sheet you want to read.. here is the code for reading xls file

public void readXLSFile()throws IOException{
    InputStream ExcelFileToRead = new FileInputStream(path);
    HSSFWorkbook wb = new HSSFWorkbook(ExcelFileToRead);


    HSSFSheet sheet=wb.getSheetAt(sheetname);
    HSSFRow row; 
    HSSFCell cell;

    Iterator rows = sheet.rowIterator();

            list1.clear();

    while (rows.hasNext())
    {
                   headers.clear();
        row=(HSSFRow) rows.next();

                // Iterator cells = row.cellIterator();

                    headers.add("contents");


            cnt = cnt+1;

            cell = row.getCell(cols);
            if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
            {
                //System.out.println(cell.getStringCellValue()+"(string)");
                list.add(cell.getStringCellValue());
                                    d.add(cell.getStringCellValue());
                                    list1.add(new KeyValuePair(cell.getStringCellValue(),""));
            }
            else if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC)
            {
                //System.out.println(cell.getNumericCellValue()+"(numeric)");
                double num = cell.getNumericCellValue();
                String num2 = String.valueOf(num);
                list.add(num2);
                                     d.add(num2);
                                     list1.add(new KeyValuePair(num2,""));

            }
            else if(cell.getCellType() == HSSFCell.CELL_TYPE_BOOLEAN)
            {
                //System.out.println(cell.getBooleanCellValue()+"(boolean)");
                String bool = String.valueOf(cell.getBooleanCellValue());
                list.add(bool);
                                     d.add(bool);
                                     list1.add(new KeyValuePair(bool,""));
            }
            else
            {
                //U Can Handel Boolean, Formula, Errors
            }


        //System.out.println();
    }
        arrey = list.toArray(new String[list.size()]);
                    data.add(d);
                   // System.out.println(data);


                     model = new DefaultTableModel();
                     table_1.setModel(model);


                   table_1.setModel(model);
                      model.setColumnIdentifiers(new String[] {"row","contents"});

                     for (KeyValuePair p : list1){


                       int nor=table_1.getRowCount();

                       int n2 = nor +1;
                        n1 = Integer.toString(n2);
                     //  model.addColumn(new String [] {n1});   


                       model.addRow(new String[] {n1,p.key, p.value});


                     }
                  //   model.addColumn(new String[] {n1});

}

the variable sheetname is for the excel file's sheet number

HSSFSheet sheet=wb.getSheetAt(sheetname);

and the variable cols is for the specific column you want to read

cell = row.getCell(cols);

we can read the first column of every sheet and also the second column of the second sheet but when i edited my test file the program now can only read the first column of every sheet.. the error is null pointer exception..wish you could help thanks in advance

2
javaexcelnullpointerexceptionapache-poi
can you print the stack trace of the exception. I will help if we could know which statement is actually causing exception. – Ankur Shanbhag
Its a bit confusing... You are using sheetname that seems to be a String with HSSFSheet sheet=wb.getSheetAt(sheetname);. getSheetAt is always accepting numeric value for sheet index. Please confirm. – Sankumarsingh
this is the only error im getting java.lang.NullPointerException by the way, i surround the method mentioned above in try catch.. – y.reyes
@Sankumarsingh the sheetname variable and cols are both integer cause it only accepts numeric i just named it that way, sorry if it seems confusing :) – y.reyes
Maybe you can share the line where the exception occurs and the stacktrace. – c.s.

2 Answers

7
votes

The issue is that you never test if the cell is null!

if (cell == null)
{
   System.out.println("Cell is Empty in Column:" + cols);

} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING)
{
   //code
}

As a general matter, you should be careful while handling Cell.getCellType() function, since an empty cell could be either null or be a CELL_TYPE_BLANK.

2
votes

This my way to avoid Cell NullPoiterException.
Can you try it. Good luck!

/**
     * Get string value of {@link Cell} object
     * 
     * @param cell
     *          {@link Cell} object
     * @return String value of {@link Cell} object
     */
    private static String getCellValueString(Cell cell) {
        String value="";
        if(cell!=null) {
            switch(cell.getCellType()){
                case Cell.CELL_TYPE_BOOLEAN:
                    value=String.valueOf(cell.getBooleanCellValue());
                    break;
                case Cell.CELL_TYPE_NUMERIC:
                    value=BigDecimal.valueOf(
                        cell.getNumericCellValue()).toPlainString();
                    break;
                case Cell.CELL_TYPE_STRING:
                    value=String.valueOf(cell.getStringCellValue());
                    break;
                case Cell.CELL_TYPE_FORMULA:
                    value=String.valueOf(cell.getCellFormula());
                    break;
                case Cell.CELL_TYPE_BLANK:
                    value="";
                    break;
            }
        } else {
            logger.error("Cell is null");
        }
        return value.trim();
    }