Read exported BOM xml file to understand column order

[J_Dumont]

I am writing a program to export the BOM from an assembly. As I found out the BOM export from an API sorts the columns alphabetically. I would like to sort the columns based on an XML file that is used. I looked at some posts to resort the BOM xlsx file using the Excel reference. However, an array has to be created for the columns to sort by and the posts I viewed manually created the array.

I would like to read the XML file to capture an array dynamically as users typically have different needs. I am able to read the XML file but I'm trying to figure out how to determine the column order in the xml file.

I thought the XML file listed the columns in order and I created a list of column names where visible = true but it doesn't seem to be working as I first thought.

Any info on understanding the XML file would be greatly appreciated.

[WCrihfield]

Just checking...Is your end goal the XML file, or do you want to eventually end up with an Excel file?

Are you using a separate XML file as a reference, besides the one you're exporting from the BOM?

[J_Dumont]

I am exporting my BOM to a spreadsheet vis VB.Net and the columns are alphabetical instead of in the order that I configured. I used the Export function from the BOM so I can quickly change existing files to have the columns I need.

From other posts, it appears that I need to use Excel to sort the columns properly but each post I revied had the columns hardcoded. I have many users with different needs so I have been trying to read the XML file to figure out what columns the user wants and in what order and then I can create the array dynamically.

I think I figured out the XML file. It appears to export the Model Data as <Layout Index="0">, the Structured as, <Layout Index="1"> and the Parts Only as, <Layout Index="2">.

I can then capture the text from each Caption whose Visible = True and the VisibleIndex gives me the order.

<property iskey="true" isnull="true" name="Item1">
<property name="ImageIndex">-1</property>
<property name="ImageAlignment">Near</property>
<property name="Caption">Item</property>
<property name="ToolTip"/>
<property name="CustomizationCaption"/>
<property name="FieldName">Item Order</property>
<property name="FieldNameSort"/>
<property name="Name">treeListItem Order</property>
<property name="AllowIncrementalSearch">true</property>
<property name="Fixed">None</property>
<property name="ColumnEditName"/>
<property name="ShowButtonMode">Default</property>
<property name="VisibleIndex">0</property>
<property name="Visible">true</property>
<property name="SortOrder">None</property>
<property name="SortIndex">-1</property>
<property name="SortMode">Custom</property>
<property name="FilterMode">Value</property>

Please let me know if you think I missed anything.

[WCrihfield · Accepted solution]

When I export the 'Structured' BOMView using this:

Dim oADoc As AssemblyDocument = ThisAssembly.Document
Dim oADef As AssemblyComponentDefinition = oADoc.ComponentDefinition
Dim oBOM As BOM = oADef.BOM
oBOM.StructuredViewEnabled = True
oBOM.StructuredViewFirstLevelOnly = True
Dim oStView As BOMView = oBOM.BOMViews.Item("Structured")
Dim oFileName As String = "C:\Temp\BOM Export to Excel test.xlsx"
Dim oSheetName As String = "BOM"
oStView.Export(oFileName,FileFormatEnum.kMicrosoftExcelFormat,oSheetName)

All the columns are the same as they were when I was looking at it within Inventor's BOM dialog.

Even all the thumbnails are in place, and scale with the cell size.

I'm using Inventor Pro 2021.1, Win10 Enterprise, & MS Office 2019.

Here's a screen shot of my Inventor dialog & my Excel sheet.

[J_Dumont]

That's great info. Unfortunately, I'm programming using Inventor 2017 as we cannot upgrade for a few months.

I will try my code on a system with the newest release and check the results.

Also, I like how you added the sheet name.