python flask批量从Exce导入数据到数据库xlrd、openpyxl_python flask接口excel数据输入数据库

目的

Flask中，实现从excel导入数据到数据库。
为什么是Excel？？因为文职人员都必须掌握？？
excel两种格式

Excel 2007 以前，使用扩展名为.xls格式的文件，之后使用.xlsx。
.xls最多支持 65,536 行（在 Excel 97 之前支持的最大行数是 16,384），256 列表格。
.xlsx最多支持 1,048,576行， 16,384列。因此将此格式文件转为.xls可能会导致数据丢失。
.xls在python中使用xld读取，.xlsx则是使用openpyxl进行读取。
探究 Pandas 读取 Excel 文件报错问题 simpleapples

总结

关于python读取excel的官方解读Working with Excel Files in Python

NB: In general, these use cases are now covered by openpyxl!

然后用推荐的openpyx，发现不支持旧格式。
pandas读取新旧格式的Excel也是调用xlrd、openpyxl。。。

The pandas I/O API is a set of top level reader functions accessed like pandas.read_csv() that generally return a pandas object. The corresponding writer functions are object methods that are accessed like DataFrame.to_csv(). Below is a table containing available readers and writers.
pands IO tools

示例文件

内容一样，格式不同

读取xls - xlrd

如果尝试用xlrd读取新格式xlsx，你就会看到这样的错误

workbook = open_workbook('key.xlsx')

Traceback (most recent call last):
  File "tmp.py", line 4, in <module>
    workbook = xlrd.open_workbook('key.xlsx')
  File "/virtualenvs/flask-chat-128w9Wfh/lib/python3.7/site-packages/xlrd/__init__.py", line 170, in open_workbook
    raise XLRDError(FILE_FORMAT_DESCRIPTIONS[file_format]+'; not supported')
xlrd.biffh.XLRDError: Excel xlsx file; not supported
1
2
3
4
5
6
7
8

安装

pip install xlrd
1

案例

emmm。。xlrd的行列从0开始计数，而openpyxl则是从1开始。。

from xlrd import open_workbook


workbook = open_workbook('key.xls')
print(workbook.sheet_names())
sheet = workbook.sheet_by_name(workbook.sheet_names()[0])  # 第一张sheet
# sheet的名称，行数，列数
print (sheet.name,sheet.nrows,sheet.ncols)

# 按行读取
for i in range(1, sheet.nrows):
    for j in range(1, sheet.ncols):
        cell=sheet.cell(i,j).value
        print(cell, end=" ")
    print()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

读取xlsx - openpyxl

看到的帖子大都是xlrd，去人家官网说我不维护了，请使用openpyxl
需要注意的是openpyxl对Excel不支持旧格式xls，仅支持新格式xlsx，因此需要加一个文件类型判断决定用openpyxl还是xlrd。

安装

pip install openpyxl
1

用openpyxl打开xls会报错，还会提示你用xlrd。。xswl,尽管这个文件不存在，机智啊，先判断后缀名

openpyxl.utils.exceptions.InvalidFileException: openpyxl does not support the old .xls file format, please use xlrd to read this file, or convert it to the more recent .xlsx file format.

读取.xlsx案例

from openpyxl import load_workbook

wb = load_workbook(filename = 'key.xlsx')
sheet_names = wb.sheetnames
sheet = wb[sheet_names[0]]  # 第一张sheet

rows=sheet.max_row   # 获取行数
cols=sheet.max_column    # 获取列数

# 按行读取
for i in range(1, rows):
    for j in range(1, cols+1):
        cell=sheet.cell(i,j).value
        print(cell, end=" ")
    print()
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

缝合怪：xlrd+openpyxl

既然pandas也是底层调用这两，那我还不如直接自己检测后缀

from xlrd import open_workbook
from openpyxl import load_workbook

filepath = 'key.xlsx'

if filepath.endswith('.xls'):
    workbook = open_workbook(filepath)
    sheet = workbook.sheet_by_name(workbook.sheet_names()[0])  # 第一张sheet
    for i in range(0, sheet.nrows): # xlrd中从0 开始计数
        for j in range(0, sheet.ncols):
            cell=sheet.cell(i,j).value
            print(cell, end=" ")
        print()
elif filepath.endswith('.xlsx'):
    print(filepath,'.xlsx')
    wb = load_workbook(filepath)
    sheet_names = wb.sheetnames
    sheet = wb[sheet_names[0]]  # 第一张sheet
    for i in range(1, sheet.max_row+1): # openpyxl中从1 开始计数
        for j in range(1, sheet.max_column+1):
            cell=sheet.cell(i,j).value
            print(cell, end=" ")
        print()
else:
    raise Exception("文件类型异常：仅支持.xlsx 和 .xls格式的Excel，请重新检查文件类型。\nFile Type Error: Only *.xlsx and *.xls are supported!") 


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

flask

一下展示的是在Flask中用openpyxl读取本地excel数据批量导入数据库的过程

from openpyxl import load_workbook
from key import  db
from key.models import Key


wb = load_workbook('key.xlsx')
sheet_names = wb.sheetnames
sheet = wb[sheet_names[0]]  # 第一张sheet
rows=sheet.max_row   # 获取行数
cols=sheet.max_column    # 获取列数

for i in range(2, rows+1):  # 按行读取，跳过表头
    key = Key()
    key.k_id = sheet.cell(i, 1).value
    key.k_status = sheet.cell(i, 2).value
    key.s_id = sheet.cell(i, 3).value
    key.k_note = sheet.cell(i, 4).value
    db.session.add(key)
db.session.commit() # 提交更新
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

sheetjs

上面的例子需要将文件上传到app目录下，文件上传又需要验证读取删除。。感觉挺麻烦的后来想了下为啥不直接在前端完成数据提取再提交到flask的controller，查了下js，真的有这玩意。

参考

• 【Flask】从Excel中批量导入数据 阏男秀:xlrd读取excel，然后for循环创建对象，逐个添加到session
• [Flask]sqlalchemy批量插入数据（性能问题） 码农教程
• excel 批量导入数据库并返回导入情况的问题：xlrd、db.session.bulk_insert_mappings()、评论建议pandas或者openpyxl
• Excel文件一键上传并解析完成数据批量导入数据库 小鬼哥：jQuery的插件上传文件到服务器
• python读写excel等数据文件方法汇总 朱卫军：

pandas支持xlsx、xls、xlsm；
xlrd库： 从excel中读取数据，支持xls、xlsx
openpyxl： 主要针对xlsx格式的excel进行读取和编辑
xlwings： 对xlsx、xls、xlsm格式文件进行读写、格式修改等操作

绝了，总结的人傻了，最后一个不支持linux OS。

• python：读取Excel文件

• pandas-dev/pandas29k star 维护活跃

• python-excel/xlrd2k star 202012还在更新

• Working with Excel Files in Python

• pands IO tools

• openpyxl Documentation :openpyxl is a Python library to read/write Excel 2010 xlsx/xlsm/xltx/xltm files.

• openpyxl source code rpo

• project/openpyxl pypi

• 探究 Pandas 读取 Excel 文件报错问题 simpleapples

• Python中pandas和xlrd、xlwt操作Excel用法的总结 谷曰十鑫：error的"总结"

• github.com/SheetJS/sheetjs

• 如何使用JavaScript实现纯前端读取和导出excel文件 我是小茗同学

完结

新年快乐