topic Need help exporting sheet material to Excel in Inventor Programming Forum

Need help exporting sheet material to Excel

negutorthik — Thu, 21 Sep 2023 20:17:58 GMT

There is no work experience at Ilogic at all. There is a ready-made rule.Need to insert a column to calculate the quantity. I hope for your responsiveness

'____Create and write to a text file_________________
Try
extension = ".csv"
If (System.IO.File.Exists(ThisDoc.PathAndFileName(False) + extension) = True) Then
System.IO.File.Delete(ThisDoc.PathAndFileName(False) + extension)
End If

sBom = ThisDoc.Document.ComponentDefinition.BOM
sBom.PartsOnlyViewEnabled = True
sBOMView = sBom.BOMViews.Item("Только детали")
Catch
MessageBox.Show("Ошибка 1", "Ошибка")
End Try
Try
For Each sRow In sBOMView.BOMRows
Try
sCompDef = sRow.ComponentDefinitions.Item(1)

If sCompDef.Type = ObjectTypeEnum.kSheetMetalComponentDefinitionObject Then
Try
If sCompDef.HasFlatPattern = True Then
oFlat = sCompDef.FlatPattern
oStr = sCompDef.Document.DisplayName
oStr = oStr + ";" + (oFlat.Length * 10).ToString
oStr = oStr + ";" + (oFlat.Width * 10).ToString
oStr = oStr + ";" + (sCompDef.Thickness.Value * 10).ToString
oStr = oStr + ";" + (sCompDef.Material.Name).ToString
System.IO.File.AppendAllText(ThisDoc.PathAndFileName(False) + extension, oStr + vbNewLine, System.Text.Encoding.Default)
End If
Catch
MessageBox.Show("Ошибка 2", "Ошибка")
End Try
End If
Catch
MessageBox.Show("Ошибка 3", "Ошибка")
End Try
Next
Catch
MessageBox.Show("Ошибка 4", "Ошибка")
End Try
Try
ThisDoc.Launch(ThisDoc.PathAndFileName(False) + extension)
Catch
MessageBox.Show("Ошибка открытия файла", "Ошибка")
End Try

Re: Need help exporting sheet material to Excel

dalton98 — Fri, 22 Sep 2023 14:57:34 GMT

I got this to work. Took out all the try/catch

'____Create and write to a text file_________________
On Error Resume Next
extension = ".csv"
excel = System.IO.File.Create(ThisDoc.PathAndFileName(False) + extension)
excel.Close

sBom = ThisDoc.Document.ComponentDefinition.BOM
sBom.PartsOnlyViewEnabled = True
sBOMView = sBom.BOMViews.Item("Parts Only")

For Each sRow In sBOMView.BOMRows
sCompDef = sRow.ComponentDefinitions.Item(1)

If sCompDef.Type = ObjectTypeEnum.kSheetMetalComponentDefinitionObject Then
If sCompDef.HasFlatPattern = True Then
oFlat = sCompDef.FlatPattern
oStr = sCompDef.Document.DisplayName
oStr = oStr + ";" + (oFlat.Length * 10).ToString
oStr = oStr + ";" + (oFlat.Width * 10).ToString
oStr = oStr + ";" + (sCompDef.Thickness.Value * 10).ToString
oStr = oStr + ";" + (sCompDef.Material.Name).ToString

System.IO.File.AppendAllText(ThisDoc.PathAndFileName(False) + extension, oStr + vbNewLine, System.Text.Encoding.Default)
End If
End If
Next

ThisDoc.Launch(ThisDoc.PathAndFileName(False) + extension)

MessageBox.Show("Ошибка открытия файла", "Ошибка")

Re: Need help exporting sheet material to Excel

negutorthik — Sat, 23 Sep 2023 06:07:14 GMT

I need to add a quantity here.

example: oStr = sCompDef.Quantity

Re: Need help exporting sheet material to Excel

A.Acheson — Sat, 23 Sep 2023 06:28:01 GMT

Hi @negutorthik

The bomrow qty is not contained in the component definition of the part but can be accessed from the bomrow object shown here.

Syntax

BOMRow.ItemQuantity() As Long

Syntax

BOMRow.TotalQuantity() As String

Dim qty as String = sRow.TotalQuantity

Re: Need help exporting sheet material to Excel

