Change Value in Excel Using Python

In this article we will learn different approaches with which we can change the value of data present in an excel sheet using python.

Openxypl

Openpyxl is a Python library used for working with Excel spreadsheets. It is a popular choice for working with Excel files in Python because it is easy to use, has an active developer community, and provides many features for working with spreadsheets.

Openpyxl allows you to create, read, write, and modify Excel files using Python. It supports the following file formats:

• 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)

Here are some of the key features of openpyxl:

• Reading and Writing Excel files: With openpyxl, you can easily read and write Excel files. This includes creating new files from scratch, modifying existing files, and saving changes.

• Data manipulation: Openpyxl allows you to manipulate data within Excel spreadsheets. This includes sorting, filtering, and formatting data, as well as performing calculations and aggregations.

• Cell manipulation: Openpyxl provides a simple way to manipulate cells in Excel spreadsheets. You can set cell values, apply formatting, and add formulas to cells.

• Charts: Openpyxl supports the creation and modification of Excel charts. You can create charts from scratch or modify existing ones.

• Pivot tables: Openpyxl supports pivot tables, which allow you to analyze data in an Excel spreadsheet.

• Protection: Openpyxl allows you to protect Excel files by adding password protection to them.

• Compatibility: Openpyxl is compatible with Python 3.6 and later, and can be used on Windows, Linux, and Mac OS X operating systems.

Overall, openpyxl is a powerful and flexible library that makes it easy to work with Excel files in Python. Whether you need to read data from an Excel file, create a new spreadsheet, or perform complex data analysis, openpyxl has the tools you need to get the job done.

Now that we know a little about openxypl, let's explore an example where we will change the value of the first header in the first column of the excel sheet.

The excel sheet currently looks like this:

Now our objective is to change the first header of the first column whose value is RiskName to TutorialsPoint.

The first thing that we require in order to be able to run the below code is to have openxypl library installed on our machine.

To install openxypl, just run the command shown below.

Command

pip3 install openxypl

Note: If you are using an older version of python, you can run pip install openxypl.

Consider the code shown below.

Example

# import the openpyxl library
import openpyxl

# open the Excel file
workbook = openpyxl.load_workbook('workbook.xlsx')

# select the sheet to modify
sheet = workbook['Sheet1']

# change the header name
sheet.cell(row=1, column=1).value = 'TutorialsPoint'

# save the changes
workbook.save('example.xlsx')

Explanation

In this example, we first import the openpyxl library. We then load the Excel file 'workbook.xlsx' using the load_workbook() method.

Next, we select the sheet we want to modify using the sheet name ('Sheet1' in this case). We then change the value of the cell at row 1, column 1 (which corresponds to the first header cell) to 'TutorialsPoint'.

Finally, we save the changes to the Excel file using the save() method.

Note that this is just a basic example, and you can modify the code to change the header name of any sheet in the Excel file. You can also modify the code to change other aspects of the sheet, such as cell values, formatting, or adding new rows and columns.

To run the above code, we just need to run the command shown below.

Command

python3 main.py

Once we run the above command in the terminal, we will get a new excel file in the output.

Output

From the above image, you can see that the name of the first header of the first column is now changed to "TutorialsPoint" instead of "RiskName".

Now, let's try to change the MemberID column's value to "TutorialsPoint".

It's fairly simple, as we just need to change the value of the column keyword that we are passing in the cell method in our code to 3.

Consider the updated code shown below.

Example

# import the openpyxl library
import openpyxl

# open the Excel file
workbook = openpyxl.load_workbook('workbook.xlsx')

# select the sheet to modify
sheet = workbook['Sheet1']

# change the header name
sheet.cell(row=1, column=3).value = 'TutorialsPoint'

# save the changes
workbook.save('example.xlsx')

Explanation

The only difference between the previous code and the above code is the value of the column keyword which is changed to 3 as we want to change the value of the header that is present in the third column.

To run the above code, we just need to run the command shown below.

Command

python3 main.py

Once we run the above command in the terminal, we will get a new excel file in the output.

Output

Using xlwt/xlrd/xlutils

Let's first discuss these packages in detail.

The xlwt, xlrd, and xlutils libraries are Python modules for working with Excel files. These libraries can be used to read, write, and manipulate Excel spreadsheets in Python.

