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

Setting an Excel Range with an Array using Python and comtypes?

提问by technomalogical

Using comtypesto drive Python, it seems some magic is happening behind the scenes that is not converting tuples and lists to VARIANTtypes:

使用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

According to the comtypesdocs:

根据comtypes文档：

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

当您将简单序列（列表或元组）作为VARIANT参数传递时，COM 服务器将收到一个VARIANT包含类型代码为| 的aSAFEARRAY的VARIANTs VT_ARRAY。VT_VARIANT.

This seems to be inline with what MSDN saysabout passing an array to a Range's Value. I also found this pageshowing something similar in C#. Can anybody tell me what I'm doing wrong?

这似乎与MSDN关于将数组传递给 Range 值的说法一致。我还发现这个页面在 C# 中显示了类似的东西。谁能告诉我我做错了什么？

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

回答by wildehahn

I've spent a lot of time trying to figure out a solution to this problem to be able to fully substitute python for matlab, reading on various different forums with no real direct answer.

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

Here is my robust solution that works very well. I have to write daily/weekly/monthly/quarterly reports that write to xlsx a lot, this function works much better than some of the information out their about writing to xlsx using python & com.

这是我的强大解决方案，效果很好。我必须每天/每周/每月/每季度编写大量写入 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

Notes: I use Numpy a lot because it is very useful for creating the tables in the format I want to write them, so that is a required library for the below functions to work. I know all these functions could combined to create a class, but because this function is called in a script and their isn't really a significant benefit to creating it as a class, I haven't done so.

注意：我经常使用 Numpy，因为它对于以我想要编写的格式创建表非常有用，因此这是以下函数工作所需的库。我知道所有这些函数都可以组合起来创建一个类，但是因为这个函数是在脚本中调用的，而且它们对于将它创建为一个类并没有真正的好处，所以我没有这样做。

回答by Mike Graham

Try sheet.Range("C14", "D21").Value = vals. I'm not quite sure how the API is structured, but it works for me.

试试sheet.Range("C14", "D21").Value = vals。我不太确定 API 的结构如何，但它对我有用。

(Also, tuple([(x,y) for x,y in zip('abcdefgh',xrange(8))])can be replaced with a generator expression tuple((x, y) for x, y in zip('abcdefgh', xrange(8))), which is seems a bit cleaner habit to have. In this particular case, just the list comprehension [(x, y) for x, y in zip('abcdefgh', xrange(8))]would also do.)

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