Grasshopper/ Excel (Read, Write, Chart)

Replies to This Discussion

Permalink Reply by David Mans on January 7, 2011 at 7:29pm

UPDATE:

 I have posted an update to the file "GH-Excel_v0-02" which adds a bit of new functionality and fixes a few errors.

The new update to the GH-Excel Suite includes 

-correction to the "area" chart type which was conflicting with the color/ fill inputs foro data points.

- addition of new graph component Excel Chart (update) which allows for the active updating of an existing chart.  It uses the "Title" input to name the chart, then checks the active worksheet of excel to see a chart with this name exists.  If so, then it updates this existing chart rather than producing a new chart every time.  If there is no chart with than name, it will create a new one.  The old chart type is still there, in the event you simply want to make multiple charts. The component outputs a string (A) which tells you where the component has updated or created a new chart.  Additionally, it is recommended that you not use a "stream to excel" function with formatting when actively updating a chart, as this will drastically reduce the refresh rate of your chart.  I have found using the basic stream data with a fully formatted has made updating almost instant.

PLEASE post a comment if you find any bugs, I am continuing to try all combinations to check for conflicts. 

 

Permalink Reply by Gabe Krause on January 12, 2011 at 12:48pm

Mr. David Mans, you are the man!

I'm not sure if comments are going somewhere else, because there aren't any here; but I just wanted to commend you. This looks brilliant! GH and Excel are such powerful tools by themselves; I get almost giddy thinking about what's possible by combining the two.

I have just a couple of questions at the moment. I've only done a quick look so far and have used the Write Components successfully; but, is there some setting I need to be mindful of for the Read Components to work? I can only get "List From XL" and "Table From XL" to work on the same cells that the Write Components ("List/Table To XL") are actively generating. Suppose I want to export a list to excel, do some numerical processing and then provide the list of results a few cells away. How do I get those new, processed cells back using your "List/Table From XL" components?

The second question: out of curiosity what the outputs for "List/Table To XL" components offer?

Regards

 

[Testing with xp64, GH v0.8.0004, Excel 2003, and GH-Excel_v0-02]

Permalink Reply by David Mans on January 12, 2011 at 7:35pm

Gabriel,
Im glad your enjoying it.

Both read components require a point input, the x value representing the column, the y value representing the row in excel.  This is the first cell that will be read.  Both have optional inputs "count" for List and "bounds" for Table.  For Table this is a point input which specifies the last cell (row and column) which will be read, if left blank, it simply runs till it finds an empty cell.  For List, the count sets the last item in a row or column which will be read.  This is determined by what which direction you specify with the "ByColumn" boolean. 

I hope this helps, please let me know if you run into any issues, or have suggestions for additional, or different functionality.

Thanks

Permalink Reply by Gabe Krause on January 14, 2011 at 1:54pm

Thanks David,

That's pretty much what your instruction say in the definition. (I failed to mention that I did actually scan your instructions.) The whole definition and supporting information you prepared is laid out nicely.

However, I did find the problem I was having and I believe that you may want to make an adjustment in your VB scripts to straighten them out. It's not really a big deal, once you know, but the x and y inputs for column and row happens to be reversed on the "Stream List/Table From Excel" components. That's the reason, I wasn't able to get any info from excel to gh -- my column and row addressing was reversed.

For clarity, I'll attach a photo and a sample ghx file where I'm simply sending data to Excel, then reading the same data back to GH. Just compare corresponding inputs.

Thanks

PS: In the picture, I hadn't yet realized what the outputs were. I see it now. Ignore the "What's this? And this?" panels.

Attachments:

• 2011-01-14_154824.png, 108 KB
• xyCrossed.ghx, 404 KB

Permalink Reply by Gabe Krause on January 14, 2011 at 2:27pm

Actually, regarding the outputs on the "To Excel" components, it looks like output "A" is supposed to be a confirmation of the start position (column,row) except that the column and row seem to be switched. Then output "B" appears that it's intended to be either the end position for data collection (column,row) or the range meaning length of column/row from start position to end position. But I can't really tell. Either one could be very useful.

I apologize if that's considered to be nitpicking. Your contributions are already incredibly useful as-is. With a couple refinements, it will be a clean and robust tool that will likely be used quite a bit in the future.

Permalink Reply by David Mans on January 14, 2011 at 2:46pm

Gabriel,

Thank you for the feedback, i prefer the phrase user debugging over nitpicking, and its very useful.  Ill take a look at these relationships tonight, make sure they all align properly, and post an update over the weekend.

Thank you

Permalink Reply by Mateusz Zwierzycki on January 15, 2011 at 9:09pm

can you make it work with open office ?

Permalink Reply by David Mans on January 19, 2011 at 9:49am

Mateusz,

I am developing that right now.  

Hopefully I will have a first post out by the weekend.

The trick has been accessing an active worksheet in open office without the need for the document name.

Permalink Reply by Mateusz Zwierzycki on January 19, 2011 at 9:58am

Waiting with crossed fingers :D

Permalink Reply by Thomas Bildsøe on February 3, 2011 at 2:42am

Any news yet?

Permalink Reply by David Mans on February 3, 2011 at 9:14am

Ah yes news...

We have success with efficiently accessing the Active worksheet and populating data.  However formatting some of the items requires a bit more effort than initially anticipated.  Current incomplete items as compared to the Excel version are (superscript, subscript, fill patterns, border styles, and charts.)  Everything else works.  I can tidy it up and post a ultra beta version tonight. 

Permalink Reply by Gabe Krause on February 3, 2011 at 9:33am

Others probably feel different; But personally, I will probably not use the formatting options. I think the linking of grasshopper and excel is the amazing part. I'll be using it to take advantage of excel's familiar math and iterative capabilities. Bring on the ultra beta! Function is more important than form in this case.

• ‹ Previous
• 1
• 2
• Next ›
• Page