Python Flask File Generate Excel Report from MySQL
from flask import Flask, Response, render_template
from flaskext.mysql import MySQL
import io
import xlwt
import pymysql
app = Flask(__name__)
mysql = MySQL()
# MySQL configurations
app.config['MYSQL_DATABASE_USER'] = 'root'
app.config['MYSQL_DATABASE_PASSWORD'] = ''
app.config['MYSQL_DATABASE_DB'] = 'testingdb'
app.config['MYSQL_DATABASE_HOST'] = 'localhost'
mysql.init_app(app)
@app.route('/')
def upload_form():
return render_template('download.html')
@app.route('/download/report/excel')
def download_report():
conn = mysql.connect()
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute("SELECT emp_id, emp_first_name, emp_last_name, emp_designation FROM employees")
result = cursor.fetchall()
#output in bytes
output = io.BytesIO()
#create WorkBook object
workbook = xlwt.Workbook()
#add a sheet
sh = workbook.add_sheet('Employee Report')
#add headers
sh.write(0, 0, 'Emp Id')
sh.write(0, 1, 'Emp First Name')
sh.write(0, 2, 'Emp Last Name')
sh.write(0, 3, 'Designation')
idx = 0
for row in result:
sh.write(idx+1, 0, str(row['emp_id']))
sh.write(idx+1, 1, row['emp_first_name'])
sh.write(idx+1, 2, row['emp_last_name'])
sh.write(idx+1, 3, row['emp_designation'])
idx += 1
workbook.save(output)
output.seek(0)
return Response(output, mimetype="application/ms-excel", headers={"Content-Disposition":"attachment;filename=employee_report.xls"})
if __name__ == '__main__':
app.run(debug=True)
<html>
<head>
<title>Python Flask File Generate Excel Report from MySQL</title>
</head>
<body>
<h2>Generate Excel Report from MySQL</h2>
<p>
<a href="{{ url_for('.download_report') }}">Generate Excel Report</a>
</p>
</body>
</html>
