Setting an Excel Range with an Array using Python and comtypes?
- by technomalogical
Using comtypes to drive Python, it seems some magic is happening behind the scenes that is not converting tuples and lists to VARIANT types:
# RANGE(“C14:D21”) has values
# Setting the Value on the Range with a Variant should work, but
# list or tuple is not getting converted properly it seems
>>>from comtypes.client import CreateObject
>>>xl = CreateObject("Excel.application")
>>>xl.Workbooks.Open(r'C:\temp\my_file.xlsx')
>>>xl.Visible = True
>>>vals=tuple([(x,y) for x,y in zip('abcdefgh',xrange(8))])
# creates:
#(('a', 0), ('b', 1), ('c', 2), ('d', 3), ('e', 4), ('f', 5), ('g', 6), ('h', 7))
>>>sheet = xl.Workbooks[1].Sheets["Sheet1"]
>>>sheet.Range["C14","D21"].Value()
(('foo',1),('foo',2),('foo',3),('foo',4),('foo',6),('foo',6),('foo',7),('foo',8))
>>>sheet.Range["C14","D21"].Value[()] = vals
# no error, this blanks out the cells in the Range
According to the comtypes docs:
When you pass simple sequences (lists or tuples) as VARIANT
parameters, the COM server
will receive a VARIANT containing a SAFEARRAY of VARIANTs with the
typecode VT_ARRAY | VT_VARIANT.
This seems to be inline with what MSDN says about passing an array to a Range's Value. I also found this page showing something similar in C#. Can anybody tell me what I'm doing wrong?
EDIT
I've come up with a simpler example that performs the same way (in that, it does not work):
>>>from comtypes.client import CreateObject
>>>xl = CreateObject("Excel.application")
>>>xl.Workbooks.Add()
>>>sheet = xl.Workbooks[1].Sheets["Sheet1"]
# at this point, I manually typed into the range A1:B3
>>> sheet.Range("A1","B3").Value()
((u'AAA', 1.0), (u'BBB', 2.0), (u'CCC', 3.0))
>>>sheet.Range("A1","B3").Value[()] = [(x,y) for x,y in zip('xyz',xrange(3))]
# Using a generator expression, per @Mike's comment
# However, this still blanks out my range :(
