from excel_com import excel_com
from class_excel_format import format
from error_class import myerror
import time
#Example of usage for excel_class
def main():
e=myerror()
x=excel_com(e)
f=format()#Formatting class
x.connect()#Connect to Excel program
x.add_workbook()#Add workbook
x.delete_sheet("Sheet3")# Delete sheet by name
x._set_cell_value(sheet="Sheet1", row=1, col=1, value='Hello!') #Write value of single cell
data=[
['Col1','Col2','Col3'],
[1,5,2],
[2,4,3],
[3,3,4],
[4,2,5]
]
#Set value of range of cells using data from list
x._set_range_value(sheet="Sheet1",row1=2, col1=1,row2=6, col2=3,data=data,type='value')
#Set borders for single cell
#Create borders format definition dictionary
borders={
'xlEdgeTop':{'LineStyle':'xlContinuous','Weight':'xlThin'},
'xlEdgeBottom':{'LineStyle':'xlContinuous','Weight':'xlThin'},
'xlEdgeLeft':{'LineStyle':'xlContinuous','Weight':'xlThin'},
'xlEdgeRight':{'LineStyle':'xlContinuous','Weight':'xlThin'}
}
x.set_cell_borders(sheet="Sheet1", row=1, col=1,format_data=borders)#Set borders for single cell
#Set borders for selection
x._select_range(sheet="Sheet1",row1=2, col1=1,row2=6, col2=3)#Select range of cells
x.set_cell_borders_selection(borders)#Set bordets for selected cells
#Set fill colour of single cell
fill_defenition={
"Pattern":"xlSolid",
"PatternColorIndex":"xlAutomatic",
"ThemeColor":"xlThemeColorDark1",
"TintAndShade":-0.249977111117893,
"PatternTintAndShade":0
}
x.set_interior( sheet="Sheet1", row=1, col=1,format=fill_defenition)
#Set fill colour of selection
x._select_range(sheet="Sheet1",row1=2, col1=1,row2=2, col2=3)#Select range of cells first
x.set_interior_selection(fill_defenition)
#Set text alignment for single cell
cell_format={'HorizontalAlignment':'xlCenter'}
x.set_cell_properties(sheet="Sheet1", row=1, col=1,format=cell_format)
#Set text alignment for selection
x._select_range(sheet="Sheet1",row1=2, col1=1,row2=2, col2=3)#Select range of cells first
x.set_cell_properties_selection(cell_format)
#Set font property
font_format={'Bold':True,'Color':-16776961,'TintAndShade':0} #Define bold red
x.set_font_property(sheet="Sheet1", row=1, col=1,format=font_format) #Set property
#Set font property of range
x._select_range(sheet="Sheet1",row1=2, col1=1,row2=2, col2=3)#Select range of cells first
font_format={'Bold':True} #Define bold
x.set_font_property_selection(font_format)
#Set cell formula
cell_address=x._rowcol_to_cell(sheet="Sheet1",row=3,col=1)#Returns Excel notation cell address defined by row and column numbers
x._set_cell_formula(sheet="Sheet1", row=3, col=5, value=cell_address) #Write formula of single cell
#Set cell array formula. Don't use {}
x._set_cell_formula_array(sheet="Sheet1", row=4, col=5, value='SUM(IF(A3:A6>2,A3:A6,0))') #Write array formula to single cell
#Set cell comment
x._set_cell_comment(sheet="Sheet1", row=1, col=1,value="First cell in sheet")#Set hidden comment
x._set_cell_comment(sheet="Sheet1", row=1, col=5,value="Visible comment",comment_visible=True)#Set visible comment
#General copy-paste
x._copy_range(sheet="Sheet1",row1=2, col1=1,row2=6, col2=3)#Copy range of cells to clipboard
x._past_range(sheet="Sheet1",row=2+6, col=1)#Past data from clipboard to range selected by upper left corner location
#Copy-paste values only of range of cells
x._copy_range(sheet="Sheet1",row1=2, col1=1,row2=6, col2=3)#Copy range of cells to clipboard
x._past_values_range(sheet="Sheet1",row1=2+12, col1=1,row2=6+12, col2=3)#Past values to range
#Hide Excel window
print "Hiding Excel"
x.hide_app()
time.sleep(2)
#Show Excel window
print "Showing Excel"
x.show_app()
#Activate sheet
#Save workbook to file
filename="C:\\tmp\\tmp.xlsx"
x.save(filename,True)
#Close workbook
print "Closing workbook"
x.close_workbook()
time.sleep(2)
#Open workbook
print "Opening workbook"
x.open_workbook(filename)
if __name__=="__main__":
main()
exit(0)
