Read Excel Sheets in Python: A Simple Guide

In the world of data analysis and manipulation, Python stands as an incredibly versatile tool. Among its many strengths, reading and processing data from Excel files is a common task that Python handles with remarkable efficiency. This guide will walk you through how to read Excel files in Python using the openpyxl library, a powerful tool for working with Excel documents.

Why Use Python for Excel Files?

• Automation: Automate tasks such as data extraction, transformation, and loading (ETL).
• Scalability: Process large datasets quickly and efficiently.
• Flexibility: Easily integrate Excel data with other data sources or tools in Python.

Getting Started

To begin, you’ll need to:

1. Install Python on your system if you haven’t already.
2. Install the openpyxl library using pip:

pip install openpyxl

Reading Excel Files with openpyxl

Let’s dive into the process of reading an Excel file.

Loading the Workbook

from openpyxl import load_workbook



workbook = load_workbook(filename=‘sample.xlsx’)

Here, we use load_workbook to open an Excel file named ‘sample.xlsx’.

🗒 Note: Make sure the Excel file is not open in Excel software while you're trying to access it with Python.

Selecting a Worksheet

# Select the first active sheet
sheet = workbook.active

Or, if you know the name of the sheet:

# Select a specific sheet by name
sheet = workbook[‘Sheet1’]

Reading Data

• Reading All Data: To read all the data in the sheet:

for row in sheet.iter_rows(values_only=True):
    print(row)

<li><strong>Reading Specific Cells:</strong> Access individual cells by their coordinates:</li>

# Read a single cell
cell_value = sheet['A1'].value
print(cell_value)

# Read a range of cells
for row in sheet['A1:B5']:
    for cell in row:
        print(cell.value)

📌 Note: values_only=True returns the cell values instead of cell objects, which can be more useful when you only need the data.

Handling Complex Excel Files

Sometimes, you'll encounter Excel files with multiple sheets, named ranges, or formatting that needs to be addressed:

Working with Named Ranges

named_range = workbook.defined_names['MyRange']
cells = workbook.get_named_range('MyRange')
for cell in cells:
    print(cell.value)

Accessing Multiple Sheets

# Iterate through all sheets
for sheet_name in workbook.sheetnames:
    sheet = workbook[sheet_name]
    print(sheet.title)  # Print sheet name
    for row in sheet.iter_rows(values_only=True):
        print(row)  # Print data in the sheet

Preserving Formatting

openpyxl also allows you to read formatting:

# Access cell formatting
cell = sheet[‘A1’]
print(cell.fill.bgColor.rgb)
print(cell.font.name)

Data Analysis with Read Excel Data

Once you have your data in Python, you can leverage Python’s rich ecosystem for further analysis:

• Use pandas for data manipulation:

import pandas as pd



df = pd.DataFrame(sheet.values)

<li>Visualize data with libraries like <code>matplotlib</code> or <code>seaborn</code>.</li>
<li>Apply machine learning models using <code>scikit-learn</code>.</li>

✅ Note: When moving data to pandas, consider handling headers and null values to ensure data integrity.

In summary, Python’s ability to interact with Excel files makes it a powerful tool for data analysts and developers alike. Whether it’s for simple data extraction, transformation for further analysis, or automating large data processes, openpyxl provides the functionality needed to handle Excel files efficiently. With the knowledge of reading Excel files in Python, you can automate many tedious tasks, integrate Excel data with other systems, and expand your data manipulation capabilities.

How can I read only specific sheets from an Excel file?

+

You can read specific sheets by either directly selecting the sheet by its name or iterating through the sheet names:

workbook = load_workbook(filename=‘sample.xlsx’)
sheet = workbook[‘SheetName’]



for sheet_name in workbook.sheetnames:
    if sheet_name == ‘SheetName’:
        sheet = workbook[sheet_name]
        break

Can openpyxl handle merged cells or formatted cells?

+

Yes, openpyxl can handle merged cells, formatted cells, and even formulas. You can check if a cell is part of a merged range with:

if cell.coordinate in sheet.merged_cells:
    print(“This cell is merged!”)

And for formatting, you can access properties like font, fill, and alignment directly from the cell object.

How do I deal with large Excel files that cause performance issues?

+

For large Excel files:

• Only load the sheets you need.
• Consider using pandas to read the Excel file into memory in chunks:

for chunk in pd.read_excel(‘large_file.xlsx’, chunksize=10000):
    # Process each chunk

• Close the workbook when you’re done to free up system resources.