How to create filters for QTableView in PyQt

Question

I am using QTableView to display data retrieved from QtSql.QSqlQuery

I want to know how can i create filters for it like in excel.

In the above image i need to get the filters for All heders (Sh_Code,SH_Seq,Stage) The filters will have unique values in of that column on which we can filter.

Required result

I need the Table view header with a dropbox listing all unique values in that column just like in excel below. No need of Top,Standard filter... as shown in image. Need only "All" and the unique "column items"

This is from my .NET application, uploaded for more clarity

Answer 1

I tried to update the answer for PyQt6. I used QListWidget instead of QMenu, it will creater a scroll bar when you got a lot of data. I create a funtion to read the excel file. For the position issue of filter, I add a judgment statement, so that the filter won't go outside the screen.

import sys
from PyQt6.QtWidgets import *

from PyQt6 import QtWidgets
from PyQt6 import QtCore,QtGui
import pandas as pd

class Table(QWidget):
    def __init__(self):
        super().__init__()
        self.initUI()
    def initUI(self):
        self.setWindowTitle('QTableWidget')
        self.setGeometry(300, 300, 500, 300)
        self.table = QTableWidget()
        self.table.horizontalHeader().setSectionResizeMode(QHeaderView.ResizeMode.ResizeToContents)
        self.centralwidget = QtWidgets.QWidget(self)

        self.btn1 = QPushButton('Unfilter', self)
        self.btn1.clicked.connect(self.Unfilter)

        # 创建布局
        self.filter_layout = QHBoxLayout()
        self.filter_layout.addStretch(3)
        self.filter_layout.addWidget(self.btn1)

        self.layout = QVBoxLayout()
        self.layout.addLayout(self.filter_layout)
        self.layout.addWidget(self.table)
        self.setLayout(self.layout)

        self.horizontalHeader = self.table.horizontalHeader()
        self.horizontalHeader.sectionClicked.connect(self.on_view_horizontalHeader_sectionClicked)
        filename = 'test.xlsx'
        self.df1 = pd.read_excel(filename, engine='openpyxl', sheet_name='Sheet1', header=0)
        # self.df1['Date'] =self.df1['Date'].dt.date
        self.insertData(self.df1)

    def on_view_horizontalHeader_sectionClicked(self, logicalIndex):
        self.logicalIndex   = logicalIndex
        self.menuValues     = QtWidgets.QMenu(self)
        self.signalMapper   = QtCore.QSignalMapper(self)

        valuesUnique = [self.table.item(row, self.logicalIndex).text() for row in range(self.table.rowCount()) if not self.table.isRowHidden(row)]

        # 子窗口
        self.Menudialog = QDialog(self)
        self.Menudialog.setWindowTitle('Sub Window')
        self.Menudialog.resize(200, 150)

        self.list_widget = QListWidget(self)
        self.list_widget.addItems(sorted(list(set(valuesUnique))))
        # create QScrollArea，put QListWidget in it
        scroll_area = QScrollArea(self.Menudialog)
        scroll_area.setWidgetResizable(True)
        scroll_area.setWidget(self.list_widget)

        self.list_widget.currentItemChanged.connect(self.on_list_item_clicked)
        layout = QVBoxLayout(self.Menudialog)
        layout.addWidget(scroll_area)

        self.setLayout(layout)

        headerPos = self.table.mapToGlobal(self.horizontalHeader.pos())
        posY = headerPos.y() + self.horizontalHeader.height()
        # posX = headerPos.x() + self.horizontalHeader.sectionPosition(self.logicalIndex)
        posX = headerPos.x() + self.horizontalHeader.sectionViewportPosition(self.logicalIndex)
        print(posX)
        if posX > 1700:
            posX = 1620

        self.Menudialog.setGeometry(posX +100, posY, 200, 300)
        self.Menudialog.exec()

        ''' 
        actionAll = QtGui.QAction("All", self)
        actionAll.triggered.connect(self.Unfilter)
        self.menuValues.addAction(actionAll)
        self.menuValues.addSeparator()

        for actionNumber, actionName in enumerate(sorted(list(set(valuesUnique)))):
            action = QtGui.QAction(actionName, self)
            self.signalMapper.setMapping(action, actionNumber)
            action.triggered.connect(self.signalMapper.map)
            self.menuValues.addAction(action)

        self.signalMapper.mappedInt.connect(self.on_signalMapper_mapped)

        headerPos = self.table.mapToGlobal(self.horizontalHeader.pos())

        posY = headerPos.y() + self.horizontalHeader.height()
        posX = headerPos.x() + self.horizontalHeader.sectionPosition(self.logicalIndex)

        self.menuValues.exec(QtCore.QPoint(posX, posY))
        '''
    def on_list_item_clicked(self):
        item = self.list_widget.currentItem()
        column = self.logicalIndex
        for i in range(self.table.rowCount()):
            if self.table.item(i, column).text() != item.text():
                self.table.setRowHidden(i, True)
        self.Menudialog.close()

    def on_signalMapper_mapped(self, i):
        stringAction = self.signalMapper.mapping(i).text()
        column = self.logicalIndex
        for i in range(self.table.rowCount()):
            if self.table.item(i, column).text() != stringAction:
                self.table.setRowHidden(i, True)

    def filter(self):
        column = self.filter_box.currentIndex()
        for i in range(self.table.rowCount()):
            if self.table.item(i, column).text() != self.search_box.text() and self.search_box.text() !='':
                self.table.setRowHidden(i, True)

    def search(self):
        text = self.search_box.text()
        for i in range(self.table.rowCount()):
            self.table.setRowHidden(i, False)

        for i in range(self.table.rowCount()):
            for j in range(self.table.columnCount()):
                if text in self.table.item(i, j).text():
                    break
                else:
                    self.table.setRowHidden(i, True)

    def Unfilter(self):
        for i in range(self.table.rowCount()):
                self.table.showRow(i)
    def insertData(self, df1):
        if len(df1) < 1:
            msgBox = QMessageBox(self)
            msgBox.setWindowTitle('注意')
            msgBox.setText('数据集为空,不能显示!')
            msgBox.setStandardButtons(QMessageBox.StandardButton.Yes)
            msgBox.exec()
            return
        # 获取列名
        self.header = df1.columns.tolist()
        # 获取数据
        data = df1.values.tolist()
        self.row =len(data)
        self.col = len(data[0])
        # 设置表格的行数为0，以清空之前的内容
        self.table.setRowCount(0)
        self.table.setRowCount(self.row)
        self.table.setColumnCount(self.col)
        self.table.setHorizontalHeaderLabels(self.header)
        if data:
            # 按行添加数据
            for r in range(self.row):
                for c in range(self.col):
                    # 添加表格数据
                    newItem = QTableWidgetItem(str(data[r][c]))
                    self.table.setItem(r, c, newItem)
        else:
            self.label.setText('无数据')

