Export grouped wall data to Excel with group headers (Dynamo)

[1534745]

Hi everyone, I’m exporting wall data from Dynamo to Excel. I can group walls by wall type name (e.g., “Ladrillo 15 cm”, “Ladrillo 25 cm”), but in Excel the type name repeats on every row. How can I export the data so that each wall type appears only once as a “group header” row, followed by its rows of data (like a grouped report), without repeating the header on each row?I’d really appreciate it if the exported Excel output could match the attached screenshot

[sovitek]

Hello @1534745 and welcome to the community, could you show what you have tried in dynamo so far ?

[1534745]

1 - WALLS.dyn (121.4 KB)

Hi, I am extracting wall data and various parameters into Excel columns (as shown in my screenshot). I used List.GroupByKey to group the walls by wall type name, but I couldn’t add a single “group header” row for each name, because I want the result to look like this:

Brick 15 cm
(rows of walls)
Brick 25 cm
(rows of walls)
etc.

I am attaching my graph and a screenshot of the current Excel result; Any guidance would be greatly appreciated.

[jacob.small]

Why not build the header in excel via the tools there, and then hide the column? Pivot tables are a thing and since you’ve already committed to Excel (usually not ideal) there isn’t any penalty for doing so.

[1534745]

Thanks for the suggestion. In my case, I need the Excel output to come pre-formatted directly from Dynamo, since other people will use it and it will be run across multiple files. The goal is to save time by avoiding manual grouping/formatting in Excel, so I’m trying to generate the “group header” rows by wall type name in Dynamo.

[jacob.small]

I would do that this way (though the very use of Excel should be avoided as changes to the model makes the entirety of the Excel data invalid).

1. Export from Revit to CSV
2. Import the data using power query editor
3. Build a pivot table of the linked data and set the display as desired
4. Send the rest of the users the link to the pivot table

If you set that process up once, and just duplicate and repath the file you can manage multiple RVTs and exports concurrently without issue. Whenever needed the end users can update data by modifying the query - it’s no different from x-refs in Revit/AutoCAD, and whoever requested ‘Excel’ should be capable of repathing as needed.

The excel interop nodes do not allow grouping at this time, and any attempt at manipulating the lists to ‘get a look’ will result in data which doesn’t perform right. You’d have to do something silly like:

1. Add a ‘null’ in front of each sublist
2. Group the sublists by the type (GetItemAtIndex with the list set to L2)
3. Remove the type data from each sublist (RemoveItemAtIndex, again L2)
4. Insert the type as a ‘list’ containing just the type at the front of each sublist
5. Remove the extra list structure to get a 2d list again

[c.poupin]

Hi,
a solution with pandas and DataFrame.duplicated

import sys
import pandas as pd

columns = IN[0]
data = IN[1]
file_path = IN[2]

df = pd.DataFrame(data, columns=columns)

# .duplicated() keeps the first occurrence and returns True for all following repeats
df.loc[df['Material'].duplicated(), 'Material'] = ""

# export to Excel
df.to_excel(file_path, index=False)

OUT = repr(df),file_path

pandas.DataFrame.duplicated — pandas 2.3.3 documentation

[1534745]

Thank you so much! It was incredibly helpful.

[sovitek]

Hi @1534745 maybe something here could work as well, but absolutely not so elegant as @c.poupin panda solution…so great

[1534745]

Thanks, this option is easier because I don’t know much about programming.