python3 excel xlsx 读写 flask 上传下载

import os
import time
from io import BytesIO
from urllib.parse import quote
import openpyxl
import pymysql as pymysql

import xlsxwriter as xlsxwriter
from flask import Flask, render_template, make_response, request, jsonify
from werkzeug.utils import secure_filename

app = Flask(__name__)


@app.route('/')
def index():
    return render_template("index.html", title='后台')


@app.route('/download')
def download():
    output = BytesIO()
    # 写excel
    workbook = xlsxwriter.Workbook(output)  # 先创建一个book，直接写到io中

    sheet = workbook.add_worksheet('sheet1')
    fileds = ["名称", "电话", "性别"]
    # 写入数据到A1一列
    sheet.write_row('A1', fileds)
    workbook.close()  # 需要关闭
    output.seek(0)  # 找到流的起始位置
    resp = make_response(output.getvalue())
    basename = '数据导入模板 .xlsx'

    # 转码，支持中文名称
    resp.headers["Content-Disposition"] = "attachment; filename*=UTF-8''{utf_filename}".format(
        utf_filename=quote(basename.encode('utf-8'))
    )

    resp.headers['Content-Type'] = 'application/x-xlsx'

    return resp


@app.route('/fetchData')
def fetchData():
    page = int(request.args.get('page'))
    size = int(request.args.get('size'))

    result = dict()
    conn = pymysql.connect(host='localhost',port=3306, user='root', password='307025971', database='task009', charset='utf8')
    # cursor = conn.cursor() ### 默认返回的值是元祖类型
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  ### 返回的值是字典类型 (*********)

    sql = "select * from excelDemo limit "+str((page-1)*size)+","+str(size)
    csql = "select count(*) cc from excelDemo "

    cursor.execute(sql)
    res = cursor.fetchall()
    print(res)
    result['data'] =res

    cursor.execute(csql)
    res = cursor.fetchone()
    result['total'] =res['cc']
    cursor.close()
    conn.close()
    return jsonify(result)


@app.route('/upload', methods=['POST'])
def upload():
    f = request.files['file']
    base = os.path.dirname(os.path.dirname(__file__))
    t = time.time()
    names = f.filename.split('.')
    f.filename = str(int(t)) + "." + names.pop(len(names) - 1)
    upload_path = os.path.join(base, "app"+os.path.sep +'static' + os.path.sep + 'upload',
                               secure_filename(f.filename))
    f.save(upload_path)

    execl_input(upload_path)
    return "success"

def sql_func(sql_command):
    conn = pymysql.connect(
        host="localhost",
        port=3306,
        user="root",
        password="307025971",
        charset="utf8",
        database="task009"
    )
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    sql = sql_command
    cursor.execute(sql)
    conn.commit()
    conn.close()

# 读取excel
def execl_input(path):
    ws = openpyxl.load_workbook(path)
    sheet1 = ws.worksheets[0]

    for i in range(2, sheet1.max_row+1):  # 注意数据是从第几行开始的
        title = sheet1.cell(i, 1).value  # 取第i行，第0列，以此类推
        price = sheet1.cell(i, 2).value
        auther = sheet1.cell(i, 3).value
        sql_command = "insert into excelDemo (name,phone,sex) values ('%s',%s,'%s')" % (title, price, auther)
        sql_func(sql_command)


if __name__ == '__main__':
    app.run()

xlrd 2.0.1 不支持xlsx格式，会报错 xlrd.biffh.XLRDError: Excel xlsx file； not supported报错

用openpyxl 吧，如上