I need to read and write data in Excel sheets in Grasshopper, and be able to do that dynamically. I found a plugin “excelreadwrite”, but for some reasons I still ignore, I have errors and the plugin works on off (it works, than if I close the file and reopen it it stops working, or if I change the toggle from True to False than to True again it also stops working, etc. so it’s very unstable). I was wondering if it’s possible to access excel sheets (reading and writing) from Python in Grasshopper. If possible, what package in grasshopper should I use and what should I do?
here’s an example that fills values and changes the cells colors. x and y represent the starting position in xls.
hope this is a good base to start with, I added a comment in the code, where to put a recorded excel macro if you want it do other stuff like resize the cells, borders etc.
these elements are unstable because they stream through interop services. your excel needs to be open already to work correctly, as the “active document” is used and a simple button to stream once only is better than a switch.
otherwise try to post a screeshot of your definition, the readwrite nodes work fine basically, but which is the active document and how’s it’s created is a bit of a mess there, indeed. Avoid creating the sheet with these elements, if I remember correctly, there was somethng not working to pass the created sheets path.
Thank you for your help.
I am not well experimented in VB since I never used it until now.
Do you think it is the only way to use excel sheets in Grasshopper properly? If yes, do you have any tutorials recommendations?
Also, for the plugins, event with the excel sheets open, I have many different errors like : ’ 1. Solution exception:Impossible de charger le fichier ou l’assembly ‘CORE.Library.Office, Version=1.3.0.0, Culture=neutral, PublicKeyToken=null’ ou une de ses dépendances. Le fichier spécifié est introuvable.’ and many other…
Do you have any idea how to fix that? I dowloaded the last versions from Food4Rhino and yet I have a lot of error messages.
I think it’s the only possibility, but maybe someone proofs me wrong.
To learn VB in Excel, I personally just used the macro recorder and google.
Or try and double click on the posted element.
you can nearly just copy and paste the excel recorded macro here:
If you need your file fixed, it might be easier to post the file or a screenshot of what you did.
When it shows “fichier introuvable” there must be a problem with the path specified. Do you create your file with the excelpath node?
Here is an example with the errors I got, using excelreadwrite plugin. In the morning it was working, but when I closed Rhino and reopened it, I got this error.
For the path, I copy paste it from the bar in the folders. I put the plugins in the “Components folder” that I open by clicking on File → Special folders → Components folder.
Another option would be to use Hops to connect to an external Cpython interpreter running pandas, if you are already comfortable with that? You say you want to work dynamically - I think you need to close the excel file before reading with pandas so that might not be suitable for you.
well, the plugin is correctly installed, otherwise you wouldn’t see the elements.
I seem unable to intall the plugin by package manager and can’t find it onf food4rhino… was it bumblebee? (if not, can you provide a link, pls? I’m sorry)
try and click right on the path’s panel, then select multiline data. you are providing a list of paths with only one path inside. this might causes the node to fail, as it expects one instance only.
I’ll be glad to help more, but it’s difficult without the plugin;(
Yes exactly I need to change values in excel and get them automatically changed in grasshopper. Isn’t it possible to work like this if I use the pandas ?
it seems to be a 4 year old version that works with rhino 4 & 5, so honestly, good job making it work, my rhino 7 isn’t happy at all when I try to load it. UPDATE: I made it work, but there’s definitely some buggy thing going on there. Excel screen doesn’t update any more, it’s very probable that one of the nodes keeps stuck somewhere and then don’t finish the script…most probable you are having too many elements accessing excel at the same time, so some script stops working at some point an wont update the screen any more.
however, gh_opt_test_re.gh (14.7 KB)
works for the writing part at least.
Please note, if you use the one that adds a color to the cell without providing a color, the script will stop working and your screenupdate will never be turned on, so you won’t see any changes on excel any more. better use the one without color in case it makes your excel “crash”
note two: it needs to be activated not to stream constantly to excel, so you could set a timer on the node to make it update every 5 seconds or so.
for the read node I’d need to change a node to a write node to make it work, but not sure I’ll get it done today.
Thank you so much!
I think as you said, it might be caused by the versions incompatibilities.
For the excel screen update, I got the same issue: sometimes it works, sometimes not
I figured out that sometimes we can eliminate the errors by simply changing the extension like this: xlsx ; error → xls ; error → xlsx; No errors .
I might be a bug that needs to run from zero each time in order to be fine.
It is not the best solution to solve the problem cause that means that it is so unstable and might bring errors very quickly. Do you have any suggestions ?
I would like to thank you very much for your help, your advice are very helpful for me to be able to continue my final year project !
well, changing the file name forces the node to run again, as the input changed. xlsx at start → no recalculate, error; xls is not valid → error; xlsx ->recalculated, no error.
this can also be obtained by pressing F5. To avoid this, I’d put a boolean switch to ‘connect’ at the node and set to false before closing and saving your file and activate after opening the file.
However, I was able to open the excel file like that, but not able to read some example values. ^^
As I’m having very little time today and therefor won’t be able to write the “read node”, I’d suggest to try the bumblebee plugin from food4rhino. It’s also a elderly plug in for writing and reading from excel. the structure works slightly different, but this might be a good alternative without loosing a lot of time or waiting for me.
If this doesn’t work for you neither, don’t hesitate to write again.
I have made some headway in this realm using python.
If you can manage the translation from .NET to python you can directly use the Microsoft Excel Interop namespace.
here is a quick snippet I was using for some testing
import clr
clr.AddReference('System.Core')
#clr.AddReference('RhinoInside.Revit')
#clr.AddReference('RevitAPI')
#clr.AddReference('RevitAPIUI')
from System import Enum
import rhinoscriptsyntax as rs
import Rhino
#import RhinoInside
import Grasshopper
from Grasshopper.Kernel import GH_RuntimeMessageLevel as RML
import clr
clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=16.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel
ex = Excel.ApplicationClass()
ex.Visible = True
ex.DisplayAlerts = False
fp = filePath
wn = wsNumber
#sv = saveAs
lData = l_data
lLoc = l_location
rData = r_data
rLoc = r_location
x = 1
#wn = []
workbook = ex.Workbooks.Open(fp)
#ws = workbook.Worksheets[1]
i = 0
for w in wn:
workbook.Worksheets[w].Range(str(lLoc[i])).Value2 = int(lData[i])
workbook.Worksheets[w].Range(str(rLoc[i])).Value2 = int(rData[i])
i += 1
if (workbook.save()):
#print(ws)
O = 1
print(wn)
print(lData)
print(lLoc)
print(rData)
print(rLoc)
#workbook.close()
I had found the available components were either breaking our formulas or just not working in the granularity in which the process required.
