Export and Import Materials Library with Excel

Anonymous · ‎10-02-2017

I would like to export and import my custom material library with Excel

I add materials to custom material library, (tensile strength, yield strength,...)

diego_dorta · ‎05-17-2022

works fine.

thank you

harri_parker · ‎11-08-2022

Hi, is this solution modifiable for import, as I have to import approximately 2000 materials. Therefore, I would like a quicker fix than manually start duplicating them and changing names, as that is the important part. To have correct name on drawing. Of course, the mechanical properties too for FEM calculations

G60Dubs · ‎01-13-2025

Has there been any movment or development on this, or another method by which we can create a new matertial librarary en masse? I will shortly have to create a new large material library.

Terry_N · ‎01-14-2025

Hi,

I have made this to import materials to the active library. It works by copying the "Generic" material from the standard Inventor library then changing the properties to match a selected excel file. If the excel file isn't formatted correctly it will produce an example in the directory of the file you are running the rule from. I couldn't get the appearance to apply correctly so it just keeps the default appearance of "Generic". Maybe someone else can sort that out. If it finds a material already exists in the library it will skip it and add it to a txt doc report at the end. Let me know how it goes.

'iLogic rule to import a list of materials from excel to an existing material library.
'The defualt installed Inventor Material Library" needs to be avalible with  a material "Generic" in it for this to work.
'The defualt appreance that Generic had will be applied to all imported materials.

Imports System
Imports System.IO
Imports System.Windows.Forms
Imports Inventor

