Assign Attributes From Excel

I don't have a journal that does exactly what you want. The closest one I have currently will read an excel file and change the color of each component based on info in the Excel file. It does this by first reading the Excel file and creating some lists for the component name and color info; it then processes the components in the assembly, searching in the list for the component name. If found, it applies the specified color to the component. Perhaps the technique will help with attribute info instead of color info.

There is a link to an eng-tips thread at the beginning of the code, that will provide more background on the problem. Basically, the user had the component name in column A and a desired color (cell fill color) in column B.

'NXJournaling.com
'May 27, 2014
'journal to change color of component based on fill color of excel cell
'http://www.eng-tips.com/viewthread.cfm?qid=365135

Option Strict Off
Imports System
Imports System.Collections.Generic
Imports System.Windows.Forms

Imports NXOpen
Imports NXOpen.Assemblies
Imports NXOpen.UF
Imports NXOpenUI

Module Module1

Dim theSession As Session = Session.GetSession()
Dim theUfSession As UFSession = UFSession.GetUFSession()
Dim theUISession As UI = UI.GetUI

Sub Main()

If IsNothing(theSession.Parts.Work) Then
'active part required
Return
End If

Dim workPart As Part = theSession.Parts.Work
Dim displayPart As Part = theSession.Parts.Display
Dim lw As ListingWindow = theSession.ListingWindow
lw.Open()

Dim markId1 As Session.UndoMarkId
markId1 = theSession.SetUndoMark(Session.MarkVisibility.Visible, "Component color from Excel")

Dim excelBom As String = ChooseBomFile()
If excelBom = "" Then
'user pressed cancel
Return
End If

Dim myAsmInfo As New NXJ_Assembly_info
myAsmInfo.Part = displayPart

Dim myCompColors As New List(Of NXJ_Comp_Color)

'create list of parts and colors from excel
'http://excelribbon.tips.net/T010180_Determining_the_RGB_Value_of_a_Color.html

BuildColorList(excelBom, myCompColors)

'sort the color list by component name
myCompColors.Sort(AddressOf CompareCompNames)

For Each myComp As Assemblies.Component In myAsmInfo.AllComponents

For Each myCColor As NXJ_Comp_Color In myCompColors
If myComp.Prototype.OwningPart.Leaf = myCColor.CompName Then
'component found, change color
ChangeComponentColor(myComp, myCColor.ColorR, myCColor.ColorG, myCColor.ColorB)
End If
Next

Next

lw.Close()

End Sub

Function ChooseBomFile() As String

Dim fdlg As OpenFileDialog = New OpenFileDialog()
fdlg.Title = "Select Excel BOM file"
Dim dir As String
dir = System.Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments)
'dir = "C:\temp"
fdlg.InitialDirectory = dir
fdlg.Filter = "Excel Files(*.xls;*.xlsx;*.xlsm)|*.xls;*.xlsx;*.xlsm"
fdlg.FilterIndex = 2
fdlg.RestoreDirectory = True
If fdlg.ShowDialog() = DialogResult.OK Then
Return fdlg.FileName
Else
Return ""
End If

End Function

Sub BuildColorList(ByVal excelFileName As String, ByRef compList As List(Of NXJ_Comp_Color))

'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
'starting row and column for component part numbers
Dim row As Long = 1
Dim column As Long = 1
'which column holds the color info? (how many columns over from the part number?)
Const colorColumnOffset As Long = 1
'%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

'create Excel object
Dim objExcel = CreateObject("Excel.Application")
If objExcel Is Nothing Then
theUISession.NXMessageBox.Show("Error", NXMessageBox.DialogType.Error, "Could not start Excel, journal exiting")
'theSession.UndoToMark(markId1, "journal")
Exit Sub
End If

'open excel file
Dim objWorkbook = objExcel.Workbooks.Open(excelFileName)
objExcel.visible = True

objWorkbook.activesheet.cells(row, column).select()

Dim lastRow As Long

Const xlUp As Integer = -4162
Const xlNone As Integer = -4142

lastRow = objWorkbook.ActiveSheet.Cells(objWorkbook.activesheet.Rows.Count, column).End(xlUp).Row
'MsgBox("last row: " & lastRow.ToString)

