基于父Excel按列条件拆分生成带指定前缀的Excel文件需求

Got it, let's get this sorted out. You're looking to take your large CCBHC_Monthly_Claims.xlsx file, filter rows where columnX matches a specific value (like i1, i2), and save each filtered dataset into a new Excel file named with that value as a prefix (e.g., i1_CCBHC_MONTHLY_CLAIMS.XLSX).

Here's a complete, working version of the code that builds on your existing snippet, using xlwings (since you're already using xw):

import os
import xlwings as xw

# Define your parent file and target sheet
parent_filename = 'CCBHC_Monthly_Claims.xlsx'
sheet_name = 'CCBHC_DATA'

# Check if the parent file exists
if os.path.isfile(parent_filename):
    # Open the parent workbook in background mode (visible=False to speed things up)
    with xw.Book(parent_filename, visible=False) as wb:
        ws = wb.sheets[sheet_name]
        
        # Get the full range of data (assuming your data starts at A1 and has headers)
        data_range = ws.used_range
        data = data_range.value
        headers = data[0]
        
        # Find the index of columnX (replace 'columnX' with your actual column name)
        columnX_name = 'columnX'  # Update this to your real column header
        try:
            columnX_index = headers.index(columnX_name)
        except ValueError:
            print(f"Error: Column '{columnX_name}' not found in headers!")
            exit()
        
        # Get all unique values from columnX (skip the header row)
        columnX_values = [row[columnX_index] for row in data[1:]]
        unique_values = list(set(columnX_values))
        
        # Loop through each unique value to create filtered files
        for value in unique_values:
            # Filter rows: keep header + rows where columnX matches the current value
            filtered_data = [headers] + [row for row in data[1:] if row[columnX_index] == value]
            
            # Create a new workbook for this filtered data
            with xw.Book() as new_wb:
                new_ws = new_wb.sheets[0]
                # Write the filtered data to the new sheet
                new_ws.range('A1').value = filtered_data
                
                # Define the output filename (use the value as prefix)
                output_filename = f"{value}_CCBHC_MONTHLY_CLAIMS.XLSX"
                # Save the new file (overwrite if it exists)
                new_wb.save(output_filename)
                print(f"Successfully created {output_filename}")
                
        print("All filtered files generated!")
else:
    print(f"Error: Parent file '{parent_filename}' not found in the current directory.")

Key Notes & Customizations: #

• Update columnX_name: Replace the string 'columnX' with the actual header name of the column you want to filter on (e.g., 'ClaimID' or 'Category').
• Background Processing: Using visible=False when opening the parent workbook makes the process faster and avoids Excel windows popping up.
• Handling Large Files: If your parent file is extremely large, consider using pandas alongside xlwings for more efficient data handling. Here's a quick alternative snippet using pandas:

import pandas as pd
import os

parent_filename = 'CCBHC_Monthly_Claims.xlsx'
sheet_name = 'CCBHC_DATA'
columnX_name = 'columnX'  # Update this

if os.path.isfile(parent_filename):
    # Read the entire sheet into a DataFrame
    df = pd.read_excel(parent_filename, sheet_name=sheet_name)
    
    # Get unique values from columnX
    unique_values = df[columnX_name].unique()
    
    for value in unique_values:
        # Filter the DataFrame
        filtered_df = df[df[columnX_name] == value]
        # Save to new file
        output_filename = f"{value}_CCBHC_MONTHLY_CLAIMS.XLSX"
        filtered_df.to_excel(output_filename, index=False)
        print(f"Created {output_filename}")
else:
    print(f"Parent file not found: {parent_filename}")

This pandas approach is often faster for very large datasets since it's optimized for tabular data processing.

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