How about reading the row with multiple values using excel
for instance
| reference | testcase1 | testcase2 | testcase3 |
|---|---|---|---|
| Name | Sam | ram | cam |
| Age | 20 | 25 | 30 |
| sex | m | F | m |
| place | place1 | place2 | place 3 |
I have to read
testcase1.name=sam
testcase2.name=ram
testcase3.name=cam
and age
how can I do this... any help would be appreciated
This tutorial might be helpful to use: https://www.javatpoint.com/how-to-read-excel-file-in-java.
Basically, you are using the Apache POI Library to parse the file's values, generating iterators for each row, then for each cell in each row, and dealing with the data that way.
In your case, after importing the relevant Classes, the code snippet will be something like this:
//Input Data for Class
FileInputStream in = new FileInputStream(new File("<File Location>.xls(x)"));
// Create an Apache POI Workbook reading the file
HSSFWorkbook workbook = new HSSFWorkbook(in);
// Use the Workbook to make a sheet
HSSFSheet sheet = workbook.getSheetAt(0); // Replace 0 with your sheet number
List<Row> rowsList = new ArrayList<Row>();
sheet.iterator().forEachRemaining(actualList::add); // Gets a list of all the rows
int i = -1;
String[] names = new String[3];
for (Cell cell : rowsList[1]) { // Start at second row
if (i++ > 0) {
names[i] = cell.getStringCellValue();
}
}
testcase1.name = names[0];
testcase2.name = names[1];
testcase3.name = names[2];
i = -1;
int[] ages = new int[3];
for (Cell cell : rowsList[2]) { // Start at Third Row
if (i++ > 0) {
ages[i] = (int) cell.getNumericCellValue();
}
}
testcase1.name = ages[0];
testcase2.name = ages[1];
testcase3.name = ages[2];
Use apache poi library.
First you must read the Excel file in a java object
FileInputStream myFile = new FileInputStream(new File(FILE_NAME));
Workbook myWorkbook = new XSSFWorkbook(myFile);
Sheet datatypeSheet = myWorkbook.getSheetAt(0); //read the first sheet
Iterator<Row> iterator = datatypeSheet.iterator();
Then you can iterate over each row and in each row you can read each cell (aka column value)
List<TestCase> myList = new ArrayList();
int rowNum = 0;
while (iterator.hasNext()) {
Row row = iterator.next();
int columnsSize = row.getLastCellNum();
//create all objects
if(rowNum == 0) {
for (int i = 1; i <= columnsSize; i++) {
myList.add(new TestCase());
}
} else {
//initialize property in each row for all objects
for (int i = 1; i <= columnsSize; i++) {
TestCase testCase = myList.get(i-1);
int cellType = row.getCell(i).getCellType();
switch(cellType) {
case Cell.CELL_TYPE_NUMERIC:
testCase.properties.add(new Property(row.getCell(0).getStringCellValue(),
String.valueOf(row.getCell(i).getNumericCellValue()));
break;
case Cell.CELL_TYPE_STRING:
testCase.properties.add(new Property(row.getCell(0).getStringCellValue(),
row.getCell(i).getStringCellValue());
break;
}
}
}
rowNum++;
}
System.out.println(myList);
public class TestCase {
public List<Property> properties;
public TestCase(){
this.properties = new ArrayList();
}
}
public class Property {
String name;
String value;
public Property(String name, String value){
this.name = name;
this.value = value;
}
}
*Edit: For loop must have <= condition instead of <
