Discussions
Categories
- 385.5K All Categories
- 4.9K Data
- 2.5K Big Data Appliance
- 2.4K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
How to check empty Excel cell using while loop?

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)
Best 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.
Answers
-
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.
-
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.