Automate Excel Tasks with Python
If you've ever found yourself copying the same data, formatting it, and performing calculations daily in Excel, you might have wondered, "Isn't there a way to automate this?"
Imagine opening dozens of Excel files that contain employee information for different departments, copying names and email addresses, and consolidating them into a single file.
How long would it take to open each file, copy the data, and organize it?
Manually performing these repetitive tasks can lead to mistakes and takes away time from more important work.
Fortunately, with Python and the openpyxl library, you can easily automate these repetitive Excel tasks.
Introduction to the openpyxl Library
openpyxl is a library that provides various functionalities to read, write, and modify Excel files.
With this library, you can handle the structure and elements of an Excel file using Python code.
Structure of an Excel File
-
Workbook: The entire Excel file -
Sheet: Tabs within the workbook -
Cell: Each cell within a sheet
Using openpyxl, you can select specific workbooks, sheets, and cells, and then read or modify the data.
Reading Data from an Excel File
To read data from an Excel file, import the openpyxl library and use the load_workbook function.
# Importing the openpyxl library
from openpyxl import load_workbook
# Loading the Excel file
workbook = load_workbook('input_file.xlsx')
# Selecting a specific sheet
sheet = workbook['Sheet1']
# Reading a cell value
a_one = sheet['A1'].value
print(f"Value in cell A1: {a_one}")
When importing the openpyxl library, use the format from openpyxl import {Class or Function} to import the necessary classes or functions.
The load_workbook function loads the Excel file.
Here, input_file.xlsx is an example Excel file that should be in the same folder where the Python code is being executed.
If the Excel file is in a different folder, you need to copy the file to the same folder where the Python code is executed.
Alternatively, you can specify the absolute path of the file (e.g., C:\Users\username\Documents\input_file.xlsx).
Note : Since CodeFriends accesses the web-based environment, it cannot access files stored on your computer due to web browser security reasons. Hence, in future lessons, Excel files will be uploaded to an external storage, and the URL of the files will be used to read the Excel files.
The code workbook['Sheet1'] selects the Sheet1 tab in the Excel file. Excel files can have multiple sheets, so you need to select the required sheet.
The code sheet['A1'].value retrieves the value from cell A1 in Sheet1.
If the input_file.xlsx file has "AWARD_NAME" in cell A1, the above code will output:
Value in cell A1: AWARD_NAME
In the next lesson, we will explore the basic methods of openpyxl.
Want to learn more?
Join CodeFriends Plus membership or enroll in a course to start your journey.