def sheet_data_generate_content(self, set_validation=False):
# self.clean_sheet(self.sheet_data, 1)
if self.request.user.is_admin(self.request):
self.generate_nodes('A')
else:
self.generate_nodes('A', True)
self.generate_ostypes('B')
self.generate_networks('C')
self.generate_storages('D')
self.generate_images('E')
# self.generate_backup('F') # Backup will be part of 2.1 or later....
# self.generate_templates('G') # We decided not to export templates
if set_validation:
# Set validation for numbers
ci = self.get_column_index('RAM')
ram_validation = DataValidation(type='whole', operator='between', formula1='32', formula2='524288')
self.sheet_dc.add_data_validation(ram_validation)
ram_validation.ranges.append(ci + '2:' + ci + '1048576')
ci = self.get_column_index('vCPU')
cpu_validation = DataValidation(type='whole', operator='between', formula1='1', formula2='64')
self.sheet_dc.add_data_validation(cpu_validation)
cpu_validation.ranges.append(ci + '2:' + ci + '1048576')
ci = self.get_column_index('HDD Size')
hdd_validation = DataValidation(type='whole', operator='between', formula1='10240', formula2='268435456')
self.sheet_dc.add_data_validation(hdd_validation)
hdd_validation.ranges.append(ci + '2:' + ci + '1048576')
def add_data_validation(self, worksheet, field, row, col):
if "enum" in field:
dv = DataValidation(type="list",
formula1='"%s"' % ",".join(field["enum"]),
allow_blank=True, errorStyle='warning')
dv.error = "Your entry is not in the list, Change anyway?"
dv.errorTitle = "Invalid Entry"
dv.prompt = "Please select from the list"
dv.promptTitle = "List Selection"
elif "type" not in field:
return
elif field["type"] == "boolean":
dv = DataValidation(type="list",
formula1='"true,false"',
allow_blank=True, errorStyle='warning')
dv.error = "Your entry is not true or false, change anyway?"
dv.errorTitle = "Invalid Entry"
dv.prompt = "Please select true or false"
dv.promptTitle = "True or False Selection"
elif field["type"] == "integer":
dv = DataValidation(type="whole",
allow_blank=True, errorStyle="warning")
dv.error = "Your entry is not an integer, change anyway?"
dv.errorTitle = "Invalid Entry"
dv.prompt = "Please provide integer"
dv.promptTitle = "Integer Selection"
else:
return
worksheet.add_data_validation(dv)
c = worksheet.cell(row=row, column=col)
dv.add(c)
def make_xls_validation(self, wb, ws_info, pg_info):
from openpyxl.utils import quote_sheetname
status_ws = wb.get_sheet_by_name('状态表'.decode('utf8'))
for ii, _ik in enumerate(sorted(self.dev_status_dict.keys()), 1):
status_ws['z%d' % ii] = self.dev_status_dict[_ik]
dv_dev_status = DataValidation(
type="list",
formula1='{0}!$z$1:$z${last_row}'.format(
quote_sheetname('状态表'.decode('utf8')),
last_row=len(self.dev_status_dict.keys())),
allow_blank=True)
dv_dev_status.sqref = 'CL6:CL30000'
ws_info.add_data_validation(dv_dev_status)
dv_apl_process = DataValidation(type="decimal",
operator="between",
formula1=0,
formula2=100)
dv_apl_process.sqref = 'CC6:CC30000'
ws_info.add_data_validation(dv_apl_process)
dv_it_result = DataValidation(type="list",
formula1='"完成, 未完成"',
allow_blank=True)
dv_it_result.sqref = 'CD6:CD30000'
ws_info.add_data_validation(dv_it_result)
dv_is_dalian = DataValidation(type="list",
formula1='"是, 否"',
allow_blank=True)
dv_is_dalian.sqref = 'BX6:BX30000'
ws_info.add_data_validation(dv_is_dalian)
def validateWS(ws, wsName):
num_row = len(ws['A'])
intDV = DataValidation(type="whole")
nameDV = DataValidation(type="textLength", operator="lessThanOrEqual", formula1=100)
emailDV = DataValidation(type="custom", formula1='ISNUMBER(MATCH("*@*.?*",D2,0))')
phoneDV = DataValidation(type="whole", operator="between", formula1=1000000000, formula2=9999999999)
dateDV = DataValidation(type="date", operator="lessThan", formula1='DATEVALUE("2021/1/1")')
if wsName == "customer":
intDV.add(f'A2:A{num_row}')
nameDV.add(f'B2:C{num_row}')
emailDV.add(f'D2:D{num_row}')
phoneDV.add(f'E2:E{num_row}')
ws.add_data_validation(intDV)
ws.add_data_validation(nameDV)
ws.add_data_validation(emailDV)
ws.add_data_validation(phoneDV)
elif wsName == "invoice":
intDV.add(f'A2:B{num_row}')
dateDV.add(f'C2:C{num_row}')
ws.add_data_validation(intDV)
ws.add_data_validation(dateDV)
elif wsName == "invoice_list":
intDV.add(f'A2:C{num_row}')
ws.add_data_validation(intDV)
elif wsName == "product":
nameDV.add(f'B2:B{num_row}')
ws.add_data_validation(nameDV)
intDV.add(f'A2:A{num_row}')
intDV.add(f'C2:C{num_row}')
ws.add_data_validation(intDV)
def format_phone_number(worksheet):
"""Formats phone number by removing extra spaces and unnecessary characters"""
for col in worksheet.iter_rows(min_row=2, min_col=column_index_from_string('O'), max_col=column_index_from_string('O')):
for cell in col:
"""Excel rounds integers longers than 15 digits hence the large value in if statement below"""
if (type(cell.value) == float) or (type(cell.value) == int and cell.value > 100000000000000):
cell.fill = PatternFill(fgColor='FDAB9F', fill_type='solid')
break
elif cell.value is not None:
phone = str(cell.value)
cell.value = phone.replace('(', '').replace('-', '').replace(')', '').replace(' ', '').replace(' ', '').\
replace('#', '').replace('.', '').replace('+','').replace('=','')
if len(cell.value) > 10:
if cell.value.startswith('1') and (
cell.offset(row=0, column=-3).value == "Canada" or cell.offset(row=0,
column=-3).value == "United States Of America"):
cell.value = cell.value.replace('1', '')
else:
for key in country_codes:
if cell.value.startswith(key):
try:
if country_codes[key] == cell.offset(row=0, column=-4).value:
cell.value = cell.value.replace(key, '')
break
except:
pass
else:
cell.fill = PatternFill(fgColor='FDAB9F', fill_type='solid')
break
if len(str(cell.value)) > 10:
cell.fill = PatternFill(fgColor='FDAB9F', fill_type='solid')
try:
cell.value = int(cell.value)
except:
pass
for cell in worksheet['P']:
if (cell.offset(row=0, column=-1).value) is None:
continue
else:
cell.value = 'H'
(cell.offset(row=0, column=1).value) = 1
# Creates a data validation (drop down) object
dv = DataValidation(type="list", formula1='"H,B,C"', allow_blank=True)
dv2 = DataValidation(type="list", formula1='"0,1"', allow_blank=True)
# Add the data-validation object to the worksheet
worksheet.add_data_validation(dv)
worksheet.add_data_validation(dv2)
dv.add('P2:P1048576')
dv2.add('Q2:Q1048576')
return 0
def _fill_customer_row(ws, row_idx, customer):
action_validation = DataValidation(
type='list',
formula1='"-,create,update"',
allow_blank=False,
)
action_validation.error = str('Action must be from list')
action_validation.errorTitle = str('Invalid action')
action_validation.prompt = str('Please choose action from list')
action_validation.promptTitle = str('List of choices')
search_criteria_validation = DataValidation(
type='list',
formula1='"-,id,external_id,external_uid"',
allow_blank=False,
)
search_criteria_validation.error = str('Search criteria must be one from list')
search_criteria_validation.errorTitle = str('Invalid search criteria')
search_criteria_validation.prompt = str('Please choose search criteria from list')
search_criteria_validation.promptTitle = str('List of choices')
ws.add_data_validation(action_validation)
ws.add_data_validation(search_criteria_validation)
ws.cell(row_idx, 1, value=customer.get('id', '-'))
ws.cell(row_idx, 2, value=customer.get('external_id', '-'))
ws.cell(row_idx, 3, value=customer.get('external_uid', '-'))
ws.cell(row_idx, 4, value='-')
ws.cell(row_idx, 5, value=customer['hub'].get('id', '-') if 'hub' in customer else '-')
ws.cell(row_idx, 6, value='id' if 'parent' in customer else '-')
ws.cell(row_idx, 7, value=customer['parent'].get('id', '-') if 'parent' in customer else '-')
ws.cell(row_idx, 8, value=customer.get('type', '-'))
ws.cell(row_idx, 9, value=customer.get('tax_id', '-'))
ws.cell(row_idx, 10, value=customer.get('name', '-'))
ws.cell(row_idx, 11, value=customer['contact_info'].get('address_line1', '-'))
ws.cell(row_idx, 12, value=customer['contact_info'].get('address_line2', '-'))
ws.cell(row_idx, 13, value=customer['contact_info'].get('city', '-'))
ws.cell(row_idx, 14, value=customer['contact_info'].get('state', '-'))
ws.cell(row_idx, 15, value=customer['contact_info'].get('zip', '-'))
ws.cell(row_idx, 16, value=customer['contact_info'].get('country', '-'))
ws.cell(row_idx, 17, value=customer['contact_info']['contact'].get('first_name', '-'))
ws.cell(row_idx, 18, value=customer['contact_info']['contact'].get('last_name', '-'))
ws.cell(row_idx, 19, value=customer['contact_info']['contact'].get('email', '-'))
ws.cell(
row_idx, 20, value=_get_phone_number(
customer['contact_info']['contact'].get(
'phone_number',
'-',
),
),
)
action_validation.add(f'D{row_idx}')
search_criteria_validation.add(f'F{row_idx}')
def format_country(worksheet):
""" Changes country format to a type that LINKS """
for cell in worksheet['L']:
if cell.value == 'CANA':
cell.value = 'Canada'
elif cell.value == 'USA' or cell.value == 'United States':
cell.value = 'United States of America'
elif cell.value == 'CAMP' or cell.value == 'Camp':
cell.value = 'Canada'
(cell.offset(row=0, column=-2).value) = 'BC'
(cell.offset(row=0, column=-3).value) = 'Vancouver'
elif cell.value in countryDictionary.keys():
cell.value = countryDictionary.get(cell.value)
if cell.value in popular_countries and cell.offset(row=0, column=-2).value is not None:
try:
(cell.offset(row=0, column=-2).value) = popular_countries[cell.value][cell.offset(row=0, column=-2).value]
except:
cell.fill = PatternFill(fgColor='FDAB9F', fill_type='solid')
cell.offset(row=0, column=-1).fill = PatternFill(fgColor='FDAB9F', fill_type='solid')
continue
"""Removes state (unecessary) if the country is European, Singapore, or Taiwan"""
if cell.value in european_countries_and_singapore and cell.offset(row=0,column=-3).value is not None:
cell.offset(row=0, column=-2).value = ''
elif cell.offset(row=0,column=-2).value is None:
cell.offset(row=0, column=-2).fill = PatternFill(fgColor='FDAB9F', fill_type='solid')
cell.offset(row=0, column=-3).fill = PatternFill(fgColor='FDAB9F', fill_type='solid')
"""Sets the address type and Address is Primary option"""
for cell in worksheet['M']:
cell.value = 'H'
(cell.offset(row=0, column=1).value) = 0
# Creates a data validation (drop down) object
dv = DataValidation(type="list", formula1='"H,B,C"', allow_blank=True)
dv2 = DataValidation(type="list", formula1='"0,1"', allow_blank=True)
# Add the data-validation object to the worksheet
worksheet.add_data_validation(dv)
worksheet.add_data_validation(dv2)
dv.add('M2:M1048576')
dv2.add('N2:N1048576')
return 0
def _dump_translations(wb, client, product_id, silent):
ws = wb.create_sheet('Translations')
_setup_ws_header(ws, 'translations')
ws.column_dimensions['F'].width = 30
ws.column_dimensions['J'].width = 15
ws.column_dimensions['K'].width = 15
rql = R().context.instance_id.eq(product_id)
translations = (client.ns('localization').translations.filter(rql))
count = translations.count()
action_validation = DataValidation(
type='list',
formula1='"-,delete,update,create"',
allow_blank=False,
)
no_action_validation = DataValidation(
type='list',
formula1='"-"',
allow_blank=False,
)
disabled_enabled = DataValidation(
type='list',
formula1='"Disabled,Enabled"',
allow_blank=False,
)
ws.add_data_validation(action_validation)
ws.add_data_validation(no_action_validation)
ws.add_data_validation(disabled_enabled)
progress = trange(0,
count,
disable=silent,
leave=True,
bar_format=DEFAULT_BAR_FORMAT)
for row_idx, translation in enumerate(translations, 2):
progress.set_description(f'Processing translation {translation["id"]}')
progress.update(1)
fill_translation_row(ws, row_idx, translation)
if translation['primary']:
no_action_validation.add(ws[f'B{row_idx}'])
else:
action_validation.add(ws[f'B{row_idx}'])
disabled_enabled.add(ws[f'I{row_idx}'])
_dump_translation_attr(wb, client, translation)
setup_locale_data_validation(wb['General Information'], ws)
progress.close()
print()
def _dump_external_static_links(ws, product, silent):
_setup_ws_header(ws, 'static_links')
row_idx = 2
count = len(product['customer_ui_settings']['download_links'])
count = count + len(product['customer_ui_settings']['documents'])
action_validation = DataValidation(
type='list',
formula1='"-,create,delete"',
allow_blank=False,
)
link_type = DataValidation(
type='list',
formula1='"Download,Documentation"',
allow_blank=False,
)
if count > 0:
ws.add_data_validation(action_validation)
ws.add_data_validation(link_type)
progress = trange(0,
count,
disable=silent,
leave=True,
bar_format=DEFAULT_BAR_FORMAT)
progress.set_description("Processing static links")
for link in product['customer_ui_settings']['download_links']:
progress.update(1)
ws.cell(row_idx, 1, value='Download')
ws.cell(row_idx, 2, value=link['title'])
ws.cell(row_idx, 3, value='-')
ws.cell(row_idx, 4, value=link['url'])
action_validation.add(f'C{row_idx}')
link_type.add(f'A{row_idx}')
row_idx += 1
for link in product['customer_ui_settings']['documents']:
progress.update(1)
ws.cell(row_idx, 1, value='Documentation')
ws.cell(row_idx, 2, value=link['title'])
ws.cell(row_idx, 3, value='-')
ws.cell(row_idx, 4, value=link['url'])
action_validation.add(f'C{row_idx}')
link_type.add(f'A{row_idx}')
row_idx += 1
progress.close()
print()
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 _alter_attributes_sheet(ws):
# Search for the 'value' column
for col_idx in range(1, ws.max_column + 1):
if ws.cell(1, col_idx).value.lower() == 'value':
new_column = col_idx
break
else:
return
# Add new 'action' column before 'value' column
fill = copy(ws.cell(1, 1).fill)
font = copy(ws.cell(1, 1).font)
insert_column_ws(ws, new_column, 20)
header_cell = ws.cell(1, new_column)
header_cell.value = 'action'
header_cell.fill = fill
header_cell.font = font
# Setup 'action' column cells
if ws.max_row > 1:
action_validation = DataValidation(type='list',
formula1='"-,update"',
allow_blank=False)
ws.add_data_validation(action_validation)
for row_idx in range(2, ws.max_row + 1):
cell = ws.cell(row_idx, new_column)
cell.alignment = Alignment(vertical='top')
cell.value = '-'
action_validation.add(cell)
# (re)set auto filter
ws.auto_filter = AutoFilter(ref=f'A:{get_column_letter(ws.max_column)}')
for col_idx in range(1, ws.max_column + 1):
ws.auto_filter.add_filter_column(col_idx, [], blank=False)
def _validate_list(cell, data, _list, blank=True, _var=None, _sh=None):
from openpyxl.worksheet.datavalidation import DataValidation
cell.value = data
dv = DataValidation(type="list", formula1='"{}"'.format(_list), allow_blank=blank)
_sh.add_data_validation(dv)
dv.add(cell)
return cell.value
def add_excel_choice(formula1, prompt, prompt_title, table, column, row):
dv = DataValidation(type='list', formula1=formula1, allow_blank=False)
dv.prompt = prompt
dv.promptTitle = prompt_title
table.add_data_validation(dv)
for i in range(2, row + 1):
dv.add(table[column + str(i)])
def __set_value(self, cell, str):
""" determine the number format of the value in str and add it to a cell
:param cell:
:param str:
:return: None
"""
str = str.strip()
if str.endswith('%'):
value = float(str[:-1]) / 100
cell.value = value
cell.number_format = '0.00%'
return
parts = str.split('-')
if len(parts) == 3:
value = v = datetime(int(parts[0]), int(parts[1]), int(parts[2]))
cell.value = value
cell.number_format = "YYYY-MM-DD"
return
if str.startswith('['):
i0 = str.find('[')
i1 = str.find(']')
list_str = str[i0 + 1:i1]
value = str[i1 + 1:]
dv = DataValidation(type="list", formula1=f'"{list_str}"', allow_blank=False)
dv.add(cell)
self.ws.add_data_validation(dv)
cell.value = value
return
cell.value = float(str)
cell.number_format = "0.00"
def write(self, data, factory, outputFilename=''):
# create worksheet
wb = Workbook()
ws = wb.active
formula = '"Profissão,Habitação,Transporte,Dependentes,Saúde,Bem-estar,Outros"'
# create data validation
dv = DataValidation(type="list", formula1=formula, allow_blank=True)
ws.add_data_validation(dv)
writerBS = factory.createWriterBankStatement()
for bs in data:
writerBS.write(bs, factory, [ws, dv])
if (outputFilename == ''):
with NamedTemporaryFile() as tmp:
wb.save(tmp.name)
tmp.seek(0)
stream = tmp.read()
wb.close()
return stream
else:
wb.save(filename=outputFilename + '.xlsx')
def test_data_validations(WriteOnlyWorksheet):
from openpyxl.worksheet.datavalidation import DataValidation
ws = WriteOnlyWorksheet
dv = DataValidation(sqref="A1")
ws.data_validations.append(dv)
ws.close()
with open(ws.filename) as src:
xml = src.read()
expected = """
<worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<sheetPr>
<outlinePr summaryRight="1" summaryBelow="1"/>
<pageSetUpPr/>
</sheetPr>
<sheetViews>
<sheetView workbookViewId="0">
<selection sqref="A1" activeCell="A1"/>
</sheetView>
</sheetViews>
<sheetFormatPr baseColWidth="8" defaultRowHeight="15"/>
<sheetData />
<dataValidations count="1">
<dataValidation allowBlank="0" showErrorMessage="1" showInputMessage="1" sqref="A1" />
</dataValidations>
</worksheet>"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def __init__(self,
header=None,
choices=None,
list_validation=None,
**kwargs):
self.choices = tuple(choices) if choices else None
self.list_validation = list_validation
self.to_excel_map = {
internal: excel
for internal, excel in self.choices
}
self.from_excel_map = {
excel: internal
for internal, excel in self.choices
}
# Setup maps before super().__init__() to validation of default value.
super(ChoiceColumn, self).__init__(header=header, **kwargs)
if self.list_validation and not self.data_validation:
self.data_validation = DataValidation(
type="list",
formula1="\"%s\"" %
",".join('%s' % str(excel)
for internal, excel in self.choices))
def _set_validation_(data_sheet: openpyxl.workbook.workbook.Worksheet,
idx: str,
vsheet,
is_global=False):
"""
Sets the validation for data_sheet, limiting values to those found in column A of vsheet
:param data_sheet: The data sheet to which validation is to be applied
:param idx: the column letter (if is_global=False) or row number (if is_global=True) of the cells to validate
:param vsheet: The worksheet from which valid values are to be drawn (Drawn from column A)
:param is_global: Whether data_sheet contains global data. Default False
:return:
"""
validation_title = vsheet.title
# This DataValidation object specifies that a list rule is to be used,
# drawing its values from column A of vsheet, and allowing null values
dv = DataValidation(
type="list",
formula1=
f"{quote_sheetname(validation_title)}!$A$2:$A${vsheet.max_row}",
allow_blank=True)
# Cell address needs to be different for globals vs utterances
# For globals, each row's B cell has it's own validation rule
# For utterances, each column that has codes has its own validation rule.
# Set cell address accordingly.
column = idx if not is_global else "B"
cell_address = f"B{idx}" if is_global else f"{column}2:{column}{data_sheet.max_row}"
dv.add(cell_address)
data_sheet.add_data_validation(dv)
def _dump_templates(ws, client, product_id, silent):
_setup_ws_header(ws, 'templates')
row_idx = 2
action_validation = DataValidation(
type='list',
formula1='"-,create,update,delete"',
allow_blank=False,
)
scope_validation = DataValidation(
type='list',
formula1='"asset,tier1,tier2"',
allow_blank=False,
)
type_validation = DataValidation(
type='list',
formula1='"fulfillment,inquire"',
allow_blank=False,
)
templates = client.products[product_id].templates.all()
count = templates.count()
if count > 0:
ws.add_data_validation(action_validation)
ws.add_data_validation(scope_validation)
ws.add_data_validation(type_validation)
progress = trange(0,
count,
disable=silent,
leave=True,
bar_format=DEFAULT_BAR_FORMAT)
for template in templates:
progress.set_description(f'Processing template {template["id"]}')
progress.update(1)
_fill_template_row(ws, row_idx, template)
action_validation.add(f'C{row_idx}')
scope_validation.add(f'D{row_idx}')
type_validation.add(f'E{row_idx}')
row_idx += 1
progress.close()
print()
def update_budget_xls(self, budget_ids, template_id=None):
if not template_id:
raise ValidationError(_('Please add .xlsx template.'))
if not budget_ids:
raise ValidationError(_('No budget to export.'))
budgets = self.env['budget.plan.unit'].browse(budget_ids)
for budget in budgets:
exp_file = self.attachment_id.datas.decode('base64')
stream = cStringIO.StringIO(exp_file)
workbook = openpyxl.load_workbook(stream)
ChargeType = DataValidation(type="list",
formula1='"External,Internal"')
SHEET_FORMULAS.update({'charge_type': ChargeType})
self._update_activity_group_sheet(workbook)
self._update_job_order_sheet(workbook)
self._update_non_joborder_sheets(budget, workbook)
stream1 = cStringIO.StringIO()
workbook.save(stream1)
filename = self._get_filename(budget)
self.env.cr.execute(""" DELETE FROM budget_xls_output """)
attachement_id = self.env['ir.attachment'].create({
'name':
filename,
'datas':
stream1.getvalue().encode('base64'),
'datas_fname':
filename,
'res_model':
'budget.plan.unit',
'res_id':
budget.id,
'budget_plan_id':
budget.id,
'description':
'Export',
})
attach_id = self.env['budget.xls.output'].create({
'name':
filename,
'xls_output':
base64.encodestring(stream1.getvalue()),
})
self.env['budget.plan.history'].create({
'user_id':
self.env.user.id,
'operation_date':
fields.Datetime.now(),
'operation_type':
'export',
'plan_id':
budget.id,
'attachement_id':
attachement_id.id
})
return attach_id
def _add_validation(worksheet):
operations = [""] + VALID_OPERATIONS + OPERATIONS_TO_IGNORE
operation_data_validation = DataValidation(type="list", formula1='"%s"' % (','.join(operations)))
worksheet.add_data_validation(operation_data_validation)
for header_cell in worksheet[1]:
if header_cell.value == OPERATION_COLUMN:
letter = header_cell.column_letter
operation_data_validation.add(f"{letter}2:{letter}{worksheet.max_row}")
def categorize_emails(worksheet, chosen_column, datavalidation_location1, datavalidation_location2):
"""Looking through the email column R, determines email category and marks column next to it accordingly"""
"""A is Alumni, H Home, and B Business. Anything not categorized is highlighted yellow in the target worksheet"""
for cell in worksheet[chosen_column]:
cell.offset(row=0, column=2).value = 0
if cell.value is None:
cell.offset(row=0, column=2).value = None
continue
elif cell.value.endswith('alumni.ubc.ca'):
(cell.offset(row=0, column=1).value) = 'A'
elif cell.value.lower().endswith(hometuple):
(cell.offset(row=0, column=1).value) = 'H'
elif cell.value.lower().endswith(businesstuple):
(cell.offset(row=0, column=1).value) = 'B'
elif "@alumni" in cell.value:
(cell.offset(row=0, column=1).value) = 'H'
else:
try:
last_name_email = worksheet.cell(row=cell.row, column=column_index_from_string('D')).value.lower() + ".ca"
last_name_email_USA = worksheet.cell(row=cell.row,
column=column_index_from_string('D')).value.lower() + ".com"
last_name_email_USA2 = worksheet.cell(row=cell.row,
column=column_index_from_string('D')).value.lower() + ".us"
# print(last_name_email)
if cell.value.endswith(last_name_email) or cell.value.endswith(last_name_email_USA) or cell.value.endswith(last_name_email_USA2) :
(cell.offset(row=0, column=1).value) = 'H'
else:
cell.fill = PatternFill(fgColor='FFFF33', fill_type = 'solid')
(cell.offset(row=0, column=1).value) = 'B'
except:
cell.fill = PatternFill(fgColor='FFFF33', fill_type = 'solid')
(cell.offset(row=0, column=1).value) = 'B'
# Creates a data validation (drop down) object
dv = DataValidation(type="list", formula1='"H,B,O,A"', allow_blank=True)
dv2 = DataValidation(type="list", formula1='"0,1"', allow_blank=True)
# Add the data-validation object to the worksheet
worksheet.add_data_validation(dv)
worksheet.add_data_validation(dv2)
dv.add(datavalidation_location1)
dv2.add(datavalidation_location2)
return 0
def _validate_list(cell, data, _list, blank=True, _var=None, _sh=None):
cell.value = data
dv = DataValidation(type="list",
formula1='"{}"'.format(_list),
allow_blank=blank)
_sh.add_data_validation(dv)
dv.add(cell)
return cell.value
def generate_input_form(db: Db, path):
# retrieve data
db_d = dict()
for table in db:
table_d = dict()
db_d[table.get_ref()] = table_d
schema = table._dev_schema
for k, v in schema.declared_fields.items():
field_d = dict()
table_d[k] = field_d
choices = None
if getattr(v, "validate", None) is not None and isinstance(
v.validate, validate.OneOf):
choices = v.validate.choices
field_d["choices"] = choices
wb = Workbook()
wb.remove_sheet(wb.active) # remove default sheet
# choices worksheet
ws = wb.create_sheet("choices")
ws.cell(1, 1).value = "Table ref"
ws.cell(1, 2).value = "Var name"
ws.cell(1, 3).value = "Choices"
# initialize and loop
i = 1
choices_d = dict() # (table_ref, field_name): range
for table_ref, table_data in db_d.items():
for field_name, field_d in table_data.items():
if field_d["choices"] is not None:
i += 1
ws.cell(i, 1).value = table_ref
ws.cell(i, 2).value = field_name
choices_nb = len(field_d["choices"])
for k in range(choices_nb):
ws.cell(i, 3 + k).value = field_d["choices"][k]
choices_d[(
table_ref, field_name
)] = f"choices!$C${i}:${get_column_letter(choices_nb + 3)}${i}"
# table worksheets
for table_ref, table_data in db_d.items():
ws = wb.create_sheet(table_ref)
for i, (field_name, field_data) in enumerate(table_data.items()):
ws.cell(i + 1, 1).value = field_name
if field_data["choices"] is not None:
dv = DataValidation(type="list",
formula1=choices_d[(table_ref,
field_name)],
allow_blank=True)
ws.add_data_validation(dv)
dv.add(ws.cell(i + 1, 2))
ws.cell(i + 1, 2).fill = dropdown_fill
# save
wb.save(path)
def dataval_any(ws, range):
# Create a data-validation object with list validation
dv = DataValidation(type='date')
# Add the data-validation object to the worksheet
ws.add_data_validation(dv)
# Or, apply the validation to a range of cells
dv.ranges.append(range)
def setDropDown(ws, pullTab=None, colName=None, colStr=None, fm1=None):
if fm1 == None:
s = ",".join(pullTab[colName].dropna().unique())
fm1 = f'"{s}"'
dv = DataValidation(type="list", formula1=fm1)
dv.ranges = f'{colStr}3:{colStr}1048576'
ws.add_data_validation(dv)
return (ws)
def add_validation(self, validation_string):
self.sheet.insert_rows(1)
dv = DataValidation(type="list",
formula1=validation_string,
allow_blank=True)
self.sheet.add_data_validation(dv)
cell = self.sheet["A1"]
#cell.value = "Dog"
dv.add(cell)
def make_xls_validation(self, ws, row_count, pg):
# 迁入开发分类
it_status_list = self.get_it_status_list(pg)
status_str = '"%s"' % ', '.join(it_status_list)
dv_it_result = DataValidation(type="list",
formula1=status_str,
allow_blank=True)
dv_it_result.sqref = 'J%s:J%s' % (HMI_START_ROW_NUM,
row_count + HMI_START_ROW_NUM)
ws.add_data_validation(dv_it_result)
# 后续画面开发分类
it_followup_status_list = self.get_it_followup_status_list(pg)
followup_status_str = '"%s"' % ', '.join(it_followup_status_list)
dv_is_dalian = DataValidation(type="list",
formula1=followup_status_str,
allow_blank=True)
dv_is_dalian.sqref = 'L%s:L%s' % (HMI_START_ROW_NUM,
row_count + HMI_START_ROW_NUM)
ws.add_data_validation(dv_is_dalian)
def _create_tref_dv(_tref, allow_blank=True):
# Stringify values and create data-validation object
dv = DataValidation(type="list", formula1=_tref, allow_blank=allow_blank)
# Optionally set a custom error message
dv.error = 'Entry not in the list'
dv.errorTitle = 'Invalid Entry'
return dv
def add_validation(self, column, no_rows, validation_column):
"""
Function that creates excel validation, based on columns in Data sheet and applies it in Datacenter sheet
"""
# Define validation column with number of rows
dv_str = '=Data!$' + column + '$2:$' + column + '$' + str(no_rows + 25)
dv = DataValidation('list', formula1=dv_str, allow_blank=True)
self.sheet_dc.add_data_validation(dv)
# Apply newly created validation in Datacenter sheet to validate against generated values
dv.ranges.append(str(validation_column) + '3:' + str(validation_column) + '1048576')
