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 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 test_quoted_address(sheet_name):
addr = AddressCell('A2', sheet=sheet_name)
assert addr.quoted_address == '{}!A2'.format(quote_sheetname(sheet_name))
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 sheetname(self):
return quote_sheetname(self.worksheet.title)
def quoted_address(self):
"""requote the sheetname if going to include in formulas"""
return "{}!{}".format(quote_sheetname(self.sheet), self.coordinate)