public class ExcelUtils {
private static XSSFSheet ExcelWSheet;
private static XSSFWorkbook ExcelWBook;
private static XSSFCell Excelcell;
private static XSSFRow Excelrow;
// This method is to set the File path and to open the Excel file, Pass Excel
// Path and Sheetname as Arguments to this method
public static void setExcelFile(String Path, String SheetName) throws Exception {
try {
// Open the Excel file
FileInputStream ExcelFile = new FileInputStream(Path);
// Access the required test data sheet
ExcelWBook = new XSSFWorkbook(ExcelFile);
ExcelWSheet = ExcelWBook.getSheet(SheetName);
} catch (Exception e) {
throw (e);
}
}
// This method is to read the test data from the Excel cell, in this we are
// passing parameters as Row num and Col num
public static String getCellData(int RowNum, int ColNum) throws Exception {
try {
Excelcell = ExcelWSheet.getRow(RowNum).getCell(ColNum);
String CellData = Excelcell.getStringCellValue();
return CellData;
} catch (Exception e) {
return "";
}
}
// This method is to write in the Excel cell, Row num and Col num are the
// parameters
@SuppressWarnings("static-access")
public static void setCellData(String Result, int RowNum, int ColNum) throws Exception {
try {
Excelrow = ExcelWSheet.getRow(RowNum);
Excelcell = Excelrow.getCell(ColNum, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);
if (Excelcell == null) {
Excelcell = Excelrow.createCell(ColNum);
Excelcell.setCellValue(Result);
} else {
Excelcell.setCellValue(Result);
}
// Constant variables Test Data path and Test Data file name
FileOutputStream fileOut = new FileOutputStream(Constant.Path_TestData + Constant.File_TestData);
ExcelWBook.write(fileOut);
fileOut.flush();
fileOut.close();
} catch (Exception e) {
throw (e);
}
}
public static int getRowContains(String sTestCaseName, int colNum) throws Exception {
int i;
try {
int rowCount = ExcelUtils.getRowUsed();
for (i = 0; i < rowCount; i++) {
if (ExcelUtils.getCellData(i, colNum).equalsIgnoreCase(sTestCaseName)) {
break;
}
}
return i;
} catch (Exception e) {
throw (e);
}
}
public static int getRowUsed() throws Exception {
try {
int RowCount = ExcelWSheet.getLastRowNum();
return RowCount;
} catch (Exception e) {
throw (e);
}
}
}