Untitled

class AutoFillerPanel(wx.Panel):
    def __init__(self, parent):
        super(AutoFillerPanel, self).__init__(parent)
        self.excel_file_path = None
        self.selected_sheet = None
        self.processed_data = None
        self.hotkey_registered = False
        self.setup_ui()

    def setup_ui(self):
        self.load_button = wx.Button(self, label="Select Excel File")
        self.load_button.Bind(wx.EVT_BUTTON, self.load_excel_file)

        self.file_url_label = wx.StaticText(self, label="")

        self.sheets_label = wx.StaticText(self, label="Select a Sheet (Lane ID):")
        self.sheets_choice = wx.Choice(self, choices=[])
        self.sheets_choice.Disable()
        self.sheets_choice.Bind(wx.EVT_CHOICE, self.select_sheet)

        self.hotkey_label = wx.StaticText(self, label="Press CTRL+SHIFT+I to Fill Fields")

        sizer = wx.BoxSizer(wx.VERTICAL)
        sizer.Add(self.load_button, 0, wx.ALL | wx.EXPAND, 10)
        sizer.Add(self.file_url_label, 0, wx.ALL | wx.EXPAND, 10)
        sizer.Add(self.sheets_label, 0, wx.ALL, 10)
        sizer.Add(self.sheets_choice, 0, wx.ALL | wx.EXPAND, 10)
        sizer.Add(self.hotkey_label, 0, wx.ALL, 10)
        self.SetSizerAndFit(sizer)

    def load_excel_file(self, event):
        with wx.FileDialog(None, "Choose an Excel file", wildcard="Excel Files (*.xlsx)|*.xlsx", style=wx.FD_OPEN) as dialog:
            if dialog.ShowModal() == wx.ID_OK:
                self.excel_file_path = dialog.GetPath()
                self.file_url_label.SetLabel(f"File URL: {self.excel_file_path}")
                self.populate_sheet_names()

    def populate_sheet_names(self):
        workbook = openpyxl.load_workbook(self.excel_file_path, data_only=True)
        sheet_names = workbook.sheetnames
        self.sheets_choice.SetItems(sheet_names)
        self.sheets_choice.Enable()

    def select_sheet(self, event):
        selected_index = self.sheets_choice.GetSelection()
        self.selected_sheet = self.sheets_choice.GetString(selected_index)
        self.process_excel_data()
        self.register_hotkey()  # Register hotkey after selecting the sheet and processing data

    def register_hotkey(self):
        if not self.hotkey_registered:
            keyboard.add_hotkey('ctrl+shift+i', self.paste_data_and_tab)
            self.hotkey_registered = True

    def process_excel_data(self):
        # Initialize indices to None
        self.cost_currency_col_index = None
        self.os_cost_amnt_col_index = None

        if self.excel_file_path and self.selected_sheet:
            workbook = openpyxl.load_workbook(self.excel_file_path, data_only=True)
            if self.selected_sheet in workbook.sheetnames:
                sheet = workbook[self.selected_sheet]
                self.processed_data = []
                self.description_col_index = None

                # Identify the number of columns based on the first non-empty row
                for row in sheet.iter_rows(min_row=1, max_row=sheet.max_row, values_only=True):
                    if any(cell is not None for cell in row):
                        num_columns = len(row)
                        break

                # Identify the index of the "Description" column
                headers = [cell.value for cell in sheet[2]]
                print(headers)  # Add this line
                if "Description" in headers:
                    self.description_col_index = headers.index("Description")

                # Identify the index of the "Cost Currency" and "OS Cost Amnt" columns
                if "Cost Currency" in headers:
                    self.cost_currency_col_index = headers.index("Cost Currency")
                    print(self.cost_currency_col_index)  
                if "OS Cost Amt" in headers:  #
                    self.os_cost_amnt_col_index = headers.index("OS Cost Amt") 
                    print(self.os_cost_amnt_col_index)  

                for row in sheet.iter_rows(min_row=3, max_row=1000, min_col=1, max_col=num_columns, values_only=True):
                    # Check for an empty row to stop processing
                    if not any(row):
                        break

                    self.processed_data.append([str(cell) if cell is not None else "" for cell in row])
            else:
                wx.MessageBox(f"Sheet '{self.selected_sheet}' not found in the workbook.", "Error", wx.OK | wx.ICON_ERROR)

    def paste_data_and_tab(self):
        # Release keys and wait for 3 seconds before starting
        keyboard.release('ctrl')
        keyboard.release('shift')
        time.sleep(3)  # 3-second delay before starting

        # Press tab once to position at the first cell for data entry
        keyboard.press_and_release('tab')
        time.sleep(0.05)  # Brief pause after the initial tab press

        # Iterate through the processed data
        for row_index, row_data in enumerate(self.processed_data):
            for col_index, cell_value in enumerate(row_data):
                # If it's the "Description" column, select all text and delete it
                if col_index == self.description_col_index:
                    keyboard.press_and_release('ctrl+a')
                    keyboard.press_and_release('delete')
                    time.sleep(0.05)  # Pause after deleting the text

                # Fill the cell
                keyboard.write(cell_value)
                time.sleep(0.05)  # Pause after writing the cell value

                # Check if it's not the last cell in the last row before the empty row
                if not (row_index == len(self.processed_data) - 1 and col_index == len(row_data) - 1):
                    keyboard.press_and_release('tab')
                    time.sleep(0.05)  # Pause after pressing tab

        # After filling in all cells, go back and fill in the "Cost Currency" and "OS Cost Amnt" cells
        if self.cost_currency_col_index is not None and self.os_cost_amnt_col_index is not None:
            # Move to the "Cost Currency" column
            for _ in range(len(row_data) - self.cost_currency_col_index - 1):  # Subtract one more here
                keyboard.press_and_release('shift+tab')
                time.sleep(0.05)  # Pause after pressing shift+tab

            # Iterate through the processed data in reverse order
            for row_data in reversed(self.processed_data):
                # Fill in the "Cost Currency" cell
                keyboard.write(row_data[self.cost_currency_col_index])
                time.sleep(0.05)  # Pause after writing the cell value

                # Move up to the previous row
                keyboard.press_and_release('up')
                time.sleep(0.05)  # Pause after pressing up

            # Move to the "OS Cost Amnt" column
            for _ in range(abs(self.cost_currency_col_index - self.os_cost_amnt_col_index)):
                keyboard.press_and_release('tab')
                time.sleep(0.05)  # Pause after pressing tab

            # Iterate through the processed data
            for row_index, row_data in enumerate(self.processed_data):
                # Fill in the "OS Cost Amnt" cell
                keyboard.write(row_data[self.os_cost_amnt_col_index])
                time.sleep(0.05)  # Pause after writing the cell value

                # Check if it's not the last row before pressing 'down'
                if row_index != len(self.processed_data) - 1:
                    # Move down to the next row
                    keyboard.press_and_release('down')
                    time.sleep(0.05)  # Pause after pressing down

        self.unregister_hotkey()
        wx.MessageBox("Data entry complete!", "Info", wx.OK | wx.ICON_INFORMATION)

    def unregister_hotkey(self):
        keyboard.remove_hotkey('ctrl+shift+i')
        self.hotkey_registered = False