Importing Excel that consists of coordinates in creating a Spline or Poly Line

Anonymous · ‎07-19-2015

I have a excel file (attached) that has different coordinates. I want to get these values and draw a spline/polyline. I have looked in the web and this forum and the closest I could find was this: Import Triangle From Excel. But I can't fully compare it to my situation.

Ideas that come to mind:

1. Use the OpenFileDialog to get the file path of the Excel.

2. Read the excel file and cow the number of rows to loop it.

3. Draw the spline/polyline automatically by reading each row.

Any links or basic guides are really helpful. This is my first try in creating my own Plugin so I'm a real beginner.

Thanks a lot!

Anonymous · ‎07-20-2015

I have found a way opening the excel file but I can't convert the cell value to a One Dimensional Array of Double to make it a point for the spline/polyline. What should I do?

Imports Autodesk.AutoCAD.ApplicationServices
Imports Autodesk.AutoCAD.DatabaseServices
Imports Autodesk.AutoCAD.EditorInput
Imports Autodesk.AutoCAD.Runtime
Imports Autodesk.AutoCAD.Windows

Imports Microsoft.Office.Interop
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel
Imports Autodesk.AutoCAD.Geometry


Public Class Commands

    Dim appXL As Excel.Application
    Dim wbXL As Excel.Workbook
    Dim wbsXL As Excel.Workbooks
    Dim shXL As Excel.Worksheet

    <CommandMethod("OpenExcel")>
    Public Sub SelectSpreadsheet()

        Dim doc As Document = Application.DocumentManager.MdiActiveDocument
        Dim db As Database = doc.Database
        Dim ed As Editor = doc.Editor

        Dim ofd As OpenFileDialog = New OpenFileDialog("Select Excel spreadsheet to link", (DBNull.Value).ToString, "xlsx; xls", "Choose an Excel File", OpenFileDialog.OpenFileDialogFlags.DoNotTransferRemoteFiles)

        If ofd.ShowDialog() <> Windows.Forms.DialogResult.OK Then

            Return

        Else

            appXL = CreateObject("Excel.Application")
            appXL.Visible = False

            wbsXL = appXL.Workbooks
            wbXL = wbsXL.Open(ofd.Filename)
            shXL = wbXL.ActiveSheet

            Dim range As Excel.Range = shXL.UsedRange
            Dim Obj As Object
            Dim ary() As Double
            Dim total As Integer = 1
            Dim total2 As Integer = 1

            For rCnt = 1 To range.Rows.Count

                For cCnt = 1 To range.Columns.Count

                    Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)

                    ReDim Preserve ary(cCnt)
                    ary(cCnt) = CDbl(Obj.Value)
                    total = total + 1

                Next

            '' Start a transaction
            Using acTrans As Transaction = db.TransactionManager.StartTransaction()

                '' Open the Block table for read
                Dim acBlkTbl As BlockTable
                acBlkTbl = acTrans.GetObject(db.BlockTableId, OpenMode.ForRead)

                '' Open the Block table record Model space for write
                Dim acBlkTblRec As BlockTableRecord
                acBlkTblRec = acTrans.GetObject(acBlkTbl(BlockTableRecord.ModelSpace), _
                                                OpenMode.ForWrite)

                '' Define the fit points for the spline
                Dim ptColl As Point3dCollection = New Point3dCollection()

                Do While (total2 <= total)

                    ptColl.Add(New Point3d(ary(total2)))

                    total2 = total2 + 1

                Loop

                '' Get a 3D vector from the point (0.5,0.5,0)
                Dim vecTan As Vector3d = New Point3d(0.5, 0.5, 0).GetAsVector

                '' Create a spline through (0, 0, 0), (5, 5, 0), and (10, 0, 0) with a
                '' start and end tangency of (0.5, 0.5, 0.0)
                Dim acSpline As Spline = New Spline(ptColl, vecTan, vecTan, 4, 0.0)

                acSpline.SetDatabaseDefaults()

                '' Add the new object to the block table record and the transaction
                acBlkTblRec.AppendEntity(acSpline)
                acTrans.AddNewlyCreatedDBObject(acSpline, True)

                '' Save the new object to the database
                acTrans.Commit()

            End Using

         Next

            wbXL.Close()
            appXL.UserControl = True
            appXL.Quit()

            releaseObject(shXL)
            releaseObject(wbsXL)
            releaseObject(wbXL)
            releaseObject(appXL)

        End If

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

End Class

Anonymous · ‎07-20-2015

I have solved my own problem hehehe

I used the excel filed(attached) for my coordinates. Kudos!

Imports Autodesk.AutoCAD.ApplicationServices
Imports Autodesk.AutoCAD.DatabaseServices
Imports Autodesk.AutoCAD.EditorInput
Imports Autodesk.AutoCAD.Runtime
Imports Autodesk.AutoCAD.Windows

Imports Microsoft.Office.Interop
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel
Imports Autodesk.AutoCAD.Geometry


