我花了很多时间试图找出解决这个问题的方法,以便能够完全用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
笔记:
我知道所有这些函数都可以组合起来创建一个类,但是由于这个函数是在脚本中调用的,并且它们对于将其创建为类并不是一个很大的好处,所以我没有这样做。
