tools for the spreadsheet

Need help, or want to share a macro? Post here!
Forum rules
Be nice to others! Respect the FreeCAD code of conduct!
HoWil
Veteran
Posts: 1279
Joined: Sun Jun 14, 2015 7:31 pm
Location: Austria

tools for the spreadsheet

Post by HoWil »

Hi,
I use the spreadsheet more and more and am looking for simple tools like delete, copy and past of (multiple) cells.
It's there somewhere a hidden tool/macro-set? Did i miss something?
Br,
Howil
HoWil
Veteran
Posts: 1279
Joined: Sun Jun 14, 2015 7:31 pm
Location: Austria

Re: tools for the spreadsheet

Post by HoWil »

Thanks microelly2,
I am (only) a python guy. :D
From the little I know from c looks the examples nice and usable. Maybe wmayer or eivindkvedalen can reuse them.

I think of wiring a python macro in case they do not have the time.

I myself need copy, paste and delete of cells as well as putting marked cells wit plot wb.
Hope export will work again soon and i do not have to do it.

Any other suggestions?
Br,
Howil
HoWil
Veteran
Posts: 1279
Joined: Sun Jun 14, 2015 7:31 pm
Location: Austria

Re: tools for the spreadsheet

Post by HoWil »

Good afternoon,

I have stolen :D lots of code parts and produced a sort of fankenstein-Makro for copying, pasting and deleting spreadsheet cells.
Here is my undocumented ugly 8-) work:

Code: Select all

from PySide import QtGui, QtCore

import FreeCADGui
import FreeCAD
import FreeCAD as App


'''
*Working* Delete the selected area
*Working* Copy to clipboard
*Working* Paste from clipboard

TODO:
* (check if something is overwritten)
* shortcuts
* Test if pandas is installed and offer import export if available

* right click menu??
* Copy and paste alias ??
'''



# The following part ist from aliasManager_popup_HoWil.py see FreeCAD-forum
import string
# ===== Global variables ==============================================
alphabet_list = list(string.ascii_uppercase)

column_list = []
for i in range(0,26):
    column_list.append(alphabet_list[i])

for i in range(0,26):
    for j in range(0,26):
        column_list.append(alphabet_list[i] + alphabet_list[j])


