地图
地图

利用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
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
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
"""
环境需求:
Python 3.6+

安装依赖:
pip3 install openpyxl
pip3 install pandas
pip3 install xlutils
如有网络问题先设置镜像,
pip3 config set global.index-url https://pypi.doubanio.com/simple

使用方法:
打开终端,输入"python FileName.py"回车
可选参数,[班级:str, 时长:int 是否标记:y]

命名习惯:
变量:下划线
常量:全部大写字母
函数、方法、形参:小驼峰
类名:大驼峰

Creation Date: 2020-04-24
"""

from openpyxl import Workbook, load_workbook
from openpyxl.styles import Font, NamedStyle
# from openpyxl.styles import colors, Alignment
import pandas as pd
import time
import sys
import re


def initData():
"""进行第一次处理,将大表简化成小表。"""
wb1 = load_workbook('1.xlsx') # 打开文件1.xlsx
data_sheet = wb1[wb1.sheetnames[0]] # 打开第1位置的表格,即导出的数据Sheet

new_wb = Workbook() # 新建Excel文件
new_sheet = new_wb.active # 获取默认表

temp_dict = dict(zip(range(1, 6), (2, 3, 4, 7, 8)))
# print(temp_dict)
for x in range(1, 6):
copyColumn(new_sheet, x, data_sheet, temp_dict[x])

new_wb.save(NEWFILE)

# 利用pandas进行排序
df = pd.read_excel(NEWFILE)
df.sort_values(by='学生姓名', inplace=True, ascending=True)
# inplace默认为False,如果该值为True,那么就会在当前的dataframe上操作。
# ascending参数True为升序,False为降序。
# print(df)
df.to_excel(NEWFILE)

# pandas会对Excel添加一个索引列,由于pandas强大,暂时就不折腾,利用openpyxl去掉这一列
wb_sorted = load_workbook(NEWFILE)
sheet_sorted = wb_sorted[wb_sorted.sheetnames[0]] # 打开第1位置的表格,即排序后的Sheet
sheet_sorted.title = '原始数据'
sheet_sorted.delete_cols(1)

C = [chr(i) for i in range(65, 65 + 5)] # 字母列表 A-E,对应Excle的1~5列
for x in C:
sheet_sorted.column_dimensions[x].width = 15 # 设置列宽

sheet_sorted.column_dimensions['B'].width = 25 # 设置列宽
sheet_sorted.column_dimensions['C'].width = 20 # 设置列宽
sheet_sorted.column_dimensions['D'].width = 30 # 设置列宽

wb_sorted.save(NEWFILE)


def copyColumn(sheetA, intX, sheetB, intY):
"""将B表的第Y列复制到A表的第X列。"""
r = 1
for row in sheetB.iter_rows(min_row=5,
max_row=sheetB.max_row,
min_col=intY,
max_col=intY):
# min_row=5 视情况而定
for cell in row:
# print(cell.value, r)
sheetA.cell(r, intX, cell.value)
r += 1


def reTime(time: str):
"""Excel的E列为观看时长,正则匹配字符串中的数字,返回的是一个列表。"""
res: list = re.findall(r"\d+\.?\d*", str(time))
# 判断列表是否为空
if res:
timeLong = res[0] # 取列表的字符串,str -> int
else:
timeLong = 0 # 应对“观看不足一分钟”这种情况

return int(timeLong)


def dataFilter(sheet, classNum: int) -> list:
"""从精简的小表提取相应数据分班级,然后过滤出中文姓名,返回一个列表。"""
new_work = load_workbook(NEWFILE) # 打开Excle
new_sheet = new_work.create_sheet(CLASSNAME + str(classNum),
classNum - 1) # 新建一个班级Sheet

print('-' * 20 + '\n开始处理')
# 复制第1行表头,和5列有用信息
for init_column in range(1, 6):
new_sheet.cell(1, init_column,
sheet.cell(1, init_column).value).style = sty_black

