Spreadsheet module

Info about new community or project announcements, implemented features, classes, modules or APIs. Might get technical!
PLEASE DO NOT POST HELP REQUESTS OR OTHER DISCUSSIONS HERE!
User avatar
yorik
Site Admin
Posts: 11700
Joined: Tue Feb 17, 2009 9:16 pm
Location: São Paulo, Brazil
Contact:

Spreadsheet module

Postby yorik » Mon Aug 05, 2013 7:19 pm

The spreadsheet module is there! Still very basic, but it's working

Image

This module adds a new workbench with currently only one command, to create a new spreadsheet object in the document. That spreadsheet object can then be edited by entering editmode (I still couldn't find out why but double-clicking the object in the tree doesn't work at the moment, one must use menu Edit->Toggle edit mode).

In edit mode, a spreadsheet editor window opens, allowing you to change values of the cells. Changes are saved on the fly, so you can simply close the editor when finished. Currently the maximum size of the spreadsheet is limited to what you see in the editor (26 columns x 30 rows). What can be inserted into the cells (either by double-clicking them or by using the edit line above) are text strings, numbers or formulas.

Formulas must begin with a "=" character, and can be things like this:

Code: Select all

=a2*4
=( b4 * b5 ) + 5
= sin(c5) +4
Basically all the functions of the python math module (+ the python "abs" function) are available in a formula.

The spreadsheet object is also designed to be used as a full python object, without existence in the FreeCAD document. You can use it like this:

Code: Select all

>>> import Spreadsheet
>>> s=Spreadsheet.Spreadsheet()
>>> s
Spreadsheet object containing 0 cells
>>> s.a1 = "Hello, World!"
Setting key a1 to value Hello, World!
>>> s.a1
'Hello, World!'
>>> s
Spreadsheet object containing 1 cells
>>> s.a2 = "=4*4"
Setting key a2 to value =4*4
>>> s.a2
16
>>> s.b1 = 45
Setting key b1 to value 45
>>> s.b1
45
>>> s
Spreadsheet object containing 3 cells
As far as I tested all seems to work fine, so I guess it's time for you guys to test. It's simple, but I hope you'll like anyway!

Next things I pretend to do:
- Allow to use object properties in formulas (for example "=Objects.MyCube.Length")
- Create a new "Spreadsheet Controller" object, that can be inserted as a child in a Spreadsheet object, and that controls the contents of a series of cells. For example, it could gather the Volume property of all objects in the scene
- Import and export to some easy, basic formats (csv to start with, maybe others)
User avatar
jriegel
Site Admin
Posts: 3369
Joined: Sun Feb 15, 2009 5:29 pm
Location: Ulm, Germany
Contact:

Re: Spreadsheet module

Postby jriegel » Mon Aug 05, 2013 9:05 pm

Cool!!
Stop whining - start coding!
User avatar
psicofil
Posts: 154
Joined: Thu Dec 15, 2011 11:02 pm

Re: Spreadsheet module

Postby psicofil » Mon Aug 05, 2013 11:19 pm

Great yorik .. Would be great to also be able convinar the plot module to make graphics ;)
Ing. Gomez Lucio
Scope Ingenieria (scopeingenieria.com)
User avatar
yorik
Site Admin
Posts: 11700
Joined: Tue Feb 17, 2009 9:16 pm
Location: São Paulo, Brazil
Contact:

Re: Spreadsheet module

Postby yorik » Mon Aug 05, 2013 11:30 pm

psicofil wrote:Would be great to also be able convinar the plot module to make graphics ;)
Make graphics from a spreadsheet? How?
User avatar
jriegel
Site Admin
Posts: 3369
Joined: Sun Feb 15, 2009 5:29 pm
Location: Ulm, Germany
Contact:

Re: Spreadsheet module

Postby jriegel » Tue Aug 06, 2013 6:41 am

I will fore sure use it for a Assembly BOM! Also driving parametric designs!!
Stop whining - start coding!
User avatar
yorik
Site Admin
Posts: 11700
Joined: Tue Feb 17, 2009 9:16 pm
Location: São Paulo, Brazil
Contact:

Re: Spreadsheet module

Postby yorik » Tue Aug 06, 2013 1:16 pm

Yes, cells could also SET objects properties...
I must think a way to fill several cells at once now.. The ideal would be if you could do something like: for obj in DocObjects: cellcontents = obj.Shape.Volume, and that would fill a series of cells. But I'm not too sure how to do that in a nice way with standard object properties... Maybe just have a string property where you can type a python expression, like I used for formulas, and we would allow just a couple of python keywords like for or while. I find it cool that the formulas you can type in spreadsheet cells are actually python code... It feels very "integrated" with the rest of freecad
User avatar
jriegel
Site Admin
Posts: 3369
Joined: Sun Feb 15, 2009 5:29 pm
Location: Ulm, Germany
Contact:

Re: Spreadsheet module

Postby jriegel » Tue Aug 06, 2013 3:48 pm

But remember - no code execution from a loaded document!
You alway have to think someone makes a document with a cell: "from subprocess import call;call(['formate', 'C:'])"
Stop whining - start coding!
User avatar
yorik
Site Admin
Posts: 11700
Joined: Tue Feb 17, 2009 9:16 pm
Location: São Paulo, Brazil
Contact:

Re: Spreadsheet module

Postby yorik » Tue Aug 06, 2013 5:44 pm

Actually you can turn the eval() function in python pretty safe, giving it a dictionary of functions it can use, and banning all the rest, including all the builtins... That's what I did in the formulas, and it works well... You can basically use only a couple of functions from the math module, anything else raises an error. But this is only temporary, I'll write a custom parser later, to be on the safe side.

But in this case maybe it's not even needed, we might do something like 2 properties: one that stores an iteratable list (maybe simply a list of objects, but one might want to use this with something else than objects), and the other the thing to do with each item of the list... Then a property to set the starting cell, and another to define vertical or horizontal filling direction, and I think we'll have a powerful controller...
User avatar
yorik
Site Admin
Posts: 11700
Joined: Tue Feb 17, 2009 9:16 pm
Location: São Paulo, Brazil
Contact:

Re: Spreadsheet module

Postby yorik » Tue Aug 06, 2013 5:59 pm

Hmm I'm reading a bit further now and even if it is pretty safe it is too easy to make eval cause all kinds of crash. I'll remove it already...
User avatar
yorik
Site Admin
Posts: 11700
Joined: Tue Feb 17, 2009 9:16 pm
Location: São Paulo, Brazil
Contact:

Re: Spreadsheet module

Postby yorik » Tue Aug 06, 2013 7:03 pm

Ok I found a small math parser that does the job well... No more math functions at the moment, but at least it doesn't use eval anymore