java.lang.ArrayIndexOutOfBoundsException reading from excel file

Question

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.

Why do you think this causes due to selenium??? what is the role of selenium here?? — Saurabh Gaur, Jul 27 '16 at 10:22
I think its first column then row from what I read in another solution about the same issue public Cell getCell(int column, int row) — OhAye, Jul 27 '16 at 10:23
Well the only link with selenium is when I access the page and pass the variable in after reading the cell so the issue perhaps is not with Selenium, i'll remove Selenium from the title — OhAye, Jul 27 '16 at 10:25
I'm using jxl import jxl.Cell; import jxl.Sheet; import jxl.Workbook; import jxl.read.biff.BiffException; — OhAye, Jul 27 '16 at 10:27
are you sure that the the column is 2? I think that it's 1 because it start from 0, just like rows — Gianni B., Jul 27 '16 at 10:59
@GianniB.so 0,1 picks up 1000483 so I would assume 0,2 would pick up 1000484. I believe 2 is the row and 0 is the column. — OhAye, Jul 27 '16 at 11:04

Bhuvanwaitz · Accepted Answer · 2016-07-27T11:33:42.360

1

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

edited Jul 27 '16 at 11:33

answered Jul 27 '16 at 11:08

Bhuvanwaitz

58
7

Thanks for your code, unfortunately that still gives me an exception. However when I change it to (rows, 1) it gives me 1000483. The print out for the rows when it fails gives me 2. – OhAye Jul 27 '16 at 11:18
Also would it not be column and then row? Looking into the getCell method it shows - public Cell getCell(int column, int row); – OhAye Jul 27 '16 at 11:21
I have modified the loop to run as column then row. But this depend how we need the data. I guess in your case it should return username and password so it should be column then row – Bhuvanwaitz Jul 27 '16 at 11:35
Thanks again :) - So you're correct it works when Row = 1 and column = 0. When I change Row to be 2 I dont enter the for loop as .getRows is always 2. I think my issue is .getRows is always returning 2 and how do I get that to see more of the sheet? – OhAye Jul 27 '16 at 11:39
So it looks like I was just being silly, I've included more data in the spreadsheet. You've solved my problem so I'll mark it as solved. Thanks for your help! – OhAye Jul 27 '16 at 11:44

JasonQiang · Answer 2 · 2016-07-27T10:58:30.777

0

I use the same data with you, and I could get 1000484 value through my code.

Here is my code :

package com.jason.xls;

import java.io.File;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;

public class XlsParser {

    public static void main(String[] args) {
        final String path = "/home/coder/filepath.xls";
        System.out.println(readUserNameFromXls(path));
    }

    public static String readUserNameFromXls(final String path) {
        File file = new File(path);
        try {
            Workbook wb = Workbook.getWorkbook(file);
            Sheet sheet = wb.getSheet(0);
            Cell a2 = sheet.getCell(0, 2);
            return a2.getContents().trim();
        } catch (Exception e) {
            return null;
        }
      }
 }

I download jxl.jar from jxl.jar download here

My code result is : Code Result Image

edited Jul 27 '16 at 10:58

answered Jul 27 '16 at 10:53

JasonQiang

1
2

Thats strange, the only differences I can see is your method returns null and mine returns a String but I would still expect your code to complain about the outofbounds exception. Unless there is something different with your Excel? – OhAye Jul 27 '16 at 10:59
Also why do you not have to catch the Biff exception? – OhAye Jul 27 '16 at 11:02
I modify my code and catch BiffException and IOException, but I also get the correct answer, maybe my xls file is different from yours? – JasonQiang Jul 27 '16 at 11:27

java.lang.ArrayIndexOutOfBoundsException reading from excel file

2 Answers2