Created
November 29, 2022 06:27
-
-
Save ialexpovad/9705e8f9efa863d5d345dab401cc7d09 to your computer and use it in GitHub Desktop.
[Excel, LibreOffice table in PyQt] The task is to copy table from office applications (MS Office, LibreOffice) and to paste it into GUI that was implemented by PyQt5 and QTableView.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| #!/usr/bin/python3 | |
| # -*- coding: utf-8 -*- | |
| import sys | |
| import csv, codecs | |
| import os | |
| import pandas as pd | |
| import numpy as np | |
| from PyQt5.QtCore import Qt, QDir, QItemSelectionModel, QAbstractTableModel, QModelIndex, QVariant, QSize, QSettings | |
| from PyQt5.QtWidgets import (QMainWindow, QTableView, QApplication, QToolBar, QLineEdit, QComboBox, QDialog, | |
| QAction, QMenu, QFileDialog, QAbstractItemView, QMessageBox, QWidget, QTableWidgetItem) | |
| from PyQt5.QtGui import QStandardItemModel, QStandardItem, QCursor, QIcon, QKeySequence, QTextDocument, QTextCursor, QTextTableFormat | |
| from PyQt5 import QtPrintSupport | |
| class PandasModel(QAbstractTableModel): | |
| def __init__(self, df = pd.DataFrame(), parent=None): | |
| QAbstractTableModel.__init__(self, parent=None) | |
| self._df = df | |
| self.setChanged = False | |
| self.dataChanged.connect(self.setModified) | |
| def setModified(self): | |
| self.setChanged = True | |
| print(self.setChanged) | |
| def headerData(self, section, orientation, role=Qt.DisplayRole): | |
| if role != Qt.DisplayRole: | |
| return QVariant() | |
| if orientation == Qt.Horizontal: | |
| try: | |
| return self._df.columns.tolist()[section] | |
| except (IndexError, ): | |
| return QVariant() | |
| elif orientation == Qt.Vertical: | |
| try: | |
| return self._df.index.tolist()[section] | |
| except (IndexError, ): | |
| return QVariant() | |
| def flags(self, index): | |
| return Qt.ItemIsEnabled | Qt.ItemIsSelectable | Qt.ItemIsEditable | |
| def data(self, index, role=Qt.DisplayRole): | |
| if index.isValid(): | |
| if (role == Qt.EditRole): | |
| return self._df.values[index.row()][index.column()] | |
| elif (role == Qt.DisplayRole): | |
| return self._df.values[index.row()][index.column()] | |
| return None | |
| def setData(self, index, value, role): | |
| row = self._df.index[index.row()] | |
| col = self._df.columns[index.column()] | |
| self._df.values[row][col] = value | |
| self.dataChanged.emit(index, index) | |
| return True | |
| def rowCount(self, parent=QModelIndex()): | |
| return len(self._df.index) | |
| def columnCount(self, parent=QModelIndex()): | |
| return len(self._df.columns) | |
| def insertRows(self, position, rows=1, index=QModelIndex()): | |
| print ("\n\t\t ...insertRows() Starting position: '%s'"%position, 'with the total rows to be inserted: ', rows) | |
| indexSelected=self.index(position, 0) | |
| itemSelected=indexSelected.data() ###.toPyObject() | |
| self.beginInsertRows(QModelIndex(), position, position + rows - 1) | |
| self.endInsertRows() | |
| return True | |
| def sort(self, column, order): | |
| colname = self._df.columns.tolist()[column] | |
| self.layoutAboutToBeChanged.emit() | |
| self._df.sort_values(colname, ascending= order == Qt.AscendingOrder, inplace=True) | |
| self._df.reset_index(inplace=True, drop=True) | |
| self.layoutChanged.emit() | |
| def moveRows(self, parent, source_first, source_last, parent2, dest): | |
| self.beginMoveRows(parent, source_first, source_last, parent2, dest) | |
| self.data = self._df.values[index.row()][index.column()] | |
| self.endMoveRows() | |
| class Viewer(QMainWindow): | |
| def __init__(self, parent=None): | |
| super(Viewer, self).__init__(parent) | |
| self.setWindowIcon(QIcon.fromTheme("calc")) | |
| self.MaxRecentFiles = 5 | |
| self.windowList = [] | |
| self.recentFiles = [] | |
| self.settings = QSettings('Axel Schneider', 'QTableViewPandas') | |
| self.filename = "" | |
| self.hasHeaders = False | |
| self.setGeometry(0, 0, 800, 600) | |
| self.lb = QTableView() | |
| self.lb.verticalHeader().setVisible(True) | |
| self.model = PandasModel() | |
| self.lb.setModel(self.model) | |
| self.lb.setEditTriggers(QAbstractItemView.DoubleClicked) | |
| self.lb.setSelectionBehavior(QAbstractItemView.SelectRows) | |
| self.lb.setSelectionMode(QAbstractItemView.SingleSelection) | |
| self.lb.setDragDropMode(QAbstractItemView.InternalMove) | |
| self.lb.setDragDropOverwriteMode(False) | |
| self.lb.horizontalHeader().setStretchLastSection(False) | |
| self.lb.verticalHeader().setStretchLastSection(False) | |
| self.setStyleSheet(stylesheet(self)) | |
| self.lb.setAcceptDrops(True) | |
| self.setCentralWidget(self.lb) | |
| self.setContentsMargins(10, 10, 10, 10) | |
| self.createToolBar() | |
| self.readSettings() | |
| self.lb.setFocus() | |
| self.statusBar().showMessage("Ready", 0) | |
| def readSettings(self): | |
| print("reading settings") | |
| if self.settings.contains("geometry"): | |
| self.setGeometry(self.settings.value('geometry')) | |
| if self.settings.contains("recentFiles"): | |
| self.recentFiles = self.settings.value('recentFiles') | |
| self.lastFiles.addItem("last Files ...") | |
| self.recentFiles = list(dict.fromkeys(self.recentFiles)) | |
| print(self.recentFiles) | |
| if len(self.recentFiles) > 0: | |
| self.lastFiles.addItems(self.recentFiles[:15]) | |
| def saveSettings(self): | |
| print("saving settings") | |
| self.settings.setValue('geometry', self.geometry()) | |
| self.settings.setValue('recentFiles', self.recentFiles) | |
| def closeEvent(self, event): | |
| print(self.model.setChanged) | |
| if self.model.setChanged == True: | |
| print("is changed, saving?") | |
| quit_msg = "<b>The document was changed.<br>Do you want to save the changes?</ b>" | |
| reply = QMessageBox.question(self, 'Save Confirmation', | |
| quit_msg, QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes) | |
| if reply == QMessageBox.Yes: | |
| self.writeCSV_update() | |
| else: | |
| print("not saved, goodbye ...") | |
| return | |
| else: | |
| print("nothing changed. goodbye") | |
| self.saveSettings() | |
| def createToolBar(self): | |
| openAction = QAction(QIcon.fromTheme("document-open"), "Open", self, triggered=self.loadCSV, shortcut = QKeySequence.Open) | |
| saveAction = QAction(QIcon.fromTheme("document-save"), "Save", self, triggered= self.writeCSV_update, shortcut = QKeySequence.Save) | |
| saveAsAction = QAction(QIcon.fromTheme("document-save-as"), "Save as ...", self, triggered=self.writeCSV, shortcut = QKeySequence.SaveAs) | |
| self.tbar = self.addToolBar("File") | |
| self.tbar.setContextMenuPolicy(Qt.PreventContextMenu) | |
| self.tbar.setIconSize(QSize(16, 16)) | |
| self.tbar.setMovable(False) | |
| self.tbar.addAction(openAction) | |
| self.tbar.addAction(saveAction) | |
| self.tbar.addAction(saveAsAction) | |
| self.tbar.addSeparator() | |
| self.tbar.addAction(QIcon.fromTheme("add"), "insert row", self.insertRow) | |
| self.tbar.addSeparator() | |
| self.tbar.addAction(QIcon.fromTheme("edit"), "first row to headers", self.setHeadersToFirstRow) | |
| empty = QWidget() | |
| empty.setFixedWidth(10) | |
| self.tbar.addWidget(empty) | |
| self.lastFiles = QComboBox() | |
| self.lastFiles.setToolTip("recent Files") | |
| self.lastFiles.setFixedWidth(300) | |
| self.lastFiles.currentIndexChanged.connect(self.loadRecent) | |
| self.tbar.addWidget(self.lastFiles) | |
| empty = QWidget() | |
| empty.setFixedWidth(10) | |
| self.tbar.addWidget(empty) | |
| findbyText = QAction(QIcon.fromTheme("edit-find-symbolic"), "find", self, triggered = self.findInTable) | |
| self.lineFind = QLineEdit() | |
| self.lineFind.addAction(findbyText, 0) | |
| self.lineFind.setPlaceholderText("find") | |
| self.lineFind.setClearButtonEnabled(True) | |
| self.lineFind.setFixedWidth(250) | |
| self.lineFind.returnPressed.connect(self.findInTable) | |
| self.tbar.addWidget(self.lineFind) | |
| self.tbar.addAction(findbyText) | |
| empty = QWidget() | |
| empty.setFixedWidth(10) | |
| self.tbar.addWidget(empty) | |
| self.previewAction = QAction(QIcon.fromTheme("document-print-preview"), "Print Preview", self, triggered = self.handlePreview) | |
| self.tbar.addAction(self.previewAction) | |
| self.printAction = QAction(QIcon.fromTheme("document-print"), "Print", self, triggered = self.handlePrint) | |
| self.tbar.addAction(self.printAction) | |
| self.copyAction = QAction(QIcon.fromTheme("edit-copy"), "Copy Document", self, triggered = self.copyTable) | |
| self.tbar.addAction(self.copyAction) | |
| self.pasteAction = QAction(QIcon.fromTheme("edit-paste"), "Paste from Clipboard", self, triggered = self.pasteTable) | |
| self.tbar.addAction(self.pasteAction) | |
| def pasteTable(self): | |
| df = pd.read_clipboard(sep='\\s+', dtype=str, skip_blank_lines=True, header=None) | |
| self.df = df.replace(np.nan, '', regex=True) | |
| self.model = PandasModel(self.df) | |
| self.lb.setModel(main.model) | |
| self.lb.resizeColumnsToContents() | |
| self.lb.selectRow(0) | |
| self.statusBar().showMessage("clipboard loaded", 0) | |
| self.hasHeaders = False | |
| def copyTable(self): | |
| self.df.to_clipboard(excel=True, sep='\t') | |
| def insertRow(self): | |
| self.model.insertRows(0) | |
| def setHeadersToFirstRow(self): | |
| f = open(self.filename, 'r+b') | |
| with f: | |
| df = pd.read_csv(f, delimiter = '\t', keep_default_na = False, low_memory=False, header=0) | |
| f.close() | |
| self.model = PandasModel(df) | |
| self.lb.setModel(main.model) | |
| self.hasHeaders = True | |
| def loadRecent(self): | |
| if self.lastFiles.currentIndex() > 0: | |
| print(self.lastFiles.currentText()) | |
| print(self.model.setChanged) | |
| if self.model.setChanged == True: | |
| print("is changed, saving?") | |
| quit_msg = "<b>The document was changed.<br>Do you want to save the changes?</ b>" | |
| reply = QMessageBox.question(self, 'Save Confirmation', | |
| quit_msg, QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes) | |
| if reply == QMessageBox.Yes: | |
| self.openCSV(self.lastFiles.currentText()) | |
| else: | |
| self.openCSV(self.lastFiles.currentText()) | |
| else: | |
| self.openCSV(self.lastFiles.currentText()) | |
| self.filename = self.lastFiles.currentText() | |
| self.hasHeaders = False | |
| def openCSV(self, path): | |
| f = open(path, 'r+b') | |
| with f: | |
| self.df = pd.read_csv(f, delimiter = '\t', keep_default_na = False, low_memory=False, header=None) | |
| f.close() | |
| self.model = PandasModel(self.df) | |
| self.lb.setModel(main.model) | |
| self.lb.resizeColumnsToContents() | |
| self.lb.selectRow(0) | |
| self.statusBar().showMessage("%s %s" % (path, "loaded"), 0) | |
| self.filename = f | |
| self.hasHeaders = False | |
| def findInTable(self): | |
| self.lb.clearSelection() | |
| text = self.lineFind.text() | |
| model = self.lb.model() | |
| for column in range(self.model.columnCount()): | |
| start = model.index(0, column) | |
| matches = model.match(start, Qt.DisplayRole, text, -1, Qt.MatchContains) | |
| if matches: | |
| for index in matches: | |
| # print(index.row(), index.column()) | |
| self.lb.selectionModel().select(index, QItemSelectionModel.Select) | |
| def openFile(self, path=None): | |
| print(self.model.setChanged) | |
| if self.model.setChanged == True: | |
| print("is changed, saving?") | |
| quit_msg = "<b>The document was changed.<br>Do you want to save the changes?</ b>" | |
| reply = QMessageBox.question(self, 'Save Confirmation', | |
| quit_msg, QMessageBox.Yes | QMessageBox.No, QMessageBox.Yes) | |
| if reply == QMessageBox.Yes: | |
| self.writeCSV_update() | |
| else: | |
| print("not saved, loading ...") | |
| return | |
| path, _ = QFileDialog.getOpenFileName(self, "Open File", QDir.homePath() + "/Dokumente/CSV/","CSV Files (*.csv)") | |
| if path: | |
| return path | |
| def loadCSV(self): | |
| fileName = self.openFile() | |
| if fileName: | |
| self.recentFiles.insert(0, fileName) | |
| self.filename = fileName | |
| self.lastFiles.insertItem(1, fileName) | |
| self.hasHeaders = False | |
| self.lastFiles.setCurrentIndex(1) | |
| def writeCSV(self): | |
| fileName, _ = QFileDialog.getSaveFileName(self, "Open File", self.filename,"CSV Files (*.csv)") | |
| if fileName: | |
| print(fileName + " saved") | |
| f = open(fileName, 'w') | |
| newModel = self.model | |
| dataFrame = newModel._df.copy() | |
| if self.hasHeaders == False: | |
| dataFrame.to_csv(f, sep='\t', index = False, header = False) | |
| else: | |
| dataFrame.to_csv(f, sep='\t', index = False, header = True) | |
| self.lastFiles.insertItem(1, fileName) | |
| def writeCSV_update(self): | |
| if self.filename: | |
| f = open(self.filename, 'w') | |
| newModel = self.model | |
| dataFrame = newModel._df.copy() | |
| if self.hasHeaders == False: | |
| dataFrame.to_csv(f, sep='\t', index = False, header = False) | |
| else: | |
| dataFrame.to_csv(f, sep='\t', index = False, header = True) | |
| self.model.setChanged = False | |
| print("%s %s" % (self.filename, "saved")) | |
| self.statusBar().showMessage("%s %s" % (self.filename, "saved"), 0) | |
| def handlePrint(self): | |
| if self.model.rowCount() == 0: | |
| self.msg("no rows") | |
| else: | |
| dialog = QtPrintSupport.QPrintDialog() | |
| if dialog.exec_() == QDialog.Accepted: | |
| self.handlePaintRequest(dialog.printer()) | |
| print("Document printed") | |
| def handlePreview(self): | |
| if self.model.rowCount() == 0: | |
| self.msg("no rows") | |
| else: | |
| dialog = QtPrintSupport.QPrintPreviewDialog() | |
| dialog.setFixedSize(1000,700) | |
| dialog.paintRequested.connect(self.handlePaintRequest) | |
| dialog.exec_() | |
| print("Print Preview closed") | |
| def handlePaintRequest(self, printer): | |
| printer.setDocName(self.filename) | |
| document = QTextDocument() | |
| cursor = QTextCursor(document) | |
| model = self.lb.model() | |
| tableFormat = QTextTableFormat() | |
| tableFormat.setBorder(0.2) | |
| tableFormat.setBorderStyle(3) | |
| tableFormat.setCellSpacing(0); | |
| tableFormat.setTopMargin(0); | |
| tableFormat.setCellPadding(4) | |
| table = cursor.insertTable(model.rowCount() + 1, model.columnCount(), tableFormat) | |
| model = self.lb.model() | |
| ### get headers | |
| myheaders = [] | |
| for i in range(0, model.columnCount()): | |
| myheader = model.headerData(i, Qt.Horizontal) | |
| cursor.insertText(str(myheader)) | |
| cursor.movePosition(QTextCursor.NextCell) | |
| ### get cells | |
| for row in range(0, model.rowCount()): | |
| for col in range(0, model.columnCount()): | |
| index = model.index( row, col ) | |
| cursor.insertText(str(index.data())) | |
| cursor.movePosition(QTextCursor.NextCell) | |
| document.print_(printer) | |
| def stylesheet(self): | |
| return """ | |
| QMainWindow | |
| { | |
| background: qlineargradient(y1: 0, y2: 1, | |
| stop: 0 #E1E1E1, stop: 0.4 #DDDDDD, | |
| stop: 0.5 #D8D8D8, stop: 1.0 #D3D3D3); | |
| } | |
| QMenuBar | |
| { | |
| background: transparent; | |
| border: 0px; | |
| } | |
| QTableView | |
| { | |
| border: 1px outset #babdb6; | |
| border-radius: 0px; | |
| font-size: 8pt; | |
| background: transparent; | |
| margin-top: 8px; | |
| } | |
| QTableView::item:hover | |
| { | |
| color: #eeeeec; | |
| background: #c4a000;; | |
| } | |
| QTableView::item:selected | |
| { | |
| font-weight: bold; | |
| color: #F4F4F4; | |
| background: qlineargradient(y1:0, y2:1, | |
| stop:0 #729fcf, stop:1 #2a82da); | |
| } | |
| QTableView QTableCornerButton::section | |
| { | |
| background: qlineargradient( y1: 0, y2: 1, | |
| stop: 0 #E1E1E1, stop: 0.4 #DDDDDD, | |
| stop: 0.5 #D8D8D8, stop: 1.0 #D3D3D3); | |
| border: 1px solid #d3d7cf; | |
| } | |
| QHeaderView | |
| { | |
| background: qlineargradient( y1: 0, y2: 1, | |
| stop: 0 #E1E1E1, stop: 0.4 #DDDDDD, | |
| stop: 0.5 #D8D8D8, stop: 1.0 #D3D3D3); | |
| color: #888a85; | |
| } | |
| QToolBar | |
| { | |
| background: transparent; | |
| border: 0px; | |
| } | |
| QStatusBar | |
| { | |
| background: transparent; | |
| border: 0px; | |
| color: #555753; | |
| font-size: 7pt; | |
| } | |
| QToolTip { | |
| border: 1px solid darkkhaki; | |
| padding: 1px; | |
| border-radius: 3px; | |
| opacity: 255; | |
| font-size: 8pt; | |
| } | |
| """ | |
| if __name__ == "__main__": | |
| app = QApplication(sys.argv) | |
| main = Viewer() | |
| main.show() | |
| if len(sys.argv) > 1: | |
| main.openCSV(sys.argv[1]) | |
| sys.exit(app.exec_()) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment