How to use openpyxl: A python module for excel files

pip install openpyxl

import openpyxl 

# loading workbookfrom openpyxl import load_workbook# NOTE: loading the excel we needwb = load_workbook(filename='sampleData.xlsx')print(wb)  

# Working with Sheetsfrom openpyxl import load_workbook# NOTE: loading the excel we needwb = load_workbook(filename='sampleData.xlsx')# NOTE: get the sheetnames from the excel we readprint(wb.sheetnames)  # OUTPUT: ['Sheet1', 'Sheet2']# NOTE: shows which sheet is currently activeprint(wb.active)  # OUTPUT: <Worksheet "Sheet1"># NOTE: we can assign which sheet to be activated, it starts from left to right from the index 0,1 so on...wb.active = 0print(wb.active)  # OUTPUT: <Worksheet "Sheet1">wb.active = 1print(wb.active)  # OUTPUT: <Worksheet "Sheet2"># NOTE: above we saw we can access via index location, now lets access the sheet with the sheet namesheet = wb['Sheet2']print(sheet)  # OUTPUT: <Worksheet "Sheet2">print(sheet.title)  # OUTPUT: Sheet2

# Retrieving Cell Valuesfrom openpyxl import load_workbook# NOTE: loading the excel we needwb = load_workbook(filename='sampleData.xlsx')sheet = wb['Sheet1']# NOTE: from active sheet we are trying to fetch the value from cell B3cell_coordinates = sheet['B3']# NOTE: fetch value, row & column for the cell coordinatesprint(cell_coordinates.value)  # OUTPUT: Jojoprint(cell_coordinates.row)  # OUTPUT: 3print(cell_coordinates.column)  # OUTPUT: 2print(cell_coordinates.coordinate)  # OUTPUT: B3# NOTE: What if we fetch the empty cell value?print(sheet['B9'].value)  # OUTPUT: None# NOTE: Return Value using cellprint(sheet.cell(row=2, column=2).value)  # OUTPUT: Shijo

# Retrieving Multiple Valuesfrom openpyxl import load_workbook# NOTE: loading the excel we needwb = load_workbook(filename='sampleData.xlsx')sheet = wb['Sheet2']# NOTE: fetches all the 'A' colum that has data# OUTPUT: (<Cell 'Sheet2'.A1>, <Cell 'Sheet2'.A2>, <Cell 'Sheet2'.A3>, <Cell 'Sheet2'.A4>, <Cell 'Sheet2'.A5>, <Cell 'Sheet2'.A6>)print(sheet['A'])# NOTE: fetches range of columns without indexprint(sheet['A:C'])'''OUTOUT:((<Cell 'Sheet2'.A1>, <Cell 'Sheet2'.A2>, <Cell 'Sheet2'.A3>, <Cell 'Sheet2'.A4>, <Cell 'Sheet2'.A5>, <Cell 'Sheet2'.A6>), (<Cell 'Sheet2'.B1>, <Cell 'Sheet2'.B2>, <Cell 'Sheet2'.B3>, <Cell 'Sheet2'.B4>, <Cell 'Sheet2'.B5>, <Cell 'Sheet2'.B6>), (<Cell 'Sheet2'.C1>, <Cell 'Sheet2'.C2>, <Cell 'Sheet2'.C3>, <Cell 'Sheet2'.C4>, <Cell 'Sheet2'.C5>, <Cell 'Sheet2'.C6>))'''# NOTE: fetches range of columns with indexprint(sheet['1:3'])'''OUTPUT:((<Cell 'Sheet2'.A1>, <Cell 'Sheet2'.B1>, <Cell 'Sheet2'.C1>), (<Cell 'Sheet2'.A2>, <Cell 'Sheet2'.B2>, <Cell 'Sheet2'.C2>), (<Cell 'Sheet2'.A3>, <Cell 'Sheet2'.B3>, <Cell 'Sheet2'.C3>))'''# fetch row & column objectsfor row in sheet.rows:    print(row)'''OUTPUT:(<Cell 'Sheet2'.A1>, <Cell 'Sheet2'.B1>, <Cell 'Sheet2'.C1>)(<Cell 'Sheet2'.A2>, <Cell 'Sheet2'.B2>, <Cell 'Sheet2'.C2>)(<Cell 'Sheet2'.A3>, <Cell 'Sheet2'.B3>, <Cell 'Sheet2'.C3>)(<Cell 'Sheet2'.A4>, <Cell 'Sheet2'.B4>, <Cell 'Sheet2'.C4>)(<Cell 'Sheet2'.A5>, <Cell 'Sheet2'.B5>, <Cell 'Sheet2'.C5>)(<Cell 'Sheet2'.A6>, <Cell 'Sheet2'.B6>, <Cell 'Sheet2'.C6>)'''for col in sheet.columns:    print(col)'''OUTPUT:(<Cell 'Sheet2'.A1>, <Cell 'Sheet2'.A2>, <Cell 'Sheet2'.A3>, <Cell 'Sheet2'.A4>, <Cell 'Sheet2'.A5>, <Cell 'Sheet2'.A6>)(<Cell 'Sheet2'.B1>, <Cell 'Sheet2'.B2>, <Cell 'Sheet2'.B3>, <Cell 'Sheet2'.B4>, <Cell 'Sheet2'.B5>, <Cell 'Sheet2'.B6>)(<Cell 'Sheet2'.C1>, <Cell 'Sheet2'.C2>, <Cell 'Sheet2'.C3>, <Cell 'Sheet2'.C4>, <Cell 'Sheet2'.C5>, <Cell 'Sheet2'.C6>)'''# Show values onlyfor row in sheet.iter_rows(values_only=True):    print(row)'''OUTPUT:('id', 'fruit', 'quantity')(1, 'Apple', 2)(2, 'Orange', 5)(3, 'Pineapple', 3)(4, 'Banana', 6)(5, 'Mango', 1)'''for col in sheet.iter_cols(values_only=True):    print(col)'''OUTPUT:('id', 1, 2, 3, 4, 5)('fruit', 'Apple', 'Orange', 'Pineapple', 'Banana', 'Mango')('quantity', 2, 5, 3, 6, 1)'''

# Converting data into Python structuresimport jsonfrom openpyxl import load_workbook# NOTE: loading the excel we needwb = load_workbook(filename='sampleData.xlsx')sheet = wb['Sheet2']#  empty dictionary to keep values from excelbooks = {}for row in sheet.iter_rows(min_row=2, min_col=1, values_only=True):    book_id = row[0]    book = {        'Fruit': row[1],        'Qty': row[2]    }    books[book_id] = bookprint(json.dumps(books, indent=3))'''OUTPUT:{   "1": {      "Fruit": "Apple",      "Qty": 2   },   "2": {      "Fruit": "Orange",      "Qty": 5   },   "3": {      "Fruit": "Pineapple",      "Qty": 3   },   "4": {      "Fruit": "Banana",      "Qty": 6   },   "5": {      "Fruit": "Mango",      "Qty": 1   }}'''