BOM export using API

[gert-leonvanlier]

I have written code in C# to automatically export the correct fields of an assembly to the BOM, by importing an XML file with the right fields. When I do this manually, it works: the fields appear in the order I placed them. However, when my C# code is doing this, the order is not the same.

Correct is:

The export in Excel shows (with my code) this:

I thought it worked properly before, however I didn't change anything in my code, so most likely it was always like this and I didn't check.

Is there something I need to add to my code, is this a bug in the API? I recently updated to Inventor 2019.5.1. I will check if this also occurs on an older version.

This is the code:

public void XXXContentList()
{
	string progId = "Inventor.Application";

	Inventor.Application invApp = (Inventor.Application)Marshal.GetActiveObject(progId);

	DocumentTypeEnum docType = invApp.ActiveDocumentType;
	AssemblyDocument assemblyDoc;
	AssemblyComponentDefinition assemblyCompDef;
	BOM bom;
	SaveFileDialog saveFileDialog = new SaveFileDialog();

	if (docType == DocumentTypeEnum.kAssemblyDocumentObject)
	{
		assemblyDoc = (AssemblyDocument)invApp.ActiveDocument;
		assemblyCompDef = assemblyDoc.ComponentDefinition;

	}
	else
	{
		return;
	}

	//templateName = "All avalaible fields";
	
	saveFileDialog.FileName = "Content List.xlsx";
	saveFileDialog.Filter = "Excel File (*.xlsx)|*xlsx";
	string fullDocName = assemblyDoc.FullDocumentName;
	string fileName = assemblyDoc.DisplayName;

	try
	{
		var templateForm = new FormContentListSelect(this);

		if (templateForm.ShowDialog() == DialogResult.OK)
		{
			var contentListTemplateName = string.Empty;

			if (TemplateName == "All avalaible fields")
			{
				contentListTemplateName = "XXX_ContentListAll.xml";
			}
			else if (TemplateName == "Project Engineering Den Bosch")
			{
				contentListTemplateName = "XXX_ContentListProjectEngineeringDB.xml";
			}

			bom = assemblyCompDef.BOM;
			bom.PartsOnlyViewEnabled = true;
			bom.ImportBOMCustomization(System.IO.Path.GetDirectoryName(Assembly.GetCallingAssembly().Location) + @"\" + contentListTemplateName);
			var bomView = bom.BOMViews["Parts Only"];

			string initDirectory = fullDocName.Replace(fileName + ".iam", "");
			string fileNameExcel = fileName + " Content List.xlsx";
			string filePath = initDirectory + fileNameExcel;
			saveFileDialog.InitialDirectory = initDirectory;
			saveFileDialog.FileName = fileNameExcel;


			if (saveFileDialog.ShowDialog() == DialogResult.OK)
			{
				bomView.Export(saveFileDialog.FileName, FileFormatEnum.kMicrosoftExcelFormat);
				//EditExcel(saveFileDialog.FileName);

				DialogResult dialogResult = MessageBox.Show("Would you like to open the created Content List?", "Open Content List", MessageBoxButtons.YesNo, MessageBoxIcon.Question);

				if (dialogResult == DialogResult.Yes)
				{
					Process fileOpener = new Process();
					fileOpener.StartInfo.FileName = saveFileDialog.FileName;
					fileOpener.Start();

				}
			}
			else if (templateForm.ShowDialog() == DialogResult.Cancel)
			{
				return;
			}
		}

	}
	catch
	{
		MessageBox.Show("Please save the assembly before generating the content list.", "Please save", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
	}

}

[gert-leonvanlier]

I can confirm that issue is not arising when using Inventor 2021.

Using 2019.4.9 the problem is also present. I can later try on another pc that maybe has an even older version of 2019 than 2019.4.9.

[jjstr8]

I'm seeing the same thing in 2019.4.8.  It appears that the API export ignores the column ordering.  If you open the xml file in Visual Studio, you can see the complete list of columns, visible or not.  Within a column element's definition there's a property element with the attribute named VisibleIndex.  This should be the column ordering, except it looks like the API export ignores this.  I was able to reorder the columns by moving a column's element up or down and resequencing the name="itemxx" attributes.  Since it's not happening for you in 2021, I assume it's a glitch that Autodesk fixed only in later versions.

Parts only would be under layout index 2

Hope this helps.

Jeremy

[A.Acheson]

This is a known issue going back as far as 2014 looking at the threads. Here is the post where the below solution is discussed.  The work around is to reorder the excel columns in a separate operation after the BOM has been exported. Private Sub ReorderXLBOM() does the business. The xml is really only used to ensure the correct columns are  exported. As a result the process is independent of the user's interaction with the headers in the BOM of each assembly. Although I am sure if you have custom columns  this would need to be taken into account in the sort pattern, I assume you would need to export and read the xml  to extract the header order and assign to excel sort sub routine. 

AddReference "Microsoft.Office.Interop.Excel" 'To use excel

Imports System.Windows.Forms
Imports System.IO
Imports Microsoft.Office.Interop.Excel        'To use excel

Sub Main()
	Dim oDoc As AssemblyDocument = ThisApplication.ActiveDocument
	
	'Dim oPath As String
	'oPath = oFolderDlg
	'oFileName = oPath & "Bruce.xml"
	
	ExportBOM(oDoc)
End Sub

Private BOMCustomizationFile As String = "C:\CustFile.xml"

Private excelApp As Microsoft.Office.Interop.Excel.Application
Private xlws As Worksheet

Sub ExportBOM(oDoc As Document)

'File Path Creation/Processing
	'Note, the following line will cause an error if the document is not saved.
	Dim oExportPath As String = System.IO.Path.GetDirectoryName(oDoc.FullFileName) & "\BOM\"
	Dim oExportName As String = oExportPath & System.IO.Path.GetFileNameWithoutExtension(oDoc.FullFileName) 'without extension'get BOM Target folder path
	
	If Not System.IO.Directory.Exists(oExportPath) Then: System.IO.Directory.CreateDirectory(oExportPath): End If

	If Dir(oExportName & ".xlsx") <> "" Then
		Kill (oExportName & ".xlsx")
	End If 

'Inventor BOM Processing
	Dim oBOM As BOM = oDoc.ComponentDefinition.BOM

	oBOM.ImportBOMCustomization(BOMCustomizationFile)
	oBOM.StructuredViewEnabled = True
	oBOM.StructuredViewFirstLevelOnly = False
	
	Dim oStructuredBOMView As BOMView
	oStructuredBOMView = oBOM.BOMViews.Item("Structured")
	oStructuredBOMView.Export (oExportName & ".xlsx", kMicrosoftExcelFormat)
	
'Excel processing
	excelApp = CreateObject("Excel.Application")
	excelApp.Visible = True
	excelApp.DisplayAlerts = False
	
	wb = excelApp.Workbooks.Open(oExportName & ".xlsx")
	xlws = wb.Worksheets(1)
	
	Call ReorderXLBOM()
	
	excelApp.Columns.AutoFit
	excelApp = Nothing

End Sub

Private Sub ReorderXLBOM()
    Dim ndx As Integer
    Dim Found As Range
    Dim counter As Integer = 1
	
	Dim arrColOrder() As String = {"Item", "QTY", "Part Number", "Description", "Stock Number"}
    'arrColOrder = Array("Item", "QTY", "Part Number", "Description", "Stock Number")

	On Error Resume Next

    For ndx = LBound(arrColOrder) To UBound(arrColOrder)

        'Set Found = xlws.Rows("1:1").Find(arrColOrder(ndx),, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
        Found = xlws.Rows("1:1").Find(arrColOrder(ndx), , -4163, 1, 2, 1, False)
    
        If Err.Number <> 0 Then
            MsgBox ("Error With Excel FIND function: " & Err.Number & " :: " & Err.Description & vbLf & vbLf & ndx)
            Err.Clear
        End If

        If Not Found Is Nothing Then
                If Found.Column <> counter Then
                        Found.EntireColumn.Cut
                        xlws.Columns(counter).Insert(-4161)
                        excelApp.CutCopyMode = False
                End If
                counter = counter + 1
        End If
    Next
    
    If Err.Number <> 0 Then
            MsgBox ("Reorder Columns Rule Error: " & Err.Number & " :: " & Err.Description & vbLf & vbLf & ndx)
            Err.Clear
    End If   
End Sub

Public Function oFolderDlg
    Dim dialog = New FolderBrowserDialog()
'    dialog.SelectedPath = Application.StartupPath
	dialog.ShowNewFolderButton = True
'	openFileDialog1.InitialDirectory 
    If DialogResult.OK = dialog.ShowDialog() Then
        oPath = dialog.SelectedPath
	Else
		MsgBox("No File Selected. Aborting Rule")
		oPath = ""
    End If
	Return oPath
End Function

[gert-leonvanlier]

I never noticed it before. Strange, but it is what it is then for now. We will probably switch to 2021 at the end of this year, so till that time we have to accept how it is.

[gert-leonvanlier]

Thank you for your solution. I will keep it in mind.