Populate QTableWidget from Excel

Question

I've used xlrd to import excel file. There are no tutorials on how to populate these data into QTableWidget. Code for importing excel:

import xlrd

book = xlrd.open_workbook('bioreactorfinal.xlsx')
sheet = book.sheets() [1]
data = [[sheet.cell_value(r,c) for c in range (sheet.ncols)]for r in range(sheet.nrows)]
print(data)

Code for QTableWidget

self.tableWidget.setColumnCount(32)
self.tableWidget.setRowCount(32)

for row, columnvalues in enumerate(data):
    for column, value in enumerate(columnvalues):
        item = QtGui.QTableWidgetItem(value)
        self.tableWidget.setItem(row, column, item)

Answer 1

In order to populate it into a QTableWidget, you need to separate it into a few steps:

1. Extract the headers
2. Extract the values for each column
3. Generate your QTableWidget and fill it with the data

How you read the headers depends somewhat on your expectations, if you make no assumptions about the input data, then you will have no headers.

Here, I will assume the headers are on the first row. Starting off where you leave the data, here is how to fill the QTableWidget from the data:

from PySide import QtGui

data = [[sheet.cell_value(r,c) for c in range (sheet.ncols)] for r in range(sheet.nrows)]

app = QtGui.QApplication([])
mytable = QtGui.QTableWidget()

# skip these lines if you don't have headers
headers = data[0]
data = data[1:]
self.setHorizontalHeaderLabels(headers)
# stop skipping lines

for row, columnvalues in enumerate(data):
    for column, value in enumerate(columnvalues):
        item = QtGui.QTableWidgetItem(value)
        mytable.setItem(row, column, item)

This is just an extremely basic template, but a general idea on how to convert an Excel worksheet to a QTableWidget. You can then embed this into a QMainWindow or QDialog.

A few things to be cautious for:

1. Some excel files have padding columns and rows. You might want to remove rows and columns with only blank values (check for null strings).
2. You may have many more columns or rows than desired, in which case you want scrollbars, or may have data that fits your projection (no scrollbar). Qt automatically decides if to display the scrollbar, but you may want to override this.