单Sheet多标题Excel转对应标题CSV的实现问题咨询

Got it, let's fix this for you! The core problem with your existing code is that it’s collecting all non-empty rows into one list, which doesn’t account for the separate tables each with their own title. We need to identify where each table starts (via its title), gather the corresponding data rows, and then export each table as a CSV named after its title.

Why Your Original Code Failed #

Your code pulls all non-empty rows into a single list and converts it to a DataFrame, but this mixes data from all tables together. There’s no logic to split the sheet into individual tables, so the resulting DataFrame is either structured incorrectly or ends up empty if the filtering logic misses key rows.

Step-by-Step Solution #

Here’s a revised approach that targets your specific need:

1. Load the Excel sheet and iterate through every row.
2. Detect when a new table starts (by identifying its title row—we’ll use the first non-empty cell in a row as the title, but you can adjust this if your titles have unique formatting like bold text).
3. Collect all data rows belonging to the current table until we hit the next table title or end of the sheet.
4. Convert each table’s data to a DataFrame and export it as a CSV, using the title as the filename (with safe characters to avoid errors).

Working Code #

import pandas as pd
from openpyxl import load_workbook

# Load the Excel file and target the first sheet
wb = load_workbook("AD.XLSX")
ws = wb.worksheets[0]

# Variables to track the current table's title and data
current_table_title = None
current_table_data = []

# Iterate through all rows in the sheet
for row in ws.iter_rows(min_row=1, max_row=ws.max_row, min_col=1, max_col=ws.max_column):
    # Get all values from the current row (preserve column positions)
    row_values = [cell.value for cell in row]
    
    # Check if this row is a table title (adjust logic based on your sheet's structure)
    # Here we assume: title is the first non-empty cell in a row, and it's a standalone header
    if row_values[0] is not None and current_table_data:
        # We've hit a new title—export the previous table first
        # Convert collected data to DataFrame (first row is header)
        df = pd.DataFrame(current_table_data[1:], columns=current_table_data[0])
        # Clean title to make it a valid filename
        safe_title = current_table_title.replace("/", "_").replace("\\", "_").replace(":", "_") \
                                       .replace("*", "_").replace("?", "_").replace('"', "_") \
                                       .replace("<", "_").replace(">", "_").replace("|", "_")
        df.to_csv(f"{safe_title}.csv", index=False)
        # Reset for the new table
        current_table_data = []
    
    # If this is a title row, set the new title and add it to the data (as header)
    if row_values[0] is not None:
        current_table_title = row_values[0]
        current_table_data.append(row_values)
    # If we're in a table, add non-empty rows to the data
    elif current_table_title is not None and any(cell.value is not None for cell in row):
        current_table_data.append(row_values)

# Export the last table (since we won't hit a new title after it)
if current_table_title is not None and current_table_data:
    df = pd.DataFrame(current_table_data[1:], columns=current_table_data[0])
    safe_title = current_table_title.replace("/", "_").replace("\\", "_").replace(":", "_") \
                                   .replace("*", "_").replace("?", "_").replace('"', "_") \
                                   .replace("<", "_").replace(">", "_").replace("|", "_")
    df.to_csv(f"{safe_title}.csv", index=False)

Customization Tips #

• Title Detection: If your titles are formatted (e.g., bold text), replace the title check with if row[0].font.bold: to make detection more accurate.
• Empty Rows: If your tables have empty rows within them, remove the any(cell.value is not None for cell in row) check to include those rows.

内容的提问来源于stack exchange，提问作者Abdul Aleem Qureshi