Do Until row > lastRow
'MsgBox("cell value: " & objWorkbook.activesheet.cells(row, column).value)

If objWorkbook.activesheet.cells(row, column + colorColumnOffset).interior.colorindex <> xlNone Then
Dim C As Long
Dim R As Long
Dim G As Long
Dim B As Long
Dim Rc As Double
Dim Gc As Double
Dim Bc As Double

C = objWorkbook.activesheet.cells(row, column + colorColumnOffset).Interior.Color
R = C Mod 256
G = C \ 256 Mod 256
B = C \ 65536 Mod 256

Rc = R / 256
Gc = G / 256
Bc = B / 256

'MsgBox("cell color: " & Rc.ToString & ", " & Gc.ToString & ", " & Bc.ToString)

Dim myComp As New NXJ_Comp_Color(objWorkbook.activesheet.cells(row, column).value, Rc, Gc, Bc)

If Not compList.Contains(myComp) Then
compList.Add(myComp)
End If

Else
'no fill color, skip
End If

row += 1
Loop

objExcel.Quit()
objWorkbook = Nothing
objExcel = Nothing

End Sub

Private Function CompareCompNames(ByVal x As NXJ_Comp_Color, ByVal y As NXJ_Comp_Color) As Integer

'case-insensitive sort
Dim myStringComp As StringComparer = StringComparer.CurrentCultureIgnoreCase

'for a case-sensitive sort (A-Z then a-z), change the above option to:
'Dim myStringComp As StringComparer = StringComparer.CurrentCulture

Return myStringComp.Compare(x.CompName, y.CompName)

End Function

Private Sub ChangeComponentColor(ByVal comp As Assemblies.Component, ByVal red As Double, ByVal green As Double, ByVal blue As Double)

'find closest NX color in display part color table
Dim colorValues(2) As Double
colorValues(0) = red
colorValues(1) = green
colorValues(2) = blue

Dim NXcolor As Integer
theUfSession.Disp.AskClosestColor(UFConstants.UF_DISP_rgb_model, colorValues, UFConstants.UF_DISP_CCM_EUCLIDEAN_DISTANCE, NXcolor)

'MsgBox("NX color index: " & NXcolor.ToString)

Dim displayModification1 As DisplayModification
displayModification1 = theSession.DisplayManager.NewDisplayModification()

displayModification1.ApplyToAllFaces = True

displayModification1.ApplyToOwningParts = False

displayModification1.NewColor = NXcolor

Dim objects1(0) As DisplayableObject
objects1(0) = comp
displayModification1.Apply(objects1)

displayModification1.Dispose()

End Sub

Public Function GetUnloadOption(ByVal dummy As String) As Integer

'Unloads the image when the NX session terminates
GetUnloadOption = NXOpen.Session.LibraryUnloadOption.AtTermination

'----Other unload options-------
'Unloads the image immediately after execution within NX
'GetUnloadOption = NXOpen.Session.LibraryUnloadOption.Immediately

'Unloads the image explicitly, via an unload dialog
'GetUnloadOption = NXOpen.Session.LibraryUnloadOption.Explicitly
'-------------------------------

End Function

End Module

Public Class NXJ_Comp_Color

Private _compName As String
Public Property CompName() As String
Get
Return _compName
End Get
Set(ByVal value As String)
_compName = value
End Set
End Property

Private _colorR As Double
Public Property ColorR() As Double
Get
Return _colorR
End Get
Set(ByVal value As Double)
_colorR = value
End Set
End Property

Private _colorG As Double
Public Property ColorG() As Double
Get
Return _colorG
End Get
Set(ByVal value As Double)
_colorG = value
End Set
End Property

Private _colorB As Double
Public Property ColorB() As Double
Get
Return _colorB
End Get
Set(ByVal value As Double)
_colorB = value
End Set
End Property

Public Sub New()
_compName = ""
_colorR = 0
_colorG = 0
_colorB = 0
End Sub

Public Sub New(ByVal name As String, ByVal red As Double, ByVal green As Double, ByVal blue As Double)
_compName = name
_colorR = red
_colorG = green
_colorB = blue
End Sub

End Class

Public Class NXJ_Assembly_info

#Region "Private Variables"

Private Const Version As String = "0.1.1"

