Imports Inventor
Imports Microsoft.Office.Interop

Public Class Form1
    Private Sub btnCreate_Click(sender As Object, e As EventArgs) Handles btnCreate.Click
        If txtFilename.Text <> "" Then
            InsertTableFromExcel(txtFilename.Text, txtTableName.Text)
        End If
    End Sub

    Private Sub btnCancel_Click(sender As Object, e As EventArgs) Handles btnCancel.Click
        Me.Close()
    End Sub

    Private Sub btnBrowse_Click(sender As Object, e As EventArgs) Handles btnBrowse.Click
        Dim fd As OpenFileDialog = New OpenFileDialog()
        fd.Title = "Insert Excel Table"
        fd.Filter = "Excel files (*.xls,*.xlsx)|*.xls;*.xlsx|All files (*.*)|*.*"
        fd.FilterIndex = 1

        If fd.ShowDialog() = DialogResult.OK Then
            txtFilename.Text = fd.FileName
        End If
    End Sub

    Public Sub InsertTableFromExcel(filename As String, title As String)
        ' Connect to Iventor and get active drawing.
        Dim invApp As Inventor.Application
        Dim drawDoc As DrawingDocument
        Try
            invApp = GetObject(, "Inventor.Application")
            drawDoc = invApp.ActiveDocument
        Catch ex As Exception
            MsgBox("Inventor must be running and drawing document must be active.")
            Return
        End Try

        ' Start Excel.
        lblStatus.Text = "Connecting to Excel..."
        lblStatus.Refresh()
        Dim excelApp As Excel.Application = Nothing
        Try
            excelApp = CreateObject("Excel.Application")
        Catch ex As Exception
            MsgBox("Unable to start Excel.")
            Return
        End Try


        lblStatus.Text = "Reading table..."
        lblStatus.Refresh()

        Dim workBook As Excel.Workbook
        workBook = excelApp.Workbooks.Open(filename)

        Try
            ' Get the first sheet (assuming the data is on the first sheet.)
            Dim workSheet As Excel.Worksheet = workBook.Sheets.Item(1)

            ' Search along the first row to see how many columns have data.
            Dim columnCount As Integer = 1
            Do
                If workSheet.Cells(1, columnCount).Text <> "" Then
                    columnCount += 1
                Else
                    Exit Do
                End If
            Loop
            columnCount = columnCount - 1

            Dim rowCount As Integer = 1
            Do
                If workSheet.Cells(rowCount, 1).Text <> "" Then
                    rowCount += 1
                Else
                    Exit Do
                End If
            Loop
            rowCount = rowCount - 1

            ' Load the array for the header.
            Dim header() As String
            ReDim header(columnCount - 1)
            For i As Integer = 1 To columnCount
                header(i - 1) = workSheet.Cells(1, i).Text
            Next

            ' Load the array for the contents.
            Dim contents() As String
            ReDim contents((rowCount - 1) * columnCount - 1)
            Dim index As Integer = 0
            For row As Integer = 2 To rowCount
                For col As Integer = 1 To columnCount
                    contents(index) = workSheet.Cells(row, col).Text
                    index += 1
                Next
            Next

            'Add the table to the sheet and set the table title to "Tools"
            Dim sht As Sheet = drawDoc.ActiveSheet
            Dim pstn As Point2d = invApp.TransientGeometry.CreatePoint2d(0, 0)
            Dim table As CustomTable = sht.CustomTables.Add(title, pstn, columnCount, rowCount - 1, header, contents)

            ' Set the first column to be left justified.
            table.Columns.Item(1).ValueHorizontalJustification = HorizontalTextAlignmentEnum.kAlignTextLeft

            ' Reposition the table in the upper-right corner.
            pstn = sht.Border.RangeBox.MaxPoint
            pstn.X = pstn.X - (table.RangeBox.MaxPoint.X - table.RangeBox.MinPoint.X)
            'pstn.Y = pstn.Y - (table.RangeBox.MaxPoint.Y - table.RangeBox.MinPoint.Y)
            table.Position = pstn
        Catch ex As Exception
            MsgBox("Unexpected error creating table.")
            lblStatus.Text = "Error"
            lblStatus.Refresh()
        Finally
            lblStatus.Text = ""
            lblStatus.Refresh()

            ' Close Excel
            excelApp.Quit()
        End Try
    End Sub
End Class
