Author Topic: Dealing with arrays  (Read 661 times)

apal

  • Newbie
  • *
  • Posts: 2
  • Karma: +0/-0
    • View Profile
Dealing with arrays
« on: January 24, 2014, 03:34:44 PM »
Hi,

I was having some issue dealing with arrays and going back and forth between Excel and Python, usually regarding the orientation of the array range I've selected in Excel.

One such issue I was having was with the following code:

Code: [Select]
contracts = xl.Sheets(pSheetName).Range("V3:AC3").Value
xl.Sheets(ccSheetName).Range("K8:R8").Value = contracts
xl.Sheets(ccSheetName).Range("K10").Value = len(contracts)

I have list of 8 contract names arranged horizontally, and store them in an array called "contracts." When I set a range of 8 cells between 'K8' and 'R8', it pastes the 8 contracts perfectly. However, cell 'K10' displays a value of 1, even though contracts has 8 items in it. Is there a reason for this behavior that I'm not aware of, or a way to work around it?

And as a follow up question, if instead of pasting the contents of the array "contracts" in cells "K8:R8," I attempted to paste them in vertical fashion through cells "K8:K15," I end up getting 8 cells all filled with only the element in contents[0]. Is there a way to easily be able to pass arrays between python and excel so they aren't adversely affected by orientation?

Thanks and I would appreciate any help!

Tony

  • Administrator
  • Hero Member
  • *****
  • Posts: 541
  • Karma: +12/-0
    • View Profile
Re: Dealing with arrays
« Reply #1 on: January 24, 2014, 03:52:21 PM »
Hi,

try printing out what the range's Value actually is. You should see it's a list of lists - not a 1d list as you think it is. This makes sense as you are actually dealing with a 2d grid of data, even if you are only selecting a single row from it.

len(contracts) is 1 because it's a list of lists, of length 1 (1 row). Then length of (contracts[0]) is what you're looking for.

If you set Value to a list of lists it will do what you expect.

You should read this:
https://www.pyxll.com/docs/udfs.html#using-arrays

You can also use pyxll's get_type_converter function to convert from a numpy array to a list of lists, eg:

r.Value = pyxll.get_type_converter("numpy_array", "float[]")(my_numpy_array)

Best regards,
Tony



apal

  • Newbie
  • *
  • Posts: 2
  • Karma: +0/-0
    • View Profile
Re: Dealing with arrays
« Reply #2 on: January 24, 2014, 04:13:33 PM »
Great, that solves my issue, appreciate the help!