[FIXED] How to add excel file to QTableWidget without erasing its column and data?

Issue

How do i load excel data to QTableWidget? I want it to appear after the last recipient data

This is the code i used:

 def addExcel(self, excel_file_dir, worksheet_name):
    df = pd.read_excel(excel_file_dir, worksheet_name)
    if df.size == 0:
            return 

    df.fillna('', inplace=True)
    self.tableWidget.setRowCount(df.shape[0])
    self.tableWidget.setColumnCount(df.shape[1])

    for row in df.iterrows():
            values = row[1]
            for col_index, value in enumerate(values):
                    if isinstance(value, (float, int)):
                            value = '{0:0,.0f}'.format(value)
                    tableItem = QTableWidgetItem(str(value))
                    self.tableWidget.setItem(row[0], col_index, tableItem)

Solution

As the documentation of setRowCount() explains:

Sets the number of rows in this table's model to rows. If this is less than rowCount(), the data in the unwanted rows is discarded.

The same clearly goes for setColumnCount() as well.

If you want to preserve existing data, you obviously have to add the row and column count based on the new data.

Considering that data models usually increase the number of records (rows) while keeping the columns, you have to call setRowCount() by increasing the current row count by the number of new records, and set the column count based on the maximum between the existing column count and the retrieved one.

def addExcel(self, excel_file_dir, worksheet_name):
    df = pd.read_excel(excel_file_dir, worksheet_name)
    if df.size == 0:
            return 

    df.fillna('', inplace=True)

    newFirstRow = self.tableWidget.rowCount()
    self.tableWidget.setRowCount(newFirstRow + df.shape[0])
    self.tableWidget.setColumnCount(
        max(self.tableWidget.columnCount(), df.shape[1]))

    for rowData in df.iterrows():
        row = newFirstRow + rowData[0]
        values = rowData[1]
        for col_index, value in enumerate(values):
            if isinstance(value, (float, int)):
                value = '{0:0,.0f}'.format(value)
                tableItem = QTableWidgetItem(str(value))
                self.tableWidget.setItem(row, col_index, tableItem)

^{Note: '{0:0,.0f}'.format(value) is quite pointless in most cases: you can just use str(round(value)) instead.}

Answered By - musicamante