表格多工作组合并数据到一个组

play.bat

1
2
@echo off
start ./382/python.exe app.py

app.py

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
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
#-*- coding:utf-8 -*-

import csv
import xlrd
import pandas as pd
# 除了姓名以外的参数
listargs = ['绩效结果','岗位名称','绩效分数','合同单位']
#打开excel
wb = xlrd.open_workbook('input.xlsx')
get_sheet_name = wb.sheet_names()[0]
nameArr = []
# 获取到表格的sheet名称
for a in range(0,len(wb.sheet_names())):
    nameArr.append(wb.sheet_names()[a])
# print(nameArr)
# 判断是否是数字
def is_number(s):
    try:
        float(s)
        return True
    except ValueError:
        pass
    try:
        import unicodedata
        unicodedata.numeric(s)
        return True
    except (TypeError, ValueError):
        pass
    return False
# 获取指定列内容 前提是要有姓名列
def getRowsDatas(str,sh,nameRow):
    can = -1
    for num in range(0, sh.ncols):
        # print(sh.row_values(i)[line])
        if (sh.row_values(nameRow)[num] == str):
            # print('a2',i,num)
            can = num
            break
    return can
# 把数组保存成csv
def save(data):
    with open('output.csv', 'w', newline='') as csv_file:
        f_csv = csv.writer(csv_file)
        # f_csv.writerow(headers)
        f_csv.writerows(data)
def openSheet(name):
    print(name)
    sh = wb.sheet_by_name(name)
    #遍历excel，打印所有数据
    # 存储行和列
    nameCol = 0 #列
    nameRow = 0 #行
    var1 = []
    for a in range(0,len(listargs)):
        var1.append([])
    # 查找姓名单元格位置
    for i in range(sh.nrows):
        # print(sh.row_values(i))
        # 获取到姓名位置
        for num in range(0,sh.ncols):
            # print(sh.row_values(i)[line])
            if(sh.row_values(i)[num] == '姓名'):
                # print('a',i,num)
                nameRow = i
                nameCol = num
                break
        #获取绩效分数列位置
        for index,a in enumerate(listargs):
        # for a in listargs:
            print(a,index)
            var1[index] = getRowsDatas(a, sh, nameRow)
            print(var1[index])
        # print('ccccc',var1)

    # 输出姓名列表
    for line in range(nameRow+1,sh.nrows):
        # 先检测第一行是否有id
        if(is_number(sh.row_values(line)[0])):
            # 输出姓名列
            arr = []
            arr.append(sh.row_values(line)[nameCol])
            for a in var1:
                if(a == -1):
                    arr.append('')
                else:
                    arr.append(sh.row_values(line)[a])
            datasNames.append(arr)
# 存储数据
datasNames = []
# 启动程序
for name in nameArr:
    openSheet(name)
print(datasNames)
save(datasNames)
input()