Python Read and Write Excel Files: A Complete Guide

Python Read and Write Excel Files

Excel, developed by Microsoft, is one of the most widely used tools for organizing, storing, and analyzing data. From simple records and calculations to large datasets and business reports, Excel is used across many professional fields. Python makes working with Excel even more flexible because several libraries allow developers to read, analyze, create, and modify spreadsheet files directly from Python programs.

With the right Python library, you can automate repetitive Excel tasks, extract information from worksheets, create new workbooks, update existing data, and prepare datasets for further processing. In this guide, we will explore different approaches for reading and writing Excel files using libraries such as xlrd, pandas, openpyxl, and xlsxwriter.

Understanding Excel Documents

An Excel document is commonly called a workbook and is generally saved using the .xlsx extension. A workbook can contain one or more worksheets. Each worksheet is arranged as a grid consisting of rows and columns, while the intersection of a row and column forms an individual cell.

Cells can contain different types of information, including text, numbers, and formulas. In many spreadsheets, the first row is used for column headers, while the first column may contain labels or categories. Excel also allows users to add, remove, rename, and organize worksheets. The worksheet currently selected for display is known as the active sheet.

Python libraries provide different ways to work with these workbooks depending on whether your goal is data analysis, simple reading, workbook editing, or file generation.

Reading Excel Files in Python

Python provides several libraries for reading Excel files. Among the commonly used options are xlrd, pandas, and openpyxl. Each library has a different approach, so the appropriate choice depends on the requirements of your project.

1. Using the xlrd Library

The xlrd library is one of the older Python tools for working with Excel spreadsheets. It is mainly associated with reading Excel workbook data. You can install the library using the following command:

pip install xlrd

After installation, a workbook can be opened and a worksheet can be accessed using Python code:

# Import the xlrd module
import xlrd

# Define the file path
file_path = "path_to_file.xlsx"

# Open the workbook
workbook = xlrd.open_workbook(file_path)

# Access the first sheet
sheet = workbook.sheet_by_index(0)

# Read the first cell
value = sheet.cell_value(0, 0)

print("Cell value at (0, 0):", value)

In this example, xlrd is imported first. The specified workbook is then opened, and sheet_by_index(0) selects the first worksheet. The cell_value(0, 0) method retrieves the value from the first row and first column.

This approach can also be extended to access multiple cells or process rows and columns when working with larger spreadsheet datasets.

2. Using pandas

pandas is one of the most popular Python libraries for data analysis. It provides convenient functionality for reading Excel data and converting it into a DataFrame. A DataFrame makes spreadsheet information easier to inspect, filter, analyze, and manipulate.

Install pandas with:

pip install pandas

Once installed, you can read an Excel workbook using read_excel():

import pandas as pd

# Read the Excel file
data = pd.read_excel("path_to_file.xlsx")

# Display basic information
print("Total rows:", len(data))
print("Column headers:", list(data.columns))

The pd.read_excel() function loads the spreadsheet into a DataFrame. From there, Python can work with the data as a structured table. You can inspect the number of records and retrieve the column names using standard pandas operations.

This method is particularly useful when Excel data needs to be analyzed or processed after being loaded into Python.

3. Using openpyxl

If your project requires more direct control over an Excel workbook, openpyxl is another useful option. It is designed for working with .xlsx files and supports reading and writing workbook content.

Install it with:

pip install openpyxl

After installation, you can open an existing workbook and access its active worksheet:

import openpyxl

# Open an existing workbook
workbook = openpyxl.load_workbook("path_to_file.xlsx")

# Get the active worksheet
sheet = workbook.active

# Print the sheet title
print("Sheet title:", sheet.title)

Here, load_workbook() opens the existing Excel file. The active property retrieves the currently active worksheet, and the worksheet’s title property displays its name.

Writing Data to Excel Files in Python

Python can also create and modify Excel files. Libraries such as xlsxwriter and openpyxl provide useful functionality for generating spreadsheets and adding information to worksheets.

1. Using xlsxwriter

xlsxwriter is designed for creating .xlsx files. It provides functionality for writing data and supports spreadsheet features such as formatting, charts, and conditional formatting.

