pythone excel_pythone函数基础（11）读，写，修改EXCEL

#读EXCEL需要导入xlrd模块---在python控制台pip install xlrd模块

import xlrd

book = xlrd.open_workbook('stu3.xls')

sheet = book.sheet_by_index(0)

# sheet = book.sheet_by_name('sheet1')

# print(sheet.cell(0,0).value)#获取指定单元格的内容

# print(sheet.cell(1,0).value)

# print(sheet.row_values(0)) #获取整行的数据

# print(sheet.row_values(1))

# print(sheet.col_values(0))#获取整列的数据

# print(sheet.col_values(1))

print(sheet.nrows) #行数

print(sheet.ncols) #列数

for row in range(1,sheet.nrows):

print(sheet.row_values(row))

写EXCEL需要导入 import xlwt -----在python控制台pip install xlwt模块

import xlwt

book = xlwt.Workbook() #新建一个excel

sheet = book.add_sheet('sheet1') #添加一个sheet页

# sheet.write(0,0,'编号')

# sheet.write(0,1,'名字')

# sheet.write(0,2,'性别')

#

# sheet.write(1,0,'1')

# sheet.write(1,1,'马春波')

# sheet.write(1,2,'男')

stu_info = [

['编号','姓名','密码','性别','地址'],

[1,'machunbo','sdfsd23sdfsdf2','男','北京'],

[2,'machunbo2','sdfsd23sdfsdf2','男','北京'],

[3,'machunb3','sdfsd23sdfsdf2','男','北京'],

[4,'machunbo4','sdfsd23sdfsdf2','男','北京'],

[5,'machunbo5','sdfsd23sdfsdf2','男','北京'],

[6,'machunbo6','sdfsd23sdfsdf2','男','北京'],

[7,'machunbo6','sdfsd23sdfsdf2','男','北京'],

[8,'machunbo6','sdfsd23sdfsdf2','男','北京'],

[9,'machunbo6','sdfsd23sdfsdf2','男','北京'],

[10,'machunbo6','sdfsd23sdfsdf2','男','北京'],

[11,'machunbo6','sdfsd23sdfsdf2','男','北京'],

]

#6行5列

# row = 0 #行

# for stu in stu_info:

# sheet.write(row,0,stu[0])

# sheet.write(row,1,stu[1])

# sheet.write(row,2,stu[2])

# sheet.write(row,3,stu[3])

# sheet.write(row,4,stu[4])

# row+=1

# row = 0 #行

# for stu in stu_info:

# #stu

# col = 0 # 列

# # [1, 'machunbo', 'sdfsd23sdfsdf2', '男', '北京'],

# for s in stu: #控制列

# sheet.write(row,col,s) #0 3 男

# col+=1

# row+=1

for index,value in enumerate(stu_info):

# index 0

# value ['编号','姓名','密码','性别','地址']

#index 1

#value [1,'machunbo','sdfsd23sdfsdf2','男','北京']

for index2,v2 in enumerate(value):

print(index,index2,v2)

#0 1

#1 machunbo

#2 sdfsd23sdfsdf2

#4 北京

sheet.write(index,index2,v2)

book.save('stu3.xls') #wps xls xlsx ,微软的office xls

修改EXCEL，需要import xlutils-----在python控制台pip install xlwt模块

import xlrd

from xlutils import copy

#1、先打开原来的excel

#2、复制一份

#3、在复制的excel上修改

#4、保存

book = xlrd.open_workbook('stu3.xls')

new_book = copy.copy(book) #

sheet = new_book.get_sheet(0) #修改excel的时候，得用get_sheet()

sheet.write(0,0,'id')

sheet.write(0,3,'password')

new_book.save('stu3.xls')