Macro for Spreadsheet Alias setup and Part Family Generation

HoWil · Post by **HoWil** » Sun Dec 04, 2016 5:03 pm

Dear hatari,
Nice work, very usefull!
Do you think it is possible to get column and row numbers directly from a selected region in the Spreadsheet?
For your cube-example does this mean that I would like to mark B2 to D4 in the drv-spreadsheet and run you script.
BR
HoWil

HoWil · Post by **HoWil** » Sun Dec 04, 2016 5:21 pm

Hi hatari,

Some additional comments:
line 118

Code: Select all

endCell =  QtGui.QInputDialog.getInteger(None, "End Cell Row", "Input Start Cell Row:")

should look like

Code: Select all

endCell =  QtGui.QInputDialog.getInteger(None, "End Cell Row", "Input End Cell Row:")

How about using 'Save a Copy' instead of using 'Save as' in 'Generate Part Family'?
This would have the benefit that one works always on the same file with the same filename, while the current script always changes the filename I am working on which is a bit confusing.

Maybe something like a 'auto' mode would help saving time:
The script looks up which row in A is the first without input and defines the variable range accordingly. After that runs the variation loop until the variation name in the first row is empty.

THX

hatari · Post by **hatari** » Sun Dec 04, 2016 7:28 pm

Thanks for your thoughts, HoWil!

HoWil wrote: Do you think it is possible to get column and row numbers directly from a selected region in the Spreadsheet?

I'm still a very beginner in this and don't have a deep insight in the FreeCAD architechture yet, but I guess this is possible and it's actually on my to do list. However, I'm a bit short of time at the moment, so I didn't figure out how to implement it, yet...

HoWil wrote: Some additional comments:
line 118
Code: Select all
endCell =  QtGui.QInputDialog.getInteger(None, "End Cell Row", "Input Start Cell Row:")
should look like
Code: Select all
endCell =  QtGui.QInputDialog.getInteger(None, "End Cell Row", "Input End Cell Row:")
How about using 'Save a Copy' instead of using 'Save as' in 'Generate Part Family'?
This would have the benefit that one works always on the same file with the same filename, while the current script always changes the filename I am working on which is a bit confusing.

Thanks, it's now fixed!

HoWil wrote: Maybe something like a 'auto' mode would help saving time:
The script looks up which row in A is the first without input and defines the variable range accordingly. After that runs the variation loop until the variation name in the first row is empty.

Yeah, that's also a good idea. So if there's other content besides the Part Family data in the spreadsheet, it's enough to just separate it from the rest with an empty row/column.

HoWil · Post by **HoWil** » Sun Dec 04, 2016 9:23 pm

Dear hatari,

I adopted your code a bit and 'implemented' the automatically mode as 'routine5' as I described before.
I changed also the saveCopy part and the text for the QInputDialog.
The implementation is a bit rough since I am also no computer scientist nor a software-engineer, but it works

and maybe it helps others:

Code: Select all

# -*- coding: utf-8 -*-
''' Script for setting, moving and clearing aliases in the Spreadsheet.
    It allows to generate Part Families

    hatari 2016 v0.2

    GNU Lesser General Public License (LGPL)
'''
from PySide import QtGui, QtCore
import os

class MyButtons(QtGui.QDialog):
	""""""
	def __init__(self):
		super(MyButtons, self).__init__()
		self.initUI()
	def initUI(self):
		option1Button = QtGui.QPushButton("Set Aliases")
		option1Button.clicked.connect(self.onOption1)
		option2Button = QtGui.QPushButton("Clear Aliases")
		option2Button.clicked.connect(self.onOption2)
		option3Button = QtGui.QPushButton("Move Aliases")
		option3Button.clicked.connect(self.onOption3)
  		option4Button = QtGui.QPushButton("Generate Part Family")
		option4Button.clicked.connect(self.onOption4)
  		option5Button = QtGui.QPushButton("Generate Part Family automatically")
		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(400, 400, 300, 50)
		self.setWindowTitle("Alias Manager")
		self.setWindowFlags(QtCore.Qt.WindowStaysOnTopHint)
  
	def onOption1(self):
		self.retStatus = 1
		self.close()
	def onOption2(self):
		self.retStatus = 2
		self.close()
	def onOption3(self):
		self.retStatus = 3
		self.close()
  	def onOption4(self):
		self.retStatus = 4
		self.close()
  	def onOption5(self):
		self.retStatus = 5
		self.close()
  
  
