利用Python将一张Excel文件的sheet拆分成多个sheets

最新推荐文章于 2024-07-30 14:57:51 发布

河北一帆

最新推荐文章于 2024-07-30 14:57:51 发布

阅读量1w

点赞数 6

本文链接：https://blog.csdn.net/qq_41816368/article/details/80979105

版权

原始文件如下图所示

按照销售部门拆分成三个Excel的sheets，分别为一科，二科，三科

代码如下

# -*- coding: utf-8 -*-
"""
Created on Mon Jul  9 20:25:31 2018

@author: Lenovo
"""

import pandas as pd
data = pd.read_excel("E:\data1.xls")
rows = data.shape[0]  #获取行数 shape[1]获取列数
department_list = []

for i in range(rows):
    temp = data["销售部门"][i]
    if temp not in department_list:    #防止重复
        department_list.append(temp)   #将销售部门的分类存在一个列表中

n = len(department_list)    #销售部门科目数

df_one = pd.DataFrame()   #用于存储一科的dataframe
df_two = pd.DataFrame()   #用于存储二科的dataframe
df_three = pd.DataFrame()  #用于存储三科的dataframe
df_list = [df_one, df_two, df_three]

for department in range(n):
    for i in range (0, rows):
        if data["销售部门"][i] == department_list[department]:
            df_list[department] = pd.concat([df_list[department], data.iloc[[i],:]], axis = 0, ignore_index = True)
    
writer = pd.ExcelWriter('E:/new.xls')    #利用pd.ExcelWriter()存多张sheets

for i in range(n):
    df_list[i].to_excel(writer, sheet_name = str(department_list[i]), index=False)    #注意加上index=FALSE 去掉index列

writer.save()