Python处理Excel层级目录代码时无法正确追加描述求助
2026-6-27
问题：用openpyxl处理Excel层级目录代码，追加对应描述列
我正在编写Python脚本，使用openpyxl库处理包含层级目录代码及描述的Excel数据，需要将目录各层级对应的描述追加至相邻列。
原始Excel数据示例
| A | B | C | D |
|---|---|---|---|
| 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 |
目前无法正确实现层级描述的追加，以下是简化后的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.")
问题分析与修正方案
1. 核心错误：目标代码拼接错误
Excel每行的目录代码分散在A-D列（如F、1、10、08），需要拼接成完整的F11008才能匹配目录结构中的代码。原脚本仅取第一列的值作为target_code，导致无法定位到对应的子目录。
2. 层级描述收集逻辑错误
原脚本错误地先添加了空的description字段，遍历收集名称后的反转逻辑也导致输出顺序不符合预期。
修正后的关键代码
修改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] # 拼接完整的目标目录代码 target_code = ''.join(directory_info) directory = find_directory(root_directory, target_code) if directory: # 从当前节点向上遍历父节点，收集层级名称 descriptions = [] current_directory = directory while current_directory: descriptions.append(current_directory.name) current_directory = current_directory.parent # 反转得到从根到当前节点的层级顺序 descriptions.reverse() 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
额外优化建议
可以提前构建代码到目录对象的字典，替代递归查找，提升处理效率：
# 在初始化目录后构建映射字典 code_to_dir = {} def build_code_map(directory): code_to_dir[directory.code] = directory for subdir in directory.subdirectories: build_code_map(subdir) build_code_map(root_directory) # 查找时直接通过字典获取 directory = code_to_dir.get(target_code)
内容的提问来源于stack exchange，提问作者Heskinammo
相关产品推荐
相关产品推荐
