Link between spreadsheets in different files

Post here for help on using FreeCAD's graphical user interface (GUI).
Forum rules
and Helpful information
IMPORTANT: Please click here and read this first, before asking for help

Also, be nice to others! Read the FreeCAD code of conduct!
User avatar
dcapeletti
Posts: 383
Joined: Wed Jul 23, 2014 2:27 pm

Link between spreadsheets in different files

Postby dcapeletti » Wed Oct 24, 2018 7:40 pm

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
Posts: 1060
Joined: Thu Apr 05, 2018 1:53 am

Re: Link between spreadsheets in different files

Postby TheMarkster » Wed Oct 24, 2018 8:49 pm

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]
User avatar
Willem
Posts: 1273
Joined: Fri Aug 12, 2016 3:27 pm
Location: Lisse, The Netherlands

Re: Link between spreadsheets in different files

Postby Willem » Wed Oct 24, 2018 8:54 pm

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
Posts: 8752
Joined: Mon Dec 12, 2011 4:45 pm

Re: Link between spreadsheets in different files

Postby triplus » Wed Oct 24, 2018 9:20 pm

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.
User avatar
dcapeletti
Posts: 383
Joined: Wed Jul 23, 2014 2:27 pm

Re: Link between spreadsheets in different files

Postby dcapeletti » Thu Oct 25, 2018 8:27 am

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
User avatar
microelly2
Posts: 4412
Joined: Tue Nov 12, 2013 4:06 pm
Contact:

Re: Link between spreadsheets in different files

Postby microelly2 » Thu Oct 25, 2018 8:38 am

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/
User avatar
dcapeletti
Posts: 383
Joined: Wed Jul 23, 2014 2:27 pm

Re: Link between spreadsheets in different files

Postby dcapeletti » Thu Oct 25, 2018 2:34 pm

microelly2 wrote:
Thu Oct 25, 2018 8:38 am
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
User avatar
microelly2
Posts: 4412
Joined: Tue Nov 12, 2013 4:06 pm
Contact:

Re: Link between spreadsheets in different files

Postby microelly2 » Thu Oct 25, 2018 2:44 pm

dcapeletti wrote:
Thu Oct 25, 2018 2:34 pm

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.
User avatar
dcapeletti
Posts: 383
Joined: Wed Jul 23, 2014 2:27 pm

Re: Link between spreadsheets in different files

Postby dcapeletti » Thu Oct 25, 2018 2:55 pm

microelly2 wrote:
Thu Oct 25, 2018 2:44 pm
dcapeletti wrote:
Thu Oct 25, 2018 2:34 pm

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
Posts: 875
Joined: Tue Apr 10, 2018 3:12 pm
Location: Germany, near Köln (Cologne)

Re: Link between spreadsheets in different files

Postby kbwbe » Thu Oct 25, 2018 3:25 pm

dcapeletti wrote:
Thu Oct 25, 2018 2:34 pm
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.
KBWBE

https://github.com/kbwbe/A2plus
latest release: v0.4.29, installable via FreeCAD's addon manager
Tutorials:
Paul Randall: https://youtu.be/mnkecA9S7kc
anisim (deutsch): https://www.youtube.com/watch?v=vDcaFq6IEJM