Need help exporting sheet material to Excel

[negutorthik]

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

[dalton98]

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("Ошибка открытия файла", "Ошибка")

[negutorthik]

I need to add a quantity here.

example: oStr = sCompDef.Quantity

[A.Acheson]

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

[negutorthik]

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

[A.Acheson · Accepted solution]

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

[negutorthik]

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