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";
}
}
}
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