def _get_range_string(self): fmt = "{coord}" title = self.title if self.title: fmt = u"{title}!{coord}" title = quote_sheetname(self.title) return fmt.format(title=title, coord=self.coord)
def create_named_range(self, name, worksheet=None, value=None, scope=None): """Create a new named_range on a worksheet""" defn = DefinedName(name=name, localSheetId=scope) if worksheet is not None: defn.value = "{0}!{1}".format(quote_sheetname(worksheet.title), value) else: defn.value = value self.defined_names.append(defn)
def _update_job_order_sheet(self, workbook): try: JobOrder_Sheet = workbook.get_sheet_by_name('Job Oder') JobOrder_Sheet.protection.sheet = True JobOrder_Sheet.protection.set_password('pabi2') TahomaFont = Font(bold=False, name='Tahoma', size=10) joborders = self.env['cost.control'].search([]) # Add cost control from odoo in cost control data sheet row = 2 for joborder in joborders: JobOrder_Sheet.cell( row=row, column=1, value=joborder.cost_control_type_id.name ).font = TahomaFont JobOrder_Sheet.cell( row=row, column=2, value=joborder.code ).font = TahomaFont JobOrder_Sheet.cell( row=row, column=3, value=joborder.name ).font = TahomaFont JobOrder_Sheet.cell( row=row, column=4, value=joborder.name_short ).font = TahomaFont JobOrder_Sheet.cell( row=row, column=5, value='' ).font = TahomaFont JobOrder_Sheet.cell( row=row, column=6, value='' ).font = TahomaFont row += 1 formula1 = "{0}!$C$2:$C$%s" % (row) JobOrdeListFormula = DataValidation( type="list", formula1=formula1.format( quote_sheetname('Job Oder') ) ) SHEET_FORMULAS.update({'job_order_formula': JobOrdeListFormula}) except: pass return True
def _update_activity_group_sheet(self, workbook): try: AGObj = self.env['account.activity.group'] AG_Sheet = workbook.get_sheet_by_name('Activity Group') AG_Sheet.protection.sheet = True AG_Sheet.protection.set_password('pabi2') expense_ags = AGObj.search([('budget_method', '=', 'expense')]) # create lines in activity group data sheet expense_row = 2 for ag in expense_ags: AG_Sheet.cell(row=expense_row, column=1, value=ag.name) AG_Sheet.cell(row=expense_row, column=2, value=ag.description) expense_row += 1 formula1 = "{0}!$A$2:$A$%s" % (expense_row) ActGroupList = DataValidation( type="list", formula1=formula1.format( quote_sheetname('Activity Group') ) ) SHEET_FORMULAS.update({'expense_ag_list': ActGroupList}) revenue_ags = AGObj.search([('budget_method', '=', 'revenue')]) # create lines in activity group data sheet revenue_row = expense_row + 1 for ag in revenue_ags: AG_Sheet.cell(row=revenue_row, column=1, value=ag.name) AG_Sheet.cell(row=revenue_row, column=2, value=ag.description) revenue_row += 1 revenue_formula = "{0}!$A$%s:$A$%s" % (expense_row+1, revenue_row) RevenueActGroupList = DataValidation( type="list", formula1=revenue_formula.format( quote_sheetname('Activity Group') ) ) # Attach formulas to sheet to use further SHEET_FORMULAS.update({'revenue_ag_list': RevenueActGroupList}) except: pass return True
def write_workbook(workbook): """Write the core workbook xml.""" wb = workbook wb.rels = RelationshipList() root = WorkbookPackage() props = WorkbookProperties() if wb.code_name is not None: props.codeName = wb.code_name root.workbookPr = props # book views active = get_active_sheet(wb) view = BookView(activeTab=active) root.bookViews =[view] # worksheets for idx, sheet in enumerate(wb._sheets, 1): sheet_node = ChildSheet(name=sheet.title, sheetId=idx, id="rId{0}".format(idx)) rel = Relationship( type=sheet._rel_type, Target='{0}s/{1}'.format(sheet._rel_type, sheet._path) ) wb.rels.append(rel) if not sheet.sheet_state == 'visible': if len(wb._sheets) == 1: raise ValueError("The only worksheet of a workbook cannot be hidden") sheet_node.state = sheet.sheet_state root.sheets.append(sheet_node) # external references if wb._external_links: # need to match a counter with a workbook's relations rId = len(wb.rels) for idx, link in enumerate(wb._external_links, 1): ext = ExternalReference(id="rId{0}".format(rId + idx)) rel = Relationship(type=link._rel_type, Target='{0}s/{1}'.format(link._rel_type, link._path) ) root.externalReferences.append(ext) wb.rels.append(rel) # Defined names defined_names = copy(wb.defined_names) # don't add special defns to workbook itself. # Defined names -> autoFilter for idx, sheet in enumerate(wb.worksheets): auto_filter = sheet.auto_filter.ref if auto_filter: name = DefinedName(name='_FilterDatabase', localSheetId=idx, hidden=True) name.value = "{0}!{1}".format(quote_sheetname(sheet.title), absolute_coordinate(auto_filter) ) defined_names.append(name) # print titles if sheet.print_titles: name = DefinedName(name="Print_Titles", localSheetId=idx) name.value = quote_sheetname(sheet.print_titles) defined_names.append(name) # print areas if sheet.print_area: name = DefinedName(name="Print_Area", localSheetId=idx) name.value = ",".join(["{0}!{1}".format(quote_sheetname(sheet.title), r) for r in sheet.print_area]) defined_names.append(name) root.definedNames = defined_names root.calcPr = CalcProperties(calcId=124519, fullCalcOnLoad=True) return tostring(root.to_tree())
ws['B1'].font = headerfont ws['C1'].font = headerfont ws['D1'].font = headerfont if size == '6': cap08 += 2 * len(cables) elif size == '8': cap10 += 2 * len(cables) for index, line in enumerate(cables): row = index + 2 ws['A{}'.format(row)] = line['Label'] ws['B{}'.format(row)] = line['Code A'] ws['C{}'.format(row)] = line['Code B'] ws['D{}'.format(row)] = round(line['Confection Length'], 1) ws['A{}'.format(row + 1)] = "=COUNTA(A2:A{})".format(row) ws['D{}'.format(row + 1)] = "=SUM(D2:D{})".format(row) wb['Summary'].append([ ws_name, '={}!A{}'.format(utils.quote_sheetname(ws_name), row + 1), '={}!D{} * 0.001'.format(utils.quote_sheetname(ws_name), row + 1) ]) # ============================================================================== # Save # ============================================================================== wb.save(FILE_O)
The side-effect of the following function is to ensure there is a 'master_transposed.csv' file present in SOURCE_DIR returns a list of dicts, which makes up all the data from the master """ parse_csv_to_file(source_master_csv) with open(SOURCE_DIR + 'master_transposed.csv', 'r') as f: r = csv.DictReader(f) ls = [row for row in r] return ls sheet_name = "Dropdown" VALIDATION_REFERENCES = { 'Quarter': "{0}!$A$2:$A$9".format(quote_sheetname(sheet_name)), 'Joining Qtr': "{0}!$B$2:$B$25".format(quote_sheetname(sheet_name)), 'Classification': "{0}!$C$2:$C$4".format(quote_sheetname(sheet_name)), 'Entity format': "{0}!$D$2:$D$4".format(quote_sheetname(sheet_name)), 'Methodology': "{0}!$E$2:$E$10".format(quote_sheetname(sheet_name)), 'Category': "{0}!$F$2:$H$11".format(quote_sheetname(sheet_name)), 'Scope Changed': "{0}!$G$2:$I$4".format(quote_sheetname(sheet_name)), 'Monetised / Non Monetised Benefits': "{0}!$H$2:$H$4".format(quote_sheetname(sheet_name)), 'RAG':
def write_workbook(workbook): """Write the core workbook xml.""" wb = workbook wb.rels = RelationshipList() root = WorkbookPackage() props = WorkbookProperties() # needs a mapping to the workbook for preservation if wb.code_name is not None: props.codeName = wb.code_name if wb.excel_base_date == CALENDAR_MAC_1904: props.date1904 = True root.workbookPr = props # book views active = get_active_sheet(wb) view = BookView(activeTab=active) root.bookViews =[view] # worksheets for idx, sheet in enumerate(wb._sheets, 1): sheet_node = ChildSheet(name=sheet.title, sheetId=idx, id="rId{0}".format(idx)) rel = Relationship(type=sheet._rel_type, Target=sheet.path) wb.rels.append(rel) if not sheet.sheet_state == 'visible': if len(wb._sheets) == 1: raise ValueError("The only worksheet of a workbook cannot be hidden") sheet_node.state = sheet.sheet_state root.sheets.append(sheet_node) # external references for link in wb._external_links: # need to match a counter with a workbook's relations rId = len(wb.rels) ext = ExternalReference(id="rId{0}".format(link._id)) rel = Relationship(type=link._rel_type, Target=link.path) root.externalReferences.append(ext) wb.rels.append(rel) # Defined names defined_names = copy(wb.defined_names) # don't add special defns to workbook itself. # Defined names -> autoFilter for idx, sheet in enumerate(wb.worksheets): auto_filter = sheet.auto_filter.ref if auto_filter: name = DefinedName(name='_FilterDatabase', localSheetId=idx, hidden=True) name.value = u"{0}!{1}".format(quote_sheetname(sheet.title), absolute_coordinate(auto_filter) ) defined_names.append(name) # print titles if sheet.print_titles: name = DefinedName(name="Print_Titles", localSheetId=idx) name.value = quote_sheetname(sheet.print_titles) defined_names.append(name) # print areas if sheet.print_area: name = DefinedName(name="Print_Area", localSheetId=idx) name.value = ",".join([u"{0}!{1}".format(quote_sheetname(sheet.title), r) for r in sheet.print_area]) defined_names.append(name) root.definedNames = defined_names root.calcPr = CalcProperties(calcId=124519, fullCalcOnLoad=True) return tostring(root.to_tree())
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 walk_route(self, ws, timing_lookup): """ Walk route, creating rows in table. First row will always be the OPIN, followed by the node/wire connected to the OPIN. After a node/wire is always 1 or more pips. After a pip is always a node/wire. A terminal node/wire will then reach an IPIN. """ self.row = 2 ws['A{}'.format(self.row)] = self.net['opin']['wire'] site_pin = timing_lookup.find_site_pin(self.net['opin']['node'], node_idx=0) assert isinstance(site_pin.timing, OutPinTiming) ws['B{}'.format(self.row)] = 'Outpin' ws['C{}'.format(self.row)] = site_pin.timing.drive_resistance cells = {} cells['R'] = 'C{}'.format(self.row) delays_to_cells(ws, row=self.row, delays=site_pin.timing.delays, cells=cells) model_root = Outpin(resistance=cells['R'], delays=cells_to_delays(cells)) self.models[self.row] = model_root self.row += 1 node = self.net['opin']['node'] tile, first_wire = self.net['opin']['node'].split('/') route = [r for r in self.net['route'].strip().split(' ') if r != ''] assert route[0] == '{' assert route[1] == first_wire node = self.node_name_to_node[node] current_rc_root = self.extend_rc_tree(ws, model_root, timing_lookup, node) self.descend_route(ws, timing_lookup, node['name'], route, route_idx=2, current_rc_root=current_rc_root, was_opin=True) model_root.propigate_delays(self.math) model_rows = {} for row, model in self.models.items(): model_rows[id(model)] = row for row, model in self.models.items(): rc_delay = model.get_rc_delay() if rc_delay is not None: ws['J{}'.format(row)] = self.math.eval(rc_delay) downstream_cap = model.get_downstream_cap() if downstream_cap is not None: ws['I{}'.format(row)] = self.math.eval(downstream_cap) if isinstance(model, Inpin): ipin_results = { 'Name': model.name, 'truth': {}, 'computed': {}, } ipin_delays = {} DELAY_COLS = ( ('E', 'FAST_MAX'), ('F', 'FAST_MIN'), ('G', 'SLOW_MAX'), ('H', 'SLOW_MIN'), ) for col, value in DELAY_COLS: ipin_delays[value] = [] ipin_results['computed'][ value] = '{title}!{col}{row}'.format( title=utils.quote_sheetname(ws.title), col=col, row=row + 2) ipin_results['truth'][value] = '{title}!{col}{row}'.format( title=utils.quote_sheetname(ws.title), col=col, row=row + 4) rc_delays = [] for model in model.get_delays(): delays = model.get_intrinsic_delays() if delays is not None: ipin_delays['FAST_MAX'].append(delays[FAST].max) ipin_delays['FAST_MIN'].append(delays[FAST].min) ipin_delays['SLOW_MAX'].append(delays[SLOW].max) ipin_delays['SLOW_MIN'].append(delays[SLOW].min) if id(model) in model_rows: rc_delays.append('J{}'.format(model_rows[id(model)])) ws['J{}'.format(row + 1)] = self.math.eval( self.math.sum(rc_delays)) for col, value in DELAY_COLS: ws['{}{}'.format(col, row + 1)] = self.math.eval( self.math.sum(ipin_delays[value])) ws['{}{}'.format( col, row + 2)] = '=1000*({col}{row} + J{row})'.format( col=col, row=row + 1) yield ipin_results
def test_quoted_address(sheet_name): addr = AddressCell('A2', sheet=sheet_name) assert addr.quoted_address == '{}!A2'.format(quote_sheetname(sheet_name))
def create_enrollment_templates(business_id, show_errors=False): wb = Workbook() wb.create_sheet("Student Roster (hidden)") # students populate dropdown students_ws = wb.get_sheet_by_name("Student Roster (hidden)") students_ws.sheet_state = "hidden" students_ws.cell(row=1, column=1).value = "Student Name (email)" total_students = Student.objects.business(business_id).count() for row, student in enumerate(Student.objects.business(business_id)): students_ws.cell(row=row + 2, column=1).value = ( f"{student.user.first_name} {student.user.last_name} ({student.user.email})" ) # course templates active_and_future_courses = Course.objects.business(business_id).filter( end_date__gt=datetime.now()) for course in active_and_future_courses: sheet_name = f"{course.title} - {course.id}" wb.create_sheet(sheet_name) course_ws = wb.get_sheet_by_name(sheet_name) course_ws[ "A1"].value = "Instructions: This course enrollment sheet and course details are generated from saved courses in the Omou database. Row 9 and beyond are dropdowns of students existing in the Omou database. Please select a student from the dropdown list starting from row 9 to create an enrollment for this course." course_ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=4) style_instruction_comment(course_ws["A1"]) # course info def date_format(date): return f"{date.month}/{date.day}/{date.year}" course_info = { "Course Name": course.title, "Course ID": course.id, "Instructor": f"{course.instructor.user.first_name} {course.instructor.user.last_name}", "Subject": course.course_category.name, "Course Date": f"{date_format(course.start_date)} - {date_format(course.end_date)}", "Enrollment Capacity": course.max_capacity, } thin_border = Border( left=Side(style="thin"), right=Side(style="thin"), top=Side(style="thin"), bottom=Side(style="thin"), ) background_color = PatternFill(start_color="EBFAFF", end_color="EBFAFF", fill_type="solid") for row, info_label in enumerate(course_info): info_label_cell = course_ws.cell(row=row + 2, column=1) info_value_cell = course_ws.cell(row=row + 2, column=2) def style_course_info_cell(cell): cell.border = thin_border cell.fill = background_color cell.alignment = Alignment(horizontal="left") info_label_cell.value = info_label info_label_cell.font = Font(color="1F83A2", bold=True) style_course_info_cell(info_label_cell) info_value_cell.value = course_info[info_label] info_value_cell.font = Font(color="1F83A2") style_course_info_cell(info_value_cell) # student header if show_errors: course_ws["A8"].value = "Error Message" course_ws["A8"].font = Font(bold=True) course_ws["B8"].value = "Students Enrolled" course_ws["B8"].font = Font(bold=True) else: course_ws["A8"].value = "Students Enrolled" course_ws["A8"].font = Font(bold=True) # student validation if total_students > 0: student_dv = DataValidation( type="list", formula1="{0}!$A$2:$A${1}".format( quote_sheetname("Student Roster (hidden)"), total_students), allow_blank=False, showDropDown=False, ) course_ws.add_data_validation(student_dv) student_dv.add("A9:A1048576") # remove default sheet del wb["Sheet"] # formatting for sheet_name in wb.sheetnames: sheet = wb.get_sheet_by_name(sheet_name) if sheet.sheet_state == "hidden": continue autosize_ws_columns(sheet) # bold column headers for cell in sheet["8:8"]: cell.font = Font(bold=True) # increase comment row height sheet.row_dimensions[1].height = 70 # freeze top 2 rows sheet.freeze_panes = "A9" return wb
def create_course_templates(business_id, show_errors=False): wb = Workbook() wb.create_sheet("Instructor Roster (hidden)") wb.create_sheet("Step 1 - Subject Categories") wb.create_sheet("Step 2 - Classes") # instructors populate dropdown instructors_ws = wb.get_sheet_by_name("Instructor Roster (hidden)") instructors_ws.sheet_state = "hidden" instructors_ws.cell(row=1, column=1).value = "Instructor Name (email)" total_instructors = Instructor.objects.business(business_id).count() for row, instructor in enumerate(Instructor.objects.business(business_id)): combined_value = f"{instructor.user.first_name} {instructor.user.last_name} ({instructor.user.email})" instructors_ws.cell(row=row + 2, column=1).value = combined_value # subject categories categories_ws = wb.get_sheet_by_name("Step 1 - Subject Categories") categories_ws.cell( row=1, column=1 ).value = "Instructions: Please FIRST fill out the list of subject or course category in the first column. This will become a list of subjects to fill out the required course subject field in Step 2 - Classes sheet. The first row is an example that will not be uploaded." categories_ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=2) style_instruction_comment(categories_ws["A1"]) categories_column_names = ["Subjects", "Description"] categories_example = [ "SAT Prep", "Preparational courses for students taking the SAT", ] if show_errors: categories_column_names.insert(0, "Error Message") categories_example.insert(0, "") for c in range(len(categories_column_names)): categories_ws.cell(row=2, column=c + 1).value = categories_column_names[c] categories_ws.cell(row=3, column=c + 1).value = categories_example[c] # classes course_ws = wb.get_sheet_by_name("Step 2 - Classes") course_ws.cell( row=1, column=1 ).value = "Instructions: Please fill out the course details similar to the example in row 3. You may only select subjects previous defined in Step 1 - Subject Categories. You may only select instructors that are already in the database. The first row is an example that will not be uploaded." course_ws.merge_cells(start_row=1, start_column=1, end_row=1, end_column=3) style_instruction_comment(course_ws["A1"]) course_ws.cell( row=1, column=10 ).value = 'Instructions: 5 course session slots are provided in format of "Session Day" "Start Time" and "End Time." "Session Day" is the day of week the session takes place on. If a course meets more than 5 times a week, please add additional "Session Day" "Start Time" and "End Time" columns in increasing order (e.g. "Session Day 6"). If a course only meets once, you may leave the other session slots empty.' course_ws.merge_cells(start_row=1, start_column=10, end_row=1, end_column=14) style_instruction_comment(course_ws["J1"]) class_column_names = [ "Course Name", "Instructor", "Instructor Confirmed? (Y/N)", "Subject", "Course Description", "Academic Level", "Room Location", "Total Tuition", "Enrollment Capacity (>=4)", "Start Date", "End Date", "Session Day 1", "Start Time 1", "End Time 1", "Session Day 2", "Start Time 2", "End Time 2", "Session Day 3", "Start Time 3", "End Time 3", "Session Day 4", "Start Time 4", "End Time 4", "Session Day 5", "Start Time 5", "End Time 5", ] class_example = [ "SAT Math Prep", "Daniel Wong (email)", "Y", "SAT Prep", "This is a prep course for SAT math. Open for all grade levels.", "High School", "Online", "450.0", "5", "12/1/2021", "12/30/2021", "Wednesday", "4:00 PM", "5:00 PM", "Friday", "2:00 PM", "3:00 PM", ] if show_errors: class_column_names.insert(0, "Error Message") class_example.insert(0, "") """ Validations: openpyxl bug: need to set showDropDown=False to show dropdown menu 1048576 is the last row in excel """ # date validation date_dv = DataValidation(type="date", allow_blank=False) course_ws.add_data_validation(date_dv) # day of week validation day_of_week_dv = DataValidation( type="list", formula1='"Monday,Tuesday,Wednesday,Thursday,Friday,Saturday,Sunday"', allow_blank=False, showDropDown=False, ) course_ws.add_data_validation(day_of_week_dv) # time validation time_dv = DataValidation( type="time", allow_blank=False, ) course_ws.add_data_validation(time_dv) # academic level validation academic_level_dv = DataValidation( type="list", formula1='"Elementary,Middle School,High School,College"', allow_blank=False, showDropDown=False, ) course_ws.add_data_validation(academic_level_dv) # instructor validation if total_instructors > 0: instructor_dv = DataValidation( type="list", formula1="{0}!$A$2:$A${1}".format( quote_sheetname("Instructor Roster (hidden)"), total_instructors), allow_blank=False, showDropDown=False, ) course_ws.add_data_validation(instructor_dv) # Y/N validation yes_no_dv = DataValidation(type="list", formula1='"Y,N"', allow_blank=False, showDropDown=False) course_ws.add_data_validation(yes_no_dv) # subject validation subject_formula1 = "{0}!$B$4:$B$1048576" if show_errors else "{0}!$A$4:$A$1048576" subject_dv = DataValidation( type="list", formula1=subject_formula1.format( quote_sheetname("Step 1 - Subject Categories")), allow_blank=False, showDropDown=False, ) course_ws.add_data_validation(subject_dv) # tuition validation tuition_dv = DataValidation(type="decimal", allow_blank=False, operator="greaterThan", formula1=0) course_ws.add_data_validation(tuition_dv) # capacity validation capacity_dv = DataValidation(type="whole", operator="greaterThanOrEqual", formula1=4) course_ws.add_data_validation(capacity_dv) def colnum_string(n): string = "" while n > 0: n, remainder = divmod(n - 1, 26) string = chr(65 + remainder) + string return string # populate validations for c in range(len(class_column_names)): col = colnum_string(c + 1) if class_column_names[c] == "Academic Level": academic_level_dv.add(f"{col}4:{col}1048576") if class_column_names[c] == "Instructor" and total_instructors > 0: instructor_dv.add(f"{col}4:{col}1048576") if class_column_names[c] == "Instructor Confirmed? (Y/N)": yes_no_dv.add(f"{col}4:{col}1048576") if class_column_names[c] == "Subject": subject_dv.add(f"{col}4:{col}1048576") if class_column_names[c] == "Total Tuition": tuition_dv.add(f"{col}4:{col}1048576") if class_column_names[c] == "Enrollment Capacity (>=4)": capacity_dv.add(f"{col}4:{col}1048576") if class_column_names[c] in ["Start Date", "End Date"]: date_dv.add(f"{col}4:{col}1048576") if class_column_names[c].startswith("Session Day"): day_of_week_dv.add(f"{col}4:{col}1048576") if class_column_names[c].startswith( "Start Time") or class_column_names[c].startswith("End Time"): time_dv.add(f"{col}4:{col}1048576") course_ws.cell(row=2, column=c + 1).value = class_column_names[c] for c in range(len(class_example)): course_ws.cell(row=3, column=c + 1).value = class_example[c] # remove default sheet del wb["Sheet"] # formatting for sheet_name in wb.sheetnames: sheet = wb.get_sheet_by_name(sheet_name) if sheet.sheet_state == "hidden": continue autosize_ws_columns(sheet) # bold column headers for cell in sheet["2:2"]: cell.font = Font(bold=True) # increase comment row height sheet.row_dimensions[1].height = 70 # freeze top 2 rows sheet.freeze_panes = "A3" return wb
def test_unquote_sheetname(sheet_name): assert sheet_name == unquote_sheetname(quote_sheetname(sheet_name))
def quote_sheet(sheet): if ' ' in sheet: sheet = quote_sheetname(sheet) return sheet
def sheetname(self): return quote_sheetname(self.worksheet.title)
def update_sheet(self, sheet_nm, columns, data, tf): """ Create or Update excel sheet with db data and cf. :param sheet_nm: sheet name :param columns: column names :param data: Database data that needs to be exported :param tf: Table cf. Can be None :return: """ logging.debug(f'Creating/Updating name [{sheet_nm}]') if not columns or not data: logging.error( f'[{"columns" if not columns else "data"}] required but received None' ) sheet = self._get_sheet_by_name(ws_name=sheet_nm, read=False, ws_details=tf) col_lock = False sheet.append(columns) if len(data) <= 0: logging.error(f'No values to insert for [{sheet_nm}]') sheet["$1$1"].comment = 'No data available for insert' else: for d in data: sheet.append(d) for col in columns: cf = tf.get_column(col, default=True) sheet.column_dimensions[ cf.column_number].width = cf.formatters.width if cf.formatters.comment: sheet[ f'${cf.column_number}$1'].comment = cf.formatters.comment cr = cf.formatters.get('reference', None) if cr and cf.formatters.get('dv', True): dv = DataValidation(type="list", formula1="{0}!{1}:{2}".format( quote_sheetname(cr.sheet_name), cr.startcell, cr.endcell)) dv.add('{0}2:{0}{1}'.format(cf.column_number, len(data) + 1)) sheet.add_data_validation(dv) if tf.formatters.alignment.wrapText is True: for cell in sheet[cf.column_number]: cell.alignment = Alignment(wrapText=True) if cf.formatters.locked or tf.formatters.locked: col_lock = True cell.protection = Protection(locked=True) else: cell.protection = Protection(locked=False) # Other Worksheet level settings sheet.alignment = tf.formatters.alignment sheet.freeze_panes = tf.formatters.freeze_panes sheet.add_table( openpyxl.worksheet.table.Table( ref="%s" % sheet.dimensions, displayName=sheet_nm.replace(" ", ""), tableStyleInfo=tf.formatters.table_style_info)) if tf.formatters.locked: sheet.protection.sheet = True elif col_lock: sheet.protection = SheetProtection(sheet=True, selectLockedCells=False, selectUnlockedCells=False, objects=True, scenarios=True, formatCells=True, formatRows=True, formatColumns=True, insertColumns=True, insertRows=True, insertHyperlinks=True, deleteColumns=True, deleteRows=True, sort=True, autoFilter=True, pivotTables=True, password=None) self._wb.save(self._filename) return
def write_workbook(workbook): """Write the core workbook xml.""" wb = workbook wb.rels = RelationshipList() root = WorkbookPackage() props = WorkbookProperties() # needs a mapping to the workbook for preservation if wb.code_name is not None: props.codeName = wb.code_name if wb.excel_base_date == CALENDAR_MAC_1904: props.date1904 = True root.workbookPr = props # workbook protection root.workbookProtection = wb.security # book views active = get_active_sheet(wb) if wb.views: wb.views[0].activeTab = active root.bookViews = wb.views # worksheets for idx, sheet in enumerate(wb._sheets, 1): sheet_node = ChildSheet(name=sheet.title, sheetId=idx, id="rId{0}".format(idx)) rel = Relationship(type=sheet._rel_type, Target=sheet.path) wb.rels.append(rel) if not sheet.sheet_state == 'visible': if len(wb._sheets) == 1: raise ValueError("The only worksheet of a workbook cannot be hidden") sheet_node.state = sheet.sheet_state root.sheets.append(sheet_node) # external references for link in wb._external_links: # need to match a counter with a workbook's relations rId = len(wb.rels) + 1 rel = Relationship(type=link._rel_type, Target=link.path) wb.rels.append(rel) ext = ExternalReference(id=rel.id) root.externalReferences.append(ext) # Defined names defined_names = copy(wb.defined_names) # don't add special defns to workbook itself. # Defined names -> autoFilter for idx, sheet in enumerate(wb.worksheets): auto_filter = sheet.auto_filter.ref if auto_filter: name = DefinedName(name='_FilterDatabase', localSheetId=idx, hidden=True) name.value = u"{0}!{1}".format(quote_sheetname(sheet.title), absolute_coordinate(auto_filter) ) defined_names.append(name) # print titles if sheet.print_titles: name = DefinedName(name="Print_Titles", localSheetId=idx) name.value = ",".join([u"{0}!{1}".format(quote_sheetname(sheet.title), r) for r in sheet.print_titles.split(",")]) defined_names.append(name) # print areas if sheet.print_area: name = DefinedName(name="Print_Area", localSheetId=idx) name.value = ",".join([u"{0}!{1}".format(quote_sheetname(sheet.title), r) for r in sheet.print_area]) defined_names.append(name) root.definedNames = defined_names # pivots pivot_caches = set() for pivot in wb._pivots: if pivot.cache not in pivot_caches: pivot_caches.add(pivot.cache) c = PivotCache(cacheId=pivot.cacheId) root.pivotCaches.append(c) rel = Relationship(Type=pivot.cache.rel_type, Target=pivot.cache.path) wb.rels.append(rel) c.id = rel.id wb._pivots = [] # reset root.calcPr = wb.calculation return tostring(root.to_tree())
def write_workbook(workbook): """Write the core workbook xml.""" wb = workbook wb.rels = RelationshipList() root = WorkbookPackage() props = WorkbookProperties() # needs a mapping to the workbook for preservation if wb.code_name is not None: props.codeName = wb.code_name if wb.epoch == CALENDAR_MAC_1904: props.date1904 = True root.workbookPr = props # workbook protection root.workbookProtection = wb.security # book views active = get_active_sheet(wb) if wb.views: wb.views[0].activeTab = active root.bookViews = wb.views # worksheets for idx, sheet in enumerate(wb._sheets, 1): sheet_node = ChildSheet(name=sheet.title, sheetId=idx, id="rId{0}".format(idx)) rel = Relationship(type=sheet._rel_type, Target=sheet.path) wb.rels.append(rel) if not sheet.sheet_state == 'visible': if len(wb._sheets) == 1: raise ValueError("The only worksheet of a workbook cannot be hidden") sheet_node.state = sheet.sheet_state root.sheets.append(sheet_node) # external references for link in wb._external_links: # need to match a counter with a workbook's relations rId = len(wb.rels) + 1 rel = Relationship(type=link._rel_type, Target=link.path) wb.rels.append(rel) ext = ExternalReference(id=rel.id) root.externalReferences.append(ext) # Defined names defined_names = copy(wb.defined_names) # don't add special defns to workbook itself. # Defined names -> autoFilter for idx, sheet in enumerate(wb.worksheets): auto_filter = sheet.auto_filter.ref if auto_filter: name = DefinedName(name='_FilterDatabase', localSheetId=idx, hidden=True) name.value = u"{0}!{1}".format(quote_sheetname(sheet.title), absolute_coordinate(auto_filter) ) defined_names.append(name) # print titles if sheet.print_titles: name = DefinedName(name="Print_Titles", localSheetId=idx) name.value = ",".join([u"{0}!{1}".format(quote_sheetname(sheet.title), r) for r in sheet.print_titles.split(",")]) defined_names.append(name) # print areas if sheet.print_area: name = DefinedName(name="Print_Area", localSheetId=idx) name.value = ",".join([u"{0}!{1}".format(quote_sheetname(sheet.title), r) for r in sheet.print_area]) defined_names.append(name) root.definedNames = defined_names # pivots pivot_caches = set() for pivot in wb._pivots: if pivot.cache not in pivot_caches: pivot_caches.add(pivot.cache) c = PivotCache(cacheId=pivot.cacheId) root.pivotCaches.append(c) rel = Relationship(Type=pivot.cache.rel_type, Target=pivot.cache.path) wb.rels.append(rel) c.id = rel.id wb._pivots = [] # reset root.calcPr = wb.calculation return tostring(root.to_tree())
def quoted_address(self): """requote the sheetname if going to include in formulas""" return "{}!{}".format(quote_sheetname(self.sheet), self.coordinate)