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 add_excel_choice(formula1, prompt, prompt_title, table, column, row): dv = DataValidation(type='list', formula1=formula1, allow_blank=False) dv.prompt = prompt dv.promptTitle = prompt_title table.add_data_validation(dv) for i in range(2, row + 1): dv.add(table[column + str(i)])
def create_list_validation(value, strict=True, allow_blank=True): if type(value) == list: formula = _build_list_formula(value) else: formula = value dv = DataValidation(type="list", formula1=formula, showErrorMessage=strict, allow_blank=allow_blank) if strict: dv.promptTitle = 'Strict Selection' dv.prompt = 'You must select a value from the list' else: dv.promptTitle = 'Proposed Selection' dv.prompt = 'You may select a value from the list or enter your own' if allow_blank: dv.promptTitle += " (blank allowed)" return dv
def create_list_validation(value, strict=True, allow_blank=True): if isinstance(value, list): formula = _build_list_formula(value) else: formula = value dv = DataValidation(type="list", formula1=formula, showErrorMessage=strict, allow_blank=allow_blank) if strict: dv.promptTitle = 'Strict Selection' dv.prompt = 'You must select a value from the list' else: dv.promptTitle = 'Proposed Selection' dv.prompt = 'You may select a value from the list or enter your own' if allow_blank: dv.promptTitle += " (blank allowed)" 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 create_size_configure_excel(conf_path, order, size): headers = ('order_no', 'size_label', 'num_id', 'name_index', 'fold', 'unit', 'check', 'std_val', 'tol_lower', 'tol_upper', 'offset') with open(conf_path, encoding='utf-8') as f: content = yaml.safe_load(f.read()) result = [] steps = content.get('steps') for s in size: for i in steps: std_val_map = { k: v.get("val") for k, v in i.get("std_val").items() } if i.get("std_val") else {} std_val_list = list(std_val_map.keys()) measure_list = list( i.get('measure').keys()) if i.get('measure') else [] diff_list = list(i.get('diff').keys()) if i.get('diff') else [] num_id_list = set(std_val_list + measure_list + diff_list) dataset = [(order, s, i, '01', '', 'Inch', '', std_val_map.get(i), '', '', '') for i in num_id_list] result += dataset data = tablib.Dataset(*result, headers=headers, title="sheet1") open("2.xxxx.xlsx", "wb").write(data.xlsx) # 继续对表格进行处理 excel = load_workbook("2.xxxx.xlsx") table = excel.get_sheet_by_name("sheet1") row = table.max_row dv = DataValidation(type='list', formula1='"Inch", "cm"', allow_blank=False) dv.prompt = "选择单位" dv.promptTitle = 'List Selection' table.add_data_validation(dv) for i in range(2, row + 1): dv.add(table["F" + str(i)]) # 添加单元格颜色 title = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K"] for i in range(2, row + 1): for t in title: table[t + "1"].fill = greyfill table[t + "1"].border = border if table["B" + str(i)].value in size and size.index( table["B" + str(i)].value) % 2 == 0: table[t + str(i)].fill = redfill elif table["B" + str(i)].value in size and size.index( table["B" + str(i)].value) % 2 == 1: table[t + str(i)].fill = bluefill table[t + str(i)].border = border excel.save("2.xxxx.xlsx")
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 create_validation(header): # if we need the regex to match the dropdown string - from pythex.org # dropdown_regex = # re.compile('"=\\'Dropdown List\\'!\$([A-Z]+)\$(\d+):\$([A-Z]+)\$(\d+)"') # try: f_str = VALIDATION_REFERENCES[header] dv = DataValidation(type='list', formula1=f_str, allow_blank=True) dv.prompt = "Please select from the list" dv.promptTitle = 'List Selection' return dv except KeyError: print("No validation") return
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 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 processor(): # create workbook wb = Workbook() wb = load_workbook('static/output/output.xlsm', keep_vba=True) # create worksheets # bankstatement = wb.active # bankstatement.title = 'Bank Statement' # billstatement = wb.create_sheet("Bill Statement") # loanrepayment = wb.create_sheet("Loan Repayment Schedule") # incomestatement = wb.create_sheet("Income Statement") # balancesheet = wb.create_sheet("Balance Sheet") #calculatedcf = wb.create_sheet("Calculated CF") usermetrics = wb.create_sheet("User Metric") summary = wb.create_sheet("Summary") # add bank statement data in bankstatement worksheet # for r in dataframe_to_rows(bankdata, index=False, header=True): # bankstatement.append(r) # bankstatement.delete_rows(1) # # # add bill statement data in billstatement worksheet # for r in dataframe_to_rows(billdata, index=False, header=True): # billstatement.append(r) # # # add loan repayment data in loanrepayment worksheet # for r in dataframe_to_rows(loandata, index=False, header=True): # loanrepayment.append(r) # add balance sheet from CAT to balancesheet worksheet # need to figure this out #balancesheet = wb2.get_sheet("Balance Sheet") # assign fixed variables to user metrics # User Input # create drop down list for user input ## Type of Client clientdv = DataValidation(type="list", formula1='"new-to-bank,existing"', allow_blank=False) clientdv.prompt = 'Please select from the list' clientdv.promptTitle = 'Client List Selection' ## Payment status paymentdv = DataValidation(type="list", formula1='"punctual,not punctual"', allow_blank=False) paymentdv.prompt = 'Please select from the list' paymentdv.promptTitle = 'Payment List Selection' ## Operating account oadv = DataValidation(type="list", formula1='"reasonable,not reasonable"', allow_blank=False) oadv.prompt = 'Please select from the list' oadv.promptTitle = 'Behavior List Selection' ## Liquidity status liquiditydv = DataValidation(type="list", formula1='"low,medium,high"', allow_blank=False) liquiditydv.prompt = 'Please select from the list' liquiditydv.promptTitle = 'Liquidity Level List Selection' ## TU Information tudv = DataValidation(type="list", formula1='"found,not found"', allow_blank=False) tudv.prompt = 'Please select from the list' tudv.promptTitle = 'Behavior List Selection' # Summary # color scheme greyFill = PatternFill(start_color='757171', end_color='757171', fill_type='solid') whiteFill = PatternFill(start_color='d0cece', end_color='d0cece', fill_type='solid') # text style and color whiteheadingstyle = Font(size=14, name='Segoe UI', color='ffffff', underline='single') whitebodystyle = Font(size=11, name='Segoe UI', color='ffffff') greybodystyle = Font(size=11, name='Segoe UI', color='757171') # width width = 2.8 # formatting # change background color # No of written Rows in sheet # 1,048,576 rows by 16,384 columns # No of written Rows in sheet r = 500 # No of written Columns in sheet c = 100 # mass format color, font, filler lines, result lines and account background sentences in excel filler_lines_in_grey_square = ["Please fill in the blanks accordingly via the dropdown options", \ "(The inputs will be used within the sentences on the left)", \ "Description", \ "Type of client", \ "Type of business (SIC)", \ "Type of business (NAICS)", \ "Number of Installment Loans", \ "Payment Status", \ "Operating Account", \ "Liquidity Level", \ "Negative TU Information"] result_lines_in_grey_square = [clientdv, \ '=VLOOKUP(\'Income Statement\'!$E$2, SIC!$A$4:$B$1008,2,FALSE)', \ '=VLOOKUP(\'Income Statement\'!$E$3, NAICS!$B$4:$C$2231,2,FALSE)', \ "Fill in here", \ paymentdv, \ oadv, \ liquiditydv, \ tudv] account_background_sentences = ['=CONCATENATE("This is a ",\'User Input\'!B2," client of Citi. At present, client also incorporated bank accounts in Citi for ",$G$7," activities.")', \ '=CONCATENATE("The company engages in ",$G$6," business, sales proceeds collection was captured in bank account.")', \ '=CONCATENATE("With reference to ",\'User Input\'!B3," bank statement, about ",DOLLAR(Final!D27)," credit transaction was recorded. The annualised sales proceeds was estimated to be ",DOLLAR(Final!D28),". If subject have more than 2 banks, state the transaction figures and periods.")', \ 'Such figure could serve as an estimate for annual sales of subject borrower. ', \ '=CONCATENATE("As at ",TEXT(MONTH(\'Bank Statement\'!A44),"mmm")," ",YEAR(\'Bank Statement\'!A44),", client is keeping about ",DOLLAR(\'Bank Statement\'!E43)," bank balance in Citibank. ")',\ '=CONCATENATE("No negative record could be found in TU and CCRA on ", TEXT(MONTH(\'Bank Statement\'!A44),"mmm")," ",YEAR(\'Bank Statement\'!A44),".")', \ '=CONCATENATE(G8," instalment loans was respectively booked for BankA (Loan Amount, number of years) e.g. HKD 5MM for 10 years, and it is revealed by TU repayment of these instalment has been ",G9,".")', \ '=CONCATENATE("As per bank statement from Citibank provided, it is observed that Subject received ",DOLLAR(Final!D28)," throughput (Annualized: $$$)")', \ '==CONCATENATE("AO confirmed that aggregated operating account for $$$ is considered ",G10)', \ '=CONCATENATE("Average free cash flow is around ",DOLLAR(Final!E27)," in bank statement. ", \'User Input\'!B7," liquidity is observed.")', \ '=CONCATENATE("Negative information is ",G12, " on TU.")'] types_of_data_validation = [clientdv, oadv, liquiditydv, tudv] for i in range(1, r + 1): for j in range(1, c + 1): summary.cell(row=i, column=j).fill = greyFill summary.cell(row=i, column=j).font = whitebodystyle if j == 4: if i == 2: # heading style summary.cell(row=i, column=j).font = whiteheadingstyle # add filler lines summary.cell( row=i, column=j ).value = 'ACCOUNT BACKGROUND (For your designated usage)' elif i > 3 and i < 15: # add account background sentences summary.cell( row=i, column=j).value = account_background_sentences[i - 4] summary.cell(row=i, column=j).alignment = Alignment( wrap_text=Alias('wrapText'), wrapText=Bool(False)) # labelling the sentences if i > 3 and i < 15 and j == 2: summary.cell(row=i, column=j).value = i - 3 # create grey square in excel, with designated font for i in range(2, 14): for j in range(5, 9): if i == 4 and j == 6: summary.cell(row=i, column=j).fill = greyFill summary.cell(row=i, column=j).font = whitebodystyle elif i == 4 and j == 7: summary.cell(row=i, column=j).fill = greyFill summary.cell(row=i, column=j).font = whitebodystyle else: summary.cell(row=i, column=j).fill = whiteFill summary.cell(row=i, column=j).font = greybodystyle # add filler lines and data validation if j == 6 and i < 13: summary.cell( row=i, column=j).value = filler_lines_in_grey_square[i - 2] if i > 4: if type(result_lines_in_grey_square[i - 5]) == str: summary.cell(row=i, column=j + 1).value = result_lines_in_grey_square[i - 5] summary.cell(row=i, column=j + 1).alignment = Alignment( wrap_text=Alias('wrapText'), wrapText=Bool(False)) else: summary.add_data_validation( result_lines_in_grey_square[i - 5]) result_lines_in_grey_square[i - 5].add( summary.cell(row=i, column=j + 1)) summary.cell(row=i, column=j + 1).value = "Select from drop-down" if j == 7 and i == 3: summary.cell(row=i, column=j).value = "To be filled" # set the width of the column specialcolumns = ['A', 'B', 'C', 'E', 'H'] for i in specialcolumns: summary.column_dimensions[i].width = width summary.column_dimensions['D'].width = 105 summary.column_dimensions['F'].width = 29 summary.column_dimensions['G'].width = 49.64 # adjust zoom level summary.sheet_view.zoomScale = 85 # Save the file wb.save("static/output/output.xlsm")
#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) # Optionally set a custom error message dv_active.error ='You have entered free text'
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()
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') # store output file on U drive with following convention # with following format: 'Wk of [Monday following current day] Anticipated #HL Training Attendance.xlsx'# Generate new sheet inside of the workbook print 'Saving Spreadsheet...'
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 # 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")
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"
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 generate_template(self): template = 'Load Summary Template.xlsx' path = os.path.abspath('.') file_path = os.path.join(path, template) print(file_path) border = Border(left=Side(border_style='thin', color='000000'), right=Side(border_style='thin', color='000000'), top=Side(border_style='thin', color='000000'), bottom=Side(border_style='thin', color='000000')) if not os.path.isfile(file_path): table = Workbook() sheet = table['Sheet'] sheet.title = 'Main' sheet['A1'] = 'Wind Turbine Name' sheet['B1'] = 'Category' sheet['C1'] = 'Path' # font for the first row sheet['A1'].font = Font(name='Microsoft Ya Hei', size=9, bold=True) sheet['B1'].font = Font(name='Microsoft Ya Hei', size=9, bold=True) sheet['C1'].font = Font(name='Microsoft Ya Hei', size=9, bold=True) # width for each column sheet.column_dimensions['A'].width = 20 sheet.column_dimensions['B'].width = 10 sheet.column_dimensions['C'].width = 70 for col in sheet.iter_rows(min_row=1, max_col=3, max_row=1): for cell in col: cell.alignment = Alignment(vertical='center', horizontal='center') for row in sheet.iter_rows(min_row=2, max_col=3, max_row=31): for cell in row: cell.font = Font(name='Microsoft Ya Hei', size=9) # for row in sheet.iter_rows(min_row=2, max_col=1, max_row=31): for cell in row: cell.alignment = Alignment(vertical='center', horizontal='center') # set data validation for column B dv = DataValidation(type='list', formula1='"Ultimate,Rainflow,Post,LCT,DLC12"') dv.prompt = 'Make sure the selection is unique for each loop' dv.promptTitle = 'List Selection' sheet.add_data_validation(dv) dv.add('B2:B301') # apply the validation to a range of cellls # border for i in range(1, 52): for j in range(1, 4): sheet.cell(row=i, column=j).border = border # merge for i in range(10): sheet.merge_cells(start_row=5 * i + 2, start_column=1, end_row=5 * i + 6, end_column=1) table.save('Load Summary Template.xlsx') print('Template generated successfully!') # open template os.startfile(file_path)
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
def create_scan_code_excel(conf_path, entry_code, order_no, size_label): # 使用openpyxl 创建excel表格 wb = Workbook() ws = wb.active dv = DataValidation( type='list', formula1= '"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24"', allow_blank=False) dv.prompt = "选择颜色" dv.promptTitle = 'List Selection' ws.add_data_validation(dv) # 设置表头 entry_code_title = ws["A1"] entry_code_title.value = "entry_code" entry_code_title.fill = greyfill entry_code_title.border = border order_no_title = ws["B1"] order_no_title.value = "order_no" order_no_title.fill = greyfill order_no_title.border = border style_id_title = ws["C1"] style_id_title.value = "style_id" style_id_title.fill = greyfill style_id_title.border = border batch_no_title = ws["D1"] batch_no_title.value = "batch_no" batch_no_title.fill = greyfill batch_no_title.border = border color_no_title = ws["E1"] color_no_title.value = "color_no" color_no_title.fill = greyfill color_no_title.border = border size_label_title = ws["F1"] size_label_title.value = "size_label" size_label_title.fill = greyfill size_label_title.border = border quantity_title = ws["G1"] quantity_title.value = "quantity" quantity_title.fill = greyfill quantity_title.border = border row = ["A", "B", "C", "D", "E", "F", "G"] with open(conf_path, encoding='utf-8') as f: content = yaml.safe_load(f.read()) info = content.get('info') style_id = info.get('style_id') # 组织数据 data_list = [[ entry_code, order_no, style_id, "", "24", size_label[i], "1" ] for i in range(len(size_label))] # 填充数据 for data in data_list: current_row = ws.max_row for i, v in enumerate(row): if v == 'E': dv.add(ws[v + str(current_row + 1)]) else: ws[v + str(current_row + 1)].value = data[i] ws[v + str(current_row + 1)].fill = redfill ws[v + str(current_row + 1)].border = border wb.save("1.xxxx.xlsx")