使用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: empmy_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 docs:
当您将简单序列(列表或元组)作为 VARIANT 传递时参数，COM服务器将收到一个 VARIANT，其中包含 VARIANT 的 SAFEARRAY，其中类型代码 VT_ARRAY |VT_VARIANT.
When you pass simple sequences (lists or tuples) as
VARIANTparameters, the COM server will receive aVARIANTcontaining aSAFEARRAYofVARIANTs with the typecodeVT_ARRAY|VT_VARIANT.
这似乎与 MSDN 所说的内容一致 关于将数组传递给 Range 的值.我还发现 this page 在 C# 中显示了类似的内容.谁能告诉我我做错了什么?
This seems to be inline with what MSDN says about passing an array to a Range's Value. I also found this page showing something similar in C#. Can anybody tell me what I'm doing wrong?
编辑
我想出了一个更简单的例子，它执行相同的方式(因为它不起作用):
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 :(
我花了很多时间试图找出解决这个问题的办法，以便能够完全用 python 代替 matlab，在各种不同的论坛上阅读没有真正的直接答案.
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.
这是我运行良好的强大解决方案.我必须编写每天/每周/每月/每季度的报告，这些报告经常写入 xlsx，这个功能比他们使用 python & 写入 xlsx 的一些信息要好得多.com.
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，因为它对于以我想要编写的格式创建表非常有用，因此这是以下函数工作所需的库.我知道所有这些函数都可以组合起来创建一个类，但是因为这个函数是在脚本中调用的，而且它们对于将它创建为一个类并没有真正的好处，所以我没有这样做.
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.
这篇关于使用 Python 和 comtypes 使用数组设置 Excel 范围?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！