Python其实很简单 第十八章 用Python操作Excel文件

虽然Excel有很多函数，可以进行数据处理，但要想做到灵活、方便、个性化却比较困难。如果运用比较熟练，用Python处理Excel中的数据也很方便，并且将程序保存起来，一直可以解决相同的问题。

在本章中，将以如下Excel文件student.xlsx（表18-1）为例进行探讨。

在表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'],数据类型为.

第二列内容为[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'],数据类型为.

第二行第二列的单元格内容为: 180101

第三行第二列的单元格内容为: 180102

第五行第三列的单元格内容为: 姜海燕

第五行第三列的单元格内容为姜海燕,数据类型为

第五行第三列的单元格内容为text:'姜海燕',数据类型为

在上面的代码中，读取单元格数据值的方法有好几种，我们只需要掌握其中最常用的方法就可以了，如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