How to check empty Excel cell using while loop?

User_AYF65

Hi,

I have written a java program to read excel sheets. Its reading a cell which I want. I am using a while loop. My while loop should stop when it encounters a null value. However its not doing this. Its crashing when number of rows are beyond 100. I think my sheet has 100 rows.

Some body please guide me why my loop doesn’t stop when it encounters a null value. My code is:

import java.io.*;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

import javax.swing.*;

/**

*

* @author HP

*/

public class ReadExcelSheetMain2 {

    /**

     * @param args the command line arguments

     */

    public void readExcel() throws BiffException, IOException {

                                String FilePath = "D:\\Marks-2A-SE-OOP-2015.xls";

                                FileInputStream fs = new FileInputStream(FilePath);

                                Workbook wb = Workbook.getWorkbook(fs);

                                // TO get the access to the sheet

                                Sheet sh = wb.getSheet("Sheet1");

               

                                // To get the number of rows present in sheet

                                int totalNoOfRows = sh.getRows();

JOptionPane.showMessageDialog(null, "totalNoOfRows = " + totalNoOfRows);

                                // To get the number of columns present in sheet

                                int totalNoOfCols = sh.getColumns();

JOptionPane.showMessageDialog(null, "totalNoOfCols = " + totalNoOfCols);

                int row=8;

                int col=1;

                String rollNo = sh.getCell(col, row).getContents();

                while( rollNo != null) {

System.out.print(rollNo+ "\n");

                   rollNo = sh.getCell(col, row).getContents();

                   row++;

                }

System.out.print(sh.getCell(col, row).getContents() + "\t");

                                /*for (int row = 0; row < totalNoOfRows; row++) {

                                                for (int col = 0; col < totalNoOfCols; col++) {

                                                                System.out.print(sh.getCell(col, row).getContents() + "\t");

                                                }

                                                System.out.println();

                                }*/

    }

    public static void main(String[] args)throws BiffException, IOException {

        // TODO code application logic here

        ReadExcelSheetMain2 obj = new ReadExcelSheetMain2();

        obj.readExcel();

    }

   

}

This is my output:

Warning:  Polygon Object on sheet "Sheet1" not supported - omitting

Warning:  Polygon Object on sheet "Sheet1" not supported - omitting

2015-SE-001

2015-SE-001

2015-SE-002

2015-SE-003

2015-SE-004

2015-SE-005

2015-SE-006

2015-SE-007

2015-SE-008

2015-SE-009

2015-SE-010

2015-SE-011

2015-SE-012

2015-SE-013

2015-SE-014

2015-SE-015

2015-SE-016

2015-SE-017

2015-SE-018

2015-SE-019

2015-SE-020

2015-SE-021

2015-SE-022

2015-SE-023

2015-SE-024

2015-SE-025

2015-SE-026

2015-SE-027

2015-SE-028

2015-SE-029

2015-SE-030

2015-SE-031

2015-SE-032

2015-SE-033

2015-SE-034

2015-SE-035

2015-SE-036

2015-SE-037

2015-SE-038

2015-SE-039

2015-SE-040

2015-SE-041

2015-SE-042

2015-SE-043

2015-SE-044

2015-SE-045

2015-SE-046

2015-SE-047

2015-SE-048

2015-SE-049

2015-SE-051

2015-SE-130

2015-SE-138

Exception in thread "main" java.lang.ArrayIndexOutOfBoundsException: 100

        at jxl.read.biff.SheetImpl.getCell(SheetImpl.java:356)

        at ReadExcelSheetMain2.readExcel(ReadExcelSheetMain2.java:41)

        at ReadExcelSheetMain2.main(ReadExcelSheetMain2.java:56)

Unknown

Its crashing when number of rows are beyond 100.

No - it is crashing when  you try to read rows that don't exist and are beyond the last row

 I think my sheet has 100 rows.

You 'think'? Why are you 'thinking'?

                                // To get the number of rows present in sheet                                int totalNoOfRows = sh.getRows();

Doesn't that code give you the number of rows? If so there is nothing to 'think' about.

Modify your code to print out the number of rows and to only process the rows that exist

for (int i = 0; i < totalNoOfRows; i++)

Your loop should be based on the number of rows not on whether a cell is null.

Test for the null cell in the loop and just exit the loop if you get a null cell.

1. Get rid of the dialog boxes for trying to display info about what is going on.

2. Print messages to the console or, better, use logging and print them to a log file

3. Use a GUI like NetBeans so you can step through the code and see what it is doing.

1.

Unknown

Its crashing when number of rows are beyond 100.

No - it is crashing when  you try to read rows that don't exist and are beyond the last row

 I think my sheet has 100 rows.

You 'think'? Why are you 'thinking'?

                                // To get the number of rows present in sheet                                int totalNoOfRows = sh.getRows();

Doesn't that code give you the number of rows? If so there is nothing to 'think' about.

Modify your code to print out the number of rows and to only process the rows that exist

for (int i = 0; i < totalNoOfRows; i++)

Your loop should be based on the number of rows not on whether a cell is null.

Test for the null cell in the loop and just exit the loop if you get a null cell.

1. Get rid of the dialog boxes for trying to display info about what is going on.

2. Print messages to the console or, better, use logging and print them to a log file

3. Use a GUI like NetBeans so you can step through the code and see what it is doing.

1.

User_AYF65

Hi,

Thanks for your advice. I found the code for checking null string from stack overflows.

Based upon your advice i am now using for loop:

                String rollNo=null;

                for ( row = 8; row < totalNoOfRows; row++) {

                   rollNo = sh.getCell(col, row).getContents();

                   boolean isEmpty = rollNo == null || rollNo.trim().length() == 0;

                   if (isEmpty) {

                      break;

                   }

                   System.out.println("rollNo" + rollNo);

                  

                }

My output is :

Warning:  Polygon Object on sheet "Sheet1" not supported - omitting

Warning:  Polygon Object on sheet "Sheet1" not supported - omitting

rollNo2015-SE-001

rollNo2015-SE-002

rollNo2015-SE-003

rollNo2015-SE-004

rollNo2015-SE-005

rollNo2015-SE-006

rollNo2015-SE-007

rollNo2015-SE-008

rollNo2015-SE-009

rollNo2015-SE-010

rollNo2015-SE-011

rollNo2015-SE-012

rollNo2015-SE-013

rollNo2015-SE-014

rollNo2015-SE-015

rollNo2015-SE-016

rollNo2015-SE-017

rollNo2015-SE-018

rollNo2015-SE-019

rollNo2015-SE-020

rollNo2015-SE-021

rollNo2015-SE-022

rollNo2015-SE-023

rollNo2015-SE-024

rollNo2015-SE-025

rollNo2015-SE-026

rollNo2015-SE-027

rollNo2015-SE-028

rollNo2015-SE-029

rollNo2015-SE-030

rollNo2015-SE-031

rollNo2015-SE-032

rollNo2015-SE-033

rollNo2015-SE-034

rollNo2015-SE-035

rollNo2015-SE-036

rollNo2015-SE-037

rollNo2015-SE-038

rollNo2015-SE-039

rollNo2015-SE-040

rollNo2015-SE-041

rollNo2015-SE-042

rollNo2015-SE-043

rollNo2015-SE-044

rollNo2015-SE-045

rollNo2015-SE-046

rollNo2015-SE-047

rollNo2015-SE-048

rollNo2015-SE-049

rollNo2015-SE-051

rollNo2015-SE-130

rollNo2015-SE-138

>

Thanks God bless you.

Zulfi.