pythonFlask导出Excel
自己做了一个python flask框架，引用sql语句，并将数据数据展示在页面上。
现在是在页面上增加一个导出Excel的功能，能将页面上展示的数据导出Excel文件。
下面是我写的部分代码：
sql_lianjie.py
import pymssql
import pandas as pd
class Mysql(object):
def __init__(self):
try:
self.db = pymssql.connect(host='11.11.11.11',
port='1433',
user='sa',password='111111111',
database='rwdb',
charset='GBK')
self.cursor =self.db.cursor()
print('连接成功')
except:
print('连接失败')
#1小时查询语句函数
def onedata(self):
sql = "select top 40 r.STCD,sum(r.DRP) as DRP_SUM ,b.STNM,b.STLC,b.ADMAUTH from RWDB.dbo.ST_PPTN_R r left join RWDB.dbo.ST_STBPRP_B b on r.STCD=b.STCD where r.TM >= DATEADD(hour,-1,GETDATE()) group by r.STCD,b.STNM,b.STLC,b.ADMAUTH order by sum(r.DRP) desc"
#执行语句
self.cursor.execute(sql)
#获取所有记录
results = self.cursor.fetchall()
print(sql)
print(results)
return results
app.py
from flask import render_template
import pymysql
from flask import Flask, request, make_response
import openpyxl
from sql_lianjie import Mysql
app = Flask(__name__)
@app.route('/')
def hello_world():
return 'Hello World!'
#1小时排序
@app.route('/product/list',methods=['GET','POST'])
def product_list():
db =Mysql()
# resultsenshi = db.enshidata()
resultst = db.onedata()
return render_template('productList.html',results=resultst)
@app.route('/export_data', methods=['GET'])
def export_data():
# 执行查询语句
results = Mysql().onedata()
# 创建 Excel 文件
wb = openpyxl.Workbook()
ws = wb.active
ws.title = '1小时排序'
ws.append(['序号', '站码', '1小时雨量', '测站名称', '站址', '县市'])
for i, result in enumerate(results):
ws.append([i+1, result[0], result[1], result[2], result[3], result[4]])
# 将 Excel 文件以二进制形式返回给客户端
response = make_response(wb.save_virtual_workbook())
response.headers['Content-Type'] = 'application/vnd.ms-excel'
response.headers['Content-Disposition'] = 'attachment; filename=1小时排序.xlsx'
return response
html代码
<body>
<div>
<!-- 1小时排序 -->
<h4 align="center">1小时排序 ╰(￣▽￣)╭</h4>
<button type="button" onclick="exportToExcel()">导出Excel</button>
<table border="1" width="50%" weight="30%" align="center">
<thead>
<tr>
<th style="text-align: center">序号</th>
<th style="text-align: center">站码</th>
<th style="text-align: center">1小时雨量</th>
<th style="text-align: center">测站名称</th>
<th style="text-align: center">站址</th>
<th style="text-align: center">县市</th>
</tr>
</thead>
<tbody>
{% for result6 in results %}
<tr>
<td style="text-align: center">{{ loop.index }}</td>
<td style="text-align: center">{{ result6[0] }}</td>
<td style="text-align: center">{{ result6[1] }}</td>
<td style="text-align: center">{{ result6[2] }}</td>
<td style="text-align: center">{{ result6[3] }}</td>
<td style="text-align: center">{{ result6[4] }}</td>
</tr>
{% endfor %}
</tbody>
</table>
</div>
<script>
function exportToExcel() {
// 发送 AJAX 请求，获取导出数据
$.ajax({
url: '/export_data',
type: 'GET',
success: function(response) {
// 创建 Blob 对象
var blob = new Blob([response], {type: 'application/vnd.ms-excel'});
// 创建 URL 对象
var url = URL.createObjectURL(blob);
// 打开 URL
window.open(url);
}
});
}
</script>
测试页面正常，如下
点击按钮时会报错：
连接成功
select top 40 r.STCD,sum(r.DRP) as DRP_SUM ,b.STNM,b.STLC,b.ADMAUTH from RWDB.dbo.ST_PPTN_R r left join RWDB.dbo.ST_STBPRP_B b on r.STCD=b.STCD where r.TM >= DATEADD(hour,-1,GETDATE()) group by r.STCD,b.STNM,b.STLC,b.ADMAUTH order by sum(r.DRP) desc
[('61335067', 0.0, None, None, None), ('61038880', 0.0, '景\u3000阳', '建始县景阳镇 ', '建始县 '), ('61008440', 0.0, '善家河水库 ', '巴东县野三关善家河水库 ', '巴东县 ')]
[2023-04-26 09:45:15,686] ERROR in app: Exception on /export_data [GET]
Traceback (most recent call last):
File "D:\PycharmProjects\testFlask\venv\lib\site-packages\flask\app.py", line 2525, in wsgi_app
response = self.full_dispatch_request()
File "D:\PycharmProjects\testFlask\venv\lib\site-packages\flask\app.py", line 1822, in full_dispatch_request
rv = self.handle_user_exception(e)
File "D:\PycharmProjects\testFlask\venv\lib\site-packages\flask\app.py", line 1820, in full_dispatch_request
rv = self.dispatch_request()
File "D:\PycharmProjects\testFlask\venv\lib\site-packages\flask\app.py", line 1796, in dispatch_request
return self.ensure_sync(self.view_functions[rule.endpoint])(**view_args)
File "D:\PycharmProjects\testFlask\app.py", line 42, in export_data
AttributeError: 'Workbook' object has no attribute 'save_virtual_workbook'
127.0.0.1 - - [26/Apr/2023 09:45:15] "GET /export_data HTTP/1.1" 500 -
按你方式来的，又出现新问题了
File "D:\py\lib\http\server.py", line 516, in send_header
("%s: %s\r\n" % (keyword, value)).encode('latin-1', 'strict'))
UnicodeEncodeError: 'latin-1' codec can't encode characters in position 43-46: ordinal not in range(256)
server.py
def send_header(self, keyword, value):
"""Send a MIME header to the headers buffer."""
if self.request_version != 'HTTP/0.9':
if not hasattr(self, '_headers_buffer'):
self._headers_buffer = []
self._headers_buffer.append(
("%s: %s\r\n" % (keyword, value)).encode('latin-1', 'strict'))
flask读取数据库内容导出为excel文件
可以借鉴下
https://blog.csdn.net/weixin_44571402/article/details/127966396
- 你可以参考下这个问题的回答, 看看是否对你有帮助, 链接: https://ask.csdn.net/questions/7745722
- 这篇博客你也可以参考下：python flask框架，在请求前加入参数过滤，防止sql注入
- 除此之外, 这篇博客: Flask 性能分析与SQL慢查询发现机制中的 生成调用树 部分也许能够解决你的问题, 你可以仔细阅读以下内容或跳转源博客中阅读:
安装依赖
pip install gprof2dot # 将.prof文件存储的数据转换为dot语言 brew install graphviz # 根据dot语言的描述绘图生成调用树
python -m gprof2dot -f pstat your_prof_file.prof | dot -Tsvg -o profile.svg生成调用树样式的含义：
- 结点信息
+------------------------------+ | function name | | total time % ( self time % ) | | total calls | +------------------------------+- 边信息
total time % calls parent --------------------> children
第一个错是 save_virtual_workbook 不是 wb 用法
from openpyxl.workbook import Workbook
from openpyxl.writer.excel import save_virtual_workbook
wb = Workbook()
save_virtual_workbook(wb)
首先，你的openpyxl版本太高了，其writer.excel中的save_virtual_workbook() 不存在，你可以手动加把这个函数加上：
def save_virtual_workbook(workbook,):
"""Return an in-memory workbook, suitable for a Django response."""
from io import BytesIO
temp_buffer = BytesIO()
archive = ZipFile(temp_buffer, 'w', ZIP_DEFLATED, allowZip64=True)
writer = ExcelWriter(workbook, archive)
try:
writer.write_data()
finally:
archive.close()
virtual_workbook = temp_buffer.getvalue()
temp_buffer.close()
return virtual_workbook
然后，修改app.py中的函数export_data，其内容如下：
当然要首先导入quote ，即from urllib.request import quote
def export_data():
# 执行查询语句
results = Mysql().onedata()
# 创建 Excel 文件
wb = openpyxl.Workbook()
ws = wb.active
ws.title = '1小时排序'
ws.append(['序号', '站码', '1小时雨量', '测站名称', '站址', '县市'])
for i, result in enumerate(results):
ws.append([i+1, result[0], result[1], result[2], result[3], result[4]])
# 将 Excel 文件以二进制形式返回给客户端
response = make_response(openpyxl.writer.excel.save_virtual_workbook(wb))
response.headers['Content-Type'] = 'application/x-xlsx;'
response.headers['Content-Disposition'] = f'attachment; filename={quote("1小时")}.xlsx'
return response
最终看结果：
很明显的错误save_virtual_workbook不是的属性哈，改成下面的样子试试
导入函数 from openpyxl.writer.excel import save_virtual_workbook
第32行改成 response = make_response(save_virtual_workbook(wb))
解决问题记得采纳
save_virtual_workbook 函数的用法参考下面链接。
https://vimsky.com/examples/detail/python-method-openpyxl.writer.excel.save_virtual_workbook.html
错误已经提示了，这个方法不能用wb.save_virtual_workbook() 来调用。
AttributeError: 'Workbook' object has no attribute 'save_virtual_workbook'