BlenderBIM spreadsheet writer for .xlsx and .ods

C by Coen on 26 Dec 2021, edited 1 Nov 2024 # +6 votes

For I begin: I decided to make a new thread, because I completely reorganized my repo, it was full of dead links and a complete mess. Hope that's okay.

I have been asking a lot of question on this forum recently and received a lot of help and good tips. This is very much appreciated. I made an add-on for BlenderBIM which creates an Open Office XML file which can be used to filter the IFC elements in Blender.

Here is an installation instruction for Windows.

And here is a video :

If you have the time to test drive it and give me feedback we could make this a very useful add-on I think.

2022-01-31 Edit discussion name for accuracy

G by Gorgious on 27 Dec 2021, edited 27 Dec 2021 # +4 votes

Nice ! Happy to see your work evolving and growing :)

A few python tips I can offer if you're open to it, otherwise feel free to ignore :)

In Python there is not need to test if var == True: since if var: is automatically evaluated to True or False depending on the value of var (in most situations at least). Similarly an empty list returns False when boolean tested : if len(my_list) == 0: is the same as if not my_list:.

You should use if var is None instead of if var == None see https://stackoverflow.com/a/3257957

Now that you've figured out how to store variables on the scene properties, you can consider also using scene properties for excel_file and sheet_name_custom so that they are not global and they are kept in memory in between sessions.

You're generating all the data lists regardless of what the user selected or unselected before exporting. You may optimise the script by only fetching the data when you test if each individual data headers have been selected. https://github.com/C-Claus/BlenderScripts/blob/d64923a166dbd442f2bcefd7a3a6a2b572b99626/BlenderBIMOpenOfficeXML/BlenderBIMOpenOfficeXML.py#L90-L167

You have some rogue lines of codes here : https://github.com/C-Claus/BlenderScripts/blob/d64923a166dbd442f2bcefd7a3a6a2b572b99626/BlenderBIMOpenOfficeXML/BlenderBIMOpenOfficeXML.py#L253-L254

Here I think you can use elif instead of if https://github.com/C-Claus/BlenderScripts/blob/d64923a166dbd442f2bcefd7a3a6a2b572b99626/BlenderBIMOpenOfficeXML/BlenderBIMOpenOfficeXML.py#L300-L304

Instead of chaining if statements you can consider using and boolean operator, eg https://github.com/C-Claus/BlenderScripts/blob/d64923a166dbd442f2bcefd7a3a6a2b572b99626/BlenderBIMOpenOfficeXML/BlenderBIMOpenOfficeXML.py#L322-L325 If the line is too long you can escape and continue writing on the next one with \

else: pass can be removed since it does nothing, but I presume it's something you added while debugging the script ?

Usually it's good practice to unregister classes in the reversed order from which they have been register ed, since for instance a panel may reference an operator so the operator has to be registered before the panel, and the panel has to be unregistered before the operator. All in all it's not really important but it might prevent some warnings in the console when enabling/disabling the add-on.

Cheers !

D by duncan on 27 Dec 2021 # +1 votes

Hi @Coen great project you've got happening there.

It would be great if you used the Open Document Format which is an ISO standard developed out of the free software / opensource movement. You can read about Open Document Format on Wikipedia and at the OASIS project page. The format you've used there is xlsx which is the MS Office Open XML - it has nothing at all to do with OpenOffice and is a widely criticized file format, you can read more about it why it was criticized.

C by Coen on 27 Dec 2021 #

@Gorgious

Thank you very much for the code review, much appreciated!

In Python there is not need to test if var == True: since if var: is automatically evaluated to True or False depending on the value of var (in most situations at least). Similarly an empty list returns False when boolean tested : if len(my_list) == 0: is the same as if not my_list:.

Changed it everywhere in the scripts from == to is where applicable.

You're generating all the data lists regardless of what the user selected or unselected before exporting. You may optimise the script by only fetching the data when you test if each individual data headers have been selected.

This is a very good point and I didn't think of this. I now have put an if statement for each check so it doesn't construct the entire pandas dataframe. Though not very elegant but effective and easy readable

You have some rogue lines of codes here :

Removed the lines of code

Still need to implement the other suggestions.

@duncan

It would be great if you used the Open Document Format

I didn't know that, thanks for the heads up. To be honest, I never bothered to open a .xlsx file with notepad++. I always made the assumption they were .xml files.... it's not open source indeed and the name of the application is now misleading and wrong at the moment.

I looked into creating a .ods file

I found with pandas you can do the following:

writer = pd.ExcelWriter(excel_file, engine='odf')

But the whole code relies heavily on xlsxwriter. So that might be some work before I am able to create .ods files. I found this Python module to create .ods files. But it's very light and I can't find a method to make a table or a filter. Which is quite essential to the add-on.

