Overlay Points from XLS onto part

[ackimberli21]

I have a quick question for you all. I have a part in Inventor that needs to be machined and instrumented. For the instrumentation locations, I have been given an Excel spreadsheet with XYZ coordinates. I need to be able to overlay the sensor locations onto the part purely for visualization purposes. Thus far, I have done this by taking the part file, creating a 3D sketch and importing the Excel sheet which places markers at all of the locations. This kind of works but I would like to do one of two things:

1) Change the style of the markers so that it more visible (ie change it to a large circle) or...

2) Place small spheres at each of the locations specified by the XLS file (this would be prefered because it shows up in renderings).

I have attached an example part file and XLS sheet for you all go play with.

Thanks in advance.

[wimann]

Unfortunately, I don't see that you've attached the XLS document. Nevertheless, if I were in your shoes, I would probably end up placing work points at each 3D sketch point the XLS document created, then I'd make an assembly for demonstration and have a sphere that is simply constrained to each work point. However, that would be my method and it's not something I've had to do before. There may be others on here who have more insight. iLogic may be a handy tool for what you're trying to do.

Hope this helps.

[ackimberli21]

For some reason it isn't letting me attach the XLS file so I will simply just post the coordinates here. Ideally, I would like to automate this because there are hundreds of sensor locations, all of which are subject to change.

0.5 0.5 0.0
0.5 0.0 0.5

0.5 0.5 1.0

0.5 1.0 0.5

0.0 0.5 0.5

1.0 0.5 0.5

0.0 0.0 0.0

1.0 0.0 0.0

1.0 1.0 0.0

0.0 1.0 0.0

0.0 0.0 1.0

1.0 0.0 1.0

1.0 1.0 1.0

0.0 1.0 1.0

[JDMather]

---

@ackimberli21 wrote:

1) Change the style of the markers so that it more visible (ie change it to a large circle) or...

2) Place small spheres at each of the locations specified by the XLS file (this would be prefered because it shows up in renderings).

---

1. You can increase the size of sketch points at Tools>Application Options> General tab Annotation scale.

You could toggle the Centerpoints to Points.

