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