def add_data_validation(self, worksheet, field, row, col): if "enum" in field: dv = DataValidation(type="list", formula1='"%s"' % ",".join(field["enum"]), allow_blank=True, errorStyle='warning') dv.error = "Your entry is not in the list, Change anyway?" dv.errorTitle = "Invalid Entry" dv.prompt = "Please select from the list" dv.promptTitle = "List Selection" elif "type" not in field: return elif field["type"] == "boolean": dv = DataValidation(type="list", formula1='"true,false"', allow_blank=True, errorStyle='warning') dv.error = "Your entry is not true or false, change anyway?" dv.errorTitle = "Invalid Entry" dv.prompt = "Please select true or false" dv.promptTitle = "True or False Selection" elif field["type"] == "integer": dv = DataValidation(type="whole", allow_blank=True, errorStyle="warning") dv.error = "Your entry is not an integer, change anyway?" dv.errorTitle = "Invalid Entry" dv.prompt = "Please provide integer" dv.promptTitle = "Integer Selection" else: return worksheet.add_data_validation(dv) c = worksheet.cell(row=row, column=col) dv.add(c)
def _fill_customer_row(ws, row_idx, customer): action_validation = DataValidation( type='list', formula1='"-,create,update"', allow_blank=False, ) action_validation.error = str('Action must be from list') action_validation.errorTitle = str('Invalid action') action_validation.prompt = str('Please choose action from list') action_validation.promptTitle = str('List of choices') search_criteria_validation = DataValidation( type='list', formula1='"-,id,external_id,external_uid"', allow_blank=False, ) search_criteria_validation.error = str('Search criteria must be one from list') search_criteria_validation.errorTitle = str('Invalid search criteria') search_criteria_validation.prompt = str('Please choose search criteria from list') search_criteria_validation.promptTitle = str('List of choices') ws.add_data_validation(action_validation) ws.add_data_validation(search_criteria_validation) ws.cell(row_idx, 1, value=customer.get('id', '-')) ws.cell(row_idx, 2, value=customer.get('external_id', '-')) ws.cell(row_idx, 3, value=customer.get('external_uid', '-')) ws.cell(row_idx, 4, value='-') ws.cell(row_idx, 5, value=customer['hub'].get('id', '-') if 'hub' in customer else '-') ws.cell(row_idx, 6, value='id' if 'parent' in customer else '-') ws.cell(row_idx, 7, value=customer['parent'].get('id', '-') if 'parent' in customer else '-') ws.cell(row_idx, 8, value=customer.get('type', '-')) ws.cell(row_idx, 9, value=customer.get('tax_id', '-')) ws.cell(row_idx, 10, value=customer.get('name', '-')) ws.cell(row_idx, 11, value=customer['contact_info'].get('address_line1', '-')) ws.cell(row_idx, 12, value=customer['contact_info'].get('address_line2', '-')) ws.cell(row_idx, 13, value=customer['contact_info'].get('city', '-')) ws.cell(row_idx, 14, value=customer['contact_info'].get('state', '-')) ws.cell(row_idx, 15, value=customer['contact_info'].get('zip', '-')) ws.cell(row_idx, 16, value=customer['contact_info'].get('country', '-')) ws.cell(row_idx, 17, value=customer['contact_info']['contact'].get('first_name', '-')) ws.cell(row_idx, 18, value=customer['contact_info']['contact'].get('last_name', '-')) ws.cell(row_idx, 19, value=customer['contact_info']['contact'].get('email', '-')) ws.cell( row_idx, 20, value=_get_phone_number( customer['contact_info']['contact'].get( 'phone_number', '-', ), ), ) action_validation.add(f'D{row_idx}') search_criteria_validation.add(f'F{row_idx}')
def _create_tref_dv(_tref, allow_blank=True): # Stringify values and create data-validation object dv = DataValidation(type="list", formula1=_tref, allow_blank=allow_blank) # Optionally set a custom error message dv.error = 'Entry not in the list' dv.errorTitle = 'Invalid Entry' return dv
def _create_bool_dv(allow_blank=True): # Stringify values and create data-validation object _values = '"{}"'.format(','.join(['TRUE', 'FALSE'])) dv = DataValidation(type="list", formula1=_values, allow_blank=allow_blank) # Optionally set a custom error message dv.error = 'Entry not in the list' dv.errorTitle = 'Invalid Entry' return dv
def validation_from_list(validation_range): '''Creates a data validation object based on validation_range (cell range to point to). Requires import of DataValidation from openpyxl.worksheet.datavalidation ''' from openpyxl.worksheet.datavalidation import DataValidation validation = DataValidation(type='list', formula1=validation_range) validation.error = 'Your entry is not in the list' validation.errorTitle = 'Invalid Entry' validation.prompt = 'Please select from the list' validation.promptTitle = 'List Selection' return validation
def __create_data_list_for_justification(is_fac): """ Création de la liste de choix pour la justification :return: """ dv = DataValidation(type="list", formula1='"%s"' % ExamEnrollment.justification_label_authorized(is_fac), allow_blank=True) dv.error = str(_('Invalid entry, not in the list of choices')) dv.errorTitle = str(_('Invalid entry')) dv.prompt = str(_('Please choose in the list')) dv.promptTitle = str(_('List of choices')) return dv
def generate_spreadsheet(self, validated_params, template_name): spreadsheet_info = {} output_dir = os.path.join( self.scratch_dir, 'sample_spreadsheet_' + str(int(time.time() * 10000))) output_filename = validated_params['output_name'] + '.xlsx' output_filepath = os.path.join(output_dir, output_filename) os.makedirs(output_dir) _column = _COLUMNS[template_name] common_font = Font(name='Arial', size=9) highlight_font = Font(name='Arial', size=9, bold=True) header_fill = PatternFill("solid", fgColor="D1E5FE") wb = Workbook() ws = wb.active ws.title = "samples" ws.page_setup.fitToWidth = 1 ws['A1'] = 'Object Type:' ws['A1'].font = highlight_font ws['B1'] = validated_params['object_type'] ws['B1'].font = common_font ws['C1'] = 'User Code:' ws['C1'].font = highlight_font ws['D1'] = validated_params['user_code'] ws['D1'].font = common_font for c in range(1, len(_column) + 1): _col = _column[c - 1] cell = ws.cell(row=2, column=c, value=_col['header']) cell.fill = header_fill cell.font = highlight_font if 'type' in _col and _col['type']: cl = get_column_letter(c) dv = DataValidation(type=_col['type'], allow_blank=True) dv.error = 'Your entry is not ' + _col['type'] dv.errorTitle = 'Invalid Entry' ws.add_data_validation(dv) dv.add(cl + '3:' + cl + '1048576') wb.save(filename=output_filepath) spreadsheet_info['output_dir'] = output_dir spreadsheet_info['output_file'] = [{ 'path': output_filepath, 'name': output_filename, 'label': output_filename, 'description': 'Sample spreadsheet' }] return spreadsheet_info
def _create_enum_dv(values, allow_blank=True): # Remove 'null' values from JSON schema values = [x for x in values if x is not None] # Stringify values and create data-validation object _values = '"{}"'.format(','.join(values)) dv = DataValidation(type="list", formula1=_values, allow_blank=allow_blank) # Optionally set a custom error message dv.error = 'Entry not in the list' dv.errorTitle = 'Invalid Entry' return dv
def dataval_list(ws, enum, range): # Create a data-validation object with list validation dvl = DataValidation(type="list", formula1=enum, allow_blank=True) # Optionally set a custom error message dvl.error = 'Your entry is not in the list' dvl.errorTitle = 'Invalid Entry' # Optionally set a custom prompt message dvl.prompt = 'Please select from the list' dvl.promptTitle = 'List Selection' # Add the data-validation object to the worksheet ws.add_data_validation(dvl)
def applyValidationToCell(self, ws, startingRow, nokiaAttr): #get Validation rule for this cell validationRule = self.validationRules[nokiaAttr] if "list:" in validationRule: validationString = self.getValidationAsString(validationRule) str = '"' + validationString + '"' dv = DataValidation(type="list", formula1=str, allow_blank=False, showDropDown=False) dv.error = 'Your entry is not as per the validation rule' dv.errorTitle = 'Invalid Entry' ws.add_data_validation(dv) cell = ws.cell(column=self.cols[nokiaAttr] + 1, row=startingRow) dv.add(cell)
def dataval_list(ws, enum, range): # Create a data-validation object with list validation dvl = DataValidation(type="list", formula1=enum, allow_blank=True) # Optionally set a custom error message dvl.error ='Your entry is not in the list' dvl.errorTitle = 'Invalid Entry' # Optionally set a custom prompt message dvl.prompt = 'Please select from the list' dvl.promptTitle = 'List Selection' # Add the data-validation object to the worksheet ws.add_data_validation(dvl) # Or, apply the validation to a range of cells dvl.ranges.append(range)
def exportar_planilha_importacao_usuarios_perfil_codae(request, **kwargs): response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = 'attachment; filename=planilha_importacao_usuarios_perfil_CODAE.xlsx' workbook: Workbook = Workbook() ws = workbook.active ws.title = 'CODAE' headers = [ 'Nome do Usuário', 'Cargo', 'Email', 'CPF', 'Telefone', 'RF', 'Perfil', 'Número CRN' ] _font = styles.Font(name='Calibri', sz=10) {k: setattr(styles.DEFAULT_FONT, k, v) for k, v in _font.__dict__.items()} for i in range(0, len(headers)): cabecalho = ws.cell(row=1, column=1 + i, value=headers[i]) cabecalho.fill = styles.PatternFill('solid', fgColor='ffff99') cabecalho.font = styles.Font(name='Calibri', size=10, bold=True) cabecalho.border = styles.Border( left=styles.Side(border_style='thin', color='000000'), right=styles.Side(border_style='thin', color='000000'), top=styles.Side(border_style='thin', color='000000'), bottom=styles.Side(border_style='thin', color='000000') ) dv = DataValidation( type='list', formula1='"COORDENADOR_GESTAO_ALIMENTACAO_TERCEIRIZADA,' 'COORDENADOR_DIETA_ESPECIAL,' 'COORDENADOR_SUPERVISAO_NUTRICAO,' 'COORDENADOR_GESTAO_PRODUTO"', allow_blank=True ) dv.error = 'Perfil Inválido' dv.errorTitle = 'Perfil não permitido' ws.add_data_validation(dv) dv.add('G2:G1048576') workbook.save(response) return response
def add_scenario_selector(sheet, column, row, selections): """ add_scenario_selector() -> None --``filename`` is the xlsx file (Chef chopped) to work on --``sources_dict`` is a dictionary containing the Excel sheet names and selector cell coordinates to link Method adds VB code to a completed Chef workbook to link scenario selection cells together to allow universal scenario change from any sheet in the workbook. """ select_column = column + 2 options = ','.join(selections) dv = DataValidation(type="list", formula1='"%s"' % options, allow_blank=False) # Optionally set a custom error message dv.error = 'Your entry is not in the list' dv.errorTitle = 'Invalid Entry' # Optionally set a custom prompt message dv.prompt = 'Please select from the list' dv.promptTitle = 'List Selection' sheet.add_data_validation(dv) selector_cell = sheet.cell(column=select_column, row=row) selector_cell.value = "Base" dv.add(selector_cell) sheet.bb.scenario_selector = selector_cell.coordinate # Make label cells and drop-down selector CellStyles.format_scenario_selector_cells(sheet, column, select_column, row)
def exportar_planilha_importacao_usuarios_perfil_escola(request, **kwargs): response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') response['Content-Disposition'] = 'attachment; filename=planilha_importacao_usuarios_perfil_ESCOLA.xlsx' workbook: Workbook = Workbook() ws = workbook.active ws.title = 'ESCOLA' headers = [ 'Cód. EOL da U.E', 'Nome do Usuário', 'Cargo', 'Email', 'CPF', 'Telefone', 'RF', 'Perfil', ] _font = styles.Font(name='Calibri', sz=10) {k: setattr(styles.DEFAULT_FONT, k, v) for k, v in _font.__dict__.items()} for i in range(0, len(headers)): cabecalho = ws.cell(row=1, column=1 + i, value=headers[i]) cabecalho.fill = styles.PatternFill('solid', fgColor='ffff99') cabecalho.font = styles.Font(name='Calibri', size=10, bold=True) cabecalho.border = styles.Border( left=styles.Side(border_style='thin', color='000000'), right=styles.Side(border_style='thin', color='000000'), top=styles.Side(border_style='thin', color='000000'), bottom=styles.Side(border_style='thin', color='000000') ) dv = DataValidation( type='list', formula1='"DIRETOR, DIRETOR CEI"', allow_blank=True ) dv.error = 'Perfil Inválido' dv.errorTitle = 'Perfil não permitido' ws.add_data_validation(dv) dv.add('H2:H1048576') workbook.save(response) return response
def active(name): # Create the workbook and worksheet we'll be working with wb = openpyxl.load_workbook(name) ws = wb.active ws['C1'] = '数据反馈' ws['D1'] = '数据反馈2' # Create a data-validation object with list validation dv = DataValidation(type="list", formula1='"不需要,暂时不需要,未接,设置,需要,关机,停机,空号"', allow_blank=True) # Optionally set a custom error message dv.error = '内容填写错误' dv.errorTitle = 'Invalid Entry' # Optionally set a custom prompt message # dv.prompt = 'Please select from the list' # dv.promptTitle = '请筛选' # Add the data-validation object to the worksheet ws.add_data_validation(dv) dv.add('C2:C5000') wb.save(name)
def create_sample_sheet(project_uuid): """Create sample sheet with 'initial data' for project""" wb = load_workbook( os.path.join(os.path.dirname(__file__), 'static/portal/excel/mng_excel_template.xlsx')) ws = wb['Data'] project = limsfm_get_project(project_uuid) ws['F1'] = project['reference'] data_rows = ws.rows[6:] for i, pl in enumerate(project['projectlines']): row = data_rows[i] row[1].value = pl['sample_ref'] if pl['customers_ref']: row[2].value = pl['customers_ref'] if pl['dna_concentration_ng_ul']: row[3].value = float(pl['dna_concentration_ng_ul']) if pl['volume_ul']: row[4].value = float(pl['volume_ul']) row[5].value = pl['taxon_id'] if pl['collection_day']: row[6].value = int(pl['collection_day']) if pl['collection_month']: row[7].value = int(pl['collection_month']) if pl['collection_year']: row[8].value = int(pl['collection_year']) row[9].value = pl['geo_country_name'] row[10].value = pl['geo_specific_location'] if pl['study_type'] == "Lab": row[11].value = pl['lab_experiment_type'] row[12].value = pl['further_details'] elif pl['study_type'] == "Host": row[13].value = pl['host_taxon_id'] row[14].value = pl['host_sample_type'] row[15].value = pl['further_details'] elif pl['study_type'] == "Environmental": row[16].value = pl['environmental_sample_type'] row[17].value = pl['further_details'] # Country list validation country_validator = DataValidation(type='list', formula1='=Lookups!$B$1:$B$267', allow_blank=True) country_validator.error = "Please select a country from the list" country_validator.errorTitle = "Invalid country" ws.add_data_validation(country_validator) country_validator.ranges.append('J7:J1000') # Environmental sample type list validation est_validator = DataValidation(type='list', formula1='=Lookups!$C$1:$C$31', allow_blank=True) est_validator.error = ("Please select a environmental sample type from " "the list") est_validator.errorTitle = "Invalid environmental sample type" ws.add_data_validation(est_validator) est_validator.ranges.append('Q7:Q1000') # Lab experiment type list validation lab_validator = DataValidation(type='list', formula1='=Lookups!$E$1:$E$5', allow_blank=True) lab_validator.error = "Please select a lab experiment type from the list" lab_validator.errorTitle = "Invalid lab experiment type" ws.add_data_validation(lab_validator) lab_validator.ranges.append('L7:L1000') # Host sample type list validation hst_validator = DataValidation(type='list', formula1='=Lookups!$D$1:$D$67', allow_blank=True) hst_validator.error = "Please select a host sample type from the list" hst_validator.errorTitle = "Invalid host sample type" ws.add_data_validation(hst_validator) hst_validator.ranges.append('O7:O1000') return wb
df_mapping = pd.read_excel(directory+plo_clo_filename, sheet_name='Program_course_mapping', converters={'course_id': str}) #print(tabulate(df_plo[:20], headers='keys')) #print(tabulate(df_clo[:20], headers='keys')) #print(tabulate(df_mapping[:20], headers='keys')) # get program list df_programs = df_mapping[['program_code', 'plan_code', 'program_name']].drop_duplicates() # Matching column data validation dv_match = DataValidation(type="list", formula1='"0,1"', allow_blank=True) # Optionally set a custom error message dv_match.error ='Your entry is not in the list' dv_match.errorTitle = 'Invalid Entry' # Optionally set a custom prompt message dv_match.prompt = 'Please select from the list' dv_match.promptTitle = 'List Selection' # General column data validation dv_general = DataValidation(type="list", formula1='selections!$A$2:$A$7', allow_blank=True) # Optionally set a custom error message dv_general.error ='You have entered free text' dv_general.errorTitle = 'Free Text' dv_general.errorStyle = 'warning' # Active Verb column data validation dv_active = DataValidation(type="list", formula1='selections!$B$2:$B$6', allow_blank=True)
# ask user to add another trainee another = raw_input("Do you need to add another trainee? ") if another == 'yes' or another == 'y': continue else: break else: print 'OK. I just wanted to make sure.' break # Data validation for cells that do not have classes assigned dv = DataValidation(type='list', formula1='"Test1,Test2,Test3"', allow_blank=True) # Data validation error messages dv.error = 'Your entry is not in the list.' dv.errorTitle = 'Invalid Entry' # Data validation prompt messages dv.prompt = 'Please select from the list.' dv.promptTitle = 'List Selection' # Add data validation object to roster worksheet sheet1.add_data_validation(dv) # Apply validation to the range of cells requiring them # Need cells I3 to the last row in the I column maxrow = sheet1.max_row maxvalidationcell = sheet1.cell(row=maxrow, column=9) maxvalidationcell = sheet1[maxvalidationcell.coordinate] dv.ranges.append('I3:maxvalidationcell')
def generate_xlsx_export_template(data, mtef=False): if mtef: current_year = datetime.datetime.utcnow().date().year mtef_cols = [ u"FY{}/{} (MTEF)".format(str(year)[2:4], str(year + 1)[2:4]) for year in range(current_year, current_year + 3) ] _headers = [u"ID", u"Project code", u"Activity Title"] _headers += mtef_cols _headers += [ u'Activity Status', u'Activity Dates (Start Date)', u'Activity Dates (End Date)', u"County" ] else: mtef_cols = [] _headers = [ u"ID", u"Project code", u"Activity Title", util.previous_fy_fq(), u'Activity Status', u'Activity Dates (Start Date)', u'Activity Dates (End Date)', u"County", ] writer = xlsxDictWriter(_headers) cl_lookups = get_codelists_lookups() myFill = PatternFill(start_color='FFFF00', end_color='FFFF00', fill_type='solid') statuses = get_codelists_lookups_by_name()["ActivityStatus"].keys() # Activity Status validation v_status = DataValidation(type="list", formula1='"{}"'.format(u",".join(statuses)), allow_blank=False) v_status.error = 'Your entry is not in the list' v_status.errorTitle = 'Activity Status' v_status.prompt = 'Please select from the list' v_status.promptTitle = 'Activity Status' v_id = DataValidation(type="whole") v_id.errorTitle = "Invalid ID" v_id.error = "Please enter a valid ID" v_id.promptTitle = 'Liberia Project Dashboard ID' v_id.prompt = 'Please do not edit this ID. It is used by the Liberia Project Dashboard to uniquely identify activities.' v_date = DataValidation(type="date") v_date.errorTitle = "Invalid date" v_date.error = "Please enter a valid date" v_number = DataValidation(type="decimal") v_number.errorTitle = "Invalid number" v_number.error = "Please enter a valid number" for org_code, activities in sorted(data.items()): writer.writesheet(org_code) writer.ws.add_data_validation(v_status) writer.ws.add_data_validation(v_date) writer.ws.add_data_validation(v_number) writer.ws.add_data_validation(v_id) #writer.ws.protection.sheet = True for activity in activities: existing_activity = activity_to_json(activity, cl_lookups) for mtef_year in mtef_cols: fy_start, fy_end = re.match("FY(\d*)/(\d*) \(MTEF\)", mtef_year).groups() existing_activity[mtef_year] = sum([ float( existing_activity["20{} Q1 (MTEF)".format(fy_start)]), float( existing_activity["20{} Q2 (MTEF)".format(fy_start)]), float( existing_activity["20{} Q3 (MTEF)".format(fy_start)]), float(existing_activity["20{} Q4 (MTEF)".format(fy_start)]) ]) writer.writerow(existing_activity) if mtef == True: for rownum in range(1 + 1, len(activities) + 2): writer.ws.cell(row=rownum, column=4).fill = myFill writer.ws.cell(row=rownum, column=5).fill = myFill writer.ws.cell(row=rownum, column=6).fill = myFill writer.ws.cell(row=rownum, column=4).number_format = u'"USD "#,##0.00' writer.ws.cell(row=rownum, column=5).number_format = u'"USD "#,##0.00' writer.ws.cell(row=rownum, column=6).number_format = u'"USD "#,##0.00' writer.ws.column_dimensions[u"C"].width = 70 writer.ws.column_dimensions[u"D"].width = 15 writer.ws.column_dimensions[u"E"].width = 15 writer.ws.column_dimensions[u"F"].width = 15 writer.ws.column_dimensions[u"G"].width = 15 writer.ws.column_dimensions[u"H"].width = 20 writer.ws.column_dimensions[u"I"].width = 20 v_id.add('A2:A{}'.format(len(activities) + 2)) v_number.add('D2:F{}'.format(len(activities) + 2)) v_status.add('G2:G{}'.format(len(activities) + 2)) v_date.add('H2:I{}'.format(len(activities) + 2)) elif mtef == False: for rownum in range(1 + 1, len(activities) + 2): writer.ws.cell(row=rownum, column=4).fill = myFill writer.ws.cell(row=rownum, column=4).number_format = u'"USD "#,##0.00' writer.ws.column_dimensions[u"C"].width = 70 writer.ws.column_dimensions[u"D"].width = 15 writer.ws.column_dimensions[u"E"].width = 15 writer.ws.column_dimensions[u"F"].width = 20 writer.ws.column_dimensions[u"G"].width = 15 v_id.add('A2:A{}'.format(len(activities) + 2)) v_number.add('D2:D{}'.format(len(activities) + 2)) v_status.add('E2:E{}'.format(len(activities) + 2)) v_date.add('F2:G{}'.format(len(activities) + 2)) writer.delete_first_sheet() return writer.save()
def decorateExcel(self): BOX_GREEN = Border(left=Side(border_style='thin', color='98bd47'), right=Side(border_style='thin', color='98bd47'), top=Side(border_style='thin', color='98bd47'), bottom=Side(border_style='thin', color='98bd47') ) BOX_BLUE = Border(left=Side(border_style='thin', color='5083c0'), right=Side(border_style='thin', color='5083c0'), top=Side(border_style='thin', color='5083c0'), bottom=Side(border_style='thin', color='5083c0') ) BOX_RED = Border(left=Side(border_style='thin', color='C03737'), right=Side(border_style='thin', color='C03737'), top=Side(border_style='thin', color='C03737'), bottom=Side(border_style='thin', color='C03737') ) for ws in self.wb: if ws.title == 'SUMMARY': # 셀 너비 ws.column_dimensions["a"].width = 20 for i in ('b', 'c', 'd'): ws.column_dimensions[i].width = 14 for i in ('e', 'f', 'g', 'h'): ws.column_dimensions[i].width = 8 for i in ('j', 'k', 'l', 'm'): ws.column_dimensions[i].width = 13.5 # 셀 폰트 ws['a1'].font = Font(b=True, size=14) for i in ('a','b','c','d','e','f','g','h'): ws[i+str(3)].font = Font(b=True, color=WHITE) ws[i+str(3)].fill = PatternFill(fgColor=Color('5083c0'), patternType='solid') for i in ('j', 'k', 'l', 'm'): ws[i+str(3)].font = Font(b=True, color=WHITE) ws[i+str(3)].fill = PatternFill(fgColor=Color('C03737'), patternType='solid') # 셀 얼라인 for i in ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i','j','k','l','m'): ws[i + str(3)].alignment = Alignment(horizontal='center', vertical='center') # 셀 고정 ws.freeze_panes = 'A4' # 테두리 for j in range(4,ws.max_row+1): for i in ('a', 'b', 'c','d','e','f','g','h'): ws[i+str(j)].border = BOX_BLUE for i in ('j', 'k', 'l', 'm'): ws[i + str(4)].border = BOX_RED else: # 셀 너비 ws.column_dimensions["a"].width = 50 ws.column_dimensions["b"].width = 60 ws.column_dimensions["c"].width = 15 for i in ('d', 'e', 'f', 'g', 'h', 'i'): ws.column_dimensions[i].width = 7 # 셀 폰트 ws['a1'].font = Font(b=True, size=14) for i in ('a','b','c'): ws[i+str(2)].font = Font(b=True, color=WHITE) ws[i+str(2)].fill = PatternFill(fgColor=Color('98bd47'), patternType='solid') for i in ('d','e','f','g','h','i'): ws[i+str(1)].font = Font(b=True, color=WHITE) ws[i+str(1)].fill = PatternFill(fgColor=Color('5083c0'), patternType='solid') # 셀 얼라인 for i in ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'): ws[i+str(2)].alignment= Alignment(horizontal='center', vertical='center') for i in ('d', 'e', 'f', 'g', 'h', 'i'): ws[i + str(1)].alignment = Alignment(horizontal='center', vertical='center') # 셀 고정 ws.freeze_panes = 'A3' # 테두리 for j in range(3,ws.max_row+1): for i in ('a', 'b', 'c'): ws[i+str(j)].border = BOX_GREEN for i in ('d', 'e', 'f', 'g', 'h', 'i'): ws[i + str(2)].border = BOX_BLUE # 필터 ws.auto_filter.ref = "A2:c"+str(ws.max_row+1) # 값 고정 (data-validation) dv = DataValidation(type='list', formula1='"OK,NG,NA,NT"', allow_blank=True) dv.error = 'OK, NG, NA, NT 만 기입할 수 있습니다. / Your entry is not in the list(OK, NG, NA, NT)' dv.errorTitle = 'Invalid Entry' dv.prompt = 'Please select from the list (OK, NG, NT(need to support but not yet ready), NA(not support)' dv.promptTitle = 'List Selection' ws.add_data_validation(dv) # dv.ranges.append('c3:c' + str(ws.max_row )) dv.add('c3:c' + str(ws.max_row))
def call(request): token_key = request._get_get().get("tk") token_data = manager.get_parameter(request, token_key) if token_data.get('data', None) is None: return HttpResponse(content= "<h3>" + token_data["error"] + "</h3>", mimetype='text/html') collection_name = token_data["data"]["collection_name"] wb = Workbook() ws = wb.active #ws["A1"].value = "TEST" ws.title = KEY.WS_PREFIX_NAME + "1"; #add sheet mapping wb.create_sheet(KEY.WS_MAPPING_SHEET); ws_mapping = wb[KEY.WS_MAPPING_SHEET]; #add sheet init data wb.create_sheet(KEY.WS_INIT_DATA); ws_init = wb[KEY.WS_INIT_DATA]; #Get column config ret_columns = db.get_collection("HCSSYS_CollectionInfo").aggregate([ { '$match': { '$and' : [ {'parent_field' : None}, {'field_path': {'$regex': '^,' + collection_name + ','}}, {'is_parent' : False}, ] } }, {'$project': {'_id': 0}} ]); columns = list(ret_columns) #Get export data ret_data = db.get_collection(collection_name).find({}) data_items = list(ret_data); if(len(data_items) == 0): raise "Collection '" + collection_name + "' has not been declared in the database" return null; ref_accessmode = [{ 'value':1, 'caption': u'Toàn quyền' },{ 'value': 2, 'caption': u'Từ vùng được chọn đến các cấp con' },{ 'value': 3, 'caption': u'Chỉ trên vùng được chọn' }] #create reference sheet ref_columns = [a for a in columns if a["field_name"] == "access_mode"] for col in ref_columns: wb.create_sheet(KEY.WS_PREFIX_REFERENCE + col["field_name"]) ws_ref = wb[KEY.WS_PREFIX_REFERENCE + col["field_name"]] for iData, vData in enumerate(ref_accessmode): ws_ref.cell(row=iData+1, column=1).value = vData["value"] ws_ref.cell(row=iData+1, column=2).value = vData["caption"] ws_ref.cell(row=iData+1, column=3).value = vData["value"] #create header title header_fields = [] for c in columns: if(c["field_name"] == 'access_mode'): header_fields.append({ 'field_name': c["field_name"], 'key' : KEY.PREFIX_INIT, 'display_name': c["description"], 'is_ref' : True, 'is_data_field' : False, 'is_hidden' : True }) header_fields.append({ 'field_name': c["field_name"], 'key' : KEY.PREFIX_REF, 'display_name': c["description"], 'is_ref' : True,#List of reference data 'is_data_field' : False, 'is_hidden' : False }) header_fields.append({ 'field_name': c["field_name"], 'key' : c["field_name"], 'display_name': c["description"], 'is_ref' : True, 'is_data_field' : True, #data_field in database 'is_hidden' : True }) else: header_fields.append({ 'field_name': c["field_name"], 'key' : c["field_name"], 'display_name': c["description"], 'is_ref' : False, 'is_data_field' : True, 'is_hidden' : False }) #Create header worksheet (row 1) idx_mapping_row_column = 1 ws_mapping.cell(row=1, column=1).value = KEY.BEGIN_MAPPING ws_mapping.cell(row=1, column=2).value = collection_name ws_mapping.cell(row=1, column=3).value = ws.title idx_mapping_row_column += 1 idx_init_column = 0 for iCol, vCol in enumerate(header_fields): if not (vCol["key"] == KEY.PREFIX_INIT): cell = ws.cell(row=1, column=iCol + 1 - idx_init_column) cell.value = vCol["display_name"] ws.column_dimensions[cell.column].width = len(vCol["display_name"]) if len(vCol["display_name"]) > 20 else 20 #20 characters if(vCol["is_hidden"]): ws.column_dimensions[cell.column].hidden = True if(vCol["is_data_field"] == True): #create mapping data ws_mapping.cell(row=idx_mapping_row_column, column=1).value = cell.column ws_mapping.cell(row=idx_mapping_row_column, column=2).value = vCol["field_name"] idx_mapping_row_column += 1 else: cell = ws_init.cell(row=1, column=idx_init_column + 1) cell.value = vCol["field_name"] idx_init_column += 1 ws_mapping.cell(row=idx_mapping_row_column, column=1).value = KEY.END_MAPPING #Render content to worksheet #if (len(data_items) > 0): for iItem, vItem in enumerate(data_items): num_init_column = 0 for iCol, vCol in enumerate(header_fields): idx_col = iCol - num_init_column curr_cell = ws.cell(row = iItem + 2, column = idx_col + 1) if (vCol["is_data_field"] and not vCol["is_ref"]) : curr_cell.value = vItem[vCol["field_name"]] elif (vCol["is_ref"] and vCol["key"] == KEY.PREFIX_INIT): init_cell = ws_init.cell(row = iItem + 2, column = num_init_column + 1) init_cell.value = vItem[vCol["field_name"]] num_init_column +=1 elif (vCol["is_ref"] and vCol["key"] == KEY.PREFIX_REF): #curr_cell: value list #vlookup column 1-2 in reference data ws_ref = wb[KEY.WS_PREFIX_REFERENCE + col["field_name"]] #ref_beg_cell = ws_ref.cell(row=1, column=1) ref_end_cell = ws_ref.cell(row = ws_ref.max_row, column = 2) ref_address = KEY.WS_PREFIX_REFERENCE + col["field_name"] + "!" + \ '$A$1:$B$' + str(ws_ref.max_row) # "$" + ref_beg_cell.column + "$" + str(ref_beg_cell.col_idx) + ":" + \ # "$" + ref_end_cell.column + "$" + str(ref_end_cell.col_idx) init_cell = ws_init.cell(row = iItem + 2, column = num_init_column) curr_cell.value = "=VLOOKUP(" + KEY.WS_INIT_DATA + "!" + init_cell.coordinate + "," + ref_address+ ",2, FALSE)" ref_address_title = KEY.WS_PREFIX_REFERENCE + col["field_name"] + "!" + \ "$B$1:" + "$B$" + str(ws_ref.max_row) # Create a data-validation object with list validation dv = DataValidation(type="list", formula1="=" + ref_address_title, allow_blank=True) # Optionally set a custom error message dv.error ='Your entry is not in the list' dv.errorTitle = 'Invalid Entry' # Optionally set a custom prompt message dv.prompt = 'Please select from the list' dv.promptTitle = 'List Selection' ws.add_data_validation(dv) dv.add(curr_cell) elif (vCol["is_data_field"] and vCol["is_ref"]): pre_cell = ws.cell(row = iItem + 2, column = idx_col) #vlookup column 2-3 in reference data ws_ref = wb[KEY.WS_PREFIX_REFERENCE + col["field_name"]] #ref_beg_cell = ws_ref.cell(row=1, column=2) ref_end_cell = ws_ref.cell(row = ws_ref.max_row, column = ws_ref.max_column) ref_address = KEY.WS_PREFIX_REFERENCE + col["field_name"] + "!" + \ '$B$1:$C$' + str(ws_ref.max_row) #"$" + ref_beg_cell.column + "$" + str(ref_beg_cell.col_idx) + ":" + \ #"$" + ref_end_cell.column + "$" + str(ref_end_cell.col_idx) curr_cell.value = "=VLOOKUP(" + pre_cell.coordinate + "," + ref_address+ ",2, FALSE)" #format worksheet ws = format_style(worksheet_style["NORMAL"]).format(ws) #wb.defined_names.append(defined_name.DefinedName(attr_text="HCSSYS_DataDomain!A$1", name="TEST_NAME_0000000")) response = HttpResponse(content=save_virtual_workbook(wb), mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') time_export = datetime.datetime.now().strftime("%Y.%m.%dT%H.%M.%S"); response['Content-Disposition'] = 'attachment; filename=' + 'export-' + time_export + '.xlsx' return response
from openpyxl.worksheet.datavalidation import DataValidation from projectdashboard.lib.codelists import get_codelists_lookups_by_name # Activity Status validation v_status = DataValidation(type="list", allow_blank=False) v_status.error = 'Your entry is not in the list' v_status.errorTitle = 'Activity Status' v_status.prompt = 'Please select from the list' v_status.promptTitle = 'Activity Status' # ID validation v_id = DataValidation(type="whole") v_id.errorTitle = "Invalid ID" v_id.error = "Please enter a valid ID" v_id.promptTitle = 'Liberia Project Dashboard ID' v_id.prompt = 'Please do not edit this ID. It is used by the Liberia Project Dashboard to uniquely identify activities.' # Date validation v_date = DataValidation(type="date") v_date.errorTitle = "Invalid date" v_date.error = "Please enter a valid date" # Number validation v_number = DataValidation(type="decimal") v_number.errorTitle = "Invalid number" v_number.error = "Please enter a valid number"
from openpyxl.worksheet.datavalidation import DataValidation # Create the workbook and worksheet we'll be working with wb = Workbook() ws = wb.active # Create a data-validation object with list validation dv = DataValidation( type="list", formula1='"корпусном,металлическом,мягкой мебели,столярном"', allow_blank=True, ) # Optionally set a custom error message dv.error = "Запись отсутствует в списке" dv.errorTitle = "Ошибочный ввод" # Optionally set a custom prompt message dv.prompt = "Выберите цех" dv.promptTitle = "Список цехов" # Add the data-validation object to the worksheet ws.add_data_validation(dv) # Create some cells, and add them to the data-validation object c1 = ws["A1"] c1.value = "Dog" dv.add(c1) wb.save(r"d:\test.xlsx") os.startfile(r"d:\test.xlsx")
def call(request): token_key = request._get_get().get("tk") token_data = manager.get_parameter(request, token_key) if token_data.get('data', None) is None: return HttpResponse(content="<h3>" + token_data["error"] + "</h3>", mimetype='text/html') collection_name = token_data["data"]["collection_name"] wb = Workbook() ws = wb.active #ws["A1"].value = "TEST" ws.title = KEY.WS_PREFIX_NAME + "1" #add sheet mapping wb.create_sheet(KEY.WS_MAPPING_SHEET) ws_mapping = wb[KEY.WS_MAPPING_SHEET] #add sheet init data wb.create_sheet(KEY.WS_INIT_DATA) ws_init = wb[KEY.WS_INIT_DATA] #Get column config ret_columns = db.get_collection("HCSSYS_CollectionInfo").aggregate([{ '$match': { '$and': [ { 'parent_field': None }, { 'field_path': { '$regex': '^,' + collection_name + ',' } }, { 'is_parent': False }, ] } }, { '$project': { '_id': 0 } }]) columns = list(ret_columns) #Get export data ret_data = db.get_collection(collection_name).find({}) data_items = list(ret_data) if (len(data_items) == 0): raise "Collection '" + collection_name + "' has not been declared in the database" return null ref_accessmode = [{ 'value': 1, 'caption': u'Toàn quyền' }, { 'value': 2, 'caption': u'Từ vùng được chọn đến các cấp con' }, { 'value': 3, 'caption': u'Chỉ trên vùng được chọn' }] #create reference sheet ref_columns = [a for a in columns if a["field_name"] == "access_mode"] for col in ref_columns: wb.create_sheet(KEY.WS_PREFIX_REFERENCE + col["field_name"]) ws_ref = wb[KEY.WS_PREFIX_REFERENCE + col["field_name"]] for iData, vData in enumerate(ref_accessmode): ws_ref.cell(row=iData + 1, column=1).value = vData["value"] ws_ref.cell(row=iData + 1, column=2).value = vData["caption"] ws_ref.cell(row=iData + 1, column=3).value = vData["value"] #create header title header_fields = [] for c in columns: if (c["field_name"] == 'access_mode'): header_fields.append({ 'field_name': c["field_name"], 'key': KEY.PREFIX_INIT, 'display_name': c["description"], 'is_ref': True, 'is_data_field': False, 'is_hidden': True }) header_fields.append({ 'field_name': c["field_name"], 'key': KEY.PREFIX_REF, 'display_name': c["description"], 'is_ref': True, #List of reference data 'is_data_field': False, 'is_hidden': False }) header_fields.append({ 'field_name': c["field_name"], 'key': c["field_name"], 'display_name': c["description"], 'is_ref': True, 'is_data_field': True, #data_field in database 'is_hidden': True }) else: header_fields.append({ 'field_name': c["field_name"], 'key': c["field_name"], 'display_name': c["description"], 'is_ref': False, 'is_data_field': True, 'is_hidden': False }) #Create header worksheet (row 1) idx_mapping_row_column = 1 ws_mapping.cell(row=1, column=1).value = KEY.BEGIN_MAPPING ws_mapping.cell(row=1, column=2).value = collection_name ws_mapping.cell(row=1, column=3).value = ws.title idx_mapping_row_column += 1 idx_init_column = 0 for iCol, vCol in enumerate(header_fields): if not (vCol["key"] == KEY.PREFIX_INIT): cell = ws.cell(row=1, column=iCol + 1 - idx_init_column) cell.value = vCol["display_name"] ws.column_dimensions[cell.column].width = len( vCol["display_name"]) if len( vCol["display_name"]) > 20 else 20 #20 characters if (vCol["is_hidden"]): ws.column_dimensions[cell.column].hidden = True if (vCol["is_data_field"] == True): #create mapping data ws_mapping.cell(row=idx_mapping_row_column, column=1).value = cell.column ws_mapping.cell(row=idx_mapping_row_column, column=2).value = vCol["field_name"] idx_mapping_row_column += 1 else: cell = ws_init.cell(row=1, column=idx_init_column + 1) cell.value = vCol["field_name"] idx_init_column += 1 ws_mapping.cell(row=idx_mapping_row_column, column=1).value = KEY.END_MAPPING #Render content to worksheet #if (len(data_items) > 0): for iItem, vItem in enumerate(data_items): num_init_column = 0 for iCol, vCol in enumerate(header_fields): idx_col = iCol - num_init_column curr_cell = ws.cell(row=iItem + 2, column=idx_col + 1) if (vCol["is_data_field"] and not vCol["is_ref"]): curr_cell.value = vItem[vCol["field_name"]] elif (vCol["is_ref"] and vCol["key"] == KEY.PREFIX_INIT): init_cell = ws_init.cell(row=iItem + 2, column=num_init_column + 1) init_cell.value = vItem[vCol["field_name"]] num_init_column += 1 elif (vCol["is_ref"] and vCol["key"] == KEY.PREFIX_REF): #curr_cell: value list #vlookup column 1-2 in reference data ws_ref = wb[KEY.WS_PREFIX_REFERENCE + col["field_name"]] #ref_beg_cell = ws_ref.cell(row=1, column=1) ref_end_cell = ws_ref.cell(row=ws_ref.max_row, column=2) ref_address = KEY.WS_PREFIX_REFERENCE + col["field_name"] + "!" + \ '$A$1:$B$' + str(ws_ref.max_row) # "$" + ref_beg_cell.column + "$" + str(ref_beg_cell.col_idx) + ":" + \ # "$" + ref_end_cell.column + "$" + str(ref_end_cell.col_idx) init_cell = ws_init.cell(row=iItem + 2, column=num_init_column) curr_cell.value = "=VLOOKUP(" + KEY.WS_INIT_DATA + "!" + init_cell.coordinate + "," + ref_address + ",2, FALSE)" ref_address_title = KEY.WS_PREFIX_REFERENCE + col["field_name"] + "!" + \ "$B$1:" + "$B$" + str(ws_ref.max_row) # Create a data-validation object with list validation dv = DataValidation(type="list", formula1="=" + ref_address_title, allow_blank=True) # Optionally set a custom error message dv.error = 'Your entry is not in the list' dv.errorTitle = 'Invalid Entry' # Optionally set a custom prompt message dv.prompt = 'Please select from the list' dv.promptTitle = 'List Selection' ws.add_data_validation(dv) dv.add(curr_cell) elif (vCol["is_data_field"] and vCol["is_ref"]): pre_cell = ws.cell(row=iItem + 2, column=idx_col) #vlookup column 2-3 in reference data ws_ref = wb[KEY.WS_PREFIX_REFERENCE + col["field_name"]] #ref_beg_cell = ws_ref.cell(row=1, column=2) ref_end_cell = ws_ref.cell(row=ws_ref.max_row, column=ws_ref.max_column) ref_address = KEY.WS_PREFIX_REFERENCE + col["field_name"] + "!" + \ '$B$1:$C$' + str(ws_ref.max_row) #"$" + ref_beg_cell.column + "$" + str(ref_beg_cell.col_idx) + ":" + \ #"$" + ref_end_cell.column + "$" + str(ref_end_cell.col_idx) curr_cell.value = "=VLOOKUP(" + pre_cell.coordinate + "," + ref_address + ",2, FALSE)" #format worksheet ws = format_style(worksheet_style["NORMAL"]).format(ws) #wb.defined_names.append(defined_name.DefinedName(attr_text="HCSSYS_DataDomain!A$1", name="TEST_NAME_0000000")) response = HttpResponse( content=save_virtual_workbook(wb), mimetype= 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet') time_export = datetime.datetime.now().strftime("%Y.%m.%dT%H.%M.%S") response[ 'Content-Disposition'] = 'attachment; filename=' + 'export-' + time_export + '.xlsx' return response
def generate_review_form_spreadsheet(paperInfoWithReview, tjyear, isManager, isPaperSub = False): filenameStr = paperSubUtilsFile.form_file_name(tjyear,\ paperInfoWithReview['paperInfo']['submissionSequence'],paperInfoWithReview['paperInfo']) if isManager == True: filenameStr = filenameStr+'manager_Review' if isPaperSub == False: reviewInfo = paperInfoWithReview['managerReviewInfo'] else: filenameStr = filenameStr+'expert_Review' if isPaperSub == False: reviewInfo = paperInfoWithReview['expertReviewInfo'] filename = filenameStr+'.xlsx' copyfile(os.path.join(app.config['UPLOAD_FOLDER'],'review_form.xlsx'), os.path.join(app.config['UPLOAD_FOLDER'],filename)) wb = load_workbook(os.path.join(app.config['UPLOAD_FOLDER'],filename)) ws = wb.active ws.cell(row = 1, column = 1, value = app.config['EDITORIAL_COMMITEE_INFO']['journal']+ app.config['EDITORIAL_COMMITEE_INFO']['form']) ws.cell(row = 3, column = 2, value = paperInfoWithReview['paperInfo']['paperTitle']) ws.cell(row = 4, column = 2, value = paperInfoWithReview['authorList'][0]['email']) if isManager == True: ws.cell(row = 5, column = 2, value = paperInfoWithReview['managerInfo']['email']) else: ws.cell(row = 5, column = 2, value = paperInfoWithReview['expertInfo']['email']) i=0 for reviewItemName in app.config['REVIEW_ITEM']: ws.cell(row=9+i,column=1,value=reviewItemName) i = i+1 i = 0 for question in app.config['REVIEW_COMMENTS_QUESTIONS']: ws.cell(row=19+i*5, column= 1, value = question) i = i+1 set_border(ws, 'A18:D43') if isPaperSub == False: if reviewInfo['reviewSubmitted']==True: i=0 for reviewItem in reviewInfo['itemList']: ws.cell(row=9+i,column=3,value=app.config['REVIEW_RESULT'][reviewItem['reviewItemGrade']]) i = i+1 ws.cell(row=16, column=3, value=app.config['REVIEW_RESULT'][reviewInfo['Overall']]) i = 0 for reviewQuestion in reviewInfo['questionList']: ws.cell(row=20+i*5, column= 1, value = reviewQuestion['answer']) i = i+1 else: ws_bk = sheet = wb.get_sheet_by_name('bk') i=1 string = "" for result in app.config['REVIEW_RESULT']: ws_bk.cell(row=1,column=i, value=result) i = i+1 dv = DataValidation(type="list",\ formula1="{0}!$A$1:$D$1".format(quote_sheetname('bk'))\ ) #Optionally set a custom error message dv.error ='Your entry is not in the list' dv.errorTitle = 'Invalid Entry' #Optionally set a custom prompt message dv.prompt = 'Please select from the list' dv.promptTitle = 'List Selection' ws.add_data_validation(dv) dv.add('C9:c16') else: ws_bk = sheet = wb.get_sheet_by_name('bk') i=1 string = "" for result in app.config['REVIEW_RESULT']: ws_bk.cell(row=1,column=i, value=result) i = i+1 dv = DataValidation(type="list",\ formula1="{0}!$A$1:$D$1".format(quote_sheetname('bk'))\ ) #Optionally set a custom error message dv.error ='Your entry is not in the list' dv.errorTitle = 'Invalid Entry' #Optionally set a custom prompt message dv.prompt = 'Please select from the list' dv.promptTitle = 'List Selection' ws.add_data_validation(dv) dv.add('C9:c16') wb.save(os.path.join(app.config['UPLOAD_FOLDER'],filename)) return filename
""" # 数据验证器可以应用于单元格范围,但不强制或评估。范围不必是连续的: # 例如。“A1 B2:B5”包含A1和细胞B2到B5,而不是A2或B2。 from openpyxl import Workbook from openpyxl.worksheet.datavalidation import DataValidation # Create the workbook and worksheet we'll be working with wb = Workbook() ws = wb.active # Create a data-validation object with list validation dv = DataValidation(type='list', formula1='"Dog,Cat,Bat"', allow_blank=True) # Optionally set a custom error message dv.error = 'Your entyr is not in the list' dv.errorTitle = 'Invalid Entyr' # Optionally set a custom prompt message dv.prompt = 'please select from the list' dv.promptTitle = 'List Selection' # Add the data-validation object to the worksheet ws.add_data_validation(dv) # Create some cells, and add them to the data-validation object c1 = ws["A1"] c1.value = "Dog" dv.add(c1) c2 = ws["A2"] c2.value = "An invalid value" dv.add(c2)