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
| from openpyxl import load_workbook
from openpyxl import Workbook
import os
## 获取所有的文件
def getfilelist(dirname):
li = []
for f in os.listdir(dirname):
if f.endswith('.xlsx'):
li.append(f)
return li
## 更新
## 1. 打开输出文件，处理一个sheet页面，
## 2. 循环获取某一行记录，放入字典结构从所有的输入文件中获取补充记录
## 3. 如果是单条直接更新， 如果出现重复记录入职(暂不处理)
## 4. 依据以上处理方式，处理完所有sheet页面
INPATH=".\\in\\"
OUTPATH=".\\out\\"
productlist = ('product1','product2')
## 1. sheetname名称
## 2. 位置1: 过滤条件 product 所在列位置，判断是否productlist
## 3. 位置2：通过判断第一个需要填写项判断是否填写数据
sheetlist = ( ('sheet1）', 6, 15),
('sheet2', 6, 13),
('sheet3', 5, 12),
('sheet4', 6, 14))
def update():
inlist = getfilelist(".\\in")
outlist = getfilelist(".\\out")
for li in sheetlist:
sheetname = li[0]
product = li[1]
firstfilled= li[2]
d = readdata(inlist, sheetname, product, firstfilled)
writedata(outlist, d, sheetname)
def readdata(inlist, sheetname, product, firstfilled):
d = {}
for i in inlist:
wb = load_workbook(filename = INPATH+i)
ws = wb[sheetname]
row = tuple(ws.rows)
c = filter(lambda cell: (cell[product].value in productlist) and (cell[firstfilled].value is not None), row)
for cell in c:
d[cell[0].row] = cell
return d
def writedata(outlist, d, sheetname):
wbout = load_workbook(filename = OUTPATH+outlist[0])
wsout = wbout[sheetname]
print(sheetname)
#for key in d.keys(): for key in d:
for col in range(1, wsout.max_column+1):
wsout.cell(row=key, column=col).value = d[key][col-1].value
wbout.save(filename = OUTPATH+outlist[0])
update()
|