python脚本：将指定目录下所有excel的所有sheet单独生成新表

注：
1.自行安装python2.7，安装openpyxl这个第三方库（！！！版本为1.5.8。最新版本为2.4.6，ExcelWriter这个类会多一个archive入参，这里用不到，入参一个workbook即可）
2.将倒数第二行代码中的路径替换成excel所在目录（不是excel的绝对路径）。
3.excel格式为xlsx，如果不是执行前先修改下格式。
4.类名、类对象名自行替换下，，，

 1 #coding:utf-8
 2 import os
 3 import sys
 4 import time
 5 import shutil
 6 from openpyxl.reader.excel import load_workbook
 7 from openpyxl.workbook import Workbook
 8 from openpyxl.writer.excel import ExcelWriter
 9 
10 reload(sys)
11 sys.setdefaultencoding('utf8')
12 #wb = load_workbook()
13 
14 class DaibiChang(object):
15 
16     def __init__(self, path):
17         self.path = path
18 
19     def run(self):
20         DaibiChang.SplitExcel(self.path)
21     
22     @staticmethod
23     def SplitExcel(dir_path):
24         file_list = os.listdir(dir_path)
25         dir_name = [filename.split('.')[0] for filename in file_list if filename.endswith('xlsx')]
26         os.chdir(dir_path)
27         for item in dir_name:
28             try : 
30                 shutil.rmtree(item)
31             except:
32                 pass
33             finally:
34                 os.mkdir(item)
35         
36         file_path = [os.path.join(dir_path, filename) for filename in file_list if filename.endswith('xlsx')]
37         content_list = []
38         for path in file_path:
39             wb = load_workbook(path)
40             sheet_names = wb.get_sheet_names()
41             for index,sheetname in enumerate(sheet_names):
42                 wb1 = Workbook()
43                 ew = ExcelWriter(workbook = wb1)
44                 wb1.worksheets[0] = wb.get_sheet_by_name(sheetname)
45                 abs_path = '\\'.join([dir_path, os.path.split(path)[1].split('.')[0], sheetname+'.xlsx'])
46                 if os.path.exists(abs_path):
47                     abs_path.replace(sheetname,'{0}{1}'.fortmat(sheetname,time.time()))
48                 ew.save(abs_path)
49         
50 
51 if __name__ == '__main__':
52     daibichang = DaibiChang(u'E:\\umac_local\\mme_41\\before_upgrade\\alarm')
53     daibichang.run()

 

转载于:https://www.cnblogs.com/Ellon-Daris/p/6744617.html