对excel文件进行操作python（Python其实很简单第十八章）
对excel文件进行操作python（Python其实很简单第十八章）数学语文姓名年级班级
虽然Excel有很多函数，可以进行数据处理，但要想做到灵活、方便、个性化却比较困难。如果运用比较熟练，用Python处理Excel中的数据也很方便，并且将程序保存起来，一直可以解决相同的问题。
在本章中，将以如下Excel文件student.xlsx（表18-1）为例进行探讨。
|
序号 |
学号 |
姓名 |
年级 |
班级 |
语文 |
数学 |
英语 |
总分 |
名次 |
|
1 |
070101 |
王博宇 |
84 |
71 |
93 | ||||
|
2 |
070102 |
陈冠涛 |
89 |
89 |
89 | ||||
|
3 |
070103 |
李文博 |
89 |
72 |
76 | ||||
|
4 |
070204 |
姜海燕 |
89 |
89 |
89 | ||||
|
5 |
070205 |
林若溪 |
91 |
95 |
83 | ||||
|
6 |
070206 |
贾梦瑶 |
72 |
60 |
64 | ||||
|
7 |
070207 |
陈怡彤 |
77 |
74 |
87 | ||||
|
8 |
070208 |
王星辰 |
79 |
87 |
89 | ||||
|
9 |
070301 |
马美玲 |
90 |
71 |
58 | ||||
|
10 |
070302 |
蒋倩颖 |
96 |
88 |
68 | ||||
|
11 |
070303 |
侯宇博 |
97 |
100 |
95 | ||||
|
12 |
080304 |
陈琳娜 |
69 |
52 |
59 | ||||
|
13 |
080305 |
焦可菲 |
80 |
59 |
64 | ||||
|
14 |
080306 |
王思晨 |
54 |
69 |
68 | ||||
|
15 |
080401 |
张雨桐 |
89 |
67 |
55 | ||||
|
16 |
080402 |
陈丹妮 |
57 |
72 |
55 | ||||
|
17 |
080403 |
蒋璐茜 |
94 |
85 |
51 | ||||
|
18 |
080404 |
马丁 |
83 |
83 |
73 | ||||
|
19 |
080405 |
陈可儿 |
100 |
69 |
91 | ||||
|
20 |
080406 |
路易 |
72 |
87 |
85 | ||||
|
21 |
090101 |
陈佳明 |
101 |
89 |
98 | ||||
|
22 |
090104 |
王克非 |
45 |
76 |
65 | ||||
|
23 |
090301 |
马依琳 |
78 |
87 |
95 | ||||
|
24 |
091101 |
王一诺 |
108 |
104 |
98 | ||||
|
25 |
091204 |
贾宇轩 |
98 |
77 |
98 | ||||
|
26 |
090303 |
贾朝轩 |
118 |
112 |
76 | ||||
|
27 |
090808 |
白丽娜 |
87 |
86 |
76 | ||||
|
28 |
090807 |
金海通 |
76 |
89 |
98 | ||||
|
29 |
090802 |
丁能通 |
119 |
120 |
99 | ||||
|
30 |
090203 |
沈丹妮 |
109 |
108 |
99 | ||||
|
31 |
090314 |
关立新 |
103 |
107 |
100 |
在表18-1中，学号信息由6位数字字符构成，前两位表示年级信息，中间两位表示班级信息，后两位表示班内编号。
18.1关于xlrd模块
在python中，xlrd库是一个很常用的读取excel文件的库，其对excel文件的读写可以实现比较精细的控制。这是一种底层的操作，虽然现在已经不太常用，但在很多时候仍被较多使用。特别是在不知道类似pandas这样的库的使用方法的情况下，可以快速的使用xlrd库。
它是一个第三方库，可以在命令行中使用命令安装：
pip install xlrd
安装完成后，可以使用 pip list检查是否安装成功。
18.2读取Excel中的数据
先看下面的例子：
import xlrd
file='d:\\student.xlsx'
book = xlrd.open_workbook(file) # 打开工作簿
print('当前工作表名称:' book.sheet_names()) # 输出当前工作表的名称
sheet = book.sheet_by_index(0) #sheet变量赋值为第1个工作表
rows = sheet.nrows #rows为行数
cols = sheet.ncols #cols为列数
print('该工作表有%d行，%d列.'%(rows cols))
print('第三行内容为:' sheet.row_values(2))
print('第二列内容为%s 数据类型为%s.'%(sheet.col_values(1) type(sheet.col_values(1))))
print('第二列内容为%s 数据类型为%s.'%(sheet.col(1) type(sheet.col(1))))
print('第二行第二列的单元格内容为:' sheet.cell_value(1 1))
print('第三行第二列的单元格内容为:' sheet.cell(2 1).value)
print('第五行第三列的单元格内容为:' sheet.row(4)[2].value)
print('第五行第三列的单元格内容为%s 数据类型为%s'%(sheet.col(2)[4].value type(sheet.col(2)[4].value)))
print('第五行第三列的单元格内容为%s 数据类型为%s'%(sheet.col(2)[4] type(sheet.col(2)[4])))
输出结果为：
sheet页名称: ['Sheet1']
该工作表有21行，9列.
第三行内容为: ['2' '180102' '陈冠涛' '' 89.0 89.0 89.0 '' '']
第二列内容为['学号' '180101' '180102' '180103' '180204' '180205' '180206' '180207' '180208' '180301' '180302' '180303' '180304' '180305' '180306' '180401' '180402' '180403' '180404' '180405' '180406'] 数据类型为<class 'list'>.
第二列内容为[text:'学号' text:'180101' text:'180102' text:'180103' text:'180204' text:'180205' text:'180206' text:'180207' text:'180208' text:'180301' text:'180302' text:'180303' text:'180304' text:'180305' text:'180306' text:'180401' text:'180402' text:'180403' text:'180404' text:'180405' text:'180406'] 数据类型为<class 'list'>.
第二行第二列的单元格内容为: 180101
第三行第二列的单元格内容为: 180102
第五行第三列的单元格内容为: 姜海燕
第五行第三列的单元格内容为姜海燕 数据类型为<class 'str'>
第五行第三列的单元格内容为text:'姜海燕' 数据类型为<class 'xlrd.sheet.Cell'>
在上面的代码中，读取单元格数据值的方法有好几种，我们只需要掌握其中最常用的方法就可以了，如sheet.cell(2 1).value或sheet.cell_value(1 1)，因为它最接近Excel的单元格表示方法。只要你了解Excel，一定会使用这两种表示方法。
18.3向Excel中的写入数据
向Excel写入数据时，需要使用第三方库xlwt。安装xlwt的方法与安装xlrd一样，在命令行输入：
pip install xlwt
下面举例说明写入数据：
import xlwt
book = xlwt.Workbook() #新建Excel文件对象
sheet = book.add_sheet('Sheet1') #添加工作表
sheet.write(0 0 'hello') #向第1行第1列写入数据
sheet.write(1 0 '你好') #向第2行第1列写入数据
book.save('d:\hello.xls') #保存文件
18.4处理Excel文件中的数据
1、求和
既可以对Excel表横向求和（行内求和，譬如每个学生的总分），也可以纵向求和（列内求和，譬如计算语文总分、数学总分等）。
2、获取年级和班级信息
在student.xlsx中，每个学生的学号信是用一个字符串表示的，字符串长度为6个字符，前两个字符为年级信息，接下来两个字符为班级信息，最后两个字符为班内编号。截取学号的前两个字符即可得到年级信息，截取学号的中间两个字符即可得到班级信息。
3、分班级统计平均分、及格率、优秀率、双科（语文、数学）合格率、三科（语文、数学、英语）合格率。同时，生成一个新的Excel文件stuscores.xlsx，并将上述统计结果写入。
为了便于初学者阅读学习，下面代码没有进一步优化。
import xlrd #导入读取Excel文件的模块
import xlwt #导入写入Excel文件的模块
file='d:\\student.xlsx' #该文件为原始数据（见表18-1）
book = xlrd.open_workbook(file) #以读取方式打开Excel工作簿
sheet = book.sheet_by_index(0) #变量赋值为当前工作簿的第一个工作表
rows = sheet.nrows #变量rows表示工作表的行数
cols = sheet.ncols #变量cols表示工作表的列数
bookNew=xlwt.Workbook() #以写入方式建立一个新的工作簿
sheetNew1=bookNew.add_sheet('Sheet1') #为工作簿添加一个新的工作表
sheetNew2=bookNew.add_sheet('Sheet2') #为工作簿添加一个新的工作表
#计算第5列(语文)、第6列(数学)、第7列(英语)的和（列号从0开始）
sum5=0 #变量sum5表示第5列和
sum6=0 #变量sum6表示第6列和
sum7=0 #变量sum7表示第7列和
#遍历第1行到最后1行（第0行为标题行，非成绩，最后一行为rows-1）
for i in range(1 rows):
sum5 =float(sheet.cell(i 5).value) # float函数为了将非数值转为为浮点数
sum6 =float(sheet.cell(i 6).value)
sum7 =float(sheet.cell(i 7).value)
col8=[0]
#列表col8存放每一个学生的总分，即每一行第5、6、7列的和，第一个元素为0是为了给标题行占位，这样列表的索引号正好与表格的行号对应
col3=[''] #列表col3存放年级信息，第一个元素为空字符
col4=[''] #列表col4存放班级信息，第一个元素为空字符
#遍历各行，为第3 4 8列准备数据，数据暂时存放在列表中
for i in range(1 rows):
total=0 #变量total存放每个学生的三科总分
for j in range(5 8): #遍历行内的第5至7列
total =float(sheet.cell(i j).value)
col8.append(total) #将合计追加到列表col8中
strno=str(sheet.cell(i 1).value) #变量strno为学号信息
col3.append(strno[:2]) #对字符串切片获取年级信息，存入列表col3中
col4.append(strno[:4])
#对字符串切片获取班级信息，存入列表col3中（取了前四位，包含了年级信息）
for i in range(rows): #遍历各行，从第0行到最后一行
for j in range(cols): #遍历各列，从第0列到最后一列
if sheet.cell(i j).value!='':
sheetNew1.write(i j sheet.cell(i j).value)
#如果原表单元格非空，则将原表单元格的值写入新表中对应的单元格中
elif j==3:
sheetNew1.write(i 3 col3[i]) #将列表col3中的值写入对应单元格
elif j==4:
sheetNew1.write(i 4 col4[i]) #将列表col4中的值写入对应单元格
elif j==8:
sheetNew1.write(i 8 col8[i]) #将列表col8中的值写入对应单元格
sheetNew1.write(rows 5 sum5) #在第rows行（新增行）第5列写入语文合计值
sheetNew1.write(rows 6 sum6) #在第rows行（新增行）第6列写入数学合计值
sheetNew1.write(rows 7 sum7) #在第rows行（新增行）第6列写入英语合计值
'''
至此，完成了新建Excel文件中Sheet1工作表数据的填充工作。
在表18-1中还有一个根据总分对学生进行排名的问题，但如果采用通常的算法效率太低，
还是建议采用Excel函数进行排名为宜，这里不再赘述。
'''
grade=[] #列表grade用于存放年级编号
for i in col3[1:]: #遍历列表col3（索引号为0的元素除外）
if i not in grade:
#为了保证列表grade中的元素值都是唯一的，即年级编号不重复
grade.append(i)
grade.sort #对列表grade排序
classno=[] #列表classno用于存放班级编号
for i in col4[1:]: #遍历列表col4（索引号为0的元素除外）
if i not in classno:
#为了保证列表classno中的元素值都是唯一的，即班级编号不重复
classno.append(i)
classno.sort #对列表classno排序
sumChnC=0 #全班语文总分
sumMathC=0 #全班数学总分
sumEnC=0 #全班英语总分
sumChnG=0 #全级语文总分
sumMathG=0 #全级数学总分
sumEnG=0 #全级英语总分
numClass=0 #班级学生人数
numGrade=0 #年级学生人数
passChnC=0 #全班语文及格人数
passMathC=0 #全班数学及格人数
passEnC=0 #全班英语及格人数
passChnG=0 #全级语文及格人数
passMathG=0 #全级数学及格人数
passEnG=0 #全级英语及格人数
excellenChnC=0 #全班语文优秀人数
excellenMathC=0 #全班数学优秀人数
excellenEnC=0 #全班英语优秀人数
excellenChnG=0 #全级语文优秀人数
excellenMathG=0 #全级数学优秀人数
excellenEnG=0 #全级英语优秀人数
pass2C=0 #全班双科及格人数
pass3C=0 #全班三科及格人数
pass2G=0 #全级双科及格人数
pass3G=0 #全级三科及格人数
excellen2C=0 #全班双科优秀人数
excellen3C=0 #全班三科优秀人数
excellen2G=0 #全级双科优秀人数
excellen3G=0 #全级三科优秀人数
passChn=72 #语文及格线
passMath=72 #数学及格线
passEn=60 #英语及格线
excellenChn=96 #语文优秀线
excellenMath=96 #数学优秀线
excellenEn=80 #英语优秀线
n=1 #变量n表示'班级成绩统计表'的行号
font = xlwt.Font()
#创建字体对象实例，并初始化
font.height = 20 * 11
#设置字体大小，11为字号，20为衡量单位
font.bold = True #字体加粗
alignment = xlwt.Alignment() #创建单元格对齐方式实例，并初始化
alignment.horz = 0x02 #设置单元格水平对齐方式
# 0x01(左端对齐)、0x02(水平方向上居中对齐)、0x03(右端对齐)
alignment.vert = 0x01
# 0x00(上端对齐)、0x01(垂直方向上居中对齐)、0x02(底端对齐)
style = xlwt.XFStyle() #创建式样实例，并初始化
style.alignment = alignment # 设置式样的对齐方式属性值
style.font = font # 设置式样的字体属性
sheetNew2.write_merge(0 0 0 16 '班级成绩统计表' style)
'''
xlwt模块的合并单元格函数
格式：worksheet.write_merge(开始行 结束行 开始列 结束列 字符串 style)
'''
sheetNew2.write(n 0 '序号' style) #第1行第0列单元格的内容，以下类似
sheetNew2.write(n 1 '年级' style)
sheetNew2.write(n 2 '班级' style)
sheetNew2.write(n 3 '人数' style)
sheetNew2.write(n 4 '语均' style)
sheetNew2.write(n 5 '语及' style)
sheetNew2.write(n 6 '语优' style)
sheetNew2.write(n 7 '数均' style)
sheetNew2.write(n 8 '数及' style)
sheetNew2.write(n 9 '数优' style)
sheetNew2.write(n 10 '英均' style)
sheetNew2.write(n 11 '英及' style)
sheetNew2.write(n 12 '英优' style)
sheetNew2.write(n 13 '双合' style)
sheetNew2.write(n 14 '双优' style)
sheetNew2.write(n 15 '三合' style)
sheetNew2.write(n 16 '三优' style)
'''
以下代码为统计各班级、各年级各科平均成绩、及格率、优秀率、双科合格率、
双科优秀率、三科合格率、三科优秀率，并将这些数据写入Excel文件的对应单元格中。
'''
for g in grade: #遍历各年级
for c in classno: #遍历各班级
if g != c[:2]: #判断班级是否是年级所属的班级
continue #若不是年级所属班级，遍历下一个班级
n =1 #变量n控制写入Excel行号变化
for i in range(1 rows): #遍历原始数据表student.xlsx各行
strno=str(sheet.cell(i 1).value) #变量strno赋值为学号值
if strno[:2]==g and strno[:4]==c: #如果是该年级和班级的学生
fpass2=0 #变量fpass2表示两科及格科目数
fpass3=0 #变量fpass3表示三科及格科目数
fexcellen2=0 #变量fexcellen2表示两科优秀科目数
fexcellen3=0 #变量fexcellen3表示三科优秀科目数
numClass =1 #变量numClass表示班级学生人数
sumChnC =float(sheet.cell(i 5).value)
#变量sumChnC记录全班语文总分
sumMathC =float(sheet.cell(i 6).value)
#变量sumMathC记录全班数学总分
sumEnC =float(sheet.cell(i 7).value)
#变量sumEnC记录全班数学总分
if float(sheet.cell(i 5).value) >= passChn:
#如果该生语文成绩及格
passChnC =1 #记录全班语文及格人数
fpass2 =1 #记录两科及格科目数
fpass3 =1 #记录三科及格科目数
if float(sheet.cell(i 5).value)>= excellenChn:
#如果该生语文成绩优秀
excellenChnC =1 #记录全班语文优秀人数
fexcellen2 =1 #记录两科优秀科目数
fexcellen3 =1 #记录三科优秀科目数
if float(sheet.cell(i 6).value) >= passMath:
#如果该生数学成绩及格
passMathC =1 #记录全班数学及格人数
fpass2 =1 #记录两科及格科目数
fpass3 =1 #记录三科及格科目数
if float(sheet.cell(i 6).value) >= excellenMath:
#如果该生数学成绩优秀
excellenMathC =1 #记录全班数学优秀人数
fexcellen2 =1 #记录两科优秀科目数
fexcellen3 =1 #记录三科优秀科目数
if float(sheet.cell(i 7).value) >= passEn:
#如果该生英语成绩及格
passEnC =1 #记录全班英语及格人数
fpass3 =1 #记录三科及格科目数
if float(sheet.cell(i 7).value) >= excellenEn:
#如果该生英语成绩优秀
excellenEnC =1 #记录全班英语优秀人数
fexcellen3 =1 #记录三科优秀科目数
if fpass2 == 2: #条件成立表明该生两科合格
pass2C =1 #班级两科合格人数增加1
if fpass3 == 3: #条件成立表明该生三科合格
pass3C =1 #班级三科合格人数增加1
if fexcellen2 == 2: #条件成立表明该生两科优秀
excellen2C =1 #班级两科优秀人数增加1
if fexcellen3 == 3: #条件成立表明该生三科优秀
excellen3C =1 #班级三科优秀人数增加1
sheetNew2.write(n 0 n-1) #写入序号
sheetNew2.write(n 2 c) #写入班级编号
sheetNew2.write(n 3 numClass) #写入班级人数
sheetNew2.write(n 4 round(sumChnC/numClass 2))
#写入全班语文平均分
sheetNew2.write(n 5 round(passChnC/numClass*100 2))
#写入全班语文及格率
sheetNew2.write(n 6 round(excellenChnC/numClass*100 2))
#写入全班语文优秀率
sheetNew2.write(n 7 round(sumMathC/numClass 2))
#写入全班数学平均分
sheetNew2.write(n 8 round(passMathC/numClass*100 2))
#写入全班数学及格率
sheetNew2.write(n 9 round(excellenMathC/numClass*100 2))
#写入全班数学优秀率
sheetNew2.write(n 10 round(sumEnC/numClass 2))
#写入全班英语平均分
sheetNew2.write(n 11 round(passEnC/numClass*100 2))
#写入全班英语及格率
sheetNew2.write(n 12 round(excellenEnC/numClass*100 2))
#写入全班英语优秀率
sheetNew2.write(n 13 round(pass2C/numClass*100 2))
#写入全班两科合格率（语文、数学两科同时达到及格线）
sheetNew2.write(n 14 round(excellen2C/numClass*100 2))
#写入全班两科优秀率（语文、数学两科同时达到优秀线）
sheetNew2.write(n 15 round(pass3C/numClass*100 2))
#写入全班三科合格率（语文、数学、英语三科同时达到及格线）
sheetNew2.write(n 16 round(excellen3C/numClass*100 2))
#写入全班三科优秀率（语文、数学、英语三科同时达到优秀线）
numGrade =numClass #将班级学生人数增加到年级学生人数
sumChnG =sumChnC #将班级语文总分增加到年级语文总分
sumMathG =sumMathC #将班级数学总分增加到年级数学总分
sumEnG =sumEnC #将班级英语总分增加到年级英语总分
passChnG =passChnC #将班级语文及格人数增加到年级语文及格人数
passMathG =passMathC #将班级数学及格人数增加到年级数学及格人数
passEnG =passEnC #将班级英语及格人数增加到年级英语及格人数
excellenChnG =excellenChnC
#将班级语文优秀人数增加到年级语文优秀人数
excellenMathG =excellenMathC
#将班级数学优秀人数增加到年级数学优秀人数
excellenEnG =excellenEnC
#将班级英语优秀人数增加到年级优秀优秀人数
pass2G =pass2C
#将班级两科合格人数增加到年级两科合格人数
pass3G =pass3C
#将班级三科合格人数增加到年级三科合格人数
excellen2G =excellen2C
#将班级两科合优秀人数增加到年级两科优秀人数
excellen3G =excellen3C
#将班级三科合优秀人数增加到年级三科优秀人数
numClass=0
#变量numClass(班级人数)设置为0，为统计下一个班级的信息做准备
sumChnC=0 #与上语句类似，下同
sumMathC=0
sumEnC=0
passChnC=0
passMathC=0
passEnC=0
excellenChnC=0
excellenMathC=0
excellenEnC=0
pass2C=0
pass3C=0
excellen2C=0
excellen3C=0
n =1 #行号增加1，为了在下一行填入年级的统计数据
sheetNew2.write(n 0 n-1) #写入序号
sheetNew2.write(n 1 g) #写入年级编号
sheetNew2.write(n 3 numGrade) #写入年级人数
sheetNew2.write(n 4 round(sumChnG/numGrade 2))
#写入全级语文平均分
sheetNew2.write(n 5 round(passChnG/numGrade*100 2))
#写入全级语文及格率
sheetNew2.write(n 6 round(excellenChnG/numGrade*100 2))
#写入全级语文优秀率
sheetNew2.write(n 7 round(sumMathG/numGrade 2))
#写入全级数学平均分
sheetNew2.write(n 8 round(passMathG/numGrade*100 2))
#写入全级数学及格率
sheetNew2.write(n 9 round(excellenMathG/numGrade*100 2))
#写入全级数学优秀率
sheetNew2.write(n 10 round(sumEnG/numGrade 2))
#写入全级英语平均分
sheetNew2.write(n 11 round(passEnG/numGrade*100 2))
#写入全级英语及格率
sheetNew2.write(n 12 round(excellenEnG/numGrade*100 2))
#写入全级英语优秀率
sheetNew2.write(n 13 round(pass2G/numGrade*100 2))
#写入全级两科合格率（语文、数学两科同时达到及格线）
sheetNew2.write(n 14 round(excellen2G/numGrade*100 2))
#写入全级两科优秀率（语文、数学两科同时达到优秀线）
sheetNew2.write(n 15 round(pass3G/numGrade*100 2))
#写入全级三科合格率（语数英三科同时达到及格线）
sheetNew2.write(n 16 round(excellen3G/numGrade*100 2))
#写入全级三科优秀率（语数英三科同时达到优秀线）
numGrade=0
#变量numGrade(年级人数)设置为0，为统计下一个年级的信息做准备
sumChnG=0 #与上语句类似，下同
sumMathG=0
sumEnG=0
passChnG=0
passMathG=0
passEnG=0
excellenChnG=0
excellenMathG=0
excellenEnG=0
pass2G=0
pass3G=0
excellen2G=0
excellen3G=0
bookNew.save('d:\stuscores.xlsx') #保存Excel文件
运行结果如图18-2所示。对于表格的各种格式设置，在Excel中设置远比用Python命令设置更方便，所以仅仅用Python完成各种计算即可，然后在Excel中按照需要完成各种格式设置。
图18-2