# 设置列宽,代码重复,考虑优化
C = [chr(i) for i in range(65, 65 + 5)] # 字母列表 A-E,对应Excle的1~5列
for x in C:
new_sheet.column_dimensions[x].width = 15 # 设置列宽

new_sheet.column_dimensions['B'].width = 25 # 设置列宽
new_sheet.column_dimensions['C'].width = 20 # 设置列宽
new_sheet.column_dimensions['D'].width = 30 # 设置列宽

name_filter = []
a = 1
# print('行数', sheet.max_row)
# 开始筛选复制有用信息
for x in range(1, sheet.max_row + 1):
if str(sheet["C" + str(x)].value).startswith(
CLASSNAME + str(classNum)): # 复制以classNum开头的行
for column in range(1, 6):
timeLong = reTime(sheet["E" + str(x)].value) # 正则匹配字符串中的数字的函数
# print('时长:', timeLong)
# 观看少于 TIMELONG 分钟则标红
if int(timeLong) >= TIMELONG:
new_sheet.cell(a + 1, column,
sheet.cell(x,
column).value).style = sty_black
else:
new_sheet.cell(a + 1, column,
sheet.cell(x, column).value).style = sty_red
# 其中的'0-行, 0-列'指定表中的单元,'value'是向该单元写入的内容

# 正则匹配中文姓名
res = re.findall('[\u4e00-\u9fa5]+', sheet["C" + str(x)].value)
name_filter.extend(res) # 筛选出中文姓名,返回一个list
a += 1 # 以classNum开头的行的计数器

# listIndex(name_filter)
# 处理掉线重连后重复的情况
def duplicates(aList, item):
return [i for i, x in enumerate(aList) if x == item]

re_dict = dict((x, duplicates(name_filter, x)) for x in set(name_filter)
if name_filter.count(x) > 1)
# 寻找列表中重复项目,并返回索引,存储到一个字典
# print(re_dict, len(re_dict))
if len(re_dict):
for key in sorted(re_dict):
time_sum = 0
print(key, '掉线重连过。')
print(re_dict[key]) # 字典的值是一个列表,存储列表重复项目的索引位置
for x in re_dict[key]:
name = new_sheet.cell(x + 2, 3).value # 列表中的索引+2对应Excle中的行
time = new_sheet.cell(x + 2, 5).value
print(name, time)
time_sum += reTime(time)

print('总时长,', time_sum, '\n')
if time_sum >= TIMELONG: # 如果总时长超过TIMELONG
# print('索引为:', re_dict[key])
for i in re_dict[key]: # 则把所有的符合的行标记为一个颜色
for c in range(1, 6):
new_sheet.cell(i + 2, c).style = sty_flag

new_work.save(NEWFILE)
return name_filter


def studentFilter(sheet, classNum: int):
"""从学生名单Excle提取中文姓名,返回一个列表。"""
student_filter = []
for x in range(1, sheet.max_row + 1):
# 已修改学生名单"800.xlsx",只存在中文姓名,所以不需要正则筛选。但是以防万一,保证正确。
res = re.findall('[\u4e00-\u9fa5]+',
str(sheet.cell(x, classNum).value))
student_filter.extend(res)
return student_filter


def absentStudent(classNum: int):
"""筛出缺勤学生。"""
wb1 = load_workbook(NEWFILE) # 打开Excle
data_sheet = wb1['原始数据'] # 打开经过initData()精简后的Sheet
wb80 = load_workbook(CLASSNAME + '.xlsx') # 打开学生名单Excle
student_sheet = wb80['Sheet1'] # 打开Sheet

# 追加缺勤名单到表尾部,返回缺勤名单列表
name_filter: list = dataFilter(data_sheet, classNum) # 过滤后的原始数据Excle姓名列表
student_filter: list = studentFilter(student_sheet,
classNum) # 过滤后的学生Excle姓名列表

a = [x for x in name_filter if x in student_filter]
# 两个列表中都存在,交集
absent_list = [y for y in (name_filter + student_filter) if y not in a]
# 两个列表中的不同元素,缺勤

