import io
import pandas as pd

def parseExcelDF(df):
    RES = ""
    DELI = ","
    DELI_REPLACE = ";"
    st = 0
    row_count = len(df.iloc[:,0])
    #print(row_count)
    ta_idx = -1
    ke_idx = -1
    db_idx = -1
    kr_idx = -1
    for j in range(row_count):
        col = df.iloc[j,:]
        col_count = len(col)
        if st==0:
            for i in range(col_count):
                x = col[i]
                if (isinstance(x,str)):
                    x = x.upper()
                    if (x == "TGL/BLN/THN" or x=="TANGGAL"):
                        ta_idx = i
                    if (x == "KETERANGAN" or x=="DESKRIPSI"):
                        ke_idx = i
                    if (x == "DEBET" or x=="DEBIT"):
                        db_idx = i
                    if (x == "KREDIT"):
                        kr_idx = i
            if (ta_idx>=0 and ke_idx>=0 and db_idx>=0 and kr_idx>=0):
                st=1
        elif st==1:
            if (isinstance(col[db_idx],int) or isinstance(col[kr_idx],int)):
                RES = RES + str(col[ta_idx]) + DELI + \
                            str(col[ke_idx]).replace(DELI,DELI_REPLACE) + DELI + \
                            str(col[db_idx]) + DELI + \
                            str(col[kr_idx]) + "\r\n"
    return RES

def parseExcelFileFromBuffer(buff):
    xl_file = pd.ExcelFile(buff)
    df = xl_file.parse(0)
    return parseExcelDF(df)

def parseExcelFile(file):
    content = None
    with open(file, 'rb') as content_file:
        content = io.BytesIO(content_file.read())
    return parseExcelFileFromBuffer(content)