使用Python和comtypes为数组设置Excel范围？

Question

使用^{}来驱动Python，似乎有一些魔术正在幕后发生，它们没有将元组和列表转换为VARIANT类型：

# 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

根据comtypesdocs：

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.

这似乎与what MSDN says有关将数组传递到范围值的内联。我还发现this page在C中显示了类似的内容。有人能告诉我我做错了什么吗？

编辑

我想出了一个更简单的例子，其执行方式与此相同（在这方面，它不起作用）：

>>>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 :(

Answer 1

我花了很多时间试图找出解决这个问题的方法，以便能够完全用python代替matlab，在各种不同的论坛上阅读，却没有真正的直接答案。

这是我的健壮的解决方案，效果很好。我必须编写每天/每周/每月/季度的报告，这些报告经常写入xlsx，这个功能比使用python&com向xlsx写入的一些信息要好得多。

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，因为它对于以我想编写的格式创建表非常有用，所以这是下面函数工作所必需的库。 我知道所有这些函数都可以组合起来创建一个类，但是由于这个函数是在脚本中调用的，并且它们对于将其创建为类并不是一个很大的好处，所以我没有这样做。