import streamlit as st
import pandas as pd

@st.cache(show_spinner=False)
def excel_init():
    # Use database like in Bending Stiffness model
    orig_database_path = "/path/to/file.xlsx"      # local copy of the Excel database used in Bending Stiffness & Permeability model
    read_orig_database = r"" + orig_database_path
    sheet_name = "Database"
    
    # Read in the Resin/Materials from Carol's Excel file for getting data by column
    ses["read_table"] = pd.read_excel(read_orig_database, sheet_name=sheet_name, engine='openpyxl', skiprows=3)   # skip first 3 rows since they are either empty space or extra headers

    # Excel column header names from original materials database for easily reading the database column values
    ses["col1"] = ses["read_table"].columns[0]      # "Column 1"
    ses["col3"] = ses["read_table"].columns[2]      # "Column 3"
    ses["col6"] = ses["read_table"].columns[5]      # "Column 6"
    
    # Setting up list of materials for dropdown
    col1_pandas = pd.DataFrame(ses["read_table"][ses["col1"]].values)  # get col1 column values as pandas dataframe
    customize_options = pd.DataFrame(["[Placeholder]"])                                              # "blank" placeholder for any item not in the database
    ses["list_streamlit"] = pd.concat([customize_options, col1_pandas], axis=0)                       # axis=0 --> concat by row, axis=1 --> concat by col

    # Assign "Excel indices" for easy data retrieval (same applies to col3, col6, etc.) after user selects a material (need to start range at 1 since Excel is 1-based index, not 0-based)
    # we access the item by
    ses["col1_prefill_index"] = {}                                                         # [material_name_key]: [index_0-based_val]
    for i in range(1, len(ses["read_table"][ses["col1"]]) + 1):      # loop through col1 pandas dataframe "list"
        ses["col1_prefill_index"][ses["read_table"][ses["col1"]][i-1]] = i-1
    
    return