Dynamically add items from a file to a ComboBox

combobox, openpyxl, pandas, pyqt

Solution

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)

Problem

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.

Original source