Selecting Excel Sheet within multi-page app

Dash Python
1

Hey everyone,

I’ve been working on app that allows the user to select an excel sheet and view the content within it. I managed to get the dropdown implemented, but I’m not too sure exactly how to use the app.layout formatting. The menu displays at the bottom of each page (Fairly sure I understand why) but I’m not too sure how to implement it only on page 2. Ideally, I would prefer the dropdown, and the excel table to both display on the same page, but I am unsure as to how.

This is what I have:

import dash
from dash.dependencies import Input, Output
import dash_core_components as dcc
import dash_html_components as html
import plotly.graph_objs as go
import pandas as pd
import os
import dash_table_experiments as dt


df_excel = pd.read_csv('C:\\Users\\intern1\\info.csv', nrows = 5)
df_excel_wsj = pd.read_csv('C:\\Users\\intern1\\WSJDATA.csv', encoding='iso-8859-1', nrows = 10)
df_excel_calendar = pd.read_excel('C:\\Users\\intern1\\Excel Calendar Correct.xlsm', sheet_name= 'August')
df_ma_stats = pd.read_csv('C:\\Users\\intern1\\MAstatistics.csv', encoding='iso-8859-1')
df_bloomb_stats = pd.read_csv('C:\\Users\\intern1\\Excel5kFields.csv', encoding ='iso-8859-1')
sheet_to_df_map = pd.ExcelFile('2013 Onwards Database.xlsm')
dropdown_options = pd.read_excel('2013 Onwards Database.xlsm', sheet_name=None)


app = dash.Dash(__name__)
server = app.server

colorss = {
    'background': '#111111',
    'text': '#7FDBFF'
}

def make_dash_table(df):
    
    table =[]
    for index, row in df.iterrows():
        html_row = []
        for i in range(len(row)):
            html_row.append(html.Td([row[i]]))
        table.append(html.Tr(html_row))
    return table

def get_logo():
    logo = html.Div([
        html.Div([ 
        html.Img(src='https://media.giphy.com/media/jl7eVqDXCFcm4/giphy.gif', height = '200', width = '350')
        ], className = "ten columns padded"),
        
        html.Div([
            dcc.Link('Full View   ', href='/full-view')
        ], className = "two columns page-view no-print")
    ], className = "row gs-header")
    return logo



def get_header():
    header = html.Div([
            
        html.Div([
            html.H5('M&A Database Analysis'),
           ], className = "twelve columns")
        ], className = "row gs-header gs-text-header padded")
    return header

def get_menu():
    menu = html.Div([
        dcc.Link('Overview  ', href ='/overview', className="tab first"),
        dcc.Link('Timeline & Dates', href = '/timeline-dates', className = "tab"),
        dcc.Link('Further Analysis', href = '/further-analysis', className= "tab"),
        dcc.Link('Extra Excel Data', href='/excel-data', className= "tab")
    ], className = "row ")
    return menu

overview = html.Div([
    
    html.Div([
        
        get_logo(),
        get_header(),
        html.Br([]),
        get_menu(),
        
        html.Div([
            
            html.Div([
                html.H6('ok',
                        className="gs-header gs-text-header padded"),
               
                html.Br([]),
                
                html.P("filler."),
            
            ], className = "six columns"),
            
            html.Div([
                html.H6('HSR ',
                    className="gs-header gs-text-header padded"),
                   
                html.Table(make_dash_table(df_excel), style = {'width': 380, 'overflowX': 'scroll', 'display':'inline-block'})
            ], className="six columns"),
        ],className="row "),
    
    html.Div([
        
        html.H6('WSJ Data',
                  className="gs-header gs-text-header padded"),
                 
                  
                    html.Table(make_dash_table(df_excel_wsj), style = {'width':800, 'overflowX': 'wordwrap', 'height': 600, 'overflowY': 'scroll', 'display':'inline-block'}),
                 ], className = "twelve columns"),
            
        ], className="row")
    ], className = "page")


timelineDates = html.Div([
    
    html.Div([
        
        get_logo(),
        get_header(),
        html.Br([]),
        get_menu(),
        
        html.Div([
            
            html.H6('Filler Text',
                className= 'gs-header gs-text-header padded'),
                
            html.Br([]),
            html.Table(make_dash_table(update_datatable), style = {'height': 1200, 'overflowX': 'wordwrap', 'width': 800, 'overflowY': 'scroll'}),
        ], className= 'six columns'),
    ],className="row ")
], className="page")
            
furtherAnalysis = html.Div([
    
    html.Div([
        
        get_logo(),
        get_header(),
        html.Br([]),
        get_menu(),
        
        html.Div([
            
            html.H6('Calendar',
                className= 'gs-header gs-text-header padded center-aligned'),
               
            html.Br([]),
            html.Table(make_dash_table(df_excel_calendar), style = {'height':800, 'width':800}),
        ], className= 'twelve columns center-aligned'),
    ],className="row ")
], className="page")
         
    
excelData = html.Div([
 
    get_logo(),
    get_header(),
    html.Br([]),
    get_menu(),
    
    html.Div([
        
        html.H6('Graphs from the main sheet', 
                className = 'gs-header gs-text-header padded'),
                
            html.Div([
                        
                
                    dcc.Graph(
                        id='graph-4',
                        figure={
                            'data': [
                                go.Scatter(
                                    x = df_bloomb_stats['Buyer Name'],
                                    y = df_bloomb_stats['Tot Value'],
                                    line = {"color": "rgb(0, 0, 255)"},
                                    mode = "lines",
                                    name = "Total Value by Buyer (mil)"
                                ),
                                go.Scatter(
                                    x = df_bloomb_stats['Buyer Name'],
                                    y = df_bloomb_stats['Average Size'],
                                    line = {"color": "rgb(0, 191, 255)"},
                                    mode = "lines",
                                    name = "Average Value by Buyer (mil)"
                                )
                            ],
                            'layout': go.Layout(
                                autosize = True,
                                width = 800,
                                height = 300,
                                font = {
                                    "family": "Raleway",
                                    "size": 10
                                  },
                                 margin = {
                                    "r": 40,
                                    "t": 40,
                                    "b": 30,
                                    "l": 40
                                  },
                                  showlegend = True,
                                  titlefont = {
                                    "family": "Raleway",
                                    "size": 10
                                  },
                                  xaxis = {
                                    "autorange": True,
                                                                
                        },
                    
                        
                    )
                }
            )
        ], className="twelve columns")

   ], className="row "),

], className="page")
         

