原创  python3 excel xlsx 读写 flask 上传下载

分类:python 2022-06-18 21:52    395人阅读   
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 吧,如上

服务器费用不足...

时尚的社交网站前端界面HTML模板 - Cirkle

物品租赁买卖业务平台HTML5模板 - Doremi

时尚和轻量设计Bootstrap4管理系统模板 - Sunny

动画效果教育行业SVG画图和图标

很酷的元素周期表three.js动画

程序员向妹子表白专用代码

简约的Bootstrap5价格表小部件

CSS3 Button按钮悬停效果集合

CSS DIV中秋节网页代码

建筑工程机械设备租赁网站HTML模板 - Antek

消息通知提示jQuery小部件

移动端友好的新闻博客类HTML5模板

程序员向妹子表白专用代码

通用的医院医疗保健HTML5模板

区块链数字货币管理系统网页模板 - Cryptio

墨汁跳跃闯关js小游戏源码

bootstrap风格后台界面管理系统模板 - Voler

纯css太阳系动画html源码

jQuery弹出提示框组件

bootstrap框架web UI工具包后台模板 - MegaDin

服务器费用不足...
 工具推荐 更多»