ramaxa mro ramaxa mro - 2 years ago 417
Python Question

PyQt populate QTableWidget from excel

im trying to populate QTableWidget with some data from excel file, i want to add row to my QTable only if ID is on the listID, and i get no data in QTable cells

https://drive.google.com/file/d/0B_PFK3V2Ij4tSko4emplYmNuN1E/view?usp=sharing here an excel file, it don't contain any formatin or formula, just empty rows and columns, because file is generated automaticaly

here is my code

wb = openpyxl.load_workbook(os.path.join(os.getcwd(), file), read_only=True)
ws = wb.active

headers = []
for item in ws[4]:
headers.append(item.value)
headers.pop(0)

listID = []
for index in range(self.listWidgetID.count()):
listID.append(self.listWidgetID.item(index).text())

data = ws.iter_rows(row_offset=5, column_offset=1)

row_increment = 0
self.tableWidgetDATA.setRowCount(1)
self.tableWidgetDATA.setColumnCount(len(headers))
self.tableWidgetDATA.setHorizontalHeaderLabels(headers)

for x, rows in enumerate(data):
if str(rows[0].value) in listID:
for y, cell in enumerate(rows):
item = QTableWidgetItem(str(cell.value))
self.tableWidgetDATA.setItem(x, y, item)
row_increment = row_increment + 1
self.tableWidgetDATA.setRowCount(row_increment)

Answer Source

To add elements to the QTableWidget, the position must be setRowCount(), in your case you update after adding, so it will not be saved, Another error is that you do not verify if the data is valid.

class Widget(QWidget):
    def __init__(self, parent=None):
        QWidget.__init__(self, parent)

        self.tableWidgetDATA = QTableWidget(self)
        self.listWidgetID = QListWidget(self)
        self.setLayout(QVBoxLayout())
        self.layout().addWidget(self.listWidgetID)
        self.layout().addWidget(self.tableWidgetDATA)
        self.listWidgetID.addItems(["113894", "113906", "113895"])
        file = "Livro2.xlsx"
        wb = openpyxl.load_workbook(os.path.join(os.getcwd(), file), read_only=True)
        ws = wb.active

        headers = [item.value for item in ws[4] if item.value is not None]

        listID = [self.listWidgetID.item(i).text() for i in range(self.listWidgetID.count())]

        data = ws.iter_rows(row_offset=5, column_offset=1)

        self.tableWidgetDATA.setColumnCount(len(headers))
        self.tableWidgetDATA.setHorizontalHeaderLabels(headers)

        for x, rows in enumerate(data):
            if rows[0].value is not None:
                if str(rows[0].value) in listID:
                    self.tableWidgetDATA.setRowCount(self.tableWidgetDATA.rowCount()+1)
                    for y, cell in enumerate(rows):
                        val = cell.value
                        if val is not None:
                            item = QTableWidgetItem(str(val))
                            self.tableWidgetDATA.setItem(self.tableWidgetDATA.rowCount()-1, y, item)

Output:

enter image description here

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download