You could add Workpoints (the size of these are controlled the same as sketch points.

These will show up in Realistic visual style that you can screen capture.

2. I like this idea, but a bit more work.  Not sure how you would automate.

[wimann]

You may be able to embed the spreadsheet into your .ipt for this demonstration. My fix is kind of a work-a-round but I'm certain what you're trying to do can be done through iLogic/VBA. I'm just not savvy enough to tell you how 😕

Hopefully someone who is gets wind of this thread. I've seen a certain Cutris solve something similar to this before...

[ackimberli21]

This is by no means a final solution but I was able to find a Matlab library that creates DXF files which can contain primatives (spheres). Since I am far more familiar with MATLAB, I decided to take this approach. I import the coordinates from the Excel file into MATLAB, then use MATLAB to create the DXF file that contains all of the spheres located at each coordinate, and then import the DXF into Inventor. Although this does work, it is FAR from pretty =/

[ackimberli21]

If anyone is interested, I am making a GUI in Matlab that allows you to import a series of XYZ coordinates and then generates a single STL file that has spheres at each of the coordinates specified. So far, I only have it set up to where you can alter the size of the spheres but plan on upgrading it some more. So far I have done up to 1,500 points and it doesn't seem to have any issues.

[ackimberli21]

I have taken a few screenshots to demonstrate how this works...

First, generate an Excel sheet that contains the positions (XYZ) and sizes of the spheres that you want to generate. In this example, I have the coordinates of the corners of a pyramid where each sphere with a diameter of .1 in will be generated.

If you want to specify the size of each sphere individually, simply change the data in the 'D' column.

You have the flexibility to generate just about whatever you want at this point. For example, a bunch of random spheres at random locations...

Of course, this allows you to export all of the patterns at STL files which can then be used in assemblies. Each STL can be colored independantly so in my case, we can color certain sensors red and others blue, etc.

Finally, I was curious as to how many points this could handle before running into serious problems. I decided to generate 1500 spheres, each of which consists of roughly 400 points (sphere resolution^2) which results in a grand total of roughly 600,000 coordinates. It seemed to handle it with no issues even though the STL file was rather large (55 MB).

[ackimberli21]

In order for this EXE to function properly, the 2014 (or later) Matlab Runtime Engine must be installed on your machine. You can download the engine from the following link.

http://www.mathworks.com/products/compiler/mcr/

Since I cannot post EXEs on this forum, I have placed it in my Google Drive. You may download it here:

https://drive.google.com/file/d/0B7UpKwOd1J4NNVJxdFRJRnBwckU/edit?usp=sharing

I hope that someones finds this to be useful. Again, this will work for any CAD package that uses STL files. Enjoy!

[ackimberli21 · Accepted solution]

I was finally able to write a VBA script that will accomplish this. What is really nice about this is that it will allow you to render the spheres generated. Unforunately the previous solutions that I came up with wouldn't.

Build a CSV file with the XYZ and radius parameters in the first four columns like so:

-8.3608,-3.3438,2.7252,6.2777

9.105,7.8004,-14.16,2.9976

-0.3403,-4.7925,-10.162,3.362

-12.267,-14.213,-6.6875,7.5863

-3.4028,13.411,1.3214,5.9026

9.1006,-11.125,11.186,4.9798

-4.2857,0.40726,5.1367,0.018905

Then copy/paste/run the following code in the Inventor VBA environment.

Public Sub ClientGraphics3DPrimitives()
 Dim oPartDoc As PartDocument
    Set oPartDoc = ThisApplication.Documents.Add(kPartDocumentObject, _
    ThisApplication.FileManager.GetTemplateFile(kPartDocumentObject))

    ' Set a reference to the component definition.
    Dim oCompDef As PartComponentDefinition
    Set oCompDef = oPartDoc.ComponentDefinition

    ' Set a reference to the TransientBRep object.
    Dim oTransientBRep As TransientBRep
    Set oTransientBRep = ThisApplication.TransientBRep

    
    FileNum = FreeFile()
    'Open "C:\<path here>.csv" For Input As #FileNum
    
    Dim path As String

    path = InputBox("Enter a file path for the CSV file containing center points", "Prompt")
    Open path For Input As #FileNum

    Dim strArray() As String
    Dim x_coords(10000) As Double
    Dim y_coords(10000) As Double
    Dim z_coords(10000) As Double
    Dim R(10000) As Double
    Dim index As Integer
    index = 0
    
    While Not EOF(FileNum)
        Line Input #FileNum, dataLine
        strArray = Split(dataLine, ",")
        x_coords(index) = (CDbl(strArray(0)))
        y_coords(index) = (CDbl(strArray(1)))
        z_coords(index) = (CDbl(strArray(2)))
        R(index) = (CDbl(strArray(3)))
        
        Dim oPoint As Point
        Set oPoint = ThisApplication.TransientGeometry.CreatePoint(x_coords(index), y_coords(index), z_coords(index))
    
        ' Create the block.
        If index = 0 Then
            Dim oBody As SurfaceBody
            Set oBody = oTransientBRep.CreateSolidSphere(oPoint, R(index))
        Else
            Dim oBody2 As SurfaceBody
            Set oBody2 = oTransientBRep.CreateSolidSphere(oPoint, R(index))
            Call oTransientBRep.DoBoolean(oBody, oBody2, kBooleanTypeUnion)
        End If
        
        index = index + 1
    Wend
    
    Dim oBaseFeature As NonParametricBaseFeature
    Set oBaseFeature = oCompDef.Features.NonParametricBaseFeatures.Add(oBody)
    Close #FileNum
End Sub

[ackimberli21]

Once you get it figured out, you can make all sorts of interesteing patterns with your CSV file

[ackimberli21]

For some reason, whenever you import the coordinates, it applies a scale factor of .3937 (the conversion from cm to inches). Too fix this, just multiply the coordinates by 2.54 to offset the scale factor. I am sure that there are ways to fix this problem the correct way in the code but I am too lazy to figure it out. Until then, this will suffice...

        x_coords(index) = (CDbl(strArray(0)))*2.54
        y_coords(index) = (CDbl(strArray(1)))*2.54
        z_coords(index) = (CDbl(strArray(2)))*2.54
        R(index) = (CDbl(strArray(3)))*2.54