O'Reilly logo

Python Programming On Win32 by Mark Hammond, Andy Robinson

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Arrays

In Chapter 7 we built a view of an account. To do this, we fetched a 2D array of data from Python and looped over it, putting one number at a time into a grid. When we first started playing with Python and Excel, we expected to have to do something similar. Not so! Ask a range for its value, and you get an array. You can set the value of a range to an array, too. At this point we’ve typed a few more items into our spreadsheet to refer to (see Figure 9.2).

Passing arrays between Python and Excel
Figure 9.2. Passing arrays between Python and Excel

First, grab a horizontal array:

>>> xlSheet.Range('C3:E3').Value
((L'left', L'to', L'right'),)
>>>

Note that you get back Unicode strings, which you could convert to Python with a str() operation. When you asked for a single cell value earlier, the Python COM framework was smart enough to convert the Unicode string to a Python string; with a big array, you have to do the work.

Now, for a matrix with several rows and columns:

>>> xlSheet.Range('C5:D7').Value
((L'North', L'South'), (100.0, 200.0), (300.0, 400.0))
>>>

This returns a tuple of tuples, exactly the natural representation you would choose in Python. (For the rest of this section we use the term array to mean a Python structure of this shape—a list of lists, tuple of tuples, or list of tuples.) Finally, look at a vertical row, taking the items in column F:

>>> xlSheet.Range('F2:F4').Value
((1.0,), (2.0,), (3.0,))
>>>

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required