import streamlit as st
import pandas as pd



from PIL import Image
image = Image.open('KYB.png')
st.image(image, caption='Powered by TedCas')


uploaded_file = st.file_uploader("Choose product file", type="csv")

if uploaded_file:
    df = pd.read_csv(uploaded_file, delimiter=';', encoding='utf8')
    import numpy as np

    #select only the numeric columns in the DataFrame
    dfnumeric=df.select_dtypes(include=np.number)




def write_excel(data,date):
    
    
    ## Python3 code to select
    ## data from excel
    #import xlwings as xw
 
    ## Specifying a sheet
    #ws = xw.Book("07-253-b Estudio capacidad (con grafico).xls").sheets['DATOS']
 
    ## Selecting data from
    ## a single cell
    #v1 = ws.range("A1:A7").value
    ##v2 = ws.range("F5").value
    #print("Result:", v1)
    #length=len(data)
    #for i in range(len(date)):
        #ws.range(9+i,0).value = date[i]
        #ws.range(9+i,1).value = data[i]
        #ws.range(1,0).value = "pepe"
    #    ws.range("A"+str(9+i)).value = date[i]
    #    ws.range("B"+str(9+i)).value = data[i]
    import xlwt
    import xlrd
    from xlutils.copy import copy

    # load the excel file
    rb = xlrd.open_workbook('07-253-b Estudio capacidad (con grafico).xls', formatting_info=True)

    # copy the contents of excel file
    wb = copy(rb)

    # open the first sheet
    w_sheet = wb.get_sheet(1)

    # row number = 0 , column number = 1
    #w_sheet.write(0,1,'Modified !')
    length=len(data)
    for i in range(len(date)):
        #ws.range("A"+str(9+i)).value = date[i]
        #ws.range("B"+str(9+i)).value = data[i]
        w_sheet.write(9+i,0,str(date[i]))
        w_sheet.write(9+i,1,str(data[i]))
    

    # save the file
    data=wb.save('07-253-b Estudio capacidad (con grafico)2.xls')
    
####################################################################################################################
    import base64
    writer = pd.ExcelWriter('07-253-b Estudio capacidad (con grafico).xls')
    df.to_excel(writer, index = False, header=True,encoding='utf-8')
    with open(writer,'rb') as f:
        b64 = base64.b64encode(f.read())
        href = f'<a href="data:file/xls;base64,{b64}" download="new_file.xls">Download xls</a>'
    
    st.write(href, unsafe_allow_html=True)



    
    
    
def KYBConvert(df,make_choice):
    #INPUT FILE

    df.to_excel('export.xlsx',index=False)
    
    date=pd.to_datetime(df.iloc[:,1], format="%y%m%d-%H:%M")
    #data=pd.Series(df.iloc[:,4],dtype="int")
    data=pd.Series(df.loc[:,make_choice],dtype="int")
    
    write_excel(data,date)
    


if uploaded_file:
    st.dataframe(dfnumeric)
    makes = df.select_dtypes([np.number]).columns
    make_choice = st.sidebar.selectbox('Select cathegory:', makes)
    print(make_choice)
    if make_choice!='ID':
        KYBConvert(df,make_choice)
        st.write("Puedes encontrar tu excel en: C:/Users/15572890/Desktop/I+D/ProyectosPython/KYB/07-253-b Estudio capacidad (con grafico).xls")