131. Working with Excel Files

Working with Excel files in Python is a common task, and libraries like openpyxl and pandas can make it easy to manipulate Excel files (both .xls and .xlsx formats). Below are some examples using both libraries.

1. Using openpyxl to Create and Write Data to an Excel File

import openpyxl

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

# Writing data to cells
sheet['A1'] = 'Name'
sheet['B1'] = 'Age'
sheet['A2'] = 'Alice'
sheet['B2'] = 30
sheet['A3'] = 'Bob'
sheet['B3'] = 25

# Save the workbook to a file
workbook.save('people.xlsx')

print("Excel file created and data written.")

---

2. Reading Data from an Excel File with openpyxl

import openpyxl

# Load the existing Excel file
workbook = openpyxl.load_workbook('people.xlsx')

# Select the active sheet
sheet = workbook.active

# Reading data from specific cells
name = sheet['A2'].value
age = sheet['B2'].value

print(f"Name: {name}, Age: {age}")

---

3. Using pandas to Read an Excel File

import pandas as pd

# Read an Excel file into a DataFrame
df = pd.read_excel('people.xlsx')

# Display the contents of the Excel file
print(df)

---

4. Using pandas to Write Data to an Excel File

import pandas as pd

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

# Write DataFrame to an Excel file
df.to_excel('people_from_pandas.xlsx', index=False)

print("Data written to Excel file using pandas.")

---

5. Modifying Existing Excel Files with openpyxl

import openpyxl

# Load an existing Excel file
workbook = openpyxl.load_workbook('people.xlsx')

# Select the active sheet
sheet = workbook.active

# Modify data in a specific cell
sheet['B2'] = 31  # Change Alice's age to 31

# Save the workbook with the changes
workbook.save('people_modified.xlsx')

print("Excel file modified and saved.")

---

6. Appending Rows to an Existing Excel File with openpyxl

import openpyxl

# Load the existing Excel file
workbook = openpyxl.load_workbook('people.xlsx')

# Select the active sheet
sheet = workbook.active

# Append new rows of data
new_data = [('David', 40), ('Eva', 28)]
for row in new_data:
    sheet.append(row)

# Save the workbook with the new data
workbook.save('people_appended.xlsx')

print("New rows appended to the Excel file.")

---

7. Working with Multiple Sheets in an Excel File using openpyxl

import openpyxl

# Create a new workbook and add multiple sheets
workbook = openpyxl.Workbook()

# Create multiple sheets
sheet1 = workbook.create_sheet('Sheet 1')
sheet2 = workbook.create_sheet('Sheet 2')

# Write data to the first sheet
sheet1['A1'] = 'Name'
sheet1['B1'] = 'Age'
sheet1.append(['Alice', 30])

# Write data to the second sheet
sheet2['A1'] = 'Product'
sheet2['B1'] = 'Price'
sheet2.append(['Laptop', 1000])

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

print("Excel file with multiple sheets created.")

---

8. Reading Multiple Sheets with pandas

import pandas as pd

# Read an Excel file with multiple sheets
df = pd.read_excel('multiple_sheets.xlsx', sheet_name=None)

# Print data from each sheet
for sheet_name, data in df.items():
    print(f"Data from sheet: {sheet_name}")
    print(data)

---

9. Applying Formatting to Excel Cells using openpyxl

import openpyxl
from openpyxl.styles import Font, Color, Alignment

# Create a new workbook
workbook = openpyxl.Workbook()

# Select the active sheet
sheet = workbook.active

# Write data
sheet['A1'] = 'Name'
sheet['B1'] = 'Age'

# Apply styles
sheet['A1'].font = Font(bold=True, color="FF0000")  # Red, bold text
sheet['B1'].font = Font(bold=True)

# Center align the text in both cells
sheet['A1'].alignment = Alignment(horizontal="center")
sheet['B1'].alignment = Alignment(horizontal="center")

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

print("Excel file with styles applied.")

---

10. Using pandas for Filtering and Writing Data to an Excel File

import pandas as pd

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

# Filter data: Select rows where age is greater than 30
filtered_df = df[df['Age'] > 30]

# Write the filtered DataFrame to an Excel file
filtered_df.to_excel('filtered_data.xlsx', index=False)

print("Filtered data written to Excel file.")

---

Key Points:

• openpyxl: A powerful library to create, read, and modify Excel files (.xlsx). It allows cell-level manipulation and formatting.

• pandas: Provides higher-level, fast manipulation of Excel data via DataFrame objects. It is useful for working with large datasets.

• Writing/Reading Data: Both libraries offer easy-to-use functions to read from and write to Excel files.

• Multiple Sheets: Both openpyxl and pandas support working with multiple sheets in a workbook.