5 Best Ways to Read and Write Excel Files Using the openpyxl Module in Python

πŸ’‘ Problem Formulation: When working with data, it’s common to encounter the need to automate the process of reading from and writing to Excel files. Python’s openpyxl module simplifies this task, providing a way to manipulate Excel files programmatically. Whether you’re looking to import data for analysis or export data after processing it, understanding how to leverage openpyxl can enhance your data handling workflow. This article will guide you through examples starting from reading basic Excel files to writing complex data into them.

Method 1: Reading Excel Files

Reading Excel files with openpyxl is straightforward. It allows you to load an existing workbook, select a worksheet, and read data cell by cell or in batches. You can also retrieve maximum row and column information for iterating over the entire sheet.

Here’s an example:

from openpyxl import load_workbook

# Load the workbook and select the active worksheet
workbook = load_workbook('sample.xlsx')
sheet = workbook.active

# Read the content of a specific cell
cell_content = sheet['A1'].value
print(cell_content)

Output: “Hello, Excel!”

In this example, we load a workbook named ‘sample.xlsx’, select the active worksheet, and read the value of cell A1. This process can be repeated for other cells or automated to read multiple cells, rows, or columns.

Method 2: Writing to Excel Files

Writing data to Excel files using openpyxl is as intuitive as reading them. This method shows how to open (or create) a workbook, select (or add) a worksheet, and write data to specific cells. It also demonstrates how to save the workbook after making changes.

Here’s an example:

from openpyxl import Workbook

# Create a new workbook and select the default worksheet
workbook = Workbook()
sheet = workbook.active

# Write data to a cell
sheet['A1'] = 'Hello, Openpyxl!'

# Save the workbook
workbook.save('new_sample.xlsx')

Output: The cell A1 in the newly created ‘new_sample.xlsx’ contains “Hello, Openpyxl!”.

This snippet demonstrates creating a new Excel file and writing a greeting to cell A1. The Workbook() class initializes a new workbook and workbook.save() persists the changes to disk.

Method 3: Appending Rows of Data

Appending rows of data to a worksheet is a common operation while compiling reports or datasets. The openpyxl library allows you to append data row-wise easily, making it efficient to add data without worrying about the current size of the sheet.

Here’s an example:

from openpyxl import Workbook

# Initialize a new workbook and select the active worksheet
workbook = Workbook()
sheet = workbook.active

# Data to append
rows = [
    ['Name', 'Age', 'City'],
    ['Alice', 24, 'New York'],
    ['Bob', 22, 'Los Angeles']
]

# Append each row to the worksheet
for row in rows:
    sheet.append(row)

# Save the workbook
workbook.save('append_sample.xlsx')

Output: An Excel file named ‘append_sample.xlsx’ with two rows of data under the headers ‘Name’, ‘Age’, and ‘City’.

This code creates a new workbook and appends a list of lists (representing rows of data) to the worksheet. Each sublist in the ‘rows’ list is appended as a new row in the Excel file, and the file is then saved.

Method 4: Working with Multiple Sheets

Handling multiple worksheets within a single workbook is common in many Excel-based tasks. openpyxl offers the ability to create and access multiple worksheets, enabling complex data management and organization.

Here’s an example:

from openpyxl import Workbook

# Initialize a workbook
workbook = Workbook()

# Create a new worksheet
workbook.create_sheet(title='Data Sheet')

# Access a specific worksheet by name
data_sheet = workbook['Data Sheet']

# Write data to the new worksheet
data_sheet['A1'] = 'Data analysis'

# Save the workbook
workbook.save('multiple_sheets.xlsx')

Output: An Excel file named ‘multiple_sheets.xlsx’ with an additional worksheet called ‘Data Sheet’ containing the text “Data analysis” in cell A1.

This code snippet shows how to create a new worksheet, access it by its name, and write data to it. It highlights the flexibility of managing different sheets within a single workbook.

Bonus One-Liner Method 5: Creating and Saving a Workbook

Sometimes, you just need a quick one-liner to get the job done. This method showcases how to create and save an empty workbook with a single line of code using the openpyxl module.

Here’s an example:

from openpyxl import Workbook; Workbook().save('one-liner.xlsx')

Output: A new, empty Excel file named ‘one-liner.xlsx’ is saved to your working directory.

This powerful one-liner succinctly demonstrates the creation and saving of a new, blank Excel workbook, illustrating the simplicity and efficiency of the openpyxl module.

Summary/Discussion

  • Method 1: Reading Excel Files. Allows for targeted or batch data retrieval from existing sheets. Strength: Precise and controlled data access. Weakness: Requires knowledge of specific cell references or programmatic iteration for large datasets.
  • Method 2: Writing to Excel Files. Provides a simple way to write data to specific cells in workbooks. Strength: Straightforward implementation for data insertion. Weakness: Writing large amounts of data may require optimization.
  • Method 3: Appending Rows of Data. Efficient for adding structured data to worksheets. Strength: Easily append rows without worrying about sheet size. Weakness: Appending data to large files could be memory-intensive.
  • Method 4: Working with Multiple Sheets. Facilitates complex data organization. Strength: Seamless creation and management of multiple worksheets. Weakness: Handling a very high number of sheets may become unwieldy.
  • Bonus One-Liner Method 5: Creating and Saving a Workbook. Quick setup of new, blank workbooks. Strength: Maximum efficiency for initializing a new Excel file. Weakness: Not suitable for any data manipulation before saving.