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.

12 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.

  3. avatar David Robinson says:

    Hi Ryan,

    Thanks for this post.
    I am currently trying to replicate the graph however i am encountering a problem at the IndexOf node for the views element. Where you note above “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” I currently cannot adjust the input as required.

    Is this function only available in a later version than 0.9.1 and if so is there a workaround available?

    Thanks

    David

  4. avatar Ryan Lenihan says:

    Hey David, really sorry for not replying earlier, I’ve been travelling a lot for work and haven’t been keeping an eye on the site.

    I had been using v1.3 or so in my examples. list levels were introduced in Dynamo 1.2.

    If you have a look at the images in http://dynamobim.org/introducing-listlevel-working-with-lists-made-easier/ the previous way to do this was with List.Map. There are some before and after examples of using List.Map and then moving to using list levels so you’d be able to use that to see how to apply a List.Map node.

  5. avatar Jay says:

    hello,

    I tried doing the one above and i still get -1 on my list of index. Please let me know where i mess up.

  6. avatar Ronette says:

    Hello,

    Please let me know where i made the mistake. I’m wondering why i still get -1 on the list of index.

    Thank you

    https://www..co.uk/my/photo/full?photo_id=500826120927

  7. avatar Ryan Lenihan says:

    Hi, can you re-post the image to imgur or similar, the link you provided doesn’t work.

  8. avatar Ricardo Gutiérrez says:

    I do not have the node Manage.ReplaceNulls Where can I download it?

  9. avatar Ryan Lenihan says:

    Hi Ricardo,

    Sorry about that. The node is in the Lunchbox package.

  10. avatar Kairo Filipe says:

    Hello Ryan, thanks for the post

    I am trying to automatic generate my sheets, so I need to put more than one view in each sheet, that’s possible ?

    And as your next post, i want to replace my views in specific points on the sheet, So i can worry in the model and sheets automatically is generated.

    (Sorry for my english)

  11. avatar Ryan Lenihan says:

    Hi Kairo,

    You certainly can place multiple views on the sheet, you just need to do a little bit of extra work in the Excel file and then with the Dynamo graph so it knows what to do with the extra views. I made another post about doing this which you can find here https://www.revit.com.au/placing-multiple-views-sheets-dynamo/ let me know if that does the trick for you. if you’re still stuck or this doesn’t do quite what you want, let me know and i’ll try and help you out.

Leave a Reply to Ryan LenihanCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.