871
openpyxl is a Python library to read and write Excel 2010 xlsx/xlsm/xltx/xltm files.
In this article we look at reading data in an excel worksheet using python
Installation
pip install openpyxl
Examples
This is the sample data in the spreadsheet
Country | Capital | Population |
Germany | Berlin | 83190556 |
France | Paris | 67413000 |
Spain | Madrid | 47450795 |
Italy | Rome | 60317116 |
Portugal | Lisbon | 10344802 |
Reading an individual cell
import openpyxl workbook = openpyxl.load_workbook('test3.xlsx') sheet = workbook.active x1 = sheet['A1'] x2 = sheet['A2'] #using cell() function x3 = sheet.cell(row=3, column=1) print("The first cell value:",x1.value) print("The second cell value:",x2.value) print("The third cell value:",x3.value)
This displayed the following
>>> %Run openpyxlreaddata.py The first cell value: Germany The second cell value: France The third cell value: Spain
total number of rows and columns
# import openpyxl module import openpyxl # to open the workbook # workbook object is created workbook = openpyxl.load_workbook('test3.xlsx') sheet = workbook.active # print the total number of rows print(sheet.max_row) # print total number of column print(sheet.max_column)
This displayed the following
>>> %Run openpyxlrowcolumn.py 6 3
Display column name
# importing openpyxl module import openpyxl # to open the workbook # workbook object is created workbook = openpyxl.load_workbook('test3.xlsx') sheet = workbook.active max_col = sheet.max_column # Loop will print all columns name for i in range(1, max_col + 1): cell = sheet.cell(row = 1, column = i) print(cell.value)
This displayed the following
>>> %Run openpyxlcolumn.py Country Capital Population
Display column 1
# importing openpyxl module import openpyxl # workbook object is created workbook = openpyxl.load_workbook('test3.xlsx') sheet = workbook.active maxrows = sheet.max_row # print all values of first column for i in range(1, maxrows + 1): cell = sheet.cell(row = i, column = 1) print(cell.value)
This displayed the following
>>> %Run openpyxlcolumn1.py Country Germany France Spain Italy Portugal
Display a row
# importing openpyxl module import openpyxl # workbook object is created workbook = openpyxl.load_workbook('test3.xlsx') sheet = workbook.active maxcols = sheet.max_column # Will print a particular row value for i in range(1, maxcols + 1): cell = sheet.cell(row = 2, column = i) print(cell.value, end = " ")
This displayed the following
>>> %Run openpyxlrows.py Germany Berlin 83190556