Macro for Spreadsheet Alias setup and Part Family Generation

Need help, or want to share a macro? Post here!
hatari
Posts: 38
Joined: Fri Jul 22, 2016 10:56 am

Macro for Spreadsheet Alias setup and Part Family Generation

Postby hatari » Thu Nov 10, 2016 10:29 am

Hi,

since I like and use the Spreadsheet Workbench a lot for parametric modeling, I wrote a small script to accelerate the (quite annoying) task of setting aliases. I hope you find it useful. Since it's my first code it's very coarse, so I apologize in advance and I hope you have some suggestions to improve it!

You can find it on GitHub: https://github.com/tarihatari/FreeCAD_M ... Manager.py
aliasManager.png
aliasManager.png (12.77 KiB) Viewed 2864 times
It allows you to:
  • set aliases defined in column A to any other area of the Spreadsheet
  • clear aliases set somewhere in the Spreadsheet
  • move a range of aliases from one cell area to another
  • Generate a fully parametric Part Family, which is thus saved in the working directory
Workflow with new documents:
- Insert Spreadsheet
- Define the Parameters, in column A, values wherever you want
- Save, Close and reopen the Document
- Now run the macro

- Parameters must be defined in column A, values wherever you want
- In design tables, variant names must be in row 1, so it should look similar to the spreadsheet below:
design_table.png
design_table.png (46.96 KiB) Viewed 2864 times
- Using the "Move Alias" function you can switch to another configuration of your model by just typing in the source and target cell range, i.e. from B3-10 to C3-10

- "Generate Part Family" will run through all columns of the selected range, compute the model and save the configuration with a suffix defined in row 1. Thus your working directory will look like this, for instance:
folderview.png
folderview.png (94 KiB) Viewed 2864 times
Be aware that Generate Part Family starts defining aliases in the "range-from-cells", so when you call the function there should be either no aliases set or they should be in the same Cells as the range-from-cells.

I attached here two fully parametric FC models, a simple one (cube) to play with, and a more complex model of a DIN Part, to show what is possible.
The design table of the latter was created by simply copying the values from a webshop-website, formatting in excel and thus exporting to csv.
If the models are complex, it takes quite a long time to compute all the configurations (my PC is rather old, to save the 20 files shown in the screenshot it took about 15 minutes coffee drinking ;)).


Current limitations:
- works only for the first Spreadsheet inserted (not with, for instance, Spreadsheet001). I did not manage yet to set the current selected spreadsheet active for editing.
Attachments
cube.fcstd
(2.91 KiB) Downloaded 95 times
Sicherungsblech_DIN5406.fcstd
(42.49 KiB) Downloaded 97 times
Last edited by hatari on Fri Nov 11, 2016 12:18 pm, edited 1 time in total.
Jee-Bee
Posts: 1950
Joined: Tue Jun 16, 2015 10:32 am
Location: Netherlands

Re: Macro for Spreadsheet Alias setup and Part Family Generation

Postby Jee-Bee » Thu Nov 10, 2016 10:39 am

Nice work!!
freecad-heini-1
Posts: 5753
Joined: Tue Jan 07, 2014 11:10 am
Contact:

Re: Macro for Spreadsheet Alias setup and Part Family Generation

Postby freecad-heini-1 » Thu Nov 10, 2016 1:56 pm

Thank you so much, great work! :D
ulrich1a
Posts: 1872
Joined: Sun Jul 07, 2013 12:08 pm

Re: Macro for Spreadsheet Alias setup and Part Family Generation

Postby ulrich1a » Thu Nov 10, 2016 9:22 pm

Yes, I was thinking about a similar approach for some time, but did not have the time to program it. My idea was to make a workbench out of it, because I would need a command to create a spreadsheet based part. This command would link the geometry and the spreadsheet to a family part.
Then there is a need for a view provider for such spreadsheet based parts, that contains the select-field for the spreadsheet row and has the property for the spreadsheet name.

