Organize and export sublist for Excel export?

Hi,
I’m trying to create and export a schedule for wall panel sets to Excel.
I’m able to export data of a single panel set(flatten list) without any problems. but how can I export data of sublist and have them organized that way I want in excel(see attachment)? what list logic do I need to get the result I’m looking for?
Thanks.


Data is sorted by row. So for a list of lists, each sublist represents a single row with each item in that sublist representing a new column.

Yes,I got the logic by transpose a flatten list. but have the problem when deal with the sublists. I’m playing with the lacing but doesn’t seem work.

Then you need a sheet name for each set of sublists.

@glenncai Just to be clear, your Levels column is staying the same, so does this mean that the # of items per Panel are == to the # of Levels?

Also, are these elements storing in a parameter the panel group they belong to?

Hi,awilliams
Question #1: Yes, the Levels column are always same,one level one item.
Question #2: Yes, each item has a parameter to id the panel its belongs to. and the sublist result has been grouped and sorted by that parameter.
Let me know if you come up will any ideas. Thank you for your reply

@glenncai Glad to hear the answers were both yes :slight_smile: Since question #1 was yes, no need to get the Levels from the element’s parameter values, just collect the level elements from the project. Since question #2 was yes, use Group by Key to group your elements by their panel parameter value, then List.Map to get the Steel Grade & Type Parameter Values per each Panel group. Then separate each panel group’s parameter values using Get Item at Index.

(I am using “Unconnected Height” and “Wall Type” where you would use your Panel and Type parameter)

1 Like

I think I see what you’re saying now. So each set of sublists is a panel. You can export a single panel set but you want to export multiple panels side by side with only one column for Level…

If that’s the case you’ll need to sort your panel information without Levels first. You want all your Level 28 info on one line then Level 27 info on the next and so on. Excel only has 2 dimensions (rows and columns) so your lists have to be 2-dimensional (per sheet).

Assuming your lists are already sorted by level, you need to do some fancy list transposals to get your items in the correct order. Then you can just add your unique levels back to the front of each list.


Export (without the column headers, but you get the point :wink: )
image

2 Likes

Looks like @Nick_Boyts’ method works as well! I realized after reading his solution that you probably still do want to be pulling the Level value from the elements. So a revised version of the method I gave above would be to first group the elements by their levels, and also include getting their level parameter with List.Map

@Nick_Boyts’ method makes more sense to use @glenncai considering your script is already set up with that list structure. :slight_smile: My method would rework the way you collect your values to end up with a different list structure.

Hi, Nike
That works awesome, but I need to add one more transpose to allow the panels reads from left to right. Sorry I did not explain it clearly. Thanks


Hi, Awilliams
Thanks for your help too

1 Like

You’re saying want the P1, P2, P3 and P4 to read left to right, do you mean you want them to be their own row rather than a column, basically as headers? Right now your data is presenting the data per each panel from left to right, so i’m assuming you mean you want the panels as headers.

If I’m understanding correctly, then I would remove the first List.Create node and feed the list you were putting in for item 1 into the first List.Transpose. Then add this line of code into a code block and feed it into your “List.AddItemsToFront” node and see if this gives you the results you’re looking for:

DSCore.List.Transpose({{"Level"},{"P1"},{" "},{"P2"},{" "},{"P3"},{" "},{"P4"},{" "}});

I cycle the repeat headers to # of panels happens and add “Level” to the beginning. This give me the result I want. I do not need to put P1,P2… for the each sub header for now, but would like to group “Steel Grade” & “Type” into a main upper level header call “P1,P2,P3…and so on” just like the image shown on the first post. not sure Dynamo allows me to merge cells in excel.

@glenncai You will have to merge cells in Excel after you’ve written the data to the file. Dynamo can’t control for merging cells to my knowledge. Try the following though, and it will give you outputs you can quickly merge the cells to reach your desired result.

Add a Code Block with the following code:
DSCore.List.Transpose({{" "},{"PANEL 1"},{" "},{"PANEL 2"},{" "},{"PANEL 3"},{" "},{"PANEL 4"},{" "}});

Add another List.AddItemToFront node after your first one, and feed the above code block into the “item” input and feed the output from the first List.AddItemToFront node into "list.

Try that and let me know if this is more like the results you are looking for. :slight_smile:

1 Like

If excel can do something programmatically than Dynamo Excel can do it as well… That said this may not yet have been exposed in any availabe package. See here:

Hi, I’m new here for Dynamo. I’m in a similar situation but simpler than the OP’s. I don’t need to have each set of sublist next to each other. I need each sublist to be in an individual sheet with the panel name is the sheet name.
So, panels bearing the same name (on different levels and can have different thickness) will be sorted by levels, and listed in a separate sheet.
The exported Excel file will have “n” sheets represent “n” panel names. Each of sheet will show those panels’ parameters such as thicknesses (type parameter), concrete grades, reinforcement rates (instance parameter)
Then another script to read the modified Excel file and change those panels’ parameters in Revit accordingly.
This is my very first Dynamo script, so please help me.
Thank you.

It’s a slightly different problem so I would ask that you start a new post. However, there are many similar posts with writing to multiple sheets in Excel that you could probably look at first. I would recommend getting your graph to work for a single panel before moving on to multiple panels.

Thanks. I’ll give it ago.
I’m trying to understand all the posts in this thread but each graph is not finished and I can’t link them together to get a full picture. It’s so difficult for me to understand what you guys were trying to do.
Can anyone provide a full graph for this panel thing which works? That would help me to learn it.
Cheers,