Forum Stats

  • 3,733,670 Users
  • 2,246,802 Discussions
  • 7,856,836 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Log In Register

Categories

How to check empty Excel cell using while loop?

User_AYF65
User_AYF65 Member Posts: 135 Red Ribbon
edited August 2017 in New To Java

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)

· Share on TwitterShare on Facebook

Best Answer

  • Unknown
    Unknown
    edited August 2017 Accepted Answer 
    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.

    · Share on TwitterShare on Facebook

Answers

  • Unknown
    Unknown
    edited August 2017 Accepted Answer 
    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.

    · Share on TwitterShare on Facebook
  • User_AYF65
    User_AYF65 Member Posts: 135 Red Ribbon
    edited August 2017

    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.

    · Share on TwitterShare on Facebook
This discussion has been closed.