Dynamically add items from a file to a ComboBox

Question

I am working on an application that allows the user to dynamically add and remove elements from an excel file. The number of items is not limited.

I am looking for a way to capture elements from an excel file and transfer them to a ComboBox.

To make myself clearer: the problem is not iterating through cells, but getting cell values ​​in a ComboBox. I need a method that captures the contents of all cells with values ​​in this column, where the end of the range is unknown, and then the values ​​are passed to the ComboBox.

Combobox only accepts values, not empty cells. I also do not need fields in the ComboBox that say "No value."

I tried using cell and range methods, but this does not get the value in the ComboBox.

What I still have:

wb = load_workbook (source_file) ws = wb.active self.value_1 = ws['B2'].value self.value_2 = ws['B3'].value self.value_3 = ws['B4'].value self.value_4 = ws['B5'].value self.value_5 = ws['B6'].value self.value_6 = ws['B7'].value self.value_7 = ws['B8'].value self.value_8 = ws['B9'].value self.value_9 = ws['B10'].value self.value_10 = ws['B11'].value stock_items = [ self.value_1 , self.value_2 , self.value_3 , self.value_4 , self.value_5 , self.value_6 , self.value_7 , self.value_8 , self.value_9 , self.value_10 ] self.combo_items_list = [ ] for stock_item in stock_items : if stock_item != None : self.combo_items_list.append (stock_item) self.combo.addItems(self.combo_items_list) 

This works as expected, but it bothers me that I have to add a line of code for each item I extract from the excel file, in addition to add an extra entry to the Stock_items list. If there were 5,000 elements in the file, this would result in 5,000 lines of code and 5,000 entries in the list.

Is there a more efficient and elegant way to deal with a problem using "counter" or pandas?

Thanks in advance.

Answer 1

I found a way to do this with Pandas and not opnpyxl:

  import pandas as pd import numpy as np # get sheet and whole column sales = pd.read_excel ("Inventory.xlsx") # filter out any None Values sales_article = sales ["Artigo"] .dropna() # transform into list sales_list = sales_article.values.tolist() # add list to ComboBox self.combo.addItems(sales_list) 

Answer 2

Openpyxl 2.4 sheets has an iter_cols method that allows you to select a range of cells and return them as columns. Just like iter_rows returns them as strings. This is the easiest and most effective way to do what you want to do.

See https://openpyxl.readthedocs.io/en/default/tutorial.html#accessing-many-cells for more details.

An example of your use case:

cells = [cell.value for cell in ws.iter_cols(min_col=2, max_col=2, min_row=2) if cell.value is not None]

Answer 3

 wb = load_workbook(source_file) ws = wb.active lastrow = ws.UsedRange.Height # don't remember method name for row in range(lastrow): value = ws['B' + str(row + 2)].value if value is not None: self.combo_items_list.append (value) self.combo.addItems(self.combo_items_list) 

See worksheet for other ways to get excel row range.

Answer 4

 wb = load_workbook (source_file) ws = wb.active self.combo_items_list = [ ] // loop from 2(start)-11(end) // check if ws['B'<counter>].value is available and not null // add this value to your array of combo for each ittration. self.combo_items_list.append (ws['B'<counter>].value) self.combo.addItems(self.combo_items_list) 

Sorry if I'm wrong. I do not know the syntax of this language. Still saw a logical answer and decided to publish.