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