python flask批量从Exce导入数据到数据库xlrd、openpyxl

目的参考【Flask】从Excel中批量导入数据阏男秀:xlrd读取excel，然后for循环创建对象，逐个添加到session[Flask]sqlalchemy批量插入数据（性能问题）码农教程excel 批量导入数据库并返回导入情况的问题：xlrd、db.session.bulk_insert_mappings()、评论建议pandas或者openpyxlExcel文件一键上传并解析完成数据批

Kearney form An idea

2560人浏览 · 2021-02-14 20:09:58

Kearney form An idea · 2021-02-14 20:09:58 发布

目的

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

安装

pip install xlrd

案例

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()

在这里插入图片描述

读取xlsx - openpyxl

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

安装

pip install openpyxl

用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()

在这里插入图片描述

缝合怪：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!")

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() # 提交更新