class format(object):
def __init__(self):
self.conditiomal_formatting_operators_list_xl=['xlGreater','xlLess','xlBetween','xlEqual','xlNotEqual','xlNotBetween','xlGreaterEqual','xlLessEqual']
self.conditiomal_formatting_conversion_dict={
'<':'xlLess',
'>':'xlGreater',
'<=':'xlLessEqual',
'>=':'xlGreaterEqual',
'=':'xlEqual',
'<>':'xlNotEqual',
'between':'xlBetween',
'not between':'xlNotBetween'
}
def get_conditiomal_formatting_operators_list_xl(self):
return self.conditiomal_formatting_operators_list_xl
def get_conditiomal_formatting_operators_list(self):
return self.conditiomal_formatting_conversion_dict.keys()
def bold(self):
return {"Bold":True}
def text_center(self):
return {"HorizontalAlignment":"xlCenter","VerticalAlignment":"xlCenter"}
def cell_borders(self):
result={
'xlEdgeTop':{'LineStyle':'xlContinuous','Weight':'xlThin'},
'xlEdgeBottom':{'LineStyle':'xlContinuous','Weight':'xlThin'},
'xlEdgeLeft':{'LineStyle':'xlContinuous','Weight':'xlThin'},
'xlEdgeRight':{'LineStyle':'xlContinuous','Weight':'xlThin'}
}
return result
def fill_type1(self):
result={
"Pattern":"xlSolid",
"PatternColorIndex":"xlAutomatic",
"ThemeColor":"xlThemeColorDark1",
"TintAndShade":-0.249977111117893,
"PatternTintAndShade":0,
}
return result
def add_number_format(self,format,number_format):
if 'properties' in format:
format['properties']['NumberFormat']=number_format
else:
format['properties']={}
format['properties']['NumberFormat']=number_format
return format
def convert_conditional_formatting_to_xl(self,format):
assert format in self.get_conditiomal_formatting_operators_list()
return self.conditiomal_formatting_conversion_dict[format]
#---------------------------------------------------
def default_list_validation(self,list,formula=False):
"""
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
"""
result={}
result['Type']='xlValidateList'
result['AlertStyle']='xlValidAlertStop'
result['Operator']='xlBetween'
if not formula:
result['Formula1']=','.join(str(item) for item in list)
else:
result['Formula1']="=%s"%(list)
result['properties']={}
result['properties']['IgnoreBlank']=True
result['properties']['InCellDropdown']=True
result['properties']['InputTitle']=''
result['properties']['ErrorTitle']=''
result['properties']['InputMessage']=''
result['properties']['ErrorMessage']=''
result['properties']['ShowInput']=True
result['properties']['ShowError']=False
return result
def default_conditional_formatting(self,operator,formula1,formula2=None):
assert operator in self.conditiomal_formatting_operators_list_xl
assert isinstance(formula1, str)
result={}
result['Type']='xlCellValue'
result['Formula1']="=%s"%(formula1)
if formula2!=None:
assert isinstance(formula2, str)
result['Formula2']="=%s"%(formula2)
result['Operator']=operator
result['Font']={'Color': -16383844,'TintAndShade':0}
result['Interior']={'PatternColorIndex':'xlAutomatic','Color':13551615,'TintAndShade':0}
return result
def header_allign_left_cell(self):
result={}
result['font']=self.bold()
result['interior']=self.fill_type1()
result['borders']=self.cell_borders()
#result['properties']=self.text_center()
return result
def header_allign_center_cell(self):
result={}
result['font']=self.bold()
result['interior']=self.fill_type1()
result['borders']=self.cell_borders()
result['properties']=self.text_center()
return result
def allign_center_cell(self):
result={}
result['borders']=self.cell_borders()
result['properties']=self.text_center()
return result
