python导入导出Excel表

桃扇骨 2022-06-04 08:37 283阅读 0赞

\*\*摘要：\*\*python关于操作Excel表的库有以下几个 XlsxWriter，xlrd&xlwt，OpenPyXL，Microsoft Excel API关于这几个库介绍自己看文档，我这里使用OpenPyXL操作.xlsx后缀的Excel表，说明  
  
本次测试是从MySQL数据库里导出到Excel表，在从Excel表导入到MySQL数据库的一个过程  
数据库一张user，user\_copy表，属性字段都一样，就表名不同

CREATE TABLE `user` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(50) DEFAULT NULL,
      `password` varchar(50) DEFAULT NULL,
      PRIMARY KEY (`id`)
    )

## 导入Excel ##

# pylint: disable = C0103, W0104, C0111
    
    import pymysql
    from openpyxl import Workbook
    
    # 连接数据库信息
    conn = pymysql.Connect(
        host='127.0.0.1',
        port=3306,
        user='root',
        passwd='admin',
        db='test',
        charset='utf8')
    cur = conn.cursor()
    
    sql = 'select * from user'
    
    # 执行查询
    cur.execute(sql)
    
    # 保存结果
    result = cur.fetchall()
    
    # 关闭连接
    cur.close
    conn.close
    
    # 在内存中创建一个workbook对象，而且会至少创建一个 worksheet
    wb = Workbook()
    
    # 获取当前活跃的worksheet,默认就是第一个worksheet
    ws = wb.active
    
    # 设置Excel头
    ws.title = "test sheet"
    
    # 设置Excel第一行的标签名字
    ws.cell(row=1, column=1).value = "id"
    ws.cell(row=1, column=2).value = "name"
    ws.cell(row=1, column=3).value = "password"
    
    # 添加从数据表中查出来的数据
    for i in result:
        ws.append(i)
    
    # 保存到本地Excel
    url = "C:/Users/yifeng/Desktop/t.xls"
    wb.save(url)
    wb.close

## 导出Excel ##

这里要说明的是，数据库的连接信息是引用export\_excel的

import export_excel as a
    from openpyxl import load_workbook
    # pylint: disable = C0103, W0104, C0111, C0325, C0303, W0703
    url = "C:/Users/yifeng/Desktop/t.xlsx"
    
    # 打开一个workbook
    wb = load_workbook(filename=url)
    
    # 获取当前活跃的worksheet,默认就是第一个worksheet
    # ws = wb.active
    
    # 当然也可以使用下面的方法
    
    # 获取所有表格(worksheet)的名字
    sheets = wb.get_sheet_names()
    # 第一个表格的名称
    
    sheet_first = sheets[0]
    # 获取特定的worksheet
    ws = wb.get_sheet_by_name(sheet_first)
    
    column_number = ws.max_column  # 最大的列
    row_number = ws.max_row  # 最大的行
    
    # 获取表格所有行和列，两者都是可迭代的，换成list可取下标
    rows = list(ws.rows)
    columns = list(ws.columns)
    
    # 要添加的sql语句
    add_sql = " insert into user_copy(id,name,password) VALUES (%s, %s, %s)"
    
    # 要保存的列表
    l2 = []
    
    ii = 0
    for i in range(1, row_number):    # 从第二行开始遍历
        l1 = []    # 完整的一条添加数据
        for j in range(0, column_number):    # 从第一列开始读取
            data_value = rows[i][j].value    # 取到 i 行 j 列的数据值
            l1.append(data_value)
        l2.append(l1)
    
    # 这是效果是一样的，通俗一点理解，上面的正规一点
    # for row in ws.iter_rows():
    # ii += 1
    # # 去掉数据上面的标签 ID name password
    # if (ii == 1):
    # continue
    # l1 = [] # 完整的一条添加数据
    # for cell in row:
    # l1.append(cell.value)
    # l2.append(l1)
    
    try:
        count = a.cur.executemany(add_sql, l2)
        print(count)
    except Exception as e:
        print(str(e))
    
    # 关闭连接
    a.conn.commit()
    a.cur.close()
    a.conn.close()

几种库的比较，这里写了一篇http://www.gocalf.com/blog/python-read-write-excel.html\#xlrd-xlwt