1、读excel
import xlrd book = xlrd.open_workbook('金牛座.xls') sheet = book.sheet_by_index(0) #sheet = book.sheet_by_name('sheet1') print(sheet.nrows) #excel里面有多少行 print(sheet.ncols) #excel中有多少列 print(sheet.cell(0,0).value)#获取指定单元格的内容 print(sheet.cell(0,1).value) #获取整行整列的内容,将获取到的内容存到list里 print(sheet.row_values(1)) print(sheet.col_values(1)) for i in range(sheet.nrows):#循环获取每行的内容 print(sheet.row_values(i))
2、写excel
import xlwt #只能写excel import xlrd #只能读excel import xlutils #修改excel!重要! #写excel book = xlwt.Workbook() sheet = book.add_sheet('sheet1') sheet.write(0,0,'id') #指定行和列写内容 sheet.write(0,1,'username') sheet.write(0,2,'password') sheet.write(1,0,'1') sheet.write(1,1,'linhuizhen') sheet.write(1,2,'123456') #################################### stus = [ [1,'njf','1234'], [2,'xiaojun','1234'], [3,'hailong','1234'], [4,'xiaohei','1234'], [5,'xiaohei','1234'], [6,'xiaohei','1234'], [7,'xiaohei','1234'], [8,'xiaohei','1234'], [9,'xiaohei','1234'], ] line = 0#控制的是行 for stu in stus: #外面的循环控制 行 #stu = [1,'njf','1234'] col = 0 # 控制列 for s in stu: #内部循环控制 列 #0行 0列 1 #0行 1列 njf #0行 2列 1234 sheet.write(line,col,s) col += 1 line += 1 book.save('stu.xls')#只能用.xls结尾 ''' #双重循环,循环了5*10=50次 for i in range(5): for j in range(10): print('haha') '''
3、修改excel
#修改excel很重要!与xlrd配合用 import xlutils import xlrd from xlutils import copy #从xlutils中导入copy这个功能 book = xlrd.open_workbook('stu.xls') #先用xlrd打开一个excel new_book = copy.copy(book) #然后用xlutils里面的copy功能,复制一个excel sheet = new_book.get_sheet(0)#获取sheet页 sheet.write(0,1,'test') sheet.write(1,1,'test2') new_book.save('stu.xls')