MEP BOM (Bill of material) to Excel

Hi,

I’m happy to share with the community a Script that I created for my needs.
I was boring to export the schedules of equipments and have to format them in a unique excel file to have my Bill of Material (BOM).

Before launching the script you need to open an emply excel sheet and to have at least one segment, one fitting and one accessory.
There is one script for pipes and one for ducts.

You can see the video of the job there (the comments are in french):
https://bimsuisseromande.ch/pyrevit-bom/

And you can find the Source code there :

Hope it can helps you.

Yoann

2 Likes

Hi @Yoann.Obry, Great work!

I assume you started developing with dynamo and stuck with the “operations on lists” paradigm, but may I suggest you to learn python dictionaries? With this data structure your code would be much simpler, no need to create multiple “parallel” lists, and keep track of the index to get the related items.

Even without the use of dictionaries, the loop on the lists can be simplified with for item in my_list, and if you have to use the items from mutliple lists you can use for item_1, item_2 in zip(list_1, list_2) and so on… If you really need the index, use for i, item in enumerate(my_list) instead of range(len(my_list)).

Other things I would change:

  • The loop to change None and the empty string to _N/A can be turned into a function that handles a single value (return "_N/A" if value in (None, "") else value) and called directly when you are adding the value to the list (or the dict if you follow that advice).
  • The loops for the rounding of the angle are terrible by a performance perspective, you loop through all the items 5 times when you can do it in only one pass! just use elif in the first loop to consolidate all the conditionals. Even better make the check a function that return the value and then use a simple list comprehension:
    def round_angle(value):
        if value < 15 or value > 95:
            return value # or throw an error if it's not allowed
        if value < 25:
            return 20
        if value < 35:
            return 30
        ...
        # last item doesn't need the check
        return 90
    
    PF_angle = [round_angle(angle) for angle in PF_angle]
    
  • To count the occurrences of the items in a list, you could use the collections.Counter class
  • you don’t need a transaction to write to Excel. Transactions are used to ad/remove/edit geometries and information in the revit detabase.
  • you call xlApp.Worksheets(worksheet) many times, why not saving it to a variable outside of the loop and use it instead?
  • is it range(len(circuit_unique))[k] just k?
  • I understand this code is made to be compatible with RevitPythonShell, but you could leverage the xlsxwriter package shipped with pyRevit to avoid depending on the Excel app to be installed (it is also faster). RevitPythonShell can be configured to load pyrevit libraries (of course, one needs to install pyrevit alongside rps for this to work)
  • in the same vein, most of the code that access the elements attributes can be simplified by using the pyrevit library.
  • circuit_unique = sorted(set(circuit_unique)) does in one line what you did in 3, and saves some memory
  • this is a coding style preference, but a code is more readable when you split it into functions that have a meaningful name (so there’s no need to read the entire code to understand what it does at first glance)

Please take my comments not as a judgement, but as an advice to improve constantly and learn how to write better code faster (knowing the right data structure/tool for the job lets you save quite some time!).

1 Like

Hi Andrea,

I really appreciate your long answer and I will take it in consideration to write my future scripts.

I learned RevitPythonShell when I began to use the Revit API and I’m very late with PyRevit because I only us it for my custom ribbon :slight_smile:

So I need an update of this skills. If you have a short code exemple how you would extract the elements attributes with pyrevit library using a Dictionnary it will be helpfull for me.

I’m using list, tupple, dict and funct in my 100% python project like on tkinter and I can find a lot of advices on the web. But this is more difficult to code on PyRevit / RPS because the debugger is “hidden”, i.e. the console doens’t show you the line of the error.

I have also some difficulties to find the name of the Python Revit commands. So I created this glossary PyRevit : Glossaire – BIM Suisse Romande to help the new coders (with my novice vision) but if you know where I can find a similar thing I’m interested !

Yoann

I’m sorry, I got confused with pyrevitlib facilities for retrieving the parameters, you’re already doing it the right way. (I’ve developed a shortcut in my own extension and I always think it is part of the pyrevit library :sweat_smile:)

What I meant for using dictionaries was to drop all the lists and having a single one with the name and values of the parameters read.
The following could be hard to read at first but bear with me…

