import win32com.client
import win32com.client.dynamic
import win32api
from pywintypes import UnicodeType, TimeType
import numpy as np
import mso
#################################################################################################################
# Warning! Use C:\Python27\Lib\site-packages\win32com\client\makepy.py and import Microsoft excel class first #
# Class can work only with single workbook #
#################################################################################################################
#import win32com.client.constants
import inspect
####################################################################
#Keep in mind that the Excel Object Model has the following hierarchy: Application, WorkBook, Sheet, Range, and Cell.
#www.icodeguru.com/WebServer/Python-Programming-on-Win32/ch09.htm
#xlApp.ActiveSheet.Cells(1,1).Value = 'Python Rules!'
#xlApp.ActiveWorkbook.ActiveSheet.Cells(1,1).Value = 'Python Rules!'
#xlApp.Workbooks("Book1").Sheets("Sheet1").Cells(1,1).Value = "Python Rules!"
#xlApp.Workbooks(1).Sheets(1).Cells(1,1).Value = "Python Rules!"
#xlApp.Workbooks(1).Sheets(1).Cells(1,1).Value = "Python Rules!"
# xlBook = xlApp.Workbook(1)
# xlSheet = xlApp.Sheets(1)
# xlSheet.Cells(1,1).Value = "Python Rules!"
#xlBook.Sheets(1).Name
#'Sheet1'
#xlBook.Sheets[1].Name
#'Sheet1'
#Time
# import time
# now = time.time()
# now # how many seconds since 1970?
#923611182.35
# import pythoncom
# time_object = pythoncom.MakeTime(now)
# int(time_object) # can get the value back?BR> 923611182
# xlSheet.Cells(3,1).Value = time_object # ?r send it
# xlSheet.Cells(3,1).Value
#<time object at 188c080>
#
# myRange1 = xlSheet.Cells(4,1) # one-cell range
# myRange2 = xlSheet.Range("B5:C10") # excel notation
# myRange3 = xlSheet.Range(xlSheet.Cells(2,2), xlSheet.Cells(3,8))
#wksht_names = [xlBook.Sheets(i).Name for i in range(1,xlBook.Sheets.Count+1)]
#sheet.Range("C14", "D21").Value = vals
# xlBook.Sheets.Add(After=xlBook.Sheets(xlBook.Sheets.Count)).Name = sheet
#
#used = ws.UsedRange
#nrows = used.Row + used.Rows.Count - 1
#ncols = used.Column + used.Columns.Count - 1
###############################################################
class excel_com(object):
def __init__(self,error_handler):
self.e=error_handler
self.cache_cell_objects=True #Set to false if memory problem exists for large sheets
def connect(self,screen_updating=True,show_app=True):
'''Connect to Excel application
show_app - if True application window will be visible, else application window will be hidden
screen_updating - if true screen will be updated in real time, else screen will not reflect updates (can speed up execution)
'''
#################################################################################################################
# Warning! Use C:\Python27\Lib\site-packages\win32com\client\makepy.py and import Microsoft excel class first #
#################################################################################################################
#gencache.EnsureDispatch http://timgolden.me.uk/python/win32_how_do_i/generate-a-static-com-proxy.html
#THis will ensure module created, so usage of win32com.client.constants will be possible (static proxy)
self.xlApp = win32com.client.gencache.EnsureDispatch("Excel.Application")
self.xlApp.ScreenUpdating=screen_updating
#Other possible ways:
#self.xlApp = win32com.client.dynamic.Dispatch('Excel.Application')
#self.xlApp = win32com.client.Dispatch('Excel.Application')#This require import of module first (static proxy)
if show_app:
self.show_app()
else:
self.hide_app()
#Define some useful constants
self.xlCategory=getattr(win32com.client.constants,'xlCategory')
self.xlSeries=getattr(win32com.client.constants,'xlSeries')
self.xlValue=getattr(win32com.client.constants,'xlValue')
self.xlPasteValues=getattr(win32com.client.constants,'xlPasteValues')
self.xlNone=getattr(win32com.client.constants,'xlNone')
self.xlTop=getattr(win32com.client.constants,'xlTop')
self.xlBottom=getattr(win32com.client.constants,'xlBottom')
self.xlLeft=getattr(win32com.client.constants,'xlLeft')
self.xlRight=getattr(win32com.client.constants,'xlRight')
self.debug=False
self.sheet_names=[]
def debug_mode(self):
return self.e.debug_mode()
#*************************** Application operations ***************************
def SetDisplayAlerts(self,val):
"""
If false display alerts will not be shown
"""
assert val in [True, False]
self.xlApp.DisplayAlerts=val
def set_screen_updating(self,val):
assert val in [True, False]
self.xlApp.ScreenUpdating=val
def close_app(self):
del self.xlApp
def show_app(self):
"""
Show Excel application window
"""
self.xlApp.Visible = 1
def hide_app(self):
"""
Hide Excel application window
"""
self.xlApp.Visible = 0
def SetCutCopyMode(self,mode):
"""
False - Not in Cut or Copy mode.
xlCopy - In Copy mode.
xlCut - In Cut mode.
"""
self.xlApp.CutCopyMode = mode
def GetCutCopyMode(self):
"""
False - Not in Cut or Copy mode.
xlCopy - In Copy mode.
xlCut - In Cut mode.
"""
return self.xlApp.CutCopyMode
#*************************** Workbook operations ***************************
def open_workbook(self,filename):
'''
Class can work only with single workbook
'''
self.xlBook = self.xlApp.Workbooks.Open(filename)
self.wb_open=True
self.sheet_names=self.get_list_of_sheets()
self.worksheet_objects={}
self.worksheet_cell_objects={}
def add_workbook(self):
'''
Add workbook to current worksheet
Class can work only with single workbook
'''
self.xlBook = self.xlApp.Workbooks.Add()
self.wb_open=True
self.worksheet_cell_objects={}
self.sheet_names=self.get_list_of_sheets()
self.worksheet_objects={}
return True
def save(self, newfilename=None,silent_owerwrite=False):
'''
Save workbook to file
If file name isn't specified workbook will be saved to same file from which it was opened
silent_owerwrite - if True will overwrite existing file without prompt
'''
#xlLocalSessionChanges=getattr(win32com.client.constants,'xlLocalSessionChanges')
#xlExclusive=getattr(win32com.client.constants,'xlExclusive')
if newfilename:
assert isinstance(newfilename, str)
self.filename = newfilename
try:
#self.xlBook.SaveAs(newfilename,AccessMode=xlExclusive,ConflictResolution=xlLocalSessionChanges) #not working :(
if silent_owerwrite:self.SetDisplayAlerts(False)
result=self.xlBook.SaveAs(newfilename)
if silent_owerwrite:self.SetDisplayAlerts(True)
except:
self.e.print_error("Unable to save workbook to file '%s'"%(newfilename))
else:
try:
result=self.xlBook.Save()
except:
self.e.print_error("Unable to save workbook")
return True
def close_workbook(self):
#wb_name=self.xlBook.Name
#self.xlApp.Windows(wb_name).Activate()
#self.xlApp.ActiveWindow.Close(SaveChanges=0)
#self.xlApp.Windows(wb_name).Close(SaveChanges=0)
self.xlBook.Close(SaveChanges=0)
self.wb_open=False
self.sheet_names=[]
#*************************** Sheet operations ***************************
def gridlines_off(self,sheet):
#DisplayGridlines = False
assert isinstance(sheet, str)
activesheet=self.xlApp.ActiveSheet.Name
if activesheet!=sheet:
self._activate_sheet(sheet)
self.xlApp.ActiveWindow.DisplayGridlines = False
if activesheet!=sheet:
self._activate_sheet(activesheet)
def _hide_sheet(self,sheet):
assert isinstance(sheet, str)
sht = self._get_sheet(sheet)
sht.Visible = False
def _add_sheet(self,name):
assert isinstance(name, str)
assert len(name)<31
if not self._sheet_exists(name):
sheet=self.xlBook.Sheets.Add()
sheet.Name=name
self.sheet_names.append(name)
if name not in self.worksheet_cell_objects:
self.worksheet_cell_objects[name]={}
return True
else:
self.e.print_error("Unable to add sheet '%s'. Sheet exists"%(name))
return False
def _activate_sheet(self,name):
assert isinstance(name, str)
#self.xlApp.Windows(name).Activate()
self.xlApp.Sheets(name).Select()
def delete_sheet(self,name):
'''
Delete worksheet by name
Return False if sheet with given name not exists
'''
assert isinstance(name, str)
if self._sheet_exists(name):
self.xlBook.Sheets(name).Delete()
self.sheet_names.remove(name)
if name in self.worksheet_cell_objects:
self.worksheet_cell_objects[name]={}
return True
else:
if self.debug:
print "Sheet %s not exists"%(name)
return False
def _sheet_exists(self,name):
assert isinstance(name, str)
return name in self.sheet_names
def get_list_of_sheets(self):
'''
Get list of worksheet names
'''
numofsheets=self.xlBook.Worksheets.Count
sheet_names=[]
for n in range(1,numofsheets+1):
current_name=self.xlBook.Worksheets(n).Name
sheet_names.append(current_name)
if current_name not in self.worksheet_cell_objects:
self.worksheet_cell_objects[current_name]={}
return sheet_names
#*************************** Chart operations ***************************
def _set_3d_rotation(self,chrt,properties):
"""
ActiveSheet.Shapes("Chart 1").ThreeD.RotationX = 20
ActiveSheet.Shapes("Chart 1").ThreeD.RotationY = 140
ActiveSheet.Shapes("Chart 1").ThreeD.FieldOfView = 35
"""
if 'RotationX' in properties:
chrt.SeriesCollection(1).Format.ThreeD.RotationX=properties['RotationX']
if 'RotationY' in properties:
chrt.SeriesCollection(1).Format.ThreeD.RotationY =properties['RotationY']
if 'FieldOfView' in properties:
chrt.SeriesCollection(1).Format.ThreeD.FieldOfView =properties['FieldOfView']
def _add_3d_chart(self,sheet,Left=100, Width=375, Top=75, Height=225):
sht = self._get_sheet(sheet)
chrt=sht.Shapes.AddChart().Chart
self._set_chart_type(chrt,'xlSurface')
chrt.ChartType=xlSurface
chrt.SetElement (mso.msoElementSeriesAxisTitleRotated)
return chrt
def _add_chart_ws(self,sheet,Left=100, Width=375, Top=75, Height=225):
#chrt=self.xlBook.Charts.Add()
#where=getattr(win32com.client.constants,'xlLocationAsObject')
#chrt=chrt.Location(Where=where, Name="data")
#return chrt
#exit()
sht = self._get_sheet(sheet)
return sht.ChartObjects().Add(Left=Left, Width=Width, Top=Top, Height=Height).Chart
def _add_chart_text(self,chrt,txt,left=292.25,top=56.25,width=72,height=72):
"""
Allows to add text label to chart
"""
label=chrt.Shapes.AddLabel(mso.msoTextOrientationHorizontal, left,top,width,height )
label.TextFrame2.TextRange.Characters.Text = txt
def _set_chart_type(self,chrt,type):
assert (isinstance(type, str))
attr=getattr(win32com.client.constants,type)
chrt.ChartType=attr
if type=='xlColumnClustered':
#ActiveChart.ChartGroups(1).GapWidth = 60
chrt.ChartGroups(1).GapWidth = 10
#More on chartgroups http://stackoverflow.com/questions/19841561/excel-in-which-ways-can-one-create-chartgroups
if type in ['xlSurface','xlSurfaceTopView']:
chrt.SetElement (mso.msoElementSeriesAxisTitleRotated)
def set_axes_crosses_left(self,chrt):
#Axes(xlCategory).Crosses = xlMinimum
chrt.Axes(self.xlCategory).Crosses = getattr(win32com.client.constants,'xlMinimum')
def set_x_logarithmic(self,chrt):
#ActiveChart.Axes(xlCategory).ScaleType = xlLogarithmic
xlLogarithmic=getattr(win32com.client.constants,'xlLogarithmic')
chrt.Axes(self.xlCategory).ScaleType = xlLogarithmic
def set_y_logarithmic(self,chrt):
#ActiveChart.Axes(xlValue).ScaleType = xlLogarithmic
xlLogarithmic=getattr(win32com.client.constants,'xlLogarithmic')
chrt.Axes(self.xlValue).ScaleType = xlLogarithmic
def set_chart_xvalue_labels_oruentation(self,chrt,orientation):
"""
ActiveChart.Axes(xlCategory).TickLabels.Orientation = xlTickLabelOrientationUpward
Other possible values for the Orienteration property are:
xlTickLabelOrientationAutomatic
xlTickLabelOrientationHorizontal
xlTickLabelOrientationDownward
xlTickLabelOrientationVertical
"""
try:
orientation=int(orientation)
except:
orientation=getattr(win32com.client.constants,orientation)
chrt.Axes(self.xlCategory).TickLabels.Orientation = orientation
def AxisBetweenCategories(self,chrt,type,val):
chrt.Axes(getattr(win32com.client.constants,type)).AxisBetweenCategories = val
def set_chart_ticklabels_numberformat(self,chrt,format):
if 'xnumformat' in format:
#print "xlCategory",format['xnumformat']
chrt.Axes(self.xlCategory).TickLabels.NumberFormat = r'#,##%s'%(format['xnumformat'])
if 'ynumformat' in format:
#print "xlValue",format['ynumformat']
chrt.Axes(self.xlValue).TickLabels.NumberFormat = r'#,##%s'%(format['ynumformat'])
def set_gridlines(self,chrt,format):
if 'xlCategory' in format:
if 'HasMajorGridlines' in format['xlCategory']:
chrt.Axes(self.xlCategory).HasMajorGridlines=format['xlCategory']['HasMajorGridlines']
if 'TickMarkSpacing' in format['xlCategory']:
chrt.Axes(self.xlCategory).TickMarkSpacing=format['xlCategory']['TickMarkSpacing']
if 'MajorUnit' in format['xlCategory']:
chrt.Axes(self.xlCategory).MajorUnit=format['xlCategory']['MajorUnit']
if 'xlValue' in format:
if 'HasMajorGridlines' in format['xlValue']:
chrt.Axes(self.xlValue).HasMajorGridlines=format['xlValue']['HasMajorGridlines']
if 'MajorUnit' in format['xlValue']:
chrt.Axes(self.xlValue).MajorUnit=format['xlValue']['MajorUnit']
if 'TickMarkSpacing' in format['xlValue']:
chrt.Axes(self.xlValue).TickMarkSpacing=format['xlValue']['TickMarkSpacing']
def set_chart_axis_minmax(self,chrt,data):
"""
ActiveChart.Axes(xlValue).MaximumScale = 10
ActiveChart.Axes(xlValue).MaximumScale = -40
ActiveChart.Axes(xlCategory).MinimumScale = 0.001
ActiveChart.Axes(xlCategory).MaximumScale = 100
"""
if 'xmin' in data:
chrt.Axes(self.xlCategory).MinimumScale=data['xmin']
if 'xmax' in data:
chrt.Axes(self.xlCategory).MaximumScale=data['xmax']
if 'ymin' in data:
chrt.Axes(self.xlValue).MinimumScale=data['ymin']
if 'ymax' in data:
chrt.Axes(self.xlValue).MaximumScale=data['ymax']
def set_chart_data(self,chrt,data):
"""
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).Name = "=psrr_group!$A$2"
ActiveChart.SeriesCollection(1).XValues = "=psrr_group!$B$1:$O$1"
ActiveChart.SeriesCollection(1).Values = "=psrr_group!$B$2:$O$2"
data - list of dicts in format:
'Name':
'Values'
'XValues'
'type'
"""
assert (isinstance(data, list))
for s in data:
series= chrt.SeriesCollection().NewSeries()
if 'type' in s:
type_num=getattr(win32com.client.constants,type)
series.ChartType=type_num
eval_val= self.evaluate(s['Name'])
if eval_val in [-2146826259,-2146826273]:#If evaluate function returned error
setattr(series,'Name','%s'%(s['Name']))
else:
setattr(series,'Name','%s'%(eval_val))
for attr in ['Values','XValues']:
if attr in s:
setattr(series,attr,"=%s"%(s[attr]))
if 'rgb' in s and s['rgb']!=None:
series.Format.Line.ForeColor.RGB = win32api.RGB(s['rgb'][0],s['rgb'][1],s['rgb'][2])
if 'data_labels' in s and s['data_labels']:
series.ApplyDataLabels()
def set_title(self,chrt,info):
"""
.HasTitle = True
.ChartTitle.Characters.Text = "Tools Sales for Qtr 1"
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales"
"""
chrt.HasTitle = True
if 'title' in info:
chrt.ChartTitle.Characters.Text =info['title']
xlPrimary=getattr(win32com.client.constants,'xlPrimary')
if 'ytitle' in info:
chrt.Axes(self.xlValue, xlPrimary).HasTitle = True
chrt.Axes(self.xlValue, xlPrimary).AxisTitle.Characters.Text = info['ytitle']
if 'xtitle' in info:
chrt.Axes(self.xlCategory, xlPrimary).HasTitle = True
chrt.Axes(self.xlCategory, xlPrimary).AxisTitle.Characters.Text = info['xtitle']
if 'depth_title' in info:
#ActiveChart.Axes(xlSeries, xlPrimary).AxisTitle.Text = "depth"
#Selection.Format.TextFrame2.TextRange.Characters.Text = "depth"
chrt.Axes(self.xlSeries).HasTitle = True
chrt.Axes(self.xlSeries, xlPrimary).AxisTitle.Characters.Text = info['depth_title']
def set_legend(self,chrt,info):
"""
ActiveChart.ChartArea.Select
ActiveChart.Legend.Select
Selection.Delete
"""
if 'position' in info:
assert info['position'] in ['xlTop','xlBottom','xlLeft','xlRight','None']
if info['position']=='None':
chrt.Legend.Delete()
else:
chrt.Legend.Position=getattr(win32com.client.constants,info['position'])
#*************************** OLEObjects ***************************
def _insert_file(self,sheet,filename):
#self.xlApp.Workbooks.Open(Filename=filename)
sht = self._get_sheet(sheet)
sht.OLEObjects().Add(Filename=filename,Link=False, DisplayAsIcon=True,IconLabel='config')
#sht.OLEObjects().Add(Filename=filename,Link=False, DisplayAsIcon=True,IconFileName=r"C:\Windows\Installer\{90140000-0011-0000-0000-0000000FF1CE}\xlicons.exe",IconIndex=0,IconLabel=filename)
#*************************** ***************************
def evaluate(self,strFormula):
return self.xlApp.Evaluate(strFormula)
def _columns_auto_fit(self,sheet,col1,col2=None):
assert isinstance(sheet, str)
#Cells.EntireColumn.AutoFit
#Columns("A:C").EntireColumn.AutoFit
sht = self._get_sheet(sheet)
if col2==None:
assert (col1>0)
assert isinstance(col1, int)
col_str=self._colnum_to_letter(sheet,col1)
else:
assert (col1>0 and col2>0)
assert (isinstance(col1, int) and isinstance(col2, int))
col1=self._colnum_to_letter(sheet,col1)
col2=self._colnum_to_letter(sheet,col2)
col_str="%s:%s"%(col1,col2)
sht.Columns(col_str).EntireColumn.AutoFit()
def _select_range(self,sheet,row1, col1,row2, col2):
'''
Select range of cells
row1, col1 - upper left cell
row2, col2 - lower right cell
'''
range=self._get_range_object(sheet,row1, col1,row2, col2)
range.Select()
def _copy_range(self,sheet,row1, col1,row2, col2):
"""
Copy range of cells to clipboard
"""
range=self._get_range_object(sheet,row1, col1,row2, col2)
range.Copy()
def _past_values_range(self,sheet,row1, col1,row2, col2, SkipBlanks=False, Transpose=False):
"""
Past ONLY values from clipboard to range of cells defined by row1, col1,row2, col2
"""
#Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
range=self._get_range_object(sheet,row1, col1,row2, col2)
range.PasteSpecial(Paste=self.xlPasteValues, Operation=self.xlNone, SkipBlanks=SkipBlanks, Transpose=Transpose)
def _past_range(self,sheet,row, col):
"""
General past
This will past data from the clipboard to place defined by left upper corner
"""
#Range("A14").Select
#ActiveSheet.Paste
r=self._get_range_object(sheet,row, col)#Get object of single cell range
r.Select()#Select range
sht = self._get_sheet(sheet)#Get sheet object
sht.Paste()#Paste data from clipboarde
def _select_cells_col(self,sheet,row, col,numofcells):
assert isinstance(numofcells, int)
assert numofcells>0
self._select_range(sheet,row,col,row,col+numofcells-1)
def _merge_selection(self):
self.xlApp.Selection.Merge()
def _merge(self,sheet,row1, col1,row2, col2):
range=self._get_range_object(sheet,row1, col1,row2, col2)
range.Merge()
def _merge_cells_col(self,sheet,row, col,numofcells):
assert isinstance(numofcells, int)
assert numofcells>0
self._merge(sheet,row,col,row,col+numofcells-1)
def _colnum_to_letter(self,sheet,col): # col is 1 based
"""
Converts column number to Excel column letter
Fast without calling Excel application
"""
excelCol = str()
div = col
while div:
(div, mod) = divmod(div-1, 26) # will return (x, 0 .. 25)
excelCol = chr(mod + 65) + excelCol
return excelCol
def _rowcol_to_cell(self,sheet,row,col,abs=False):
"""
Returns address of cell from row and column numbers
abs=False will return address in format A1
abs=True will return address in format $A$1
"""
assert (isinstance(row, int) and isinstance(col, int))
assert (row>0 and col>0)
if abs:
return "$%s$%s"%(self._colnum_to_letter(sheet,col),str(row))
else:
return "%s%s"%(self._colnum_to_letter(sheet,col),str(row))
#*************************** GET ***************************
def _get_cell(self,sheet_name,row,col):
'''
Returns Excel cell object
'''
assert (isinstance(row, int) and isinstance(col, int))
assert (row>0 and col>0)
assert isinstance(sheet_name, str)
sht = self._get_sheet(sheet_name)
if self.cache_cell_objects:
if (row, col) not in self.worksheet_cell_objects[sheet_name]:
self.worksheet_cell_objects[sheet_name][(row, col)]=sht.Cells(row, col)
return self.worksheet_cell_objects[sheet_name][(row, col)]
else:
return sht.Cells(row, col)
def _get_sheet(self,name):
'''
Get excel sheet object by name
'''
assert isinstance(name, str)
if self._sheet_exists(name):
if name not in self.worksheet_objects:
self.worksheet_objects[name]=self.xlBook.Worksheets(name)
return self.worksheet_objects[name]
else:
self.e.print_error("Unable to get sheet object. Sheet '%s' not exists"%(name))
return False
def _get_range_object(self,sheet,row1, col1,row2=None, col2=None):
'''
Returns excel range object
row1, col1 - upper left cell
row2, col2 - lower right cell
If row2, col2 not set range of single cell will be returned
'''
assert (isinstance(row1, int) and isinstance(col1, int))
assert (row1>0 and col1>0)
if (row2!=None and col2!=None):
assert (isinstance(row2, int) and isinstance(col2, int))
assert (row2>0 and col2>0)
assert row2>=row1
assert col2>=col1
assert isinstance(sheet, str)
sht = self._get_sheet(sheet)
if (row2!=None and col2!=None):
range_object=sht.Range(self._get_cell(sheet,row1,col1), self._get_cell(sheet,row2,col2))
else:
range_object=sht.Range(self._rowcol_to_cell(sheet,row1,col1))
return range_object
def get_cell_value(self, sheet, row, col):
"Get value of one cell"
assert (isinstance(row, int) and isinstance(col, int))
assert (row>0 and col>0)
assert isinstance(sheet, str)
return self._get_cell(sheet,row,col).Value
def _get_range_value(self,sheet,row1, col1,row2, col2):
def numpyfixStrings(item):
if type(item) is UnicodeType:
return str(item)
else:
return item
f = np.vectorize(numpyfixStrings,otypes=[np.ndarray])
assert (isinstance(row1, int) and isinstance(col1, int))
assert (isinstance(row2, int) and isinstance(col2, int))
assert isinstance(sheet, str)
assert (row1>0 and col1>0)
assert (row2>0 and col2>0)
sht = self._get_sheet(sheet)
return f(np.array(sht.Range(self._get_cell(sheet,row1,col1), self._get_cell(sheet,row2,col2)).Value))
#*************************** SET ***************************
def _set_sort(self,sheet,config):
"""
'full_range':range of all data
'range_list': list of columns
{'range':, 'order':}
where 'order':xlDescending,xlAscending
ActiveWorkbook.Worksheets("inv_cross1").Sort.SortFields.Add Key:=Range( _
"B2:B52"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
or
range.Sort(Key1=self.get_range(key_cell), Order1=1, Header=0, OrderCustom=1, MatchCase=False, Orientation=1)
"""
sht = self._get_sheet(sheet)
xlSortOnValues=getattr(win32com.client.constants,'xlSortOnValues')
xlSortNormal=getattr(win32com.client.constants,'xlSortNormal')
for range in config['range_list']:
order=getattr(win32com.client.constants,range['order'])
sht.Sort.SortFields.Add(Key=sht.Range(range['range']),SortOn=xlSortOnValues,Order=order,DataOption= xlSortNormal)
sht.Sort.SetRange(sht.Range(config['full_range']))
sht.Sort.Header = getattr(win32com.client.constants,'xlNo')
sht.Sort.MatchCase = False
sht.Sort.Orientation=getattr(win32com.client.constants,'xlTopToBottom')
sht.Sort.SortMethod=getattr(win32com.client.constants,'xlPinYin')
sht.Sort.Apply()
def _set_cell_value(self, sheet, row, col, value):
'''
set value of one cell by column and row numbers
returns cell name
'''
assert (isinstance(row, int) and isinstance(col, int))
assert isinstance(value, (str,int,float))
assert (row>0 and col>0)
self._get_cell(sheet,row,col).Value = value
return self._rowcol_to_cell(sheet, row, col)
def _set_cell_comment(self, sheet, row, col, value, comment_visible=False):
"""
set comment value of one cell
If comment_visible=True the comment will be permanently visible
"""
assert (isinstance(row, int) and isinstance(col, int))
assert isinstance(value, str)
assert (row>0 and col>0)
self._get_cell(sheet,row,col).AddComment()
self._get_cell(sheet,row,col).Comment.Visible = comment_visible
self._get_cell(sheet,row,col).Comment.Text(Text=value)
def set_row_range(self, sheet, row, first_col,last_col, data):
assert isinstance(data, list)
assert isinstance(sheet, str)
range=self._get_range_object(sheet,row,first_col, row,last_col)
range.Value = data
def _set_range_value(self,sheet,row1, col1,row2, col2,data,type='value'):
'''
Set values of range of cells
row1, col1 - upper left cell
row2, col2 - lower right cell
type - type of content
'value' - simple value
'formula' - formula
'array_formula' - array formula
data - list of lists.
Example:
data=[
['Col1','Col2','Col3'],
[1,5,2],
[2,4,3],
[3,3,4],
[4,2,5]
]
'''
assert isinstance(data, list)
assert type in ['value','formula','array_formula']
range=self._get_range_object(sheet,row1,col1,row2, col2)
if type=='value':
try:
range.Value = data
except:
print "Unable to set range data"
print sheet,row1, col1,row2, col2
print "Data:"
print data
raise
elif type=='formula':
try:
range.Formula = data
except:
print "Unable to set range formula"
print sheet,row1, col1,row2, col2
print "Data:"
print data
raise
elif type=='array_formula':
range.FormulaArray = data
else:
self.e.print_error("Unsupported type %s"%(type))
def _set_cell_formula_array(self, sheet, row, col, value):
"""
set array formula of one cell
Don't put {}! It will be put automatically by the script
row, col - numeric values
value - excel array formula definition.
"""
assert (isinstance(row, int) and isinstance(col, int))
assert isinstance(value, (str,UnicodeType))
assert (row>0 and col>0)
try:
self._get_cell(sheet,row,col).FormulaArray = "=%s"%(value)
except:
self.e.print_error("Unable to set array formula sheet %s row %s column %s value '%s'"%(sheet, row, col, value))
#print sheet, row, col, value
#raise
return self._rowcol_to_cell(sheet, row, col)
def _set_cell_formula(self, sheet, row, col, value):
"""
set formula of one cell
row, col - numeric values
value - excel formula
"""
assert (isinstance(row, int) and isinstance(col, int))
assert isinstance(value, str)
assert (row>0 and col>0)
try:
self._get_cell(sheet,row,col).Formula = "=%s"%(value)
except:
self.e.print_error("Unable to set formula sheet %s row %s column %s value '%s'"%(sheet, row, col, value))
return self._rowcol_to_cell(sheet, row, col)
def set_font_property(self, sheet, row, col,format):
"""
Set properties of font
Example of properties that can be set:.Color,.Bold,.Italic,.Name
Font type example: Name = "Arial"
format variable is dictionary in format {'property_name':'property_value'}
example:format={'Bold':True,'Color':-16776961,'TintAndShade':0}
"""
assert isinstance(format, dict)
assert (row>0 and col>0)
assert (isinstance(row, int) and isinstance(col, int))
assert isinstance(sheet, str)
for property in format.keys():
if isinstance(format[property], str):
value=getattr(win32com.client.constants,format[property])
else:
value=format[property]
setattr(self._get_cell(sheet,row,col).Font,property,value)
#sht.Cells(row, col).Font.Bold=True
def set_font_property_selection(self,format):
"""
Set font property of selected range (for example by using _select_range)
format defined in set_font_property() function
"""
assert isinstance(format, dict)
for property in format.keys():
if isinstance(format[property], str):
value=getattr(win32com.client.constants,format[property])
else:
value=format[property]
setattr(self.xlApp.Selection.Font,property,value)
def set_interior(self, sheet, row, col,format):
"""
background_property
property:.Pattern,.PatternColorIndex,.ThemeColor,.TintAndShade,.PatternTintAndShade
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
"""
assert isinstance(format, dict)
assert (row>0 and col>0)
assert (isinstance(row, int) and isinstance(col, int))
assert isinstance(sheet, str)
for property in format.keys():
if isinstance(format[property], str):
value=getattr(win32com.client.constants,format[property])
else:
value=format[property]
setattr(self._get_cell(sheet,row,col).Interior,property,value)
def set_interior_selection(self, format):
"""
background_property
property:.Pattern,.PatternColorIndex,.ThemeColor,.TintAndShade,.PatternTintAndShade
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorAccent6
.TintAndShade = 0.599993896298105
.PatternTintAndShade = 0
"""
assert isinstance(format, dict)
for property in format.keys():
if isinstance(format[property], str):
value=getattr(win32com.client.constants,format[property])
else:
value=format[property]
setattr(self.xlApp.Selection.Interior,property,value)
def set_cell_properties(self, sheet, row, col,format):
"""
property:.HorizontalAlignment,.VerticalAlignment,.WrapText,.Orientation,.AddIndent,.IndentLevel,.ShrinkToFit,.ReadingOrder, .MergeCells
properties examples:
HorizontalAlignment:xlCenter,xlGeneral,xlRight,xlLeft
VerticalAlignment:xlTop,xlCenter,xlBottom
Expected dictionary structure:
{'property1':'value1','property2':'value2'...}
"""
assert isinstance(format, dict)
assert (row>0 and col>0)
assert (isinstance(row, int) and isinstance(col, int))
assert isinstance(sheet, str)
for property in format.keys():
if isinstance(format[property], str) and hasattr(win32com.client.constants,format[property]):
value=getattr(win32com.client.constants,format[property])
else:
value=format[property]
setattr(self._get_cell(sheet,row,col),property,value)
def set_cell_properties_selection(self, format):
"""
property:.HorizontalAlignment,.VerticalAlignment,.WrapText,.Orientation,.AddIndent,.IndentLevel,.ShrinkToFit,.ReadingOrder, .MergeCells
properties:
HorizontalAlignment:xlCenter,xlGeneral,xlRight,xlLeft
VerticalAlignment:xlTop,xlCenter,xlBottom
Expected dictionaty structure:
{'property':'value'}
"""
assert isinstance(format, dict)
for property in format.keys():
if isinstance(format[property], str):
value=getattr(win32com.client.constants,format[property])
else:
value=format[property]
setattr(self.xlApp.Selection,property,value)
def set_cell_borders(self, sheet, row, col,format_data):
"""
Border types:xlEdgeTop,xlEdgeLeft,xlEdgeBottom,xlEdgeRight,xlDiagonalDown,xlDiagonalUp,xlInsideVertical,xlInsideHorizontal
.LineStyle:xlContinuous,xlNone,xlDouble
.Weight: xlThin,xlMedium,xlThick
.ColorIndex:0-black
Expected dictionaty structure:
{'one_of_border_types':{
'LineStyle':LineStyle
'Weight':Weight
'ColorIndex':ColorIndex
}
}
Example1:
format_data={'xlEdgeBottom':{'LineStyle':'xlContinuous'}}
Example2:
format_data={
'xlEdgeTop':{'LineStyle':'xlContinuous','Weight':'xlThin'},
'xlEdgeBottom':{'LineStyle':'xlContinuous','Weight':'xlThin'},
'xlEdgeLeft':{'LineStyle':'xlContinuous','Weight':'xlThin'},
'xlEdgeRight':{'LineStyle':'xlContinuous','Weight':'xlThin'}
}
"""
assert isinstance(format_data, dict)
assert (row>0 and col>0)
assert (isinstance(row, int) and isinstance(col, int))
assert isinstance(sheet, str)
for edge_type in format_data.keys():
border=self._get_cell(sheet,row,col).Borders(getattr(win32com.client.constants,edge_type))
for attr in format_data[edge_type]:
setattr(border, attr, getattr(win32com.client.constants,format_data[edge_type][attr]))
def set_cell_borders_selection(self, format_data):
"""
Border types:xlEdgeTop,xlEdgeLeft,xlEdgeBottom,xlEdgeRight,xlDiagonalDown,xlDiagonalUp,xlInsideVertical,xlInsideHorizontal
.LineStyle:xlContinuous,xlNone,xlDouble
.Weight: xlThin,xlMedium,xlThick
.ColorIndex:0-black
Expected dictionaty structure:
{'one_of_border_types':{
'LineStyle':LineStyle
'Weight':Weight
'ColorIndex':ColorIndex
}
}
Example1:
format_data={'xlEdgeBottom':{'LineStyle':'xlContinuous'}}
Example2:
borders={
'xlEdgeTop':{'LineStyle':'xlContinuous','Weight':'xlThin'},
'xlEdgeBottom':{'LineStyle':'xlContinuous','Weight':'xlThin'},
'xlEdgeLeft':{'LineStyle':'xlContinuous','Weight':'xlThin'},
'xlEdgeRight':{'LineStyle':'xlContinuous','Weight':'xlThin'}
}
"""
assert isinstance(format_data, dict)
for edge_type in format_data.keys():
border=self.xlApp.Selection.Borders(getattr(win32com.client.constants,edge_type))
for attr in format_data[edge_type]:
setattr(border, attr, getattr(win32com.client.constants,format_data[edge_type][attr]))
def _set_cell_validation(self,sheet, row, col,configuration):
"""
Validation.Add
Type:xlValidateList
AlertStyle:xlValidAlertStop
Operator:xlBetween
Formula1:"1,2,3,4"
"""
assert (row>0 and col>0)
assert (isinstance(row, int) and isinstance(col, int))
assert isinstance(sheet, str)
assert isinstance(configuration,dict)
Type=getattr(win32com.client.constants,configuration['Type'])
AlertStyle=getattr(win32com.client.constants,configuration['AlertStyle'])
Operator=getattr(win32com.client.constants,configuration['Operator'])
validation=self._get_cell(sheet,row,col).Validation
validation.Delete()
try:
#validation.Add(Type=Type,AlertStyle=AlertStyle,Operator=Operator,Formula1=configuration['Formula1'])
validation.Add(Type=Type,AlertStyle=AlertStyle,Operator=Operator,Formula1=configuration['Formula1'])
except Exception, e:
print configuration
#print e
raise
for param in configuration['properties']:
setattr(validation,param,self.get_attr_value(configuration['properties'][param]))
def _set_cell_conditional_formatting(self,sheet, row, col,configuration):
"""
FormatConditions.Add
Operator:xlGreater,xlLess,xlBetween,xlEqual,xlNotEqual,xlNotBetween,xlGreaterEqual,xlLessEqual
Type:xlCellValue
Formula1:formula like "=1"
Formula2: formula like "=1"
configuration - dictionary. Keys:
Operator,Type,Formula1,Formula2,Font,Interior
"""
assert (row>0 and col>0)
assert (isinstance(row, int) and isinstance(col, int))
assert isinstance(sheet, str)
assert isinstance(configuration,dict)
# Range("J9").Select
# Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
# Formula1:="=1"
# Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
# With Selection.FormatConditions(1).Font
# .Color = -16383844
# .TintAndShade = 0
# End With
# With Selection.FormatConditions(1).Interior
# .PatternColorIndex = xlAutomatic
# .Color = 13551615
# .TintAndShade = 0
# End With
# Selection.FormatConditions(1).StopIfTrue = False
cell=self._get_cell(sheet,row,col)
xlType=getattr(win32com.client.constants,configuration['Type'])
xlOperator=getattr(win32com.client.constants,configuration['Operator'])
if 'Formula2' not in configuration:
cell.FormatConditions.Add(Type=xlType,Operator=xlOperator, Formula1=configuration['Formula1'])
else:
cell.FormatConditions.Add(Type=xlType,Operator=xlOperator, Formula1=configuration['Formula1'],Formula2=configuration['Formula2'])
cell.FormatConditions(cell.FormatConditions.Count).SetFirstPriority()
FormatConditions=cell.FormatConditions(1)
font=FormatConditions.Font
for param in configuration['Font'].keys():
setattr(font,param,self.get_attr_value(configuration['Font'][param]))
Interior=FormatConditions.Interior
for param in configuration['Interior'].keys():
setattr(Interior,param,self.get_attr_value(configuration['Interior'][param]))
FormatConditions.StopIfTrue = False
#**************************** Non Excel functions #****************************
def get_attr_value(self,param):
if isinstance(param, str) and hasattr(win32com.client.constants,param):
value=getattr(win32com.client.constants,param)
else:
value=param
return value
def fixStrings(self, aMatrix):
# converts all unicode strings and times
newmatrix = []
for row in aMatrix:
newrow = []
for cell in row:
if type(cell) is UnicodeType:
newrow.append(str(cell))
else:
newrow.append(cell)
newmatrix.append(newrow)
return newmatrix
def numpyfixStrings(self,item):
if type(item) is UnicodeType:
return str(item)
else:
return item
def main():
#Usage example
e=excel_com()
e.connect()
e.add_workbook()
sheet_name="Sheet1"
e._insert_file(sheet_name,r"C:\Users\stepanov\Documents\projects\R31\LDRV\test\core\calibration.txt")
e.gridlines_off(sheet_name)
sheet_name="Sheet2"
e._activate_sheet(sheet_name)
chrt=e._add_chart_ws(sheet_name)
e.set_chart_type(chrt,'xlXYScatterSmoothNoMarkers')
e._select_range(sheet_name,1,1,1,3)
e._merge_selection()
e._set_cell_value(sheet_name,1,1,"Test table")
data=[
['Col1','Col2','Col3'],
[1,5,2],
[2,4,3],
[3,3,4],
[4,2,5]
]
e._set_range_value(sheet_name,2, 1,6, 3,data,'value')
e._set_cell_value(sheet_name,1,6,"Test table")
e._set_range_value(sheet_name,row1=2, col1=6,row2=6, col2=8,data=data,type='value')
data=[]
data.append({'Name':"aaa",'Values':'Sheet2!$B$3:$B$6','XValues':"Sheet2!$A$3:$A$6"})
data.append({'Name':"bbb",'Values':'Sheet2!$C$3:$C$6','XValues':"Sheet2!$A$3:$A$6"})
e.set_chart_data(chrt,data)
e._add_chart_text(chrt,'test')
sort_config={
'full_range':'F2:H6',
'range_list':[{'range':'G2:G6','order':'xlAscending'},{'range':'H2:H6','order':'xlAscending'}]
}
e._set_sort(sheet_name,sort_config)
if __name__=="__main__":
main()
exit(0)
