对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