OhAye OhAye - 4 months ago 45
Java Question

java.lang.ArrayIndexOutOfBoundsException reading from excel file

I am trying to read the following data from an Excel sheet

Data

With the following code

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public String readUsernameFromExcel() {
File src = new File("C:/filepath.xls");

try {

Workbook wb = Workbook.getWorkbook(src);
Sheet sh1 = wb.getSheet(0);
Cell a2 = sh1.getCell(0, 2);
data1 = a2.getContents().trim();


} catch (BiffException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return data1;

}


So when I try and get the cell
0,1
I can pick up the username
1000483
just fine. But when I try to read
0,2
and I get
java.lang.ArrayIndexOutOfBoundsException: 2
.

What I'm trying to do is read data from an excel sheet return it as a
String
and then pass it in to login my application. But it seems when I try
0,2
I'm going outside of what is expected. I've tried a few things such as a for loop

for (int rows = 0; rows < sh1.getRows(); rows++) {
Sheet sh1 = wb.getSheet(0);
Cell a2 = sh1.getCell(0, 2);

}


I understand the first number is the column and the second is the row. I also understand that the code isn't able to see past
0,1
. I'm just at a loss as to how to get it to see the rest of the sheet after trying other solutions of the same problem.

Answer

sh1.getRows() returns 3. As loop starts from 0, sh1.getRows() needs to be decremented by 1 (as below). Below loop works fine and returns value properly.

import java.io.File;
import java.io.IOException;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class Excel {

    public static void main(String[] args) {
        File src = new File("c:/filepath.xls");

        try {
            String data1;
            Workbook wb = Workbook.getWorkbook(src);
            Sheet sh1 = wb.getSheet(0);


            for (int rows = 1; rows < sh1.getRows(); rows++)  {
                for (int column = 0; column <= sh1.getColumns()-1; column++) {

                    Cell a2 = sh1.getCell(column, rows);
                    data1 = a2.getContents().trim();
                    System.out.println(data1);
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        } 
    }



}

The above code works and fetches the date without error