class MyButtons(QtGui.QDialog):
    """"""
    def __init__(self):
        super(MyButtons, self).__init__()

        mw = FreeCADGui.getMainWindow()
        mdiarea = mw.findChild(QtGui.QMdiArea)

        subw = mdiarea.subWindowList()

        for i in subw:
            if i.widget().metaObject().className() == "SpreadsheetGui::SheetView":
                sheet = i.widget()
                table=sheet.findChild(QtGui.QTableView)
        ind = table.selectedIndexes()

        self.mw = mw
        self.sheet = sheet
        self.table = table
        self.table_widget = table
        self.ind = ind

        self.getSelection()

        FreeCAD.Console.PrintMessage("\nEnd of __init__")

        self.initUI(table)


    def initUI(self, table):
        FreeCAD.Console.PrintMessage(" initUI")

        option1Button = QtGui.QPushButton("Option 1, Delete selection")
        option1Button.clicked.connect(self.onOption1)
        option2Button = QtGui.QPushButton("Option 2, Past from clipboard")
        option2Button.clicked.connect(self.onOption2)
        option3Button = QtGui.QPushButton("Option 3, Copy to clipboard")
        option3Button.clicked.connect(self.onOption3)
        option4Button = QtGui.QPushButton("Option 4, Print selection")
        option4Button.clicked.connect(self.onOption4)
        option5Button = QtGui.QPushButton("Option 5, Close.")
        option5Button.clicked.connect(self.onOption5)
        #
        buttonBox = QtGui.QDialogButtonBox()
        buttonBox = QtGui.QDialogButtonBox(QtCore.Qt.Vertical)
        buttonBox.addButton(option1Button, QtGui.QDialogButtonBox.ActionRole)
        buttonBox.addButton(option2Button, QtGui.QDialogButtonBox.ActionRole)
        buttonBox.addButton(option3Button, QtGui.QDialogButtonBox.ActionRole)
        buttonBox.addButton(option4Button, QtGui.QDialogButtonBox.ActionRole)
        buttonBox.addButton(option5Button, QtGui.QDialogButtonBox.ActionRole)
        #
        mainLayout = QtGui.QVBoxLayout()
        mainLayout.addWidget(buttonBox)
        self.setLayout(mainLayout)
        # define window     xLoc,yLoc,xDim,yDim
        self.setGeometry(   250, 250, 0, 50)
        self.setWindowTitle("Pick a Button")


    def getSelection(self):
        ind = self.ind

        l_elements = len(ind)

        first_element = ind.__getitem__(0)
        fe_col = first_element.column()
        fe_row = first_element.row()
        fe_alphanum = column_list[fe_col] + str(fe_row+1)

        last_element = ind.__getitem__(l_elements-1)
        le_col = last_element.column()
        le_row = last_element.row()
        le_alphanum = column_list[le_col] + str(le_row+1)

        FreeCAD.Console.PrintMessage("\nThe first selected element is " + fe_alphanum)
        FreeCAD.Console.PrintMessage("\nThe last selected element is " + le_alphanum)

        self.fe_alphanum = fe_alphanum
        self.fe_col = fe_col # numeric representation of column, D=>3
        self.fe_row = fe_row # numeric representation of row

        self.le_alphanum = le_alphanum
        self.le_col = le_col
        self.le_row = le_row


    def onOption1(self):
        # Delete selection
        self.retStatus = 1
        FreeCAD.Console.PrintMessage("\nYou selected option one. Before table.")
        ind = self.ind
        FreeCAD.Console.PrintMessage("\nYou selected option one. After table.")

        FreeCAD.Console.PrintMessage("\nThe first selected element is " + self.fe_alphanum)
        FreeCAD.Console.PrintMessage("\nThe last selected element is " + self.le_alphanum)

        rows = range(self.fe_row, self.le_row+1)
        columns = range(self.fe_col, self.le_col+1)

        FreeCAD.Console.PrintMessage(rows)
        FreeCAD.Console.PrintMessage(columns)

        for row in rows:
            for column in columns:
                cell = column_list[column] + str(row+1)
                App.ActiveDocument.Spreadsheet.set(cell, str('') )
                App.activeDocument().recompute()

        FreeCAD.Console.PrintMessage("\nEnd onOption1\n")


    def onOption2(self):
        # Past from clipboard
        self.retStatus = 2
        FreeCAD.Console.PrintMessage("\nYou selected option two. ")

        clipboard = QtGui.QApplication.clipboard()

        from PySide.QtGui import *

        if clipboard.mimeData().hasText():
            cbtext = clipboard.mimeData().text()
            FreeCAD.Console.PrintMessage(cbtext)
            cbtext_split = [s.split('\t') for s in cbtext.splitlines()]

            index_col = self.fe_col

            for n_line, line in zip(range(len(cbtext_split)), cbtext_split):
                for n_word, col in zip(range(len(line)), line):
                    cell = column_list[index_col+n_word] + str(self.fe_row+n_line+1)
                    App.ActiveDocument.Spreadsheet.set(cell, str(col) )
                    App.activeDocument().recompute()

        else:
            clipboard.setText(tr("Cannot display data"))

        FreeCAD.Console.PrintMessage("\nYou selected option two.2 ")


    def onOption3(self):
        #Copy to clipboard
        self.retStatus = 3
        FreeCAD.Console.PrintMessage("\nYou selected option three. ")

        if len(self.ind) > 0:
            # sort select indexes into rows and columns
            previous = self.ind[0]
            columns = []
            rows = []

            for index in self.ind:
                if previous.column() != index.column():
                    columns.append(rows)
                    rows = []
                rows.append(index.data())
                previous = index

            columns.append(rows)
            cell_content = columns
            FreeCAD.Console.PrintMessage("\ncell_content: ")
            FreeCAD.Console.PrintMessage(cell_content)

            # add rows and columns to clipboard
            clipboard = ""
            nrows = len(cell_content[0])
            ncols = len(cell_content)
            for r in xrange(nrows):
                for c in xrange(ncols):
                    if cell_content[c][r] is not None:
                        clipboard += cell_content[c][r]
                    else:
                        clipboard += ''
                    if c != (ncols-1):
                        clipboard += '\t'
                clipboard += '\n'

            # copy to the system clipboard
            sys_clip = QtGui.QApplication.clipboard()
            sys_clip.setText(clipboard)
            FreeCAD.Console.PrintMessage('\nThe content of the clipboard is:')
            FreeCAD.Console.PrintMessage(clipboard)

        FreeCAD.Console.PrintMessage("\nYou selected option three. END")


    def onOption4(self):
        # Print selection
        self.retStatus = 4
        FreeCAD.Console.PrintMessage("\nYou selected option four. ")
        ind = self.ind

        FreeCAD.Console.PrintMessage("\nThe first selected element is " + self.fe_alphanum)
        FreeCAD.Console.PrintMessage("\nThe last selected element is " + self.le_alphanum)

        rows = range(self.fe_row, self.le_row+1)
        columns = range(self.fe_col, self.le_col+1)

        #~ FreeCAD.Console.PrintMessage(rows)
        #~ FreeCAD.Console.PrintMessage(columns)

        for row in rows:
            for column in columns:
                cell = column_list[column] + str(row+1)
                data = App.ActiveDocument.Spreadsheet.get(cell)
                FreeCAD.Console.PrintMessage("\n "+cell + " contains : " + str(data) )

                App.activeDocument().recompute()

        FreeCAD.Console.PrintMessage("\nEnd on Option4\n")

    def onOption5(self):
        self.retStatus = 5
        FreeCAD.Console.PrintMessage("\nYou selected option five. Closing.")
        self.close()


