Ilogic Sort BOM

[marco_bonacina5834P]

Hi everyone,
I'm trying to export the BOM to an Excel file with this rule,

AddReference "Microsoft.Office.Interop.Excel.dll"
Imports Microsoft.Office.Interop
Imports Microsoft.Office.Interop.Excel
Sub Main()
'Clear DebugView
Logger.Info("iLogic: DBVIEWCLEAR")


'Declaretions
Dim oAssyDoc As AssemblyDocument
oAssyDoc = ThisApplication.ActiveDocument

Dim oAssyCompDef As AssemblyComponentDefinition
oAssyCompDef = oAssyDoc.ComponentDefinition

'Get path of current document
Dim oPath As String = ThisDoc.Path

'Get BOM from Assembly
Dim oBOM As BOM
oBOM = oAssyCompDef.BOM
Logger.Info("oBOM is set.")

Dim oBOMView As BOMView
oBOMView = oBOM.BOMViews.Item("Strutturata")
'oBOMView = oBOMViews.Item("Parts Only")


oBOMView.Sort("Part Number", 1)
oBOMView.Renumber(1, 1)


Logger.Trace("oBOMView is set.")

oBOM.StructuredViewEnabled = True
'oBOM.PartsOnlyViewEnabled = True

oBOM.StructuredViewFirstLevelOnly = False




Logger.Info("iLogic: BOM_Export Set oBOMView")



'BOM Export

