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)