# Define Aliases
def routine1():
    column = QtGui.QInputDialog.getText(None, "Column containing Values", "Enter Column Letter")
    if column[1]:
        col = str.capitalize(str(column[0])) # Always use capital characters for Spreadsheet
        startCell =  QtGui.QInputDialog.getInteger(None, "start Row number", "Input start Row number:")
        if startCell[1]:
            endCell =  QtGui.QInputDialog.getInteger(None, "end Row number", "Input end Row number:")
            if endCell[1]:
                for i in range(startCell[0],endCell[0]+1):
                    cellFrom = 'A' + str(i)
                    cellTo = str(col[0]) + str(i)
                    App.ActiveDocument.Spreadsheet.setAlias(cellTo, '')
                    App.ActiveDocument.recompute()
                    App.ActiveDocument.Spreadsheet.setAlias(cellTo, App.ActiveDocument.Spreadsheet.getContents(cellFrom))
# Clear Aliases
def routine2():
        column = QtGui.QInputDialog.getText(None, "Column containing Values", "Enter Column Letter")
        if column[1]:
            col = str.capitalize(str(column[0]))
            startCell =  QtGui.QInputDialog.getInteger(None, "start Row number", "Input start Row number:")
            if startCell[1]:
                endCell =  QtGui.QInputDialog.getInteger(None, "end Row number", "Input end Row number:")
                if endCell[1]:
                    for i in range(startCell[0],endCell[0]+1):
                        cellTo = str(col[0]) + str(i)
                        App.ActiveDocument.Spreadsheet.setAlias(cellTo, '')
                        App.ActiveDocument.recompute()
# Move Aliases
def routine3(columnFrom=None, columnTo=None, startCell=None, endCell=None):
    columnFrom = QtGui.QInputDialog.getText(None, "Value Column", "Move From")
    if columnFrom[1]:
        columnTo = QtGui.QInputDialog.getText(None, "Value Column", "Move To")
        if columnTo[1]:
            colF = str.capitalize(str(columnFrom[0]))
            colT = str.capitalize(str(columnTo[0]))
            startCell =  QtGui.QInputDialog.getInteger(None, "start Row number", "Input start Row number:")
            if startCell[1]:
                endCell =  QtGui.QInputDialog.getInteger(None, "end Row number", "Input end Row number:")
                if endCell[1]:
                    for i in range(startCell[0],endCell[0]+1):
                        cellDef = 'A'+ str(i)                        
                        cellFrom = str(colF[0]) + str(i)
                        cellTo = str(colT[0]) + str(i)
                        App.ActiveDocument.Spreadsheet.setAlias(cellFrom, '')
                        App.ActiveDocument.recompute()
                        App.ActiveDocument.Spreadsheet.setAlias(cellTo, App.ActiveDocument.Spreadsheet.getContents(cellDef))


