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

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.

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 '
File Doesnt Exist, Writing File...


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