Monday, January 22, 2018

Reading from XLSx simplified with TestNG-Multi columns

package com.main;

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

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.testng.annotations.DataProvider;
import org.testng.annotations.Test;

public class ExcelReading {

static File strPath = new File("Mapping_State_Sites.xlsx");
static String strSheetName = "Sheet1";
static XSSFSheet sheet;
static XSSFSheet sheetOther;
static XSSFWorkbook workbook;
static Object[][] rawData;
static XSSFCell cell;
static XSSFRow row;

@Test(dataProvider = "data")
public void test(String agency, String code, String city, String metro) {

System.out.println(agency + ": " + code + ": " + city + ": " + metro);
}

@DataProvider(name = "data")
public Object[][] getTableObject() throws IOException {

sheetOther = DataSheet(strPath, strSheetName);
int totalRows = sheetOther.getLastRowNum();
rawData = new Object[totalRows][4];
for (int i = 0; i < totalRows; i++) {

for (int j = 0; j <= 3; j++) {
// System.out.println("TotalRows: " + "[" + i + "]" + " " + "[" + j +
// "]");
rawData[i][j] = getCellData(strSheetName, (i + 1), j);
// System.out.println(getCellData(strSheetName, (i + 1), j));
}

}
return rawData;
}

public static XSSFSheet DataSheet(File strPath2, String SheetName) {
File file = strPath2;
try {
FileInputStream fis = new FileInputStream(file);
workbook = new XSSFWorkbook(fis);
sheet = workbook.getSheet(SheetName);
fis.close();
} catch (Exception e) {
e.printStackTrace();
}

return sheet;
}

@SuppressWarnings("deprecation")
public static String getCellData(String Sheet, int row1, int col) {

try {

int index = workbook.getSheetIndex(Sheet);

sheet = workbook.getSheetAt(index);
row = sheet.getRow(row1);
if (row == null)
  return "";

cell = row.getCell(col);
if (cell == null)
  return "";

switch (cell.getCellType()) {
case Cell.CELL_TYPE_STRING:
return cell.getStringCellValue();

case Cell.CELL_TYPE_BOOLEAN:
return String.valueOf(cell.getBooleanCellValue());

case Cell.CELL_TYPE_BLANK:
return "";

case Cell.CELL_TYPE_ERROR:
return cell.getStringCellValue();

case Cell.CELL_TYPE_NUMERIC:
return String.valueOf(cell.getNumericCellValue());

default:
return "Cell not found";

}
} catch (Exception e) {
e.printStackTrace();
return "row " + row + " or column " + col + " does not exist in xls";
}

}

}

No comments:

Post a Comment