Untitled
unknown
plain_text
a year ago
7.1 kB
13
Indexable
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.")
Editor is loading...
Leave a Comment