Reading and Writing Excel Files (openpyxl, pandas)

📊 Reading and Writing Excel Files with Python: A Guide Using openpyxl and pandas

Working with Excel files is a common task in data analysis, business intelligence, and automation workflows. In Python, two popular libraries make this easy and efficient: openpyxl and pandas. In this post, we’ll dive into how to read from and write to Excel files using both libraries.

---

🔧 Tools We’ll Use

• pandas: Ideal for high-level data manipulation (think: DataFrames).

• openpyxl: Great for lower-level Excel file manipulation, especially with formatting, formulas, and styles.

---

🔹 Installing the Required Libraries

Before we begin, install the libraries using pip:

pip install pandas openpyxl

---

📥 Reading Excel Files

Using pandas

Reading Excel files with pandas is straightforward:

import pandas as pd

# Read the first sheet
df = pd.read_excel('example.xlsx')

# Read a specific sheet by name
df_sheet = pd.read_excel('example.xlsx', sheet_name='SalesData')

# View the data
print(df.head())

Using openpyxl

If you need more control over the workbook structure or formatting:

from openpyxl import load_workbook

# Load workbook
wb = load_workbook('example.xlsx')

# Select a sheet
sheet = wb['SalesData']

# Read a specific cell
value = sheet['B2'].value
print(f'Value in B2: {value}')

---

📤 Writing Excel Files

Using pandas

# Sample DataFrame
data = {
    'Name': ['Alice', 'Bob'],
    'Age': [25, 30]
}
df = pd.DataFrame(data)

# Write to Excel
df.to_excel('output.xlsx', index=False)

You can also write to specific sheets:

with pd.ExcelWriter('multi_sheet.xlsx', engine='openpyxl') as writer:
    df.to_excel(writer, sheet_name='Sheet1', index=False)
    df.to_excel(writer, sheet_name='Sheet2', index=False)

Using openpyxl

Create a new Excel file and write data manually:

from openpyxl import Workbook

# Create a workbook and select active sheet
wb = Workbook()
ws = wb.active

# Write data
ws['A1'] = 'Name'
ws['B1'] = 'Age'
ws.append(['Alice', 25])
ws.append(['Bob', 30])

# Save the file
wb.save('manual_output.xlsx')

---

🎨 Formatting with openpyxl

Need to style your sheet? Here’s an example:

from openpyxl.styles import Font, PatternFill

# Bold header and fill color
ws['A1'].font = Font(bold=True)
ws['B1'].fill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid')

---

🤔 When to Use What?

Task	Recommended Tool
Read/Write large tables	pandas
Work with formulas, styles, multiple sheets	openpyxl
Automate reports with formatting	openpyxl
Quick data export/import	pandas

---

✅ Conclusion

Both openpyxl and pandas are powerful tools for Excel manipulation. Use pandas for data wrangling and openpyxl for advanced Excel tasks like formatting and formulas. Together, they cover most Excel-related workflows in Python.

---

Have questions or cool Excel automation tricks? Share them in the comments below!