add_absent_wb = load_workbook(NEWFILE) # 打开Excle
add_absent_sheet = add_absent_wb[CLASSNAME + str(classNum)] # 打开相应的班级Sheet

# 利用学生名单Excel,获取某列的元组,然后获取行数,取非空项目数,作为学生人数
alphabet_dict = dict(zip(range(1, 9), [chr(i) for i in range(65, 65 + 8)]))
# {1: 'A', 2: 'B', 3: 'C', 4: 'D', 5: 'E', 6: 'F', 7: 'G', 8: 'H'}
column_tpl: tuple = student_sheet[alphabet_dict[classNum]] # 配合字典,获取某列的元组
# print(column_tpl)
n = 0
for x in range(len(column_tpl)):
if column_tpl[x].value: # 非空,则计数器+1
n += 1
print(classNum, '班人数:', n)
full = n

# 追加的统计信息
str1 = '备注:红色为观看总时长少于 ' + str(TIMELONG) + ' 分钟的人'
global STRFLAG
note = str1 + STRFLAG
add_absent_sheet.cell(add_absent_sheet.max_row + 2, 1,
note).style = sty_red

remarks = CLASSNAME + str(classNum) + ' 应到 ' + str(full) + ' 人,实到 ' + str(
full - len(absent_list)) + ' 人'
add_absent_sheet.cell(add_absent_sheet.max_row + 1, 1,
remarks).style = sty_red

if len(absent_list) == 0:
absentStr = '缺勤 0 人。'
else:
absentStr = '缺勤 ' + str(len(absent_list)) + ' 人:' + ', '.join(absent_list)

add_absent_sheet.cell(add_absent_sheet.max_row + 1, 1,
absentStr).style = sty_red

add_absent_wb.save(NEWFILE)
print('已生成的表格', ', '.join(add_absent_wb.sheetnames))
return absent_list


if __name__ == '__main__':

print(
'注意事项:\n腾讯课堂导出的Excle需要重命名为\"1.xlsx\",\n需要常备一份学生名单的Excle命名为\"80.xlsx\",\n注意班级人数是否正确。\n'
)

# 格式化成时间格式,作为文件名
time_stamp = time.strftime("%m-%d", time.localtime())
NEWFILE = str(time_stamp) + '.xlsx'
print('将在当前文件夹生成新文件:' + NEWFILE)

# 默认黑色字体,红色字体,蓝色字体,对齐方式
font_black = Font(name=u'宋体', size=14, color='000000')
font_red = Font(name=u'宋体', size=14, color='FF0000')
font_blue = Font(name=u'宋体', size=14, color='0000FF')
# alignment = Alignment(horizontal='center', vertical='center')
sty_black = NamedStyle(name='sty_black', font=font_black)
sty_red = NamedStyle(name='sty_red', font=font_red)
sty_blue = NamedStyle(name='sty_blue', font=font_blue)

# 接受命令行参数
# print(sys.argv)
# 默认值
CLASSNAME = '80' # 班级学生名单Excel文件名
TIMELONG = 40 # 默认听课时长
STRFLAG = ''
sty_flag = NamedStyle(name='sty_flag', font=font_black) # 不标记蓝色
if len(sys.argv) == 4: # 3个参数
# sys.argv[0] 表示脚本名
CLASSNAME = str(sys.argv[1]) # 第1个参数设置班级
TIMELONG = int(sys.argv[2]) # 第2个参数设置时长
if str(sys.argv[3]) == 'y': # 第3个参数为'y'则标记为蓝色
STRFLAG = ',蓝色为断线后重连的人'
sty_flag = NamedStyle(name='sty_flag', font=font_blue)

print('本次处理的是 ' + CLASSNAME[0] + ' 年级,' + '将把观看时长低于', TIMELONG,
'分钟的人标为红色' + STRFLAG + '。\n')

initData()

for x in range(1, 7): # 从1班到6班,索引从1开始
absentStudent(x)

input('程序运行完成!按 回车键 退出->')

从输入到输出一步都不用点,就得到自己想要的表格,这才是我想要的。

哪里不会,import哪里。