Setting an Excel range using an array using Python and comtypes?

Question

Using comtypesPython to control it seems like some magic happens behind the scenes that doesn't convert tuples and lists to VARIANTtypes:

# 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 comtypes docs :

When you pass simple sequences (lists or tuples) as VARIANTparameters, the COM server will receive VARIANT, containing a SAFEARRAYof VARIANT, with typecode VT_ARRAY| VT_VARIANT.

This is similar to what MSDN says about passing an array to a range value. I also found this page showing something similar in C #. Can someone tell me what I am doing wrong?

EDIT

, ( ):

>>>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 comment
# However, this still blanks out my range :(

Answer 1

, , python matlab, .

, . /// , xlsx, , xlsx python com.

from numpy import *
from win32com.client import DispatchEx
# DispatchEx opens up an independent instance of Excel so writing to a document won't interfere with any other instances you have running
def xlsxwrite(filename, sheet, data, cellstr, screenupdating = False, direction = 'h', visible = 0):
'''
Write to an excel document by setting ranges equal to arrays.
'''
xl = DispatchEx("Excel.Application")
xl.ScreenUpdating = screenupdating
xl.Visible = visible
try:
  excel_type = get_exceltype(filename)
  # Check to see if workbook exists, if it doesn't create workbook
  try:
      xlBook = xl.Workbooks.Open(filename)
  except:
      print '\nFile Doesnt Exist, Writing File...\n\n\n'
      xlBook = xl.Workbooks.Add()
      try:
          xlBook.SaveAs(filename, excel_type)
      except:
          xl.Quit()
          raise NameError('Error writing file: %s, check to make sure path exists' % filename)
  # Get wksht names
  wksht_names = [xlBook.Sheets(i).Name for i in range(1,xlBook.Sheets.Count+1)]
  # If 'sheet' variable is an integer, get sheet by index number, else get it by name, or add new one
  try:
      int(sheet)
      try:
          xlSheet = xlBook.Sheets(int(sheet))
      except:
          raise NameError('Error, referencing an invalid sheet')
  except:
      # If sheet input not in wksht names, add it
      if sheet not in wksht_names:
          print 'Worksheet, "%s", not found, Adding Worksheet' % sheet
          xlBook.Sheets.Add(After=xlBook.Sheets(xlBook.Sheets.Count)).Name = sheet
      xlSheet = xlBook.Sheets(sheet)
  # Convert Excel Range to Python Range
  row,col = getcell(cellstr)
  # Write out data
  output_dict, shp = data_export_cleaner(data, direction)
  a,b = shp
  start_cells = [(row,col+i) for i in range(b)]
  end_cells = [(row + a -1,col+i) for i in range(b)]
  for i in output_dict.keys():
      cell_range = eval('xlSheet.Range(xlSheet.Cells%s,xlSheet.Cells%s)' %   (start_cells[i],end_cells[i]))
      cell_range.Value = output_dict[i]
  # Save and close document, Quit Excel App
  xlBook.Close(True)
  xl.Quit()
  return
  except:
    xlBook.Close(False)
    xl.Quit()
    raise NameError('Error occurred while trying to write file')

def data_export_cleaner(data,direction):
  """
  Summary: Return data in a format that works with Excel Com (Numpy int32 for some reason was causing an error, have to turn it into a string, doesn't affect any formatting possibilities).
  Defaults: Going to set the default for writing data with len(shape(array(data))) == 1, such as a list, to horizontal, if you want to write it vertically, specify 'v', only applicable for lists.
  """
  darray = array(data)
  shp = shape(darray)
  if len(shp) == 0:
      darray = array([data])
      darray = darray.reshape(1,1)
  if len(shp) == 1:
      darray = array([data])
      if direction.lower() == 'v':
          darray = darray.transpose()
  shp = shape(darray)
  tempdict = dict()
  for i in range(shp[1]):
      tempdict[i] = [(str(darray[j,i]),) for j in range(shp[0])]
  return tempdict, shp


def get_exceltype(filename):
  format_dict = {'xlsx':51,'xlsm':52,'xlsb':50,'xls':56}
  temp = character_count(filename)
  if (temp['.'] > 1 or temp['.'] == 0):
      raise NameError('Error: Incorrect File Path Name, multiple or no periods')
  f_type = filename.split('.')
  f_type = f_type[len(f_type)-1]
  if f_type not in format_dict.keys():
      raise NameError('Error: Incorrect File Path, No excel file specified')
  else:
      return format_dict[f_type]


def character_count(a_string):
  temp = dict()
  for c in a_string:
      temp[c] = temp.get(c,0) + 1
  return temp



def getcell(cell):
  '''Take a cell such as 'A1' and return the corresponding numerical row and column in excel'''
  a = len(cell)
  temp_column = []
  row = []
  temp_row = []
  if a < 2:
      raise NameError('Error, the cell you entered is not valid')
  for i in range(a):
      if str.isdigit(cell[i])==False:
          temp_column.append(cell[i])
      else:
          temp_row.append(cell[i])
  row.append(string.join(temp_row,''))
  row = int(row[0])
  column = getnumericalcolumn(temp_column)
  return row, column


def getnumericalcolumn(column):
  '''Take an excel column specification such as 'A' and return its numerical equivalent in excel'''
  alpha = str(string.ascii_uppercase)
  alphadict = dict(zip(alpha,range(1,len(alpha)+1)))
  if len(column) == 1:
      numcol = alphadict[column[0]]
  elif len(column) == 2:
      numcol = alphadict[column[0]]*26 + alphadict[column[1]]
  elif len(column) == 3:
      numcol = 26**2 + alphadict[column[1]]*26 + alphadict[column[2]]
  return numcol

: Numpy, , , . , , script, , .

Answer 2

sheet.Range("C14", "D21").Value = vals. , API, .

( , tuple([(x,y) for x,y in zip('abcdefgh',xrange(8))]) - tuple((x, y) for x, y in zip('abcdefgh', xrange(8))), , -, . [(x, y) for x, y in zip('abcdefgh', xrange(8))].)