from pyrevit.revit.db.query import get_elements_by_categories
from collections import Counter

# by convention, constants in python are SNAKE_UPPERCASE
#Shared parameter code circuit
CODE_CIR = Guid(r'55934d0c-0246-4ce2-9bdf-57ed4244e11b')
#Shared parameter FMF_Angle
ANGLE = Guid(r'a8b84336-4f16-462c-a50f-f0f8b2e4f7c2')

### DA : Création d'un BOM de DUCT ACCESSORIES sous forme de liste de tuple

# using pyrevit library to simplify element collection
# by convention, python variables are snake_case
das = get_elements_by_categories([BuiltInCategory.OST_DuctAccessory], doc=doc)

#Créer des listes vides
duct_accessories = []

for da in das:
	da_type = doc.GetElement(da.GetTypeId())
	# no need to create a separate functrion/loop to set _N/A on empty or null string
	code_circuit = da.get_Parameter(CODE_CIR).AsString() or "_N/A"
	family_name = da_type.get_Parameter(BuiltInParameter.SYMBOL_FAMILY_NAME_PARAM).AsString()
	description = da_type.get_Parameter(BuiltInParameter.ALL_MODEL_DESCRIPTION).AsString()
	size = da.get_Parameter(BuiltInParameter.RBS_CALCULATED_SIZE).AsString()
	duct_accessories.append(
		{
			"code_circuit": code_circuit,
			"family_name": family_name,
			"description": description,
			"size": size,
		}
	)

counter = Counter((tuple(i.items() for i in duct_accessories))
lst_da = sorted(
	({**dict(itm), "count": count} for itm, count in counter.items()),
	key=lambda x: x["count"]
)

The last 5 lines create a sorted list of the unique duct accessories dictionaries, eliminating the need to create a list with the code and the concatenation of the other fields, count the items one by one and then removing the duplicates.
There’s the need transform the dict to tuple and back because of how the Counter works; we could have created a tuple instead of a dictionary in the loop, but if we keep it like this we can extract these last line to create a generic count function that can be reused for any list of dictionaries (like the elements in the rest of the code)

def count(records):
	counter = Counter((tuple(r.items() for r in records))
	lst_da = sorted(
		({**dict(itm), "count": count} for itm, count in counter.items()),
		key=lambda x: x["count"]
	)

you can also extract the element parameter reading into its own function

def read_da_parameters(da):
	da_type = doc.GetElement(da.GetTypeId())
	# no need to create a separate functrion/loop to set _N/A on empty or null string
	code_circuit = da.get_Parameter(CODE_CIR).AsString() or "_N/A"
	family_name = da_type.get_Parameter(BuiltInParameter.SYMBOL_FAMILY_NAME_PARAM).AsString()
	description = da_type.get_Parameter(BuiltInParameter.ALL_MODEL_DESCRIPTION).AsString()
	size = da.get_Parameter(BuiltInParameter.RBS_CALCULATED_SIZE).AsString()
	return {
		"code_circuit": code_circuit,
		"family_name": family_name,
		"description": description,
		"size": size,
	}

and then the main code would look like

das = get_elements_by_categories([BuiltInCategory.OST_DuctAccessory], doc=doc)
duct_accessories = count((read_da_parameters(da) for da in das))

You then only need to create the read_xx_parameters for the other types and repeat these 2 lines with the right category and function (you could also parametrize those inputs to reach the ultimate DRY-Don’t Repeat Yourself, but that’s another story…).

Also note that I used generators wherever I could to avoid wasting memory with intermediate lists, since all these sequences are read only once.

The usual disclaimer is: I didn’t fully test the code, there might be some (many) errors in there…

1 Like

Took a quick look, already saw a not-so-good practice: if you want the element ids from a FilteredElementCollector, just use ToElementIds() instead of ToElements() and the loop to get the id from the elements :wink:

Did you take a look at the pinned quick start discussions in this forum? They contain a list of resources that could help, such as (but not limited to) the developer notes hosted on notion and the pyrevit lib reference api.

If you already view them or don’t find the right info, let us know what you think should be added to the documentation

Thank you very much for this very good explanations ! It will help a lot of people who want to extract datas in a better way.
And I think we can also use this tips in others softwares to create some ifc BOM for example.