First, let’s go over some basic definitions:
An Excel spreadsheet document is called a workbook. A single workbook is saved in a file with the .xlsx extension.
Each workbook can contain multiple sheets (also called worksheets). The sheet the user is currently viewing (or last viewed before closing Excel) is called the active sheet.
Each sheet has columns (addressed by letters starting at A) and rows (addressed by numbers starting at 1). A box at a particular column and row is called a cell.
Each cell can contain a number or text value. The grid of cells with data makes up a sheet.
OpenPyXL is a library used to read and write Excel 2010 xlsx/xlsm/xltx/xltm files. This is the library we will be using in this tutorial to work with Excel documents.
The first thing we need to do in order to make use of this library is install OpenPyXL. Python does not come with OpenPyXL, so you’ll have to install it.
pip install openpyxl
Post installing the OpenPyXl, lets start to read an excel file from the system. For example purpose I will be using the excel sheet data in below image
import unittest from openpyxl import load_workbook class Test(unittest.TestCase): def test_read_excel_file(self): # set file path filepath="excel-example.xlsx" # load excel-example.xlsx wb=load_workbook(filepath) # activate demo.xlsx sheet=wb.active # get b1 cell value b1=sheet['B1'] print("b1 --> ", b1.value) # get b2 cell value b2=sheet['B2'] print("b2 --> ", b2.value) # get b3 cell value b3=sheet.cell(row=3,column=2) print("b3 --> ", b3.value) if __name__ == "__main__": unittest.main()
We can retrieve fetch the values present in the specific column using openpyxl in python.
import unittest from openpyxl import load_workbook, cell class Test(unittest.TestCase): def test_read_excel_column_file(self): # set file path filepath="excel-example.xlsx" wb = load_workbook(filepath) sheet = wb["Sheet1"] row_count= sheet.max_row print("Total rows : ", row_count) for cell in sheet['C']: print(cell.value) if __name__ == "__main__": unittest.main()
We can create and excel file using openpyxl module in python.
import unittest from openpyxl import load_workbook, cell import openpyxl class Test(unittest.TestCase): def test_read_excel_column_file(self): wb = openpyxl.Workbook() sheet = wb.create_sheet("DummySheet", 1) sheet["A1"].value = "This is sample writing" wb.save("test-excel.xlsx") if __name__ == "__main__": unittest.main()
Article is written by Pavan (a) KarthiQ. Well, I am serving notice period in an MNC, Bangalore. I thought to enrich every person knowledge a little, I always have a feeling, when we teach something, we will learn more than what you know.
Knowledge is the only thing that doubles when you spend it.
I have also created the reporter for Protractor Jasmine. Use for your projects without any hesitation