Excel Automation with Python’s Openpyxl: A Comprehensive Guide
Are you tired of manually processing large amounts of data in Excel? Do you want to automate your tasks and save time? Look no further! In this blog post, we’ll explore how to read and write Excel files using the
openpyxl module in Python, along with some coding examples.
openpyxl is a popular Python library that provides easy-to-use methods for working with Excel files. Whether you need to read data from an existing Excel file or create a new Excel file and write data into it,
openpyxl has got you covered. So let’s dive in!
Before we start, let’s make sure you have
openpyxl installed. You can install it using
pip, the Python package manager, by running the following command in your terminal or command prompt:
pip install openpyxl
Reading Excel Files
Let’s start with reading data from an existing Excel file. Here’s an example of how you can do that using
import openpyxl # Load the Excel workbook workbook = openpyxl.load_workbook('example.xlsx') # Get the active sheet sheet = workbook.active # Access data from cells cell_value = sheet['A1'].value print("Cell A1 value:", cell_value) # Access data from rows and columns for row in sheet.iter_rows(min_row=2, max_row=5, min_col=1, max_col=3, values_only=True): print(row)
In this example, we first loaded the Excel workbook using
openpyxl.load_workbook(), passing the file name as an argument. Then, we accessed the active sheet using the
active property of the workbook object. We can access the data in individual cells by specifying the cell’s coordinates in square brackets (e.g.,
We can also access data from multiple cells using the
iter_rows() method, which allows us to iterate through a range of rows and columns in the sheet. In this example, we iterated through rows 2 to 5 and columns 1 to 3 and accessed the values from those cells using the
values_only parameter set to
Writing Excel Files
Next, let’s learn how to create a new Excel file and write data into it using
openpyxl. Here’s an example:
import openpyxl # Create a new Excel workbook workbook = openpyxl.Workbook() # Get the active sheet sheet = workbook.active # Write data to cells sheet['A1'] = 'Name' sheet['B1'] = 'Age' sheet['C1'] = 'City' data = [ ('Alice', 25, 'New York'), ('Bob', 30, 'Los Angeles'), ('Charlie', 35, 'Chicago'), ('David', 40, 'San Francisco') ] for row in data: sheet.append(row) # Save the workbook workbook.save('example_new.xlsx')
In this example, we first created a new Excel workbook using
openpyxl.Workbook(). Then, we accessed the active sheet as before. We can write data to cells by assigning values to the cell’s coordinates (e.g.,
sheet['A1'] = 'Name'). We can also use the
append() method to add data as rows to the sheet.
Finally, we saved the workbook using the
save() method, specifying the file name to save the new Excel file with the written data.
Modifying Excel Files
openpyxl also allows you to modify existing excel files by updating the values in cells, adding or deleting rows and columns, and formatting cells. Here’s an example:
import openpyxl from openpyxl.styles import Font # Load the Excel workbook workbook = openpyxl.load_workbook('example.xlsx') # Get the active sheet sheet = workbook.active # Update cell values sheet['B2'] = '26' # Update cell B2 with a new value # Add a new row new_row = ('Eve', 28, 'Seattle') sheet.append(new_row) # Append the new row to the end of the sheet # Delete a row sheet.delete_rows(3, 1) # Delete row 3 # Format cells font = Font(bold=True) sheet['A1'].font = font # Set cell A1 to bold # Save the modified workbook workbook.save('example_modified.xlsx')
In this example, we first loaded the existing Excel workbook as before. Then, we accessed the active sheet and made updates to cell values using the cell’s coordinates (e.g.,
sheet['B2'] = '26'). We also appended a new row to the sheet using the
append() method with a tuple of values for the new row.
Next, we deleted a row using the
delete_rows() method, specifying the starting row and the number of rows to delete. We also applied formatting to a cell using the
Font class from the
openpyxl.styles module. In this case, we set the font of cell A1 to bold.
Finally, we saved the modified workbook using the
save() method, which created a new Excel file with the updates.
And that’s it! With
openpyxl, you can easily read, write, and modify Excel files in Python, enabling you to automate your data processing tasks and save time.
Here’s a summary of the basic steps for reading and writing Excel files using
pipif not already installed.
- Load an existing Excel file using
openpyxl.load_workbook()and access the active sheet.
- Read data from cells using cell coordinates or iterate through rows and columns using
- Write data to cells using cell coordinates or append new rows using the
- Modify Excel files by updating cell values, adding or deleting rows and columns, and formatting cells.
- Save the workbook using the
save()method to create a new Excel file with the changes.
With these steps and some basic Python programming knowledge, you can easily automate your Excel-related tasks using
openpyxl in Python. Happy coding!