Public Class Commands

    Dim appXL As Excel.Application
    Dim wbXL As Excel.Workbook
    Dim wbsXL As Excel.Workbooks
    Dim shXL As Excel.Worksheet

    <CommandMethod("OpenExcel")>
    Public Sub SelectSpreadsheet()

        Dim doc As Document = Application.DocumentManager.MdiActiveDocument
        Dim db As Database = doc.Database
        Dim ed As Editor = doc.Editor

        Dim ofd As OpenFileDialog = New OpenFileDialog("Select Excel spreadsheet to link", (DBNull.Value).ToString, "xlsx; xls", "Choose an Excel File", OpenFileDialog.OpenFileDialogFlags.DoNotTransferRemoteFiles)

        If ofd.ShowDialog() <> Windows.Forms.DialogResult.OK Then

            Return

        Else

            appXL = CreateObject("Excel.Application")
            appXL.Visible = False

            wbsXL = appXL.Workbooks
            wbXL = wbsXL.Open(ofd.Filename)
            shXL = wbXL.ActiveSheet

            Dim range As Excel.Range = shXL.UsedRange
            Dim Obj As Object
            Dim x, y, z As Double
            Dim counter As Integer = 0

            Dim pKeyOpts As PromptKeywordOptions = New PromptKeywordOptions("")
            pKeyOpts.Message = vbLf & "Select a shape : "
            pKeyOpts.Keywords.Add("Spline")
            pKeyOpts.Keywords.Add("Polyline")
            pKeyOpts.Keywords.Add("3DPolyline")
            pKeyOpts.AllowNone = False

            Dim pKeyRes As PromptResult = doc.Editor.GetKeywords(pKeyOpts)

            '' Start a transaction
            Using acTrans As Transaction = db.TransactionManager.StartTransaction()

                '' Open the Block table for read
                Dim acBlkTbl As BlockTable
                acBlkTbl = acTrans.GetObject(db.BlockTableId, OpenMode.ForRead)

                '' Open the Block table record Model space for write
                Dim acBlkTblRec As BlockTableRecord
                acBlkTblRec = acTrans.GetObject(acBlkTbl(BlockTableRecord.ModelSpace), _
                                                OpenMode.ForWrite)

                '' Define the fit points for the spline
                Dim ptColl As Point3dCollection = New Point3dCollection()

                '' Create a polyline with two segments (3 points)
                Dim acPoly As Polyline = New Polyline()
                acPoly.SetDatabaseDefaults()
                Try
                    For rCnt = 1 To range.Rows.Count

                        For cCnt = 1 To 3

                            Obj = CType(range.Cells(rCnt, cCnt), Excel.Range)

                            If cCnt = 1 Then

                                x = Obj.Value

                            ElseIf cCnt = 2 Then

                                y = Obj.Value

                            ElseIf cCnt = 3 Then

                                z = Obj.Value

                            End If

                        Next

                        ptColl.Add(New Point3d(x, y, z))

                        acPoly.AddVertexAt(counter, New Point2d(x, y), 0, 0, 0)
                        counter = counter + 1

                    Next

                Catch e As Exception

                    MsgBox("Reading error occured!" & vbLf & e.Message)

                End Try

                If pKeyRes.StringResult = "Spline" Then

                    '' Get a 3D vector from the point (0.5,0.5,0)
                    Dim vecTan As Vector3d = New Point3d(0.5, 0.5, 0).GetAsVector

                    '' Create a spline through (0, 0, 0), (5, 5, 0), and (10, 0, 0) with a
                    '' start and end tangency of (0.5, 0.5, 0.0)
                    Dim acSpline As Spline = New Spline(ptColl, vecTan, vecTan, 4, 0.0)

                    acSpline.SetDatabaseDefaults()

                    '' Add the new object to the block table record and the transaction
                    acBlkTblRec.AppendEntity(acSpline)
                    acTrans.AddNewlyCreatedDBObject(acSpline, True)

                ElseIf pKeyRes.StringResult = "Polyline" Then

                    '' Add the new object to the block table record and the transaction
                    acBlkTblRec.AppendEntity(acPoly)
                    acTrans.AddNewlyCreatedDBObject(acPoly, True)

                ElseIf pKeyRes.StringResult = "3DPolyline" Then

                    Dim poly As Polyline3d = New Polyline3d()

                    acBlkTblRec.AppendEntity(poly)

                    acTrans.AddNewlyCreatedDBObject(poly, True)

                    For Each pt As Point3d In ptColl

                        Dim vex3d As PolylineVertex3d = New PolylineVertex3d(pt)

                        poly.AppendVertex(vex3d)

                        acTrans.AddNewlyCreatedDBObject(vex3d, True)

                    Next

                    poly.Closed = True

                End If

                '' Save the new object to the database
                acTrans.Commit()

            End Using


            wbXL.Close()
            appXL.UserControl = False
            appXL.Quit()

            releaseObject(shXL)
            releaseObject(wbsXL)
            releaseObject(wbXL)
            releaseObject(appXL)

        End If

    End Sub

    Private Sub releaseObject(ByVal obj As Object)
        Try
            System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
        End Try
    End Sub

End Class

Community

Importing Excel that consists of coordinates in creating a Spline or Poly Line

Importing Excel that consists of coordinates in creating a Spline or Poly Line

Importing Excel that consists of coordinates in creating a Spline or Poly Line