Test Case Code:

class Test_Login:

@pytest.mark.parametrize("argVals", readData.getData("Login", "E:\\PythonHybridFramework\\KMHybridFramework\\testResources\\TestData.xlsx"))
def test_a(self,argVals):
print(argVals)
runMode = readData.isRunnable("Login", "E:\\PythonHybridFramework\\KMHybridFramework\\testResources\\TestData.xlsx")
print(runMode)

============================= =================== =============================

ReadData sheet Code

def getData(testCaseName, xlsPath):
xls = XLSReader(xlsPath)

#readXLS is a variable created in class XLSReader into which XL file path is passed.
#
print(xls.getCellData("DataSheet", 2, 2))
dataList=[]

#Row index is initialized as Zero, until teststartrowindex, columindix i.e., 0 == test case name,
testStartRowIndex=0

while not(xls.getCellData("DataSheet", testStartRowIndex, 0)==testCaseName):
testStartRowIndex=testStartRowIndex+1
print(testStartRowIndex)

#Once test case name is identified, identifying column heading row and data starting rows
colStartRowIndex = testStartRowIndex+1
dataStartRowIndex = testStartRowIndex+2

#Identify maximum number of columns to iterate
maxRows=0
try:
while not(xls.checkEmptyCell("DataSheet", dataStartRowIndex+maxRows, 0)):
maxRows = maxRows+1
print("Total No. of Rows : "+ str(maxRows))
except Exception:
pass
print("------------------------")

#Identify maximum no of columns to iterate
maxCols=0
try:
while not(xls.checkEmptyCell("DataSheet", colStartRowIndex, maxCols)):
maxCols = maxCols+1

print("Total No. of columns : " + str(maxCols))
except Exception:
pass

for rNum in range(dataStartRowIndex, maxRows):
dataDictionary={}
for cNum in range(0, maxCols):
dataKey = xls.getCellData("DataSheet", colStartRowIndex, cNum)
dataValue=xls.getCellData("DataSheet", rNum, cNum)
dataDictionary[dataKey]=dataValue
dataList.append(dataDictionary)
return dataList

def isRunnable(testCaseName, xlsPath):

xls = XLSReader(xlsPath)
rows=xls.rowCount("TestCase")

for rNum in range(0, rows):
tName = xls.getCellDataByColName("TestCase", rNum, "TCID")
print(tName)

if(tName==testCaseName):
runMode=xls.getCellDataByColName("TestCase", rNum, "RunMode")
print(runMode)
if runMode=='Y':
return True
else:
return False
============================= =================== =============================

Console Text:

============================= test session starts =============================
platform win32 -- Python 3.8.4, pytest-5.4.3, py-1.9.0, pluggy-0.13.1 -- C:\Users\kpolireddy.NEXTSPHERE\AppData\Local\Programs\Python\Python38-32\python.exe
cachedir: .pytest_cache
rootdir: E:\PythonHybridFramework\KMHybridFramework
plugins: allure-pytest-2.8.16
collecting ... collected 2 items

testcases/test_a.py::Test_Login::test_a[argVals0] PASSED [ 50%]
testcases/test_a.py::Test_Login::test_a[argVals1] PASSED [100%]

============================== 2 passed in 0.25s ==============================

============================= =================== =============================

Reading Data Sheet Code:

class XLSReader:

#Constructor to read data
def __init__(self,path):

#Assigning path
self.path=path
#Pass path as parameter to method xlrd.open_workbook() method
self.readXLS = xlrd.open_workbook(path)

def getCellData(self,sheetname,rowNum,colNum):
#To get the sheet by name
sheet = self.readXLS.sheet_by_name(sheetname)
return sheet.cell_value(rowNum,colNum)

def getCellDataByColName(self,sheetname,rowNum, colName):
sheet = self.readXLS.sheet_by_name(sheetname)
for cNum in range(0, sheet.ncols):
extractedColName = sheet.cell_value(0, cNum)
if(extractedColName == colName):
cellData = sheet.cell_value(rowNum, cNum)
if(cellData!=''):
return cellData
else:
return ''



# Method to identify empty cells
def checkEmptyCell(self,sheetname,rowNum,colNum):
sheet = self.readXLS.sheet_by_name(sheetname)
cellType=sheet.cell_type(rowNum,colNum)
if(cellType==xlrd.XL_CELL_EMPTY):
return True
else:
return False

#Method to identify number of rows with data in testcase sheet
def rowCount(self, sheetname):
sheet = self.readXLS.sheet_by_name(sheetname)
return sheet.nrows

#Method to identify number of columns with data in testcase sheet
def colCount(self,sheetname):
sheet = self.readXLS.sheet_by_name(sheetname)
return sheet.ncols