Python for Testers #44 - How to Read Excel File in Python | openpyxl

In this Python for Testers Tutorial we will learn how to read excel file in Python using openpyxl library in Python. Reading the contents of excel file is required in many scenarios of coding and test automation. We can easily read data from XLSX file using openpyxl library.

openpyxl is a Python library to read/write Excel 2010 xlsx/xlsm/xltx/xltm files.

Help me in spreading the knowledge, please hit LIKE, SHARE, and SUBSCRIBE for the latest tutorials. More tutorial playlists below:

🔶 ENROL IN MANY FREE TRAININGS ON RCV ACADEMY PORTAL 🔶

🔶 FOLLOW US ON TWITTER 🔶

🔶 LIKE US ON FACEBOOK 🔶

🔶 OUR TUTORIAL WEBSITES 🔶

🔶 GET MY TRAININGS ON UDEMY 🔶

#PythonForTesters #PythonTutorial #PythonForBeginners #Python #SeleniumPythonTutorial #TestAutomation #RcvAcademy #SoftwareTestingMentor

Join this channel to get access to perks:

[Katimoy]

Thank you sir for this great tutorial.🙂

[ravibtps]

Thanks for such a nice video. I have a query How we can close the sheet and end the process?

[piyushvadhel]

In my case when I enter dot it does not show me options i.e active etc.

[azeemmohammed]

Kindly clear my query
1.for me the Demosheet is not reading in pycharm
2.like i was getting (raise Keyerror("Worksheet{0} does not exist." ".format(key)"

[ShaikSalman]

E fixture 'mobile' not found
> available fixtures: cache, capfd, capfdbinary, caplog, capsys, capsysbinary, class_setup, doctest_namespace, monkeypatch, pytestconfig, record_property, record_testsuite_property, record_xml_attribute, recwarn, setup, tmp_path, tmp_path_factory, tmpdir, tmpdir_factory
> use 'pytest --fixtures [testpath]' for help on them.


I am getting this error while performing DDT with XLSX file. please help me fix this issue


from utilities.utils import Utils
from ddt import ddt, data, file_data, unpack

Testclass - @data(*Utils.read_data_from_excel("C:\\Users\\shaik\\PycharmProjects\\pythonSeleniumLearn\\isehatiprovider\\testdata\\testdata.xlsx", "patient"))
@unpack
def test_clientbooking(self, mobile, client_name, gender, dob, nationality, id_type, id_number, emailid, service_name, visit_reason):

utils-
def read_data_from_excel(filename, sheet):
mainlist = []
wb =
sh = wb[sheet]
row_count = sh.max_row
column_count = sh.max_column

for i in range(1, row_count + 1):
row = []
for j in range(1, column_count + 1):
cell_value = sh.cell(i, j).value
row.append(cell_value)
mainlist.append(row)
return mainlist