# Generate Part Family
def routine4():
    # Get Filename
    doc = FreeCAD.ActiveDocument    
    if not doc.FileName:
        FreeCAD.Console.PrintError('Must save project first\n')
        
    docDir, docFilename = os.path.split(doc.FileName)
    filePrefix = os.path.splitext(docFilename)[0]

    def char_range(c1, c2):
        """Generates the characters from `c1` to `c2`, inclusive."""
        for c in xrange(ord(c1), ord(c2)+1):
            yield str.capitalize(chr(c))
    columnFrom = QtGui.QInputDialog.getText(None, "Column", "Range From")
    if columnFrom[1]:
        columnTo = QtGui.QInputDialog.getText(None, "Column", "Range To")
        if columnTo[1]:
            startCell =  QtGui.QInputDialog.getInteger(None, "Start Cell Row", "Input Start Cell Row:")
            if startCell[1]:
                endCell =  QtGui.QInputDialog.getInteger(None, "End Cell Row", "Input End Cell Row:")
                if endCell[1]:    
                    fam_range = []
                    for c in char_range(str(columnFrom[0]), str(columnTo[0])):
                        fam_range.append(c)
                    for index in range(len(fam_range)-1):
                        for i in range(startCell[0],endCell[0]+1):
                            cellDef = 'A'+ str(i)                        
                            cellFrom = str(fam_range[index]) + str(i)
                            cellTo = str(fam_range[index+1]) + str(i)
                            App.ActiveDocument.Spreadsheet.setAlias(cellFrom, '')
                            App.ActiveDocument.recompute()
                            App.ActiveDocument.Spreadsheet.setAlias(cellTo, App.ActiveDocument.Spreadsheet.getContents(cellDef))
                            App.ActiveDocument.recompute()
                            sfx = str(fam_range[index+1]) + '1'
                        suffix = App.ActiveDocument.Spreadsheet.getContents(sfx)
                    
                        filename = filePrefix + '_' + suffix + '.fcstd'
                        filePath = os.path.join(docDir, filename)
                    
                        FreeCAD.Console.PrintMessage("Saving view to %s\n" % filePath)
#                        App.getDocument(filePrefix).saveAs(filePath)
                        App.getDocument(filePrefix).saveCopy(filePath)
                        
                        


# Generate Part Family automatically
def routine5():
    # Get Filename
    doc = FreeCAD.ActiveDocument    
    if not doc.FileName:
        FreeCAD.Console.PrintError('Must save project first\n')
        
    docDir, docFilename = os.path.split(doc.FileName)
    filePrefix = os.path.splitext(docFilename)[0]

    def char_range(c1, c2):
        """Generates the characters from `c1` to `c2`, inclusive."""
        for c in xrange(ord(c1), ord(c2)+1):
            yield str.capitalize(chr(c))
                               
    fam_range = []
#                    
    # Walk through all columns in row 1 and test if the cell is empty        
    values_present = True
    curr_column = 1 # column 1 or A is for alias definition
    
    while values_present and curr_column<=26: # crashes for columns like AA or AB
        curr_column += 1

        cellDef = str.capitalize(chr(65-1+curr_column)) + '1' # the integer representation of the char 'A' is 65
        try:
            if App.ActiveDocument.Spreadsheet.getContents(cellDef)=='':
                values_present = False
        except:
            values_present = False
    
    columnFrom = []
    columnFrom.append(str.capitalize(chr(65-1+1)) )
    columnTo = []
    columnTo.append(str.capitalize(chr(65-1+curr_column-1)) )
    
    
    # Walk through all rows in column A and test if the cell is empty
    values_present = True
    curr_row = 1
    
    while values_present:
        curr_row += 1

        cellDef = 'A' + str(curr_row) 
        try:
            if App.ActiveDocument.Spreadsheet.getContents(cellDef)=='':
                values_present = False
        except:
            values_present = False

    startCell = []
    startCell.append(2)
    endCell = []
    endCell.append(curr_row)
    
    
    # Generate all variants
    for c in char_range(str(columnFrom[0]), str(columnTo[0])): # !!! This does not include columns like AA or AB !!!!
        fam_range.append(c)

    for index in range(len(fam_range)-1):
        for i in range(startCell[0],endCell[0]+1):
            cellDef = 'A'+ str(i)                        
            cellFrom = str(fam_range[index]) + str(i)
            cellTo = str(fam_range[index+1]) + str(i)
            App.ActiveDocument.Spreadsheet.setAlias(cellFrom, '')
            App.ActiveDocument.recompute()
            App.ActiveDocument.Spreadsheet.setAlias(cellTo, App.ActiveDocument.Spreadsheet.getContents(cellDef))
            App.ActiveDocument.recompute()
            sfx = str(fam_range[index+1]) + '1'
        suffix = App.ActiveDocument.Spreadsheet.getContents(sfx)
    
        filename = filePrefix + '_' + suffix + '.fcstd'
        filePath = os.path.join(docDir, filename)
    
        FreeCAD.Console.PrintMessage("Saving view to %s\n" % filePath)
