Python Programming On Win32

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).

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,))
>>>