#!/usr/bin/env python # _*_ coding:utf-8 _*_ #操作Excel表格 #先导入：pip install xlrd  ;  pip install xlwt  ;   pip install xlutils import xlwt #创建表格 book = xlwt.Workbook()#创建一个Excel #创建sheet页 sheet = book.add_sheet('sheet1')#创建一个sheet页 #表格中单元格写入数据 sheet.write(0,0,"姓名") sheet.write(0,1,"年龄") sheet.write(0,2,"身高") sheet.write(1,0,"孙树江") sheet.write(1,1,"38") sheet.write(1,2,"150cm") #保存表格 book.save('孙树江.xls')#后缀必须是xls title = ['姓名','班级','住址','手机号'] data = [     ['孙树江','巨蟹座','中南海',110],     ['贾梦媛', '巨蟹座', '紫禁城', 119],     ['老孙', '巨蟹座', '花果山', 0],     ['光远', '巨蟹座', '天空', 120] ] i = 0#控制列 for j in title:     sheet.write(0,i,j)     i+=1#每次循环的时候加1 line = 1#控制行 # for d in data: #     sheet.write(line,0,d[0]) #     sheet.write(line,1,d[1]) #     sheet.write(line,2,d[2]) #     sheet.write(line,3,d[3]) #     line+=1 # for d in data: #     row = 0 #     for dd in d: #         sheet.write(line,row,dd) #         row+=1 #     line+=1 #列表或者元组写入表格方法 for d in range(len(data)):     for j in range(len(data[d])):         sheet.write(d+1,j,data[d][j]) book.save('stu.xls') #练习题 import pymysql,xlwt from pymysql.cursors import DictCursor #将数据转化为字典 #连接mysql coon = pymysql.connect(host='211.149.218.16',port=3306,user='jxz',passwd='123456',db='jxz',charset='utf8') #建立游标,仓库管理员 cur = coon.cursor() #cur1 = coon.cursor(DictCursor) #定义sql #insert_sql = 'insert into stu VALUE(11,"test01")' #sql = 'select * from stu limit 3' #sql = 'select * from stu limit 2,4'#从第3条，即下标为2的数据开始取值，取4条 sql = 'select * from stu limit 10' #执行sql语句 cur.execute(sql) #cur1.execute(sql) #cur.execute(insert_sql) #获取sql执行的结果 res = cur.fetchall() print(res) print(type(res)) #res1 = cur1.fetchall() book = xlwt.Workbook()#创建一个Excel sheet = book.add_sheet('sheet1')#创建一个sheet页 for i in range(len(res)):     for j in range(len(res[i])):         sheet.write(i,j,res[i][j]) book.save('stu.xls') #读取 # import xlrd # book = xlrd.open_workbook('stu_new.xls') # sheet = book.sheet_by_name('sheet1') # rows = sheet.nrows # cols = sheet.ncols # print(sheet.nrows) # print(sheet.ncols) # for i in range(rows): #     print(sheet.row_values(i))#获取第i行数据 # print(sheet.cell(1,1)) # #打印：text:'update语句测试' # print(sheet.cell(1,1).value)#获取第2行第2个值 # #打印：update语句测试 # import xlrd,pymysql,xlwt # # def coon_mysql(sql): #     coon = pymysql.connect(host='211.149.218.16',user='jxz',password='123456',db='jxz',charset='utf8') #     cur = coon.cursor() #     cur.execute(sql) #     coon.commit() #     res = cur.fetchall() #     print(res) #     cur.close() #     coon.close() #     return res # book = xlrd.open_workbook('stu.xls') # #sheet1 = book.add_sheet('sheet1') # sheet1 = book.sheet_by_name('sheet1') # for i in range(sheet.nrows): #     for j in range(sheet.ncols): #         name = sheet.cell(i+1,0).value #         coon_mysql('insert into jxz_stu (name) values (name)') #         if sheet.cell(i+1,j+1).value == 1: #             col_name = sheet.cell(0,j+1).value #             coon_mysql('insert into jxz_stu (col_name) values("交")') #         else: #             coon_mysql('insert into jxz_stu (col_name) values("无")') #             #insert into jxz_stu  (name,cl,c2,c3) values ('牛寒阳','交','交','交'); #老师： # def readExcel(file_name): #     import xlrd,pymysql #     book = xlrd.open_workbook(file_name) #     sheet = book.sheet_by_name('Sheet1') #     def is_send(a): #         if a: #             return '交' #         else: #             return '无' # #     for i in range(sheet.nrows): #         line = sheet.row_values(i) #         name = line[0] #         c1 = line[1] #         c2 = line[2] #         c3 = line[3] #         sql = "insert into jxz_stu values('{name}','{c1}','{c2}','{c3}'".format(name=name,c1=c1,c2=c2,c3=c3) #readExcel('stu.xlsx') #修改表格 import xlrd from xlutils.copy import copy #打开需要修改的表格--》打开备用表格--》将修改的数据保存到第二个--》将原来的表格删除，新表格重命名为原名称 book = xlrd.open_workbook('stu.xlsx')#打开一个表格 new_book = copy(book)#拷贝一份表格 ws = new_book.get_sheet(0)#获取到第一个sheet #ws = new_book.sheet_by_index(0) ws.write(2,1,'test') new_book.save('new_stu.xls')