博客园  :: 首页  :: 新随笔  :: 联系 :: 管理

Python基础-读取excel

Posted on 2017-11-13 22:18  代码的味道是什么  阅读(339)  评论(0编辑  收藏  举报
import xlrd
book = xlrd.open_workbook('lanxia.xls')
sheet = book.sheet_by_name('sheet1')
rows = sheet.nrows#sheet页里面的行数
clos = sheet.ncols#sheet页里面的列数
print(sheet.cell(1,1).value)#通过指定行和列去获取到单元格里面的内容
row_data = sheet.row_values(1)#获取第一行的内容
for i in range(rows):
print(sheet.row_values(i))#获取第几行的数据
#1、先读excel
#2、读一行,拼一条sql语句
# insert into jxz_stu  (name,c1,c2,c3) values ('牛寒阳','交','交','交');
#3、插入数据库

def conn_mysql(sql):
    import pymysql
    conn = pymysql.connect(host='211.149.218.16',user='jxz',password='123456',db='jxz',charset='utf8')
    cur = conn.cursor(cursor=pymysql.cursors.DictCursor)
    cur.execute(sql)
    res = cur.fetchone()
    conn.commit()
    cur.close()
    conn.close()
    return res
def is_send(a):
    if a:
        return '交了'
    return '未交'

def readExcel(file_name):
    book = xlrd.open_workbook(file_name)
    sheet = book.sheet_by_name('Sheet1')
    for i in range(sheet.nrows):
        line = sheet.row_values(i)
        name = line[0]
        c1 = is_send(line[1])
        c2 = is_send(line[2])
        c3 = is_send(line[3])
        sql = "insert into jxz_stu values('{name}','{c1}','{c2}','{c3}');".format(name=name,c1=c1,c2=c2,c3=c3)
        conn_mysql(sql)
readExcel('stu.xlsx')