form = MyButtons()
form.exec_()

EDIT:
When using the macro one has to do a selection in the spreadsheet before. Than one can choose if he/she wants to copy/del/paste. After that the macro has to be closed and restarted because it is not possible to select a different cell region to for instance past the copied stuff. The dialog is always in front and blocks the interaction with the spreadsheet.

I want to know the following before I clean everything up. How do I make the dialog not blocking one for changing the selection in the spreadsheet?

Thanks in advance,
HoWil
ickby
Veteran
Posts: 3116
Joined: Wed Oct 05, 2011 7:36 am

Re: tools for the spreadsheet

Post by ickby »

To get a non-modal dialog, open it with show() rather than exec_().
HoWil
Veteran
Posts: 1279
Joined: Sun Jun 14, 2015 7:31 pm
Location: Austria

Re: tools for the spreadsheet

Post by HoWil »

ickby wrote:To get a non-modal dialog, open it with show() rather than exec_().
Thank you very much ickby. Worked like a charm! 8-)
HoWil
Veteran
Posts: 1279
Joined: Sun Jun 14, 2015 7:31 pm
Location: Austria

Re: tools for the spreadsheet

Post by HoWil »

So now let present the first version of Spreadsheet_tools:

Code: Select all

# ============================================================================================================
# ============================================================================================================
# ==                                                                                                        ==
# ==                                       Spreadsheet tools                                                ==
# ==                                                                                                        ==
# ============================================================================================================
# ============================================================================================================
# ABOUT
# ============================================================================================================
# version v1.0
# Macro developed for FreeCAD (http://www.freecadweb.org/).
# This macro helps managing cells inside FreeCAD Spreadsheet workbench. It is able to:
#        - Cut/delete a selected area of cells
#        - Copy a selected area of cells to clipboard
#        - Paste a selected area of cells from cliboard
# More information might be found on FreeCAD forums: http://forum.freecadweb.org/
#
#
# LICENSE
# ============================================================================================================
#
# This work is licensed under GNU Lesser General Public License (LGPL).
# To view a copy of this license, visit https://www.gnu.org/licenses/lgpl-3.0.html.
#
# ============================================================================================================
__title__   = "Spreadsheet_tools"
__author__  = "HoWil"
__version__ = "01.00"
__date__    = "2017-03-09"

__Comment__ = "This macro helps managing cells inside FreeCAD Spreadsheet workbench. It is able to cut/delete, copy and paste an area of cells."

