Author Topic: Draw Polylines using Excel  (Read 2418 times)

0 Members and 1 Guest are viewing this topic.

Offline fixo

  • Full Member
  • ***
  • Posts: 135
  • Karma: +4/-0
  • Gender: Male
    • prefered language: C
    • Prog expertise: Good
    • View Profile
Draw Polylines using Excel
« on: April 20, 2013, 08:28:38 PM »
Here is complete code, tested on AutoCAD 2010, Excel 2010:
Code: [Select]
Imports System.IO
Imports Microsoft.Office.Interop.Excel
Imports Autodesk.AutoCAD.Runtime
Imports Autodesk.AutoCAD.ApplicationServices
Imports Autodesk.AutoCAD.DatabaseServices
Imports Autodesk.AutoCAD.EditorInput
Imports Autodesk.AutoCAD.Geometry
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel
'Imports MyProjectName.MyProjectName

<Assembly: CommandClass(GetType(MyProjectName.MyClassName))>
Namespace MyProjectName
    Public Class MyClassName

        <CommandMethod("hex")> _
        Public Sub ExcelToAcad()
            ReadExcelRange("C:\Test\TestXL.xlsx", "Sheet1")'<-- change file name and sheet name here
        End Sub

        Public Sub ReadExcelRange(ByVal xlFileName As String, ByVal xlSheetName As String)
            Dim lstPlines As New List(Of List(Of Object))
            If System.IO.File.Exists(xlFileName) Then

                Dim xlApp As Excel.Application = Nothing
                Dim xlWorkBooks As Excel.Workbooks = Nothing
                Dim xlWorkBook As Excel.Workbook = Nothing
                Dim xlWorkSheet As Excel.Worksheet = Nothing
                Dim xlWorkSheets As Excel.Sheets = Nothing
                Dim xlCells As Excel.Range = Nothing
                Dim xlRange As Excel.Range = Nothing

                xlApp = New Excel.Application
                Try
                    xlApp.DisplayAlerts = False
                    xlWorkBooks = xlApp.Workbooks
                    xlWorkBook = xlWorkBooks.Open(xlFileName)
                    xlApp.Visible = False
                    xlWorkSheets = xlWorkBook.Sheets
                    xlWorkSheet = CType(xlWorkSheets(xlSheetName), Excel.Worksheet)

                    xlRange = CType(xlWorkSheet.UsedRange.CurrentRegion, Excel.Range)


                    Dim xlRow As Excel.Range = Nothing
                    Dim xlCell As Excel.Range = Nothing

                    For irow As Integer = 1 To xlRange.Rows.Count

                        xlRow = CType(xlRange.Rows(irow), Excel.Range)
                        Dim lstRow As New List(Of Object)
                        For icol As Integer = 1 To xlRow.Cells.Count
                            xlCell = CType(xlRange.Cells(irow, icol), Excel.Range)
                            If xlCell.Value IsNot Nothing Then
                                lstRow.Add(xlCell.Value)
                            End If
                        Next

                        lstPlines.Add(lstRow)

                    Next
                    releaseObject(xlCell)
                    releaseObject(xlRow)
                    releaseObject(xlRange)

                    xlWorkBook.Close()

                    xlApp.Quit()

                    releaseObject(xlWorkSheets)
                    releaseObject(xlWorkSheet)
                    releaseObject(xlWorkBook)
                    releaseObject(xlWorkBooks)
                    releaseObject(xlApp)
                Catch ex As System.Exception

                    System.Windows.MessageBox.Show(ex.Message)

                End Try

                If lstPlines.Count > 0 Then
                    Dim doc As Document = Autodesk.AutoCAD.ApplicationServices.Application.DocumentManager.MdiActiveDocument
                    Dim ed As Editor = doc.Editor
                    Dim db As Database = doc.Database
                    Using tr As Transaction = db.TransactionManager.StartTransaction
                        Dim btr As BlockTableRecord = DirectCast(tr.GetObject(db.CurrentSpaceId, OpenMode.ForWrite), BlockTableRecord)

                        For Each lstCoords As List(Of Object) In lstPlines
                            Dim pline As New Polyline
                            Dim n As Integer = 0
                            Dim i As Integer = 0
                            For n = 0 To lstCoords.Count - 1 Step 2
                                Dim x As Double = Convert.ToDouble(lstCoords(n))
                                Dim y As Double = Convert.ToDouble(lstCoords(n + 1))

                                Dim pp As Point2d = New Point2d(x, y)
                                pline.AddVertexAt(i, pp, 0, 0, 0)
                                i += 1
                            Next
                            btr.AppendEntity(pline)
                            tr.AddNewlyCreatedDBObject(pline, True)
                        Next
                        tr.Commit()
                      System.Windows.MessageBox.Show("See result.")
                    End Using
                End If
            Else
                System.Windows.MessageBox.Show("'" & xlFileName & "' does not found.")
            End If
        End Sub
        Public Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.FinalReleaseComObject(obj)
                obj = Nothing
            Catch ex As System.Exception
                System.Diagnostics.Debug.Print(ex.ToString())
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub
End Namespace

Please, see about Excel VB.NET for more :
http://www.siddharthrout.com/tag/vb-net-and-excel/
http://code.msdn.microsoft.com/Basics-of-using-Excel-4453945d
In the attached picture is data used for this code
« Last Edit: April 20, 2013, 09:45:18 PM by fixo »