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 to install OpenPyXL. Python does not come with OpenPyXL, so you’ll have to install it.
pip install openpyxl
Post installing the OpenPyXl, let's start to read an excel file from the system. For example, purpose, I will be using the excel sheet data in the 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 an excel file using an 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()
I am Pavankumar, Having 8.5 years of experience currently working in Video/Live Analytics project.