N by Nigel on 27 Dec 2021 #

Hi @Coen

I like the Spreadsheet Writer Addon, would it be useful to add a warning that 'Cannot write IFC data because file is already open' with options to write new with appended filename, what do you think?

C by Coen on 27 Dec 2021 #

@Nigel

Yes, that's a good idea. I will implement that as well. I also like the name Spreadsheet Writer for the add-on.

N by Nigel on 27 Dec 2021 # +6 votes

finally, I am of some use to the OSArch community! haha

N by Nigel on 27 Dec 2021 # +2 votes

Would it be possible to add search & list all the Psets found in the current IFC (including custom Psets) then the user could tick/select what they require. In time 'favourites' feature could be added for when the user repeats the same operation on different IFCs.

C by Coen on 28 Dec 2021, edited 28 Dec 2021 # +3 votes

@Nigel said:

Would it be possible to add search & list all the Psets found in the current IFC (including custom Psets) then the user could tick/select what they require. In time 'favourites' feature could be added for when the user repeats the same operation on different IFCs.

I like this idea as well. I started very simple. The user can now export their own Property by typing their own PropertySet.PropertyName. I saw this in the IFCCSV repo that they used a . to seperate the PropertySet from the Property.

Checking in another IFC viewer to validate.

I've written this function for this, it made me think I need to refactor all the other functions to make them more robust and implement the tips from Gorgiuos


    def get_custom_pset(self, context, ifcproduct, pset_name, property_name):




        custom_pset_list = []



        if ifcproduct.IsDefinedBy:        

            for ifcreldefinesbyproperties in ifcproduct.IsDefinedBy:

                if (ifcreldefinesbyproperties.is_a()) == 'IfcRelDefinesByProperties':

                    if ifcreldefinesbyproperties.RelatingPropertyDefinition.is_a() == 'IfcPropertySet':

                        if pset_name in (ifcreldefinesbyproperties.RelatingPropertyDefinition.Name):

                            for ifcproperty in (ifcreldefinesbyproperties.RelatingPropertyDefinition.HasProperties):



                                if (ifcproperty.Name == property_name):

                                    custom_pset_list.append(ifcproperty.NominalValue[0])





        if not custom_pset_list:

            custom_pset_list.append(None)



        return custom_pset_list

I need to figure out how to the user can add multiple custom propertyset and properties.

V by vpajic on 29 Dec 2021 # +1 votes

@Coen have a look at the ifccsv module again, you’ll see there how multiple properties can be added/deleted using collection properties. https://docs.blender.org/api/current/bpy.props.html#collection-example

D by duncan on 3 Jan 2022 #

@Coen said:

...

I didn't know that, thanks for the heads up. To be honest, I never bothered to open a .xlsx file with notepad++. I always made the assumption they were .xml files.... it's not open source indeed and the name of the application is now misleading and wrong at the moment.

As long as a switch gets onto the roadmap that would be great. Of course the earlier the better, but I'm an advocate not a developer, so I can only provide opinions.

M by Meetlat on 4 Jan 2022 #

Hi @Coen just wanted to let you know that installing modules with your script on blender installed with the windows store and opened as an admin is not possible. Windows store has the advantage that a user has always the latest version and don't need an admin/it-department to install the software for them. I've managed to get it working by installing blender regular, without the store. But perhaps there is a way to manual install the modules?

C by Coen on 4 Jan 2022, edited 6 Jan 2022 #

@Meetlat said:

Hi @Coen just wanted to let you know that installing modules with your script on blender installed with the windows store and opened as an admin is not possible. Windows store has the advantage that a user has always the latest version and don't need an admin/it-department to install the software for them. I've managed to get it working by installing blender regular, without the store. But perhaps there is a way to manual install the modules?

Thanks for the feedback, someone I know also used the Windows Store to install Blender. The exact same problem as you describe now. Windows need admin rights because it's downloading and installing dependencies. It also doesn't work from a Windows User account, blender console will give a [win5error].

I am going to research on how to fix it, one workaround could be to ship the python dependencies with the add-on in the zip file. If anyone has any better ideas. Please let me know.

C by Coen on 7 Jan 2022 #

@Meetlat

Could you please test and verify this works when Blender is installed with Microsoft Store? I updated the .zip to include the dependencies

https://github.com/C-Claus/BlenderScripts/blob/master/BlenderBIMOpenOfficeXML/BlenderBIMOpenOfficeXML.zip

M by Meetlat on 8 Jan 2022 # +1 votes

Yeah seems to work! I've deleted the appdata folder to make sure I started with a fresh blender. Installed it from the store and installed both plugins without any problem. Thanks! Although the add-on manager does gives a (unnecessary) warning? See:

C by Coen on 8 Jan 2022 # +1 votes

@Meetlat said:

