How to create table from excel data into Civil 3D using Dynamo script?

Civil3D
,
5

Hi
here 2 examples with Python

  • solution with OleDbConnection (work with all pyEngine but only on Civil3d)

# Load the Python Standard and DesignScript Libraries
import sys
import clr
import System
# Add Assemblies for AutoCAD and Civil3D
clr.AddReference('AcMgd')
clr.AddReference('AcCoreMgd')
clr.AddReference('AcDbMgd')
clr.AddReference('AecBaseMgd')
clr.AddReference('AecPropDataMgd')
clr.AddReference('AeccDbMgd')
import Autodesk
# Import references from AutoCAD
from Autodesk.AutoCAD.Runtime import *
from Autodesk.AutoCAD.ApplicationServices import *
from Autodesk.AutoCAD.EditorInput import *
from Autodesk.AutoCAD.DatabaseServices import *
from Autodesk.AutoCAD.Geometry import *

# Import references from Civil3D
from Autodesk.Civil.ApplicationServices import *
from Autodesk.Civil.DatabaseServices import *

clr.AddReference('System.Data')
from System.Data import *
from System.Data.OleDb import *

clr.AddReference("System.Reflection")
from System.Reflection import BindingFlags


def read_excel(fileName):
	dt = None
	connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=\"Excel 12.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text\""
	with  OleDbConnection(connectionString) as conn:
		conn.Open()
		sheets = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, ( None, None, None, "TABLE" ))
		with conn.CreateCommand() as cmd:
			cmd.CommandText = "SELECT * FROM [" + sheets.Rows[0]["TABLE_NAME"] + "] "
			adapter = OleDbDataAdapter(cmd)
			dt = DataTable("DataXls")
			#adapter.Fill(ds)
			adapter.GetType().InvokeMember("Fill", BindingFlags.InvokeMethod , None, adapter, (dt,))
		return dt

adoc = Application.DocumentManager.MdiActiveDocument
editor = adoc.Editor

dataTable = read_excel(IN[0])

with adoc.LockDocument():
    with adoc.Database as db:

        with db.TransactionManager.StartTransaction() as t:
            # Place your code below
            # 
            pr =  editor.GetPoint("\nEnter table insertion point: ")
            if pr.Status == PromptStatus.OK:
                tb = Autodesk.AutoCAD.DatabaseServices.Table()
                tb.TableStyle = db.Tablestyle
                tb.NumRows = dataTable.Rows.Count
                tb.NumColumns = dataTable.Columns.Count
                # tb.SetRowHeight(3)
                # tb.SetColumnWidth(15)
                tb.Position = pr.Value
                for i in range(dataTable.Rows.Count):
                    for j in range(dataTable.Columns.Count):
                        tb.SetTextString(i, j, dataTable.Rows[i][j])
                tb.GenerateLayout()
                #
                bt = t.GetObject(adoc.Database.BlockTableId,  OpenMode.ForRead)
                btr = t.GetObject(bt.get_Item(BlockTableRecord.ModelSpace), OpenMode.ForWrite)
                btr.AppendEntity(tb)
                t.AddNewlyCreatedDBObject(tb, True)
            # Commit before end transaction
            t.Commit()
# Assign your output to the OUT variable.

OUT = 0
  • solution with pandas (work only with CPython3)

# Load the Python Standard and DesignScript Libraries
import sys
import clr
import System
# Add Assemblies for AutoCAD and Civil3D
clr.AddReference('AcMgd')
clr.AddReference('AcCoreMgd')
clr.AddReference('AcDbMgd')
clr.AddReference('AecBaseMgd')
clr.AddReference('AecPropDataMgd')
clr.AddReference('AeccDbMgd')
import Autodesk
# Import references from AutoCAD
from Autodesk.AutoCAD.Runtime import *
from Autodesk.AutoCAD.ApplicationServices import *
from Autodesk.AutoCAD.EditorInput import *
from Autodesk.AutoCAD.DatabaseServices import *
from Autodesk.AutoCAD.Geometry import *

# Import references from Civil3D
from Autodesk.Civil.ApplicationServices import *
from Autodesk.Civil.DatabaseServices import *

import pandas as pd


adoc = Application.DocumentManager.MdiActiveDocument
editor = adoc.Editor

df = pd.read_excel(IN[0], header=None)
df = df.fillna('')

with adoc.LockDocument():
    with adoc.Database as db:
        with db.TransactionManager.StartTransaction() as t:
            # Place your code below
            # 
            pr =  editor.GetPoint("\nEnter table insertion point: ")
            if pr.Status == PromptStatus.OK:
                tb = Autodesk.AutoCAD.DatabaseServices.Table()
                tb.TableStyle = db.Tablestyle
                tb.NumRows = len(df)
                tb.NumColumns = len(df.columns)
                # tb.SetRowHeight(3)
                # tb.SetColumnWidth(15)
                tb.Position = pr.Value
                for i in range(len(df)):
                    for j in range(len(df.columns)):
                         tb.SetTextString(i, j, df.iloc[i,j])
                tb.GenerateLayout()
                #
                bt = t.GetObject(adoc.Database.BlockTableId,  OpenMode.ForRead)
                btr = t.GetObject(bt.get_Item(BlockTableRecord.ModelSpace), OpenMode.ForWrite)
                btr.AppendEntity(tb)
                t.AddNewlyCreatedDBObject(tb, True)
            # Commit before end transaction
            t.Commit()
# Assign your output to the OUT variable.

OUT = repr(df)

be careful the line in the header of the Autocad table corresponds to line 0 so it may be necessary to add a blank line at the top of the Dataframe or Datable