Practical Dynamo – Generate Sheets from Excel

I was discussing Dynamo workflows with good old Captain BIMCAD the other night and we got to the topic of project setup.

Personally I don’t use Dynamo in my everyday project setup workflow, I use Ideate BIMLink, Omnia Scope Box Synchroniser and Sheet Duplicator but if you don’t have access to this software; especially BIMLink as it’s a bit pricey, Dynamo is definitely a viable option. Here’s how to get it done.

First we need to create a list of sheets in Excel with Name and Number information. Starting with a blank workbook in Excel, create a list with sheet numbers in column A and sheet names in column B.

From here we need to generate new sheets with this Excel data. Don’t forget the File.FromPath node, you can not feed the File Path node directly into the Excel.ReadFromFile node. Note that the name of the sheet in the Excel workbook is case sensitive. You can click on the image to view in full size


 

The next step is to remove the headers from our Excel file. They’re useful to us as it makes the Excel file more readable, however they need to be removed when used in Dynamo.

To achieve this we’re doing to use 2 nodes, List.FirstItem and List.RestOfItems.

 

Next we need to transpose our list so that we can feed in our sheet details into the sheet creation node. You can see once we run the list through the List.Transpose node that we now have a list of sheet numbers and a list of sheet names which sets us up for our next step.

Most of the magic happens at the next node which is the Sheet.ByNameNumberTitleBlockAndView node.

For the node to work, we need to input the sheet name, sheet number, the titleblock family which you can see how we achieve this in the next screenshot.

While you’ve been reading, I’ve taken it upon myself to generate some views in our model and add them to our original Excel file.

We can copy what we’ve already created in Dynamo for the sheet names and numbers and we simply take index 2 from the list, giving us the view names. Note that these will be case sensitive.

The next step is to actually find those views in the model to drop onto the sheets. We do this by creating a list of all the views within the model. Take the Categories node and select Views from the drop down, feed this into the All Elements of Category node and then finally feed this into an Element.GetParameterValueByName node. For the parameter name, we want to get the value for the View Name parameter.

From here we need to search the list of view names in Excel with the list of view names in the model. To do this, use an IndexOf node.

When you run this though, you’ll end up with a result of -1 instead of a list of indices. To fix this, change the level of list in the node. To do this, click on the right arrow on the element input of the node, select Use Levels and select @L1. Run the graph again and you’ll see the list of indices.

But what happens if you have a model where you don’t have the views setup yet? In our example we don’t have a view for the cover sheet or site plan yet which is why the view name is represented as null. You can see that the null view names give a -1 index result. If we feed this data into the Sheet.ByNameNumberTitleBlockAndView node as it is, it won’t create the sheets with the null views.

You can still use the same node, but there is a trick to it.

First, grab the Manage.ReplaceNulls node. Feed the list for views into the data section.

Next, create an empty drafting view, I’m just going to leave mine as the default Drafting 1. Feed the ReplaceWith input of the Manage.RemoveNulls node with the string Drafting 1.

Now when we search our views in the model, we’ll have the correct indices returned.

But hold on there a minute! We can’t drop drafting views on multiple sheets, how is this even going to work? To be honest, I’m not quite sure why but if you feed an empty drafting view into the Sheet.ByNameNumberTitleBlockAndView node it will generate an empty sheet. Whatever the reason, that’s a win for us!

Simply feed Manage.ReplaceNulls into the Sheet.ByNameNumberTitleBlockAndView node and we’re done!

 

If you’ve had automatic run selected, you’ll have a nice set of shiny new sheets created, otherwise simply click run and watch the magic happen.

Blink and you’ll miss it!

The end result. Click the image for the full resolution version.

2 thoughts on “Practical Dynamo – Generate Sheets from Excel

  1. avatar Andy1912 says:

    Hi mate,
    This is a great post and i’ve been trying to replicate it. But keeping getting 2 errors;
    1. 1 of my views has a 0 in the Indexof and it won’t actually populate on the sheet.
    2. I can’t populate empty views with it, the indexoff with @L1 ends up not filter thru.

    Greatly appreciate any guidance on this.
    Thanks
    Andy

  2. avatar Ryan Lenihan says:

    Hi Andy,

    Off the top of my head, thinking of the area you’re talking about, this might be what you need to do


    (right click and select open image to see full size)

    But it’s hard to tell without being able to see how you’ve connected your nodes. Can you take a screenshot of the areas that you’re having trouble with? It will be easier to troubleshoot.

    With the empty views/sheets, make sure you have an empty drafting view created that matches the name of what you’re searching for, i.e. Drafting 1. I forget to make sure there is an empty drafting view on maybe 8 out of every 10 models.

Leave a Reply

Error: Please enter a valid email address

Error: Invalid email

Error: Please enter your first name

Error: Please enter your last name

Error: Please enter a username

Error: Please enter a password

Error: Please confirm your password

Error: Password and password confirmation do not match