How to replace a word in excel using Python?

Excel is a very useful tool where we can have the data in the format of rows and columns. We can say that before the database comes into existence, excel played an important role in the storage of data. Nowadays using Excel input, many batch processing is getting done. There may be the requirement of replacing text in Excel sheet is always there as excel always holds important data. In this article, let us see how to replace a word in Excel using Python

Methods and approaches used

Here let us see via xlwt & xlrd packages and openpyxl packages for replacing a word in excel using Python

Method 1 : xlwt & xlrd

To install these packages type the below command in the terminal.

# for writing into excel
pip install xlwt

# for reading
pip install xlrd 

The Below code has two excel workbook. One for reading and getting the text. Other for writing the replaced text. We can do “n” number of replacements. The column should exactly have the specified searched text which needs to be replaced and once found they are replaced and written to a new workbook. 

Example:

Excel file used – 

Python3

import xlwt
import xlrd

# Excel file can be in your local drive
# and if not, specify the exact path
sampleWorkbook = xlrd.open_workbook('sampleexcel.xlsx')
originalSheet = sampleWorkbook.sheet_by_name('Test')

newWorkbookForTextReplacement = xlwt.Workbook()
newsheetForTextReplacement = newWorkbookForTextReplacement.add_sheet('Test')

replacementTextKeyPairs = {'Apple': 'Kiwi',
                           'Oranges': 'Lemons',
                           'Grapes': 'Papayas'}

# iterate over the rows of your sheet
# ncols - number of columns in the
# selected sheet, here it is for 'Test' sheet
# nrows - number of rows in the selected
# sheet, here it is for 'Test' sheet
for i in range(originalSheet.nrows):
    print(i)

    # Get the data of each column
    data = [originalSheet.cell_value(i, col)
            for col in range(originalSheet.ncols)]

    for index, value in enumerate(data):

        # If any key present in replacementTextKeyPairs
        # matches with excel column value, replace the
        # column with the value
        if value in replacementTextKeyPairs.keys():
            newsheetForTextReplacement.write(
                i, index, str(replacementTextKeyPairs.get(value)))
        else:
            newsheetForTextReplacement.write(i, index, value)

# Replaced text will be present in the new workbook
# with name sampleexcelwithreplacedtext.xls
newWorkbookForTextReplacement.save('sampleexcelwithreplacedtext.xls')

Output:

Method 2 : openpyxl 

To install this module type the below command in the terminal.

pip install openpyxl

The advantage of openpyxl package is it can be used to both read and write xlsx/xlsm/xltx/xltm files. Below code uses openpyxl in reading and getting the text from one excel file, replacing the text and writing into another excel file. 

Example:

Excel File Used – 

Python3

# Reading and writing in excel can be done by single module
import openpyxl
from openpyxl.utils.cell import get_column_letter

workbook = openpyxl.load_workbook('sampleexcelopenpyxl.xlsx')
workbook.sheetnames
worksheet = workbook["Test"]

# Number of rows
number_of_rows = worksheet.max_row

# Number of columns
number_of_columns = worksheet.max_column

replacementTextKeyPairs = {'1': 'One', '2': 'Two', '3': 'Three'}

# Iterate over the columns and rows, search
# for the text and replace
for i in range(number_of_columns):
    for k in range(number_of_rows):

        cellValue = str(worksheet[get_column_letter(i+1)+str(k+1)].value)

        for key in replacementTextKeyPairs.keys():

            if str(cellValue) == key:
                newCellValue = replacementTextKeyPairs.get(key)
                worksheet[get_column_letter(i+1)+str(k+1)] = str(newCellValue)

workbook.save('sampleexcelwithreplacedtextusingopenpyxl.xlsx')

Output:

Note: openpyxl does not support the old .xls file format, use xlrd to read this file, or convert it to the more recent .xlsx file format