__Status__ = "stable"
__Requires__ = "FreeCAD 0.17"

from PySide import QtGui, QtCore

import FreeCADGui
import FreeCAD
import FreeCAD as App

import string


'''
*Working* Cut/delete the selected area
*Working* Copy to clipboard
*Working* Paste from clipboard

TODO:
* Check if something is overwritten and show a warning.
* Use shortcuts like Ctrl-c.
* Test if pandas is installed and offer import-export if available.

* Right click menu??
* Copy and paste alias and formating???

Known limitations:
* Does work only with one opened FC-Spreadsheet.
* A cell has to be selected before selecting on of the options of Spreadsheet_tools.
'''




# ===== Global variables ==============================================
alphabet_list = list(string.ascii_uppercase)

column_list = [] # is filled with A, B, C,.... AA, AB, AC,...
for i in range(0,26):
    column_list.append(alphabet_list[i])

for i in range(0,26):
    for j in range(0,26):
        column_list.append(alphabet_list[i] + alphabet_list[j])


class Spreadsheet_Tools(QtGui.QDialog):
    """"""
    def __init__(self):
        super(Spreadsheet_Tools, self).__init__()

        self.init_UI()


    def init_UI(self):
        FreeCAD.Console.PrintMessage("init_UI")

        option1Button = QtGui.QPushButton("Cut/Delete selection")
        option1Button.clicked.connect(self.cut_delete_selection)
        option2Button = QtGui.QPushButton("Paste from clipboard")
        option2Button.clicked.connect(self.paste_from_clipboard)
        option3Button = QtGui.QPushButton("Copy to clipboard")
        option3Button.clicked.connect(self.copy_to_clipboard)
        option4Button = QtGui.QPushButton("Close this dialog")
        option4Button.clicked.connect(self.close_dialog)

        buttonBox = QtGui.QDialogButtonBox()
        buttonBox = QtGui.QDialogButtonBox(QtCore.Qt.Vertical)
        buttonBox.addButton(option1Button, QtGui.QDialogButtonBox.ActionRole)
        buttonBox.addButton(option2Button, QtGui.QDialogButtonBox.ActionRole)
        buttonBox.addButton(option3Button, QtGui.QDialogButtonBox.ActionRole)
        buttonBox.addButton(option4Button, QtGui.QDialogButtonBox.ActionRole)

        mainLayout = QtGui.QVBoxLayout()
        mainLayout.addWidget(buttonBox)
        self.setLayout(mainLayout)
        # define window     xLoc,yLoc,xDim,yDim
        self.setGeometry(   250, 250, 0, 50)
        self.setWindowTitle("Pick a Button")


    def get_selection(self):

        mw = FreeCADGui.getMainWindow()
        mdiarea = mw.findChild(QtGui.QMdiArea)

        subw = mdiarea.subWindowList()

        for i in subw:
            if i.widget().metaObject().className() == "SpreadsheetGui::SheetView":
                sheet = i.widget()

                table = sheet.findChild(QtGui.QTableView)
        ind = table.selectedIndexes()

        self.mw = mw    # mainwindow
        self.sheet = sheet  # spreadsheet
        self.table = table  # table
        self.ind = ind

        l_elements = len(ind)

        first_element = ind.__getitem__(0)
        fe_col = first_element.column()
        fe_row = first_element.row()
        fe_alphanum = column_list[fe_col] + str(fe_row+1)

        last_element = ind.__getitem__(l_elements-1)
        le_col = last_element.column()
        le_row = last_element.row()
        le_alphanum = column_list[le_col] + str(le_row+1)

        self.fe_alphanum = fe_alphanum
        self.fe_col = fe_col # numeric representation of column, D=>3
        self.fe_row = fe_row # numeric representation of row

        self.le_alphanum = le_alphanum
        self.le_col = le_col
        self.le_row = le_row


    def delete_selection(self):
        # Delete selection
        self.get_selection()

        ind = self.ind

        rows = range(self.fe_row, self.le_row+1)
        columns = range(self.fe_col, self.le_col+1)

        FreeCAD.Console.PrintMessage(rows)
        FreeCAD.Console.PrintMessage(columns)

        for row in rows:
            for column in columns:
                cell = column_list[column] + str(row+1)
                App.ActiveDocument.Spreadsheet.set(cell, str('') )
                App.activeDocument().recompute()

    def cut_delete_selection(self):
        # Cut selection
        self.copy_to_clipboard()
        self.delete_selection()


    def paste_from_clipboard(self):
        # Past from clipboard

        self.get_selection()

        clipboard = QtGui.QApplication.clipboard()

        from PySide.QtGui import *

        if clipboard.mimeData().hasText():
            cbtext = clipboard.mimeData().text()
            FreeCAD.Console.PrintMessage("\n" + cbtext)
            cbtext_split = [s.split('\t') for s in cbtext.splitlines()]

            index_col = self.fe_col

            for n_line, line in zip(range(len(cbtext_split)), cbtext_split):
                for n_word, col in zip(range(len(line)), line):
                    cell = column_list[index_col+n_word] + str(self.fe_row+n_line+1)
                    App.ActiveDocument.Spreadsheet.set(cell, str(col) )
                    App.activeDocument().recompute()

        else:
            clipboard.setText(tr("Cannot display data! No proper information stored in clipboard."))


    def copy_to_clipboard(self):
        # Copy to clipboard

        self.get_selection()

        if len(self.ind) > 0:
            # sort select indexes into rows and columns
            previous = self.ind[0]
            columns = []
            rows = []

            for index in self.ind:
                if previous.column() != index.column():
                    columns.append(rows)
                    rows = []
                rows.append(index.data())
                previous = index

            columns.append(rows)
            cell_content = columns
            FreeCAD.Console.PrintMessage("\ncell_content: ")
            FreeCAD.Console.PrintMessage(cell_content)

            # add rows and columns to clipboard
            clipboard = ""
            nrows = len(cell_content[0])
            ncols = len(cell_content)
            for r in xrange(nrows):
                for c in xrange(ncols):
                    if cell_content[c][r] is not None:
                        clipboard += cell_content[c][r]
                    else:
                        clipboard += ''
                    if c != (ncols-1):
                        clipboard += '\t'
                clipboard += '\n'

            # copy to the system clipboard
            sys_clip = QtGui.QApplication.clipboard()
            sys_clip.setText(clipboard)

    def close_dialog(self):
        self.close()


