Need to update web page after Excel update

Dash Python
1

Working on a simple project…Need a simple web page to display a couple sheets from an excel workbook. However, I need the webpage to update when I change data in the Excel file and save it. I know I need to “re-read” the excel file to do this, but also need some sort of auto updater. Having probs even after trying to follow many tutorials and other forum entrys. New programmer, go easy.

Here is what I have so far. This works to spin up the web server and display one sheet.
Needed:

  1. Display multiple sheets with headers
  2. Auto update web when excel file is updated and saved.

import dash
import dash_core_components as dcc
from dash.dependencies import Input, Output, Event
import dash_table
import pandas as pd

df = pd.read_excel(’~/Documents/projects/data_test.xlsx’)

app = dash.Dash(name)

app.layout = dash_table.DataTable(
id=‘table’,
columns=[{“name”: i, “id”: i} for i in df.columns],
data=df.to_dict(“rows”)
)

if name == ‘main’:
app.run_server(debug=True)

2

I have a very simple app that reads from a CSV to check if it’s been updated and displays as a DataTable. I do this by having a 20 second interval and then check if the file has been updated in the last 30 seconds. First I have an interval in the layout:

dcc.Interval(id='interval-component', interval=20_000)

And then I have a callback that looks like this:

@app.callback(
    output=dash.dependencies.Output('table', 'data'),
    inputs=[dash.dependencies.Input('interval-component', 'n_intervals')])
def update_table(n_intervals):
    if not n_intervals:
        raise PreventUpdate

    how_long_ago = time.time() - os.stat(filename)[stat.ST_MTIME]
    if how_long_ago > 30:
        raise PreventUpdate

    df = pandas.read_csv(filename)

    return df.to_dict("rows")

Where filename is the file path of my CSV, in your case you will need to switch to xlsx.

Though I believe Excel locks files, so you will need to be careful about how you handle that if you’re manually editing an excel file.

1 Like
3

Thank you so much for your reply. Im sure ive messed something up here…but updated with your suggestions to this and throwing an exception:

#! python3

import dash
import dash_core_components as dcc
from dash.dependencies import Input, Output, Event
import dash_table
import pandas as pd

df = pd.read_excel(’~/Documents/projects/data_test.xlsx’)

app = dash.Dash(name)

app.layout = dash_table.DataTable(
dcc.Interval(id=‘interval-component’, interval=20_000),
id=‘table’,
columns=[{“name”: i, “id”: i} for i in df.columns],
data=df.to_dict(“rows”)
)

@app.callback(
output=dash.dependencies.Output(‘table’, ‘data’),
inputs=[dash.dependencies.Input(‘interval-component’, ‘n_intervals’)])
def update_table(n_intervals):
if not n_intervals:
raise PreventUpdate

how_long_ago = time.time() - os.stat('~/Documents/projects/data_test.xlsx')[stat.ST_MTIME]
if how_long_ago > 30:
    raise PreventUpdate

df = pd.read_excel('~/Documents/projects/data_test.xlsx')

return df.to_dict("rows")

if name == ‘main’:
app.run_server(debug=True)

EXCEPTION:
dash.exceptions.NonExistantIdException:

Attempting to assign a callback to the

component with the id “interval-component” but no

components with id “interval-component” exist in the

app’s layout.

Here is a list of IDs in layout:

[‘table’]

If you are assigning callbacks to components

that are generated by other callbacks

(and therefore not in the initial layout), then

you can suppress this exception by setting

app.config['suppress_callback_exceptions']=True.

4

Yeah, your layout is wrong:

app.layout = dash_table.DataTable(
    dcc.Interval(id=‘interval-component’, interval=20_000),
    id=‘table’,
    columns=[{“name”: i, “id”: i} for i in df.columns],
    data=df.to_dict(“rows”)
)

You’ve made Interval a child of DataTable, Datatable doesn’t really understand what this is and ignores it. You should make your layout children of a Div element as a Div element understands how to place it’s children in the web page:

import dash_html_components as html

app.layout = html.Div([dcc.Interval(id='interval-component', interval=20_000),
                       dash_table.DataTable(id='table',
                                            columns=[{"name": i, "id": i} for i in df.columns],
                                            data=df.to_dict("rows"))])
1 Like
5

Getting there I think…I really appreciate your help. Still not updating the web page…I see it refresh but its not getting the new date from the csv…btw, I changed the file to a csv out of concern for xlsx locking.

#! python3

import dash
from dash.dependencies import Input, Output, Event
import dash_core_components as dcc
import dash_html_components as html
import dash_table
import datetime
import time
import os
import pandas as pd

      
df = pd.read_csv('~/Documents/projects/data_test1.csv')

app = dash.Dash(__name__)

app.layout = html.Div([dcc.Interval(id='interval-component', interval=10_000),
                       dash_table.DataTable(id='table',
                                            columns=[{"name": i, "id": i} for i in df.columns],
                                            data=df.to_dict("rows"))])

@app.callback(
    output=dash.dependencies.Output('table', 'data'),
    inputs=[dash.dependencies.Input('interval-component', 'n_intervals')])
def update_table(n_intervals):
    if not n_intervals:
        raise Exception

    how_long_ago = time.time() - os.stat('~/Documents/projects/data_test1.csv')[stat.ST_MTIME]
    if how_long_ago > 30:
        raise Exception

    df = pd.read_csv('~/Documents/projects/data_test1.csv')

    return df.to_dict("rows")

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

You’re raising the wrong type of exception, you want to raise PreventUpdate:

from dash.exceptions import PreventUpdate

To debug the issue you can always comments out the if statements or throw a print in there to see if it’s working as you expect or not.

1 Like
7

Shoot! Got it! Makes sense. Thank you! Just prior, I commented out this:

#    how_long_ago = time.time() - os.stat('~/Documents/projects/data_test1.csv')[stat.ST_MTIME]
#    if how_long_ago > 30:
#        raise Exception

And noticed it was working. Ill fix that, works perfect!! Thank you so much for your help!

8

Oh, I imagine the issue is that os.stat doesn’t know what ~ is, I think you need to provide the real path or use expand it:

os.stat(os.path.expanduser('~/Documents/projects/data_test1.csv'))
9

So, that didnt seem to help…works with those lines commented out for now, so I can continue to debug this under less pressure! Thanks again for your help!

10

bro did you find the solution ??
m stuck on the same problem