if __name__ == '__main__':
    app = QApplication(sys.argv)
    table = Table()
    table.show()
    sys.exit(app.exec())

Answer 2

I tried to update the answer provided in the above for PyQt5

from PyQt5 import QtCore, QtGui, QtWidgets

class myWindow(QtWidgets.QMainWindow):
    def __init__(self, parent=None):
        super(myWindow, self).__init__(parent)
        self.centralwidget  = QtWidgets.QWidget(self)
        self.lineEdit       = QtWidgets.QLineEdit(self.centralwidget)
        self.view           = QtWidgets.QTableView(self.centralwidget)
        self.comboBox       = QtWidgets.QComboBox(self.centralwidget)
        self.label          = QtWidgets.QLabel(self.centralwidget)

        self.gridLayout = QtWidgets.QGridLayout(self.centralwidget)
        self.gridLayout.addWidget(self.lineEdit, 0, 1, 1, 1)
        self.gridLayout.addWidget(self.view, 1, 0, 1, 3)
        self.gridLayout.addWidget(self.comboBox, 0, 2, 1, 1)
        self.gridLayout.addWidget(self.label, 0, 0, 1, 1)

        self.setCentralWidget(self.centralwidget)
        self.label.setText("Regex Filter")

        self.model = QtGui.QStandardItemModel(self)

        for rowName in range(3*5):
            self.model.invisibleRootItem().appendRow(
                [   QtGui.QStandardItem("row {0} col {1}".format(rowName, column))    
                    for column in range(3)
                    ]
                )

        self.proxy = QtCore.QSortFilterProxyModel(self)
        self.proxy.setSourceModel(self.model)

        self.view.setModel(self.proxy)
        self.comboBox.addItems(["Column {0}".format(x) for x in range(self.model.columnCount())])

        self.lineEdit.textChanged.connect(self.on_lineEdit_textChanged)
        self.comboBox.currentIndexChanged.connect(self.on_comboBox_currentIndexChanged)

        self.horizontalHeader = self.view.horizontalHeader()
        self.horizontalHeader.sectionClicked.connect(self.on_view_horizontalHeader_sectionClicked)

    @QtCore.pyqtSlot(int)
    def on_view_horizontalHeader_sectionClicked(self, logicalIndex):
        self.logicalIndex   = logicalIndex
        self.menuValues     = QtWidgets.QMenu(self)
        self.signalMapper   = QtCore.QSignalMapper(self)  

        self.comboBox.blockSignals(True)
        self.comboBox.setCurrentIndex(self.logicalIndex)
        self.comboBox.blockSignals(True)

        valuesUnique = [    self.model.item(row, self.logicalIndex).text()
                            for row in range(self.model.rowCount())
                            ]

        actionAll = QtWidgets.QAction("All", self)
        actionAll.triggered.connect(self.on_actionAll_triggered)
        self.menuValues.addAction(actionAll)
        self.menuValues.addSeparator()

        for actionNumber, actionName in enumerate(sorted(list(set(valuesUnique)))):              
            action = QtWidgets.QAction(actionName, self)
            self.signalMapper.setMapping(action, actionNumber)  
            action.triggered.connect(self.signalMapper.map)  
            self.menuValues.addAction(action)

        self.signalMapper.mapped.connect(self.on_signalMapper_mapped)  

        headerPos = self.view.mapToGlobal(self.horizontalHeader.pos())        

        posY = headerPos.y() + self.horizontalHeader.height()
        posX = headerPos.x() + self.horizontalHeader.sectionPosition(self.logicalIndex)

        self.menuValues.exec_(QtCore.QPoint(posX, posY))

    @QtCore.pyqtSlot()
    def on_actionAll_triggered(self):
        filterColumn = self.logicalIndex
        filterString = QtCore.QRegExp(  "",
                                        QtCore.Qt.CaseInsensitive,
                                        QtCore.QRegExp.RegExp
                                        )

        self.proxy.setFilterRegExp(filterString)
        self.proxy.setFilterKeyColumn(filterColumn)

    @QtCore.pyqtSlot(int)
    def on_signalMapper_mapped(self, i):
        stringAction = self.signalMapper.mapping(i).text()
        filterColumn = self.logicalIndex
        filterString = QtCore.QRegExp(  stringAction,
                                        QtCore.Qt.CaseSensitive,
                                        QtCore.QRegExp.FixedString
                                        )

        self.proxy.setFilterRegExp(filterString)
        self.proxy.setFilterKeyColumn(filterColumn)

    @QtCore.pyqtSlot(str)
    def on_lineEdit_textChanged(self, text):
        search = QtCore.QRegExp(    text,
                                    QtCore.Qt.CaseInsensitive,
                                    QtCore.QRegExp.RegExp
                                    )

        self.proxy.setFilterRegExp(search)

    @QtCore.pyqtSlot(int)
    def on_comboBox_currentIndexChanged(self, index):
        self.proxy.setFilterKeyColumn(index)


