1

I would like to create a Flask app to render dynamically an excel sheet. My need is the following: someone created (a long time ago) a complex excel document, having many sheets with formula across the sheets. The "last" sheet of the excel displays valuable information (that can be updated regularly by someone on the server side, like every minute), and I would like to broadcast via a web server. Currently what I'm doing (but it's not working), is to read the sheet with pandas (or openpyxl) and render the results via a Flask app:

from flask import Flask
from flask import render_template

app = Flask(__name__)

@app.route('/', methods=('POST', 'GET'))
def index():
    html = pd.read_excel("foo.xlsx", sheetname="Sheet_1").to_html()
    return render_template('index.html', html_code=html)

and in my html template :

{% block content %}
    <h1>From excel:</h1>
    {{ html_code|safe }}
{% endblock %}

What is not working (from more problematic to less): * Pandas (or Openpyxl) does not redo the excel formula (it displays the formula, without resolving them). Is there a way to do that ? * How to "slow down" or control the refresh rate of the Flask index page ? * Is there a way to also render the formating of the excel sheet ? (like borders, colors, ....)

Also, I know my architecture is pretty awkward and probably not working in fine, so if you guys have another simple architecture that allows someone to regularly edit (like every minutes) an excel and render a sheet ? The only constraint that I have is that I have to use the excel, and I don't want to save every minutes a .mht or .html from the excel file.

Many thanks !!

RomainL
  • 69
  • 1
  • 4
  • this may [help](https://sarahleejane.github.io/learning/python/2015/08/09/simple-tables-in-webapps-using-flask-and-pandas-with-python.html) – sahasrara62 Mar 19 '20 at 20:46

1 Answers1

1

Since this is not the format to discuss opinions on different software architectures, I'll try to give you some ideas for the different parts of your question:

To read the (calculated) value of an excel cell instead of the formula, you could e.g. have a look at this discussion: Python reads only the formula from excel cell

To read the formatting of the Excel sheet, you could e.g. have a look at this discussion: How to read excel cell and retain or detect its format in Python

As for the refresh rate: Since you are not using websockets or any other asynchronous communication in your code, the template will only render if a client requests it.

One last general thought: If you are just looking for an easy way to make the Excel sheet available via HTTP, you could simply use Flask-Excel

Elisabeth Strunk
  • 516
  • 1
  • 6
  • 15