Private _theSession As Session = Session.GetSession()
Private _theUfSession As UFSession = UFSession.GetUFSession

Private _components As New List(Of Assemblies.Component)
Private _uniqueParts As New List(Of Part)
Private _allComponents As New List(Of Assemblies.Component)
Private _allUniqueParts As New List(Of Part)
Private _notLoaded As New List(Of String)

Private lg As LogFile = _theSession.LogFile

#End Region

#Region "Properties"

Private _isTCRunning As Boolean
Public ReadOnly Property IsTCRunning() As Boolean
Get
Return _isTCRunning
End Get
End Property

Private _thePart As Part = Nothing
Public Property Part() As Part
Get
Return _thePart
End Get
Set(ByVal value As Part)
_thePart = value
'Me.GetInfo()
Me.GetAllInfo()
End Set
End Property

Public ReadOnly Property AllComponents() As List(Of Component)
Get
Return _allComponents
End Get
End Property

Public ReadOnly Property AllUniqueParts() As List(Of Part)
Get
Return _allUniqueParts
End Get
End Property

Public ReadOnly Property Components As List(Of Component)
Get
Return _components
End Get
End Property

Public ReadOnly Property UniqueParts As List(Of Part)
Get
Return _uniqueParts
End Get
End Property

Public ReadOnly Property NotLoaded As List(Of String)
Get
Return _notLoaded
End Get
End Property

#End Region

Public Sub New()

lg.WriteLine("")
lg.WriteLine("~ NXJournaling.com: NXJ_Assembly_info object created ~")
lg.WriteLine(" ~~ Version: " & Version & " ~~")
lg.WriteLine(" ~~ Timestamp of run: " & DateTime.Now.ToString & " ~~")
lg.WriteLine("NXJ_Assembly_info Sub New()")

'determine if we are running under TC or native
_theUfSession.UF.IsUgmanagerActive(_isTCRunning)
lg.WriteLine("IsTcRunning: " & _isTCRunning.ToString)

lg.WriteLine("exiting Sub New")
lg.WriteLine("")

End Sub

Private Sub GetAllInfo()

'get all component info from assembly (all levels)
lg.WriteLine("Sub GetAllInfo()")

Try
Dim c As ComponentAssembly = Part.ComponentAssembly
If Not IsNothing(c.RootComponent) Then
'*** insert code to process 'root component' (assembly file)
lg.WriteLine(" part has components")
'*** end of code to process root component
lg.WriteLine(" calling GetAllComponentChildren")
GetAllComponentChildren(c.RootComponent)
Else
'*** insert code to process piece part, part has no components
lg.WriteLine(" part has no components")
End If
Catch ex As NXException
lg.WriteLine("Sub GetAllInfo error: " & ex.ErrorCode)
lg.WriteLine(" " & ex.Message)
End Try

lg.WriteLine("exiting Sub GetAllInfo()")

End Sub

Private Sub GetAllComponentChildren(ByVal comp As Component)

For Each child As Component In comp.GetChildren()
lg.WriteLine(child.DisplayName)
'*** insert code to process component or subassembly

If Me.LoadComponent(child) Then

_allComponents.Add(child)

Dim tempPart As Part = child.Prototype.OwningPart
If Not _allUniqueParts.Contains(tempPart) Then
_allUniqueParts.Add(tempPart)
End If

Else
'component could not be loaded
End If
'*** end of code to process component or subassembly
If child.GetChildren.Length <> 0 Then
'*** this is a subassembly, add code specific to subassemblies

'*** end of code to process subassembly
Else
'this component has no children (it is a leaf node)
'add any code specific to bottom level components

End If
Me.GetAllComponentChildren(child)
Next
End Sub

Private Sub GetInfo()

'get top level component info from assembly (no recursion)
lg.WriteLine("Sub GetInfo()")

Try
Dim c As ComponentAssembly = Part.ComponentAssembly
If Not IsNothing(c.RootComponent) Then
'*** insert code to process 'root component' (assembly file)
lg.WriteLine(" part has components")
'*** end of code to process root component
lg.WriteLine(" calling GetComponentChildren")
Me.GetComponentChildren(c.RootComponent)
Else
'*** insert code to process piece part, part has no components
lg.WriteLine(" part has no components")
End If
Catch ex As NXException
lg.WriteLine("Sub GetInfo error: " & ex.ErrorCode)
lg.WriteLine(" " & ex.Message)
End Try

