Dynamically add items from a file to a ComboBox

Question

I am working on an application that allows the user to dynamically add to and remove items from an excel file. The quantity of items shall be unlimited.

I am looking for a way to grab the items from the excel file and transfer them to the ComboBox.

To make myself clearer: The problem is not iterating through cells, but getting cell values into the ComboBox. I need a method that captures the content of all cells with values in a given column, where the end of range is unknown and then transfer the values to a ComboBox.

The Combobox only accepts values, not any empty cells. I also don't want fields in the ComboBox that say "No Value".

I have tried itering through cells and range methods, but this doesn't get the values into the ComboBox.

What I have so far is:

    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 what troubles me is that I have to add a line of code for each item I grab from the excel file, besides having to put an extra entry into the stock_items list. If there were 5.000 items in the file, that would result in 5.000 lines of code and 5000 entries in the list.

Is there a more efficient and elegant way to handle the issue with "counter" or pandas?

Thanks in advance.

Answer 1

I found a way to do this nicely using Pandas, 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)