tools for the spreadsheet
Forum rules
Be nice to others! Respect the FreeCAD code of conduct!
Be nice to others! Respect the FreeCAD code of conduct!
tools for the spreadsheet
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
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
- microelly2
- Veteran
- Posts: 4688
- Joined: Tue Nov 12, 2013 4:06 pm
- Contact:
Re: tools for the spreadsheet
Thanks microelly2,
I am (only) a python guy.
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
I am (only) a python guy.
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
Re: tools for the spreadsheet
Good afternoon,
I have stolen lots of code parts and produced a sort of fankenstein-Makro for copying, pasting and deleting spreadsheet cells.
Here is my undocumented ugly work:
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
I have stolen lots of code parts and produced a sort of fankenstein-Makro for copying, pasting and deleting spreadsheet cells.
Here is my undocumented ugly 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_()
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
Re: tools for the spreadsheet
To get a non-modal dialog, open it with show() rather than exec_().
Re: tools for the spreadsheet
Thank you very much ickby. Worked like a charm!ickby wrote:To get a non-modal dialog, open it with show() rather than exec_().
Re: tools for the spreadsheet
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()
Re: tools for the spreadsheet
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
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
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
Re: tools for the spreadsheet
Here a short screencast for all interested...
Re: tools for the spreadsheet
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.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
BR,
HoWil