多sheet转单sheet-优快云博客

本文链接：https://blog.youkuaiyun.com/u012366798/article/details/123680954

本文介绍如何使用Python pandas库从'原始表.xlsx'读取Excel文件，通过迭代处理逐行数据，提取并整合日期、客户、抛光等信息，最后保存为'结果表.xlsx'。重点在于数据清洗和转换过程。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

import pandas as pd

read_file = '原始表.xlsx'
save_file = '结果表.xlsx'

df = pd.read_excel(read_file, sheet_name=None)

all_data = []
for sheet in df:
    print('当前sheet: ', sheet)

    date = None
    customer = None
    polish = None
    remark = None

    start = False
    out_data = []
    for idx, rows in df[sheet].iterrows():
        line = rows.tolist()
        if start:
            out_line = line[1:]
            if pd.isna(out_line[0]):
                start = False
                continue
            
            out_data.append(out_line)
        elif not pd.isna(line[1]) and 'NO' in line[1]:
            start = True
        else:
            for col in line:
                if pd.isna(col):
                    continue
                if 'Date:' in col and date is None:
                    date = col.lstrip('Date:')
                elif 'Customer:' in col and customer is None:
                    customer = col.lstrip('Customer:')
                elif 'Polish:' in col and polish is None:
                    polish = col.lstrip('Polish:')
                elif '备注：' in col and remark is None:
                    remark = col.lstrip('备注：')
    for out_line in out_data:
        out_line.extend([remark, date, customer, polish])
        all_data.append(out_line)

    # break
columns = ['NO', 'Wafer ID', 'Substarte ID', 'Thickness(um)', 'Resistivity(Ωcm)', 'MPD', 'RMS', 'TTV', 'LTV',
           'BOW', 'WARP', 'BPD', 'TSD', 'TED', 'EPD', 'Box No', '包装方式', '备注', 'Date', 'Customer', 'Polish']
df = pd.DataFrame(all_data, columns=columns)
df.to_excel(save_file, index=None)