Read Excel Sheet

[Prasad Rooge]

I'm trying to read the excel sheet. I've used POI library.

try {
            Workbook workbook = WorkbookFactory.create(new FileInputStream("table.xls"));
            Sheet sheet = workbook.getSheetAt(0);
             int lastrow= sheet.getLastRowNum();
//            Row row= sheet.getRow(5);
//            System.out.println("Cell Value:" + row.getCell(2).getStringCellValue());
          
                    for(int i=0;i<lastrow;i++)
                    {
                     Row row= sheet.getRow(i);
                     System.out.println(row.getCell(0).getStringCellValue());
                   
                    }
        } catch (IOException ex) {
            Logger.getLogger(ReadWriteExcel.class.getName()).log(Level.SEVERE, null, ex);
        } catch (InvalidFormatException ex) {
            Logger.getLogger(ReadWriteExcel.class.getName()).log(Level.SEVERE, null, ex);
        } 

I'm getting null pointer exception at

Row row= sheet.getRow(i);

in for loop.

I want to display all contents from excel sheet.

[Junilu Lacar]

Are you sure you're getting the NPE in the for loop? If anything, I would expect an NPE way back on Line 4 before you'd get one on Line 10. Is that a complete listing or did you omit some code in between Line 4 and Line 10?

[Prasad Rooge]

line 4 not returning NPE. Now i found that the excel sheet I'm reading has first 3 rows empty. so it is returning NPE at line 10. How to read the remaining rows?

[Junilu Lacar]

The JavaDocs say that getRow returns null if the row is not defined. Are you sure that the NPE is thrown on Line 10 and not Line 11? That's where the NPE would be thrown if getRow(i) returns null.

[Prasad Rooge]

My mistake, getCell() method is returning NPE... well then how I'm i supposed to get further rows even if there is an empty row in the middle.

[Ankit Dan]

    
      for (int j = 0; j < sheet.getColumns(); j++) {
        for (int i = 0; i < sheet.getRows(); i++) {
          Cell cell = sheet.getCell(j, i);
          CellType type = cell.getType();
          System.out.println(cell.getContents());
          
        }
      }

this might help...

[Prasad Rooge]

Welcome To Ranch buddy
Well sheet.getColumns() and sheet.getRows() are in JXL library... I want to do it in Apache's POI library..

[Ankit Dan]

Prasad Rooge wrote:Welcome To Ranch buddy
Well sheet.getColumns() and sheet.getRows() are in JXL library... I want to do it in Apache's POI library..

....ok let me read your query again then...

[Ankit Dan]

You can use the static class Row.MissingCellPolicy

Row.getCell(int cellnum, Row.MissingCellPolicy policy) 

and pass Row.RETURN_NULL_AND_BLANK in the policy parameter
which will have missing cells returned as null, Blank cells returned as normal

was this helpful??

[Prasad Rooge]

had seen it in documentation and tried but same exception...

for(int i=0;i<lastrow;i++)  
                        {  
                         Row row= sheet.getRow(i);  
                         System.out.println(row.getCell(i,Row.CREATE_NULL_AS_BLANK).getStringCellValue());  
                         
                        }  

[Ankit Dan]

try this

((row.getCell(0, Row.CREATE_NULL_AS_BLANK).getStringCellValue()) == null) ? "" : (row.getCell(0, Row.CREATE_NULL_AS_BLANK).getStringCellValue());

tell me if this works or not..

[Prasad Rooge]

First i dont know what will that return.. second dont know where do you want me to try it.

[Ankit Dan]

Prasad Rooge wrote:First i dont know what will that return.. second dont know where do you want me to try it.

in your console output...
am i getting you right...your getCell() is throwing an NPE...

System.out.print(((row.getCell(i, Row.CREATE_NULL_AS_BLANK).getStringCellValue()) == null) ? "" : (row.getCell(i, Row.CREATE_NULL_AS_BLANK).getStringCellValue()));

it just checks for the null you are getting in form of blanks. if null then print "" otherwise get the value..

[Prasad Rooge]

Ya getCell() is throwing an NPE... but even before i check for that condition it is throwing exception.. may be there is some other way to proceed..

[Stuart A. Burkett]

Prasad Rooge wrote:My mistake, getCell() method is returning NPE... well then how I'm i supposed to get further rows even if there is an empty row in the middle.

Am I missing something ? Why don't you just check if row is null before you use it

Row row= sheet.getRow(i);  
if (row != null)
    System.out.println(row.getCell(0).getStringCellValue());  
else
    System.out.println("Empty row");

[Prasad Rooge]

You got that right... damn its so simple

[Ankit Dan]

Prasad Rooge wrote: You got that right... damn its so simple