1 # encoding:utf-8
2
3 import pandas as pd
4 '''
5 整合sheet
6 将符合的列放在第一个sheet
7 将符合的列放在第二个sheet
8 '''
9
10 class OpenMyXLS():
11 def __init__(self, filepath):
12 self.filepath = filepath
13 self.openxls()
14
15 def openxls(self):
16 writer = pd.ExcelWriter('../zhtotest82.xlsx')
17 writer2 = pd.ExcelWriter('../zhtotest82_2.xlsx')
18 df = pd.read_excel(self.filepath,sheet_name=None)
19 print("所有的sheet名称：")
20 sheetNameList = list(df)
21 print(len(sheetNameList))
22
23 # df0 = pd.read_excel(self.filepath, sheet_name=sheetNameList[0])
24 # df1 = pd.read_excel(self.filepath, sheet_name=sheetNameList[1])
25 # df2 = pd.read_excel(self.filepath, sheet_name=sheetNameList[2])
26 # df3 = pd.read_excel(self.filepath, sheet_name=sheetNameList[3])
27 # df4 = pd.read_excel(self.filepath, sheet_name=sheetNameList[4])
28 # df5 = pd.read_excel(self.filepath, sheet_name=sheetNameList[5])
29 # df6 = pd.read_excel(self.filepath, sheet_name=sheetNameList[6])
30 # df7 = pd.read_excel(self.filepath, sheet_name=sheetNameList[7])
31 # df8 = pd.read_excel(self.filepath, sheet_name=sheetNameList[8])
32 # df9 = pd.read_excel(self.filepath, sheet_name=sheetNameList[9])
33 # frames = [df0, df1, df2, df3, df4, df5, df6, df7, df8, df9]
34
35 k = 0
36 frames = []
37 while k < len(sheetNameList):
38 sheetName = sheetNameList[k]
39 print(sheetName)
40 df1 = pd.read_excel(self.filepath, sheet_name=sheetName)
41 print(df1)
42 frames.append(df1)
43 k += 1
44 print(frames)
45
46 result = pd.concat(frames, keys=sheetNameList)
47 result.to_excel(writer, sheet_name="allhsgmlb")
48 writer.save()
49 writer.close()
50
51 result2 = pd.concat(frames)
52 result2.to_excel(writer2, sheet_name="allhsgmlb", index=False)
53 writer2.save()
54 writer2.close()
55 print("数据excel整合成功！")
56
57 if __name__ == '__main__':
58 filepath = '../totest82.xlsx'
59 oxl = OpenMyXLS(filepath)
