After Dynamo is used to collect and analyze information, a common final step is to report the data in the form of a table in an Excel spreadsheet. In this video, we will construct a nested list of numerical and text data that is properly structured for export, then write the table to a new tab of an Excel spreadsheet.
- [Voiceover] After Dynamo is used to collect and analyze information, a common final step is to report the data in the form of a table in an Excel spreadsheet. In this video, we'll construct a list of data that's structured properly for export to Excel, and then we'll write that data to a new Excel spreadsheet. In a previous example, we've imported data from an Excel spreadsheet and used some common Dynamo nodes for managing nested lists, including the Transposed node, which grouped each retail shelf's type together by their properties rather than the shelf type. In this video, let's solve an example problem.
Let's start by pulling the lists that report each shelf type's width and its height. Then, let's multiply those values together to find out each shelf's total wall area, which is a brand-new list of information that we didn't originally bring in from Excel. We'll need to begin by extracting the right lists of information from our List Transpose node. In the video when we first imported this data from Excel, we used the First Item and the Rest Of Items as a way of separating the header row from the rest of the actual data. Let's take a look at the node previews for each.
In particular, we're interested in the width and the height properties for this example. If we look at the data coming out of the First Item node, we can see that the width information can be found at Index Two, and the height, Index Three. Taking a look at the data coming out of the Rest of Items node, this means that each sublist's Item Two correlates to the width that we see in the first item, and Index Three of each sublist will correlate to the height parameter. Now, if you remember from our last video, List Transpose is a way of taking all of the sublists' index and reorganizing them.
So, what we have here at Index Zero is Index Zero of each sublist: S1, S2, P1, so on and so forth. What that means is Index Two, coming out of the List Transpose node, should be each shelf type's width in inches, and in the same way, Index Three would be each shelf type's height in inches. Now let's use our Get Item At Index node to extract only those two lists, Index Two and Index Three, for the width and the height of each shelf type.
I'm going to place two Get Item At Index nodes onto our workspace. We'll plug the List Transpose node into the List Input, and we'll want to plug in the number two for one of these indexes, and three for the other. For that, we'll need a Number Input node. I'll change this one to two, and we'll set this one at three. So, there, we've now extracted a single list of only the information that we're interested in: the width and the height of each shelf type.
Our next step is to multiply these two lists together, in order to find the total wall area of each shelf type. We can use a Multiplication node to do this, which we can find in the Operator section of the Node Library. Here, we'll plug the data that represents each shelf type's width into X and each shelf type's height into Y. Taking a look at the node preview coming out of the Multiplication node, it seems that each of our nine items are multiplied together into a list of nine new items.
Now, because we're reporting our width and our height in inches, we're seeing here the overall wall area in square inches. Usually, square inches aren't used for area measurements, so let's divide each one of these items by 144, which is the conversion factor from square inches to square feet. We could do that with the Division node. I'll copy one of our existing Number nodes using Ctrl + C and Ctrl + V, I'll change that value to 144, and we'll plug it into our Y input.
And there, that's looking a lot more manageable. Now we're seeing each shelf type's wall area in square feet, instead of square inches. Let's take a quick step back for a second and make an observation about how Dynamo is handling our lists. Notice how, for our Multiplication node, we've provided two lists as the inputs, whereas for our Division node, we provided one list as the X input, and a single number as the Y input. By default, if you provide a node with lists of the same length as inputs to the same node, Dynamo will perform the function by matching up the data by index, meaning, in this case, Dynamo multiplied Index Zero by Index Zero to produce that product.
And then it went down the list, multiplying Index One by Index One, and Index Two by Index Two. But, in the Division node, it only had a single value to work with, plugged into the Y input, so it automatically reused that value for every item in the list plugged into X. There are ways of changing how Dynamo matches data between lists, which we'll look at in more detail in another video, but it's a good concept to understand any time that you're working with lists. For the last step, we'll write this new data that we've generated to our Excel file.
Let's begin by dropping the Excel Write To File node onto our canvas, which we found last time just by searching for Excel. We need to specify a few inputs, particularly the location of the Excel file, the name of the sheet, which are both familiar from when we were reading an Excel file, but this time we'll also need to specify the start row, the start column, and the data that we're interested in writing. Let's go ahead and drop in a File Path node, as a way of specifying the location of our Excel file. Dynamo sometimes runs into problems when we're trying to read and write data to the same Excel file in the same graph, so let's create a copy of the Excel file that we've been reading, called Retail Shelf Types 2.
We can go ahead and plug that into File Path. Let's pull up our new Excel file and look at it, side-by-side with our Dynamo graph. We're interested in adding our new column of wall area data, beginning in cell F2, and then continuing down through F10. Let's plug in the rest of the inputs to the Excel Write node. Our active worksheet is called Sheet1, so we can plug that in as a string to the sheet name. I'll search for a new string input, and type Sheet1.
Now for our start row and our start column. If we hover over the input preview, you can see that Dynamo expects an integer for both the start row and for the start column. The row that we're interested in writing our data to, our start row, would be row two. However, if you remember, Dynamo starts counting with the number zero instead of one, unlike Excel, so in our case, one in Excel equals zero in Dynamo, so row two, which is what we're after, would actually be number one in Dynamo.
Let's plug in a number one to the start row. I'll drop in a new number input, and we'll change that number to a one. In the same way, our start column also requests an integer, so instead of typing in the letter F, we'll need to count over from the very first row. A would equal zero, B would equal one, C two, three, four, five. Let's Copy/Paste this Number node, replace it with the number five, and plug it in to our start column.
Let's move these up a little bit to keep our graph looking tidy. Now, our last step is to plug in our new data, which is coming out of our Division node. Let's plug that in to the data input in Excel Write. And, as you can see on the left-hand side, Dynamo has automatically written this data to the right location in Excel. Now, if we wanted to import multiple columns of data into Excel, we would need to provide a nested list into our data input, rather than a single list, like we just exported. With a little bit of planning, and some list management, we're now successfully able to export our Dynamo data out to Excel.
This is a great way to print reports and visualize the data that you're working with in Dynamo, perhaps using Excel's built-in graph and chart tools, or even just displaying your data in a tabular way.
- Placing and connecting Dynamo nodes
- Understanding Dynamo's data types
- Performing math functions
- Creating number lists and text strings
- Writing data to an Excel spreadsheet
- Creating points, curves, surfaces, and solids
- Analyzing geometry
- Linking a Dynamo-driven SAT into Revit
- Placing Revit families with Dynamo
- Creating Revit views and sheets with Dynamo