川小胖學python

 1 import xlrd,os,xlwt
 2 from xlutils.copy import copy
 3 
 4 files = os.listdir(os.getcwd())  # 獲取指定路徑下的所有文件的文件名稱
 5 list_xls = [file for file in files if file.split('.')[-1] in ['xls']] #獲取所有xls格式的excel文件
 6 # print(list_xls)
 7 
 8 list_all_data=[]
 9 for f in list_xls:
10     workbook = xlrd.open_workbook(f)
11     data_sheet = workbook.sheets()[0]
12     list_data=[]
13     for row in range(16,43):
14         if data_sheet.cell(row, 7).value:
15             cell = data_sheet.cell(row, 7).value
16         else:
17             cell=0
18         list_data.append(cell)
19     list_all_data.append(list_data)
20 
21 l=[]
22 a=0
23 for i in range(len(list_all_data[0])):
24     _=[]
25     for j in list_all_data:
26         _.append(j[i])
27     l.append(round(sum(_)/len(list_all_data),2))
28 print(len(l),l)
29 # print(sum(l[0])/len(l[0]))
30 
31 #賦值
32 
33 workbook = xlrd.open_workbook(list_xls[0])
34 data_sheet = workbook.sheets()[0].name
35 print(type(list_xls[0]),list_xls[0],type(data_sheet),data_sheet)
36 old_excel = xlrd.open_workbook(list_xls[0],formatting_info=True)
37 # 將操作文件對象拷貝，變成可寫的workbook對象
38 new_excel = copy(old_excel)
39 # 獲得第一個sheet的對象
40 data_sheet1 = new_excel.get_sheet(data_sheet)
41 
42 #設置格式
43 alignment = xlwt.Alignment() # Create Alignment
44 # 格式為: HORZ_GENERAL, HORZ_LEFT, HORZ_CENTER, HORZ_RIGHT, HORZ_FILLED, HORZ_JUSTIFIED, HORZ_CENTER_ACROSS_SEL, HORZ_DISTRIBUTED
45 alignment.horz = xlwt.Alignment.HORZ_CENTER
46 # 格式為: VERT_TOP, VERT_CENTER, VERT_BOTTOM, VERT_JUSTIFIED, VERT_DISTRIBUTED
47 alignment.vert = xlwt.Alignment.VERT_CENTER 
48 
49 
50 #邊框
51 borders = xlwt.Borders() # Create Borders
52 #邊框格式: NO_LINE, THIN, MEDIUM, DASHED, DOTTED, THICK, DOUBLE, HAIR, MEDIUM_DASHED, THIN_DASH_DOTTED, 
53 # MEDIUM_DASH_DOTTED, THIN_DASH_DOT_DOTTED, MEDIUM_DASH_DOT_DOTTED, SLANTED_MEDIUM_DASH_DOTTED, or 0x00 through 0x0D.
54 borders.left = xlwt.Borders.THIN 
55 borders.right = xlwt.Borders.THIN
56 borders.top = xlwt.Borders.THIN
57 borders.bottom = xlwt.Borders.THIN
58 borders.left_colour = 0x40
59 borders.right_colour = 0x40
60 borders.top_colour = 0x40
61 borders.bottom_colour = 0x40
62 style = xlwt.XFStyle() # 創建樣式
63 style.borders = borders # 加載樣式
64 style.alignment = alignment
65 
66 for i in range(16,43):
67     data_sheet1.write(i, 7, l[i-16],style) #寫入數據
68 
69 new_excel.save('輸出.xls')