Dynamo Scripting: Import layers from an Excel Spreadsheet into AutoCAD

During a recent services project working on a custom template, we received a request to generate a set of client specific layers based off of a listing provided in a spreadsheet.

Instead of typing these in, I thought this is a great automation task for Dynamo. 

Here’s how to do it:

1. Start with a blank template – layers 0 and Defpoints exist in the drawing. “Dynamo-LAYER TEST.dwg”.  Make sure that any linetypes called for in the spreadsheet do exist in the drawing.

2. Arrange the cells in the Spreadsheet:  See file “IMAGINiT-Proposed Layers.xlsx”  The columns are Layer, R,G,B, Linetype, Lineweight, Description, Color:

3. On the Manage Tab, Launch Dynamo and open the file: IMAGINiT Proposed Layers Import_Excel to Dynamo.dyn

4. This Dynamo Script is executed in 3 passes:

1. 1. The first task is to use a File Path node to allow browsing to a file:

5. After the Excel File is read by Dynamo, a ExcelFile.Read node is implemented.

Once the file is read, several List operations need to be performed:

A List.Transpose node is applied so we can get index items and filter / select entity groups.

6. These nodes grab the data from excel for creating layers, colors, linetypes and lineweights.

7. The 3 stages are grouped and executed in order.  Stages 2 and 3 are frozen.

8.  Stage 1 is executed which uses the Layer Create node.  This node uses a Color by ARGB node to initially create the layer colors.  The RGB portion is used and can be populated or not.  If there are no colors, it will create colors as 255,255,255.  After Stage 1 is executed, Freeze the Layer Create Node.

9. Now run Stage 2: Unfreeze Document.LayerByName  and Layer.SetColor nodes.  After Stage 2 is executed, Freeze the associated nodes again.

10. Now run Stage 3: Unfreeze the Layer.SetDescription node.  This node will read in the descriptions as long as the layers exist (which they should from Stage 1).  If there is an error regarding a layer not existing, save the dynamo script and re-open and run it.  Since Stage 3 is frozen, it retained some information from the first 2 stages that need to be reconciled with the last stage.

You can see from the screenshot above that the layers, index colors, linetypes, lineweights and descriptions have been created successfully.

You can now use this drawing in further development of CAD standards and template creation.

Important: Make sure that the linetypes in the Excel Spreadsheet existing in the drawing being used in the script.

About the Author

Infrastructure Applications Expert<br><br>Simmie has diverse experience in Civil Engineering design and Survey Mapping support since 1979. He has been involved in key projects from Civil Site, Subdivision, Rail Transit, Airports, Highway, Utility Infrastructure, and Utility Scale Solar Farm site layouts. His start with a small surveying firm as a drafter in Oakland, CA provided a good base for transitioning and becoming a designer / engineering technician utilizing Computer Aided Design software. He quickly learned proprietary computer engineering systems such as Wang – Holguin, Vango and MS Dos based AutoCAD which transitioned from SoftDesk to Land Development Desktop to Civil 3D. <br><br> Simmie has a solutions-based focus that has served his clients very well. The vast majority of projects he has been involved in have been permitted and built.

More Content by Simmie Graves