xlrd

xlrd is a library that can be used to extract data from Excel files. It is capable of reading Excel files in the .xls format. xlrd provides a number of functions for reading data from a spreadsheet, including:

• xlrd.open_workbook(filename, on_demand=False, formatting_info=False): This function is used to open an Excel file and returns an instance of the xlrd.book.Book class. The on_demand parameter can be used to control whether the entire spreadsheet is loaded into memory or not. The formatting_info parameter is used to enable or disable the parsing of formatting information.

• book.sheets(): This function returns a list of xlrd.sheet.Sheet objects, each representing a worksheet in the workbook.

• sheet.nrows and sheet.ncols: These attributes return the number of rows and columns in a sheet, respectively.

xlwt

xlwt is a library that can be used to create and write data to Excel files in the .xls format. It provides a number of functions for creating and writing data to a spreadsheet, including:

• xlwt.Workbook(encoding='utf−8'): This function creates a new xlwt.Workbook object, which represents a new Excel file.

• workbook.add_sheet(sheetname, cell_overwrite_ok=False): This function creates a new worksheet with the specified name and returns an xlwt.Worksheet object.

• worksheet.write(row, col, value, style=None): This function writes a value to a cell at the specified row and column. The style parameter can be used to apply formatting to the cell.

xlutils

xlutils is a library that provides a number of utility functions for working with Excel files. It includes functions for copying worksheets, copying cells, and more. The main classes and functions in xlutils are:

• xlutils.copy.copy: This function can be used to create a new Excel file by copying an existing one.

• xlutils.copy.copy_worksheet: This function can be used to copy a worksheet from one workbook to another.

• xlutils.copy.cell: This function can be used to copy the contents and formatting of a cell from one worksheet to another.

Now that we have discussed these packages in detail, let's make use of a code where we will be making use of them in a python code.

Consider the code shown below.

Example

import xlrd
import xlwt
from xlutils.copy import copy

# Open the Excel file and get the first sheet
workbook = xlrd.open_workbook("workbook.xls")
sheet = workbook.sheet_by_index(0)

# Find the column index of the header you want to change
header_col = None
for col in range(sheet.ncols):
	if sheet.cell_value(0, col) == "First Name":
    	header_col = col
    	break

# If the header was found, change its value
if header_col is not None:
	# Create a new workbook and copy the existing sheet
	new_workbook = copy(workbook)
	new_sheet = new_workbook.get_sheet(0)

	# Set the value of the header in the copied sheet
	new_sheet.write(0, header_col, "TutorialsPoint")

	# Save the new workbook to a file
	new_workbook.save("workbook_modified.xls")

Explanation

We begin by importing the necessary modules:

• xlrd: This module is used for reading data from Excel files.

• xlwt: This module is used for writing data to Excel files.

• xlutils: This module provides a way to modify existing Excel files.

• We open the Excel file that we want to modify using xlrd.open_workbook() and get the first sheet using workbook.sheet_by_index(0).

• We find the column index of the header we want to change by iterating thro ugh the columns of the first row of the sheet using a for loop and sheet.cell_value(row, col) to get the value of the cell.

• If the header is found, we create a new workbook using copy() from xlutils and copy the existing sheet into it using new_workbook.get_sheet(0).

• We then set the value of the header to the new value using new_sheet.write(row, col, value).

• Finally, we save the modified workbook to a new file using new_workbook.save(filename).

That's it! This program demonstrates how to use xlrd, xlwt, and xlutils to modify an Excel file by changing the value of a header.

To run the above code, we just need to run the command shown below.

Command

python3 main.py

Once we run the above command in the terminal, we will get a new excel file in the output.

Output

Conclusion

In conclusion, Python provides several libraries for working with Excel files, including xlrd, xlwt, and xlutils. These libraries allow us to read, write, and modify Excel files using Python code.

In this specific case, we used these libraries to change the value of a header in an Excel sheet. The process involves opening the Excel file using xlrd, finding the index of the header we want to change, creating a copy of the workbook using xlutils, modifying the value of the header in the copied sheet using xlwt, and then saving the modified workbook to a new file.

With these powerful libraries, we can automate complex Excel tasks and integrate them into larger Python applications, making it easier to work with and analyse data stored in Excel files.