How To Use Python On Excel

In this tutorial, we will learn how to use Python on Excel spreadsheets using the openpyxl library in Python. Python is a powerful programming language that is well-suited for spreadsheet processing, and the openpyxl library provides an easy and clear way to work with Excel documents.

To get started, you should have Python and pip (the package installer for Python) installed on your computer.

Step 1: Install the openpyxl library

To install the openpyxl library, open your terminal or Command Prompt and run the following command:

1
pip install openpyxl

This will download and install the library on your system.

Step 2: Create a sample Excel file

Before we begin working with Python, let’s create a sample Excel file with some data. In this example, we will use a file named **sample_data.xlsx** with the following data:

Name  Age  Country
Alice 25   USA
Bob   30   Canada
Eve   28   France
Steve 22   UK
Sara  27   Germany

Save this file to a location of your choice on your computer.

Step 3: Read and display data from the Excel file

Now that we have the sample Excel file, let’s read and display its data using openpyxl. Use the following code to do so:

1
2
3
4
5
6
7
8
9
10
11
import openpyxl
 
# Load the workbook from the Excel file
workbook = openpyxl.load_workbook('sample_data.xlsx')
 
# Choose the active sheet (Sheet1) for reading
sheet = workbook.active
 
# Read and display the data in the Excel file
for row in sheet.iter_rows(min_row=1, max_row=6, min_col=1, max_col=3, values_only=True):
print(row)

This code will display the following output:

('Name', 'Age', 'Country')
('Alice', 25, 'USA')
('Bob', 30, 'Canada')
('Eve', 28, 'France')
('Steve', 22, 'UK')
('Sara', 27, 'Germany')

Step 4: Modify data in the Excel file

Now let’s update the age value of Alice from 25 to 26 using the following code:

1
2
3
4
5
# Modify the age of Alice in the Excel file
sheet.cell(row=2, column=2).value = 26
 
# Save the modified workbook
workbook.save('sample_data_modified.xlsx')

This code will create a new Excel file named **sample_data_modified.xlsx** with the updated age value of Alice:

Name  Age  Country
Alice 26   USA
Bob   30   Canada
Eve   28   France
Steve 22   UK
Sara  27   Germany

Full Code

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
import openpyxl
 
# Load the workbook from the Excel file
workbook = openpyxl.load_workbook('sample_data.xlsx')
 
# Choose the active sheet (Sheet1) for reading
sheet = workbook.active
 
# Read and display the data in the Excel file
for row in sheet.iter_rows(min_row=1, max_row=6, min_col=1, max_col=3, values_only=True):
    print(row)
 
# Modify the age of Alice in the Excel file
sheet.cell(row=2, column=2).value = 26
 
# Save the modified workbook
workbook.save('sample_data_modified.xlsx')

Conclusion

In this tutorial, we have learned **how to use Python with Excel** by seeing an example that demonstrates reading, displaying, and modifying data in Excel files using the **openpyxl** library.

This is just the beginning; there are many other features and functionalities provided by openpyxl which can assist you in working with Excel using Python. For more information, refer to the [official documentation](https://openpyxl.readthedocs.io/en/stable/) of the openpyxl library.