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 _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 _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 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 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 _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 __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 _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 _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 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 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 create_excel(path, rows=1000, sheet_name="Sheet1"):
if len(path) > 3:
if path[-5:] == ".xlsx":
excel_path = path
else:
log.error("文件格式不正确")
return 0
else:
log.error("文件不正确")
return 0
wb = Workbook()
# wb = load_workbook(path)
wb.active
sheet = wb.create_sheet(sheet_name, 0)
sheet2 = wb.create_sheet("系统数据", 1)
# sheet = wb["Sheet1"]
titles = ["用例标题", "页面操作", "元素路径", "输入内容", "其他说明"]
types = [
"get", "input_text", "assertElementisExist", "assertText",
"assertElementText", "wait", "wait_elements", "refresh", "click",
"slow_click"
]
# sheet2=wb["Sheet2"]
for k, t in enumerate(types):
sheet2["A{}".format(k + 1)].value = t
border = Border(left=Side(border_style="thin", color="00000000"),
right=Side(border_style="thin", color="00000000"),
top=Side(border_style="thin", color="00000000"),
bottom=Side(border_style="thin", color="00000000"))
fill = PatternFill("solid", fgColor="4472C4")
font = Font(name="等线", size=11, bold=False, italic=False, color='00FFFFFF')
for k, title in enumerate(titles):
col = chr(ord("A") + k) + "1"
cell = sheet[col]
cell.border = border
cell.fill = fill
cell.font = font
cell.value = title
sheet.column_dimensions[chr(ord("A") + k)].width = 22
sheet.row_dimensions[1].height = 22
dv = DataValidation(type="list",
formula1="{0}!$A$1:$A${1}".format(
quote_sheetname("系统数据"), len(types)),
allow_blank=True)
sheet.add_data_validation(dv)
row = 2
for _ in range(rows):
dv.add(sheet["B{}".format(row)])
row += 1
wb.save(excel_path)
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 _alter_general_sheet(ws):
for row_idx in range(1, ws.max_row + 1): # pragma: no branch
if ws.cell(row_idx, 1).value.lower() == 'auto-translation':
disabled_enabled = DataValidation(
type='list',
formula1='"Disabled,Enabled"',
allow_blank=False,
)
ws.add_data_validation(disabled_enabled)
disabled_enabled.add(ws.cell(row_idx, 2))
break
def makeDuplicateCheck(column_letter, sheet):
# an excel function to check for duplicates
# https://www.ablebits.com/office-addins-blog/2013/10/20/prevent-duplicates-in-excel-column/
formula = f"=(COUNTIF(${column_letter}:${column_letter}, {column_letter}2) = 1)"
# ensure ids are greater than zero
formula += f"AND(=${column_letter} > 0)"
_range = f"{column_letter}2:{column_letter}{max_rows}"
rule = DataValidation(type="custom", formula1=formula)
rule.add(_range)
sheet.add_data_validation(rule)
def _set_global_validation_(data_sheet: openpyxl.workbook.workbook.Worksheet,
row_number: int, vsheet):
validation_title = vsheet.title
dv = DataValidation(
type="list",
formula1=
f"{quote_sheetname(validation_title)}!$A$2:$A${vsheet.max_row}",
allow_blank=True)
dv.add(f"B{row_number}")
data_sheet.add_data_validation(dv)
def main():
cm.sheet.insert_rows(1)
dv = DataValidation(type="list",
formula1='"alex,hannah,frog"',
allow_blank=True)
cm.sheet.add_data_validation(dv)
c1 = cm.sheet["A1"]
c1.value = "Dog"
dv.add(c1)
cm.save_doc(FILE_PATH)
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 create_size_configure_excel(conf_path, order, size):
headers = ('order_no', 'size_label', 'num_id', 'name_index', 'fold',
'unit', 'check', 'std_val', 'tol_lower', 'tol_upper', 'offset')
with open(conf_path, encoding='utf-8') as f:
content = yaml.safe_load(f.read())
result = []
steps = content.get('steps')
for s in size:
for i in steps:
std_val_map = {
k: v.get("val")
for k, v in i.get("std_val").items()
} if i.get("std_val") else {}
std_val_list = list(std_val_map.keys())
measure_list = list(
i.get('measure').keys()) if i.get('measure') else []
diff_list = list(i.get('diff').keys()) if i.get('diff') else []
num_id_list = set(std_val_list + measure_list + diff_list)
dataset = [(order, s, i, '01', '', 'Inch', '', std_val_map.get(i),
'', '', '') for i in num_id_list]
result += dataset
data = tablib.Dataset(*result, headers=headers, title="sheet1")
open("2.xxxx.xlsx", "wb").write(data.xlsx)
# 继续对表格进行处理
excel = load_workbook("2.xxxx.xlsx")
table = excel.get_sheet_by_name("sheet1")
row = table.max_row
dv = DataValidation(type='list',
formula1='"Inch", "cm"',
allow_blank=False)
dv.prompt = "选择单位"
dv.promptTitle = 'List Selection'
table.add_data_validation(dv)
for i in range(2, row + 1):
dv.add(table["F" + str(i)])
# 添加单元格颜色
title = ["A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K"]
for i in range(2, row + 1):
for t in title:
table[t + "1"].fill = greyfill
table[t + "1"].border = border
if table["B" + str(i)].value in size and size.index(
table["B" + str(i)].value) % 2 == 0:
table[t + str(i)].fill = redfill
elif table["B" + str(i)].value in size and size.index(
table["B" + str(i)].value) % 2 == 1:
table[t + str(i)].fill = bluefill
table[t + str(i)].border = border
excel.save("2.xxxx.xlsx")
def generate_spreadsheet(self, validated_params, template_name):
spreadsheet_info = {}
output_dir = os.path.join(
self.scratch_dir,
'sample_spreadsheet_' + str(int(time.time() * 10000)))
output_filename = validated_params['output_name'] + '.xlsx'
output_filepath = os.path.join(output_dir, output_filename)
os.makedirs(output_dir)
_column = _COLUMNS[template_name]
common_font = Font(name='Arial', size=9)
highlight_font = Font(name='Arial', size=9, bold=True)
header_fill = PatternFill("solid", fgColor="D1E5FE")
wb = Workbook()
ws = wb.active
ws.title = "samples"
ws.page_setup.fitToWidth = 1
ws['A1'] = 'Object Type:'
ws['A1'].font = highlight_font
ws['B1'] = validated_params['object_type']
ws['B1'].font = common_font
ws['C1'] = 'User Code:'
ws['C1'].font = highlight_font
ws['D1'] = validated_params['user_code']
ws['D1'].font = common_font
for c in range(1, len(_column) + 1):
_col = _column[c - 1]
cell = ws.cell(row=2, column=c, value=_col['header'])
cell.fill = header_fill
cell.font = highlight_font
if 'type' in _col and _col['type']:
cl = get_column_letter(c)
dv = DataValidation(type=_col['type'], allow_blank=True)
dv.error = 'Your entry is not ' + _col['type']
dv.errorTitle = 'Invalid Entry'
ws.add_data_validation(dv)
dv.add(cl + '3:' + cl + '1048576')
wb.save(filename=output_filepath)
spreadsheet_info['output_dir'] = output_dir
spreadsheet_info['output_file'] = [{
'path': output_filepath,
'name': output_filename,
'label': output_filename,
'description': 'Sample spreadsheet'
}]
return spreadsheet_info
def create_source_column(first_file, destws):
for cell in destws['V']:
cell.value = "Ruffalo Cody"
dv = DataValidation(type="list", formula1='"Registrar: SIS Import, Ruffalo Cody"', allow_blank=True)
# Add the data-validation object to the worksheet
destws.add_data_validation(dv)
dv.add('V2:V1048576')
return 0
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 custom_action_type(self):
"""
[メソッド概要]
アクション部のバリデーション
アクション種別のセルに使用可能なアクション種別のリストを作成する。
<builtin_formats 参照>
https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/styles/numbers.html
"""
tables_ws = self.wb['Tables']
header_row = tables_ws[11]
target_header_text = 'アクション種別\n（必須）'
action_type_col = 0
for cell in header_row:
if cell.col_idx == 1:
# A列は無視
continue
if cell.value == target_header_text:
# hitしたら終了
action_type_col = cell.col_idx
break
if cell.value is None or cell.value == '':
# 空白を終端とする
break
if action_type_col == 0:
# hitできずに来たらエラー
raise Exception('No match action type col.')
dti = ActionType.objects.filter(disuse_flag='0').values_list(
'driver_type_id', flat=True)
rs = DriverType.objects.filter(driver_type_id__in=dti).values(
'name', 'driver_major_version')
ati = []
for r in rs:
name_version = r['name'] + '(ver' + str(
r['driver_major_version']) + ')'
ati.append(name_version)
acts = ','.join(ati)
acts_type_dv = DataValidation(type="list",
formula1='"{}"'.format(acts))
for r in range(12, 19):
acts_type_dv.add(tables_ws.cell(row=r, column=action_type_col))
for data_validation in tables_ws.data_validations.dataValidation:
# TODO 1シート内に入力規則が複数来たらバグる
tables_ws.data_validations.dataValidation.remove(data_validation)
tables_ws.add_data_validation(acts_type_dv)
def main():
filename = 'MadSkillz.xlsx'
role_pattern = '^## .+'
role_description_pattern = '>.+'
behaviour_pattern = '^- .+'
behaviour_description_pattern = '^#### .+'
row_number = 0
wb = Workbook()
active_ws = wb.active
data_validation = DataValidation(
type="list",
formula1=
'"Cannot Assess,Need Coaching,Maturing,No Brainer,Outstanding"',
allow_blank=False)
f = import_file_from_readify_github()
logging.info('Parsing...')
for line in f:
line = line.decode('unicode-escape')
row_number += 1
role = re.search(role_pattern, line)
if role:
active_ws = wb.create_sheet(0)
row_number = 1
active_ws.add_data_validation(data_validation)
active_ws.title = line.strip('#')
line = line.strip('#')
change_cell_value(row_number, 1, active_ws, line)
change_cell_style_header(row_number, 1, active_ws)
change_cell_style_header(row_number, 2, active_ws)
change_cell_value(row_number, 2, active_ws, "Rating")
behaviour = re.search(behaviour_pattern, line)
if behaviour:
change_cell_value(row_number, 1, active_ws, line)
rating_cell = active_ws.cell(row=row_number, column=2)
data_validation.add(rating_cell)
role_description = re.search(role_description_pattern, line)
if role_description:
line = line.strip('>')
change_cell_value(row_number, 1, active_ws, line)
change_cell_style_role_description(row_number, 1, active_ws)
c = active_ws.cell(row=row_number, column=1)
c.value = line
logging.info('Parsing...Done!')
wb.save(filename)
logging.info('Saved "%s"', filename)
def addDataValidation(worksheet: Worksheet, cellFormulaDict: dict):
"""Adds data validation to the specified worksheet
on the listed cells mapped to the formula.
Arguments:
worksheet {Worksheet} -- [description]
cellFormulaDict {dict} -- Cell address is the key and the formula is the value
"""
if cellFormulaDict[CELL_IDENTIFIER["taskName"]] is not None:
taskDataValidation = DataValidation(
type="list",
formula1=cellFormulaDict[CELL_IDENTIFIER["taskName"]],
allow_blank=False)
worksheet.add_data_validation(taskDataValidation)
taskDataValidation.add(CELL_IDENTIFIER["taskName"])
def set_applicable_experiments_in_xls(
applicable_experiments, spreadsheet, aggregate_ws_name):
"""Write drop-down list of applicable experiments into aggregate tabs."""
appl_exps_as_comma_delim_string = ", ".join(applicable_experiments)
# Note this requires string enclosed in quotes internally
cell_formula = '"{}"'.format(appl_exps_as_comma_delim_string)
aggregate_ws = spreadsheet[aggregate_ws_name]
# Clear the placeholder value in the answer cell
aggregate_ws["B27"].value = None
# Add the drop-down with the available options to that cell
appl_exps_dropdown = DataValidation(
type="list", formula1=cell_formula, allow_blank=True)
aggregate_ws.add_data_validation(appl_exps_dropdown)
appl_exps_dropdown.add("B27")
def add_validation(self, validation_string):
'''
Inserts a new row then adds a data validation drop down list to cell 'A1'.
'validation_string' parameter contains list of values added to drop down list.
'''
try:
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"]
dv.add(cell)
error_logger.logger('Drop down menu added')
except Exception as traceback_error:
statement = "Trouble with adding validation drop-down menu"
error_logger.logger(statement, traceback_error)
def applyValidationToCell(self, ws, startingRow, nokiaAttr):
#get Validation rule for this cell
validationRule = self.validationRules[nokiaAttr]
if "list:" in validationRule:
validationString = self.getValidationAsString(validationRule)
str = '"' + validationString + '"'
dv = DataValidation(type="list",
formula1=str,
allow_blank=False,
showDropDown=False)
dv.error = 'Your entry is not as per the validation rule'
dv.errorTitle = 'Invalid Entry'
ws.add_data_validation(dv)
cell = ws.cell(column=self.cols[nokiaAttr] + 1, row=startingRow)
dv.add(cell)
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 create_dropdown_cols(self):
from openpyxl import utils
from openpyxl.worksheet.datavalidation import DataValidation
idx = 1
for x in self.columns:
if x.dropdown != None:
dv = DataValidation(type="list",
formula1=x.dropdown.name + '!$A:$A',
allow_blank=True)
data_len = list(self.ws.rows).__len__()
address_col = utils.get_column_letter(
idx) + "2:" + utils.get_column_letter(idx) + "1048576"
print address_col
dv.add(address_col)
self.ws.add_data_validation(dv)
idx = idx + 1
return self
def exportar_planilha_importacao_usuarios_perfil_codae(request, **kwargs):
response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename=planilha_importacao_usuarios_perfil_CODAE.xlsx'
workbook: Workbook = Workbook()
ws = workbook.active
ws.title = 'CODAE'
headers = [
'Nome do Usuário',
'Cargo',
'Email',
'CPF',
'Telefone',
'RF',
'Perfil',
'Número CRN'
]
_font = styles.Font(name='Calibri', sz=10)
{k: setattr(styles.DEFAULT_FONT, k, v) for k, v in _font.__dict__.items()}
for i in range(0, len(headers)):
cabecalho = ws.cell(row=1, column=1 + i, value=headers[i])
cabecalho.fill = styles.PatternFill('solid', fgColor='ffff99')
cabecalho.font = styles.Font(name='Calibri', size=10, bold=True)
cabecalho.border = styles.Border(
left=styles.Side(border_style='thin', color='000000'),
right=styles.Side(border_style='thin', color='000000'),
top=styles.Side(border_style='thin', color='000000'),
bottom=styles.Side(border_style='thin', color='000000')
)
dv = DataValidation(
type='list',
formula1='"COORDENADOR_GESTAO_ALIMENTACAO_TERCEIRIZADA,'
'COORDENADOR_DIETA_ESPECIAL,'
'COORDENADOR_SUPERVISAO_NUTRICAO,'
'COORDENADOR_GESTAO_PRODUTO"',
allow_blank=True
)
dv.error = 'Perfil Inválido'
dv.errorTitle = 'Perfil não permitido'
ws.add_data_validation(dv)
dv.add('G2:G1048576')
workbook.save(response)
return response
def call(request):
token_key = request._get_get().get("tk")
token_data = manager.get_parameter(request, token_key)
if token_data.get('data', None) is None:
return HttpResponse(content= "<h3>" + token_data["error"] + "</h3>", mimetype='text/html')
collection_name = token_data["data"]["collection_name"]
wb = Workbook()
ws = wb.active
#ws["A1"].value = "TEST"
ws.title = KEY.WS_PREFIX_NAME + "1";
#add sheet mapping
wb.create_sheet(KEY.WS_MAPPING_SHEET);
ws_mapping = wb[KEY.WS_MAPPING_SHEET];
#add sheet init data
wb.create_sheet(KEY.WS_INIT_DATA);
ws_init = wb[KEY.WS_INIT_DATA];
#Get column config
ret_columns = db.get_collection("HCSSYS_CollectionInfo").aggregate([
{
'$match': {
'$and' : [
{'parent_field' : None},
{'field_path': {'$regex': '^,' + collection_name + ','}},
{'is_parent' : False},
]
}
},
{'$project': {'_id': 0}}
]);
columns = list(ret_columns)
#Get export data
ret_data = db.get_collection(collection_name).find({})
data_items = list(ret_data);
if(len(data_items) == 0):
raise "Collection '" + collection_name + "' has not been declared in the database"
return null;
ref_accessmode = [{
'value':1,
'caption': u'Toàn quyền'
},{
'value': 2,
'caption': u'Từ vùng được chọn đến các cấp con'
},{
'value': 3,
'caption': u'Chỉ trên vùng được chọn'
}]
#create reference sheet
ref_columns = [a for a in columns if a["field_name"] == "access_mode"]
for col in ref_columns:
wb.create_sheet(KEY.WS_PREFIX_REFERENCE + col["field_name"])
ws_ref = wb[KEY.WS_PREFIX_REFERENCE + col["field_name"]]
for iData, vData in enumerate(ref_accessmode):
ws_ref.cell(row=iData+1, column=1).value = vData["value"]
ws_ref.cell(row=iData+1, column=2).value = vData["caption"]
ws_ref.cell(row=iData+1, column=3).value = vData["value"]
#create header title
header_fields = []
for c in columns:
if(c["field_name"] == 'access_mode'):
header_fields.append({
'field_name': c["field_name"],
'key' : KEY.PREFIX_INIT,
'display_name': c["description"],
'is_ref' : True,
'is_data_field' : False,
'is_hidden' : True
})
header_fields.append({
'field_name': c["field_name"],
'key' : KEY.PREFIX_REF,
'display_name': c["description"],
'is_ref' : True,#List of reference data
'is_data_field' : False,
'is_hidden' : False
})
header_fields.append({
'field_name': c["field_name"],
'key' : c["field_name"],
'display_name': c["description"],
'is_ref' : True,
'is_data_field' : True, #data_field in database
'is_hidden' : True
})
else:
header_fields.append({
'field_name': c["field_name"],
'key' : c["field_name"],
'display_name': c["description"],
'is_ref' : False,
'is_data_field' : True,
'is_hidden' : False
})
#Create header worksheet (row 1)
idx_mapping_row_column = 1
ws_mapping.cell(row=1, column=1).value = KEY.BEGIN_MAPPING
ws_mapping.cell(row=1, column=2).value = collection_name
ws_mapping.cell(row=1, column=3).value = ws.title
idx_mapping_row_column += 1
idx_init_column = 0
for iCol, vCol in enumerate(header_fields):
if not (vCol["key"] == KEY.PREFIX_INIT):
cell = ws.cell(row=1, column=iCol + 1 - idx_init_column)
cell.value = vCol["display_name"]
ws.column_dimensions[cell.column].width = len(vCol["display_name"]) if len(vCol["display_name"]) > 20 else 20 #20 characters
if(vCol["is_hidden"]):
ws.column_dimensions[cell.column].hidden = True
if(vCol["is_data_field"] == True):
#create mapping data
ws_mapping.cell(row=idx_mapping_row_column, column=1).value = cell.column
ws_mapping.cell(row=idx_mapping_row_column, column=2).value = vCol["field_name"]
idx_mapping_row_column += 1
else:
cell = ws_init.cell(row=1, column=idx_init_column + 1)
cell.value = vCol["field_name"]
idx_init_column += 1
ws_mapping.cell(row=idx_mapping_row_column, column=1).value = KEY.END_MAPPING
#Render content to worksheet
#if (len(data_items) > 0):
for iItem, vItem in enumerate(data_items):
num_init_column = 0
for iCol, vCol in enumerate(header_fields):
idx_col = iCol - num_init_column
curr_cell = ws.cell(row = iItem + 2, column = idx_col + 1)
if (vCol["is_data_field"] and not vCol["is_ref"]) :
curr_cell.value = vItem[vCol["field_name"]]
elif (vCol["is_ref"] and vCol["key"] == KEY.PREFIX_INIT):
init_cell = ws_init.cell(row = iItem + 2, column = num_init_column + 1)
init_cell.value = vItem[vCol["field_name"]]
num_init_column +=1
elif (vCol["is_ref"] and vCol["key"] == KEY.PREFIX_REF):
#curr_cell: value list
#vlookup column 1-2 in reference data
ws_ref = wb[KEY.WS_PREFIX_REFERENCE + col["field_name"]]
#ref_beg_cell = ws_ref.cell(row=1, column=1)
ref_end_cell = ws_ref.cell(row = ws_ref.max_row, column = 2)
ref_address = KEY.WS_PREFIX_REFERENCE + col["field_name"] + "!" + \
'$A$1:$B$' + str(ws_ref.max_row)
# "$" + ref_beg_cell.column + "$" + str(ref_beg_cell.col_idx) + ":" + \
# "$" + ref_end_cell.column + "$" + str(ref_end_cell.col_idx)
init_cell = ws_init.cell(row = iItem + 2, column = num_init_column)
curr_cell.value = "=VLOOKUP(" + KEY.WS_INIT_DATA + "!" + init_cell.coordinate + "," + ref_address+ ",2, FALSE)"
ref_address_title = KEY.WS_PREFIX_REFERENCE + col["field_name"] + "!" + \
"$B$1:" + "$B$" + str(ws_ref.max_row)
# Create a data-validation object with list validation
dv = DataValidation(type="list", formula1="=" + ref_address_title, allow_blank=True)
# Optionally set a custom error message
dv.error ='Your entry is not in the list'
dv.errorTitle = 'Invalid Entry'
# Optionally set a custom prompt message
dv.prompt = 'Please select from the list'
dv.promptTitle = 'List Selection'
ws.add_data_validation(dv)
dv.add(curr_cell)
elif (vCol["is_data_field"] and vCol["is_ref"]):
pre_cell = ws.cell(row = iItem + 2, column = idx_col)
#vlookup column 2-3 in reference data
ws_ref = wb[KEY.WS_PREFIX_REFERENCE + col["field_name"]]
#ref_beg_cell = ws_ref.cell(row=1, column=2)
ref_end_cell = ws_ref.cell(row = ws_ref.max_row, column = ws_ref.max_column)
ref_address = KEY.WS_PREFIX_REFERENCE + col["field_name"] + "!" + \
'$B$1:$C$' + str(ws_ref.max_row)
#"$" + ref_beg_cell.column + "$" + str(ref_beg_cell.col_idx) + ":" + \
#"$" + ref_end_cell.column + "$" + str(ref_end_cell.col_idx)
curr_cell.value = "=VLOOKUP(" + pre_cell.coordinate + "," + ref_address+ ",2, FALSE)"
#format worksheet
ws = format_style(worksheet_style["NORMAL"]).format(ws)
#wb.defined_names.append(defined_name.DefinedName(attr_text="HCSSYS_DataDomain!A$1", name="TEST_NAME_0000000"))
response = HttpResponse(content=save_virtual_workbook(wb), mimetype='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
time_export = datetime.datetime.now().strftime("%Y.%m.%dT%H.%M.%S");
response['Content-Disposition'] = 'attachment; filename=' + 'export-' + time_export + '.xlsx'
return response
def _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 _format_sheet(self, Sheet):
# Background Color
CyanFill = PatternFill(
start_color='E0FFFF',
end_color='E0FFFF',
fill_type='solid',
)
SkyBlueFill = PatternFill(
start_color='71acd6',
end_color='71acd6',
fill_type='solid',
)
# Border Style
thin = Side(
border_style="thin",
color="000000"
)
border = Border(
top=thin,
left=thin,
right=thin,
bottom=thin
)
# Font Style
TahomaFont = Font(
bold=False,
name='Tahoma',
size=10
)
TahomaRedFont = Font(
bold=False,
name='Tahoma',
size=10,
color='FF0000'
)
TahomaBoldFont = Font(
bold=True,
name='Tahoma',
size=10
)
# Cell Type
DecimalNumber = DataValidation(
type="decimal",
operator="greaterThanOrEqual",
formula1=0
)
num_format = '#,##0.00'
ChargeTypeFormula = SHEET_FORMULAS.get('charge_type', False)
ExpenseAGFormula = SHEET_FORMULAS.get('expense_ag_list', False)
RevenueAGFormula = SHEET_FORMULAS.get('revenue_ag_list', False)
JobOrderFormula = SHEET_FORMULAS.get('job_order_formula', False)
# Adding drop-down formula to sheet
Sheet.add_data_validation(ChargeTypeFormula)
Sheet.add_data_validation(ExpenseAGFormula)
Sheet.add_data_validation(RevenueAGFormula)
Sheet.add_data_validation(JobOrderFormula)
Sheet.add_data_validation(DecimalNumber)
protection = Protection(locked=False)
startrow = 10
lastrow = self.editable_lines + startrow
# Formating Lines
for row in range(startrow, lastrow):
for col in range(1, 26):
Sheet.cell(row=row, column=col).fill = CyanFill
Sheet.cell(row=row, column=col).border = border
Sheet.cell(row=row, column=col).font = TahomaFont
if col == 1: # Charge Type Column
ChargeTypeFormula.add(Sheet.cell(row=row, column=col))
Sheet.cell(row=row, column=col).protection = protection
elif col == 2: # Activity Group Column
if Sheet.title == 'Expense':
ExpenseAGFormula.add(Sheet.cell(row=row, column=col))
else:
RevenueAGFormula.add(Sheet.cell(row=row, column=col))
Sheet.cell(row=row, column=col).protection = protection
elif col == 3:
JobOrderFormula.add(Sheet.cell(row=row, column=col))
Sheet.cell(row=row, column=col).protection = protection
elif col == 9: # Total Budget Column
col_F = get_column_letter(6)
col_G = get_column_letter(7)
col_H = get_column_letter(8)
value = "=%s%s*$%s$%s*$%s$%s" % (col_F, row,
col_G, row,
col_H, row)
Sheet.cell(row=row, column=col).value = value
Sheet.cell(row=row, column=col).number_format = num_format
elif col == 23: # Total of phased entries Column
col_K = get_column_letter(11)
col_V = get_column_letter(22)
value = "=SUM(%s%s:$%s$%s)" % (col_K, row,
col_V, row)
Sheet.cell(row=row, column=col).value = value
Sheet.cell(row=row, column=col).number_format = num_format
elif col == 24: # Total minus phased total Column
col_I = get_column_letter(9)
col_W = get_column_letter(23)
value = "=IF(%s%s-%s%s<>0,%s%s-%s%s,0)" % (col_I, row,
col_W, row,
col_I, row,
col_W, row)
Sheet.cell(row=row, column=col).value = value
Sheet.cell(row=row, column=col).number_format = num_format
elif col == 25: # Error Column
col_X = get_column_letter(24)
value = '=IF(ABS(%s%s)>0,"Error","")' % (col_X, row)
Sheet.cell(row=row, column=col).value = value
Sheet.cell(row=row, column=col).font = TahomaRedFont
elif col not in (4, 5):
Sheet.cell(row=row, column=col).protection = protection
DecimalNumber.add(Sheet.cell(row=row, column=col))
Sheet.cell(row=row, column=col).number_format = num_format
else:
Sheet.cell(row=row, column=col).protection = protection
# Formating TOTAL line
for col in range(1, 25):
Sheet.cell(row=lastrow, column=col).fill = SkyBlueFill
Sheet.cell(row=lastrow, column=col).border = border
Sheet.cell(row=lastrow, column=col).font = TahomaFont
if col == 1:
Sheet.cell(row=lastrow, column=col).value = 'TOTAL'
Sheet.cell(row=lastrow, column=col).font = TahomaBoldFont
if col > 7:
col_letter = get_column_letter(col)
value = "=SUM(%s%s:$%s$%s)" % (col_letter, startrow,
col_letter, row)
Sheet.cell(row=lastrow, column=col).value = value
Sheet.cell(row=lastrow, column=col).number_format = num_format