create levels from Excel part 1 (Dynamo to C#) link

May 14, 2021

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.

Dynamo script

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:

• Determine the Excel file’s path and read the file
• Clean up the information from the file
• Create the levels and adjust the name of the levels

Excel file

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.

private void ReadExcel(string fileLocation, string sheetName, List<string> levelNames, List<string>levelHeights)     {         Excel.Application xlApp = new Excel.Application();         Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileLocation);         Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[sheetName];         Excel.Range xlRange = xlWorksheet.UsedRange;         int rowCount = xlRange.Rows.Count; }

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.

for (int i = 2; i <= rowCount; i++)         {             if (xlRange.Cells[i, "A"] != null && !string.IsNullOrEmpty(xlRange.Cells[i, "A"].Text as String))             {                 levelNames.Add(xlRange.Cells[i, "A"].Text as String);             }         }         for (int i = 2; i <= rowCount; i++)         {             if (xlRange.Cells[i, "B"] != null && !string.IsNullOrEmpty(xlRange.Cells[i, "B"].Text as String))             {              levelHeights.Add(xlRange.Cells[i, "B"].Text as String);             }         }

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.

// cleanup to deallocate memory         GC.Collect();         C.WaitForPendingFinalizers();         // release com objects to kill the excel process running in the background         Marshal.ReleaseComObject(xlRange);         Marshal.ReleaseComObject(xlWorksheet);         // close and release         xlWorkbook.Close();         Marshal.ReleaseComObject(xlWorkbook);         // quit and release         xlApp.Quit();         Marshal.ReleaseComObject(xlApp);

Summary

//gets data from excel and updates the lists that are storing the level names and the level heights private void ReadExcel(string fileLocation, string sheetName, List<string> levelNames, List<string>levelHeights) { Excel.Application xlApp = new Excel.Application(); Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(fileLocation); Excel._Worksheet xlWorksheet = xlWorkbook.Sheets[sheetName]; Excel.Range xlRange = xlWorksheet.UsedRange; int rowCount = xlRange.Rows.Count; //add level names to list //start a i=2 to skip the first row which will be the header, excel ranges are not zero based for (int i = 2; i <= rowCount; i++) { if (xlRange.Cells[i, "A"] != null && !string.IsNullOrEmpty(xlRange.Cells[i, "A"].Text as String)) { levelNames.Add(xlRange.Cells[i, "A"].Text as String); } } //add level heights to list for (int i = 2; i <= rowCount; i++) { if (xlRange.Cells[i, "B"] != null && !string.IsNullOrEmpty(xlRange.Cells[i, "B"].Text as String)) { levelHeights.Add(xlRange.Cells[i, "B"].Text as String); } } //cleanup to deallocate memory GC.Collect(); GC.WaitForPendingFinalizers(); //release com objects to fully kill excel process from running in the background Marshal.ReleaseComObject(xlRange); Marshal.ReleaseComObject(xlWorksheet); //close and release xlWorkbook.Close(); Marshal.ReleaseComObject(xlWorkbook); //quit and release xlApp.Quit(); Marshal.ReleaseComObject(xlApp); }

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.