When I first started working with Excel files in Python, I quickly realized that not all libraries could do everything. Some were great for writing data, while others were perfect for reading.
After more than a decade of working with Python for data automation, I’ve used almost every Excel-related library available, from XlsxWriter to openpyxl and pandas.
In this tutorial, I’ll walk you through how to read Excel files in Python using XlsxWriter (and a few alternatives). I’ll also share some practical examples that I’ve used in real-world projects here in the USA, especially when working with financial reports and sales data.
Understand XlsxWriter in Python
Before we jump into reading Excel files, it’s important to understand what XlsxWriter actually does. XlsxWriter is a Python module that allows you to create and write Excel files (.xlsx). It’s fast, reliable, and supports advanced features like formatting, charts, conditional formatting, and formulas.
However, one key limitation is that XlsxWriter cannot read existing Excel files. It’s a write-only library.
But don’t worry, I’ll show you how to handle this limitation by combining XlsxWriter with other Python libraries like openpyxl or pandas to read and then modify Excel files.
Install XlsxWriter in Python
Before we start, make sure you have XlsxWriter installed. If not, you can install it using pip.
pip install XlsxWriterThis command installs the latest version of XlsxWriter in your Python environment.
I always recommend using a virtual environment for Python projects, especially when you’re dealing with multiple dependencies.
Method 1 – Create and Write Excel Files Using Python XlsxWriter
Let’s start with the part XlsxWriter does best, creating and writing Excel files.
Here’s a simple example where I create a new Excel file containing sales data for a few U.S. states.
import xlsxwriter
# Create a new Excel file
workbook = xlsxwriter.Workbook('us_sales_data.xlsx')
worksheet = workbook.add_worksheet('Sales Report')
# Add headers
headers = ['State', 'Month', 'Sales ($)']
worksheet.write_row('A1', headers)
# Add some sample sales data
sales_data = [
['California', 'January', 15000],
['Texas', 'January', 12000],
['New York', 'January', 18000],
['Florida', 'January', 10000],
['Illinois', 'January', 9000],
]
# Write data to worksheet
row = 1
for state, month, sales in sales_data:
worksheet.write_row(row, 0, [state, month, sales])
row += 1
# Apply formatting
bold = workbook.add_format({'bold': True})
worksheet.set_row(0, None, bold)
# Add a total formula
worksheet.write(row, 1, 'Total')
worksheet.write_formula(row, 2, '=SUM(C2:C6)')
# Save and close the workbook
workbook.close()
print("Excel file 'us_sales_data.xlsx' created successfully!")You can see the output in the screenshot below.
This Python code creates a new Excel file named us_sales_data.xlsx and writes sales data for different states. I often use this approach when generating automated reports or dashboards for clients.
Method 2 – Read Excel Files in Python (XlsxWriter Alternative)
As I mentioned earlier, XlsxWriter cannot read existing Excel files. To read Excel data, I usually combine XlsxWriter with openpyxl or pandas. These libraries are excellent for reading and manipulating Excel data in Python.
Let’s look at both approaches.
1. Use openpyxl to read Excel Files in Python
The openpyxl library is one of my favorites when I need to read and update Excel files.
Here’s how you can use it:
pip install openpyxlNow, let’s write a Python script to read the Excel file we created earlier.
from openpyxl import load_workbook
# Load the existing Excel file
workbook = load_workbook('us_sales_data.xlsx')
sheet = workbook['Sales Report']
# Read and print data
print("Reading Excel data using openpyxl:")
for row in sheet.iter_rows(values_only=True):
print(row)You can see the output in the screenshot below.
This code loads the us_sales_data.xlsx file and prints each row’s content.
I’ve used this method in many data-cleaning projects, especially when I needed to extract data from Excel sheets and then rewrite updated results using XlsxWriter.
2. Use pandas to read Excel Files in Python
If you’re working with large datasets, pandas is the most efficient way to read Excel files in Python.
First, install pandas:
pip install pandas openpyxlNow, let’s read the same Excel file using pandas.
import pandas as pd
# Read the Excel file
df = pd.read_excel('us_sales_data.xlsx', sheet_name='Sales Report')
# Display the data
print("Reading Excel data using pandas:")
print(df)This method is extremely popular in data science and analytics. In my experience, pandas is ideal when you want to perform data analysis, filtering, or visualization after reading Excel files.
Method 3 – Combine pandas and XlsxWriter in Python
One of the most powerful combinations I’ve used in my professional projects is reading Excel data with pandas and writing results back using XlsxWriter.
Here’s a complete example that reads sales data, calculates total sales, and writes the results to a new Excel file.
import pandas as pd
# Step 1: Read the Excel file
df = pd.read_excel('us_sales_data.xlsx', sheet_name='Sales Report')
# Step 2: Perform some data analysis
total_sales = df['Sales ($)'].sum()
average_sales = df['Sales ($)'].mean()
# Step 3: Add summary data
summary_df = pd.DataFrame({
'Metric': ['Total Sales', 'Average Sales'],
'Value': [total_sales, average_sales]
})
# Step 4: Write both original and summary data to a new Excel file
with pd.ExcelWriter('us_sales_summary.xlsx', engine='xlsxwriter') as writer:
df.to_excel(writer, sheet_name='Sales Data', index=False)
summary_df.to_excel(writer, sheet_name='Summary', index=False)
print("Excel summary file created successfully!")You can see the output in the screenshot below.
This Python script reads an existing Excel file, performs calculations, and writes the results to a new workbook.
I often use this approach in my consulting projects when I need to automate Excel-based reporting workflows for American retail clients.
Method 4 – Read Specific Columns or Rows in Python
Sometimes, you might not need all the data from an Excel file. You can easily read specific columns or rows using pandas.
import pandas as pd
# Read only specific columns
df = pd.read_excel('us_sales_data.xlsx', usecols=['State', 'Sales ($)'])
# Display the filtered data
print("Filtered data:")
print(df.head())You can see the output in the screenshot below.
This method helps reduce memory usage when dealing with large Excel files, something I’ve faced often while processing monthly sales data for multiple U.S. regions.
Method 5 – Read Multiple Excel Sheets in Python
If your Excel file contains multiple sheets, pandas makes it easy to read them all at once.
import pandas as pd
# Read all sheets
sheets = pd.read_excel('us_sales_data.xlsx', sheet_name=None)
# Print sheet names and data
for sheet_name, data in sheets.items():
print(f"\nSheet: {sheet_name}")
print(data.head())Using this method, you can loop through all sheets and process them dynamically. I’ve used this in projects where clients had separate sheets for each U.S. state’s sales data.
Practical Use Case – Automate Excel Reports in Python
Let’s look at a real-world example.
Suppose you receive weekly Excel reports from different U.S. branches, California, Texas, and New York. You want to combine them into one master report automatically.
Here’s how you can do it:
import pandas as pd
import glob
# Get all Excel files in the folder
files = glob.glob('weekly_reports/*.xlsx')
# Combine all files into one DataFrame
combined_data = pd.concat([pd.read_excel(file) for file in files])
# Write the combined data to a new Excel file
combined_data.to_excel('master_sales_report.xlsx', index=False, engine='xlsxwriter')
print("Master sales report created successfully!")This Python script automatically merges multiple Excel files into one consolidated report. I’ve used a similar script for automating regional sales reports for clients in the retail industry.
Conclusion
Working with Excel files in Python is easier than ever, thanks to libraries like XlsxWriter, openpyxl, and pandas. While XlsxWriter focuses on writing Excel files, combining it with pandas gives you the best of both worlds: reading, analyzing, and writing Excel data effortlessly.
If you’re working with business reports or automating Excel workflows in the U.S., this approach will save you hours every week.
You may also like to read:
- Leap Year Program in Python using Function
- Python Function to Find the Max of Three Numbers
- Palindrome Number in Python Using Function
- Python Return Function
I am Bijay Kumar, a Microsoft MVP in SharePoint. Apart from SharePoint, I started working on Python, Machine learning, and artificial intelligence for the last 5 years. During this time I got expertise in various Python libraries also like Tkinter, Pandas, NumPy, Turtle, Django, Matplotlib, Tensorflow, Scipy, Scikit-Learn, etc… for various clients in the United States, Canada, the United Kingdom, Australia, New Zealand, etc. Check out my profile.
