Change value in Excel using Python

by

Here, we will use Python to update a value in an Excel spreadsheet.

1)Opening an Excel File:

openpyxl is a Python package that allows you to read and write Excel xlsx/xlsm/xltx/xltm files. It originated from the lack of an existing library that allowed Python to read and write the Office Open XML format natively. The openpyxl library is required for the following task. You can install the openpyxl module in Python by running the following command.

pip install openpyxl

Openpyxl:

The Openpyxl module in Python is used to work with Excel files without requiring third-party Microsoft application software. We can control Excel without having to open the application by using this module. It is used to perform excel tasks such as reading data from an excel file or writing data to an excel file, drawing charts, accessing an excel sheet, renaming the sheet, modifying (adding and deleting) the sheet, formatting, styling the sheet, and any other task. Openpyxl is a very efficient tool for handling these tasks for you.

Data scientists frequently use Openpyxl to perform various operations such as data copying, data mining, and data analysis.

Functions:

  • load_workbook(): This function reads the Excel spreadsheet
  • workbook.active: directs attention to the active sheet in the excel spreadsheet
  • workbook.save(): This function saves the workbook.

Program to Change value in Excel in Python

Example1

Let us take an example of demo.xlsx excel spreadsheet as shown below:

Change value in Excel using Python import load workbook

Approach:

  • Import load_workbook from openpyxl module using the import keyword.
  • Pass the filename as an argument to the load_workbook() function to load the excel file.
  • Apply active function for the above to open the workbook.
  • Modify the cell name that you want to alter.
  • Pass the filename as an argument to the save() function to save the file.
  • The Exit of the Program.

Below is the implementation:

# Import load_workbook from openpyxl module using the import keyword
from openpyxl import load_workbook
 
# Pass the filename as an argument to the load_workbook() function
# to load the excel file
workbook = load_workbook(filename="demo.xlsx")
 
# Apply active function for the above to open the workbook
sheet = workbook.active
 
# Modify the cell name that you want to alter.
sheet["A1"] = "Full Name"
 
# Pass the filename as an argument to the save() function to save the file
workbook.save(filename="demo.xlsx")

Output:

Change value in Excel using Python

Example2: Using xlwt, xlrd, xlutils

This package contains a number of tools for working with Excel files. Because these utilities may require either or both of the xlrd and xlwt packages, they are collected together here, separate from either package.

Installation of xlwt/xlrd/xlutils modules in Python:

pip install xlwt
pip install xlrd
pip install xlutils

Functions:

  • open_workbook(): It is a function that is used to read an Excel spreadsheet.
  • copy(): duplicates the contents of a worksheet.
  • get_sheet(): refers or points to a specific sheet in Excel workbook
  • write(): writes data to a file.
  • save(): This function saves the file.

Approach:

  • Import xlwt module using the import keyword.
  • Import xlrd module using the import keyword.
  • Import copy of xlutils.copy module using the import keyword.
  • Pass the filename as an argument to the open_workbook() function to load the excel file and store it in a variable.
  • Make a dulpicate copy of excel workbook using the copy() function and store it in another variable.
  • Open the sheet that you want to work with of the excel file by passing the number as an argument to the get_sheet() function.
  • Store it in another variable
  • Pass the row number, column number, and some random data as arguments to the write() function.
  • Save the workbook using the save() function.
  • The Exit of the Program.

Below is the implementation:

# Import xlwt module using the import keyword
import xlwt
# Import xlrd module using the import keyword
import xlrd
# Import copy of xlutils.copy module using the import keyword
from xlutils.copy import copy
 
# Pass the filename as an argument to the open_workbook() function
# to load the excel file and store it in a variable
read_book = xlrd.open_workbook('demo.xlsx')
 
# Make a dulpicate copy of excel workbook using the copy() function and store it in another variable
work_book = copy(read_book)
 
# Open the sheet that you want to work with of the excel file by passing 
# the number as an argument to the get_sheet() function
# Store it in another variable
work_sheet = work_book.get_sheet(0)
 
# Pass the row number, column number and some random data as arguments to the
# write() function 
work_sheet.write(0,1,'S.No')
 
# Save the workbook using the save() function
work_book.save('demo.xlsx')

Output:

Change value in Excel using Python using xlwt

Leave a Comment