10.使用pandas整合所有sheet的数据放在一个sheet中 

 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)

 

posted @ 2022-03-19 14:01  种太阳  阅读(286)  评论(0)    收藏  举报