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
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
|
# 配置项
##文件查找路径(留空为当前运行目录)
##格式如下[C:\\目录\\工作目录]
base = ''
##行列查找限制(设0为自动判断)
row_limit = 10
column_limit = 30
##关键词定义
FileName_Keyword = ['明细.xls', '明细项目.xls']
ProjectName_Keyword = ['项 目 名 称', '项目名称']
ProjectManager_Keyword = ['项 目 经 理', '项目经理']
AvailableBalance_Keyword = ['工程可用余额：', '工程款可用余额：']
import os
import xlwings as xw
#输出程序信息
print('')
print('程序版本：V3.0')
print('编写日期：2023-06-12')
print('编写者：LaoZhu')
#遍历查找文件
if((base == '' )|(base == None)):
base = os.getcwd()
paths = []
warns = []
print('\n已查找到文件：')
for root,dirs,files in os.walk(base):
if(root == base):
base_dirs = []
for dir in dirs:
base_dirs.append(os.path.join(root,dir))
if(root in base_dirs):
has_file_flag = False
for file in files:
for keyword in FileName_Keyword:
if file.endswith(keyword):
path = os.path.join(root,file)
paths.append(path)
has_file_flag = True
print(path)
if(has_file_flag != True):
msg = '文件夹[' + root + ']中没有找到明细表格'
warns.append(msg)
#输出警告信息
if(len(warns) != 0):
print("\n警告：")
for warn in warns:
print(warn)
print('\n请确认文件查找正确')
os.system('pause')
#读取数据
datas = []
warns = []
print('\n正在读取数据...')
app = xw.App(visible=True, add_book=False)
for path in paths:
wb = app.books.open(path)
sht = wb.sheets[0]
#获取使用中的最大行数
if not (row_limit > 0):
max_row = sht.used_range.last_cell.row
else:
max_row = row_limit
#获取使用中的最大列数
if not (column_limit > 0):
max_column = sht.used_range.last_cell.column
else:
max_column = column_limit
#临时变量
ProjectName = None
ProjectManager = None
AvailableBalance = None
#循环查找数据
print("正在读取文件[" + path + ']')
end_flag = False
for r in range(0, max_row):
if(end_flag == True):
break
for c in range(0, max_column):
if(end_flag == True):
break
print("读取文件位置：",r,c)
for keyword in ProjectName_Keyword:
if(sht[r,c].value == keyword):
i = len(sht[r,c].merge_area.columns)
ProjectName = sht[r,c+i].value
for keyword in ProjectManager_Keyword:
if(sht[r,c].value == keyword):
i = len(sht[r,c].merge_area.columns)
ProjectManager = sht[r,c+i].value
for keyword in AvailableBalance_Keyword:
if(sht[r,c].value == keyword):
i = len(sht[r,c].merge_area.columns)
AvailableBalance = sht[r,c+i].value
if((ProjectName != None) & (ProjectManager != None) & (AvailableBalance != None)):
datas.append([ProjectName,ProjectManager,AvailableBalance])
print("文件[" + path + "]读取完毕")
end_flag = True
if(end_flag != True):
print("读取文件至尾部或到达限制")
#缺少项目经理数据的处理
if( (ProjectName != None) & (ProjectManager == None) & (AvailableBalance != None) ):
datas.append([ProjectName,ProjectManager,AvailableBalance])
print("文件[" + path + "]读取完毕（缺少项目经理数据）")
#缺少特定数据的提示生成
msg = '文件[' + path + ']缺少数据：'
add_flag = False
if(ProjectName == None):
msg += '项目名称 '
add_flag = True
# if(ProjectManager == None):
# msg += '项目经理 '
# add_flag = True
if(AvailableBalance == None):
msg += '工程可用余额 '
add_flag = True
if(add_flag == True):
warns.append(msg)
wb.close()
#输出读取内容
print("\n已读出以下数据：")
for data in datas:
print(data)
#输出警告信息
if(len(warns) != 0):
print("\n警告：")
for warn in warns:
print(warn)
#写入表格内容
print("\n正在写入数据...")
wb = app.books.add()
sht = wb.sheets[0]
sht[0,0].value = "项目名称"
sht[0,1].value = "项目经理"
sht[0,2].value = "工程可用余额"
for i,data in enumerate(datas):
for j,value in enumerate(data):
sht[i+1,j].value = value
wb.save('数据集合.xlsx')
wb.close()
app.quit()
print("\n写入完成！")
|
