Python Forum
[PyQt] How to Copy-Paste a table from Office apps to QTableView?
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
[PyQt] How to Copy-Paste a table from Office apps to QTableView?
#1
Hi there,
I’m new in Python 3 & PyQt5 and I was faced with a problem that stunned me with its ruthlessness. I assume that my inexperience in the Python is the root cause but nevertheless…

Task
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. I studied some elementary examples on the internet, but my task is a little bit more complicated. It's needed to copy square made of some values that are surrounded by empty cells on all sides:

   

Actually, this selection should be copied together with both numbers and empty cells.

What has been tried
Ctrl+C Ctrl+V don’t work, so I tried more complicated ways listed below

QtWidget.QApplication.clipboard().text()
I started with the simplest - text content of QtWidget.QApplication.clipboard() and found a complete mayhem that depends on the source of a table:
Output:
MS Office Excel: '\t\t\n\t1\t2\n\t3\t4\n' LibreOffice Calc: '1\t2\n3\t4\n' MS Office Word: '\t\t\t\n\t1\t2\t\n\t3\t4\t\n\t\t\t\n' LibreOffice Writer: '\n\n\n\n\n1\n2\n\n\n3\n4\n\n\n\n\n\n'
QtWidget.QApplication.clipboard().text("html")
Getting the html-content of the clipboard, in principle, solved the problem, but in a very clumsy way. HTML tagged text had to be carefully cleaned from any garbage tags, which Word produced especially much. Long, complicated, no certainty that with office program upgrades I will not have to rewrite the algorithm.

pandas.read_clipboard(header=None)
I thought that pandas could help me, but found that output of pandas.read_clipboard(header=None) is as absurd as QtWidget.QApplication.clipboard().text(): output:

Output:
MS Excel: 2 0 1 NaN NaN NaN 1.0 2.0 3.0 4.0 LibreOffice Calc: 0 1 0 1 2 1 3 4 MS Word: 3 0 1 2 NaN NaN NaN NaN 1.0 2.0 NaN 3.0 4.0 NaN NaN NaN NaN LibreOffice Writer: 0 0 1 1 2 2 3 3 4
Question
Could you help me with the right solution of this problem? Smile
Reply
#2
You want to insert the clipboard in the selected row?
Reply
#3
(Aug-04-2021, 03:45 PM)Axel_Erfurt Wrote: You want to insert the clipboard in the selected row?

Thank you for the reply. Not exactly.
I just want to copy a part of some table (from Excel, Word etc.) and paste it in my program in QTableView as it is - with values and empty cells. It's a very common action in terms of data analysis (when you take some data from Ecxel and put it in some special software). I'm really surprized that I cannot make this in the snap of a finger.
Reply
#4
I use this to copy from LibreOffice to QTableView

pd is pandas
np is numpy
lb is QTableView

    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
Reply
#5
(Aug-04-2021, 05:23 PM)Axel_Erfurt Wrote: pd is pandas
np is numpy
lb is QTableView
And what is self?
Reply
#6
self is the main window (QMainWindow)

This is the full code, The paste button is on the right side of the toolbar.

#!/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_())
Reply


Possibly Related Threads…
Thread Author Replies Views Last Post
  [PyQt] PyQt5 QTableView SQLite : edit cell HeinKurz 2 2,162 Mar-27-2023, 10:41 AM
Last Post: HeinKurz
  [PyQt] QStyledItemDelegate and QTableView malonn 1 1,553 Feb-07-2023, 07:15 PM
Last Post: malonn
  [PyQt] QTableView set header labels HeinKurz 2 5,983 Jan-23-2023, 08:46 AM
Last Post: HeinKurz
  [PyQt] Determine whether text in QTableView cell is fully visible or not random_nick 0 929 Oct-27-2022, 09:29 PM
Last Post: random_nick
  [PyQt] QTableView: scroll to top cell of the screen random_nick 2 2,681 Oct-08-2022, 12:29 AM
Last Post: random_nick
  [PyQt] [Solved]Add a Blank Row To QTableView Extra 3 5,124 Oct-02-2022, 04:53 PM
Last Post: Extra
  How to update the list of a combo box in a QTableView panoss 10 6,001 Feb-05-2022, 03:24 PM
Last Post: panoss
  [Tkinter] Override the paste function(copy from excel file - paste in separate tkinter entryes) AndreiV 3 4,468 Jun-05-2020, 04:46 PM
Last Post: AndreiV
  [PyQt] Qtableview adapte size to WBPYTHON 3 10,884 Mar-23-2020, 01:51 AM
Last Post: deanhystad
  [PyGUI] Showing text in QTableView sequence 0 3,007 Jan-20-2019, 05:00 PM
Last Post: sequence

Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020