BlenderBIM spreadsheet writer for .xlsx and .ods

Coen

I managed to write a table to an ods format with the following code:

        df = pd.DataFrame(ifc_dictionary)
        writer_ods = pd.ExcelWriter(blenderbim_openoffice_xml_properties.my_ods_file, engine='odf')
        df.to_excel(writer_ods, sheet_name=blenderbim_openoffice_xml_properties.my_workbook, startrow=0, header=True, index=False)

        worksheet_ods = writer_ods.sheets[blenderbim_openoffice_xml_properties.my_workbook]
        writer_ods.save()
        os.startfile(blenderbim_openoffice_xml_properties.my_ods_file)

The result in LibreOffice:

Now it starts to get difficult,
I want to apply an autofilter in LibreOffice through Python.
I found this SO post
It seems to overcomplicate the script a lot.
Maybe this is the wrong platform to ask, I should ask on SO.

But I also get confused about the .osd format. Because when I open it in notepad++ I just see binary text... I expected a .xml file.

baswein

Thanks for all of your work on this.
Wikipedia says .ods is a zipped xml and when I unzipped one I do see a content.xml as well as a bunch of other files.

Coen

@baswein
I unzipped it and indeed see huge xml files, just wondering if I can add an autofilter directly with lxml python instead of applying an autofilter directly on libreoffice.

Moult

The XML schema of ODS is quite tricky to understand I find. I find that a good way to discover how things work is to create a small tiny file in Libreoffice, make a change (e.g. add your autofilter), then save it out again, then compare using diff what happened in the XMLs.

Coen

How would I move a variable, in my case a pandas dataframe from one class to another class in Blender?
I made two new classes to Write to .ods and open and .ods file.
I would like to sent the pandas dataframe to that class. With Blender is all seems to work a bit different then I am used to with Python.

Gorgious

Maybe an XY problem, you may be more at ease if you uncouple your pandas logic from both classes and only write to a particular file at the end ?

Coen

@Gorgious said:
Maybe an XY problem, you may be more at ease if you uncouple your pandas logic from both classes and only write to a particular file at the end ?

Thanks for your answer.
I will try to be more specific. my aim is to have three classes

• ConstructPandasDataFrame
• WriteToXLSX
• WriteToODS

The reason I want the ConstructPandasDataFrame as a seperate class is I think it will help to be sofware-agnostic.
In the ConstructPandasDataFrame class I end up with a variable called df. This variable contains the dataframe and all the data to be written to .ods or .xlslx.

Now I made a simple class called WriteToODS

class WriteToODS(bpy.types.Operator):
    """Write IFC data to .ods"""
    bl_idname = "object.write_to_ods"
    bl_label = "Simple ODS Object Operator"

    def execute(self, context):
        print ('hello from ods')
        return {'FINISHED'}

In the ConstructPandasDataFrame class the dataframe is constructed, I will not copy paste it for the sake of brevity
But in short

class ConstructPandasDataFrame(bpy.types.Operator):
    """Construct Pandas Datafra,"""
    bl_idname = "object.construct_pandas_dataframe"
    bl_label = "Simple Pandas Object Operator"

    def execute(self, context):
        #a lot of stuff happening here but eventually I end up with

       df = pd.DataFrame(ifc_dictionary)

        return {'FINISHED'}

Now I don't understand how to sent/use the variable df in the WriteToODS class

Gorgious

Alright I understand now. So you want to have a button that is only responsible for building the data frame in memory ? Couldn't it be made at runtime when the user clicks on either the button to create the xls or the ods ?

Coen

@Gorgious said:
Alright I understand now. So you want to have a button that is only responsible for building the data frame in memory ? Couldn't it be made at runtime when the user clicks on either the button to create the xls or the ods ?

No, I don't want a button for building a dataframe, I want the dataframe constructed when the xlsx or ods button is clicked :-)

Gorgious

Oh right then you don't need to inherit from bpy.types.Operator, you can even keep it as a function even though it could benefit from being object oriented. Then in your WriteToODS execute you go df = get_dataframe() with for instance :

