Python和Excel中更改未保存

Question

我正在写一个Python脚本，用来处理包含目录代码和描述的Excel数据。每个目录都有一个层级结构，我需要把每个层级对应的描述添加到旁边的列里。

这里有一个Excel数据的例子：

|  A  |  B  |  C  |  D  |
--------------------------
|  F  |  1  |  10 |  08 |

我想根据目录的层级结构来添加目录描述。比如，对于目录代码“F 1 10 08”，我希望得到的结果是：

|  A  |  B  |  C  |  D  |       E       |              F               |                  G                   |             H                |
---------------------------------------------------------------------------------------------------------
|  F  |  1  |  10 |  08 |  Financial  | Revenue (including Recoveries) | Revenue – Service Recipient Services | Funding-LHINs One Time  |

我尝试使用openpyxl库来实现这个功能，但在根据层级结构正确添加描述时遇到了一些困难。

这是我Python脚本的一个简化版本：

import openpyxl

class Directory:
    def __init__(self, code, name, description="", parent=None):
        self.code = code
        self.name = name
        self.description = description
        self.parent = parent
        self.subdirectories = []

    def add_subdirectory(self, subdirectory):
        subdirectory.parent = self
        self.subdirectories.append(subdirectory)


def find_directory(directory, target_code):
    if directory.code == target_code:
        return directory
    for subdirectory in directory.subdirectories:
        result = find_directory(subdirectory, target_code)
        if result:
            return result
    return None

def process_excel_file(excel_file, root_directory):
    try:
        wb = openpyxl.load_workbook(excel_file)
        sheet = wb.active
        data_rows = sheet.iter_rows(min_row=2, min_col=1, max_col=4, values_only=True)
        processed_data = []
        for row in data_rows:
            directory_info = [str(cell).strip() if cell else "" for cell in row]
            target_code = directory_info[0]
            directory = find_directory(root_directory, target_code)
            if directory:
                descriptions = [directory.description]
                current_directory = directory
                while current_directory:
                    descriptions.append(current_directory.name)
                    current_directory = current_directory.parent
                descriptions.reverse()
                processed_row = directory_info + descriptions[1:]
                processed_data.append(processed_row)
            else:
                print(f"Directory {target_code} not found.")
        return processed_data
    except Exception as e:
        print(f"Error occurred while processing Excel file: {e}")
        return None

