Beispiel #1
0
 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)
Beispiel #2
0
    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
Beispiel #5
0
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)
Beispiel #7
0
    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':
Beispiel #8
0
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
Beispiel #11
0
def test_quoted_address(sheet_name):
    addr = AddressCell('A2', sheet=sheet_name)
    assert addr.quoted_address == '{}!A2'.format(quote_sheetname(sheet_name))
Beispiel #12
0
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
Beispiel #13
0
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
Beispiel #14
0
def test_unquote_sheetname(sheet_name):
    assert sheet_name == unquote_sheetname(quote_sheetname(sheet_name))
Beispiel #15
0
def test_unquote_sheetname(sheet_name):
    assert sheet_name == unquote_sheetname(quote_sheetname(sheet_name))
Beispiel #16
0
 def quote_sheet(sheet):
     if ' ' in sheet:
         sheet = quote_sheetname(sheet)
     return sheet
Beispiel #17
0
def test_quoted_address(sheet_name):
    addr = AddressCell('A2', sheet=sheet_name)
    assert addr.quoted_address == '{}!A2'.format(quote_sheetname(sheet_name))
Beispiel #18
0
 def sheetname(self):
     return quote_sheetname(self.worksheet.title)
Beispiel #19
0
    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
Beispiel #20
0
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())
Beispiel #21
0
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())
Beispiel #22
0
 def sheetname(self):
     return quote_sheetname(self.worksheet.title)
Beispiel #23
0
 def quoted_address(self):
     """requote the sheetname if going to include in formulas"""
     return "{}!{}".format(quote_sheetname(self.sheet), self.coordinate)