Spreadsheet module

[yorik]

Hm interesting! Actually once we have the spreadsheet up, exporting it to several formats (the drawing module, save to files, etc...) should be easy.

One thing I'm still trying to figure out, is a good way to fill a spreadsheet automatically. For example, you want to gather the volume of a set of objects, stack this in the spreadsheet. Let's say it occupies 6 rows. Then, you add one object to the set, and it should update that block to 7 rows, and push all the rest 1 row down... I had thought about inserting "views" in the spreadsheet, that would have a (column, row) offset, like drawing views, but still not sure how they could stack, for example if 2 views occupy the same spot.

[zeroflop]

New to SF and just getting my feet wet. But I wanted to comment on the use of a spreadsheet feature.

First as mentioned a spreadsheet can be used to track assemblies for all sorts of meta data related to a part or assembly.
Part Numbers ( Part 1234)
Quantities ( 5 x screws in the assembly )
Descriptions ( 8-32 screw )
Material Name ( SS )
Material information ( Expansion coefficient etc. this can be useful if you want to eventually export to Finite element programs. )
If the spreadsheet is able to be customized by the user some may want to track the following with the part.
Source ( where to buy )
Cost ( to buy or machine)
etc

The most benefit would come from a spreadsheet that you can add default columns, but then also create custom columns. For example quantities may be a default that you can turn on which would be universal. But source might be a more general column that people might create and name.

The second useful thing about a spreadsheet is you can make a part and assign a variable to a few dimensions.
Then in the spread sheet you can create a table of those dimensions. Allowing you to draw one template part, and with a spreadsheet you can have all the similar parts.
Take for example a flat washer.

You could draw a flat washer with a fillet on one edge. Then create a table with ID, OD, thickness, fillet Then with one DWG and filling in the table you can create any size flat washer with or without a fillet (round = 0)

The power of this is pretty huge when assemblies come out imagine when you download SF you could also get:
All common screws, nuts, washers, springs and it's not just mechanical fasteners. but also EE chips that follow a set packaging format. 555 times, to mosfets Anything that might have predefined dimensions since it's draw once and fill in a table.

[yorik]

I am actually thinking in having 3 components:

1) a "host" spreadsheet object (that behaves like the Drawing page). It is simply a 2-dimension array, with cells that you can get/set manually, like A5 = "yorik", B6 = 34.9, print(C11) etc... (Just for the fun, a spreadsheet in python can be a dead simple thing: http://lateral.netmanagers.com.ar/weblo ... BB585.html )

2) "cell" objects (that behave like the Drawing views), that can span on several rows/columns, and that fill cells automatically. You could have for example views that retrieve one property of one specific object, or others that count all objects of a certain type, or gather a specific property of a certain class of objects, etc. The possibilities are endless, and they would be very easy to code.

3) A viewer to display the spreadsheet, based on a simple Qt table widget (something in the lines of http://doc.qt.nokia.com/4.7-snapshot/de ... sheet.html )

The main problem I haven't found a solution so far, is when you have such views that spread over several cells, and their size in not fixed (for example one that lists all the bolts, you might add more bolts after you created the spreadsheet), how do you deal with overlaps? For example you place a view object at A1 that spans over 5 rows. Then you place another one at A6. Then the first one changes to 10 rows. What happens to the second one? Okay, this case might be simple to solve (the second one would shift down as necessary), but it can quickly become very complex if there are more views inbetween.

Anyway, I should start a project page on the wiki...

[jrheinlaender]

it would provide the functionality that some people have been wanting for long (for ex. Link the width of a cube to 2x the height of a cone...)

The CAD programs I know (ProE, AD Inventor) provide this functionality by
a) Assigning an identifier to every parameter in the model (e.g. Sketch001:d1 would be dimension 1 in DocumentObject Sketch001)
b) Allowing the user to input a formula using these identifiers instead of a numeric value everywhere.

