Dear all,
I’ve a create an export to excel in order to have a list of element with related spaces information. Basically with these script I’m able to have the list of spaces related to an MEP element (duct, cable tray, pipe, air terminal, etc.) If the element is crossing more than a space I can have the entire list of spaces.
For a construction company this is very useful and I’m able now to link this information to a planning but, this is not the problem.
My issue is related with the excel write node. In my Excel I’ve a deep bounces of formulas reading from a sheet which basically contains the list coming from Revit
The only way is to use the function replace in Excel, to relink all the references to the name of the correct sheet.
On the other side, if I’m choosing to not overwrite the excel from dynamo, I don’t have this error but, I’ve to delete manually the sheet content before exporting, in order to prevent old information to be in the sheet.
Question: Is there a way to export information to a sheet in overwrite mode, without creating a sheet from scratch?
I suggest using one spreadsheet (or worksheet) to contain the raw data from Dynamo and link that data into another spreadsheet/worksheet which contains the formulas/charts etc
So you would just write the data with Dynamo then refresh the ‘reporting’ spreadsheet
another option is to use a proper database that knows about about appending data
Then either use a view in the database, or link into Excel to get the data/charts that you want.
Search on this forum for SQLite- some people seem scared off and try to do everything in Excel, but it is really pretty simple
When you export to Excel you can choose to overwrite or not the worksheet. If you choose yes, in the excel file the worksheet is deleted and recreated runtime and with this, all the references from other worksheets and formulas are lost.
If you choose “no” the worksheet is not deleted but then you are not sure that all the previous information are cleaned.
I would like something to “clean” the worksheet before starting to write new data so, the worksheet still exist and all the references are ok.
Thanks
Cesare
@Cesare_Caoduro3 how can you say that it is not clean? May be you can create a screencast showing your issue that might help others to get clear understanding.
I wrote this node to clear the worksheet before starting to write and it works.
The only problem now is that at the end of the run, I have two excel opened.
It’s easy to prove that. If you export to excel a model with couple of elements and then you update the model removing some element and export again, you will see still the previous elements in the excel. THis is because if you choose to not overwrite, you overwrite only changes. This is good you you add more elements but it is not, if you delete elements.
Yes, I tried but is not an export issue, just I think in the export node should be included a function to clear the worksheet if this is already in the excel file, or create the worksheet if not.
I know this was a while ago, but did you ever figure out a better way to do this. I am having the exact same problem you described in your original post.
HI Alex,
to be honest I completely abandoned Excel long time ago, to move to more sustainable and flexible dictionaries using JSON.
The short answer is I have never looked into it again.
Good luck!
Cesare