import ctypes
import pandas as pd
import os.path
import win32com.client
import os

def Mbox(title, text, style):
    return ctypes.windll.user32.MessageBoxW(0, text, title, style)

def run_macro(pathof):
    try:
        xlApp = win32com.client.DispatchEx('Excel.Application')
        xlsPath = os.path.expanduser(pathof)
        wb = xlApp.Workbooks.Open(Filename=xlsPath)
        xlApp.Run('importCSV')
        wb.Close(True)
        xlApp.Quit()
    except:
        wb.Close(True)
        xlApp.Quit()


def main():


    excel_name = "Audit_Soft.xlsm"
    sheet_name = "Main"
    table_Sheet = "Group Table"
    output_name = "output.csv"

    excel_path = os.path.dirname(os.path.realpath('__file__'))
    excel_path = os.path.join(excel_path, excel_name)

    df=pd.read_excel(excel_path, sheet_name=sheet_name, header=None)

    column_name = df.iloc[2,1]
    txt_path = df.iloc[1,0]

    #reading txt data file line by line
    with open(txt_path) as f:
        lines = []
        for line in f:
            if line.strip() == "":
                continue
            lines.append(line.strip())

    masterdb = []

    #saving txt db into array
    for line_index, _ in enumerate(lines):
        masterdb.append([lines[line_index].split()[0], lines[line_index].split()[1]])
    ##################

    df = pd.read_excel(excel_name, sheet_name=table_Sheet)
    cpt_list = df[column_name].tolist()

    results = []

    col1, col2 = set(), set()
    for master_elem in masterdb:
        col1.add(master_elem[0])
        col2.add(master_elem[1])

    for elem in cpt_list:
        to_append = ""
        if elem in col1:
            to_append = "Col1"
        elif elem in col2:
            to_append = "Col2"
        results.append(to_append)

    with open(output_name, 'w') as file_handler:
        file_handler.write("\n".join(results))

    run_macro(excel_path)
    os.startfile(excel_path)

if __name__ == '__main__':
    main()