#       App.getDocument(filePrefix).saveAs(filePath)
        App.getDocument(filePrefix).saveCopy(filePath)
    
    
    # Move the alias back to column B
    colF = str.capitalize(str(columnTo[0]))
    colT = 'B'
    for i in range(startCell[0],endCell[0]+1):
        cellDef = 'A'+ str(i)                        
        cellFrom = str(colF[0]) + str(i)
        cellTo = str(colT[0]) + str(i)
        App.ActiveDocument.Spreadsheet.setAlias(cellFrom, '')
        App.ActiveDocument.recompute()
        App.ActiveDocument.Spreadsheet.setAlias(cellTo, App.ActiveDocument.Spreadsheet.getContents(cellDef))


form = MyButtons()
form.exec_()
#try:
if form.retStatus==1:
    routine1()
elif form.retStatus==2:
    routine2()
elif form.retStatus==3:
    routine3()
elif form.retStatus==4:
    routine4()
elif form.retStatus==5:
    routine5()
#except:
#    pass

pablogil · Post by **pablogil** » Mon Dec 05, 2016 5:44 pm

Hi all,

I've been working on improving this macro for a while. I'm not an experienced coder but as I found it very useful and I have used it for a while now, I wanted to improve it in order to speed up the workflow and solving some issues and inconsistencies I found in the @hatari original macro.

I have talked with @hatari and we decided to release both (original and this improved version) under LGPL licence so that you all can enjoy it and, if you feel like, try to improve it even more.
Some of the improvements are:

the GUI is now a popup window from where quickly inset the requested information
now the rows/columns range start and ends at the correct cell
is possible to run the command and rerun it without having to close the popup window
little fixes and improvements to the original code

So, I'm sharing with you my first release, you can download it at GitHub: https://github.com/pgilfernandez/FreeCAD_AliasManager

: screenshot.png (108.97 KiB) Viewed 2634 times

I have also included an icon in case you want to add the macro to the toolbar with a unique icon:

: aliasManager_icon.png (42.68 KiB) Viewed 2634 times

I hope you like it

Cheers

triplus · Post by **triplus** » Mon Dec 05, 2016 10:38 pm

Didn't test it yet as i guess i must first grasp the concept a bit better.

Just wanted to say if you will in addition add Macro to the FreeCAD Wiki more users will be able to find it.

pablogil · Post by **pablogil** » Mon Dec 05, 2016 11:00 pm

triplus wrote:Didn't test it yet as i guess i must first grasp the concept a bit better.

Just wanted to say if you will in addition add Macro to the FreeCAD Wiki more users will be able to find it.

Thank you triplus, I will do it when I have a new spare time

pablogil · Post by **pablogil** » Tue Dec 06, 2016 1:35 pm

pablogil wrote:
triplus wrote:Didn't test it yet as i guess i must first grasp the concept a bit better.

Just wanted to say if you will in addition add Macro to the FreeCAD Wiki more users will be able to find it.
Thank you triplus, I will do it when I have a new spare time

Done

triplus · Post by **triplus** » Tue Dec 06, 2016 1:40 pm

Great.

P.S. And could you share one example on how you use it? For me to follow the procedure and see how it goes.

pablogil · Post by **pablogil** » Tue Dec 06, 2016 3:41 pm

triplus wrote:Great.

P.S. And could you share one example on how you use it? For me to follow the procedure and see how it goes.

It basically does the same as the original tahari macro, read the first thread post and you will know how to use it and even a pair of examples to play with

Macro for Spreadsheet Alias setup and Part Family Generation

Re: Macro for Spreadsheet Alias setup and Part Family Generation

Re: Macro for Spreadsheet Alias setup and Part Family Generation

Re: Macro for Spreadsheet Alias setup and Part Family Generation

Re: Macro for Spreadsheet Alias setup and Part Family Generation

Re: Macro for Spreadsheet Alias setup and Part Family Generation

Re: Macro for Spreadsheet Alias setup and Part Family Generation

Re: Macro for Spreadsheet Alias setup and Part Family Generation

Re: Macro for Spreadsheet Alias setup and Part Family Generation

Re: Macro for Spreadsheet Alias setup and Part Family Generation

Re: Macro for Spreadsheet Alias setup and Part Family Generation