So in the example quoted above, I would find the name of (or click on) the parameter for the height of the cone (e.g. d7), and then input "2 * cone:d7" for the width of the cube. Since most of this kind of linking happens inside a sketch between different sketch entities, the prefix to the dimension ("cone:") will usually not be required.

IMHO regardless of whether FC has a spreadsheet module or not, using formulas instead of numeric values everywhere should be supported by FC.

[mrlukeparry]

it would provide the functionality that some people have been wanting for long (for ex. Link the width of a cube to 2x the height of a cone...)

The CAD programs I know (ProE, AD Inventor) provide this functionality by
a) Assigning an identifier to every parameter in the model (e.g. Sketch001:d1 would be dimension 1 in DocumentObject Sketch001)
b) Allowing the user to input a formula using these identifiers instead of a numeric value everywhere.

So in the example quoted above, I would find the name of (or click on) the parameter for the height of the cone (e.g. d7), and then input "2 * cone:d7" for the width of the cube. Since most of this kind of linking happens inside a sketch between different sketch entities, the prefix to the dimension ("cone:") will usually not be required.

IMHO regardless of whether FC has a spreadsheet module or not, using formulas instead of numeric values everywhere should be supported by FC.

I think this should be considered definitely when designing the UNITs framework whenever this gets achieved, because it will require every measurement / parameter to be changed in the source.

[jcc242]

Thanks for the example Normand. Indeed it's nothing too complicated. A spreadsheet viewer would be useful there, so one can tweak a resulting spreadsheet, add titles, etc... and then we could have a special drawing view feature that produces a svg fragment from it. So someone would not need to export anything, it all could be done inside freecad itself.

Did someone say SVG fragment? I was thinking I needed to have table support in gdtsvg.py anyway, to place BOMs and stuff on the drawing sheet. I haven't thought anything up yet on this, just vague ideas while I am working on other stuff. I'm totally open to guidance, ideas, code, etc.

I've used SolidWorks, Inventor, and AutoCAD's built in spreadsheets and feel that they are all garbage compared to working in a true spreadsheet program. If we do have in-house spreadsheet stuff, I would really appreciate at least being able to edit every table-like feature in a real spreadsheet program. Maybe even allow it to seamlessly "sync" (or update, if you prefer) with a CSV or ODF file.

[sanguinariojoe]

Hi Yorik!

I requested a merge of my plot module, that maybe can be used here.

Probably you want to see this discussion (the simple spreadsheet implementation is unsafe):
http://code.activestate.com/recipes/355045/

There are also approach with pyCell:
http://lateral.netmanagers.com.ar/weblo ... BB585.html

[microelly2]

It would be nice to extend the computational power of the spreadsheet modul.

Actually there are only the simples ops +-*/ available.
But sometime I need more powerful functions.

example: to get the radius of a circle given the area as input I need the square root function

I think about the possibility to create spreadsheet subobjects (I call them function container) which contain in a multi line field python code with a user defined function

these function should be callable from cells with numbers and cell references as parameters

=myfunction(4;a1;a2)

these concept is general enough so that everybody cann extend the power of a spreadsheet as a control centre to configure complex models.

[jmaustpc]

I think about the possibility to create spreadsheet subobjects (I call them function container) which contain in a multi line field python code with a user defined function

Wouldn't allowing general Python code to be embedded in a spreadsheet be a potential security issue? Unlike a macro, the spreadsheet data is saved into the fcstd file.

But even if I am correct about the security risk, you are right that we could use more power/functionality in the spreadsheet.

Perhaps another option might be to allow spreadsheet to read an external spreadsheet file ODS, (and remember the path to it) which would be edited in LibreOfiice or whatever... and then just have FreeCAD be able to push data to the ODS file and read back the data from the ODS file?

Would that make sense?

Jim

[yorik]

I also think we need to take care and restrict what can be done in the spreadsheet.
There is basically much space for improvement to the expression parser... But I think the attempt to a C++ version of the spreadsheet already had a more advanced parser... Someone would need to volunteer and pursue the work there. I could do that at some point, but it's pretty low in my priorities...