Read data from excel sheet into 2D Arraylist

Web Testing
1

Hi there,

I need help in getting the data into 2D array from excel sheet. I am aware on how to get the data from excelsheet into 1D array. Could you please help on the same?

All I have is the below excel sheet

image
image600×371 3.78 KB

Below code works fine to fetch single column data but I would like to fetch both mentioned columns data into a 2D Array . Could you please help?

Object excelD= ExcelFactory.getExcelDataWithDefaultSheet(‘D:\Users\Test.xlsx’,
sheet1, true)
for (int i = 1; i <= tot; i++) {
if (excelData.getValue(‘Pname’, i) == ‘’) {
break
}

ExlPgm.add(excelD.getValue('Pname', i).trim())

}

2

Is this what you want?

for (int i = 1; i <= tot; i++) {
	...
	ExlPgm.add([excelD.getValue('Pname', i).trim(),
				excelD.getValue('Pref',  i).trim()  ])
}
3

@kazurayam
This code works for 1D array. This is able to get both column data into one array . I need the data in the table format into below defined array(2D)
ArrayList[][] ExlPgm= new ArrayList[numRows][];

4

This line look very strange to me. I have never seen ArrayList[][] in any Java/Groovy tutorial documents.

I would rather write to express an 2-dimensional List:

List<List<Object>> exlPgm = new ArrayList<ArrayList<Object>>()
5

@kazurayam
Thanks for the correction. But still am stuck at the same question

6

I do not see what is your question.

7

As mentioned above
“This code works for 1D array. This is able to get both column data into one array . I need the data in the table format into below defined array(2D)”

List<List> exlPgm = new ArrayList<ArrayList>()

So if I have the above 2D array defined and want to fetch the data from the excel sheet mentioned above then what is the way to do it ?

8

Make a Test Case and copy&page the following, and run it.

List<List<Object>> exlPgm = [
	["Administrative", "Payer"],
	["Advance", "Payer 1"],
	["Complex", "Line 1"]
	]

println "--------------- demo 1 ----------------------"
for (int i = 0; i < exlPgm.size(); i++) {
	List<Object> row = exlPgm.get(i)
	for (int j = 0; j < row.size(); j++) {
		println "i=${i},j=${j} \'" + row.get(j) + "\'"
	}
}

println "\n--------------- demo 2 ----------------------"
for (int i = 0; i < exlPgm.size(); i++) {
	for (int j = 0; j < exlPgm[i].size(); j++) {
		println "[${i}][${j}] \'" + exlPgm[i][j] + "\'"     // array-like syntax sugar
	}
}

println "\n--------------- demo 3 ----------------------" 
// list.each { groovy closure}
exlPgm.eachWithIndex { row, i ->
	row.eachWithIndex { cell, j ->
		println "[${i}][${j}] \'" + cell + "\'"
	}
}

You will see the following output:

--------------- demo 1 ----------------------
i=0,j=0 'Administrative'
i=0,j=1 'Payer'
i=1,j=0 'Advance'
i=1,j=1 'Payer 1'
i=2,j=0 'Complex'
i=2,j=1 'Line 1'

--------------- demo 2 ----------------------
[0][0] 'Administrative'
[0][1] 'Payer'
[1][0] 'Advance'
[1][1] 'Payer 1'
[2][0] 'Complex'
[2][1] 'Line 1'

--------------- demo 3 ----------------------
[0][0] 'Administrative'
[0][1] 'Payer'
[1][0] 'Advance'
[1][1] 'Payer 1'
[2][0] 'Complex'
[2][1] 'Line 1'

I think that the demo 3 is the most Groovy-ish way of iterating over all cells in the 2D list.

9

@kazurayam Thank you it helped