另一种excel数据导入的写法

# 提取所有表格文件

path = '/Users/tianfch/data-analysis_new/tianfch/wangyan/'

dirs = os.listdir(path)

# print(dirs)

file_list = []

# 读取目录文件

for file in dirs:

    file_path = os.path.join(path,file)

    # 遍历excel文件生成表格

    if file_path.endswith('.xlsx'):

        df = pd.read_excel(file_path, sheet_name='SC')

        #取拼接表格前三列内容

        df_head = df.loc[4:6,['Unnamed: 16','Unnamed: 17']] 

        print(df_head)

        # 取拼接表格后面的内容

        lable = 1

        row_first = 21

        row_end = 21

        while(1):

            if df.iloc[row_end,0] != lable:

                break

            row_end = row_end + 1

            lable = lable + 1

        df_data = df.iloc[row_first:row_end]

        df_data.drop(['Unnamed: 1', 'Unnamed: 2', 'Unnamed: 6', 'Unnamed: 7', 'Unnamed: 8', 'Unnamed: 9', 'Unnamed: 13'],axis=1, inplace=True)  # 删除冗余列

        df_data.columns = ['LINE','CODE','DESC','MARKING','PACK TYPE','PCS/BOX','B/C','PCS/CTN','CTN','QUANTITY PCS','PRICE $/1000','AMOUNT US$']

        # 前三列内容拼接进入dataframe

        df_data['SC NO:'] = df_head.iloc[0,1]

        df_data['PO NO:'] = df_head.iloc[1,1]

        df_data['DATE:'] = df_head.iloc[2,1]

        # 调整列顺序

        result = df_data[['SC NO:', 'PO NO:', 'DATE:', 'LINE','CODE','DESC','MARKING','PACK TYPE','PCS/BOX','B/C','PCS/CTN','CTN','QUANTITY PCS','PRICE $/1000','AMOUNT US$']]

        # 结果存文件

        result.to_excel(path + '/Results/' + file,encoding='utf-8',index=False)