# Author:Clown
# -*- codeing = utf-8 -*-
# @Time :2021/5/15 10:57
# @File: excel拆分.py
import os
import pandas as pd
import xlrd
import pandas as pd
import os
import openpyxl
def excel2csv(excel_file):
    # 打开excel文件
    for a,b,files in os.walk(excel_file,topdown = False):
        for file in files:
            path = str(excel_file)+'/'+str(file)
            workbook=xlrd.open_workbook(r'%s'%str(path))
            # 获取所有sheet名字
            sheet_names=workbook.sheet_names()
            # dir_path=os.path.abspath(os.path.dirname(excel_file))
            # # 获取excel当前目录
            # csv_path=dir_path+'\\csv\\'
            # #if not os.path.exists(csv_path):
            # os.mkdir(csv_path)
            # 在excel所在目录下新建csv文件夹
            print(file, '正在拆分')
            for worksheet_name in sheet_names:
                # 遍历每个sheet并用Pandas读取

                data_xls=pd.read_excel(path,worksheet_name,dtype=str)
                data_xls.to_excel('F:/cppc/cppc_工具/食亨订单拆解/订单明细拆分/'+str(file)[:-5]+worksheet_name+'.xlsx',index=0,encoding='utf-8-sig')
                #把每个sheet分别存为一个csv文件
                #data_xls.to_csv(csv_path+worksheet_name+'.csv',index=None,encoding='utf-8-sig')
                #data_xls.to_excel(csv_path+worksheet_name+'.xlsx',index=None,encoding='utf-8')
                #data_xls.to_excel(str(worksheet_name)+".xls", sheet_name=worksheet_name, index = False)
                print(file, worksheet_name, '已拆出')
# 测试
if __name__ == '__main__':

    excel2csv('F:/cppc/cppc_工具/食亨订单拆解/订单详情')