title: Uploading .xlsx file and processing it with .py file when we click submit button and store data to mysql
body: <p>I am developing program where I can upload excel invoice .xlsx file using Flask and then store that file to mysql when user click submit button. There are total three files involved. getFile.py which is Flask to upload file using index.html. exlTomySQL.py store data to mysql from file received by index.html. I am new to Flask and don't know how to process data after clicking submit button on index.html to process .xlsx file and return response for user. </p>
  
  <p><strong>getFile.py</strong></p>
  
  <pre><code>from flask import Flask, render_template, request
  
  app = Flask(__name__)
  
  
  @app.route('/', methods=['GET', 'POST'])
  def index():
      if request.method == 'POST':
  
          fileDetails = request.form
          myFile = fileDetails['myfile']
  
          return myFile
  
      return render_template('index.html')
  
  
  
  if __name__ == "__main__":
      app.run(debug=True)
  </code></pre>
  
  <p><strong>Index.html</strong></p>
  
  <pre><code>&lt;!DOCTYPE html&gt;
  &lt;html lang="en"&gt;
  
  &lt;head&gt;
      &lt;meta charset="UTF-8"&gt;
      &lt;meta name="viewport" content="width=device-width, initial-scale=1.0"&gt;
      &lt;title&gt;File Page&lt;/title&gt;
  &lt;/head&gt;
  
  &lt;body&gt;
      &lt;form method="POST" action=""&gt;
          &lt;label for="myfile"&gt;Select a file:&lt;/label&gt;
          &lt;br&gt;
          &lt;input type="file" id="myfile" name="myfile"&gt;
          &lt;br&gt;
          &lt;input type="submit"&gt;
      &lt;/form&gt;
  &lt;/body&gt;
  
  &lt;/html&gt;
  </code></pre>
  
  <p><strong>exlToSQL.py</strong></p>
  
  <pre><code>import openpyxl
  import math
  import datetime
  import mysql.connector
  
  wb = openpyxl.load_workbook('myFile')
  sheet = wb.active
  
  # count rows of data for column 2 as it represent description
  
  
  def countRows():
      count = 0
      for i in range(1, 100, 1):
          if sheet.cell(row=i, column=2).value != None:
              count = i
      return count
  
  
  # list each row description, (not counting quantity), gst amount and last column(total excluding GST)
  
  
  def readRows():
      description = []
      # quantity = []
      amount = []
      gstAmount = []
  
      for i in range(3, countRows() + 1, 1):
          description.append(sheet.cell(row=i, column=2).value)
          # quantity.append(sheet.cell(row=i, column=5).value)
          gstAmount.append(sheet.cell(row=i, column=10).value)
          amount.append(sheet.cell(row=i, column=11).value)
      uperCaseDescription = [desc.upper()
                             for desc in description]
      return uperCaseDescription, gstAmount, amount
  
  # count all list totals for GST and Amount
  
  
  def countTotals():
      uperCaseDescription, gstAmount, amount = readRows()
  
      totalGST = round(sum(gstAmount), 2)
      subTotalAmount = round(sum(amount), 2)
      totalAmountOfInvoice = round((totalGST + subTotalAmount), 2)
  
      return totalGST, subTotalAmount, totalAmountOfInvoice
  
  
  # get static data of invoice e.g. invoice number,
  # invoice date, sub total, total gst, total inc gst
  
  print(countRows())
  print(countTotals())
  invoice_number = sheet.cell(row=1, column=1).value
  print("Invoice Number:", invoice_number)
  invoice_date = sheet.cell(row=1, column=2).value
  print("Invoice Date:", invoice_date)
  
  sun_offset = (invoice_date.weekday() - 6) % 7
  
  weekEnd = invoice_date - datetime.timedelta(days=sun_offset)
  print("Week End Date:", weekEnd)
  
  print(readRows())
  
  # merge all list into tuple
  
  
  def mergeList():
      uperCaseDescription, gstAmount, amount = readRows()
  
      mergeTuple = tuple(zip(uperCaseDescription, gstAmount, amount))
      return mergeTuple
  
  
  # MySql connection and transfer data to mysql
  
  
  mydb = mysql.connector.connect(
      host="localhost",
      user="root",
      password="Vrishab2145",
      database="testdbOne"
  )
  # creating database_cursor to perform SQL operation
  
  mycursor = mydb.cursor()
  sqlFormula = "INSERT INTO invoice (description, gstAmount, totalExGst) VALUES (%s,%s,%s)"
  mycursor.executemany(sqlFormula, mergeList())
  mydb.commit()
  
  </code></pre>