如何用Python将.xlsm文件单个单元格数据拆分至独立列？

问题核心 #

pd.read_excel()的delimiter参数仅适用于CSV类文本文件，读取Excel（.xlsm）时该参数不会生效，所以直接用它拆分单元格内容行不通。需要先读取文件，再单独处理单元格内的带列名数据。

分步解决方案 #

假设你的目标列名为combined_data，单元格内容格式为"列名1:值1, 列名2:值2, 列名3:值3, 列名4:值4"，按以下步骤操作：

1. 读取.xlsm文件
   直接用pd.read_excel读取，无需额外指定分隔符：

   import pandas as pd
   df = pd.read_excel("1.xlsm")
   

2. 拆分单元格内的带列名数据
   定义函数拆分单个单元格内容为字典，再通过apply+pd.Series展开成独立列：

   def split_cell(content):
       # 按逗号+空格拆分键值对，若你的分隔符不同（如仅逗号），修改此处参数
       pairs = content.split(', ')
       # 拆分每个键值对，生成列名:值的字典
       return {item.split(':')[0]: item.split(':')[1] for item in pairs}
   
   # 拆分目标列并生成新的DataFrame
   split_columns = df['combined_data'].apply(lambda x: pd.Series(split_cell(x)))
   # 合并原数据与拆分后的列
   final_df = pd.concat([df, split_columns], axis=1)
   

3. 批量处理多个.xlsm文件
   用glob批量获取文件路径，循环处理后合并所有数据：

   import glob
   
   # 获取当前目录下所有.xlsm文件
   file_list = glob.glob("*.xlsm")
   all_processed_data = []
   
   for file_path in file_list:
       df = pd.read_excel(file_path)
       split_columns = df['combined_data'].apply(lambda x: pd.Series(split_cell(x)))
       processed_df = pd.concat([df, split_columns], axis=1)
       all_processed_data.append(processed_df)
   
   # 合并所有文件的数据为一个大表
   total_df = pd.concat(all_processed_data, ignore_index=True)
   

异常处理（可选） #

如果存在格式不规范的单元格（比如分隔符错误、键值对缺失），可以给拆分函数添加异常处理，避免程序崩溃：

def split_cell(content):
    try:
        pairs = content.split(', ')
        return {item.split(':')[0]: item.split(':')[1] for item in pairs}
    except:
        # 返回指定列名的空值，列名需和你预期的拆分列一致
        return pd.Series([None]*4, index=['列名1', '列名2', '列名3', '列名4'])

验证结果 #

处理完成后，你可以直接通过列名访问拆分后的数据，比如：

# 获取拆分后的"列名1"列数据
final_df['列名1']

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