Maintaining Excel Donor Worksheets: Keeping References Local to File

Title: Working with Excel Files: Merging Two Spreadsheets with Multiple Worksheets

In this article, we will discuss how to merge two Excel files with multiple worksheets. We will use the openpyxl library in Python to read, manipulate, and write the Excel files.

Prerequisites

• Python 3.x
• openpyxl library

Installation

Install the openpyxl library using pip:

pip install openpyxl

Merging Two Excel Files

Let's assume we have two Excel files: donor.xlsx and recipient.xlsx.

• donor.xlsx has two worksheets, and the first worksheet contains a large table.
• recipient.xlsx has dozens of worksheets.

We will merge the data from the first worksheet of the donor.xlsx file into the first worksheet of the recipient.xlsx file.

Importing Libraries and Reading Excel Files

from openpyxl import load_workbook

# Load the donor and recipient workbooks
donor_wb = load_workbook('donor.xlsx')
recipient_wb = load_workbook('recipient.xlsx')

Accessing the First Worksheet of Each Workbook

# Access the first worksheet of each workbook
donor_ws = donor_wb.active
recipient_ws = recipient_wb['Sheet1']

Reading Data from the Donor Worksheet

# Read data from the donor worksheet
donor_data = []
for row in donor_ws.iter_rows():
    donor_data.append([cell.value for cell in row])

Writing Data to the Recipient Worksheet

# Write data to the recipient worksheet starting from the second row (row 2)
for row in donor_data[1:]:
    recipient_ws.append(row)

Saving the Merged Excel File

# Save the merged Excel file
recipient_wb.save('merged_excel.xlsx')

Summary

• Use the openpyxl library to read, manipulate, and write Excel files in Python.
• Load the Excel files using the load_workbook() function.
• Access the worksheets using the active property or by specifying the worksheet name.
• Read data from a worksheet using the iter_rows() function.
• Write data to a worksheet using the append() function.
• Save the Excel file using the save() function.

References

• openpyxl - Read/Write Excel 2010 xlsx/xlsm/xltx/xltm files in Python
• Python Excel Tutorial: Reading and Writing Excel Files in Python
• Merging Multiple Excel Worksheets into One Using Python