import sys

from PyQt5.QtGui import *
from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5.QtWidgets import *
import pandas as pd


class App(QWidget):

    CATEGORY, ID, DESCRIPTION = range(3)

    def __init__(self):
        super().__init__()

        self.initUI()

    def initUI(self):
        self.setWindowTitle("Insumos.CSV - Find")

        self.dataView = QTreeView()
        self.dataView.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.dataView.doubleClicked.connect(self.copySelected)

        self.searchText = QLineEdit()
        self.searchButton = QPushButton("Find")
        self.searchButton.clicked.connect(self.findString)
        self.searchText.returnPressed.connect(self.searchButton.click)

        self.searchBox = QHBoxLayout()
        self.searchBox.addWidget(self.searchText)
        self.searchBox.addWidget(self.searchButton)

        self.mainBox = QVBoxLayout()
        self.mainBox.addLayout(self.searchBox)
        self.mainBox.addWidget(self.dataView)

        self.readData()

        self.resize(500, 200)
        self.setLayout(self.mainBox)

        self.show()

    def createTreeModel(self, parent):
        model = QStandardItemModel(0, 3, parent)
        model.setHeaderData(self.CATEGORY, Qt.Horizontal, "Category")
        model.setHeaderData(self.ID, Qt.Horizontal, "ID")
        model.setHeaderData(self.DESCRIPTION, Qt.Horizontal, "Description")
        return model

    def addRow(self, model, category, id_, description):
        self.treeModel.insertRow(0)
        self.treeModel.setData(self.treeModel.index(0, self.CATEGORY), category)
        self.treeModel.setData(self.treeModel.index(0, self.ID), id_)
        self.treeModel.setData(self.treeModel.index(0, self.DESCRIPTION), description)

    def updateTreeModel(self):
        self.treeModel = self.createTreeModel(self)
        self.dataView.setModel(self.treeModel)

    def readData(self):
        self.updateTreeModel()
        self.data_frame = pd.read_excel("test2.xlsx")
        for row in self.data_frame.values:
            self.addRow(self.treeModel, *row)

    def findString(self):
        self.updateTreeModel()
        for row in self.data_frame[self.data_frame["DESCRIPTION"].str.find(self.searchText.text()) != -1].values:
            self.addRow(self.treeModel, *row)

    def copySelected(self):
        index = self.dataView.currentIndex()
        text = " ".join([str(self.treeModel.data(index.sibling(index.row(), col))) for col in range(3)])
        QApplication.clipboard().setText(text)


if __name__ == '__main__':
    app = QApplication(sys.argv)
    ex = App()
    sys.exit(app.exec_())