How To Read & Update Excel File Using Python

in this tutorial, I will create a python script that will read excel file data and modified them, and update the excel file. I am using python 3.7 and some colors libs to display logs colorful.

I have created a python script that updates the excel sheet based on some input parameters, I just need to read all rows and column values and update accordingly.

You can also checkout other python excel tutorials:

There are the following functionality will achieve in this tutorial –

Read excel file using an absolute path.
File the column index based on excel column heading
Iterate on all rows
Get and Update column field value
Save the excel file

Read and Update Microsoft Excel File In Python

We will create a “sample.xlsx” excel file that will have the following data –
employee.xlsx

Name         age     Salary
Roji          32      1234
Adam          34      2134

We will update grade column value A or B based on salary filed value, Update the grade column value if the salary column value is greater > 1500.

How To Read Excel File in Python

We will create emp.py file and add the below code into this file, I am using some python packages that will install using pip command.

pip install colorama
pip install openpyxl

The colorama package is optional, that only used to display logs in colorful format. The openpyxl mandatory required package.

Let’s import all packages into emp.py file.

import sys
from colorama import Fore, init, Back, Style
import openpyxl
import re

How To Read excel file Using openpyxl

The openpyxl package has load_workbook() method that will use to open xlsx file. There is a number of helper methods that help to read and write excel file.

path = "C:\\employee.xlsx"
wb_obj = openpyxl.load_workbook(path.strip())
# from the active attribute 
sheet_obj = wb_obj.active

We have also set the active sheet to read the data of the excel file.

How To Read Column and rows Length

We will use max_column and max_row properties of the excel file object.

# get max column count
max_column=sheet_obj.max_column
max_row=sheet_obj.max_row

How To iterate on Excel File Rows in Python

We will use range() method to iterate excel file on rows length, skipped the first row which has excel file header information.

for j in range(2, 5):
        salary_cell=sheet_obj.cell(row=i,column=colum_index)

How To Get and Set Excel File data

We will get row cell object and then get cell value using .value property.

//get col object 
salary_cell=sheet_obj.cell(row=i,column=2) 
//get value 
salary = salary_cell.value: 
//set value 
salary_cell.value = 2000;

The full source code :

I have consolidated all parts of the code and added them into emp.py file.

import sys
from colorama import Fore, init, Back, Style
import openpyxl
import re

init(convert=True)
print("\n")
path = input("Enter xls file path, ex- C:\\employee.xlsx : ")
input_col_name = input("Enter colname, ex- Endpoint : ")
try:
    print(Fore.RESET)
    #path = "C:\\employee.xlsx"
    wb_obj = openpyxl.load_workbook(path.strip())
    # from the active attribute 
    sheet_obj = wb_obj.active

    # get max column count
    max_column=sheet_obj.max_column
    max_row=sheet_obj.max_row
    for j in range(2, 5):
        salary_cell=sheet_obj.cell(row=i,column=2)
        if salary_cell.value &gt; 1500:
            salary_cell.value =  salary_cell.value+500

    wb_obj.save()
except Exception as e:
    print(e)
    print (Fore.RED + "Error : The file does not found")
print(Fore.GREEN + "###################### Successfully! Excel file has been read/write. ##############################")