python 表格操作

#!/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')