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

Question

使用comtypes来操作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

根据comtypes的文档：

当你将简单的序列（列表或元组）作为VARIANT参数传递时，COM服务器会接收到一个包含SAFEARRAY的VARIANT，这个SAFEARRAY里面装的是类型代码为VT_ARRAY | VT_VARIANT的VARIANT。

这似乎和MSDN上说的关于将数组传递给Range的值是一致的。我还找到了一个页面，里面展示了在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

试试这个：sheet.Range("C14", "D21").Value = vals。我不太确定这个接口是怎么设计的，但对我来说是有效的。

（另外，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))] 也可以。）

Answer 2

我花了很多时间试图找到一个解决方案，想要完全用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，因为它在创建我想要的表格格式时非常有用，所以下面的函数需要这个库才能正常工作。 我知道这些函数可以组合成一个类，但因为这个函数是在脚本中调用的，创建成类并没有什么显著的好处，所以我没有这样做。