Python-Excel的操作

@TOC## 标题
涉及到Excel的读写，样式设置，新增表格等。测试excel文末有下载链接。

#-*-coding:gb2312-*-     #中文
import xlrd
import xlwt
import numpy as np

#打开路径
openFilePath = ""
#保存路径
saveFilePath = ""
with open('filePath.txt') as file_object: 
	lines = file_object.readlines()
	openFilePath = lines[0].strip()
	saveFilePath = lines[1].strip()
file_object.close()

#打开源数据excel
workbook = xlrd.open_workbook(openFilePath)
sheet0 = workbook.sheets()[0]
rows = sheet0.nrows
cols = sheet0.ncols


termList = []
subjectList = []
allDataMap = {}

#获取学期信息
for i in range(cols):
	colValue = sheet0.row_values(0)[i]
	if(len(colValue) and i != 0):
		termList.append(colValue)
#获取科目信息		
for i in range(6,15):
	subjectList.append(sheet0.row_values(1)[i])
#print(termList)
#print(subjectList)
termLen = len(termList)

#读取源Excel数据到缓存
for i in range(2,rows):
	name = sheet0.row_values(i)[3]
	allDataMap[name] = {}
	for j in range(len(termList)):
		allDataMap[name][termList[j]] = {}
		for k in range(len(subjectList)):
			allDataMap[name][termList[j]][subjectList[k]] = sheet0.row_values(i)[6+j*9+k]

#print(allDataMap)
#写总学生数文件
with open('sum.txt', 'w') as file_object_sum: 
	file_object_sum.write(str(len(allDataMap)))
file_object_sum.close()
#设置居中
alignment = xlwt.Alignment()
alignment.horz = xlwt.Alignment.HORZ_CENTER
alignment.vert = xlwt.Alignment.VERT_CENTER
#定义Excel表格样式
def set_style(name,height,bold=False):
	style = xlwt.XFStyle()
	font = xlwt.Font()
	font.name = name
	font.bold = bold
	font.color_index = 4
	font.height = height
	style.font = font
	style.alignment = alignment
	return style

#根据每个学生生成相应的excel
studentNum = 1
for name,termMsg in allDataMap.items():
	#写进度文件
	with open('progress.txt', 'w') as file_object_progress: 
		file_object_progress.write(str(studentNum))
	file_object_progress.close()
	
	termMsg['平均值']={}
	writeWorkBook = xlwt.Workbook()
	sheet_write = writeWorkBook.add_sheet('个人成绩打印表',cell_overwrite_ok=True)

	#写第一行
	sheet_write.write(0,0,name,set_style('宋体',290,True))
	sheet_write.col(0).width = 200*25
	
	for i in range(1,13):
		if(i%2 == 0):
			sheet_write.write(0,i,'班排',set_style('宋体',240)) 			
		else:
			sheet_write.write(0,i,subjectList[int(i/2)],set_style('宋体',240))
	sheet_write.write(0,13,'总分',set_style('宋体',240,True)) 		 
	sheet_write.write(0,14,'年排',set_style('宋体',240,True))
	sheet_write.write(0,15,'班排',set_style('宋体',240,True))
	#写每学期成绩
	rowNum = 1
	allTermSubGrade = {} #每列成绩
	for listIndex in range(1,16):
		allTermSubGrade[listIndex] = []
	
	for termName,gradeList in termMsg.items():	
		sheet_write.write(rowNum,0,termName,set_style('宋体',240))				
		#统计平均值
		if(termName == '平均值'):
			sheet_write.write(rowNum,0,termName,set_style('宋体',240,True))
			for subjectIndex in range(1,16):
				#print(allTermSubGrade[subjectIndex])
				sumValue = int(0)
				length = len(allTermSubGrade[subjectIndex])	
				for values in allTermSubGrade[subjectIndex]:
					if len(str(values)) != 0:
						sumValue = int(values)+sumValue			
				avg = round(sumValue/length)
				sheet_write.write(rowNum,subjectIndex,avg,set_style('宋体',240,True))
			#print(allTermSubGrade)
		else:
			#记录每科成绩并统计班排	
			for sujIndex in range(1,16):
				if sujIndex >= 13 :#最后三列
					allgrade = gradeList[subjectList[sujIndex-7]]
					if isinstance(allgrade,float):
						allgrade = round(allgrade)
					allTermSubGrade[sujIndex].append(allgrade)
					sheet_write.write(rowNum,sujIndex,allgrade,set_style('宋体',240,True))
				else:#
					if sujIndex%2 != 0 : #科目列
						subGrade = gradeList[subjectList[int(sujIndex/2)]]
						if isinstance(subGrade,float):
							subGrade = round(subGrade)
						allTermSubGrade[sujIndex].append(subGrade)
						sheet_write.write(rowNum,sujIndex,subGrade,set_style('宋体',240))
					else:#班排							
						#统计班排
						sub_name = subjectList[int((sujIndex-1)/2)]
						sub_grade = 0.0
						subAllGrade = [] #存储该学期所有同学成绩
						for student in allDataMap.keys():
							if isinstance(allDataMap[student][termName][sub_name],float):
								subAllGrade.append(allDataMap[student][termName][sub_name])
								if student == name:
									#print("find the student,grade = :"+str(allDataMap[student][termName][sub_name]))
									sub_grade = allDataMap[student][termName][sub_name]
						#print(subAllGrade)						
						sortedList = sorted(subAllGrade,reverse=True)#从大到小排序
						if sub_grade in sortedList :
							sortIndex = sortedList.index(sub_grade)+1
						allTermSubGrade[sujIndex].append(sortIndex)
						sheet_write.write(rowNum,sujIndex,sortIndex,set_style('宋体',240))
		rowNum+=1
	#保存Excel
	writeWorkBook.save(saveFilePath+'/个人成绩_'+name+'.xlsx')
	studentNum += 1
	

测试Excel表格下载链接：
https://pan.baidu.com/s/1km_hPmaXshSsGvLfsAGwaQ
注意： 运行目录下需要创建名为filePath.txt的如下所示文档：

第一行：excel表格路径
第二行：生成excel文件的路径