form = Spreadsheet_Tools()
form.show()

User avatar
Kunda1
Veteran
Posts: 13434
Joined: Thu Jan 05, 2017 9:03 pm

Re: tools for the spreadsheet

Post by Kunda1 »

Awesome!
FYI, here are some other feature requests for the spreadsheet JIC they aren't on your radar:
https://freecadweb.org/tracker/search.p ... preadsheet
Noteably: issue #2957 which was recently discussed in https://forum.freecadweb.org/viewtopic.php?f=22&t=21127
dprojects wrote:summoning dprojects
Alone you go faster. Together we go farther
Please mark thread [Solved]
Want to contribute back to FC? Checkout:
'good first issues' | Open TODOs and FIXMEs | How to Help FreeCAD | How to report Bugs
HoWil
Veteran
Posts: 1279
Joined: Sun Jun 14, 2015 7:31 pm
Location: Austria

Re: tools for the spreadsheet

Post by HoWil »

Here a short screencast for all interested...
fc_spreadsheet_tools.webm
(881.35 KiB) Downloaded 395 times
HoWil
Veteran
Posts: 1279
Joined: Sun Jun 14, 2015 7:31 pm
Location: Austria

Re: tools for the spreadsheet

Post by HoWil »

Kunda1 wrote:Awesome!
FYI, here are some other feature requests for the spreadsheet JIC they aren't on your radar:
https://freecadweb.org/tracker/search.p ... preadsheet
Noteably: issue #2957 which was recently discussed in https://forum.freecadweb.org/viewtopic.php?f=22&t=21127
dprojects wrote:summoning dprojects
Thank you Kunda1. I will have a look at these issues, but have to admit that most of the known requests are to high for me and my limited scripting abilities. Maybe the provided easy copy and past option to LibreOffice is helpful for someone.

BR,
HoWil
Post Reply