BlenderBIM spreadsheet writer for .xlsx and .ods

Coen

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

Gorgious

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 !

duncan

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.

Coen

@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.

Nigel

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?

Coen

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

Nigel

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

Nigel

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.

Coen

@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.

vpajic

@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

duncan

@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.

Meetlat

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?

Coen

@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.

Coen

@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

Meetlat

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:

Coen

@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.

Moult

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?

Coen

@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.

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!

Gorgious

@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 !

Coen

@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

vpajic

@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 ;)

Gorgious

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)

Coen

@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.

Gorgious

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.

Coen

@Gorgious
Thank you so much! :-D the whole script now looks a lot cleaner and more efficient using defaultdict. No need to make a new list each time. Just committed. And now the add custom property button also works. :-D

@vpajic
How would I start integrating this with IFCCSV?, I have no idea where to start..

vpajic

@Coen - I would simply start by looking at the general structure of the modules. You generally have 4 files:

• init.py
• operators.py
• ui.py
• prop.py

If you have a lot of helper functions you could also create a helper.py file and import it where necessary. I only glimpsed at your code but I saw the "WriteToXLSX" operator class and thought wow, that's a behemoth ?. Perhaps start by refactoring your script so it fits in with the other modules and then simplifying the giant classes a bit? @Gorgious might be able to give you better tips though, these are just my quick 2 cents.

Moult

@Coen there are two aspects to merge. The first aspect has got nothing to do with the BlenderBIM Add-on - it's to do with IfcCSV directly. First, I'd get familiar with how to use IfcCSV purely through Python, a CLI, or anything that doesn't require the Blender interface. IfcCSV currently only works with CSV right now. the IfcCSV class has an import function and an export function which does the actual reading and writing to and from CSV. This needs to be split out into its own function, and have two classes - one class would do CSV, and another would do XLSX. In the future, we'd also add ODS support. You'd do this refactoring and add a new XLSXWriter/Reader class and function purely in IfcCSV land. This way, any other app in the future can benefit from it: Blender, FreeCAD, or anything at all.

The second aspect would be the Blender interface that you've written. I think after you've done the first aspect and are familiar with how IfcCSV by itself works without Blender, the second aspect of integrating your UI should be much more straightforward, and you can look at the bim/module/csv/* directory for some hints.

Hope it helps.

Coen

I've done quite some refactoring . The whole script is now only 600 lines with some more efficient use of functions.

• The IsExternal, LoadBearing, Firerating functions is now one function called get_common_properties. I fixed a big mistake. It didn't export the loadbearing at all..
• All the various get_quantities functions is now one function called get_quantities. If the user ticks "Area" it exports all the area defintions available like NetSideArea, GrossSideArea. Maybe I should split this up in a seperate UI to prevent confusion.
• Removed all the global variables definitions

My plan is start integrating the add-on into BlenderBIM when I have the export to .ods export ready. Else I don't think it's worth of the term open source.

vpajic

@Coen I'm inspired by the effort you put into this!

Nigel

@vpajic said:
@Coen I'm inspired by the effort you put into this!

Me too, Coen I can't wait for your StairMaker ?