Yeah seems to work! I've deleted the appdata folder to make sure I started with a fresh blender. Installed it from the store and installed both plugins without any problem. Thanks! Although the add-on manager does gives a (unnecessary) warning? See:

Thank you very much for testing, the warning is indeed unnecessary. I will remove it.

M by Moult on 10 Jan 2022 # +1 votes

This looks awesome @Coen! It looks quite similar to IfcCSV - in that it goes from IFC->Spreadsheet, with the added awesome graphical filtering.

What other differences are there to this and IfcCSV? Would you be interested in integrating the code? Perhaps adding ODS and XLSX support to IfcCSV?

C by Coen on 10 Jan 2022, edited 10 Jan 2022 #

@Moult said:

This looks awesome @Coen! It looks quite similar to IfcCSV - in that it goes from IFC->Spreadsheet, with the added awesome graphical filtering.

What other differences are there to this and IfcCSV? Would you be interested in integrating the code? Perhaps adding ODS and XLSX support to IfcCSV?

It is very similar to IFCCSV and I got some help from @vpajic . Like you said the main difference is graphical filtering. I got help from @Gorgious with the filtering and writing the add-on.

I think the differences to this and IFCCSV are the following:

Extended Graphical User Interface with check boxes for the most common property sets, so users can easily make a filtering on what they want to export. With ease of use in mind.
The graphical filtering like you mentioned.
I use the pandas module to construct a dataframe, I don't know if IFCCSV does that?
Adds total sum of area and volume in the header of the xlsx file, doesn't matter what column the property is, because the sum moves with it. When filtering will automatically update the total sum. Very convenient for cost estimates.

Would you be interested in integrating the code?

Yes! very much so. Only I need to refactor the code heavily I think. I got a lot of good pointers in other threads I haven't implemented yet. Plus I already looked into adding ODS. but the add-on relies heavily on xlsxwriter and openpyxl. I can't find any python modules who are that easy to use as those python modules and are that well documented. If anyone can point me in the right direction on how to make an ods with filtering would be very much appreciated.

M by Moult on 10 Jan 2022 #

Yeah it would be awesome to get a slick graphical interface to IfcCSV, as well as to add an optional Pandas / dataframe feature. Feel free to submit a PR anytime and we can continue the code review process then :) I'm sure lots of people will benefit from your code!

G by Gorgious on 10 Jan 2022 # +2 votes

@Coen I'd like to suggest an improvement if you're open to it.

Regarding custom properties stored on bpy.types.Scene, you could consider putting them in a custom Property Group.

eg :


class BlenderBIMOpenOfficeXMLProperties(bpy.types.PropertyGroup):

    ifcproduct: bpy.props.BoolProperty(name="IfcProduct",description="Export IfcProduct",default = True)

    ifcbuildingstorey: bpy.props.BoolProperty(name="IfcBuildingStorey",description="Export IfcBuildingStorey",default = True)     

    ifcproduct_name: bpy.props.BoolProperty(name="Name",description="Export IfcProduct Name",default = True)

    etc.

There are three immediate benefits to that workflow :

It reduces custom properties clutter in the interface. eg if you want to add a custom properties using the UI to your current scene, it can be a bit daunting if there are already dozens of properties that belong to different addons.
It prevents name collision. I see you have properties named my_length, my_area, etc. The chance that another addon uses the same name for their custom properties is quite high.
It's easier to register and unregister : You only need one call to bpy.types.Scene.bbim_openoffice_xml_props = bpy.props.PointerProperty(Type=BlenderBIMOpenOfficeXMLProperties) (example name) in register and then del bpy.types.Scene.bbim_openoffice_xml_props in unregister. That way you're sure to leave no mess when the user wants to stop using your addon.

Also you can get rid of noisy prefixes like my in my_prop by taking advantage of OOP.

If you want pointers on how to achieve that I'd be happy to help.

Cheers !

C by Coen on 11 Jan 2022 #

@Gorgious

Thank you very much for the feedback, I have seen @LaurensJN make a simlar post in this thread

I've updated the script

Put the Custom Properties in a Property Group
Removed the warning
Included Python dependencies in the zip file

I still need to remove some global variables, and I need to find out how the user can add a custom property field by clicking a + button. BlenderBIM uses this all the time I've seen. I also still need to research on how to write an ODS file.

@Moult

Yeah it would be awesome to get a slick graphical interface to IfcCSV, as well as to add an optional Pandas / dataframe feature. Feel free to submit a PR anytime and we can continue the code review process then :) I'm sure lots of people will benefit from your code!

That would be really cool.

The add-on as it looks now:

If people could test it would be very much appreciated

Here is the link to the .zip

V by vpajic on 11 Jan 2022, edited 11 Jan 2022 # +2 votes