lg.WriteLine("exiting GetInfo()")

End Sub

Private Sub GetComponentChildren(ByVal comp As Component)

For Each child As Component In comp.GetChildren()
'*** insert code to process component or subassembly
_components.Add(child)
Dim tempPart As Part = child.Prototype.OwningPart
If Not _uniqueParts.Contains(tempPart) Then
_uniqueParts.Add(tempPart)
End If
'*** end of code to process component or subassembly
If child.GetChildren.Length <> 0 Then
'*** this is a subassembly, add code specific to subassemblies

'*** end of code to process subassembly
Else
'this component has no children (it is a leaf node)
'add any code specific to bottom level components

End If
Next
End Sub

Private Function LoadComponent(ByVal theComponent As Component) As Boolean

lg.WriteLine("Sub LoadComponent()")

Dim thePart As Part = theComponent.Prototype.OwningPart

Dim partName As String = ""
Dim refsetName As String = ""
Dim instanceName As String = ""
Dim origin(2) As Double
Dim csysMatrix(8) As Double
Dim transform(3, 3) As Double

Try
If thePart.IsFullyLoaded Then
'component is fully loaded
Else
'component is partially loaded
End If
lg.WriteLine(" component: " & theComponent.DisplayName & " is already partially or fully loaded")
lg.WriteLine(" return: True")
lg.WriteLine("exiting Sub LoadComponent()")
lg.WriteLine("")
Return True
Catch ex As NullReferenceException
'component is not loaded
Try
lg.WriteLine(" component not loaded, retrieving part information")
_theUfSession.Assem.AskComponentData(theComponent.Tag, partName, refsetName, instanceName, origin, csysMatrix, transform)
lg.WriteLine(" component part file: " & partName)

Dim theLoadStatus As PartLoadStatus
_theSession.Parts.Open(partName, theLoadStatus)

If theLoadStatus.NumberUnloadedParts > 0 Then
If theLoadStatus.NumberUnloadedParts > 1 Then
lg.WriteLine(" problem loading " & theLoadStatus.NumberUnloadedParts.ToString & " components")
Else
lg.WriteLine(" problem loading 1 component")
End If

Dim allReadOnly As Boolean = True
For i As Integer = 0 To theLoadStatus.NumberUnloadedParts - 1
lg.WriteLine("part name: " & theLoadStatus.GetPartName(i))
lg.WriteLine("part status: " & theLoadStatus.GetStatus(i))
If theLoadStatus.GetStatus(i) = 641058 Then
'read-only warning, file loaded ok
Else
'641044: file not found
allReadOnly = False
If Not _notLoaded.Contains(partName) Then
_notLoaded.Add(partName)
End If
End If
lg.WriteLine("status description: " & theLoadStatus.GetStatusDescription(i))
lg.WriteLine("")
Next
If allReadOnly Then
lg.WriteLine(" 'read-only' warnings only")
lg.WriteLine(" return: True")
Return True
Else
'warnings other than read-only...
lg.WriteLine(" return: False")
lg.WriteLine("exiting Sub LoadComponent()")
lg.WriteLine("")
Return False
End If
Else
lg.WriteLine(" component(s) loaded successfully")
lg.WriteLine(" return: True")
lg.WriteLine("exiting Sub LoadComponent()")
lg.WriteLine("")
Return True
End If

Catch ex2 As NXException
lg.WriteLine(" Load error: " & ex2.Message)
lg.WriteLine(" error code: " & ex2.ErrorCode)
lg.WriteLine(" return: False")
lg.WriteLine("exiting Sub LoadComponent()")
lg.WriteLine("")
If ex2.Message.ToLower = "file not found" Then
If Not _notLoaded.Contains(partName) Then
_notLoaded.Add(partName)
End If
End If
Return False
End Try
Catch ex As NXException
'unexpected error
lg.WriteLine(" Error in Sub LoadComponent: " & ex.Message)
lg.WriteLine(" return: False")
lg.WriteLine("exiting Sub LoadComponent()")
lg.WriteLine("")
Return False
End Try

End Function

End Class

How to Importing Attributes from Excel to NX part file