I'm trying to filter multiple columns with 1 QLineEdit box in a QTableView. I'm using a QAbstractTableModel to do the filtering and I've subclassed QSortFilterProxyModel to handle multiple columns. The data is pulled from a xlsx file and loaded into a dataframe using pandas.
So my code is working, as long as the headers in the excel file are numerical (0, 1, 2..etc). In the pandas to_excel function there's a flag to prevent pandas from adding it's own 'header' and index' labels header=none and index=none respectively. So when I set the headers to none I get this error when populating the QTableView
TypeError: 'PySide2.QtCore.QRegExp.indexIn' called with wrong argument types:
PySide2.QtCore.QRegExp.indexIn(int)
Supported signatures:
PySide2.QtCore.QRegExp.indexIn(str, int = 0, PySide2.QtCore.QRegExp.CaretMode = PySide2.QtCore.QRegExp.CaretMode.CaretAtZero)
Here's my custom 'filterAcceptsRow' function:
def filterAcceptsRow(self, sourceRow, sourceParent):
index0 = self.sourceModel().index(sourceRow, 0, sourceParent)
index1 = self.sourceModel().index(sourceRow, 1, sourceParent)
index2 = self.sourceModel().index(sourceRow, 2, sourceParent)
# The following line causes the error
return ((self.filterRegExp().indexIn(self.sourceModel().data(index0, self.role)) >= 0
or self.filterRegExp().indexIn(self.sourceModel().data(index1, self.role)) >= 0
or self.filterRegExp().indexIn(self.sourceModel().data(index2, self.role)) >= 0))
Here's a working example of my code
from pandas import DataFrame, ExcelFile, Series
from PySide2.QtWidgets import QApplication, QTabWidget, QVBoxLayout, QAbstractItemView, QSizePolicy, QAbstractScrollArea, QHeaderView, QTableView, QMainWindow, QGridLayout, QLineEdit, QWidget
from PySide2.QtCore import Qt, QAbstractTableModel, QSortFilterProxyModel, QModelIndex, QRegExp, QDate, QDateTime
class PandasModel(QAbstractTableModel):
def __init__(self, data, parent=None):
QAbstractTableModel.__init__(self, parent)
self._filters = {}
self._sortBy = []
self._sortDirection = []
self._dfSource = data
self._dfDisplay = data
self._dfDisplay = self._dfDisplay.fillna("")
def rowCount(self, parent=QModelIndex()):
if parent.isValid():
return 0
return self._dfDisplay.shape[0]
def columnCount(self, parent=QModelIndex()):
if parent.isValid():
return 0
return self._dfDisplay.shape[1]
def data(self, index, role):
if index.isValid() and role == Qt.DisplayRole:
return self._dfDisplay.values[index.row()][index.column()]
return None
def headerData(self, col, orientation=Qt.Horizontal, role=Qt.DisplayRole):
if orientation == Qt.Horizontal and role == Qt.DisplayRole:
return str(self._dfDisplay.columns[col])
return None
def setupModel(self, header, data):
self._dfSource = DataFrame(data, columns=header)
self._sortBy = []
self._sortDirection = []
self.setFilters({})
def setFilters(self, filters):
self.modelAboutToBeReset.emit()
self._filters = filters
print(filters)
self.updateDisplay()
self.modelReset.emit()
def sort(self, col, order=Qt.AscendingOrder):
# Storing persistent indexes
self.layoutAboutToBeChanged.emit()
oldIndexList = self.persistentIndexList()
oldIds = self._dfDisplay.index.copy()
# Sorting data
column = self._dfDisplay.columns[col]
ascending = (order == Qt.AscendingOrder)
if column in self._sortBy:
i = self._sortBy.index(column)
self._sortBy.pop(i)
self._sortDirection.pop(i)
self._sortBy.insert(0, column)
self._sortDirection.insert(0, ascending)
self.updateDisplay()
# Updating persistent indexes
newIds = self._dfDisplay.index
newIndexList = []
for index in oldIndexList:
id = oldIds[index.row()]
newRow = newIds.get_loc(id)
newIndexList.append(self.index(newRow, index.column(), index.parent()))
self.changePersistentIndexList(oldIndexList, newIndexList)
self.layoutChanged.emit()
self.dataChanged.emit(QModelIndex(), QModelIndex())
def updateDisplay(self):
dfDisplay = self._dfSource.copy()
# Filtering
cond = Series(True, index=dfDisplay.index)
for column, value in self._filters.items():
cond = cond & \
(dfDisplay[column].str.lower().str.find(str(value).lower()) >= 0)
dfDisplay = dfDisplay[cond]
# Sorting
if len(self._sortBy) != 0:
dfDisplay.sort_values(by=self._sortBy, ascending=self._sortDirection, inplace=True)
# Updating
self._dfDisplay = dfDisplay
class SortFilterProxyModel(QSortFilterProxyModel):
def __init__(self, data, parent=None):
super(SortFilterProxyModel, self).__init__(parent)
self.role = Qt.DisplayRole
self.minDate = QDate()
self.maxDate = QDate()
self.__data = data
def setFilterMinimumDate(self, date):
self.minDate = date
self.invalidateFilter()
def filterMinimumDate(self):
return self.minDate
def setFilterMaximumDate(self, date):
self.maxDate = date
self.invalidateFilter()
def filterMaximumDate(self):
return self.maxDate
def filterAcceptsRow(self, sourceRow, sourceParent):
index0 = self.sourceModel().index(sourceRow, 0, sourceParent)
index1 = self.sourceModel().index(sourceRow, 1, sourceParent)
index2 = self.sourceModel().index(sourceRow, 2, sourceParent)
return ((self.filterRegExp().indexIn(self.sourceModel().data(index0, self.role)) >= 0 or self.filterRegExp().indexIn(self.sourceModel().data(index1, self.role)) >= 0 or self.filterRegExp().indexIn(self.sourceModel().data(index2, self.role)) >= 0))
def lessThan(self, left, right):
leftData = self.sourceModel().data(left, self.role)
rightData = self.sourceModel().data(right, self.role)
if not isinstance(leftData, QDate):
emailPattern = QRegExp("([\\w\\.]*@[\\w\\.]*)")
if left.column() == 1 and emailPattern.indexIn(leftData) != -1:
leftData = emailPattern.cap(1)
if right.column() == 1 and emailPattern.indexIn(rightData) != -1:
rightData = emailPattern.cap(1)
return leftData < rightData
def dateInRange(self, date):
if isinstance(date, QDateTime):
date = date.date()
return ((not self.minDate.isValid() or date >= self.minDate)
and (not self.maxDate.isValid() or date <= self.maxDate))
class PerfSearchTest(QMainWindow):
def __init__(self):
super().__init__()
self.path_to_local_spreadsheet = "output.xlsx"
self.spreadsheet_display = {}
self.searches = {}
self.current_tab_layout = {}
self.tab_widget = {}
self.central_widget = QWidget()
self.tabs = QTabWidget()
self.layout_grid = QGridLayout(self)
self.layout_grid.setSpacing(10)
self.populate_spreadsheet_table()
self.layout_grid.addWidget(self.tabs, 0, 0)
self.central_widget.setLayout(self.layout_grid)
self.setCentralWidget(self.central_widget)
def onTextChanged(self, text):
self.proxyModelContact.setFilterRegExp(QRegExp(text, Qt.CaseInsensitive, QRegExp.FixedString))
def populate_spreadsheet_table(self):
opened_excel_flie_dataframe = ExcelFile(self.path_to_local_spreadsheet)
opened_excel_file_dataframe_worksheets = []
for y, sheet in enumerate(opened_excel_flie_dataframe.sheet_names):
df = opened_excel_flie_dataframe.parse(y)
self.tab_widget[y] = QWidget()
self.searches[y] = QLineEdit(sheet)
self.spreadsheet_display[y] = QTableView()
self.spreadsheet_display[y].setSortingEnabled(True)
self.spreadsheet_display[y].setEditTriggers(QAbstractItemView.NoEditTriggers)
self.spreadsheet_display[y].setAlternatingRowColors(True)
self.spreadsheet_display[y].horizontalHeader().setSectionResizeMode(QHeaderView.Stretch)
self.spreadsheet_display[y].verticalHeader().setSectionResizeMode(QHeaderView.ResizeToContents)
self.spreadsheet_display[y].setSizeAdjustPolicy(QAbstractScrollArea.AdjustToContents)
self.spreadsheet_display[y].setSizePolicy(QSizePolicy.Minimum, QSizePolicy.Expanding)
self.spreadsheet_display[y].resizeColumnsToContents()
self.current_tab_layout[y] = QVBoxLayout()
self.current_tab_layout[y].addWidget(self.searches[y])
self.current_tab_layout[y].addWidget(self.spreadsheet_display[y])
self.proxyModelContact = SortFilterProxyModel(df)
self.proxyModelContact.setSourceModel(PandasModel(df))
self.searches[y].textChanged.connect(self.onTextChanged)
self.spreadsheet_display[y].setModel(self.proxyModelContact)
self.tab_widget[y].setLayout(self.current_tab_layout[y])
opened_excel_file_dataframe_worksheets.append(df)
self.tabs.addTab(self.tab_widget[y], sheet)
if __name__ == "__main__":
app = QApplication()
w = PerfSearchTest()
w.show()
app.exec_()
Here's a 'broken' spreadsheet that will replicate the error
Here's a 'working' spreadsheet that will not replicate the error(or it shouldn't)
I'd be happy to pass on the entire sourcecode in case it's something else, if needed. I didn't include the stuff that relies on pygsheets to download - input to pandas dataframe - then export to xslx.
The problem is caused because indexIng expects a string and as you point out you are passing an integer, so a possible solution is to convert it to string:
def filterAcceptsRow(self, sourceRow, sourceParent):
index0 = self.sourceModel().index(sourceRow, 0, sourceParent)
index1 = self.sourceModel().index(sourceRow, 1, sourceParent)
index2 = self.sourceModel().index(sourceRow, 2, sourceParent)
value = self.sourceModel().data(index0, self.role)
for ix in (index0, index1, index2):
value = self.sourceModel().data(ix, self.role)
if self.filterRegExp().indexIn(str(value)) >= 0:
return True
return False