'Copy Job Information Spreadsheet from workspace
sJobBOMTempName = "C:\Users\marco.bonacina\Desktop\Distinta.xlsm"
Dim sJobBOMName As String = String.Concat(oPath,"\","ilogic.xlsm")
Logger.Trace("sJobBOMName: " & sJobBOMName)

Try
IO.File.Copy(sJobBOMTempName, sJobBOMName, True)
Catch ex As Exception
MessageBox.Show(ex.Message)


Return
End Try

Dim sMySheet As String = "NotaOfficina"
Static iCurrRow As Integer = 22

GoExcel.DisplayAlerts = False
GoExcel.Open(sJobBOMName, sMySheet)
	
'Write BOM values to Excel
Call QueryBOMRowProperties(oBOMView.BOMRows, sJobBOMName, iCurrRow)
oAssyDoc.Save

'Save Excel
GoExcel.Save
GoExcel.Close

'Message box
Dim oMessage  As String = "BOM Info"
Dim oCaption As String = "Esportazione completata..."
Dim oButtons As MessageBoxButtons = MessageBoxButtons.OK
Dim oIcons As MessageBoxIcon = MessageBoxIcon.Information

Dim oResult As DialogResult

'Display the MessageBox
oResult = MessageBox.Show(oMessage, oCaption, oButtons, oIcons)



ThisDoc.Launch(sJobBOMName)

End Sub

Public Sub QueryBOMRowProperties(oBOMRows As BOMRowsEnumerator, JobSpreadSheetName As String, iCurrRow As Integer)

'Iterate through the contents of BOM Rows.
Static CurrentRow As Integer = iCurrRow
Dim i As Long
Dim sWriteCell As String

For i = 1 To oBOMRows.Count

'Get the current row
Dim oRow As BOMRow
oRow = oBOMRows.Item(i)

'Set a reference to the primary ComponentDefinition of the row
Dim oCompDef As ComponentDefinition
oCompDef = oRow.ComponentDefinitions.Item(1)

Dim oPropSets As PropertySets
oPropSets = oCompDef.Document.PropertySets

oDesignTrackingPropertySet = oPropSets.Item("Design Tracking Properties")
oInventorSummaryPropertySet = oPropSets.Item("Inventor Summary Information")
oCustomPropertySet = oPropSets.Item("Inventor User Defined Properties")

'Get the file properties that are required
oItemNumberProperty = oRow.ItemNumber
oPartNumProperty = oDesignTrackingPropertySet.Item("Part Number")
oDescripProperty = oDesignTrackingPropertySet.Item("Description")
oTitleProperty = oInventorSummaryPropertySet.Item("Title")
oMaterialProperty = oDesignTrackingPropertySet.Item("Material")

'Trace.WriteLine("iLogic: oItemNumberProperty: " & oItemNumberProperty & " | " & oPartNumProperty.Value & " | " & oDescripProperty.Value & " | " & oTitleProperty.Value)
Logger.Info("iLogic: oItemNumberProperty: " & oItemNumberProperty & " | " & oPartNumProperty.Value & " | " & oDescripProperty.Value & " | " & oTitleProperty.Value)



sWriteCell = "E" & CurrentRow
GoExcel.CellValue(sWriteCell) = oItemNumberProperty

sWriteCell = "L" & CurrentRow
GoExcel.CellValue(sWriteCell) = oRow.ItemQuantity

sWriteCell = "F" & CurrentRow
GoExcel.CellValue(sWriteCell) = oPartNumProperty.Value

sWriteCell = "G" & CurrentRow
GoExcel.CellValue(sWriteCell) = oDescripProperty.Value

sWriteCell = "J" & CurrentRow
GoExcel.CellValue(sWriteCell) = oMaterialProperty.Value



'Recursively iterate child rows if present
If Not oRow.ChildRows Is Nothing Then
		GoExcel.Application.Range("A" & CurrentRow, "H" & CurrentRow).Interior.Color = RGB(211, 211, 211)
		GoExcel.Application.Range("A" & CurrentRow, "H" & CurrentRow).Borders(XlBordersIndex.xlEdgeTop).Weight = XlBorderWeight.xlThin
		GoExcel.Application.Range("A" & CurrentRow, "H" & CurrentRow).Borders(XlBordersIndex.xlEdgeBottom).Weight = XlBorderWeight.xlThin
		CurrentRow = CurrentRow + 1
		If CurrentRow > 10 Then
				GoExcel.Application.Range("A" & CurrentRow, "H" & CurrentRow - 2).Borders(XlBordersIndex.xlEdgeTop).Weight = XlBorderWeight.xlThin
			End If
Call QueryBOMRowProperties(oRow.ChildRows, JobSpreadSheetName, CurrentRow)

Else
End If

'Increment current row
CurrentRow = CurrentRow + 1

Next i
		GoExcel.Application.Range("A" & CurrentRow-1, "H" & CurrentRow-1).Borders(XlBordersIndex.xlEdgeTop).Weight = XlBorderWeight.xlThin
End Sub

but I can't reorder the list, 
or rather within Inventor the list is sorted while in the Excel file it doesn't maintain the correct ordering, 
how can I correct it?

I attach images to better understand the problem.

[Michael.Navara · Accepted solution]

It looks like the Inventor doesn't change the content of BomRows collection. In this case you can implement your own ordering of bom rows. In this case I recommend you to implement your own Class which contains the information relevant for export (Line 67 - 74). Then you can create your collection of instances of this class (Line 3). Fill this flat list (Lines 9 - 39) and sort them when you write data to the Excel (Line 45).

Note: for some cases you need to improve the sorting. This is the simplest sorting by string. For more advanced sorting see this post.

This is not complete code, but only re-implementation of QueryBOMRowProperties method used in your original post. (Without Excel sheet formatting)

Public Sub QueryBOMRowProperties2(oBOMRows As BOMRowsEnumerator)

    Dim myBomRows As New List(Of MyBomRow)
    FillBomRowsList(oBOMRows, myBomRows)
    WriteToExcel(myBomRows)

End Sub

Private Sub FillBomRowsList(oBomRows As BOMRowsEnumerator, myBomRows As List(Of MyBomRow))
    For Each oRow As BOMRow In oBomRows

        'Set a reference to the primary ComponentDefinition of the row
        Dim oCompDef As ComponentDefinition
        oCompDef = oRow.ComponentDefinitions.Item(1)

        Dim oPropSets As PropertySets
        oPropSets = oCompDef.Document.PropertySets

        Dim oDesignTrackingPropertySet = oPropSets.Item("Design Tracking Properties")
        Dim oInventorSummaryPropertySet = oPropSets.Item("Inventor Summary Information")
        Dim oCustomPropertySet = oPropSets.Item("Inventor User Defined Properties")

        'Get the file properties that are required
        Dim myBomRow = New MyBomRow()
        myBomRow.ItemNumber = oRow.ItemNumber
        myBomRow.PartNumber = oDesignTrackingPropertySet.Item("Part Number").Value
        myBomRow.Description = oDesignTrackingPropertySet.Item("Description").Value
        myBomRow.Title = oInventorSummaryPropertySet.Item("Title").Value
        myBomRow.Material = oDesignTrackingPropertySet.Item("Material").Value

        'Append new value to List
        myBomRows.Add(myBomRow)

        'Iterate child rows if any
        If oRow.ChildRows IsNot Nothing Then
            FillBomRowsList(oRow.ChildRows, myBomRows)
        End If
    Next
End Sub

Private Sub WriteToExcel(myBomRows As List(Of MyBomRow))

    Dim rowIndex As Integer = 22

    For Each myBomRow As MyBomRow In myBomRows.OrderBy(Function(x) x.ItemNumber)
        Dim sWriteCell As String

        sWriteCell = "E" & rowIndex
        GoExcel.CellValue(sWriteCell) = myBomRow.ItemNumber ' oItemNumberProperty

        sWriteCell = "L" & rowIndex
        GoExcel.CellValue(sWriteCell) = myBomRow.ItemQuantity ' oRow.ItemQuantity

        sWriteCell = "F" & rowIndex
        GoExcel.CellValue(sWriteCell) = myBomRow.PartNumber ' oPartNumProperty.Value

        sWriteCell = "G" & rowIndex
        GoExcel.CellValue(sWriteCell) = myBomRow.Description ' oDescripProperty.Value

        sWriteCell = "J" & rowIndex
        GoExcel.CellValue(sWriteCell) = myBomRow.Material ' oMaterialProperty.Value

        rowIndex += 1
    Next
End Sub

Class MyBomRow
    Property ItemNumber() As String
    Property PartNumber() As String
    Property Description() As String
    Property Title() As String
    Property Material() As String
    Public Property ItemQuantity As String
End Class

[marco_bonacina5834P]

Thanks for the reply, with this modification it works fine

[marco_bonacina5834P]

HI,
I'm trying the rule but I can't get what I want with the sorting.
In practice, in Excel file, it does not maintain the numerical sequence from smallest to largest, for example the number 10 is inserted after the lines containing the numbers 1. Do you have any solution to this?
Thank you

[Michael.Navara · Accepted solution]

This is what I'm talking about. You need to implement custom comparer which can handle this.

Private Sub WriteToExcel(myBomRows As List(Of MyBomRow))

	Dim rowIndex = 22

	'For Each myBomRow As MyBomRow In myBomRows.OrderBy(Function(x) x.ItemNumber)
	For Each myBomRow As MyBomRow In myBomRows.OrderBy(Function(x) x, New MyBomRowComparer())

...
...
End Sub

Class MyBomRowComparer
	Implements IComparer(Of MyBomRow)
	Property StructuredViewDelimiter As String = "."

	Public Function Compare(x As MyBomRow, y As MyBomRow) As Integer Implements IComparer(Of MyBomRow).Compare

		Dim xItemNumberParts As String() = x.ItemNumber.Split(StructuredViewDelimiter)
		Dim yItemNumberParts As String() = y.ItemNumber.Split(StructuredViewDelimiter)
		Dim result As Integer
		For i = 0 To Min(xItemNumberParts.Length, yItemNumberParts.Length) -1
			Dim iX, iY As Integer

			'Try compare as integers
			If Integer.TryParse(xItemNumberParts(i), iX) And Integer.TryParse(yItemNumberParts(i), iY) Then
				result = iX.CompareTo(iY)
				If result <> 0 Then Return result

			Else
				'Compare as strings (NOT EXPECTED)
				result = xItemNumberParts(i).CompareTo(yItemNumberParts(i))
				If result <> 0 Then Return result
			End If
		Next

		'Compare number of parts
		result = xItemNumberParts.Length - yItemNumberParts.Length
		Return result
	End Function
End Class

Full code is in attachment.

NOTE:

• Fix for Excel conversion of strings to numbers dates etc. was added
• Language independent search for Structured BOMView was added
• Excel formatting is missing

[marco_bonacina5834P]

Now the sorting works but it doesn't fill in the quantities

[marco_bonacina5834P]

I solved it, there was a missing part of the code.
Thanks