python导入导出Excel表

**摘要：**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