将Excel列数据从一个工作表复制到已有工作簿的工作表中

Question

我正在尝试自动化一个过程，就是把Excel表格中的数据从一个工作表复制到一个已有的主Excel文件中的工作表里。我需要把数据复制到主文件中每个对应的工作表。我决定在我的Python代码中加入映射功能来实现这个目标。此外，我还希望这个脚本能在有Excel文件放到项目目录时自动触发，不过现在这部分不是重点。

当我运行这个文件时，它没有错误，运行得很好，但就是没有把列数据复制到主Excel文件里，我也搞不清楚为什么不行。有没有人能给我指条明路？非常感谢大家的帮助。

import openpyxl
import os
from watchdog.observers import Observer
from watchdog.events import FileSystemEventHandler

# Set the list of file names and corresponding worksheet/column mappings
file_mappings = {
    "Adams_Survey.xlsx": [
        {"source_worksheet": "1 General County Information", "target_worksheet": "1 General County Information", "column": "B"},
        {"source_worksheet": "2 Special Events-Exposure", "target_worksheet": "2 Special Events-Exposure", "column": "B"},
        {"source_worksheet": "3 Fair", "target_worksheet": "3 Fair", "column": "B"},
        {"source_worksheet": "4 Utilities", "target_worksheet": "4 Utilities", "column": "B"},
        {"source_worksheet": "5 Public Works", "target_worksheet": "5 Public Works", "column": "B"},
        {"source_worksheet": "6 Solid Waste - Facilities", "target_worksheet": "6 Solid Waste - Facilities", "column": "B"},
        {"source_worksheet": "7 Parks and Recreation", "target_worksheet": "7 Parks and Recreation", "column": "B"},
        {"source_worksheet": "8 Health Department", "target_worksheet": "8 Health Department", "column": "B"},
        {"source_worksheet": "9 Law Enforcement", "target_worksheet": "9 Law Enforcement", "column": "B"},
        {"source_worksheet": "10 Human Resources - Employment", "target_worksheet": "10 Human Resources - Employment", "column": "B"},
        {"source_worksheet": "11 Cyber Security", "target_worksheet": "11 Cyber Security", "column": "B"},
        {"source_worksheet": "12 Emergency Management", "target_worksheet": "12 Emergency Management", "column": "B"}
    ],
    # Add more file mappings as needed
}

# Set the name of the existing workbook to paste the columns into
existing_workbook_name = "SurveySelfAssess.xlsx"

# Load the existing workbook
existing_wb = openpyxl.load_workbook(existing_workbook_name)

class FileHandler(FileSystemEventHandler):
    def on_created(self, event):
        if event.is_directory:
            return
        elif event.src_path.endswith('.xlsx'):
            file = os.path.basename(event.src_path)

            if file.startswith('~$') or file.startswith('$'):
                return
            
            # Check if the file has a mapping
            if file in file_mappings:
                mappings = file_mappings[file]

                # Load the workbook
                wb = openpyxl.load_workbook(file)

                for mapping in mappings:
                    source_worksheet_name = mapping["source_worksheet"]
                    target_worksheet_name = mapping["target_worksheet"]
                    column_letter = mapping["column"]

                    # Get the source worksheet
                    ws = wb[source_worksheet_name]

                    # Find the column to copy based on the first row
                    col_to_copy = None
                    for col in range(1, ws.max_column + 1):
                        if ws.cell(row=1, column=col).value == "Column to Copy":
                            col_to_copy = col
                            break

                    # If the column to copy is found
                    if col_to_copy:
                        # Get the target worksheet
                        existing_ws = existing_wb[target_worksheet_name]

                        # Get the column number to paste into
                        existing_column_number = openpyxl.utils.column_index_from_string(column_letter)

                        # Copy the column values to the existing worksheet
                        for row in range(1, ws.max_row + 1):
                            existing_ws.cell(row=row, column=existing_column_number, value=ws.cell(row=row, column=col_to_copy).value)

                        print(f"Column 'Column to Copy' copied from '{file}', worksheet '{source_worksheet_name}' to column {column_letter} in '{existing_workbook_name}', worksheet '{target_worksheet_name}'")
                    else:
                        print(f"Column 'Column to Copy' not found in '{file}', worksheet '{source_worksheet_name}'")
            else:
                print(f"No mapping found for '{file}'")


if __name__ == "__main__":
    event_handler = FileHandler()
    observer = Observer()
    observer.schedule(event_handler, path='.', recursive=False)
    observer.start()
    try:
        while True:
            pass
    except KeyboardInterrupt:
        observer.stop()
    observer.join()

# Save the existing workbook after all operations
    existing_wb.save(existing_workbook_name)

自动化 excel 数据复制工作表脚本触发映射功能项目目录

将Excel列数据从一个工作表复制到已有工作簿的工作表中

1 个回答

撰写回答