Ajay Anand

Python Program to Edit Excel Files

Using the Python program discussed here, we can ‘add or delete Text’, ‘insert Formulas’ and ‘modify the Formatting’ across thousands of Excel Workbooks in a computer Folder.

Install Python and openpyxl

To write and execute this program, you need Python, along with the openpyxl library. Those who doesn’t have Python installed on their computer, check my blog post on Python program to Read Excel Files.

Excel Workbooks containing Weekly Sales Report

For explaining the program, I am using the Weekly Sales Reports of a company, which has outlets in 11 major cities of India. Every weekend, the country manager of this company, records the sales details in a new Excel Workbook and this Workbook will have the information of the sales occurred during that week, for all outlets.

Following is the Excel Workbook for the 10th week of 2022.

The Workbook for Week number, 37.

In total, there are 52 Excel Workbooks for 52 weeks of the year 2022. All Workbooks follow the same format and are saved in a folder called ‘Sales Report 2022’ in the ‘C drive’ of the computer.

Editing Challenge

We need to make 4 changes in all these 52 Excel Workbooks.

  1. The text ‘Bombay’ in the cell B6 should be replaced with ‘Mumbai’.
  2. The text ‘Bangalore’ in the cell B9 should be replaced with ‘Bengaluru’.
  3. The text ‘Amount’ in the cell C5 should be replaced with ‘Sales Amount’.
  4. Total of Sales Amounts (Values in the cells from C6 to C16) should be inserted in the cell C17.

If we try to make these changes in 52 Workbooks manually, it will require at least half an hour of manual labor.

But using a Python program, we can complete this task within a few seconds. In the next section, we’ll learn how to write this program in Python.

Python program to Replace Text and Insert Formula

Launch Python IDLE!

Python IDLE has two parts.

  1. Interactive Shell – Allows us to execute Python code interactively and see the results immediately, making it useful for quick experimentation and testing of small code snippets.
  2. File Editor – Provides a user-friendly interface for writing, editing, and executing Python code, making it easier to develop and maintain larger programs and scripts.

We will be using the File Editor of Python IDLE for writing and executing this program.

To open the File Editor, go to the File tab > New File

A new file window will pop up. To save this file, go to the File tab, click on Save.

Using the Save As dialog, we can specify the Name of the file and Location to save it. I will name the Python file as ‘Edit Excel Files’ and save it in the desktop of the computer.

Once you have saved the file, either type in the following code or copy it into the file editor and execute it.

import os
import openpyxl

folder = 'C:\Sales Report 2022'

for filename in os.listdir(folder):
    if filename.endswith('.xlsx'):
        file = os.path.join(folder, filename)
        book = openpyxl.load_workbook(file)
        sheet = book.active

        sheet['B6'] = 'Mumbai'
        sheet['B9'] = 'Bengaluru'
        sheet['C5'] = 'Sales Amount'

        sheet['C17'] = '=SUM(C6:C16)'
        sheet['C17'].number_format = '#,##0_ ;-##0'

        book.save(file)

Explanation of the Code

Here’s the detailed explanation of the code for those who are interested in understanding the program.

import os

os is a python module that will enable us to interact with the Operating System of the computer.

import openpyxl

openpyxl is a third party Python library that will enable us to Read and Write Excel files.

folder = 'C:\Sales Report 2022'

Sets the variable ‘folder‘ to the file path of the directory that contains the Excel files to edit.

for filename in os.listdir(folder):

Creates a loop that iterates through every file in the ‘folder‘.

if filename.endswith('.xlsx'):

The If function will make sure that only the Excel Workbooks in the folder are processed. i.e., the files with ‘.xlsx‘ extension.

file = os.path.join(folder, filename)
book = openpyxl.load_workbook(file)

Sets the ‘file‘ variable to the full file path of the current file being processed, and loads that file (Workbook) into the variable called ‘book’.

sheet = book.active

Active worksheet of the processed workbook (represented by the ‘book’ object) is saved into the variable, ‘sheet‘.

sheet['B6'] = 'Mumbai'
sheet['B9'] = 'Bengaluru'
sheet['C5'] = 'Sales Amount'

These lines set the values in the cells B6, B9 and C5 to the strings ‘Mumbai’, ‘Bengaluru’ and ‘Sales Amount’ respectively.

sheet['C17'] = '=SUM(C6:C16)'

This statement will insert the formula =SUM(C6:C16) in the cell C17, which will add the values in the cells from C6 to C16.

sheet['C17'].number_format = '#,##0_ ;-##0'

Displays the result with ‘thousands separator’.

book.save(file)

This statement saves the changes made to the workbook back to the original Excel file.

Executing and modifying the Program

For executing the program, click on Run > Run module

Within a few seconds, required modifications will be made in all 52 Excel Workbooks.

  1. The text ‘Bombay’ in the cell B6 got replaced with ‘Mumbai’.
  2. The text ‘Bangalore’ in the cell B9 got replaced with ‘Bengaluru’.
  3. The text ‘Amount’ in the cell C5 got replaced with ‘Sales Amount’.
  4. Formula to add Sales Amounts was inserted in the cell C17.

The program explained here is meant for editing a few Weekly Sales Reports. But the same program can be used for editing thousands of Excel Workbooks in folder, by modifying the following variables.

  1. folder – Use the folder path of the directory that contain your Excel Workbooks.
  2. sheet[‘B6’] = ‘Mumbai’ – Specify the Address of the cell to modify, inside the square brackets and the Text to replace with, after the Equal sign.

Python program to Read Excel Files