Author Topic: How do you read a multi-cell range?  (Read 3507 times)

newbie73

  • Newbie
  • *
  • Posts: 15
  • Karma: +0/-0
    • View Profile
How do you read a multi-cell range?
« on: June 01, 2010, 09:43:50 PM »
How can I index into a multi-cell range via pyXLL?

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 568
  • Karma: +13/-1
    • View Profile
Re: How do you read a multi-cell range?
« Reply #1 on: June 01, 2010, 10:10:57 PM »
Hi,

you can pass a range as a list of lists using the array types (e.g. float[], int[] etc) described in the documentation here: http://www.pyxll.com/documentation/udfs#arrays (or see the array examples in the examples included in the download). If you use the var type and pass a range as the parameter it will also give you a list of lists. You can also pass ranges as numpy arrays.

The list passed to your function is a list of rows, and each row is a list of values. Here's an example that shows how to index the list of rows to get an individual value:

@xl_func("float[] array, int row, int col: float")
def test(array, row, col):
    return array[row][col]

Regards,
Tony.

newbie73

  • Newbie
  • *
  • Posts: 15
  • Karma: +0/-0
    • View Profile
Re: How do you read a multi-cell range?
« Reply #2 on: June 01, 2010, 10:32:05 PM »
I can handle this when the function has an array input parameter - my particular situation involves so many parameters that I cannot realistically pass them all to the function, so I am using the xl_macro function type.

When I read a range from the python function, I can access the individual cells within the range using hard coded numbers, yet when I attempt to index using variables I get the error:

cannot use object as index

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 568
  • Karma: +13/-1
    • View Profile
Re: How do you read a multi-cell range?
« Reply #3 on: June 01, 2010, 10:58:20 PM »
Hi,

oh ok. I think this is more of an Excel COM api question then, but I'll try and help.

You can get the value of a range using the Value property. If the range is a multi-cell range then the value will be a tuple of tuples, eg

value = range.Value[row][col]

The range object is also callable, returning another range, so you can also use:

value = range(row + 1, col + 1).Value

Note the +1s as Excel indices start from 1 not 0.

If you're doing something like this already and are getting that cannot use object as index error, I would double check the type of the index variable you're using to make sure it really is an int. Try doing a "print type(row)" and check it says <type 'int'>. Is it perhaps that you're using another range object and not the value of the range or something like that?

Regards,
Tony.

newbie73

  • Newbie
  • *
  • Posts: 15
  • Karma: +0/-0
    • View Profile
Re: How do you read a multi-cell range?
« Reply #4 on: June 02, 2010, 01:45:10 AM »
Something seems off - I have a 2x3 range defined as "square"
There is a single cell range defined as "square_output"

vals = xl.Range("square").Value
xl.Range("square_output").Value = vals[1][1]

this code accesses the value at row 2, col 2 of the "square" range, instead of the expected 1,1 value which would result with arrays starting from index = 1

Another issue is accessing the indices using variables - I try the following:

i = long()
j = long()

i = 1
j = 1

I can access data using vals[j] without any trouble, however when I attempt to loop through the range I get the Cannot use object as index error like this:

len_x = len(vals)
len_y = len(vals[0])

for i in range(0, len_x-1):
    for j in range(0, len_y-1):
        vals[j]


newbie73

  • Newbie
  • *
  • Posts: 15
  • Karma: +0/-0
    • View Profile
Re: How do you read a multi-cell range?
« Reply #5 on: June 02, 2010, 06:49:42 AM »
i = int()
i = 1
print type(i)
-> 'int'

for i in range(10):
    print type(i)

> 'instance'

This only happens when writing pyxll code, in a separate python module without any pyxll action, it prints out 'int'

newbie73

  • Newbie
  • *
  • Posts: 15
  • Karma: +0/-0
    • View Profile
Re: How do you read a multi-cell range?
« Reply #6 on: June 02, 2010, 07:09:48 AM »
I figured out the problem - i was redefining the range() function with an instance of an xl.Range which is what was generating the problem.

Thanks for the help!

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 568
  • Karma: +13/-1
    • View Profile
Re: How do you read a multi-cell range?
« Reply #7 on: June 02, 2010, 07:31:48 AM »
great, glad you were able to figure it out.

cheers,
Tony.