Automating Excel Tasks with Python and Openpyxl

Introduction
Prerequisites
Installation
Opening and Reading from an Excel File
Creating a New Excel File
Writing to an Excel File
Modifying Existing Excel Files
Conclusion

Introduction

In today’s data-driven world, organizations often deal with large amounts of data stored in Excel files. Automating Excel tasks using Python can save significant time and effort. Openpyxl is a powerful Python library that allows us to interact with Excel files, read data from them, create new files, modify existing files, and perform various operations. In this tutorial, we will explore how to leverage Openpyxl to automate Excel tasks using Python. By the end of this tutorial, you will be able to read data from an Excel file, write data to an Excel file, create new Excel files, and modify existing Excel files using Python.

Prerequisites

To follow along with this tutorial, you should have a basic understanding of Python programming. Familiarity with working with Excel files is helpful but not mandatory.

Installation

Before we get started, we need to install the Openpyxl library. Open your command prompt or terminal and run the following command: python pip install openpyxl Once the installation is complete, we are ready to start automating Excel tasks with Python!

Opening and Reading from an Excel File

The first step in automating Excel tasks is to open an existing Excel file and read data from it. Openpyxl provides a simple and intuitive way to accomplish this.

First, import the load_workbook function from the openpyxl module:
```
 from openpyxl import load_workbook
```
Next, use the load_workbook function to open the Excel file. Specify the file path as the argument:
```
 workbook = load_workbook('path/to/file.xlsx')
```
Once the workbook is loaded, you can access the sheets within the workbook using the sheetnames attribute. For example, to access the first sheet, you can use:
```
 sheet = workbook[sheetnames[0]]
```
To read a specific cell value from the sheet, you can use the cell method and provide the row and column index as arguments. For example, to read the value from cell A1, you can use:
```
 value = sheet.cell(row=1, column=1).value
```
You can also iterate over rows or columns using the iter_rows or iter_cols methods. This allows you to access values from multiple cells at once. For example, to iterate over all the values in column A, you can use:
```
 for cell in sheet.iter_cols(min_row=1, max_row=sheet.max_row, min_col=1, max_col=1):
     print(cell[0].value)
```
Finally, don’t forget to close the workbook after you finish reading from it:
```
 workbook.close()
```
Now you know how to open an existing Excel file and read data from it using Openpyxl.

Creating a New Excel File

Sometimes, we may need to create a new Excel file from scratch. Openpyxl makes it easy to create Excel files and populate them with data.

To create a new workbook, import the Workbook class from the openpyxl module:
```
 from openpyxl import Workbook
```
Next, create a new instance of the Workbook class:
```
 workbook = Workbook()
```
By default, a new workbook is created with a single sheet named Sheet. You can access this sheet using the active attribute:
```
 sheet = workbook.active
```
To rename the sheet, use the title attribute:
```
 sheet.title = 'MySheet'
```
You can now write data to the Excel file. Use the cell method to specify the row and column index, and assign a value to it:
```
 sheet.cell(row=1, column=1, value='Hello')
```
After populating the workbook with data, save it using the save method and specify the file path:
```
 workbook.save('path/to/new_file.xlsx')
```
Don’t forget to close the workbook once you are done:
```
 workbook.close()
```
Now you can create a new Excel file from scratch using Python and Openpyxl.

Writing to an Excel File

In addition to creating new Excel files, Openpyxl allows us to write data to existing Excel files. This is useful when we want to update the contents of an Excel file without losing the existing data.

To open an existing Excel file for writing, use the load_workbook function:
```
 workbook = load_workbook('path/to/existing_file.xlsx')
```
Access the desired sheet within the workbook:
```
 sheet = workbook[sheetnames[0]]
```

Use the cell method to write data to a specific cell:

 sheet.cell(row=1, column=1, value='Updated Value')

Save the changes to the Excel file:

 workbook.save('path/to/existing_file.xlsx')

Close the workbook:
```
 workbook.close()
```
You can now write data to an existing Excel file using Openpyxl in Python.

Modifying Existing Excel Files

Apart from simply updating cell values, Openpyxl provides various methods to modify existing Excel files. Let’s explore a few common scenarios.

Adding a New Sheet

To add a new sheet to an existing Excel file, follow these steps:

Open the Excel file using load_workbook:

 workbook = load_workbook('path/to/existing_file.xlsx')

Create a new sheet using the create_sheet method. Provide the name of the new sheet as the argument:
```
 new_sheet = workbook.create_sheet('New Sheet')
```

Save the changes and close the workbook:

 workbook.save('path/to/existing_file.xlsx')
 workbook.close()

Deleting a Sheet

To delete a sheet from an Excel file, follow these steps:

Open the Excel file using load_workbook:

 workbook = load_workbook('path/to/existing_file.xlsx')

Access the sheet you want to delete:
```
 sheet = workbook['Sheet to Delete']
```
Delete the sheet using the remove method:
```
 workbook.remove(sheet)
```

Save the changes and close the workbook:

 workbook.save('path/to/existing_file.xlsx')
 workbook.close()

Merging Cells

To merge cells in an Excel file, use the merge_cells property of a sheet: python sheet.merge_cells('A1:B2') In the example above, cells A1 to B2 are merged into a single cell.

Formatting Cells

Openpyxl provides various formatting options for cells, such as setting the font style, size, alignment, and background color. Here’s an example of how to format a cell: ```python from openpyxl.styles import Font, Alignment

# Access the cell
cell = sheet['A1']

# Apply formatting
cell.font = Font(bold=True, size=12)
cell.alignment = Alignment(horizontal='center', vertical='center')
``` These are just a few examples of how you can modify existing Excel files using Openpyxl in Python.

Conclusion

In this tutorial, we have learned how to automate Excel tasks using Python and Openpyxl. We covered opening and reading data from Excel files, creating new Excel files, writing to Excel files, and modifying existing Excel files. With these skills, you can save time and effort by automating repetitive Excel tasks and perform complex operations on large datasets. Python and Openpyxl provide a powerful combination for working with Excel files efficiently.

Remember to check the Openpyxl documentation for more advanced features and options. Happy automating!

Published: 4 May 2021