def get_dataframe()
    # ... logic
    # return pd.DataFrame(ifc_dictionary)

Or if you want to use a class (easier to extend in the future and to separate concerns since you may want to break down your mega function at some point)

class ConstructPandasDataFrame:
    def __init__(self):
        # ... logic
        self.dataframe = pd.DataFrame(ifc_dictionary)

and in the WriteToODS execute :

def execute(self, context):
    df_creator = ConstructPandasDataFrame()
    df = df_creator.dataframe
    # ... logic
    return {"FINISHED"}

Coen

Thanks, one thing which is not clear for me.
in the class

class ConstructPandasDataFrame:
    def __init__(self):
        # ... logic
        self.dataframe = pd.DataFrame(ifc_dictionary)

How do I acces the context variable? I want to use the variables

blenderbim_openoffice_xml_properties = context.scene.blenderbim_openoffice_xml_properties
my_collection = context.scene.my_collection

in the logic

Gorgious

You can pass any variable you want to the class constructor :

class ConstructPandasDataFrame:
    def __init__(self, context):
        # ... logic (now you can use the provided context)
        self.dataframe = pd.DataFrame(ifc_dictionary)

Then

def execute(self, context):
    dataframe_creator = ConstructPandasDataFrame(context)
    df = dataframe_creator.dataframe

Although I would try and uncouple the dataframe creation more from Blender's shenanigans so you could do :

class ConstructPandasDataFrame:
    def __init__(self, xml_properties, my_collection):
        # ... logic (now you can use xml_properties and my_collection)
        self.dataframe = pd.DataFrame(ifc_dictionary)

Then

def execute(self, context):
    dataframe_creator = ConstructPandasDataFrame(
        xml_properties=context.scene.blenderbim_openoffice_xml_properties,
        my_collection=context.scene.my_collection
    )
    df = dataframe_creator.dataframe

And you could even uncouple it more with a bit of work. That way for instance if at any one point you decide you're not going to store your things into a CollectionProperty on the scene but rather get it from an external json or csv file, you don't have to even touch the ConstructPandasDataFrame class to modify the behaviour.

Also be aware that if at any point you can't get a handle on the current context, you can always use bpy.context which theoretically should point to the current context. Although it does not always do and the error it produces are very cryptic to say the least, so the best is to avoid using it if you have it handy from somewhere else.

duncan

@Coen I hope you don't mind but I've renamed this discussion to better reflect where it's gone. Let me know if you want something else.

Coen

@Gorgious

on the scene but rather get it from an external json or csv file,

Thanks for the elaborate answer, I think I am going down this route. I am so confused now. I don't understand anymore..Code also became a complete mess haha. I also still need to figure out how to apply autofilter in LibreOffice and save a filtered .ods file and read it back in Blender.

@duncan
That's indeed a better title.

Coen

@Gorgious

Although I would try and uncouple the dataframe creation more from Blender's shenanigans so you could do :

https://blender.stackexchange.com/questions/191098/blender-python-how-to-call-a-class-or-function

I found this unanswered question on SO, I have class now I called:

class ConstructDataFrame(bpy.types.Operator):

    """Construct Dataframe"""
    bl_idname = "object.construct_dataframe"
    bl_label = "Object Operator Construct Dataframe"

    def execute(self, context):
        print("Construct DataFrame")
       """ Does a lot of things like checking which buttons are checked to construct the dictionary"""
        df = pd.DataFrame(ifc_dictionary)
        df.to_csv(IfcStore.path.replace('.ifc','_blenderbim.csv'),sep=';')

        print (IfcStore.path.replace('.ifc','_blenderbim.csv'))

    return {'FINISHED'}

This class should be called in other class, should I register and unregister this class? Because when I try to call this class I do the following:

