def readExcel(excel_path):
stuDatas = []
try:
data = xlrd_open_workbook(excel_path)
table = data.sheet_by_index(0)
except:
return stuDatas
nrow = table.nrows
header = table.row_values(0)
# print(header)
for i in range(confAct.START_ROW, nrow):
stu = {}
stu['info'] = SPLIT_CHAR.join(
(table.cell(i, 0).value, table.cell(i, 1).value))
stuData = {}
for j in range(2, len(header)):
value = table.cell(i, j).value
try:
value = eval(value)
value = value[1:4]
except Exception as e:
log.debug(f'eval转换出错：{e} - value={value}')
stuData[header[j]] = value
stu['data'] = stuData
stuDatas.append(stu)
return stuDatas
def testRawExcel(excel_path):
'''判断是否符合【每日健康打卡】导出的Excel文件格式
'''
res = {}
res['code'] = 0
res['msg'] = ''
try:
excel = xlrd_open_workbook(excel_path)
sht_names = excel.sheet_names()
for sht_name in sht_names:
log.debug(f'Doing...Excel: {excel_path}, Sheet: {sht_name}')
table = excel.sheet_by_name(sht_name)
header = table.row_values(0)
if len(header) == 0:
res['msg'] = f'请删除空表：{excel_path}<{sht_name}>后重试'
elif set(header) >= set(HEADER_REQUIRED):
res['code'] = 1
else:
res['msg'] = f'{excel_path}<{sht_name}>中缺少{HEADER_REQUIRED}的部分字段'
if confAct.ONLY_FIRST_SHEET:
break
except Exception as e:
res['msg'] = f'Excel表格{excel_path}格式有误！'
log.error(f'Excel表格格式有误！ - {e}', exc_info=True)
finally:
if res['msg'] != '':
res['code'] = 0
log.info(res['msg'])
return res
def handleExcels(self, excel_list):
datas = {}
stuInfos = []
stuDates = []
count = 0
total = len(excel_list)
for excel_path in excel_list:
count += 1
try:
excel = xlrd_open_workbook(excel_path)
self._signal.emit(f'正在处理 {count}/{total}：{excel_path}')
tables = excel.sheets()
for table in tables:
header = table.row_values(0)
# 钉钉打卡导出结果中必有【工号】【提交人】【当前时间,当前地点】
idx_sno = header.index(STR_SNO)
idx_sname = header.index(STR_NAME)
idx_date = header.index(STR_DATE)
header_profile_idxs = []
for colName in HEADER_PROFILE[2:]:
if colName in header:
header_profile_idxs.append(header.index(colName))
else:
header_profile_idxs.append(-1)
log.error(f'EXCEL: {excel_path} - 缺少字段: {colName}')
for i in range(1, len(table.col_values(0))):
sinfo = f'{table.cell(i, idx_sno).value}{SPLIT_CHAR}{table.cell(i, idx_sname).value}'
if sinfo not in stuInfos:
datas[sinfo] = {}
stuInfos.append(sinfo)
try:
sdate = table.cell(i, idx_date).value
if sdate not in stuDates:
stuDates.append(sdate)
temp_data = []
for idx in header_profile_idxs:
if idx != -1:
temp_data.append(table.cell(i, idx).value)
else:
temp_data.append(STR_UNDO)
datas[sinfo][sdate] = temp_data
except Exception as e:
log.warn(f'{e}', exc_info=True)
if confAct.ONLY_FIRST_SHEET:
break
except Exception as e:
log.warn(f'读取{excel_path}出错: {e}', exc_info=True)
# 补充遗漏的日期数据
undoData = [STR_UNDO] * len(HEADER_PROFILE[2:])
for sinfo, sData in datas.items():
for date in stuDates:
if date not in sData:
datas[sinfo][date] = undoData
log.debug(f'人员: {sinfo} - 缺少日期: {date}')
return datas, stuInfos
def handleExcels(self, excel_list):
datas = {}
stuInfos = []
count = 0
total = len(excel_list)
for excel_path in excel_list:
count += 1
try:
excel = xlrd_open_workbook(excel_path)
self._signal.emit(f'正在处理 {count}/{total}：<br>{excel_path}')
time_sleep(0.5)
tables = excel.sheets()
for table in tables:
header = table.row_values(0)
# 钉钉打卡导出结果中必有【工号】【提交人】【当前时间,当前地点】
idx_sno = header.index(STR_SNO)
idx_sname = header.index(STR_NAME)
idx_location = header.index(STR_TIME_LOC)
for i in range(1, len(table.col_values(0))):
sinfo = f'{table.cell(i, idx_sno).value}{SPLIT_CHAR}{table.cell(i, idx_sname).value}'
if sinfo not in stuInfos:
stuInfos.append(sinfo)
str_location = table.cell(i, idx_location).value
if str_location != STR_UNDO:
try:
sdate = eval(str_location)[0][:10]
if sdate not in datas:
datas[sdate] = {}
datas[sdate][sinfo] = str_location
except Exception as e:
log.warn(f'{e}', exc_info=True)
if confAct.ONLY_FIRST_SHEET:
break
except Exception as e:
log.warn(f'读取{excel_path}出错: {e}', exc_info=True)
# 补充遗漏的学生数据
for date, dateData in datas.items():
log.info(f'日期: {date}, 人员数: {len(datas[date])}')
for stu in stuInfos:
if stu not in datas[date]:
datas[date][stu] = STR_UNDO
stuInfos = sorted(stuInfos)
return datas, stuInfos
def getStuInfo(self, infoFile):
self._signal.emit(f'>> 处理学生信息文件: {infoFile}')
stuInfos = {}
excel = xlrd_open_workbook(infoFile)
table = excel.sheet_by_index(0)
header = table.row_values(0)
idx_sno = header.index('学号')
idx_sname = header.index('姓名')
idx_grade = header.index('年级')
idx_class = header.index('专业班级')
idx_idnum = header.index('身份证号码')
idx_phone = header.index('联系电话')
idx_address = header.index('家庭地址')
idx_parent = header.index('家长联系方式')
nrow = table.nrows
for i in range(1, nrow):
aStuInfo = {}
sno = table.cell(i, idx_sno).value
aStuInfo['sname'] = table.cell(i, idx_sname).value
aStuInfo['grade'] = table.cell(i, idx_grade).value
aStuInfo['sclass'] = table.cell(i, idx_class).value
idnum = table.cell(i, idx_idnum).value.strip()
aStuInfo['idnum'] = idnum
aStuInfo['gender'] = '男' if int(idnum[-2]) % 2 else '女'
aStuInfo['birthday'] = f'{idnum[6:10]}.{idnum[10:12]}'
aStuInfo['phone'] = table.cell(i, idx_phone).value
aStuInfo['address'] = table.cell(i, idx_address).value
aStuInfo['parent'] = table.cell(i, idx_parent).value
stuInfos[sno] = aStuInfo
return stuInfos
def testSpectExcel(excel_path):
'''判断是否符合位置分析的Excel文件格式
'''
res = {}
res['code'] = 0
res['msg'] = ''
try:
excel = xlrd_open_workbook(excel_path)
table = excel.sheet_by_index(0)
# 只用到第一个工作表
nrows = table.nrows
ncols = table.ncols
if nrows == 0 and ncols == 0:
res['msg'] = f'该Excel为空表，请重新选择文件！'
elif nrows >= 2 and ncols >= 4:
reFlag = True
for i in range(1, nrows):
for j in range(2, ncols):
val = table.cell(i, j).value
if val != '' and val != STR_UNDO:
val = eval(val)
if not isinstance(val, list):
reFlag = False
break
if reFlag:
res['code'] = 1
else:
res['msg'] = f'该Excel表格格式有误，请重新选择文件！'
else:
res['msg'] = '该Excel表格数据量不足，请重新选择文件！'
except Exception as e:
res['msg'] = f'该Excel表格格式有误，请重新选择文件！'
log.error(f"{res['msg']} - {e}", exc_info=True)
finally:
if res['msg'] != '':
log.info(res['msg'])
return res
def getDatasFromDing(self, dingDir, stuInfos):
datas = {}
dates = []
if not os_path_exists(dingDir):
print(f'路径不存在：{dingDir}')
return dates, datas
excel_list = []
for filename in os.listdir(dingDir):
excel_list.append(os_path_join(dingDir, filename))
count = 0
total = len(excel_list)
for excel_path in excel_list:
count += 1
excel = xlrd_open_workbook(excel_path)
self._signal.emit(f'正在处理 {count}/{total}：{excel_path}')
table = excel.sheet_by_index(0)
nrows = table.nrows
header = table.row_values(0)
idx_sno = header.index('工号')
idx_sname = header.index('提交人')
idx_date = header.index('填写周期')
idx_temperature = header.index('今日体温')
if '今日有无以下症状' in header:
idx_health = header.index('今日有无以下症状')
else:
idx_health = header.index('近两日有无以下症状')
idx_address = header.index('当前时间,当前地点')
for i in range(1, nrows):
sno = table.cell(i, idx_sno).value
if sno not in datas.keys():
datas[sno] = {}
date = table.cell(i, idx_date).value
if date not in dates:
dates.append(date)
address = table.cell(i, idx_address).value
temperature = table.cell(i, idx_temperature).value
# temperature = format(random.uniform(36.4, 37.2), '.1f')
city = self.getCity(address)
isDanger = '是' if city in DANGER_PLACES else '否'
aStuDateData = {
'temperature': temperature,
'health': table.cell(i, idx_health).value,
'city': city,
'isDanger': isDanger
}
datas[sno][date] = aStuDateData
# 补充遗漏的人员数据
for sno in stuInfos.keys():
if sno not in datas.keys():
self._signal.emit(f"缺少人员: {sno} {stuInfos[sno]['sname']}")
datas[sno] = {}
# 补充遗漏的日期数据
undo = '-'
undoData = {
'temperature': undo,
'health': undo,
'city': undo,
'isDanger': undo
}
for sno, sData in datas.items():
for date in dates:
if date not in sData.keys():
datas[sno][date] = undoData
dates = sorted(dates)
return dates, datas
def saveImageByUrl(url, file_name):
if url == '':
return False
try:
response = requests_get(url, timeout=2)
image = response.content
with open(file_name, 'wb') as f:
f.write(image)
return True
except Exception as e:
print(f'ERROR: {e}')
return False
if __name__ == "__main__":
data = xlrd_open_workbook(SOURCE_EXCEL_PATH)
table = data.sheet_by_index(0)
nrow = table.nrows
if SOURCE_TYPE == "钉钉":
header = table.row_values(0)
idx_sno = header.index('工号')
idx_sname = header.index('提交人')
idx_location = header.index('当前时间,当前地点')
idx_image = header.index('请上传你的健康识别码')
for i in range(1, nrow):
try:
string = table.cell(i, idx_location).value
location = string[[i for i,x in enumerate(string) if x == '"' ][2]+1:][:6]
file_name = f"{table.cell(i, idx_sno).value}_{table.cell(i, idx_sname).value}_{location}"
url = table.cell(i, idx_image).value
extd_name = url[url.rindex('.')+1:]