How to export in excel format with flask

on

This article explains how to export data in excel in the flask. To export data in excel we are going to use xlwt package. This package will help us to export our data in excel format.

To export data in excel, create a download link, which will download the all data in excel format. So first, write HTML code to create a download link. 

Create a main directory on your desktop with the name flask. Follow the following steps to create the project folder structure - 

  • Open the terminal/cmd of your system and navigate to the desktop.
  • Create a new directory for your flask project. Follow the following steps to create a project structure.
> cd Desktop  
> mkdir flask
> mkdir templates
  • After creating the flask directory and flask/templates. Create an HTML file inside your templates directory.
templates/export-excel.html
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.0-alpha2/dist/css/bootstrap.min.css" rel="stylesheet">
    <title>Export Excel</title>
</head>
<body>
   <div class="container mt-3">
    <a href="{{ url_for('exportexcel') }}" class="btn btn-primary">Export Excel</a>
   <table class="table table-hover">
    <tr>
        <th>Id</th>
        <th>Name</th>
        <th>Brand</th>
        <th>Description</th>
        <th>Gender</th>
        <th>Num_Images</th>
        <th>Price</th>
        <th>Primary_Color</th>
        <th>Sku</th>
    </tr>
    {% if not products %}
    <tr>
        <td colspan="9">No data available.</td>
    </tr>
    {% else %}
    {% for product in products %}
    <tr>
        <td>{{ product["id"] }}</td>
        <td>{{ product["name"] }}</td>
        <td>{{ product["brand"] }}</td>
        <td>{{ product["description"] }}</td>
        <td>{{ product["gender"] }}</td>
        <td>{{ product["num_images"] }}</td>
        <td>{{ product["price"] }}</td>
        <td>{{ product["primary_color"] }}</td>
        <td>{{ product["sku"] }}</td>
    </tr>
    {% endfor %}
    {% endif %}
   </table>
   </div>
</body>
</html>
  • After creating the template file, now we will start creating routes for the export of excel. For the routing file, we have to create an app.py file.
flask/app.py
from flask import Flask,request,render_template,Response
from flask import Flask,request,render_template,Response
import io
import xlwt
import pymysql.cursors

app = Flask(__name__)

# initialize mysql database
def mysqlCursor():
    connection = pymysql.connect(host='localhost',
                             user='root',
                             password='Pandey@123',
                             database='flask_product_mst',
                             cursorclass=pymysql.cursors.DictCursor)
    return connection

connection = mysqlCursor()
cursor = mysqlCursor().cursor()

def fetchAll():
    query = "SELECT * FROM `product`"
    cursor.execute(query)
    rows = cursor.fetchall()
    if rows:
        return rows
    return False

@app.route("/",methods=["GET"])
def index():
    product = fetchAll()
    return render_template("export-excel.html",products=product)

@app.route("/exportexcel")
def exportexcel():
    try:
        products = fetchAll()
        
        #output in bytes
        output = io.BytesIO() 
        
        #create WorkBook object
        workbook = xlwt.Workbook()
        
        #add a sheet
        sh = workbook.add_sheet('Product Data')

        #headers
        sh.write(0, 0, 'Id')
        sh.write(0, 1, 'Name')
        sh.write(0, 2, 'Brand')
        sh.write(0, 3, 'Description')
        sh.write(0, 4, 'Gender')
        sh.write(0, 5, 'Num_Images')
        sh.write(0, 6, 'Price')
        sh.write(0, 7, 'Primary_Color')
        sh.write(0, 8, 'Sku')

        idx = 0
        for item in products:
            sh.write(idx+1, 0, str(item['id']))
            sh.write(idx+1, 1, item['name'])
            sh.write(idx+1, 2, item['brand'])
            sh.write(idx+1, 3, item['description'])
            sh.write(idx+1, 4, item['gender'])
            sh.write(idx+1, 5, item['num_images'])
            sh.write(idx+1, 6, item['price'])
            sh.write(idx+1, 7, item['primary_color'])
            sh.write(idx+1, 8, item['sku'])
            idx += 1
        
        workbook.save(output)
        output.seek(0)
        return Response(output, mimetype="application/ms-excel", headers={"Content-Disposition":"attachment;filename=products.xls"})
    except Exception as e:
        return e

if __name__=="__main__":
    app.run(debug=True)
To connect with MySQL, we had to write the MySQL connection code in the app.py file in the above code. Now install the MySQL and excel package.
> pip install xlwt
> python3 -m pip install PyMySQL
Now run your flask application - 
> python app.py

Output :



Comments

Post a Comment