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
– Saurabh Gaur, Commented Jul 27, 2016 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
– OhAye, Commented Jul 27, 2016 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
– OhAye, Commented Jul 27, 2016 at 10:25
@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
– OhAye, Commented Jul 27, 2016 at 11:04

Bhuvanwaitz · Accepted Answer · 2016-07-27 11:33:42Z

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, 2016 at 11:33

answered Jul 27, 2016 at 11:08

Bhuvanwaitz

587 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

OhAye Over a year ago

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 Over a year ago

Also would it not be column and then row? Looking into the getCell method it shows - public Cell getCell(int column, int row);

Bhuvanwaitz Over a year ago

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

OhAye Over a year ago

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 Over a year ago

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!

JasonQiang · Accepted Answer · 2016-07-27 10:58:30Z

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, 2016 at 10:58

answered Jul 27, 2016 at 10:53

JasonQiang

12 bronze badges

3 Comments

OhAye Over a year ago

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 Over a year ago

Also why do you not have to catch the Biff exception?

JasonQiang Over a year ago

I modify my code and catch BiffException and IOException, but I also get the correct answer, maybe my xls file is different from yours?

Collectives™ on Stack Overflow

java.lang.ArrayIndexOutOfBoundsException reading from excel file

2 Answers 2

5 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related