Python数据清洗脚本开发：保留格式复制Excel Sheet2至输出文件

Ah, I’ve been right where you are—pandas is a powerhouse for data wrangling, but it completely ignores Excel-specific details like formatting, empty top rows, and formulas when you convert sheets to DataFrames. The solution here is to use a library that interacts directly with the Excel file’s underlying structure instead of flattening everything into tabular data. Let’s go with openpyxl—it’s built for exactly this kind of .xlsx file manipulation.

Step 1: Install the Required Library #

First, make sure you have openpyxl installed:

pip install openpyxl

Step 2: The Script That Keeps Everything Intact #

This script will copy Sheet2 exactly as it exists in your original file—no lost formatting, no broken formulas, no missing empty rows:

from openpyxl import load_workbook

# Load the original Excel file
original_workbook = load_workbook('RawFile.xlsx')
# Grab the Sheet2 we need to duplicate
target_sheet = original_workbook['Sheet2']

# Create a brand new empty workbook
output_workbook = load_workbook(filename=None)
# Remove the default "Sheet" that gets auto-created
output_workbook.remove(output_workbook.active)

# Copy the entire Sheet2 to the new workbook (preserves all elements)
output_workbook.copy_worksheet(target_sheet)

# Save the final output file
output_workbook.save('OutputFile.xlsx')

Why This Works (And Pandas Didn’t) #

Unlike pandas, which parses Excel data into a DataFrame (discarding non-data elements like formatting), openpyxl copies the entire worksheet object. This means it retains:

• All cell formulas (not just their calculated values)
• Full cell formatting (fonts, colors, borders, alignment, etc.)
• Empty rows and columns
• Merged cells
• Any other Excel-specific elements you need to keep

Quick Notes for Edge Cases #

• For macro-enabled files (.xlsm), add keep_vba=True when loading the original workbook to preserve macros:

  original_workbook = load_workbook('RawFile.xlsm', keep_vba=True)
  

• This method only works for .xlsx/.xlsm files. If you’re dealing with older .xls files, you’ll need to use libraries like xlrd/xlwt, though they have more limited formatting support.

内容的提问来源于stack exchange，提问作者N. P.