How to Write Flask Data to Excel Sheets

Introduction

Excel documents are designed to help users organize, analyze and manipulate data in a tabular format. In this article, we will be looking at how to add data created in Flask to an Excel document.

Install modules

In this tutorial, we will be using a few libraries such as Flask, and Pandas. To install these libraries run the following commands below.

pip3 install Flask 

pip3 install pandas

File structure

First, let’s create a folder called flask-excel which will contain our project, and then create a folder called templates inside it, and then you will insert a file called index.html.

Import modules

Inside the flask-excel folder create a file called app.py and import the modules shown below.

from flask import Flask, render_template, request, redirect
import pandas as pd

Initialize our application

Let’s now initialize our application as shown below.

app = Flask(__name__)

Excel View

Now let’s create our view which will convert HTML post data to an excel document.

@app.route('/', methods=['POST','GET'])
def index():
    if request.method == "POST":
        name = request.form['name']
        employee = request.form['employee']
        salary = request.form['salary']
        
        # Create a dataframe
        df = pd.DataFrame({'Name': [name], 'Employee': [employee], 'Salary': [salary]})
        
        # Write the dataframe to an Excel file
        df.to_excel('data.xlsx', index=False)
        
        return render_template('index.html')
    else:
        return render_template('index.html')

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

The above view receives an employees name, job title, and salary from the template and then converts it to a pandas data frame which later writes it to an Excel format in the file data.xlsx

Create template

Now let’s create a template to get data from the user. Inside the index.html file we created earlier add the code below.

<!DOCTYPE html>
<html>
<head>
    <title>Flask Excel</title>
</head>
<body>
    <div>
        <form method="POST">
        <label for="name">Name:</label>
        <input type="text" name="name" required=True>

        <label for="employee">Employee:</label>
        <input type="text" name="employee" required=True>

        <label for="salary">Salary:</label>
        <input type="number" name="salary" required=True>

        <input type="submit" value="Submit">
    </form>
    </div>
</body>
</html>

Run

Now let’s run our application. Open your terminal and run the command below.

python3 app.py

Results

After running the command above, your application should be accessible at the address [ http://127.0.0.1:5000 ]. Here is our browser and the Excel document produced.