The spreadsheet would contain a row with the aliases used in the geometry. The view provider would copy the actual needed values into this row.
The other rows contain the variant values.
I would not generate the whole Part-family, as it makes a complex construction very heavy.
I did not work on it, so I do not know, what else is needed. Maybe code to look up, when the last instance of a part is removed from the document, so that the spreadsheet can be deleted as well.

The source code contains in the meantime a template for a python workbench: /src/Tools/_TEMPLATEPY_/
Examples for ViewProviders can be found here: http://www.freecadweb.org/wiki/index.ph ... ed_objects

Ulrich
User avatar
microelly2
Posts: 4354
Joined: Tue Nov 12, 2013 4:06 pm
Contact:

Re: Macro for Spreadsheet Alias setup and Part Family Generation

Postby microelly2 » Thu Nov 10, 2016 11:06 pm

seems to become an interesting thread 8-)
Jee-Bee
Posts: 1950
Joined: Tue Jun 16, 2015 10:32 am
Location: Netherlands

Re: Macro for Spreadsheet Alias setup and Part Family Generation

Postby Jee-Bee » Fri Nov 11, 2016 7:05 am

ulrich1a wrote:My idea was to make a workbench out of it, because I would need a command to create a spreadsheet based part. This command would link the geometry and the spreadsheet to a family part.
Why a different Workbench just as part of FreeCAD base... So accesble from every workbench! If that's not possible one of the basic/ default workbenches... part or maybe spreadsheet

in my opinion it are just 2 or 3 functions (create/edit family table; switch instance,...). Oke later on the same functions for assembly.
ulrich1a
Posts: 1872
Joined: Sun Jul 07, 2013 12:08 pm

Re: Macro for Spreadsheet Alias setup and Part Family Generation

Postby ulrich1a » Fri Nov 11, 2016 5:03 pm

Jee-Bee wrote:Why a different Workbench just as part of FreeCAD base
As it would contain in the beginning mainly python based commands, there are practical reasons for developing: It can live more or less independent for some time, until it works reliable.

In future it can be different. It may be just an option in another workbench. Or it could be merged with a part-library workbench like the one from yorik. Currently there exist bolts, the part-library from yorik and several macros generating useful parts. My future view is having them all integrated into one part-library-workbench that ships with FreeCAD. But it usually starts smaller.

Ulrich
Jee-Bee
Posts: 1950
Joined: Tue Jun 16, 2015 10:32 am
Location: Netherlands

Re: Macro for Spreadsheet Alias setup and Part Family Generation

Postby Jee-Bee » Fri Nov 11, 2016 5:46 pm

oke i understand that part a bit...
Still prefer more in main program but that takes time!!
ulrich1a
Posts: 1872
Joined: Sun Jul 07, 2013 12:08 pm

Re: Macro for Spreadsheet Alias setup and Part Family Generation

Postby ulrich1a » Tue Nov 15, 2016 10:34 pm

Have a look here for an alternative approach: viewtopic.php?f=10&t=18520

Ulrich
pablogil
Posts: 748
Joined: Wed Nov 26, 2014 3:19 pm
Location: Badajoz (Spain)
Contact:

Re: Macro for Spreadsheet Alias setup and Part Family Generation

Postby pablogil » Wed Nov 16, 2016 3:22 pm

Very handy!
I have designed a icon to quickly run it (creating a macro icon and then placing it into the workbench toolbar):
aliasManager_icon.png
aliasManager_icon.png (42.68 KiB) Viewed 2642 times
aliasManager.png
aliasManager.png (34.89 KiB) Viewed 2642 times
You can freely use it if you like it.

Some suggestions:
  • instead of different 3 popups for clearing/setting aliases it would be better to have just one popup (it would be still possible to quickly swap focus with TAB key
  • would it be possible to automatically clear aliases that previously exists? when you change aliases it's kind of slow to first clear and after set...
  • it would be handy if you set a "placeholder" for options so that is easy to guess how it works or just add a text string to the popup with instructions on how to format the entries
Thank you
Attachments
aliasManager.svg
(54.86 KiB) Downloaded 86 times
Dark and Light stylesheets v2.0 to theme your FreeCAD UI, more information here