if __name__ == "__main__":
    root_directory = Directory("F", "Financial")

    directories = [
        Directory("F1", "Revenue (including Recoveries)"),
        Directory("F3", "Compensation Expenses"),
        Directory("F4", "Supplies Expenses"),
        Directory("F5", "Service Recipient Specific Supplies"),
        Directory("F6", "Sundry Expenses"),
        Directory("F7", "Equipment Expenses"),
        Directory("F8", "Contracted-Out Services"),
        Directory("F9", "Building and Grounds Expenses")
    ]

    for directory in directories:
        root_directory.add_subdirectory(directory)

    subdirectories_F1 = [
        Directory("F110", "Revenue – Service Recipient Services"),
        Directory("F112", "Envelope Revenue"),
        Directory("F120", "Recoveries-External"),
        Directory("F121", "Recoveries-Internal"),
        Directory("F122", "Recoveries-Interdepartmental"),
        Directory("F130", "Contributed Services"),
        Directory("F131", "Amortized Donation Contributed Services"),
        Directory("F140", "Donations"),
        Directory("F141", "Amortized Donation Revenue"),
        Directory("F150", "Grants"),
        Directory("F151", "Amortized Grant Revenue"),
        Directory("F160", "Investment Revenue"),
        Directory("F165", "Unrealized Gain/Loss"),
        Directory("F170", "Revenue Transferred From Other Funds"),
        Directory("F190", "Other Revenue")
    ]

    for subdir in subdirectories_F1:
        directory_F1 = find_directory(root_directory, "F1")
        if directory_F1:
            subdir.parent = directory_F1
            directory_F1.add_subdirectory(subdir)
        else:
            print("Directory F1 not found.")

    subdirectories_F110 = [
        Directory("F11004", "Funding – LHIN Quality Based Procedures (QBP)"),
        Directory("F11005", "Funding – LHIN Health Based Allocation Model (HBAM)"),
        Directory("F11006", "Funding-Local Health Integration Networks (LHIN)"),
        Directory("F11007", "Funding-Cancer Care Ontario"),
        Directory("F11008", "Funding-LHINs One Time"),
        Directory("F11010", "Funding-Provincial MOHLTC (Allocation)"),
        Directory("F11011", "Funding-Provision for Recoveries"),
        Directory("F11012", "Funding-MOHLTC One Time"),
        Directory("F11013", "Funding-MOHLTC Pay Equity Funding"),
        Directory("F11014", "Funding-MOHLTC Other Funding Envelopes"),
        Directory("F11015", "Funding-Other MOHLTC (Care & Provider Services)"),
        Directory("F11016", "OHIP-Professional Component"),
        Directory("F11017", "OHIP-Technical Component"),
        Directory("F11019", "Funding – Paymaster / Flow Through"),
        Directory("F11020", "Funding-Provincial Government-Other (Usually Fund Type 3)"),
        Directory("F11022", "Funding-MC/MCY Fiscal Subsidy"),
        Directory("F11030", "Funding-Federal Government (Sum of 1-10-31 to 1-10-34) (Usually Fund Type 3)"),
        Directory("F11031", "Funding-Department of Veteran's Affairs"),
        Directory("F11034", "Funding-Other Federal Government"),
        Directory("F11040", "Funding-Municipal Government (Usually Fund Type 3)"),
        Directory("F11045", "Funding-Non Government (Usually Fund Type 3)"),
        Directory("F11050", "R Rev. Worker Safety and Insurance Board (WIB)"),
        Directory("F11060", "R REV. NON-RESIDENT OF PROVINCE REVENUE"),
        Directory("F11061", "R Rev. Quebec or Manitoba Provincial Health Ins. Plan"),
        Directory("F11063", "R Rev. Private Health Ins. /Out-of-Pocket (self-pay)"),
        Directory("F11064", "R Rev. All other Provincial Health Insurance Plan"),
        Directory("F11065", "R Rev. Non-Residents of Canada"),
        Directory("F11070", "R Rev. Uninsured Residents of Ontario"),
        Directory("F11080", "R Rev. Insured Residents – Private Health Insurance/Out-of-Pocket (self-pay)"),
        Directory("F11090", "R Rev. Other Service Recipient Payment Sources"),
        Directory("F11091", "R Rev.-Partial Basic Accommodation Fees"),
        Directory("F11092", "R Rev.-Full Basic Accommodation Fees"),
        Directory("F11093", "R Rev.-Preferred Accommodation Fees"),
        Directory("F11099", "R Rev.-Client Fee Waived (CSS Sector)")
    ]

    for subdir in subdirectories_F110:
        directory_F110 = find_directory(root_directory, "F110")
        if directory_F110:
            subdir.parent = directory_F110
            directory_F110.add_subdirectory(subdir)
        else:
            print("Directory F110 not found.")

    excel_file = "Test 1.xlsx"
    processed_data = process_excel_file(excel_file, root_directory)

    if processed_data:
        try:
            new_wb = openpyxl.Workbook()
            new_sheet = new_wb.active
            for row_index, data_row in enumerate(processed_data, start=1):
                for col_index, cell_value in enumerate(data_row, start=1):
                    new_sheet.cell(row=row_index, column=col_index, value=cell_value)
            new_wb.save("codes output.xlsx")
            print("Excel file saved as 'codes output.xlsx' successfully!")
        except Exception as e:
            print(f"Error occurred while processing Excel file: {e}")
    else:
        print("No data to process or error occurred during data processing.")

任何帮助或指导都将非常感谢，谢谢！

Answer 1

我更新了你的 process_excel_file 函数，现在它可以正常工作了：

def process_excel_file(excel_file, root_directory):
    try:
        wb = openpyxl.load_workbook(excel_file)
        sheet = wb.active
        data_rows = sheet.iter_rows(min_row=2, min_col=1, max_col=4, values_only=True)
        processed_data = []
        for row in data_rows:
            directory_info = [str(cell).strip() if cell else "" for cell in row]
            level = 0
            target_code = directory_info[level]
            directory = find_directory(root_directory, target_code)
            if directory:
                descriptions = [directory.name]
                current_directory = directory
                while level < len(directory_info) - 1:
                    level += 1
                    target_code += directory_info[level]
                    current_directory = find_directory(current_directory, target_code)
                    if not current_directory:
                        break
                    descriptions.append(current_directory.name)

                processed_row = directory_info + descriptions
                processed_data.append(processed_row)
            else:
                print(f"Directory {target_code} not found.")
        return processed_data
    except Exception as e:
        print(f"Error occurred while processing Excel file: {e}")
        return None

主要的更新在这些代码行上：