注：
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()