Filtering IFC with Excel and BlenderBIM (Proof of concept python script)

Coen

Before you continue reading. I'm aware of the cool capabilities of IFCCSV in the Blender BIM add-on. :-)
But I made this as a novelty and to practice my Python, it's also not really an open source solution, but an Excel solution.
I made a very basic small script which exports all the IFC product data (GlobalId, IfcProduct and Name) to Excel.
The filter functionality of Excel is used to highlight the IFC elements in Blender.

If you save the filtered Excel file, and run the script, in Blender you see what you filtered in Excel.

Coen

I updated the Blender BIM Excel script

It now exports the main useful items from IFC to table data in Excel. I instantiate an IfcBuildingStorey for each IfcProduct so filtering and viewing the corresponding items in Blender is very intuitive.

A small example

Here is the IFC model in Blender with everything visible

Here is the unfiltered Excel

Now I can start filtering in Excel. In this example I filtered on IfcBuildingStorey and Name

With help from @Gorgious running the function select_IFC_elements_in_blender() makes the elements visible.

I could add all the IfcPropertySet to another sheet, and the IfcProjectInfo to another sheet. And make a really small blender add-on with interface. Also writing back data from Excel to IFC is a wish. But I believe the IFCCSV add-on already does that?

EDIT:
Would be really cool if the same could be done using LibreOffice, then it would be a real open source solution.

vpajic

this is really awesome!

Gorgious

IIRC the last time I worked with worksheet information the pandas python module offered nice software-agnostic features to manipulate data sheets.

Ideally we could do all this good stuff in the spreadsheet editor directly in Blender but it's currently not possible. It's being worked on though :)

Coen

@Gorgious said:
IIRC the last time I worked with worksheet information the pandas python module offered nice software-agnostic features to manipulate data sheets.

Ideally we could do all this good stuff in the spreadsheet editor directly in Blender but it's currently not possible. It's being worked on though :)

Using the Pandas Module would be a good idea, instead of using openpyxl and xlsxwriter. I think a Graphical User Interface made with PyQt to let the user prompt what they want to export to Excel/CSV is also an option. Making an interface like this would add to user friendliness, or we should make a better one. :-P

But what you said, the Blender spreadsheet edit would be the best solution.

Coen

https://github.com/C-Claus/02_Blender_Python_scripts/blob/master/BlenderBIMExcel/setup_instructions_and_explanation.md

Added a more elaborate explanation on github, and updated the script with wall quantities.

Coen

Found a script here which is able to run a PyQt window within Blender while keeping Blender active.

Also found another script

This could be very useful

Coen

update:
With help from @Gorgious and @vpajic I updated the script

• Stopped using xlsxwriter directly and started using pandas to construct a dataframe which then is directly exported to Excel
• Made a simple four button interface in Blender
• Rewritten the functions which exports the Base Quantities

What I would like to do:

• Figure out a way to bundle it in a usable add-on for Linux and Windows users. What I understood the xlsxwriter module is able to work with LibreOffice but the openpyxl module is not. I use openpyxl to filter the IFC elements, maybe I should use pandas for that?
• In the header of the table make an Excel formula which recalculates the total sum when filtered. If anyone here has any Excel experience please let me know how to do it.

• Figure out a way on how to make Quantities intuitive presentable in a spreadsheet format. Sometimes quantities are defined twice. I now put them in a list, so I can write them both to one cell.
  
  The same applies to IfcMaterials, because of IfcMaterialList and IfcMaterialLayer. I now put a pipeline between them
  

• Find a faster method to filter IFC elements, it's acceptable for now. But if there are any methods which instantly shows the elements I would like to know.

• Figure out how to write back data from Excel to IFC whilst using the excel filters.

For now some screenshots:

1. The imported IFC file, click under Tools > BlenderBIM Excel > Write to Excel

2. The excel export is surprisingly quick, only takes a second or two so with this big model with a lot of information. It has 3822 records.

3. Filtering what you need in Excel and saving the Excel file, it is important to leave the excel file open.

4. Clicking the 'Filter IFC elements' button shows what you filtered in Excel

Coen

In the header of the table make an Excel formula which recalculates the total sum when filtered. If anyone here has any Excel experience please let me know how to do it.

I found it's very easy with the Excel formula:

=SUBTOTAL(109;N3:N3822)

Gives the following result.

In python I can use the xslxwriter module for this:

total_area='=SUBTOTAL(109,N3:N' + str(len(products)) + ')'
total_volume='=SUBTOTAL(109,O3:O' + str(len(products)) + ')'

worksheet.write_formula('N1', total_area)
worksheet.write_formula('O1', total_volume

The total sum now it automatically calculated when filtering in Excel. Though not really open source anymore, might be a useful solution.

Coen

Just downloaded LibreOffice to try if it works with the add-on
First try worked flawlessly, only lost the nice layout of the tables but the formulas still work.

I guess it's a true open source solution after all. It uses the .xlsx format after all. Duh.

Coen

Renamed the repo to fit the semantics more correctly.

Renamed all the buttons and the panel as well.

I think I should make a new thread when I manage to get all the buttons working and bundle the add-on in a .zip.

Because of renaming the repo all the links I made in this thread don't work anymore obviously.

Meetlat

Gaaf! My girlfriend uses solibri with excel a lot at the construction site. Nice to see a somewhat similar workflow with blenderbim. We will test drive it after the holidays and rapport back to you if we find any bugs. Happy coding and best wishes!

Nigel

Hi @Coen I would like to try this too, is it a simple Zip install addon or do I have to install other stuff first? I am a real novice so forgive me:)
Also, I got a 404 when I tried to follow the above link.
Thank you

Coen

@Meetlat
@Nigel

I made a new thread here, I would love if you could try to install it and I'm very open to feedback:
https://community.osarch.org/discussion/849/blenderbim-openoffice-xml

@Adminstrator of this board.
Could you please remove this thread? It's full of dead links now.