negutorthik — Sat, 23 Sep 2023 06:45:23 GMT

I didn't understand anything).There is absolutely no experience in iLogic. But thank you more for your help

Re: Need help exporting sheet material to Excel

A.Acheson — Sat, 23 Sep 2023 17:31:09 GMT

Hi @negutorthik

Here is the bom row qty added to the string

'____Create and write to a text file_________________ On Error Resume Next extension = ".csv" excel = System.IO.File.Create(ThisDoc.PathAndFileName(False) + extension) excel.Close Dim sBOM As BOM = ThisDoc.Document.ComponentDefinition.BOM sBOM.PartsOnlyViewEnabled = True Dim sBOMView As BOMView = sBOM.BOMViews.Item("Parts Only") For Each sRow As BOMRow In sBOMView.BOMRows Dim sCompDef As ComponentDefinition = sRow.ComponentDefinitions.Item(1) If sCompDef.Type = ObjectTypeEnum.kSheetMetalComponentDefinitionObject Then If sCompDef.HasFlatPattern = True Then oFlat = sCompDef.FlatPattern oStr = sCompDef.Document.DisplayName oStr = oStr + ";" + (oFlat.Length * 10).ToString oStr = oStr + ";" + (oFlat.Width * 10).ToString oStr = oStr + ";" + (sCompDef.Thickness.Value * 10).ToString oStr = oStr + ";" + (sCompDef.Material.Name).ToString oStr = oStr + ";" + (sRow.TotalQuantity).ToString System.IO.File.AppendAllText(ThisDoc.PathAndFileName(False) + extension, oStr + vbNewLine, System.Text.Encoding.Default) End If End If Next ThisDoc.Launch(ThisDoc.PathAndFileName(False) + extension) MessageBox.Show("Ошибка открытия файла", "Ошибка")

and here is another method to get the bom to excel but in traditionally cell values.

Imports XL = Microsoft.Office.Interop.Excel AddReference "microsoft.office.interop.excel.dll" Dim sBOM As BOM = ThisDoc.Document.ComponentDefinition.BOM sBOM.PartsOnlyViewEnabled = True Dim sBOMView As BOMView = sBOM.BOMViews.Item("Parts Only") Dim xlApp As XL.Application = CreateObject("Excel.Application") Dim xlWb As XL.Workbook 'comment out or change to false 'in order to not show Excel xlApp.Visible = True Dim xlBOMPath As String = "C:\Temp\BomExport.xlsx" If IO.File.Exists(xlBOMPath) = True Then xlWb = xlApp.Workbooks.Open(xlBOMPath) Else xlWb = xlApp.Workbooks.Add() xlWb.SaveAs(xlBOMPath) End If xlWs = xlWb.Worksheets.Item("Sheet1") 'Set Column Headers xlWs.Range("A4").Value = "DISPLAY NAME" xlWs.Range("B4").Value = "FLAT PATTERN LENGTH" xlWs.Range("C4").Value = "FLAT PATTERN WIDTH" xlWs.Range("D4").Value = "THICKNESS" xlWs.Range("E4").Value = "MATERIAL" xlWs.Range("F4").Value = "QTY" Dim i As Integer = 5 For Each sRow As BOMRow In sBOMView.BOMRows Dim sCompDef As ComponentDefinition = sRow.ComponentDefinitions.Item(1) If sCompDef.Type = ObjectTypeEnum.kSheetMetalComponentDefinitionObject Then If sCompDef.HasFlatPattern = True Then Dim oFlat As FlatPattern = sCompDef.FlatPattern xlWs.Range("A" & i).Value = sCompDef.Document.DisplayName xlWs.Range("B" & i).Value = oFlat.Length * 10 xlWs.Range("C" & i).Value = oFlat.Width * 10 xlWs.Range("D" & i).Value = sCompDef.Thickness.Value * 10 xlWs.Range("E" & i).Value = sCompDef.Material.Name xlWs.Range("F" & i).Value = sRow.TotalQuantity 'index to advance To next row i=i+1 End If End If Next xlApp.Columns.AutoFit xlWb.Save 'xlWorkbook.Close (False) 'xlApp.Quit

Re: Need help exporting sheet material to Excel

negutorthik — Sat, 23 Sep 2023 17:56:39 GMT

Thank you very much. This is what we need. You're the best