python汇总excel数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

from openpyxl import load_workbook
from openpyxl import Workbook
import pandas as pd

wb_in = load_workbook( filename = '.\\test\\data1.xlsx')
ws_in = wb_in.active
## 名字列表开始结束行号 
start_index = 2
end_index = 4

wb_info = load_workbook( filename = '.\\test\\data2.xlsx')
ws_info = wb_info.active

wb_out = load_workbook( filename = '.\\test\\result.xlsx') 
ws_out = wb_out.active
out_index = 2 

## 找到要统计人员姓名
for x in range(start_index,end_index):
    name = ws_in.cell(row=x,column=1)
    print(name.value)
    ## 查找要统计人员附件信息并更新到统计表格中
    find_flag = 0
    for row in ws_info.iter_rows("B"):
        for col in row:
            #print(col.value)
            if (str(col.value).strip() == str(name.value).strip()) and (find_flag == 0):
                ## 第四列 身份证 第五列 联系电话
                idno = ws_info.cell(row=col.row,column=4)
                phoneno = ws_info.cell(row=col.row, column=5)
                ## 更新到统计文件
                ws_out['A'+ str(out_index)].value = str(out_index-1)
                ws_out['B'+ str(out_index)].value = 'XX部'
                ws_out['C'+ str(out_index)].value = 'XXX'
                ws_out['D'+ str(out_index)].value = name.value
                ws_out['E'+ str(out_index)].value = idno.value
                ws_out['F'+ str(out_index)].value = phoneno.value
                out_index = out_index + 1
                find_flag = 1
            break
    if find_flag == 0:
        print(name.value)

wb_out.save( filename = '.\\test\\result.xlsx')