@Coen regarding adding custom properties using a "+", all it is is a collection property. See the example here:

https://docs.blender.org/api/current/bpy.props.html#collection-example

Notice that instead of bpy.props.PointerProperty(...) we're using bpy.props.CollectionProperty(...). An example of this with the IFC CSV module: https://github.com/IfcOpenShell/IfcOpenShell/blob/e81848dfba7885d03128224b9d4d3df7dff84ce5/src/blenderbim/blenderbim/bim/module/csv/prop.py#L37

And then a simple operator to add a new custom property:

https://github.com/IfcOpenShell/IfcOpenShell/blob/e81848dfba7885d03128224b9d4d3df7dff84ce5/src/blenderbim/blenderbim/bim/module/csv/operator.py#L31

Essentially, using a CollectionProperty creates an array. Each time you call the .add() method you create a new item in the array which is an instance of the PropertyGroup class you defined.

Accessing the array in the UI:

https://github.com/IfcOpenShell/IfcOpenShell/blob/e81848dfba7885d03128224b9d4d3df7dff84ce5/src/blenderbim/blenderbim/bim/module/csv/ui.py#L59

Hope that helps, also sorry if my explanation isn't strictly code-lingo correct ;)

G by Gorgious on 13 Jan 2022, edited 13 Jan 2022 # +2 votes

This is the shortest example I could come up with :

import bpy



class MyItem(bpy.types.PropertyGroup):

    name: bpy.props.StringProperty(name="My Property")  # You don't even have to define this, name is a builtin property of PropertyGroup



class MyCollection(bpy.types.PropertyGroup):

    items: bpy.props.CollectionProperty(type=MyItem)



class MyCollectionActions(bpy.types.Operator):

    bl_idname = "my.collection_actions"

    bl_label = "Execute"

    action: bpy.props.EnumProperty(

        items=(

            ("add",) * 3,

            ("remove",) * 3,

        ),

    )

    def execute(self, context):

        my_collection = context.scene.my_collection

        if self.action == "add":           

            item = my_collection.items.add()  # Here keep a handle on the last added item. You can then change its name or whatever afterwards

        if self.action == "remove":

            my_collection.items.remove(len(my_collection.items) - 1)

        return {"FINISHED"}



class MyPanel(bpy.types.Panel):

    bl_label = "My Collection"

    bl_idname = "MY_PT_collection"

    bl_space_type = 'PROPERTIES'

    bl_region_type = 'WINDOW'

    bl_context = "scene"



    def draw(self, context):

        layout = self.layout

        my_collection = context.scene.my_collection

        row = layout.row(align=True)

        row.operator("my.collection_actions", text="Add", icon="ADD").action = "add"

        row.operator("my.collection_actions", text="Remove Last", icon="REMOVE").action = "remove"



        for item in my_collection.items:

            layout.prop(item, "name")



if __name__ == "__main__":

    bpy.utils.register_class(MyItem)

    bpy.utils.register_class(MyCollection)

    bpy.types.Scene.my_collection = bpy.props.PointerProperty(type=MyCollection)

    bpy.utils.register_class(MyCollectionActions)

    bpy.utils.register_class(MyPanel)

Go to your scene properties :

And you can access the items properties with :

for item in my_collection.items:

    print(item.name)

C by Coen on 13 Jan 2022 # +3 votes

@Gorgious

Thank you very much, I was struggling with this.

I now need to think on how to add this to the pandas dictionary each time... I am already busy since twelve this morning. I am going for a walk.

G by Gorgious on 13 Jan 2022, edited 13 Jan 2022 # +2 votes

Alright after thinking a bit about it I can suggest using defaultdic.


from collections import defaultdict

my_dict = defaultdict(list)

my_dict["my_key"].append("my element")  # This does not throw error contrary to regular `dict`

my_dict["my_key"].append("my other element")

print(my_dict["my_key"])

>> ['my element', 'my other element']

So you can replace this line with ifc_dictionary = defaultdict(list) and basically remove all these expressions

And then for instance you can remove


if blenderbim_openoffice_xml_properties.my_ifcproduct is True:

    ifc_dictionary['IfcProduct'] = ifc_product_type_list

and use


if blenderbim_openoffice_xml_properties.my_ifcproduct:  # 'if condition is True:' is the same as  'if condition:'

    ifc_dictionary['IfcProduct'].append(str(product.is_a()))

instead of


if blenderbim_openoffice_xml_properties.my_ifcproduct is True:

    ifc_product_type_list.append(str(product.is_a()))

And for the new collection property:


for item in context.scene.my_collection.items:

    ifc_dictionary[item.name].append(self.get_custom_pset( context,ifcproduct=product,

                                                                pset_name=pset_name_user_a,

                                                                property_name=pset_property_user_a)[0])

in place of these lines and these lines.