class WriteToXLSX(bpy.types.Operator):
    """Write IFC data to .xlsx"""
    bl_idname = "object.write_to_xlsx"
    bl_label = "Simple Object Operator"

    def execute(self, context):
        print("Write to .xlsx")

        construct_data_frame = ConstructDataFrame(context)

        #construct_data_frame.write_to_csv

        for i in dir(construct_data_frame):
            print (i)

I can see it finds all the methods with dir, but no csv is made.

When I try
construct_data_frame.execute or any other method
I get this error

location: <unknown location>:-1
Error: Python: Traceback (most recent call last):
  File "\BlenderBIMOpenOfficeXML.py", line 345, in execute
    if not custom_pset_list:
  File "C:\Program Files\Blender Foundation\Blender 3.0\3.0\scripts\modules\bpy_types.py", line 734, in __getattribute__
    properties = StructRNA.path_resolve(self, "properties")
ValueError: Context.path_resolve("properties") could not be resolved

location: <unknown location>:-1

Gorgious

Haha welcome to the wonderful world of programming then ! This problem is the bane of all programs that started out as making one thing well, then were expanded to make two things, and so on. Unless you spend some time reflecting a bit and refactoring to be more expandable you won't be able to add functionality as easily I'm afraid :)

Coen

Unless you spend some time reflecting a bit and refactoring to be more expandable you won't be able to add functionality as easily I'm afraid :)

This is probably the best advise, I'm just impatient with myself. :-)

Gorgious

Ups it was meant to follow your previous comment ^^
The thing here is unless you want your ConstructDataFrame class to be executed when you click on a button in the Blender interface, you do not need (nor want) it to inherit from bpy.types.Operator and implement all the associated boilerplate code. All the code that inherits or references bpy.types is basically a bridge (or interface) to the actual C codebase, and as such you can't define or access classes and objects normally as you would with regular python objects.

You can for instance replace your code with :

class ConstructDataFrame:
    def __init__(self, context):  # This is a python class constructor. It's executed when you do construct_data_frame = ConstructDataFrame(context)
        print("Construct DataFrame")
       """ Does a lot of things like checking which buttons are checked to construct the dictionary"""
        df = pd.DataFrame(ifc_dictionary)
        self.df = df

class DataFrameWriter:
    def __init__(self, dataframe):
        self.df = dataframe
    def to_csv(self, path):
        self.df.to_csv(path)
    def to_xlsx(self, path):
        self.df.to_xlsx(path)  # not sure about the syntax here

class WriteToXLSX(bpy.types.Operator):
    """Write IFC data to .xlsx"""
    bl_idname = "object.write_to_xlsx"
    bl_label = "Simple Object Operator"

    def execute(self, context):
        print("Write to .xlsx")

        construct_data_frame = ConstructDataFrame(context)
        writer = DataFrameWriter(construct_data_frame .df)
        # writer.to_csv(IfcStore.path.replace('.ifc','_blenderbim.csv'),sep=';')
        writer.to_xlsx(IfcStore.path.replace('.ifc','_blenderbim.xlsx'),sep=';')

This is of course just a suggestion in order to show how it would work, you can do all of that with simple functions too.

As for the initial question linked to BSE, the way you execute an operator in code is with bpy.ops and then the operator's bl_idname property eg you would call your WriteToXLSX operator with bpy.ops.object.write_to_xlsx() (solution is given in the comments). Remember this is an API, meaning a lot of python rules are bended and there is a Deus Ex Machina under the hood that's tying everything up, calling things automatically, etc.

Coen

@Gorgious

After struggling the whole day I finally managed to make it work :-D. Will commit later when I have all the mess cleaned.

EDIT: Forgot to thank you in this post :-D @Gorgious

Coen

Does anybody maybe know how to get the filtered data from LibreOffice (.ods file) to Python. I've been reading a lot of threads on SO, but they all apply the filter in pandas.

I've made this filtering in LibreOffice,

Now I want to retrieve only the GlobalId values from that filtered list

