How to Use pandas and openpyxl Together

So far, you have learned that pandas and openpyxl are specialized in handling tabular data.

pandas is excellent for processing and analyzing large datasets, while openpyxl allows for precise formatting of Excel files.

In this lesson, we will explore how to handle Excel files using these two libraries together.

The following code example demonstrates how to process data with pandas, save it to an Excel file using openpyxl, and apply additional formatting.

Copy
import pandas as pd
from openpyxl import load_workbook

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

# Save the DataFrame to an Excel file using pandas (create the file with ExcelWriter)
file_name = 'output_file.xlsx'

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

# Additional modifications using openpyxl
wb = load_workbook(file_name)  # Load the Excel file
ws = wb['Sheet1']  # Select the sheet to be modified

# For example, add a new value to cell A1
ws['A1'] = 'Updated Name'

# Save the Excel file
wb.save(file_name)

print("The task is completed")

The code above works as follows:

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

• A DataFrame is created using the pandas library.
• There are two columns, Name and Age, which store the names and ages respectively.

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

• Use ExcelWriter to save the DataFrame to an Excel file.
• file_name is the name of the Excel file, set to output_file.xlsx.
• engine='openpyxl': specifies openpyxl as the engine for handling the Excel file.
• The method to_excel() converts the DataFrame to an Excel file.
• index=False: excludes the index when saving.
• sheet_name='Sheet1': specifies "Sheet1" as the sheet name where the data will be saved.

Copy
wb = load_workbook(file_name)  # Load the Excel file
ws = wb['Sheet1']  # Select the sheet to be modified

• Load the previously created Excel file (output_file.xlsx) using openpyxl's load_workbook() method.
• wb refers to the Workbook object that represents the Excel file.
• ws = wb['Sheet1'] loads and selects the "Sheet1" sheet.
• ws refers to the Worksheet object representing the selected sheet.

Copy
ws['A1'] = 'Updated Name'

• ws['A1'] refers to cell A1 in the sheet.
• It changes the value of cell A1 to 'Updated Name'.
• This code overwrites any existing value in cell A1 (e.g., 'Name').

Copy
wb.save(file_name)

• Save the modified Excel file using the save() method.
• It updates the changes in the specified file_name, 'output_file.xlsx'.