python3 自动编辑excel的脚本

主要功能就是把F列三行合并居中

点击(此处)折叠或打开

1. #!/usr/bin/python3
   
2. # -*- coding: utf-8 -*-
   
3. # Time : 2020/3/5 19:49
   
4. # Author : xuekai
   
5. # Email : [e-mail]
   
6. # File : excel.py
   
7. # Project : python
   
8. import os
   
9. import openpyxl
   
10. from openpyxl.styles import Border, Side, PatternFill, Font, GradientFill, Alignment
    
11. 
    
12. 
    
13. # 函数：范围单元格求和
    
14. def range_sum(worksheet, start, end):
    
15.     sum = 0
    
16.     for row in worksheet[start:end]:
    
17.         for cell in row:
    
18.             if cell.value != None:
    
19.                 sum += cell.value
    
20.     return sum
    
21. 
    
22. 
    
23. # 函数：对某sheet求和并居中
    
24. def total_amount(worksheet):
    
25.     ws = worksheet
    
26.     row, max_row = 2, ws.max_row
    
27.     print("row:%s max_row:%s ws.max_row:%s" %(row,max_row,ws.max_row))
    
28.     while row < ws.max_row:
    
29.         sum_row_start, sum_row_end = row, row
    
30.         for working_row in range(row + 1, max_row):
    
31.             # 长条件，换行增强可读性
    
32.             if (ws['A' + str(working_row)].value != None
    
33.                     or (ws['C' + str(working_row)].value == None
    
34.                         and ws['C' + str(working_row - 1)].value != None)):
    
35.                 sum_row_end = working_row - 1
    
36.                 #print(sum_row_end)
    
37.                 # 求和
    
38.                 ws['D' + str(sum_row_start)] = range_sum(ws, 'C' + str(sum_row_start), 'C' + str(sum_row_end))
    
39.                 # 合并
    
40.                 ws.merge_cells('D' + str(sum_row_start) + ':D' + str(sum_row_end))
    
41.                 ws.merge_cells('A' + str(sum_row_start) + ':A' + str(sum_row_end))
    
42.                 # 居中
    
43.                 ws['D' + str(sum_row_start)].alignment = Alignment(horizontal="center", vertical="center")
    
44.                 ws['A' + str(sum_row_start)].alignment = Alignment(horizontal="center", vertical="center")
    
45. 
    
46.                 break;
    
47.         row = sum_row_end + 1;
    
48. 
    
49. 
    
50. # 根据情况修改代码
    
51. os.chdir('D:\\')
    
52. wb = openpyxl.load_workbook('in.xlsx')
    
53. 
    
54. total_amount(wb['Sheet1'])
    
55. wb.save('Out.xlsx')

操作完成之后的样子，