[Python] Replace text in Excel cells

Replace strings in Excel cells with Python.
It can be used to output a new file by replacing a part of the string of the original file.

Put screenshots of the Excel file before and after the replacement.

Note that the method described in this article can only be used in an environment where Excel is installed.

Environment

• Windows 10
• Microsoft Excel 2016
• Python 3.6.2

Install the necessary modules

Run the following command to install win32com.

pip install pywin32

Replace text in cell

Prepare the following folder structure:

/
├ template.xlsx  ← Target Excel file
└ main.py        ← Python script

The following is a Python script.
The highlighted part is the place where the replacement process is being performed.

import win32com.client
from pywintypes import com_error
from pathlib import Path


# Search string: Replace string
REPLACE_TXTS = {
    '{{セルテキスト1}}': 'こんにちは。',
    '{{セルテキスト2}}': 'おはようございます。'
}

nowdir = Path(__file__).absolute().parent

excel = win32com.client.Dispatch('Excel.Application')

excel.Visible = False

try:
    # open xlsx
    wb = excel.Workbooks.Add(str(nowdir / 'template.xlsx'))
    sheet = wb.WorkSheets(1)
    sheet.Activate()

    # Replace text in cell
    rg = sheet.Range(sheet.usedRange.Address)
    for search_txt, replace_Txt in REPLACE_TXTS.items():
        rg.Replace(search_txt, replace_Txt)

    # save as
    wb.SaveAs(str(nowdir / 'new.xlsx'))

except com_error as e:
    print('Failure.', e)
else:
    print('Success.')
finally:
    wb.Close(False)
    excel.Quit()

The flow of processing is simply written as follows.

1. Define replacement string
   First, define the string you want to replace and what string you want to replace it with. Used in later replacement processing.

2. open xlsx file
   Open the Excel file and select (Activate) the sheet.
   Rather than opening a file because you are doing Workbooks.Add, it’s like copying that file and using it as a working book.

3. Perform replacement
   Get the cell range used in the sheet with sheet.UsedRange.Address. Then, execute the Replace function by specifying the string to be searched and the string to be replaced (it is the same as actually replacing on Excel).

4. Save the xlsx file
   Save the file with a different name because you do not want to replace the original file.

When you run this script, the text in the cell is replaced as shown in the screenshot at the beginning.

References

As a reference, I will put a link to the reference of the function used this time.
Although it is a VBA reference, I think that the function is the same.

• Worksheet.Activate method (Excel)
• Workbooks.Add method (Excel)
• Worksheet.UsedRange property (Excel)
• Range.Replace method (Excel)

Related Posts