looping through Spreadsheet cells
Forum rules
Be nice to others! Respect the FreeCAD code of conduct!
Be nice to others! Respect the FreeCAD code of conduct!
looping through Spreadsheet cells
Hi everyone,
I want to loop through the cells of a spreadsheet, where coordinates are stored and create Draft points. I've spent quite a while looking for some code to reference spreadsheet cells and how to incorporate the loop variable into them, but haven't found anything. Would I have to create a string with something like this pseudocode: Cell."A" + convert2Text(LoopVariable)?
OS: macOS Mojave (10.14)
Word size of FreeCAD: 64-bit
Version: 0.20.26720 (Git)
Build type: Release
Python version: 3.9.9
Qt version: 5.12.9
Coin version: 4.0.0
OCC version: 7.5.3
Locale: C/Default (C)
I want to loop through the cells of a spreadsheet, where coordinates are stored and create Draft points. I've spent quite a while looking for some code to reference spreadsheet cells and how to incorporate the loop variable into them, but haven't found anything. Would I have to create a string with something like this pseudocode: Cell."A" + convert2Text(LoopVariable)?
OS: macOS Mojave (10.14)
Word size of FreeCAD: 64-bit
Version: 0.20.26720 (Git)
Build type: Release
Python version: 3.9.9
Qt version: 5.12.9
Coin version: 4.0.0
OCC version: 7.5.3
Locale: C/Default (C)
Re: looping through Spreadsheet cells
The word to help your search was 'iterate', have a look at https://forum.freecadweb.org/viewtopic.php?t=31053
Re: looping through Spreadsheet cells
This is what I have so far, but it doesn't draw any points. I already have a spreadsheet open with values in the columns A, B and C
I think the make_point command is wrong, but don't know what's wrong.
I think the make_point command is wrong, but don't know what's wrong.
Code: Select all
import FreeCAD as App
import Draft
import Spreadsheet
sheet = App.ActiveDocument.ActiveObject
for row in range(0, 1000):
cellx = "%s%s" % ("A", row)
celly = "%s%s" % ("B", row)
cellz = "%s%s" % ("C", row)
try:
x = sheet.get(cellx)
y = sheet.get(celly)
z = sheet.get(cellz)
App.ActiveDocument.Draft.make_point(x, y, z, color=(0.5, 0.3, 0.6), point_size=10)
except:
break
sheet.recompute()
Re: looping through Spreadsheet cells
indeed, if you try App.ActiveDocument.Draft.make_point(1,2,3) in the console it will throw an error Draft.make_point(1,2,3) will not...
the try/except silences that error, one reason to limit the use of try/except...
here is a version without try/except, and with points which is much faster if there is a decent amount of points (and you do not require links to the spreadsheet - which it seems like you do not based on your code so far...)
also, recomputing the spreadsheet is not the same as recomputing the document...
the try/except silences that error, one reason to limit the use of try/except...
here is a version without try/except, and with points which is much faster if there is a decent amount of points (and you do not require links to the spreadsheet - which it seems like you do not based on your code so far...)
also, recomputing the spreadsheet is not the same as recomputing the document...
Code: Select all
import Points
sheet = App.ActiveDocument.SpreadSheet
points = list()
ref = '{}{}'
for row in range(1, 100):
if hasattr(sheet, ref.format('A', row)):
points.append(tuple(sheet.get(ref.format(column, row))
for column in 'ABC'))
print(points)
p = Points.Points(points)
Points.show(p, 'Points')
pobj = App.ActiveDocument.Points
pobj.ViewObject.ShapeColor = (0.5, 0.3, 0.6)
pobj.ViewObject.PointSize = 5
App.ActiveDocument.recompute()
Re: looping through Spreadsheet cells
Hi Heda,
Thank you for that. I already have a spreadsheet open in my active Document with the label 'Spreadsheet' but the line in the code gives the error:
Traceback (most recent call last):
File "<input>", line 1, in <module>
AttributeError: 'App.Document' object has no attribute 'SpreadSheet'
In the comboview an object Points appears, but with seemingly no data in it. At least no points are visible on the screen. Sorry if this appears stupid, but I'm a bit out of my depth with python - I'm a beginner.
How difficult is it to adapt your code to make draft points? I need the points to be individually selectable at a later stage. there will be eventually about 150 points.
Thank you for that. I already have a spreadsheet open in my active Document with the label 'Spreadsheet' but the line in the code gives the error:
Traceback (most recent call last):
File "<input>", line 1, in <module>
AttributeError: 'App.Document' object has no attribute 'SpreadSheet'
In the comboview an object Points appears, but with seemingly no data in it. At least no points are visible on the screen. Sorry if this appears stupid, but I'm a bit out of my depth with python - I'm a beginner.
How difficult is it to adapt your code to make draft points? I need the points to be individually selectable at a later stage. there will be eventually about 150 points.
Re: looping through Spreadsheet cells
that line is about picking up the spreadsheet object...
so
if the error is AttributeError: 'App.Document' object has no attribute 'SpreadSheet',
is it not an "Active" missing, computers tend to be pretty picky about what things are called...
"an object Points appears"
right, that means that the code-execution has gone past the loop (which is weird if you claim an error before the loop), you can put in a "print(points)" after the loop to see if there is anything in the list...
"I need the points to be individually selectable"
they are with draft/snap
"How difficult is it to adapt your code to make draft points?"
not particularly hard, after the loop you have a list of points that you can use with a 2nd loop to make your draft points
or instead of appending, just make your draft points there...
printing things is your friend, just litter the code with print statements, then you can see what you have in the variables - which more often that people care to admit is not the same as one thinks...
combine that with working in the console to explore what is in an object...
so
Code: Select all
sheet = App.ActiveDocument.SpreadSheet
# is analogue to
sheet = App.ActiveDocument.getObject('Spreadsheet')
# of course only works if your actual name on that object is "Spreadsheet"
# (which is the default name for the first spreadsheet added)
# whereas the below will pick up whatever object is active, could be your spreadsheet or something else...
sheet = App.ActiveDocument.ActiveObject
is it not an "Active" missing, computers tend to be pretty picky about what things are called...
"an object Points appears"
right, that means that the code-execution has gone past the loop (which is weird if you claim an error before the loop), you can put in a "print(points)" after the loop to see if there is anything in the list...
"I need the points to be individually selectable"
they are with draft/snap
"How difficult is it to adapt your code to make draft points?"
not particularly hard, after the loop you have a list of points that you can use with a 2nd loop to make your draft points
or instead of appending, just make your draft points there...
printing things is your friend, just litter the code with print statements, then you can see what you have in the variables - which more often that people care to admit is not the same as one thinks...
combine that with working in the console to explore what is in an object...
Re: looping through Spreadsheet cells
What is the purpose of using Draft.
As example you could use Part.Vertex(Vector()) to make a "point" and alter the viewobject assigning a proper PointSize and PointColor properties, eventually grouping them in one or several Compound(s).
it is not too difficult.
Sadly I'm on mobile so no proper code for now.
Regards
Carlo D.
As example you could use Part.Vertex(Vector()) to make a "point" and alter the viewobject assigning a proper PointSize and PointColor properties, eventually grouping them in one or several Compound(s).
it is not too difficult.
Sadly I'm on mobile so no proper code for now.
Regards
Carlo D.
GitHub page: https://github.com/onekk/freecad-doc.
- In deep articles on FreeCAD.
- Learning how to model with scripting.
- Various other stuffs.
Blog: https://okkmkblog.wordpress.com/
- In deep articles on FreeCAD.
- Learning how to model with scripting.
- Various other stuffs.
Blog: https://okkmkblog.wordpress.com/
Re: looping through Spreadsheet cells
Thank you both very much for helping with this.
Something curious is going on, If I copy and paste your code into the python interpreter, I get the error message about AttributeError: 'App.Document' object has no attribute 'SpreadSheet' and points are not filled with data, the square brackets are empty after the Print(points) statement.
But then in the interpreter if I explicitly type the line: sheet = App.ActiveDocument.SpreadSheet
there is no error message and typing 'sheet' gives the response <Sheet object>
I then paste the whole code again, the error message still comes up for the line : sheet = App.ActiveDocument.SpreadSheet , but maybe because I explicitly typed that line in the interpreter beforehand the rest of the code works and points is filled with the data from my spreadsheet and points appear in the view.
Any idea what's going on? Why is typing explicitly, different to pasting the line in the interpreter? They look exactly the same.
Now that the points have appeared in the main view, How can I select them? Heda, you mentioned with Draft snap, but I can't select anything. I need to be able to select the points in the surface WB using the surface filling command, but the points generated with the code aren't selectable.
Many thanks again
Something curious is going on, If I copy and paste your code into the python interpreter, I get the error message about AttributeError: 'App.Document' object has no attribute 'SpreadSheet' and points are not filled with data, the square brackets are empty after the Print(points) statement.
But then in the interpreter if I explicitly type the line: sheet = App.ActiveDocument.SpreadSheet
there is no error message and typing 'sheet' gives the response <Sheet object>
I then paste the whole code again, the error message still comes up for the line : sheet = App.ActiveDocument.SpreadSheet , but maybe because I explicitly typed that line in the interpreter beforehand the rest of the code works and points is filled with the data from my spreadsheet and points appear in the view.
Code: Select all
>>> sheet = App.ActiveDocument.Spreadsheet
>>> sheet
<Sheet object>
>>>
>>> import Points
>>> sheet = App.ActiveDocument.SpreadSheet
Traceback (most recent call last):
File "<input>", line 1, in <module>
AttributeError: 'App.Document' object has no attribute 'SpreadSheet'
>>> points = list()
>>> ref = '{}{}'
>>> for row in range(1, 100):
... if hasattr(sheet, ref.format('A', row)):
... points.append(tuple(sheet.get(ref.format(column, row))for column in 'ABC'))
...
>>> print(points)
[(34, 3, 4), (6, 1, 7), (5, 5, 5), (8, 3, 2)]
>>> p = Points.Points(points)
>>> Points.show(p, 'Points')
>>> pobj = App.ActiveDocument.Points
>>> pobj.ViewObject.ShapeColor = (0.0, 0.0, 0.0)
>>> pobj.ViewObject.PointSize = 10
Now that the points have appeared in the main view, How can I select them? Heda, you mentioned with Draft snap, but I can't select anything. I need to be able to select the points in the surface WB using the surface filling command, but the points generated with the code aren't selectable.
Many thanks again
Re: looping through Spreadsheet cells
hm, typo from my side...
sheet = App.ActiveDocument.SpreadSheet
vs
sheet = App.ActiveDocument.Spreadsheet
with draft
you can of course do this with part-points as well as onekk brings up
sheet = App.ActiveDocument.SpreadSheet
vs
sheet = App.ActiveDocument.Spreadsheet
with draft
Code: Select all
import Draft
sheet = App.ActiveDocument.Spreadsheet
ref = '{}{}'
for row in range(1, 100):
if hasattr(sheet, ref.format('A', row)):
x, y, z = (sheet.get(ref.format(column, row)) for column in 'ABC')
Draft.make_point(x, y, z, color=(0.5, 0.3, 0.6), point_size=10)
App.ActiveDocument.recompute()
Re: looping through Spreadsheet cells
Thank you so much for your help. This is fantastic. I stared at those lines and didn't see the second capital S in SpreadSheet...
Thanks again
Albert
Thanks again
Albert