if __name__ == "__main__":
    import sys

    app  = QtWidgets.QApplication(sys.argv)
    main = myWindow()
    main.show()
    main.resize(400, 600)
    sys.exit(app.exec_())

Answer 3

As per the answer from @user1006989 & @Behzad Jamali: The menu position for filtering on header won't popup at the exact position if the table has more columns than the current view port.

To have a correct positioning of the popup menu, use this line

posX = headerPos.x() + self.horizontalHeader.sectionViewportPosition(index)

Answer 4

Here is an example of filtering in PyQt using QSortFilterProxyModel, QStandardItemModel and QTableView, it can be easily adapted to other views and models:

#!/usr/bin/env python
#-*- coding:utf-8 -*-

from PyQt4 import QtCore, QtGui

class myWindow(QtGui.QMainWindow):
    def __init__(self, parent=None):
        super(myWindow, self).__init__(parent)
        self.centralwidget  = QtGui.QWidget(self)
        self.lineEdit       = QtGui.QLineEdit(self.centralwidget)
        self.view           = QtGui.QTableView(self.centralwidget)
        self.comboBox       = QtGui.QComboBox(self.centralwidget)
        self.label          = QtGui.QLabel(self.centralwidget)

        self.gridLayout = QtGui.QGridLayout(self.centralwidget)
        self.gridLayout.addWidget(self.lineEdit, 0, 1, 1, 1)
        self.gridLayout.addWidget(self.view, 1, 0, 1, 3)
        self.gridLayout.addWidget(self.comboBox, 0, 2, 1, 1)
        self.gridLayout.addWidget(self.label, 0, 0, 1, 1)

        self.setCentralWidget(self.centralwidget)
        self.label.setText("Regex Filter")

        self.model = QtGui.QStandardItemModel(self)

        for rowName in range(3) * 5:
            self.model.invisibleRootItem().appendRow(
                [   QtGui.QStandardItem("row {0} col {1}".format(rowName, column))    
                    for column in range(3)
                    ]
                )

        self.proxy = QtGui.QSortFilterProxyModel(self)
        self.proxy.setSourceModel(self.model)

        self.view.setModel(self.proxy)
        self.comboBox.addItems(["Column {0}".format(x) for x in range(self.model.columnCount())])

        self.lineEdit.textChanged.connect(self.on_lineEdit_textChanged)
        self.comboBox.currentIndexChanged.connect(self.on_comboBox_currentIndexChanged)

        self.horizontalHeader = self.view.horizontalHeader()
        self.horizontalHeader.sectionClicked.connect(self.on_view_horizontalHeader_sectionClicked)

    @QtCore.pyqtSlot(int)
    def on_view_horizontalHeader_sectionClicked(self, logicalIndex):
        self.logicalIndex   = logicalIndex
        self.menuValues     = QtGui.QMenu(self)
        self.signalMapper   = QtCore.QSignalMapper(self)  

        self.comboBox.blockSignals(True)
        self.comboBox.setCurrentIndex(self.logicalIndex)
        self.comboBox.blockSignals(True)

        valuesUnique = [    self.model.item(row, self.logicalIndex).text()
                            for row in range(self.model.rowCount())
                            ]

        actionAll = QtGui.QAction("All", self)
        actionAll.triggered.connect(self.on_actionAll_triggered)
        self.menuValues.addAction(actionAll)
        self.menuValues.addSeparator()

        for actionNumber, actionName in enumerate(sorted(list(set(valuesUnique)))):              
            action = QtGui.QAction(actionName, self)
            self.signalMapper.setMapping(action, actionNumber)  
            action.triggered.connect(self.signalMapper.map)  
            self.menuValues.addAction(action)

        self.signalMapper.mapped.connect(self.on_signalMapper_mapped)  

        headerPos = self.view.mapToGlobal(self.horizontalHeader.pos())        

        posY = headerPos.y() + self.horizontalHeader.height()
        posX = headerPos.x() + self.horizontalHeader.sectionPosition(self.logicalIndex)

        self.menuValues.exec_(QtCore.QPoint(posX, posY))

    @QtCore.pyqtSlot()
    def on_actionAll_triggered(self):
        filterColumn = self.logicalIndex
        filterString = QtCore.QRegExp(  "",
                                        QtCore.Qt.CaseInsensitive,
                                        QtCore.QRegExp.RegExp
                                        )

        self.proxy.setFilterRegExp(filterString)
        self.proxy.setFilterKeyColumn(filterColumn)

    @QtCore.pyqtSlot(int)
    def on_signalMapper_mapped(self, i):
        stringAction = self.signalMapper.mapping(i).text()
        filterColumn = self.logicalIndex
        filterString = QtCore.QRegExp(  stringAction,
                                        QtCore.Qt.CaseSensitive,
                                        QtCore.QRegExp.FixedString
                                        )

        self.proxy.setFilterRegExp(filterString)
        self.proxy.setFilterKeyColumn(filterColumn)

    @QtCore.pyqtSlot(str)
    def on_lineEdit_textChanged(self, text):
        search = QtCore.QRegExp(    text,
                                    QtCore.Qt.CaseInsensitive,
                                    QtCore.QRegExp.RegExp
                                    )

        self.proxy.setFilterRegExp(search)

    @QtCore.pyqtSlot(int)
    def on_comboBox_currentIndexChanged(self, index):
        self.proxy.setFilterKeyColumn(index)


if __name__ == "__main__":
    import sys

    app  = QtGui.QApplication(sys.argv)
    main = myWindow()
    main.show()
    main.resize(400, 600)
    sys.exit(app.exec_())

To get required results, a popup menu is launched by clicking on the header, and populated with the unique values for that column. Once an item in the popup menu is selected, the value is passed to self.proxy.setFilterRegExp(filterString) and the column to self.proxy.setFilterKeyColumn(filterValue).