looping through Spreadsheet cells

[acp693]

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)

[Syres]

The word to help your search was 'iterate', have a look at https://forum.freecadweb.org/viewtopic.php?t=31053

[acp693]

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.

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()

[heda]

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...

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()

[acp693]

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.

[heda]

that line is about picking up the spreadsheet 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

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...

[onekk]

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.

[acp693]

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.

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

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

[heda]

hm, typo from my side...
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()

you can of course do this with part-points as well as onekk brings up

[acp693]

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