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 _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_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 _create_bool_dv(allow_blank=True):
# Stringify values and create data-validation object
_values = '"{}"'.format(','.join(['TRUE', 'FALSE']))
dv = DataValidation(type="list", formula1=_values, allow_blank=allow_blank)
# Optionally set a custom error message
dv.error = 'Entry not in the list'
dv.errorTitle = 'Invalid Entry'
return dv
def validation_from_list(validation_range):
'''Creates a data validation object based on validation_range (cell range to point to).
Requires import of DataValidation from openpyxl.worksheet.datavalidation '''
from openpyxl.worksheet.datavalidation import DataValidation
validation = DataValidation(type='list', formula1=validation_range)
validation.error = 'Your entry is not in the list'
validation.errorTitle = 'Invalid Entry'
validation.prompt = 'Please select from the list'
validation.promptTitle = 'List Selection'
return validation
def __create_data_list_for_justification(is_fac):
"""
Création de la liste de choix pour la justification
:return:
"""
dv = DataValidation(type="list", formula1='"%s"' % ExamEnrollment.justification_label_authorized(is_fac), allow_blank=True)
dv.error = str(_('Invalid entry, not in the list of choices'))
dv.errorTitle = str(_('Invalid entry'))
dv.prompt = str(_('Please choose in the list'))
dv.promptTitle = str(_('List of choices'))
return dv
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_enum_dv(values, allow_blank=True):
# Remove 'null' values from JSON schema
values = [x for x in values if x is not None]
# Stringify values and create data-validation object
_values = '"{}"'.format(','.join(values))
dv = DataValidation(type="list", formula1=_values, allow_blank=allow_blank)
# Optionally set a custom error message
dv.error = 'Entry not in the list'
dv.errorTitle = 'Invalid Entry'
return dv
def _apply_xlsm_formats(self, sheet, cell):
"""Apply column-specific, macro-enabled spreadsheet formats."""
# Create styles
normal = Font(name='Arial')
bold = copy(normal)
bold.bold = True
# Acquire indices and headers
header = cell.value
col_id = cell.column
col = sheet.column_dimensions[col_id]
col.font = normal
col.width = self._get_column_width(header)
# Lock only the headers
col.protection = Protection(locked=False)
cell.font = normal # Required for locking (openpyxl bug?)
# Format the required columns
if header in REQUIRED:
cell.font = bold
# Format the columns with limited options
try:
options = '"{}"'.format(','.join(LIMITED[header]))
valid = DataValidation(type="list", formula1=options)
valid.error = "Invalid {}.".format(header)
sheet.add_data_validation(valid)
valid.add(self._get_column_body(col_id))
col.width = self._get_column_width(LIMITED[header])
except KeyError:
pass
# Format the date column
if header == 'SEQ_DATE':
valid_date = DataValidation(type="date")
valid_date.error = "Invalid date."
sheet.add_data_validation(valid_date)
valid_date.add(self._get_column_body(col_id))
def dataval_list(ws, enum, range):
# Create a data-validation object with list validation
dvl = DataValidation(type="list", formula1=enum, allow_blank=True)
# Optionally set a custom error message
dvl.error = 'Your entry is not in the list'
dvl.errorTitle = 'Invalid Entry'
# Optionally set a custom prompt message
dvl.prompt = 'Please select from the list'
dvl.promptTitle = 'List Selection'
# Add the data-validation object to the worksheet
ws.add_data_validation(dvl)
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 dataval_list(ws, enum, range):
# Create a data-validation object with list validation
dvl = DataValidation(type="list", formula1=enum, allow_blank=True)
# Optionally set a custom error message
dvl.error ='Your entry is not in the list'
dvl.errorTitle = 'Invalid Entry'
# Optionally set a custom prompt message
dvl.prompt = 'Please select from the list'
dvl.promptTitle = 'List Selection'
# Add the data-validation object to the worksheet
ws.add_data_validation(dvl)
# Or, apply the validation to a range of cells
dvl.ranges.append(range)
def dataValidationLength(self, chars):
"""Create a data-validation object for a string with maximum
length validation (chars >= 0) or exact length validation
(-chars for chars < 0).
"""
if chars < 0:
op = 'equal'
chars = -chars
else:
op = 'lessThanOrEqual'
dv = DataValidation(type='textLength', operator=op, formula1=chars)
# Optionally set a custom error message
dv.error = 'Entry is too long'
# Add the data-validation object to the worksheet
self._ws.add_data_validation(dv)
return dv
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 add_scenario_selector(sheet, column, row, selections):
"""
add_scenario_selector() -> None
--``filename`` is the xlsx file (Chef chopped) to work on
--``sources_dict`` is a dictionary containing the Excel sheet names and
selector cell coordinates to link
Method adds VB code to a completed Chef workbook to link scenario
selection cells together to allow universal scenario change from any
sheet in the workbook.
"""
select_column = column + 2
options = ','.join(selections)
dv = DataValidation(type="list",
formula1='"%s"' % options,
allow_blank=False)
# 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'
sheet.add_data_validation(dv)
selector_cell = sheet.cell(column=select_column, row=row)
selector_cell.value = "Base"
dv.add(selector_cell)
sheet.bb.scenario_selector = selector_cell.coordinate
# Make label cells and drop-down selector
CellStyles.format_scenario_selector_cells(sheet, column, select_column,
row)
def exportar_planilha_importacao_usuarios_perfil_escola(request, **kwargs):
response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
response['Content-Disposition'] = 'attachment; filename=planilha_importacao_usuarios_perfil_ESCOLA.xlsx'
workbook: Workbook = Workbook()
ws = workbook.active
ws.title = 'ESCOLA'
headers = [
'Cód. EOL da U.E',
'Nome do Usuário',
'Cargo',
'Email',
'CPF',
'Telefone',
'RF',
'Perfil',
]
_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='"DIRETOR, DIRETOR CEI"',
allow_blank=True
)
dv.error = 'Perfil Inválido'
dv.errorTitle = 'Perfil não permitido'
ws.add_data_validation(dv)
dv.add('H2:H1048576')
workbook.save(response)
return response
def active(name):
# Create the workbook and worksheet we'll be working with
wb = openpyxl.load_workbook(name)
ws = wb.active
ws['C1'] = '数据反馈'
ws['D1'] = '数据反馈2'
# Create a data-validation object with list validation
dv = DataValidation(type="list",
formula1='"不需要,暂时不需要,未接,设置,需要,关机,停机,空号"',
allow_blank=True)
# Optionally set a custom error message
dv.error = '内容填写错误'
dv.errorTitle = 'Invalid Entry'
# Optionally set a custom prompt message
# dv.prompt = 'Please select from the list'
# dv.promptTitle = '请筛选'
# Add the data-validation object to the worksheet
ws.add_data_validation(dv)
dv.add('C2:C5000')
wb.save(name)
print lastname, firstname, 'has been added to the roster'
# ask user to add another trainee
another = raw_input("Do you need to add another trainee? ")
if another == 'yes' or another == 'y': continue
else: break
else:
print 'OK. I just wanted to make sure.'
break
# Data validation for cells that do not have classes assigned
dv = DataValidation(type='list',
formula1='"Test1,Test2,Test3"',
allow_blank=True)
# Data validation error messages
dv.error = 'Your entry is not in the list.'
dv.errorTitle = 'Invalid Entry'
# Data validation prompt messages
dv.prompt = 'Please select from the list.'
dv.promptTitle = 'List Selection'
# Add data validation object to roster worksheet
sheet1.add_data_validation(dv)
# Apply validation to the range of cells requiring them
# Need cells I3 to the last row in the I column
maxrow = sheet1.max_row
maxvalidationcell = sheet1.cell(row=maxrow, column=9)
maxvalidationcell = sheet1[maxvalidationcell.coordinate]
dv.ranges.append('I3:maxvalidationcell')
def generate_xlsx_export_template(data, mtef=False):
if mtef:
current_year = datetime.datetime.utcnow().date().year
mtef_cols = [
u"FY{}/{} (MTEF)".format(str(year)[2:4],
str(year + 1)[2:4])
for year in range(current_year, current_year + 3)
]
_headers = [u"ID", u"Project code", u"Activity Title"]
_headers += mtef_cols
_headers += [
u'Activity Status', u'Activity Dates (Start Date)',
u'Activity Dates (End Date)', u"County"
]
else:
mtef_cols = []
_headers = [
u"ID",
u"Project code",
u"Activity Title",
util.previous_fy_fq(),
u'Activity Status',
u'Activity Dates (Start Date)',
u'Activity Dates (End Date)',
u"County",
]
writer = xlsxDictWriter(_headers)
cl_lookups = get_codelists_lookups()
myFill = PatternFill(start_color='FFFF00',
end_color='FFFF00',
fill_type='solid')
statuses = get_codelists_lookups_by_name()["ActivityStatus"].keys()
# Activity Status validation
v_status = DataValidation(type="list",
formula1='"{}"'.format(u",".join(statuses)),
allow_blank=False)
v_status.error = 'Your entry is not in the list'
v_status.errorTitle = 'Activity Status'
v_status.prompt = 'Please select from the list'
v_status.promptTitle = 'Activity Status'
v_id = DataValidation(type="whole")
v_id.errorTitle = "Invalid ID"
v_id.error = "Please enter a valid ID"
v_id.promptTitle = 'Liberia Project Dashboard ID'
v_id.prompt = 'Please do not edit this ID. It is used by the Liberia Project Dashboard to uniquely identify activities.'
v_date = DataValidation(type="date")
v_date.errorTitle = "Invalid date"
v_date.error = "Please enter a valid date"
v_number = DataValidation(type="decimal")
v_number.errorTitle = "Invalid number"
v_number.error = "Please enter a valid number"
for org_code, activities in sorted(data.items()):
writer.writesheet(org_code)
writer.ws.add_data_validation(v_status)
writer.ws.add_data_validation(v_date)
writer.ws.add_data_validation(v_number)
writer.ws.add_data_validation(v_id)
#writer.ws.protection.sheet = True
for activity in activities:
existing_activity = activity_to_json(activity, cl_lookups)
for mtef_year in mtef_cols:
fy_start, fy_end = re.match("FY(\d*)/(\d*) \(MTEF\)",
mtef_year).groups()
existing_activity[mtef_year] = sum([
float(
existing_activity["20{} Q1 (MTEF)".format(fy_start)]),
float(
existing_activity["20{} Q2 (MTEF)".format(fy_start)]),
float(
existing_activity["20{} Q3 (MTEF)".format(fy_start)]),
float(existing_activity["20{} Q4 (MTEF)".format(fy_start)])
])
writer.writerow(existing_activity)
if mtef == True:
for rownum in range(1 + 1, len(activities) + 2):
writer.ws.cell(row=rownum, column=4).fill = myFill
writer.ws.cell(row=rownum, column=5).fill = myFill
writer.ws.cell(row=rownum, column=6).fill = myFill
writer.ws.cell(row=rownum,
column=4).number_format = u'"USD "#,##0.00'
writer.ws.cell(row=rownum,
column=5).number_format = u'"USD "#,##0.00'
writer.ws.cell(row=rownum,
column=6).number_format = u'"USD "#,##0.00'
writer.ws.column_dimensions[u"C"].width = 70
writer.ws.column_dimensions[u"D"].width = 15
writer.ws.column_dimensions[u"E"].width = 15
writer.ws.column_dimensions[u"F"].width = 15
writer.ws.column_dimensions[u"G"].width = 15
writer.ws.column_dimensions[u"H"].width = 20
writer.ws.column_dimensions[u"I"].width = 20
v_id.add('A2:A{}'.format(len(activities) + 2))
v_number.add('D2:F{}'.format(len(activities) + 2))
v_status.add('G2:G{}'.format(len(activities) + 2))
v_date.add('H2:I{}'.format(len(activities) + 2))
elif mtef == False:
for rownum in range(1 + 1, len(activities) + 2):
writer.ws.cell(row=rownum, column=4).fill = myFill
writer.ws.cell(row=rownum,
column=4).number_format = u'"USD "#,##0.00'
writer.ws.column_dimensions[u"C"].width = 70
writer.ws.column_dimensions[u"D"].width = 15
writer.ws.column_dimensions[u"E"].width = 15
writer.ws.column_dimensions[u"F"].width = 20
writer.ws.column_dimensions[u"G"].width = 15
v_id.add('A2:A{}'.format(len(activities) + 2))
v_number.add('D2:D{}'.format(len(activities) + 2))
v_status.add('E2:E{}'.format(len(activities) + 2))
v_date.add('F2:G{}'.format(len(activities) + 2))
writer.delete_first_sheet()
return writer.save()
def decorateExcel(self):
BOX_GREEN = Border(left=Side(border_style='thin', color='98bd47'),
right=Side(border_style='thin', color='98bd47'),
top=Side(border_style='thin', color='98bd47'),
bottom=Side(border_style='thin', color='98bd47')
)
BOX_BLUE = Border(left=Side(border_style='thin', color='5083c0'),
right=Side(border_style='thin', color='5083c0'),
top=Side(border_style='thin', color='5083c0'),
bottom=Side(border_style='thin', color='5083c0')
)
BOX_RED = Border(left=Side(border_style='thin', color='C03737'),
right=Side(border_style='thin', color='C03737'),
top=Side(border_style='thin', color='C03737'),
bottom=Side(border_style='thin', color='C03737')
)
for ws in self.wb:
if ws.title == 'SUMMARY':
# 셀 너비
ws.column_dimensions["a"].width = 20
for i in ('b', 'c', 'd'):
ws.column_dimensions[i].width = 14
for i in ('e', 'f', 'g', 'h'):
ws.column_dimensions[i].width = 8
for i in ('j', 'k', 'l', 'm'):
ws.column_dimensions[i].width = 13.5
# 셀 폰트
ws['a1'].font = Font(b=True, size=14)
for i in ('a','b','c','d','e','f','g','h'):
ws[i+str(3)].font = Font(b=True, color=WHITE)
ws[i+str(3)].fill = PatternFill(fgColor=Color('5083c0'), patternType='solid')
for i in ('j', 'k', 'l', 'm'):
ws[i+str(3)].font = Font(b=True, color=WHITE)
ws[i+str(3)].fill = PatternFill(fgColor=Color('C03737'), patternType='solid')
# 셀 얼라인
for i in ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i','j','k','l','m'):
ws[i + str(3)].alignment = Alignment(horizontal='center', vertical='center')
# 셀 고정
ws.freeze_panes = 'A4'
# 테두리
for j in range(4,ws.max_row+1):
for i in ('a', 'b', 'c','d','e','f','g','h'):
ws[i+str(j)].border = BOX_BLUE
for i in ('j', 'k', 'l', 'm'):
ws[i + str(4)].border = BOX_RED
else:
# 셀 너비
ws.column_dimensions["a"].width = 50
ws.column_dimensions["b"].width = 60
ws.column_dimensions["c"].width = 15
for i in ('d', 'e', 'f', 'g', 'h', 'i'):
ws.column_dimensions[i].width = 7
# 셀 폰트
ws['a1'].font = Font(b=True, size=14)
for i in ('a','b','c'):
ws[i+str(2)].font = Font(b=True, color=WHITE)
ws[i+str(2)].fill = PatternFill(fgColor=Color('98bd47'), patternType='solid')
for i in ('d','e','f','g','h','i'):
ws[i+str(1)].font = Font(b=True, color=WHITE)
ws[i+str(1)].fill = PatternFill(fgColor=Color('5083c0'), patternType='solid')
# 셀 얼라인
for i in ('a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i'):
ws[i+str(2)].alignment= Alignment(horizontal='center', vertical='center')
for i in ('d', 'e', 'f', 'g', 'h', 'i'):
ws[i + str(1)].alignment = Alignment(horizontal='center', vertical='center')
# 셀 고정
ws.freeze_panes = 'A3'
# 테두리
for j in range(3,ws.max_row+1):
for i in ('a', 'b', 'c'):
ws[i+str(j)].border = BOX_GREEN
for i in ('d', 'e', 'f', 'g', 'h', 'i'):
ws[i + str(2)].border = BOX_BLUE
# 필터
ws.auto_filter.ref = "A2:c"+str(ws.max_row+1)
# 값 고정 (data-validation)
dv = DataValidation(type='list', formula1='"OK,NG,NA,NT"', allow_blank=True)
dv.error = 'OK, NG, NA, NT 만 기입할 수 있습니다. / Your entry is not in the list(OK, NG, NA, NT)'
dv.errorTitle = 'Invalid Entry'
dv.prompt = 'Please select from the list (OK, NG, NT(need to support but not yet ready), NA(not support)'
dv.promptTitle = 'List Selection'
ws.add_data_validation(dv)
# dv.ranges.append('c3:c' + str(ws.max_row ))
dv.add('c3:c' + str(ws.max_row))
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
from openpyxl.worksheet.datavalidation import DataValidation from projectdashboard.lib.codelists import get_codelists_lookups_by_name # Activity Status validation v_status = DataValidation(type="list", allow_blank=False) v_status.error = 'Your entry is not in the list' v_status.errorTitle = 'Activity Status' v_status.prompt = 'Please select from the list' v_status.promptTitle = 'Activity Status' # ID validation v_id = DataValidation(type="whole") v_id.errorTitle = "Invalid ID" v_id.error = "Please enter a valid ID" v_id.promptTitle = 'Liberia Project Dashboard ID' v_id.prompt = 'Please do not edit this ID. It is used by the Liberia Project Dashboard to uniquely identify activities.' # Date validation v_date = DataValidation(type="date") v_date.errorTitle = "Invalid date" v_date.error = "Please enter a valid date" # Number validation v_number = DataValidation(type="decimal") v_number.errorTitle = "Invalid number" v_number.error = "Please enter a valid number"
def generate_template(filename):
"""
Generate Pixel XLS template that will be used during the imporation
process.
Parameters
----------
filename : str
Pixel's XLSX template file name
Returns
-------
checksum : str
Pixel's XLSX template file checksum
version : str
Pixel's XLSX template file version
"""
wb = Workbook()
ws = wb.active
ws.title = _("Import information for Pixel")
# Styles
# https://coolors.co/4c5760-c5e8cd-fdffd9-ffd7ad-d1a690
section_title_font = Font(bold=True, size=18, color='d1a690')
field_font = Font(color='4c5760')
required_field_font = Font(bold=True, color='4c5760')
comment_fill = PatternFill('solid', fgColor='c5e8cd')
pixelset_header_fill = PatternFill('solid', fgColor='ffd7ad')
long_text_align = Alignment(vertical='center', wrap_text=True)
user_data_fill = PatternFill('solid', fgColor='fdffd9')
# Validators
def get_node_repr(node):
return '{}{}{}'.format('—' * node.level, ' ' if node.level else '',
node.name)
omics_areas_names = ','.join(
get_node_repr(oa) for oa in OmicsArea.objects.all())
omics_areas_validator = DataValidation(
type='list',
formula1='"{}"'.format(omics_areas_names),
allow_blank=False)
omics_areas_validator.error = _("Omics Area does not exists")
omics_areas_validator.prompt = _("Select an Omics Area")
ws.add_data_validation(omics_areas_validator)
data_source_validator = DataValidation(type='list',
formula1='"Published, Unpublished"',
allow_blank=False)
data_source_validator.error = _("Value not allowed")
data_source_validator.prompt = _("Select a data source")
ws.add_data_validation(data_source_validator)
omics_unit_type_names = ','.join(t.name
for t in OmicsUnitType.objects.all())
omics_unit_type_validator = DataValidation(
type='list',
formula1='"{}"'.format(omics_unit_type_names),
allow_blank=False)
omics_unit_type_validator.error = _("Value not allowed")
omics_unit_type_validator.prompt = _("Select a type of omics unit")
ws.add_data_validation(omics_unit_type_validator)
strain_names = ','.join('{} ({})'.format(s.name, s.species.name)
for s in Strain.objects.all())
strain_validator = DataValidation(type='list',
formula1='"{}"'.format(strain_names),
allow_blank=False)
strain_validator.error = _("Value not allowed")
strain_validator.prompt = _("Select a strain")
ws.add_data_validation(strain_validator)
# Ranges
comment_range = 'A{0}:K{0}'
# Experiment
ws['A1'] = _("Experiment")
ws['A1'].font = section_title_font
ws.row_dimensions[1].height = 40
ws.merge_cells(comment_range.format(2))
ws['A2'] = _(
"# This section describes the experimental conditions that were "
"applied to obtain the secondary datafile (see section 'Analysis' "
"below). Note that these experiments can be already published (in "
"this situation a DOI is required) or not (in this situation a "
"laboratory has to be specified).")
ws['A2'].fill = comment_fill
ws.row_dimensions[2].height = 40
style_range(ws,
comment_range.format(2),
fill=comment_fill,
alignment=long_text_align)
ws['A3'] = _("Omics area")
ws['A3'].font = required_field_font
ws['B3'].fill = user_data_fill
omics_areas_validator.add(ws['B3'])
ws['A4'] = _("Completion date")
ws['A4'].font = field_font
ws['B4'].fill = user_data_fill
ws['A5'] = _("Summary")
ws['A5'].font = field_font
ws['B5'].fill = user_data_fill
ws['A6'] = _("Release date")
ws['A6'].font = field_font
ws['B6'].fill = user_data_fill
ws['A7'] = _("Data source")
ws['A7'].font = field_font
ws['B7'].fill = user_data_fill
data_source_validator.add(ws['B7'])
ws['A8'] = _("Reference (entry)")
ws['A8'].font = field_font
ws['B8'].fill = user_data_fill
ws['B8'].comment = Comment(
_("If this work has been published, we expect a DOI in this cell."),
_("Pixel's administrator"))
# Analysis
ws['A10'] = _("Analysis")
ws['A10'].font = section_title_font
ws.row_dimensions[10].height = 40
ws.merge_cells(comment_range.format(11))
ws['A11'] = _(
"# This section describes the data analyses that were performed on "
"secondary datasets to obtain pixel datasets. The secondary "
"datafile has to be associated to the pixel datasets during the "
"import process.")
ws['A11'].fill = comment_fill
ws.row_dimensions[11].height = 40
style_range(ws,
comment_range.format(11),
fill=comment_fill,
alignment=long_text_align)
ws['A12'] = _("Name of secondary data file")
ws['A12'].font = required_field_font
ws['B12'].fill = user_data_fill
ws['A13'] = _("Name of notebook file")
ws['A13'].font = field_font
ws['B13'].fill = user_data_fill
ws['A14'] = _("Description")
ws['A14'].font = field_font
ws['B14'].fill = user_data_fill
ws['A15'] = _("Date of the analysis")
ws['A15'].font = field_font
ws['B15'].fill = user_data_fill
# Pixel datasets
ws['A17'] = _("Pixel datasets")
ws['A17'].font = section_title_font
ws.merge_cells(comment_range.format(18))
ws['A18'] = _(
"# This section lists and describes each pixel datasets to be "
"imported in the system. These files have to be associated to the "
"secondary datafile (and the notebook datafile if available) "
"during the import process. A specific comment can be added for "
"each set of Pixel to better describe their differences.")
ws['A18'].fill = comment_fill
ws.row_dimensions[18].height = 40
style_range(ws,
comment_range.format(18),
fill=comment_fill,
alignment=long_text_align)
style_range(ws, comment_range.format(19), fill=pixelset_header_fill)
ws['A19'] = _("File name")
ws['B19'] = _("Omics Unit type")
ws['C19'] = _("Strain (Species)")
ws['D19'] = _("Comment")
for row in range(20, 31, 1):
omics_unit_type_validator.add(ws['B{}'.format(row)])
for column in ('A', 'B', 'C', 'D'):
ws[f'{column}{row}'].fill = user_data_fill
strain_validator.add(ws['C{}'.format(row)])
ws.column_dimensions['A'].width = 40
ws.column_dimensions['B'].width = 30
ws.column_dimensions['C'].width = 30
ws.column_dimensions['D'].width = 30
wb.save(filename)
return (sha256_checksum(filename), get_template_version(filename))
from openpyxl import Workbook
from openpyxl.worksheet.datavalidation import DataValidation
# Create the workbook and worksheet we'll be working with
wb = Workbook()
ws = wb.active
# Create a data-validation object with list validation
dv = DataValidation(
type="list",
formula1='"корпусном,металлическом,мягкой мебели,столярном"',
allow_blank=True,
)
# Optionally set a custom error message
dv.error = "Запись отсутствует в списке"
dv.errorTitle = "Ошибочный ввод"
# Optionally set a custom prompt message
dv.prompt = "Выберите цех"
dv.promptTitle = "Список цехов"
# Add the data-validation object to the worksheet
ws.add_data_validation(dv)
# Create some cells, and add them to the data-validation object
c1 = ws["A1"]
c1.value = "Dog"
dv.add(c1)
wb.save(r"d:\test.xlsx")
os.startfile(r"d:\test.xlsx")
})
df_mapping = pd.read_excel(directory+plo_clo_filename, sheet_name='Program_course_mapping', converters={'course_id': str})
#print(tabulate(df_plo[:20], headers='keys'))
#print(tabulate(df_clo[:20], headers='keys'))
#print(tabulate(df_mapping[:20], headers='keys'))
# get program list
df_programs = df_mapping[['program_code', 'plan_code', 'program_name']].drop_duplicates()
# Matching column data validation
dv_match = DataValidation(type="list", formula1='"0,1"', allow_blank=True)
# Optionally set a custom error message
dv_match.error ='Your entry is not in the list'
dv_match.errorTitle = 'Invalid Entry'
# Optionally set a custom prompt message
dv_match.prompt = 'Please select from the list'
dv_match.promptTitle = 'List Selection'
# General column data validation
dv_general = DataValidation(type="list", formula1='selections!$A$2:$A$7', allow_blank=True)
# Optionally set a custom error message
dv_general.error ='You have entered free text'
dv_general.errorTitle = 'Free Text'
dv_general.errorStyle = 'warning'
# Active Verb column data validation
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
openpyxl模块学习 验证单元格 Validating Cells """ # 数据验证器可以应用于单元格范围，但不强制或评估。范围不必是连续的: # 例如。“A1 B2:B5”包含A1和细胞B2到B5，而不是A2或B2。 from openpyxl import Workbook from openpyxl.worksheet.datavalidation import DataValidation # Create the workbook and worksheet we'll be working with wb = Workbook() ws = wb.active # Create a data-validation object with list validation dv = DataValidation(type='list', formula1='"Dog,Cat,Bat"', allow_blank=True) # Optionally set a custom error message dv.error = 'Your entyr is not in the list' dv.errorTitle = 'Invalid Entyr' # Optionally set a custom prompt message dv.prompt = 'please select from the list' dv.promptTitle = 'List Selection' # Add the data-validation object to the worksheet ws.add_data_validation(dv) # Create some cells, and add them to the data-validation object c1 = ws["A1"] c1.value = "Dog" dv.add(c1) c2 = ws["A2"] c2.value = "An invalid value"
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 create_sample_sheet(project_uuid):
"""Create sample sheet with 'initial data' for project"""
wb = load_workbook(
os.path.join(os.path.dirname(__file__),
'static/portal/excel/mng_excel_template.xlsx'))
ws = wb['Data']
project = limsfm_get_project(project_uuid)
ws['F1'] = project['reference']
data_rows = ws.rows[6:]
for i, pl in enumerate(project['projectlines']):
row = data_rows[i]
row[1].value = pl['sample_ref']
if pl['customers_ref']:
row[2].value = pl['customers_ref']
if pl['dna_concentration_ng_ul']:
row[3].value = float(pl['dna_concentration_ng_ul'])
if pl['volume_ul']:
row[4].value = float(pl['volume_ul'])
row[5].value = pl['taxon_id']
if pl['collection_day']:
row[6].value = int(pl['collection_day'])
if pl['collection_month']:
row[7].value = int(pl['collection_month'])
if pl['collection_year']:
row[8].value = int(pl['collection_year'])
row[9].value = pl['geo_country_name']
row[10].value = pl['geo_specific_location']
if pl['study_type'] == "Lab":
row[11].value = pl['lab_experiment_type']
row[12].value = pl['further_details']
elif pl['study_type'] == "Host":
row[13].value = pl['host_taxon_id']
row[14].value = pl['host_sample_type']
row[15].value = pl['further_details']
elif pl['study_type'] == "Environmental":
row[16].value = pl['environmental_sample_type']
row[17].value = pl['further_details']
# Country list validation
country_validator = DataValidation(type='list',
formula1='=Lookups!$B$1:$B$267',
allow_blank=True)
country_validator.error = "Please select a country from the list"
country_validator.errorTitle = "Invalid country"
ws.add_data_validation(country_validator)
country_validator.ranges.append('J7:J1000')
# Environmental sample type list validation
est_validator = DataValidation(type='list',
formula1='=Lookups!$C$1:$C$31',
allow_blank=True)
est_validator.error = ("Please select a environmental sample type from "
"the list")
est_validator.errorTitle = "Invalid environmental sample type"
ws.add_data_validation(est_validator)
est_validator.ranges.append('Q7:Q1000')
# Lab experiment type list validation
lab_validator = DataValidation(type='list',
formula1='=Lookups!$E$1:$E$5',
allow_blank=True)
lab_validator.error = "Please select a lab experiment type from the list"
lab_validator.errorTitle = "Invalid lab experiment type"
ws.add_data_validation(lab_validator)
lab_validator.ranges.append('L7:L1000')
# Host sample type list validation
hst_validator = DataValidation(type='list',
formula1='=Lookups!$D$1:$D$67',
allow_blank=True)
hst_validator.error = "Please select a host sample type from the list"
hst_validator.errorTitle = "Invalid host sample type"
ws.add_data_validation(hst_validator)
hst_validator.ranges.append('O7:O1000')
return wb