Sub Main
    ' Get the Inventor application and active material library
    Dim InvApp As Inventor.Application = ThisApplication
    Dim AML As AssetLibrary = InvApp.ActiveMaterialLibrary

    ' Show the current active library and prompt the user to confirm
    Dim confirmMessage As String = "The active material library is: " & AML.DisplayName & vbCrLf & vbCrLf & _
                                   "Do you want to upload materials to this library?"
    Dim confirmResult As DialogResult = MessageBox.Show(confirmMessage, "Confirm Active Library", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
    If confirmResult <> DialogResult.Yes Then
        MessageBox.Show("Operation cancelled by user.", "Cancelled", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Return
    End If

    ' Check if the library is writable
    If AML.IsReadOnly Then
        MessageBox.Show("The active material library is read-only. Please select a writable library and try again.", "Library Read-Only", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        Return
    End If

    ' Prompt the user to select an Excel file
    Dim fileDialog As New OpenFileDialog()
    fileDialog.Filter = "Excel Files (*.xlsx; *.xls)|*.xlsx;*.xls"
    fileDialog.Title = "Select Excel File"
    If fileDialog.ShowDialog() <> DialogResult.OK Then
        MessageBox.Show("No file selected. Exiting rule.", "File Selection Cancelled", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Return
    End If
    Dim excelFilePath As String = fileDialog.FileName

    ' Open Excel using CreateObject
    Dim excelApp As Object = CreateObject("Excel.Application")
    excelApp.Visible = False
    excelApp.DisplayAlerts = False

    ' Open the workbook and worksheet
    Dim workbook As Object = excelApp.Workbooks.Open(excelFilePath)
    Dim worksheet As Object = workbook.Sheets(1)

    ' Validate the Excel file headers
    Dim headers As New List(Of String) From {
        "Category Name", "Material Name", "Appearance Asset", "Density",
        "Thermal conductivity", "Specific heat", "Thermal expansion coefficient",
        "Young modulus", "Poisson ratio", "Shear modulus", "Minimum yield stress",
        "Minimum tensile strength", "Keywords"
    }

    Dim isValidFile As Boolean = True
    For i As Integer = 1 To headers.Count
        If worksheet.Cells(1, i).Value <> headers(i - 1) Then
            isValidFile = False
            Exit For
        End If
    Next

    If Not isValidFile Then
        ' Create an example Excel file with the correct headers
        Dim exampleFilePath As String = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(InvApp.ActiveDocument.FullFileName), "example_library.xlsx")
        Dim exampleWorkbook As Object = excelApp.Workbooks.Add()
        Dim exampleWorksheet As Object = exampleWorkbook.Sheets(1)
        For i As Integer = 1 To headers.Count
            exampleWorksheet.Cells(1, i).Value = headers(i - 1)
        Next
        exampleWorkbook.SaveAs(exampleFilePath)
        exampleWorkbook.Close()
        workbook.Close(False)
        excelApp.Quit()

        MessageBox.Show("The selected file is not formatted correctly. An example file has been created at: " & exampleFilePath & ". Please format your file and try again.", "Invalid File Format", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        Return
    End If

    ' Read the Excel file and add materials to the library
    Dim duplicatesLog As New List(Of String)
    Dim rowCount As Integer = worksheet.UsedRange.Rows.Count

    ' Create a simple progress form without System.Drawing.Size
    Dim progressForm As New Form()
    progressForm.Text = "Processing Materials"
    progressForm.Width = 300
    progressForm.Height = 100
    Dim progressBar As New System.Windows.Forms.ProgressBar()
    progressBar.Minimum = 0
    progressBar.Maximum = rowCount - 1
    progressBar.Dock = DockStyle.Fill
    Dim cancelButton As New Button()
    cancelButton.Text = "Cancel"
    cancelButton.Dock = DockStyle.Bottom
    AddHandler cancelButton.Click, Sub(sender, E)
                                       progressForm.Close()
                                       workbook.Close(False)
                                       excelApp.Quit()
                                       MessageBox.Show("Operation cancelled by user.", "Cancelled", MessageBoxButtons.OK, MessageBoxIcon.Information)
                                       Return
                                   End Sub
    progressForm.Controls.Add(progressBar)
    progressForm.Controls.Add(cancelButton)
    progressForm.Show()

    ' Get the default Inventor library
    Dim defaultLibrary As AssetLibrary = InvApp.AssetLibraries.Item("Inventor Material Library")

    ' Find the "Generic" material in the default library
    Dim genericMaterial As MaterialAsset = Nothing
    For Each cat As AssetCategory In defaultLibrary.MaterialAssetCategories
        For Each ma As MaterialAsset In cat.Assets
            If ma.DisplayName = "Generic" Then
                genericMaterial = ma
                Exit For
            End If
        Next
        If genericMaterial IsNot Nothing Then Exit For
    Next

    If genericMaterial Is Nothing Then
        MessageBox.Show("The 'Generic' material could not be found in the default Inventor library.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        progressForm.Close()
        workbook.Close(False)
        excelApp.Quit()
        Return
    End If

    ' Iterate through each row in the Excel file
    For i As Integer = 2 To rowCount
        ' Update the progress bar
        progressBar.Value = i - 1
        System.Windows.Forms.Application.DoEvents()

        ' Read values from the Excel file
        Dim categoryName As String = worksheet.Cells(i, 1).Value
        Dim materialName As String = worksheet.Cells(i, 2).Value
        Dim appearanceAsset As String = worksheet.Cells(i, 3).Value
        Dim density As Double = worksheet.Cells(i, 4).Value
        Dim thermalConductivity As Double = worksheet.Cells(i, 5).Value
        Dim specificHeat As Double = worksheet.Cells(i, 6).Value
        Dim thermalExpansionCoefficient As Double = worksheet.Cells(i, 7).Value
        Dim youngModulus As Double = worksheet.Cells(i, 8).Value
        Dim poissonRatio As Double = worksheet.Cells(i, 9).Value
        Dim shearModulus As Double = worksheet.Cells(i, 10).Value
        Dim minimumYieldStress As Double = worksheet.Cells(i, 11).Value
        Dim minimumTensileStrength As Double = worksheet.Cells(i, 12).Value
        Dim keywords As String = worksheet.Cells(i, 13).Value

        ' Check for duplicates in the material library
        Dim isDuplicate As Boolean = False
        For Each cat As AssetCategory In AML.MaterialAssetCategories
            If cat.DisplayName = categoryName Then
                For Each ma As MaterialAsset In cat.Assets
                    If ma.DisplayName = materialName Then
                        duplicatesLog.Add("Duplicate skipped: " & categoryName & " - " & materialName)
                        isDuplicate = True
                        Exit For
                    End If
                Next
                Exit For
            End If
        Next

        ' If the material is not a duplicate, add it to the library
        If Not isDuplicate Then
            Try
				'copy inventor defualt generic to library
				genericMaterial.CopyTo(AML)
'                Rename the "Generic" material to create a new material name
				Dim newMaterial As MaterialAsset = AML.MaterialAssets.Item(genericMaterial.DisplayName)
				newMaterial.DisplayName = materialName

' Check if the category already exists
              Dim category As AssetCategory = Nothing			  
                For Each aCat As AssetCategory In AML.MaterialAssetCategories
                    If aCat.DisplayName = categoryName Then
                        category = aCat
                        Exit For				
                    End If	
                Next
'                 If the category does Not exist, create it
					If category Is Nothing Then
                    ' create category
                    category = AML.MaterialAssetCategories.Add(categoryName.ToString, newMaterial)				
					End If

'				Move the material to the correct category if it already exists.
				If newMaterial.CategoryName = "" Then
				newMaterial.CopyTo(AML.MaterialAssetCategories.Item(category.DisplayName.ToString))
				end if

'                ' Update the material properties from the Excel sheet
'                Try
'                    newMaterial.AppearanceAsset = AML.AppearanceAssets("Defualt")
'                Catch ex As Exception
'                    MessageBox.Show("Error updating AppearanceAsset for material: " & materialName & ". Error: " & ex.Message & vbCrLf & "Code Line: newMaterial.AppearanceAsset = AML.AppearanceAssets(appearanceAsset)", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
'                End Try

                Try
                    newMaterial.PhysicalPropertiesAsset.Item("structural_Density").Value = density

                    newMaterial.PhysicalPropertiesAsset.Item("structural_Thermal_conductivity").Value = thermalConductivity

                    newMaterial.PhysicalPropertiesAsset.Item("structural_Specific_heat").Value = specificHeat

                    newMaterial.PhysicalPropertiesAsset.Item("structural_Thermal_expansion_coefficient").Value = thermalExpansionCoefficient

                    newMaterial.PhysicalPropertiesAsset.Item("structural_Young_modulus").Value = youngModulus

                    newMaterial.PhysicalPropertiesAsset.Item("structural_Poisson_ratio").Value = poissonRatio

                    newMaterial.PhysicalPropertiesAsset.Item("structural_Shear_modulus").Value = shearModulus

                    newMaterial.PhysicalPropertiesAsset.Item("structural_Minimum_yield_stress").Value = minimumYieldStress

                    newMaterial.PhysicalPropertiesAsset.Item("structural_Minimum_tensile_strength").Value = minimumTensileStrength

                    newMaterial.Item("physmat_Keywords").Value = keywords
                Catch ex As Exception
                    MessageBox.Show("Error updating propeties: " & materialName & ". Error: " & ex.Message , "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            Catch ex As Exception
                MessageBox.Show("Error adding material: " & materialName & ". Error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End If
    Next

    ' Close the progress form and clean up
    progressForm.Close()
    workbook.Close(False)
    excelApp.Quit()

    ' Log duplicates to a text file
    If duplicatesLog.Count > 0 Then
        Dim logFilePath As String = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(InvApp.ActiveDocument.FullFileName), "duplicates_log.txt")
        System.IO.File.WriteAllLines(logFilePath, duplicatesLog)
        MessageBox.Show("Duplicate materials were skipped. A log file has been created at: " & logFilePath, "Duplicates Logged", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End If

    MessageBox.Show("Material import completed.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub

Jelvin_ · ‎06-16-2025

Just as a fyi, you have spelled default wrong in this line:

'                    newMaterial.AppearanceAsset = AML.AppearanceAssets("Defualt")

i corrected this line and it worked for me, and also added one for the physical asset and this code works very well.

Thank you.

Terry_N · ‎06-17-2025

Hi Jelvin_

Its always the spelling, thanks for that. I had a play with is and got the appearances working. It will now check the available appearance libraries for an appearance with the same name then assign that to the new material.

'iLogic rule to import a list of materials from excel to an existing material library.
'The default installed Inventor Material Library" needs to be avalible with  a material "Generic" in it for this to work.
'The default appreance that Generic had will be applied to all imported materials.

Imports System
Imports System.IO
Imports System.Windows.Forms
Imports Inventor

Sub Main
    ' Get the Inventor application and active material library
    Dim InvApp As Inventor.Application = ThisApplication
    Dim AML As AssetLibrary = InvApp.ActiveMaterialLibrary

    ' Show the current active library and prompt the user to confirm
    Dim confirmMessage As String = "The active material library is: " & AML.DisplayName & vbCrLf & vbCrLf & _
                                   "Do you want to upload materials to this library?"
    Dim confirmResult As DialogResult = MessageBox.Show(confirmMessage, "Confirm Active Library", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
    If confirmResult <> DialogResult.Yes Then
        MessageBox.Show("Operation cancelled by user.", "Cancelled", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Return
    End If

    ' Check if the library is writable
    If AML.IsReadOnly Then
        MessageBox.Show("The active material library is read-only. Please select a writable library and try again.", "Library Read-Only", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        Return
    End If

    ' Prompt the user to select an Excel file
    Dim fileDialog As New OpenFileDialog()
    fileDialog.Filter = "Excel Files (*.xlsx; *.xls)|*.xlsx;*.xls"
    fileDialog.Title = "Select Excel File"
    If fileDialog.ShowDialog() <> DialogResult.OK Then
        MessageBox.Show("No file selected. Exiting rule.", "File Selection Cancelled", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Return
    End If
    Dim excelFilePath As String = fileDialog.FileName

    ' Open Excel using CreateObject
    Dim excelApp As Object = CreateObject("Excel.Application")
    excelApp.Visible = False
    excelApp.DisplayAlerts = False

    ' Open the workbook and worksheet
    Dim workbook As Object = excelApp.Workbooks.Open(excelFilePath)
    Dim worksheet As Object = workbook.Sheets(1)

    ' Validate the Excel file headers
    Dim headers As New List(Of String) From {
        "Category Name", "Material Name", "Appearance Asset", "Density",
        "Thermal conductivity", "Specific heat", "Thermal expansion coefficient",
        "Young modulus", "Poisson ratio", "Shear modulus", "Minimum yield stress",
        "Minimum tensile strength", "Keywords"
    }

    Dim isValidFile As Boolean = True
    For i As Integer = 1 To headers.Count
        If worksheet.Cells(1, i).Value <> headers(i - 1) Then
            isValidFile = False
            Exit For
        End If
    Next

    If Not isValidFile Then
        ' Create an example Excel file with the correct headers
        Dim exampleFilePath As String = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(InvApp.ActiveDocument.FullFileName), "example_library.xlsx")
        Dim exampleWorkbook As Object = excelApp.Workbooks.Add()
        Dim exampleWorksheet As Object = exampleWorkbook.Sheets(1)
        For i As Integer = 1 To headers.Count
            exampleWorksheet.Cells(1, i).Value = headers(i - 1)
        Next
        exampleWorkbook.SaveAs(exampleFilePath)
        exampleWorkbook.Close()
        workbook.Close(False)
        excelApp.Quit()

        MessageBox.Show("The selected file is not formatted correctly. An example file has been created at: " & exampleFilePath & ". Please format your file and try again.", "Invalid File Format", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        Return
    End If

    ' Read the Excel file and add materials to the library
    Dim duplicatesLog As New List(Of String)
    Dim rowCount As Integer = worksheet.UsedRange.Rows.Count

    ' Create a simple progress form without System.Drawing.Size
    Dim progressForm As New Form()
    progressForm.Text = "Processing Materials"
    progressForm.Width = 300
    progressForm.Height = 100
    Dim progressBar As New System.Windows.Forms.ProgressBar()
    progressBar.Minimum = 0
    progressBar.Maximum = rowCount - 1
    progressBar.Dock = DockStyle.Fill
    Dim cancelButton As New Button()
    cancelButton.Text = "Cancel"
    cancelButton.Dock = DockStyle.Bottom
    AddHandler cancelButton.Click, Sub(sender, E)
                                       progressForm.Close()
                                       workbook.Close(False)
                                       excelApp.Quit()
                                       MessageBox.Show("Operation cancelled by user.", "Cancelled", MessageBoxButtons.OK, MessageBoxIcon.Information)
                                       Return
                                   End Sub
    progressForm.Controls.Add(progressBar)
    progressForm.Controls.Add(cancelButton)
    progressForm.Show()

    ' Get the default Inventor library
    Dim defaultLibrary As AssetLibrary = InvApp.AssetLibraries.Item("Inventor Material Library")

    ' Find the "Generic" material in the default library
    Dim genericMaterial As MaterialAsset = Nothing
    For Each cat As AssetCategory In defaultLibrary.MaterialAssetCategories
        For Each ma As MaterialAsset In cat.Assets
            If ma.DisplayName = "Generic" Then
                genericMaterial = ma
                Exit For
            End If
        Next
        If genericMaterial IsNot Nothing Then Exit For
    Next

    If genericMaterial Is Nothing Then
        MessageBox.Show("The 'Generic' material could not be found in the default Inventor library.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        progressForm.Close()
        workbook.Close(False)
        excelApp.Quit()
        Return
    End If

    ' Iterate through each row in the Excel file
    For i As Integer = 2 To rowCount
        ' Update the progress bar
        progressBar.Value = i - 1
        System.Windows.Forms.Application.DoEvents()

        ' Read values from the Excel file
        Dim categoryName As String = worksheet.Cells(i, 1).Value
        Dim materialName As String = worksheet.Cells(i, 2).Value
        Dim appearanceAsset As String = worksheet.Cells(i, 3).Value
        Dim density As Double = worksheet.Cells(i, 4).Value
        Dim thermalConductivity As Double = worksheet.Cells(i, 5).Value
        Dim specificHeat As Double = worksheet.Cells(i, 6).Value
        Dim thermalExpansionCoefficient As Double = worksheet.Cells(i, 7).Value
        Dim youngModulus As Double = worksheet.Cells(i, 8).Value
        Dim poissonRatio As Double = worksheet.Cells(i, 9).Value
        Dim shearModulus As Double = worksheet.Cells(i, 10).Value
        Dim minimumYieldStress As Double = worksheet.Cells(i, 11).Value
        Dim minimumTensileStrength As Double = worksheet.Cells(i, 12).Value
        Dim keywords As String = worksheet.Cells(i, 13).Value

        ' Check for duplicates in the material library
        Dim isDuplicate As Boolean = False
        For Each cat As AssetCategory In AML.MaterialAssetCategories
            If cat.DisplayName = categoryName Then
                For Each ma As MaterialAsset In cat.Assets
                    If ma.DisplayName = materialName Then
                        duplicatesLog.Add("Duplicate skipped: " & categoryName & " - " & materialName)
                        isDuplicate = True
                        Exit For
                    End If
                Next
                Exit For
            End If
        Next

        ' If the material is not a duplicate, add it to the library
        If Not isDuplicate Then
            Try
				'copy inventor defualt generic to library
				genericMaterial.CopyTo(AML)
'                Rename the "Generic" material to create a new material name
				Dim newMaterial As MaterialAsset = AML.MaterialAssets.Item(genericMaterial.DisplayName)
				newMaterial.DisplayName = materialName

' Check if the category already exists
              Dim category As AssetCategory = Nothing			  
                For Each aCat As AssetCategory In AML.MaterialAssetCategories
                    If aCat.DisplayName = categoryName Then
                        category = aCat
                        Exit For				
                    End If	
                Next
'                 If the category does Not exist, create it
					If category Is Nothing Then
                    ' create category
                    category = AML.MaterialAssetCategories.Add(categoryName.ToString, newMaterial)				
					End If

'				Move the material to the correct category if it already exists.
				If newMaterial.CategoryName = "" Then
				newMaterial.CopyTo(AML.MaterialAssetCategories.Item(category.DisplayName.ToString))
				end if

                ' Update the material properties from the Excel sheet

			'look for appearnce in avalible  libraries			
			Dim libAsset As Asset = Nothing

			    ' If not found, cycle through all appearance libraries
			    Dim assetLibs As AssetLibraries = ThisApplication.AssetLibraries
			For Each assetLib As AssetLibrary In assetLibs
					Try
						libAsset = assetLib.AppearanceAssets.Item(appearanceAsset)
			            Exit For
			        Catch
			' Continue to next library if not found
			        End Try
			    Next

			'Try make the new material appearance the one from the appearance library
			Try
				   newMaterial.AppearanceAsset = libAsset
			Catch
				Try
					'If doesn't work make it defualt
					newMaterial.AppearanceAsset = AML.AppearanceAssets("Default")
                Catch ex As Exception
                    MessageBox.Show("Error updating AppearanceAsset for material: " & materialName & ". Error: " & ex.Message & vbCrLf & "Code Line: newMaterial.AppearanceAsset = AML.AppearanceAssets(appearanceAsset)", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
			End Try
                Try
					'continue onto material properties
                    newMaterial.PhysicalPropertiesAsset.Item("structural_Density").Value = density

                    newMaterial.PhysicalPropertiesAsset.Item("structural_Thermal_conductivity").Value = thermalConductivity

                    newMaterial.PhysicalPropertiesAsset.Item("structural_Specific_heat").Value = specificHeat

                    newMaterial.PhysicalPropertiesAsset.Item("structural_Thermal_expansion_coefficient").Value = thermalExpansionCoefficient

                    newMaterial.PhysicalPropertiesAsset.Item("structural_Young_modulus").Value = youngModulus

                    newMaterial.PhysicalPropertiesAsset.Item("structural_Poisson_ratio").Value = poissonRatio

                    newMaterial.PhysicalPropertiesAsset.Item("structural_Shear_modulus").Value = shearModulus

                    newMaterial.PhysicalPropertiesAsset.Item("structural_Minimum_yield_stress").Value = minimumYieldStress

                    newMaterial.PhysicalPropertiesAsset.Item("structural_Minimum_tensile_strength").Value = minimumTensileStrength

                    newMaterial.Item("physmat_Keywords").Value = keywords
                Catch ex As Exception
                    MessageBox.Show("Error updating propeties: " & materialName & ". Error: " & ex.Message , "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            Catch ex As Exception
                MessageBox.Show("Error adding material: " & materialName & ". Error: " & ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End If
    Next

    ' Close the progress form and clean up
    progressForm.Close()
    workbook.Close(False)
    excelApp.Quit()

    ' Log duplicates to a text file
    If duplicatesLog.Count > 0 Then
        Dim logFilePath As String = System.IO.Path.Combine(System.IO.Path.GetDirectoryName(InvApp.ActiveDocument.FullFileName), "duplicates_log.txt")
        System.IO.File.WriteAllLines(logFilePath, duplicatesLog)
        MessageBox.Show("Duplicate materials were skipped. A log file has been created at: " & logFilePath, "Duplicates Logged", MessageBoxButtons.OK, MessageBoxIcon.Information)
    End If

    MessageBox.Show("Material import completed.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)
End Sub

Community

Export and Import Materials Library with Excel

Export and Import Materials Library with Excel

Export and Import Materials Library with Excel