Change value in Excel using Python

In this article, We are going to change the value in an Excel Spreadsheet using Python.

Method 1: Using openxml:

openpyxl is a Python library to read/write Excel xlsx/xlsm/xltx/xltm files. It was born from a lack of an existing library to read/write natively from Python the Office Open XML format. openpyxl is the library needed for the following task. You can install openpyxl module by using the following command in Python.

pip install openpyxl

Function used:

• load_workbook(): function used to read the excel spreadsheet
• workbook.active: points towards the active sheet in the excel spreadsheet
• workbook.save(): saves the workbook

Approach:

1. Import openpyxl library
2. Start by opening the spreadsheet and selecting the main sheet
3. Write what you want into a specific cell
4. Save the spreadsheet

Excel File Used:

Below is the implementation:

from openpyxl import load_workbook
#load excel file
workbook = load_workbook(filename="csv/Email_sample.xlsx")
#open workbook
sheet = workbook.active
#modify the desired cell
sheet["A1"] = "Full Name"
#save the file
workbook.save(filename="csv/output.xlsx")

Output:

Method 1: Using xlwt/xlrd/xlutils.

This package provides a collection of utilities for working with Excel files. Since these utilities may require either or both of the xlrd and xlwt packages, they are collected together here, separate from either package.You can install xlwt/xlrd/xlutils modules by using the following command in Python

pip install xlwt
pip install xlrd
pip install xlutils

Prerequisite:

• open_workbook(): function used to read the excel spreadsheet
• copy(): copies the content of a workbook
• get_sheet(): points towards a specific sheet in excel workbook
• write(): writes data in the file
• save(): saves the file

Approach:

1. Open Excel File
2. Make a writable copy of the opened Excel file
3. Read the first sheet to write within the writable copy
4. Modify value at the desired location
5. Save the workbook
6. Run the program

Excel File Used:

Below is the implementation:

import xlwt
import xlrd
from xlutils.copy import copy
# load the excel file
rb = xlrd.open_workbook('UserBook.xls')
# copy the contents of excel file
wb = copy(rb)
# open the first sheet
w_sheet = wb.get_sheet(0)
# row number = 0 , column number = 1
w_sheet.write(0,1,'Modified !')
# save the file
wb.save('UserBook.xls')

Output: