def test_data_validation(out, doc, worksheet):
from ..worksheet import write_worksheet_datavalidations
from openpyxl.datavalidation import DataValidation, ValidationType
ws = worksheet
dv = DataValidation(ValidationType.LIST, formula1='"Dog,Cat,Fish"')
dv.add_cell(ws['A1'])
ws.add_data_validation(dv)
write_worksheet_datavalidations(doc, worksheet)
xml = out.getvalue()
expected = """
<dataValidations count="1">
<dataValidation allowBlank="0" showErrorMessage="1" showInputMessage="1" sqref="A1" type="list">
<formula1>"Dog,Cat,Fish"</formula1>
<formula2>None</formula2>
</dataValidation>
</dataValidations>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
####WRITE REGIONAL SCENARIOS SHEETS####
#Write Column Headers for Regional Scenarios Sheet
print 'Writing header row for Settings worksheet'
dv = DataValidation(validation_type="list", formula1='"Annual,Summer,Winter,January,February,March,April,May,June,July,August,September,October,November,December"', allow_blank=True)
sheet_settings.add_data_validation(dv)
sheet_settings.cell(row = 0, column = 0).value = 'Parameter'
sheet_settings.cell(row = 0, column = 1).value = 'Value'
sheet_settings.cell(row = 1, column = 0).value = 'Date'
sheet_settings.cell(row = 1, column = 1).value = datetime.now().strftime('%x %X')
sheet_settings.cell(row = 2, column = 0).value = 'Season/Month'
sheet_settings.cell(row = 2, column = 1).value = season
dv.add_cell(sheet_settings.cell(row = 2, column = 1))
sheet_settings.cell(row = 3, column = 0).value = 'SB375 Run'
sheet_settings.cell(row = 3, column = 1).value = sb375
sheet_settings.cell(row = 5, column = 1).value = 'You can edit this'
sheet_settings.cell(row = 6, column = 1).value = 'You can not edit this'
#Write Column Headers for Scenario Daily VMT by Veh Tech Sheet
print 'Writing header row for Daily_VMT_By_Veh_Tech worksheet'
sheet_scen_vmt.cell(row = 0, column = 0).value = 'MPO'
sheet_scen_vmt.cell(row = 0, column = 1).value = 'GAI'
sheet_scen_vmt.cell(row = 0, column = 2).value = 'Sub-Area'
sheet_scen_vmt.cell(row = 0, column = 3).value = 'Cal_Year'
sheet_scen_vmt.cell(row = 0, column = 4).value = 'Veh_Tech'
sheet_scen_vmt.cell(row = 0, column = 5).value = 'New Total VMT'
def initial_operations_template(building):
wb = Workbook()
ws = wb.worksheets[0]
ws.title = u'Solduri inițiale'
ws.cell(row=0, column=0).value = 'ID intern'
ws.cell(row=0, column=1).value = 'Apartament'
ws.cell(row=0, column=2).value = 'Sold'
ws.cell(row=0, column=3).value = 'Data'
def attr_validator(attr):
l = (str(getattr(ap, attr)) for ap in building.apartments())
formula = '"' + ','.join(l) + '"'
validator = DataValidation(ValidationType.LIST, formula1=formula,
allow_blank=False)
return validator
id_validator = attr_validator('id')
ws.add_data_validation(id_validator)
name_validator = attr_validator('name')
ws.add_data_validation(name_validator)
decimal_validator = DataValidation(ValidationType.DECIMAL, allow_blank=True)
decimal_validator.set_error_message('Introduceți un număr zecimal', 'Eroare de validare')
ws.add_data_validation(decimal_validator)
date_validator = DataValidation(ValidationType.DATE, allow_blank=True)
date_validator.set_error_message('Introduceți o dată', 'Eroare de validare')
ws.add_data_validation(date_validator)
row = 1
now = datetime.now()
for ap in building.apartments():
cell_id = ws.cell(row=row, column=0)
cell_id.value = ap.id
id_validator.add_cell(cell_id)
cell_name = ws.cell(row=row, column=1)
cell_name.value = ap.name
name_validator.add_cell(cell_name)
cell_balance = ws.cell(row=row, column=2)
cell_balance.style.fill.fill_type = Fill.FILL_SOLID
cell_balance.style.fill.start_color.index = Color.YELLOW
decimal_validator.add_cell(cell_balance)
cell_date = ws.cell(row=row, column=3)
cell_date.value = now
cell_date.style.number_format.format_code = 'yyyy-mm-dd'
cell_date.style.fill.fill_type = Fill.FILL_SOLID
cell_date.style.fill.start_color.index = Color.YELLOW
date_validator.add_cell(cell_date)
row += 1
ws.column_dimensions['A'].visible = False
ws.column_dimensions['B'].width = 20.0
ws.column_dimensions['C'].width = 20.0
ws.column_dimensions['D'].width = 20.0
temp = tempfile.NamedTemporaryFile()
wb.save(temp)
return temp