I tried:

            if blenderbim_openoffice_xml_properties.my_file_path.endswith(".ods"):


                ###################################
                ### Get filtered data from .ods ###
                ###################################
                dataframe = pd.read_excel(blenderbim_openoffice_xml_properties.my_file_path, sheet_name=blenderbim_openoffice_xml_properties.my_workbook, engine="odf")

                print (dataframe['GlobalId'])

It returns each GlobalId from the .ods file. I only want the filtered items.
I can't use the openpyxl module, it's how I do it with .xlsx files like this for example:

                workbook_openpyxl = load_workbook(blenderbim_openoffice_xml_properties.my_file_path)
                worksheet_openpyxl = workbook_openpyxl[blenderbim_openoffice_xml_properties.my_workbook] 

                global_id_filtered_list = []

                for row in worksheet_openpyxl:     
                    if worksheet_openpyxl.row_dimensions[row[0].row].hidden == False:
                        for cell in row:  
                            if cell in worksheet_openpyxl['A']:  
                                global_id_filtered_list.append(cell.value)

Gorgious

The pandas module read_excel function has a skiprows argument but then you would need to evaluate the sheet to see wich rows are hidden before reading the file, so we're back at square 1...

Coen

Update BlenderBIM spreadsheet:
I've commited the code today and added a new zip.

Screenshot of the add-on as of 3d february 2022:

New features:

• .ods export possible, but no filtering IFC elements from an .ods file yet

• Clear distinction between Area definitions, NetSideArea, NetArea. Area export each defintion of Area. Same for Volume and NetVolume.

• A file dialog to load a spreadsheet in, so only one spreadsheet at the time can be used for filtering. So users can see from which spreadsheet file they are filtering in Blender.

• Calculates total sum in .xlsx for NetArea and NetSideArea after filtering.

To do:

• Apply autofilter in LibreOffice with an .ods file

• Make IFC elements filtering possible from an .ods file

Made a small demo on youtube in English, really hope I explain it clear.

@duncan
I renamed my github repo, so all the links in the OP are broken. Don't know if you could maybe edit it?

Gorgious

Awesome ! Do you think you could provide an example excel file that I could play with using your addon ?

Coen

@Gorgious said:
Awesome ! Do you think you could provide an example excel file that I could play with using your addon ?

Yes ofcourse :-), do you mean a template file or sample file? Or a .xlsx or .ods file? But the add-on creates them from any IFC file...

Gorgious

Ideally I'd like a very big xlsx or ods file which which would come from your addon, which I don't have at the moment but you're right that I could generate it myself from an ifc :)
I've noticed the import operation takes quite a bit of time and I'd like to explore how you could maybe make it faster, if that's ok with you

Coen

@Gorgious said:
Ideally I'd like a very big xlsx or ods file which which would come from your addon, which I don't have at the moment but you're right that I could generate it myself from an ifc :)
I've noticed the import operation takes quite a bit of time and I'd like to explore how you could maybe make it faster, if that's ok with you

Yes ofcourse. :-D.
I've attached the .xlsx file of the Schependomlaan IFC.
Also tried to attached the .ods file but got a warning that the file format is not allowed.

vpajic

@Coen you're starting to make me nervous with the pace of your developments ??. Fantastic work as always, I'm looking forward to testing this out intensively. I'll report back with any feedback I might have ;)

Nigel

@Coen Great work. From beginning in CAD in the early 90s I have been frustrated with so much data being locked inside files without easy access, what you have done in a few months, I have waited for years to happen.

duncan

@Coen sure, tell me precisely what you want me to change to what.

Coen

@vpajic

you're starting to make me nervous with the pace of your developments

It would never got this pace if I haven't found osarch and the helpful people on here :-)

@Nigel

what you have done in a few months, I have waited for years to happen.

Well, I didn't do it alone. I just based my idea on work of others. :-)

@duncan

sure, tell me precisely what you want me to change to what.

Could you please remove all the images and replace it with this embedded youtube link?
https://youtu.be/_ZXUZQkcjlI

And could you also remove all the links, and just use this link please?
https://github.com/C-Claus/BlenderScripts/tree/master/BlenderBIMSpreadsheet
Thank you very much.