search

ericremoreynolds / excelpython

An open source, easy to use interface for calling Python code from Excel
http://ericremoreynolds.github.io/excelpython
BSD 2-Clause "Simplified" License
234 stars 58 forks source link

how do i return an n-dimensional array? #30

Closed Tooblippe closed 10 years ago

ericremoreynolds commented 10 years ago

You should simply be able to return tuples or "tuples of tuples" or numpy arrays from your function and they get converted into Excel cell value.

Remember to use your formula as an Excel array function.

Please see the tutorials they explain how to do this if I recall correctly.

Regards

Eric

Tooblippe commented 10 years ago

Thanx, It works when i return the Df.matrix or Df.values - work but you have to define the size of the return tuples by dragging in excel, With xlwings you can specify the upper left corner and it does the rest. I was just assuming it works the same. Maybe if Ihave time I will add this via pull request.

regards tobie

ericremoreynolds commented 10 years ago

If you know a way to do it that would be great! I personally don't know that it's possible from a user defined function.

ericremoreynolds commented 10 years ago

So just to expand a bit on the previous comment: it isn't actually possible to modify the calling workbook from within a UDF call. This is a feature of Excel, it simply won't let you change the workbook while the UDF is executing.

What you can do however, is launch a thread from the UDF passing it the address of the calling cell (which you can obtain from Application.Caller) then subsequently fill in the array values from the thread once the UDF has returned. For example, you could do this from the thread using xlwings.

However, I think this approach is probably complicated to implement robustly and error prone, and as such I am not really interested in making it a main feature of ExcelPython right now.

However if you go ahead with it please let me know how you get on!

Regards,

Eric.

Tooblippe commented 10 years ago

Thanx for the comments! xlwings and excel python is both cool and I want to support it….

Will the two projects move closer together in future?

T

On 07 Nov 2014, at 10:12, Eric Reynolds notifications@github.com wrote:

So just to expand a bit on the previous comment: it isn't actually possible to modify the calling workbook from within a UDF call. This is a feature of Excel, it simply won't let you change the workbook while the UDF is executing.

What you can do however, is launch a thread from the UDF passing it the address of the calling cell (which you can obtain from Application.Caller) then subsequently fill in the array values from the thread once the UDF has returned. For example, you could do this from the thread using xlwings.

However, I think this approach is probably complicated to implement robustly and error prone, and as such I am not really interested in making it a main feature of ExcelPython right now.

However if you go ahead with it please let me know how you get on!

Regards,

Eric.

— Reply to this email directly or view it on GitHub https://github.com/ericremoreynolds/excelpython/issues/30#issuecomment-62111242.

ericremoreynolds commented 10 years ago

Yes, that's the idea, in the long term we're looking to merge the two projects by importing ExcelPython's technology into xlwings to enable UDFs, but we're taking it one step at a time.

Thanks for your interest,

Eric.