Python拆分Excel单元格内容求助（xlrd/xlwt使用）

Hey there! Let's get your Excel text-to-columns functionality working smoothly, and also look at a more efficient library for this task.

First, let's address the issues in your existing code and clarify how to properly split and write the cell content:

Key Issues in Your Code: #

• Import Syntax: You need separate lines for importing xlrd and xlwt
• Writing Split Values: When you split a string with split(','), you get a list of strings. xlwt can't write a list directly to a cell—you need to write each element to its own column.
• Row Writing Logic: Your current row handling is a bit off; using sheet.write(row_idx, col_idx, value) is more straightforward.

Corrected Code: #

import xlrd
import xlwt

# Open source workbook and create output workbook
workbook = xlrd.open_workbook('myexcelfile.xlsx')
workbookwrite = xlwt.Workbook()
sheet = workbookwrite.add_sheet('Sheet1')

# Write header (optional, if you need column names)
sheet.write(0, 0, 'completed_at')
sheet.write(0, 1, 'name_part1')
sheet.write(0, 2, 'name_part2')  # Add more columns based on your expected split count

worksheet = workbook.sheet_by_index(0)
output_row = 1

# Iterate through rows
for row_idx in range(worksheet.nrows):
    cell1 = worksheet.cell(row_idx, 3).value  # completed_at column
    cell2 = worksheet.cell(row_idx, 15).value  # name column to split
    
    # Skip rows where completed_at is empty
    if cell1 != '':
        # Split the name cell by comma
        split_values = str(cell2).split(',')  # Wrap in str() to avoid errors if cell is not a string
        
        # Write completed_at to first column
        sheet.write(output_row, 0, cell1)
        
        # Write each split value to consecutive columns
        for col_idx, val in enumerate(split_values):
            sheet.write(output_row, col_idx + 1, val.strip())  # strip() removes extra spaces
        
        output_row += 1

# Save the output file
workbookwrite.save('output.xls')

How the Split Works: #

• str(cell2).split(',') splits the cell content into a list using commas as separators. Using str() ensures we handle non-string cell values (like numbers) without errors.
• enumerate(split_values) gives us both the index and value of each split part, so we can write them to columns 1, 2, 3, etc.
• val.strip() removes any leading/trailing spaces from each split part (matching Excel's text-to-columns behavior).

For Excel manipulation and text processing, pandas is far more beginner-friendly and efficient than xlrd/xlwt. It handles the entire text-to-columns operation in just a few lines, and manages Excel read/write seamlessly.

Step-by-Step Pandas Implementation: #

1. First, install pandas if you haven't already:

pip install pandas openpyxl  # openpyxl is needed for reading/writing .xlsx files

2. The code to split your columns:

import pandas as pd

# Read the input Excel file
df = pd.read_excel('myexcelfile.xlsx')

# Filter out rows where 'completed_at' (column index 3) is empty
# Replace 'Unnamed: 3' with the actual column name if you know it!
filtered_df = df[df.iloc[:, 3].notna()]

# Split the 'name' column (index 15) into multiple columns, using comma as separator
# expand=True turns the split list into separate columns
split_cols = filtered_df.iloc[:, 15].str.split(',', expand=True)
split_cols = split_cols.apply(lambda x: x.str.strip())  # Clean up extra spaces

# Combine the original 'completed_at' column with the split columns
result_df = pd.concat([filtered_df.iloc[:, 3], split_cols], axis=1)

# Rename columns for clarity (adjust based on your data)
result_df.columns = ['completed_at'] + [f'name_part{i+1}' for i in range(split_cols.shape[1])]

# Save to output Excel file
result_df.to_excel('output.xlsx', index=False)

Why Pandas is Better: #

• It handles data cleaning (like removing empty rows) automatically.
• The str.split(',', expand=True) does exactly what Excel's text-to-columns does, in one line.
• No need to manually iterate through rows/columns—pandas vectorizes operations, making it faster for large files.
• It supports modern .xlsx files natively (xlwt only writes .xls files, which is outdated).

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