前几天就业管理群里需要整理已经签约的学生的相关信息,这次该我整理了,本以为很简单,就是下载execl,删除不需要的列而已呗,但是是我想象的太美好。汇总的execl需要的很多信息 签约execl里面没有,还要去学生execl里面找。一个一个吗??不可能的,几百号人呢。
我本来用的execl的LOOKUP函数,但是找完之后发现还要对学生根据班级排序,然后增加序号列等等等。那次的汇总execl的过程太令我痛苦了,碰巧看到了python处理execl就来试一下。
import xlrd
import xlwt
import time
workbook = xlwt.Workbook(encoding='utf-8')
write_sheet = workbook.add_sheet('Sheet1')
style = xlwt.XFStyle()
font = xlwt.Font()
font.name = '宋体'
font.height = 20 * 12
font.bold = False
font.underline = False
font.italic = False
style.font = font
write_sheet.write(0, 0, "列头", style)
write_sheet.write(0, 1, "列头", style)
write_sheet.write(0, 2, "列头", style)
write_sheet.write(0, 3, "列头", style)
write_sheet.write(0, 4, "列头", style)
write_sheet.write(0, 5, "列头", style)
write_sheet.write(0, 6, "列头", style)
write_sheet.write(0, 7, "列头", style)
write_sheet.write(0, 8, "列头", style)
write_sheet.write(0, 9, "列头", style)
write_sheet.write(0, 10, "列头", style)
write_sheet.write(0, 11, "列头", style)
write_sheet.write(0, 12, "列头", style)
data_excel1 = xlrd.open_workbook("D:\签约信息.xls")
data_sheet1 = data_excel1.sheet_by_index(0)
row1 = data_sheet1.nrows
col1 = data_sheet1.ncols
data_excel2 = xlrd.open_workbook("D:\学生信息.xlsx")
data_sheet2 = data_excel2.sheet_by_index(0)
row2 = data_sheet2.nrows
col2 = data_sheet2.ncols
colValue1 = data_sheet1.col_values(2)
colValue2 = data_sheet2.col_values(0)
global x
x = 1
for i in range(3, len(colValue2)):
for j in range(1, len(colValue1)):
if colValue2[i] == colValue1[j]:
write_sheet.write(x, 0, x, style)
write_sheet.write(x, 1, "XX学院", style)
rowValue = data_sheet2.row_values(i)
for colNum in range(0, 4):
write_sheet.write(x, colNum + 2, rowValue[colNum], style)
rowValue = data_sheet1.row_values(j)
y = 6
for colNum in [47, 13, 19, 21, 22, 23, 61]:
write_sheet.write(x, y, rowValue[colNum], style)
y = y + 1
x = x + 1
workbook.save('D:\'+time.strftime("%Y-%m-%d")+'签约学生汇总.xls')