noPage = html.Div([
    html.P(["404 Page not found"])
    ], className ="no-page")

       
app.layout = html.Div([
    dcc.Location(id='url', refresh=False),
    html.Div(id='page-content'),
    html.H2("Select Target Company"),
    html.Div([dcc.Dropdown(id="field_dropdown", options=[{
                               'label': i,
                               'value': i
                           } for i in dropdown_options],
                           value='Sheet3')],
             style={'width': '25%',
                    'display': 'inline-block'}),
    dt.DataTable(rows=[{}],
                 row_selectable=True,
                 filterable=True,
                 sortable=True,
                 selected_row_indices=[],
                 id='datatable')
])

@app.callback(
    dash.dependencies.Output('datatable', 'rows'),
    [dash.dependencies.Input('field_dropdown', 'value')])

def update_datatable(user_selection):
    if user_selection == 'Sheet1':
        return sheet_to_df_map.parse(0).to_dict('records')
    elif user_selection == 'Sheet2':
        return sheet_to_df_map.parse(1).to_dict('records')
    else:
        return sheet_to_df_map.parse(2).to_dict('records')

@app.callback(dash.dependencies.Output('page-content', 'children'),
              [dash.dependencies.Input('url', 'pathname')]) 

def display_page(pathname):
    if pathname == '/' or pathname == '/overview':
        return overview
    elif pathname == '/timeline-dates':
        return timelineDates
    elif pathname == '/further-analysis':
        return furtherAnalysis
    elif pathname =='/excel-data':
        return excelData
    elif pathname == '/full-view':
        return overview,timelineDates,furtherAnalysis, excelData
    else:
        return noPage

external_css = ["https://cdnjs.cloudflare.com/ajax/libs/normalize/7.0.0/normalize.min.css",
                "https://cdnjs.cloudflare.com/ajax/libs/skeleton/2.0.4/skeleton.min.css",
                "https://fonts.googleapis.com/css?family=Raleway:400,300,600",
                "https://codepen.io/bcd/pen/KQrXdb.css",
                "https://maxcdn.bootstrapcdn.com/font-awesome/4.7.0/css/font-awesome.min.css"]

for css in external_css:
    app.css.append_css({"external_url": css})

external_js = ["https://code.jquery.com/jquery-3.2.1.min.js",
               "https://codepen.io/bcd/pen/YaXojL.js"]

for js in external_js:
    app.scripts.append_script({"external_url": js})

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

Hi there,

It’s a bit hard to diagnose a complex app like this, but a general architectural point I would add is you should be putting as much as possible in the page-content div. Especially since you only want a dropdown on page 2, it would make sense to include the dcc.Dropdown (and associated output divs) in your display_page callback.

In terms of the current placement of the dcc.Dropdown, it’s showing up at the bottom because you have it below the page-content div. However, following the above advice of adding your dropdown to page-content should make this placement a bit clearer.

If you still have questions, try to simplify the app a bit and ask a more specific question. Otherwise, hope these suggestions help!

3

Thank you for the help. I’ve been working on it since then and I think that’s where my problem lies. I’m not too sure how I would manipulate the “page-content” to include the layout and then the display_page callback to also include the other separate one. Are there any examples on the forums or on the examples that are similar?

Edit. I “updated” the layout and the display_page, but now the dropdown does not come up whatsoever on the timelineDates page.
Edit 2.
This error seems to come up -Object of type ‘Response’ is not JSON serializable

app.layout = html.Div([
    dcc.Location(id='url', refresh=True),
    html.Div(id='page-content',
             children =[
             html.H2("Select Target Company"),
             html.Div([dcc.Dropdown(id="field_dropdown", options=[{
                               'label': i,
                               'value': i
                           } for i in dropdown_options],
                           value='Sheet3')],
             style={'width': '25%',
                    'display': 'inline-block'}),
                  dt.DataTable(rows=[{}],
                 row_selectable=False,
                 sortable=False,
                 selected_row_indices=[],
                 id='datatable')
         ])
])

@app.callback(
    dash.dependencies.Output('datatable', 'rows'),
    [dash.dependencies.Input('field_dropdown', 'value')])





@app.callback(dash.dependencies.Output('page-content', 'children'),
              [dash.dependencies.Input('url', 'pathname')]) 
       
def display_page(pathname):
    if pathname == '/' or pathname == '/overview':
        return overview
    elif pathname == '/timeline-dates':
        return timelineDates,
        def update_datatable(user_selection):
            if user_selection == 'Sheet1':
                return global_df_sheet_to_df_map.parse(0).to_dict('records')
            elif user_selection == 'Sheet2':
                return global_df_sheet_to_df_map.parse(1).to_dict('records')
            else:
                return global_df_sheet_to_df_map.parse(2).to_dict('records')  
    elif pathname == '/further-analysis':
        return furtherAnalysis
    elif pathname =='/excel-data':
        return excelData
    elif pathname == '/full-view':
        return overview,timelineDates,furtherAnalysis, excelData
    else:
        return noPage