Link between spreadsheets in different files

[dcapeletti]

Hi, I'm starting to worry about getting the list of materials and details of the parts of an assembly.

Imagine that you have different parts of a complex machine, each part has its model. Each of these parts also has a spreadsheet that has several data.
When it's time to assemble, you take each file and perform the assembly. So far nothing complicated.
The problem now, is to get the data from each part that is used in the assembly. For each one of it, we need to consult details that can be in the spreadsheets of the files, count the number of times a piece is repeated, among other things. Opening each part and consulting its data is not friendly, it is a lot of work and prone to many errors.

The most logical thing would be that from the assembly file you can read the spreadsheet of each linked file or even read the properties of the objects of the model.

I hope you can help me with some solution or teach me how to do it.

Thanks

[TheMarkster]

You can link across documents, but all the documents have to be open for the link to work. In this video I show an example using DynamicData, but spreadsheet aliases should also work similarly (untested). I did get some cyclic redundancy error messages, but it still seemed to work.

phpBB [video]

[Willem]

In the Arch workbench is a Schedule" function, I think you can use that, but I have never worked with it
https://www.freecadweb.org/wiki/Arch_Schedule

[triplus]

In addition to what was said this approach is known for a while now:

https://www.freecadweb.org/wiki/index.p ... nt_linking

P.S. In the future Link effort (Assembly 3) might provide more options in multi document management area.

[dcapeletti]

I understand the situation.

Having the documents open seems a bit inefficient for both spreadsheets and dd. Imagine large assembly sets with dozens of source files that several people modify, it's chaos to open each of them.

Imagine that person A needs to edit an object that belongs to a complex assembly. Person B, who has the assembly open, should be able to read the changes made by A without opening the file. Simply with the source link provided by A2Plus or in Assembly, you should get the changes to the properties of the source file.

Maybe I think it's a good idea to use python's zipfile module to decompress the source file (which can be done even with the document open), look up the property value in the Document.xml and send the changes to the assembly file. I'll try it on my own in the python console.

But in reality, this functionality should be provided by A2Plus or Assembly. These artboards can read the file objects and raise the changes without having the file open. The same concept should be applied to read the properties of source files, cell aliases in spreadsheets, pad properties, dd properties or whatever.

What do you think about that?

Thanks

[microelly2]

The better way ist to use a separate database file.
there is a way to hold data in excel files und get access with
the openpyxl module.
the execle files can be modified with libre office too, so you need not the uso of proprietary software.

https://openpyxl.readthedocs.io/en/stable/

[dcapeletti]

The better way ist to use a separate database file.
there is a way to hold data in excel files und get access with
the openpyxl module.
the execle files can be modified with libre office too, so you need not the uso of proprietary software.

https://openpyxl.readthedocs.io/en/stable/

Thank you for your reply.

Look what I have done. In this video https://peertube.video/videos/watch/685 ... 35cd9263a9 I consult information about a FreeCAD model used in an assembly, without opening the source file. Directly using python and the path provided by A2Plus, I consult the length of the pad.
While what I have done for a very quick test, I think it might be a valid method to consult directly in the Document.xml.

What do you think?

Code: Select all

import zipfile
import xml.etree.ElementTree as ET

file = App.ActiveDocument.FileName
file = file[:file.rfind("/")]

#Then, I open the imported source part in the assembly document.
file = zipfile.ZipFile(file+App.ActiveDocument.Ca__o_30x40x665_001.sourceFile, 'r')


xml = file.read('Document.xml')
tree = ET.ElementTree(ET.fromstring(xml))
file.close()

root = tree.getroot()
Objects = root.findall('ObjectData/Object') #List of all objects in the FreeCAD document

#I look for the value of the property Length and print its value
Property = Objects[9].findall("Properties/Property/Float")
value = Property [0].get("value") #I get the property value of the source file
Print value

[microelly2]

What do you think?

yes, this is a way which will work.
you have to analyze the xml tree but the result will be a reusable method for later too.

[dcapeletti]

What do you think?

yes, this is a way which will work.
you have to analyze the xml tree but the result will be a reusable method for later too.

I'm glad you can be of service.
I don't know any other way to read a FreeCAD file without having FreeCAD open. But this way works and you obviously need to write better code to analyze XML.

I think it would be important for it to be implemented in the assembly modules.

Thanks

[kbwbe]

Look what I have done. In this video https://peertube.video/videos/watch/685 ... 35cd9263a9 I consult information about a FreeCAD model used in an assembly, without opening the source file. Directly using python and the path provided by A2Plus, I consult the length of the pad.
While what I have done for a very quick test, I think it might be a valid method to consult directly in the Document.xml.

What do you think?

In any case, this is an interesting approach. I will do some experiments too. Perhaps we can generate at minimum a partsList/BOM by this way.