Change Value in Excel Using Python

In this article, we will learn different approaches to change values in Excel files using Python. We'll explore two main libraries: openpyxl for modern Excel formats and xlwt/xlrd/xlutils for legacy formats.

Using Openpyxl

Openpyxl is a Python library designed for working with Excel spreadsheets. It supports modern Excel file formats including:

• XLSX (Microsoft Excel Open XML Spreadsheet)

• XLSM (Microsoft Excel Open XML Macro?Enabled Spreadsheet)

• XLTM (Microsoft Excel Open XML Macro?Enabled Template)

• XLTX (Microsoft Excel Open XML Template)

Key Features

• Reading and Writing: Create, modify, and save Excel files

• Data Manipulation: Sort, filter, and format spreadsheet data

• Cell Operations: Set values, apply formatting, and add formulas

• Charts and Pivot Tables: Create and modify visual data representations

Installation

Install openpyxl using pip ?

pip install openpyxl

Example: Changing Header Values

Let's change the first header from "RiskName" to "TutorialsPoint" ?

import openpyxl

# Create a sample workbook for demonstration
workbook = openpyxl.Workbook()
sheet = workbook.active
sheet['A1'] = 'RiskName'
sheet['B1'] = 'FirstName'
sheet['C1'] = 'MemberID'
workbook.save('sample.xlsx')

# Now modify the header
workbook = openpyxl.load_workbook('sample.xlsx')
sheet = workbook.active

# Change the first header
sheet.cell(row=1, column=1).value = 'TutorialsPoint'
workbook.save('modified.xlsx')

# Verify the change
modified_workbook = openpyxl.load_workbook('modified.xlsx')
modified_sheet = modified_workbook.active
print(f"New header value: {modified_sheet.cell(row=1, column=1).value}")

New header value: TutorialsPoint

Example: Changing Specific Column Headers

To change the "MemberID" header (third column) to "TutorialsPoint" ?

import openpyxl

# Load and modify the third column header
workbook = openpyxl.load_workbook('sample.xlsx')
sheet = workbook.active

# Change the third column header
sheet.cell(row=1, column=3).value = 'TutorialsPoint'
workbook.save('modified_column3.xlsx')

# Display all headers
for col in range(1, 4):
    header = sheet.cell(row=1, column=col).value
    print(f"Column {col}: {header}")

Column 1: RiskName
Column 2: FirstName
Column 3: TutorialsPoint

Using xlwt/xlrd/xlutils

These libraries work with legacy Excel formats (.xls files). While older, they're still useful for specific scenarios.

Library Overview

• xlrd: Reads data from .xls files

• xlwt: Writes data to .xls files

• xlutils: Provides utility functions for copying and modifying Excel files

Installation

pip install xlrd xlwt xlutils

Example: Modifying XLS Files

Here's how to change header values in .xls files ?

import xlwt
import xlrd
from xlutils.copy import copy

# First, create a sample .xls file
workbook = xlwt.Workbook()
worksheet = workbook.add_sheet('Sheet1')
worksheet.write(0, 0, 'First Name')
worksheet.write(0, 1, 'Last Name')
worksheet.write(1, 0, 'John')
worksheet.write(1, 1, 'Doe')
workbook.save('sample.xls')

# Now read and modify the file
read_workbook = xlrd.open_workbook('sample.xls')
sheet = read_workbook.sheet_by_index(0)

# Find and change the "First Name" header
header_col = None
for col in range(sheet.ncols):
    if sheet.cell_value(0, col) == "First Name":
        header_col = col
        break

if header_col is not None:
    # Create a copy and modify it
    new_workbook = copy(read_workbook)
    new_sheet = new_workbook.get_sheet(0)
    
    # Change the header value
    new_sheet.write(0, header_col, "TutorialsPoint")
    new_workbook.save("modified_sample.xls")
    
    print(f"Header changed successfully in column {header_col + 1}")
else:
    print("Header 'First Name' not found")

Header changed successfully in column 1

Comparison of Methods

Conclusion

Use openpyxl for modern Excel files (.xlsx) as it provides better features and simpler syntax. For legacy .xls files, the xlwt/xlrd/xlutils combination remains the standard choice for reading and modifying Excel data programmatically.