Modifying Excel Files with Python

In this lesson, you will learn how to open an Excel file, make changes, and save it back using Python. We will use a library called openpyxl to do this.

Library Used: openpyxl

• openpyxl is a Python library that lets us read, write, and edit Excel (.xlsx) files.

• It is handy when you want to:

  • Update cell values

  • Add formulas

  • Change formatting (bold, colors, etc.)

  • Save updated files

To install it, run:

pip install openpyxl

 Code Example

from openpyxl import load_workbook  # To open Excel files
from openpyxl.styles import Font    # For text formatting

def modify_excel(file_path):
    """Modify specific cells in Excel"""
    wb = load_workbook(file_path)   # Open the Excel file
    ws = wb.active                  # Select the first sheet

    # Update cell values
    ws['B2'] = 29        # Change Alice's age to 29
    ws['C3'] = "IT"      # Change Bob's department to IT

    # Add formulas
    ws['D1'] = "Bonus"   # Create a header
    ws['D2'] = "=B2*100" # Formula: Age × 100
    ws['D3'] = "=B3*100"

    # Format cells
    ws['A1'].font = Font(bold=True, color="FF0000")  # Bold + red text

    # Save changes
    wb.save(file_path)
    print(f"Modified file saved: {file_path}")

# Run function on Excel file
modify_excel("sample_data.xlsx")

 Step-by-Step

1. Open the file

   wb = load_workbook(file_path)
   ws = wb.active
   

   • load_workbook opens the Excel file.

   • ws.active picks the first sheet.

2. Update cell values

   ws['B2'] = 29
   ws['C3'] = "IT"
   

   • This changes the values in specific cells.

3. Add formulas

   ws['D2'] = "=B2*100"
   

   • Adds a formula inside the Excel cell.

   • When you open Excel, it will calculate automatically.

4. Format cells

   ws['A1'].font = Font(bold=True, color="FF0000")
   

   • Makes cell A1 bold and red.

5. Save changes

   wb.save(file_path)
   

   • Saves the updated file with your changes.

 Output Example

Before:

After running code:

 Key Takeaway

• openpyxl is your go-to tool for working with Excel in Python.

• You can read, write, add formulas, and format cells programmatically.