Python-操作Excel

一、Excel模块：xlrd（读）、xlwt（写）、xlutils（结合读写可修改excel）

1、从mysql中读取数据

import xlrd,xlwt,xlutils
import pymysql


def sql_connect(sql):
    conn = pymysql.connect(host='127.0.0.1',port=3306, user='root', password='123456',db='sql_python',charset='utf8')
    cur = conn.cursor()
    cur.execute(sql)
    data = cur.fetchall()
    cur.close()
    conn.close()
    return data

 

2、从mysql中读取数据写入excel中

#写数据
def write_excel(filename, data):
    book = xlwt.Workbook()            #创建excel对象
    sheet = book.add_sheet('sheet1')  #添加一个表
    c = 0  #保存当前列
    for d in data: #取出data中的每一个元组存到表格的每一行
        for index in range(len(d)):   #将每一个元组中的每一个单元存到每一列
            sheet.write(c,index,d[index])
        c += 1
    book.save(filename) #保存excel


sql = 'select * from stu'
res = sql_connect(sql)
write_excel('firstsheet.xls', res)

 

3、从excel中读取数据

#读数据
def read_excel(filename):
    book = xlrd.open_workbook(filename)
    sheet = book.sheet_by_name('sheet1')
    rows = sheet.nrows #获取行数
    cols = sheet.ncols #获取列数
    for c in range(cols): #读取每一列的数据
        c_values = sheet.col_values(c)
        print(c_values)
    for r in range(rows): #读取每一行的数据
        r_values = sheet.row_values(r)
        print(r_values)
    print(sheet.cell(1,1)) #读取指定单元格的数据

read_excel('firstsheet.xls')

 

4、从excel读取数据写入mysql

#从excel读取数据写入mysql
def excel_to_mysql(filename):
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='jessica', password='123456', db='sql_python',charset='utf8')
    cur = conn.cursor()     #连接数据库
    book = xlrd.open_workbook(filename)
    sheet = book.sheet_by_name('sheet1')
    rows = sheet.nrows      #获取行数
    for r in range(1,rows): #将标题之外的其他行写入数据库
        r_values = sheet.row_values(r)
        sql = 'insert into stu2 values(%s,%s)'
        data = cur.execute(sql,r_values)  #将每一行插入sql
    conn.commit()           #插入所有数据后提交
    cur.close()
    conn.close()

excel_to_mysql('firstsheet.xls')

 

5、修改excel

from xlutils.copy import copy

def modify_excel(filename):
    book = xlrd.open_workbook(filename)  # 打开excel
    new_book = copy(book)  # 复制excel
    sheet = new_book.get_sheet(0)  # 获取第一个表格的数据
    sheet.write(0, 1, 'Haha')  # 修改0行1列的数据为'Haha'
    new_book.save('secondsheet.xls')  # 保存新的excel
    os.remove(filename)  # 删除旧的excel
    os.rename('secondsheet.xls', filename)  # 将新excel重命名


modify_excel('firstsheet.xls')