I want to retrieve the data from excel using filter criteria if filter matches then data should be iterated in python?

Question

I want to retrieve the data from excel using filter criteria if filter matches then data should be iterated in python. Below is the code working without filter if add filter it is not working can any one please help me out.

Can any one provide me solution using other framework in python i need to get row values using filter value.

Here am using openpyxl

from openpyxl import Workbook, load_workbook

class Util:

    def read_data_from_excel(file_name, sheet, filter):
        datalist = []
        wb = load_workbook(filename=file_name)
        sh = wb[sheet]
        row_ct = sh.max_row
        col_ct = sh.max_column

        for i in range(2, row_ct + 1):
            row = []
            for j in range(1, col_ct + 1):
                if (sh.cell(row=i, column=j).value == filter): # if i remove this line it is working
                    row.append(sh.cell(row=i, column=j).value)
            datalist.append(row)
        return datalist

#calling like this
from base_testcase import BaseTestCase
from ddt import ddt, data, file_data, unpack
from util.util import Util

@ddt
class GithubTest(BaseTestCase):

    @data(*Util.read_data_from_excel("./test_data.xlsx", "LoginData", "github")) # github is my filter
    @unpack
    def test_notifications(self, username, password):
        self.login(username, password)
        self.check_notification()

class LoginPage(object):
    url = 'http://github.com'
    sign_in_link = "a:contains('Sign in')"
    username = "[name='login']"
    password = "[name='password']"
    sign_in = "[value='Sign in']"

from seleniumbase import BaseCase
from login_page import LoginPage

class BaseTestCase(BaseCase):
   def login(self, username, password):
        self.open(LoginPage.url)
        self.click(LoginPage.sign_in_link)
        self.type(LoginPage.username, username)
        self.type(LoginPage.password, password)
        self.click(LoginPage.sign_in)

# My Excel data
username | password | filter_name | #--> from this column filter should be taken
sample   | tester   | github      |
test     | user     | tester      |

do you want `row` to contain the entire row or just the cell that includes `filter`? — Ohad Sharet, Aug 07 '22 at 14:06
@OhadSharet if filter matches then it should get all values in row — spectator, Aug 07 '22 at 14:08

score 0 · Answer 1 · edited Aug 29 '22 at 18:17

0

I changed the filter to take the entire row if one of the columns in that row has a value that equals to filter. I tried it on an XML file of my own and it works.

This should do the trick:

from openpyxl import Workbook, load_workbook

def read_data_from_excel(file_name, sheet, filter):
    datalist = []
    wb = load_workbook(filename=file_name)
    sh = wb[sheet]
    row_ct = sh.max_row
    col_ct = sh.max_column

    for i in range(2, row_ct + 1):
        row = []
        for j in range(1, col_ct + 1):
            if (sh.cell(row=i, column=j).value == filter): # if i remove this line it is working
                datalist.append([col.value for col in  sh[i]])
                break
        datalist.append(row)
    return datalist

edited Aug 29 '22 at 18:17

halfer

19,824
17
99
186

answered Aug 07 '22 at 14:15

Ohad Sharet

1,097
9
15

am getting this error: `TypeError: test_notifications() takes 3 positional arguments but 4 were given` it should run 1 time but it is running 3 times and giving this error `FAILED github_test.py::GithubTest::test_notifications_2___ - TypeError: test_notifications() missing 3 required positional arguments: 'username', 'password', and 'filter'` – spectator Aug 07 '22 at 14:27
I think i should check the condition before looping excel data – spectator Aug 07 '22 at 14:33
I think the "problem" is the way that you call the function , unfortunately, I am not familiar with `@data` but it seems that when you call your function using `@data` it sends an additional argument – Ohad Sharet Aug 07 '22 at 14:38

score 0 · Accepted Answer · answered Aug 09 '22 at 13:20

I have achieve this using pandas in python its very simple

def read_excel_data(file_path, sheet, filter):
        df = pd.read_excel(file_path, sheet)
        return df[df['filter'] == filter].to_dict('records')

# Here once you got the records then you can iterate and get value you want
login_data = read_excel_data('path to file', sheet_name, filter_varable)

for data in login_data:
    print(data['username'])

I want to retrieve the data from excel using filter criteria if filter matches then data should be iterated in python?

2 Answers2