Python3.x win32com：复制工作簿中工作表的非表头已用单元格

Got it, let's adjust your code to skip the header rows when copying data from the 5 worksheets to the first one. Here's how to make it work properly:

The Core Issue #

Your current code copies the entire UsedRange, which includes the header row. We need to modify the range we copy to start from the second row of the used range, and also add logic to paste the data into the first worksheet at the correct position (since your original code only copies, no paste step).

Modified Code with Explanations #

import win32com.client as win32

excel = win32.gencache.EnsureDispatch('Excel.Application')
wb = excel.Workbooks.Open(mergedXL)
ws_target = wb.Sheets(1)  # Renamed for clarity
sheet_list = wb.Sheets

for ws in sheet_list:
    if ws.Name != "1st sheet":
        used_range = ws.UsedRange
        
        # Skip sheets that only have a header (no data rows)
        if used_range.Rows.Count <= 1:
            print(f"Skipping {ws.Name} - no data beyond header")
            continue
            
        # Define the range starting from row 2 (skip header)
        # Offset(1,0) moves the start down 1 row; Resize removes the first row from the count
        data_to_copy = used_range.Offset(1, 0).Resize(used_range.Rows.Count - 1, used_range.Columns.Count)
        
        print(f"Copying data from {ws.Name} (excluding header)")
        data_to_copy.Copy()
        
        # Find the next empty row in the target worksheet
        # -4162 is the value for Excel's xlUp constant
        last_used_row = ws_target.Cells(ws_target.Rows.Count, 1).End(-4162).Row
        
        # Paste the data (adjust paste type as needed)
        # -4163 = xlPasteValuesAndNumberFormats (keeps values and number formats)
        # Use -4104 = xlPasteAll if you want to copy formatting too
        ws_target.Cells(last_used_row + 1, 1).PasteSpecial(-4163)

# Optional: Save changes, close the workbook, and quit Excel
# wb.Save()
# wb.Close()
# excel.Quit()

Key Adjustments Explained #

1. Skip Header Rows:

   • used_range.Offset(1, 0) shifts the starting point of the range down by 1 row (skipping the header).
   • Resize(used_range.Rows.Count - 1, ...) adjusts the range size to exclude the first row, so we only copy data rows.

2. Avoid Empty Copies:

   • The check if used_range.Rows.Count <= 1 skips worksheets that only have a header (no actual data), preventing errors from copying an empty range.

3. Paste to Correct Position:

   • We find the last used row in the target sheet using End(-4162) (equivalent to pressing Ctrl+Up in Excel), then paste the data starting from the next empty row.
   • The PasteSpecial parameter lets you control what gets pasted—adjust based on whether you need values only, formatting, or everything.

4. Clarity:

   • Renamed variables like wsSIR to ws_target for readability, making the code easier to follow.

内容的提问来源于stack exchange，提问作者gistech007