Untitled
unknown
plain_text
a year ago
8.0 kB
2
Indexable
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
Editor is loading...
Leave a Comment