border.right = border.THIN
border.top = border.THIN
border.bottom = border.THIN
font = Font()
font.bold = True
alignment = Alignment()
alignment.horz = alignment.HORZ_CENTER
pattern = Pattern()
pattern.pattern = pattern.SOLID_PATTERN
pattern.pattern_fore_colour = 0x35
title_style.borders = border
title_style.font = font
title_style.alignment = alignment
title_style.pattern = pattern
for i in range(len(title)):
sheet.write(4, i + 4, title[i], title_style)
content = [[1, 'SMITH', '测试', '蔡昶', '2020-10-10'],
[2, '凤姐', '开发', '蔡昶', '2020-10-11']]
content_style = XFStyle()
border = Borders()
border.left = border.THIN
border.right = border.THIN
border.top = border.THIN
border.bottom = border.THIN
def genTotalSheet(lis):
""" lis is UserRecord list """
global wb
wb = Workbook(encoding='gbk')
timeStyle = XFStyle()
timeStyle.num_format_str = 'h:mm'
lateStyle = XFStyle()
lateStyle.num_format_str = 'h:mm'
lateStyle.font = Font()
### when isLate = false, set colour_index = 0 will still get red font in Excel.
lateStyle.font.colour_index = 2
earlyStyle = XFStyle()
earlyStyle.num_format_str = 'h:mm'
earlyStyle.font = Font()
earlyStyle.font.colour_index = 7
sheet1 = wb.add_sheet(TOTAL_SHEET_NAME)
rowNum = 0
for i, s in enumerate(totalSheetTitles): # write header
sheet1.write(0, i, str(s))
rowNum += 1
for a in lis:
for colNum, colName in enumerate(totalSheetTitles):
if(colNum == 0):
sheet1.write_merge(rowNum, rowNum + 1, colNum, colNum, a.userId)
elif(colNum == 1):
sheet1.write_merge(rowNum, rowNum + 1, colNum, colNum, a.userName)
else:
onTime = ''
offTime = ''
if(colName in a.recDict):
times = a.recDict[colName]
if(len(times) >= 2):
if(len(times) > 2):
print '! ' + a.userName + ' ' + monthStr + str(colName) + ' 两条以上打卡记录.'
onTime = times[0]
offTime = times[len(times) - 1]
elif(len(times) == 1):
onlyTime = strToTime(times[0])
if(onlyTime.hour < divider):
onTime = times[0]
else:
offTime = times[0]
onTime = strToTime(onTime)
offTime = strToTime(offTime)
# if(onTime and offTime):
# a.totalTime += (offTime - onTime).seconds / 3600
isLate, isEarly = checkTime(onTime, offTime, colName)
if(isLate): # late
# a.late += 1
sheet1.write(rowNum, colNum, onTime, lateStyle)
else:
sheet1.write(rowNum, colNum, onTime, timeStyle)
if(isEarly): # early
# a.early += 1
sheet1.write(rowNum + 1, colNum, offTime, earlyStyle)
else:
sheet1.write(rowNum + 1, colNum, offTime, timeStyle)
rowNum += 2
getTargetFileName()
try:
wb.save(targetFileName)
addLog('"' + targetFileName + '" is in the same directory of the .txt file.')
except IOError: #IOError: [Errno 13] Permission denied: '***.xls'
addLog('please close ' + targetFileName + ' and retry')