Install the library with:

pip install xlsxwriter

A simple workbook can be created using the following example:

import xlsxwriter

# Create a new workbook
workbook = xlsxwriter.Workbook("example.xlsx")

# Add a worksheet
worksheet = workbook.add_worksheet()

# Data to write
data = ["Alice", "Bob", "Charlie"]

# Write each name to a new row
row = 0

for name in data:
    worksheet.write(row, 0, name)
    row += 1

# Save the workbook
workbook.close()

The Workbook() function creates a new Excel file, while add_worksheet() creates a worksheet inside it. The write() method places each name into a separate row. Finally, close() saves and closes the workbook.

2. Writing Excel Files with openpyxl

openpyxl can also be used to create new workbooks and modify existing Excel files. It is useful when you need to read, update, and save workbook data through Python.

For example:

from openpyxl import Workbook

# Create a new workbook
workbook = Workbook()

# Select the active worksheet
sheet = workbook.active

# Write data into the first cell
sheet["A1"] = "Hello, Excel!"

# Save the workbook
workbook.save("new_example.xlsx")

This program creates a new workbook and selects its active worksheet. The text Hello, Excel! is placed into cell A1. The save() method then stores the workbook as new_example.xlsx.

Python Libraries for Excel Files

Library	Main Purpose	Common Use
xlrd	Reading Excel workbooks	Accessing spreadsheet data
pandas	Reading and analyzing Excel data	DataFrame-based processing
openpyxl	Reading and writing .xlsx files	Creating and modifying workbooks
xlsxwriter	Creating Excel files	Writing, formatting, and generating spreadsheet content

Why Use Python with Excel?

Combining Python with Excel can make spreadsheet-based workflows more efficient. Instead of manually entering or updating information, Python programs can automate many repetitive operations. This can be useful when working with large datasets or when the same spreadsheet task needs to be performed regularly.

For example, pandas can help load Excel information into a structured DataFrame for analysis, while openpyxl can be used when direct workbook modification is required. Similarly, xlsxwriter can be useful when creating new spreadsheets that require formatting and additional Excel features.

Conclusion

Python provides several practical ways to read and write Excel files. Libraries such as xlrd, pandas, openpyxl, and xlsxwriter serve different purposes and can be selected according to the requirements of your application.

If your main goal is data analysis, pandas provides a convenient DataFrame-based approach. For working directly with .xlsx workbooks, openpyxl offers useful reading and writing functionality. When creating Excel files with formatting and spreadsheet features, xlsxwriter can be helpful.

By learning these libraries, Python developers can automate Excel-related tasks, process spreadsheet data, create new workbooks, and build more efficient data workflows.

Complete Advance AI Topics: Click Here
SQL Tutorial: Click Here
YT:- DecodeIT

Frequently Asked Questions

1. Can Python read Excel files?

Yes. Python can read Excel files using libraries such as xlrd, pandas, and openpyxl.

2. Which Python library is commonly used for reading Excel data?

pandas is commonly used when Excel data needs to be loaded into a DataFrame for analysis and processing.

3. Can pandas write Excel files?

Yes. Pandas can be used for Excel data processing and can also export DataFrame data to Excel files.

4. What is openpyxl used for?

openpyxl is used to read, create, update, and save Excel .xlsx workbooks.

5. How do I install openpyxl?

You can install it using the command pip install openpyxl.

6. What is xlsxwriter used for?

xlsxwriter is used to create Excel .xlsx files and supports features such as writing data, formatting, charts, and conditional formatting.

7. How can Python write text into an Excel cell?

With openpyxl, you can assign text directly to a cell, such as sheet["A1"] = "Hello, Excel!".

8. Can Python automate Excel tasks?

Yes. Python libraries can help automate tasks such as reading spreadsheet data, creating workbooks, writing values, modifying sheets, and processing datasets.

Keywords: Python Read and Write Excel Files, reading excel file in Python using pandas, Python read Excel file without pandas, read specific cell in Excel Python pandas, openpyxl write to Excel, read and write Excel file in Python using pandas, openpyxl Python, Python Excel, openpyxl read Excel