Happy Friday! A few weeks turned into a few months, but I’m so grateful I was able to take some time to focus on my watercolor class and keep painting for a bit after. It was so wonderful to see some familiar faces I hadn’t seen in a while and meet so many talented artists. I’m back to blogging and writing some code, but want to keep painting too so I’ll have a slightly adjusted schedule for posting.
This week I’m kicking things off again by taking a common Dynamo script (creating levels from an Excel spreadsheet) and walking through how you can write a Revit Plug-In in C# to complete this same task. The goal is to show how Dynamo nodes relate to coding and the Revit API. If you haven't built a Revit plug-in yet, I would recomend working through Autodesk's My First Revit Plugin to familiarize yourself with the process and setting up a new project in Visual Studio.
I am using a simple script that many people have made versions of for creating floor levels in Revit from an Excel spreadsheet. The script is roughly based on one of the scripts from ArchSmarter's article Save Time with Revit Dynamo.
The script has 3 basic parts:
In order to read an Excel file, you will need to have Excel installed on the machine you are writing your code on. You will also need to install and import the Microsoft Office Interop Excel Services using the Microsoft.Office.Interop.Excel NuGet package. I assigned it to a variable to make using it easier which I will show next week when I post all the code.
The first method we are going to write reads the Excel file and stores the data in lists. Our method won’t return anything but we will need the file location and sheet name as parameters. Additionally we will want one parameter for the level names and one for the level heights. All these parameters will be declared in our execute method which we will cover next week.
In order to launch Excel and read the file, we need to instantiate a new Excel application. This is the object which allows us to manage our Excel connection. Then, we open the file, read the sheet, and assign all cells with values to a new Excel Range object. We can then cycle over the data in the Excel Range object just as we would a list in Dynamo. The Excel Range has a Rows property which we can use to get the number of rows it has. This will tell us the total number of levels we are creating but will also include headers.
In order to get the information in the first column, we can set up a for-loop with a counter. Note that Excel Ranges are not zero indexed so we will want to use 1 to get the first row, 2 for the second, and so on. In this case, we can also assume our Excel file has headers and skip the first row so we will start at index 2. We first want to check if our cell has a value by using string.IsNullOrEmpty. If it has a value, we want to take that value and add it to our list of level names. We do the same thing for the second column but add it out our level heights list.
Then we need to complete some memory management and quit Excel. First, we deallocate memory, release the communications object to kill the Excel process that is running in the background, close and release the workbook, and finally, quit and release the application.
And that is how we can read an Excel file using code based on the first two parts of our Dynamo script. As you can see, some processes are simpler to execute like removing the header by starting at index 2 instead of 1 in our for loop. Others are more complex like opening the Excel file, managing memory, and keeping track of variables and outputs. Next week, I will dive into the Revit API and cover how to create levels as well as how to set up the execute method.