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_list_validation(value, strict=True, allow_blank=True):
if type(value) == list:
formula = _build_list_formula(value)
else:
formula = value
dv = DataValidation(type="list", formula1=formula, showErrorMessage=strict,
allow_blank=allow_blank)
if strict:
dv.promptTitle = 'Strict Selection'
dv.prompt = 'You must select a value from the list'
else:
dv.promptTitle = 'Proposed Selection'
dv.prompt = 'You may select a value from the list or enter your own'
if allow_blank:
dv.promptTitle += " (blank allowed)"
return 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 create_list_validation(value, strict=True, allow_blank=True):
if isinstance(value, list):
formula = _build_list_formula(value)
else:
formula = value
dv = DataValidation(type="list",
formula1=formula,
showErrorMessage=strict,
allow_blank=allow_blank)
if strict:
dv.promptTitle = 'Strict Selection'
dv.prompt = 'You must select a value from the list'
else:
dv.promptTitle = 'Proposed Selection'
dv.prompt = 'You may select a value from the list or enter your own'
if allow_blank:
dv.promptTitle += " (blank allowed)"
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 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 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 create_validation(header):
# if we need the regex to match the dropdown string - from pythex.org
# dropdown_regex =
# re.compile('"=\\'Dropdown List\\'!\$([A-Z]+)\$(\d+):\$([A-Z]+)\$(\d+)"')
#
try:
f_str = VALIDATION_REFERENCES[header]
dv = DataValidation(type='list', formula1=f_str, allow_blank=True)
dv.prompt = "Please select from the list"
dv.promptTitle = 'List Selection'
return dv
except KeyError:
print("No validation")
return
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 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)
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')
# store output file on U drive with following convention
# with following format: 'Wk of [Monday following current day] Anticipated
#HL Training Attendance.xlsx'# Generate new sheet inside of the workbook
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()
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"
dv.add(c2)
# Or, apply the validation to a range of cells
dv.add('B1:B1048576') #This is the same as for the whole of column B
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 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))
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_review_form_spreadsheet(paperInfoWithReview, tjyear, isManager, isPaperSub = False):
filenameStr = paperSubUtilsFile.form_file_name(tjyear,\
paperInfoWithReview['paperInfo']['submissionSequence'],paperInfoWithReview['paperInfo'])
if isManager == True:
filenameStr = filenameStr+'manager_Review'
if isPaperSub == False:
reviewInfo = paperInfoWithReview['managerReviewInfo']
else:
filenameStr = filenameStr+'expert_Review'
if isPaperSub == False:
reviewInfo = paperInfoWithReview['expertReviewInfo']
filename = filenameStr+'.xlsx'
copyfile(os.path.join(app.config['UPLOAD_FOLDER'],'review_form.xlsx'), os.path.join(app.config['UPLOAD_FOLDER'],filename))
wb = load_workbook(os.path.join(app.config['UPLOAD_FOLDER'],filename))
ws = wb.active
ws.cell(row = 1, column = 1, value = app.config['EDITORIAL_COMMITEE_INFO']['journal']+ app.config['EDITORIAL_COMMITEE_INFO']['form'])
ws.cell(row = 3, column = 2, value = paperInfoWithReview['paperInfo']['paperTitle'])
ws.cell(row = 4, column = 2, value = paperInfoWithReview['authorList'][0]['email'])
if isManager == True:
ws.cell(row = 5, column = 2, value = paperInfoWithReview['managerInfo']['email'])
else:
ws.cell(row = 5, column = 2, value = paperInfoWithReview['expertInfo']['email'])
i=0
for reviewItemName in app.config['REVIEW_ITEM']:
ws.cell(row=9+i,column=1,value=reviewItemName)
i = i+1
i = 0
for question in app.config['REVIEW_COMMENTS_QUESTIONS']:
ws.cell(row=19+i*5, column= 1, value = question)
i = i+1
set_border(ws, 'A18:D43')
if isPaperSub == False:
if reviewInfo['reviewSubmitted']==True:
i=0
for reviewItem in reviewInfo['itemList']:
ws.cell(row=9+i,column=3,value=app.config['REVIEW_RESULT'][reviewItem['reviewItemGrade']])
i = i+1
ws.cell(row=16, column=3, value=app.config['REVIEW_RESULT'][reviewInfo['Overall']])
i = 0
for reviewQuestion in reviewInfo['questionList']:
ws.cell(row=20+i*5, column= 1, value = reviewQuestion['answer'])
i = i+1
else:
ws_bk = sheet = wb.get_sheet_by_name('bk')
i=1
string = ""
for result in app.config['REVIEW_RESULT']:
ws_bk.cell(row=1,column=i, value=result)
i = i+1
dv = DataValidation(type="list",\
formula1="{0}!$A$1:$D$1".format(quote_sheetname('bk'))\
)
#Optionally set a custom error message
dv.error ='Your entry is not in the list'
dv.errorTitle = 'Invalid Entry'
#Optionally set a custom prompt message
dv.prompt = 'Please select from the list'
dv.promptTitle = 'List Selection'
ws.add_data_validation(dv)
dv.add('C9:c16')
else:
ws_bk = sheet = wb.get_sheet_by_name('bk')
i=1
string = ""
for result in app.config['REVIEW_RESULT']:
ws_bk.cell(row=1,column=i, value=result)
i = i+1
dv = DataValidation(type="list",\
formula1="{0}!$A$1:$D$1".format(quote_sheetname('bk'))\
)
#Optionally set a custom error message
dv.error ='Your entry is not in the list'
dv.errorTitle = 'Invalid Entry'
#Optionally set a custom prompt message
dv.prompt = 'Please select from the list'
dv.promptTitle = 'List Selection'
ws.add_data_validation(dv)
dv.add('C9:c16')
wb.save(os.path.join(app.config['UPLOAD_FOLDER'],filename))
return filename
def 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))
def create_scan_code_excel(conf_path, entry_code, order_no, size_label):
# 使用openpyxl 创建excel表格
wb = Workbook()
ws = wb.active
dv = DataValidation(
type='list',
formula1=
'"1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24"',
allow_blank=False)
dv.prompt = "选择颜色"
dv.promptTitle = 'List Selection'
ws.add_data_validation(dv)
# 设置表头
entry_code_title = ws["A1"]
entry_code_title.value = "entry_code"
entry_code_title.fill = greyfill
entry_code_title.border = border
order_no_title = ws["B1"]
order_no_title.value = "order_no"
order_no_title.fill = greyfill
order_no_title.border = border
style_id_title = ws["C1"]
style_id_title.value = "style_id"
style_id_title.fill = greyfill
style_id_title.border = border
batch_no_title = ws["D1"]
batch_no_title.value = "batch_no"
batch_no_title.fill = greyfill
batch_no_title.border = border
color_no_title = ws["E1"]
color_no_title.value = "color_no"
color_no_title.fill = greyfill
color_no_title.border = border
size_label_title = ws["F1"]
size_label_title.value = "size_label"
size_label_title.fill = greyfill
size_label_title.border = border
quantity_title = ws["G1"]
quantity_title.value = "quantity"
quantity_title.fill = greyfill
quantity_title.border = border
row = ["A", "B", "C", "D", "E", "F", "G"]
with open(conf_path, encoding='utf-8') as f:
content = yaml.safe_load(f.read())
info = content.get('info')
style_id = info.get('style_id')
# 组织数据
data_list = [[
entry_code, order_no, style_id, "", "24", size_label[i], "1"
] for i in range(len(size_label))]
# 填充数据
for data in data_list:
current_row = ws.max_row
for i, v in enumerate(row):
if v == 'E':
dv.add(ws[v + str(current_row + 1)])
else:
ws[v + str(current_row + 1)].value = data[i]
ws[v + str(current_row + 1)].fill = redfill
ws[v + str(current_row + 1)].border = border
wb.save("1.xxxx.xlsx")
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")
#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 dv_active = DataValidation(type="list", formula1='selections!$B$2:$B$6', allow_blank=True) # Optionally set a custom error message
def generate_template(self):
template = 'Load Summary Template.xlsx'
path = os.path.abspath('.')
file_path = os.path.join(path, template)
print(file_path)
border = Border(left=Side(border_style='thin', color='000000'),
right=Side(border_style='thin', color='000000'),
top=Side(border_style='thin', color='000000'),
bottom=Side(border_style='thin', color='000000'))
if not os.path.isfile(file_path):
table = Workbook()
sheet = table['Sheet']
sheet.title = 'Main'
sheet['A1'] = 'Wind Turbine Name'
sheet['B1'] = 'Category'
sheet['C1'] = 'Path'
# font for the first row
sheet['A1'].font = Font(name='Microsoft Ya Hei', size=9, bold=True)
sheet['B1'].font = Font(name='Microsoft Ya Hei', size=9, bold=True)
sheet['C1'].font = Font(name='Microsoft Ya Hei', size=9, bold=True)
# width for each column
sheet.column_dimensions['A'].width = 20
sheet.column_dimensions['B'].width = 10
sheet.column_dimensions['C'].width = 70
for col in sheet.iter_rows(min_row=1, max_col=3, max_row=1):
for cell in col:
cell.alignment = Alignment(vertical='center',
horizontal='center')
for row in sheet.iter_rows(min_row=2, max_col=3, max_row=31):
for cell in row:
cell.font = Font(name='Microsoft Ya Hei', size=9)
#
for row in sheet.iter_rows(min_row=2, max_col=1, max_row=31):
for cell in row:
cell.alignment = Alignment(vertical='center',
horizontal='center')
# set data validation for column B
dv = DataValidation(type='list',
formula1='"Ultimate,Rainflow,Post,LCT,DLC12"')
dv.prompt = 'Make sure the selection is unique for each loop'
dv.promptTitle = 'List Selection'
sheet.add_data_validation(dv)
dv.add('B2:B301') # apply the validation to a range of cellls
# border
for i in range(1, 52):
for j in range(1, 4):
sheet.cell(row=i, column=j).border = border
# merge
for i in range(10):
sheet.merge_cells(start_row=5 * i + 2,
start_column=1,
end_row=5 * i + 6,
end_column=1)
table.save('Load Summary Template.xlsx')
print('Template generated successfully!')
# open template
os.startfile(file_path)
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 processor():
# create workbook
wb = Workbook()
wb = load_workbook('static/output/output.xlsm', keep_vba=True)
# create worksheets
# bankstatement = wb.active
# bankstatement.title = 'Bank Statement'
# billstatement = wb.create_sheet("Bill Statement")
# loanrepayment = wb.create_sheet("Loan Repayment Schedule")
# incomestatement = wb.create_sheet("Income Statement")
# balancesheet = wb.create_sheet("Balance Sheet")
#calculatedcf = wb.create_sheet("Calculated CF")
usermetrics = wb.create_sheet("User Metric")
summary = wb.create_sheet("Summary")
# add bank statement data in bankstatement worksheet
# for r in dataframe_to_rows(bankdata, index=False, header=True):
# bankstatement.append(r)
# bankstatement.delete_rows(1)
#
# # add bill statement data in billstatement worksheet
# for r in dataframe_to_rows(billdata, index=False, header=True):
# billstatement.append(r)
#
# # add loan repayment data in loanrepayment worksheet
# for r in dataframe_to_rows(loandata, index=False, header=True):
# loanrepayment.append(r)
# add balance sheet from CAT to balancesheet worksheet
# need to figure this out
#balancesheet = wb2.get_sheet("Balance Sheet")
# assign fixed variables to user metrics
# User Input
# create drop down list for user input
## Type of Client
clientdv = DataValidation(type="list",
formula1='"new-to-bank,existing"',
allow_blank=False)
clientdv.prompt = 'Please select from the list'
clientdv.promptTitle = 'Client List Selection'
## Payment status
paymentdv = DataValidation(type="list",
formula1='"punctual,not punctual"',
allow_blank=False)
paymentdv.prompt = 'Please select from the list'
paymentdv.promptTitle = 'Payment List Selection'
## Operating account
oadv = DataValidation(type="list",
formula1='"reasonable,not reasonable"',
allow_blank=False)
oadv.prompt = 'Please select from the list'
oadv.promptTitle = 'Behavior List Selection'
## Liquidity status
liquiditydv = DataValidation(type="list",
formula1='"low,medium,high"',
allow_blank=False)
liquiditydv.prompt = 'Please select from the list'
liquiditydv.promptTitle = 'Liquidity Level List Selection'
## TU Information
tudv = DataValidation(type="list",
formula1='"found,not found"',
allow_blank=False)
tudv.prompt = 'Please select from the list'
tudv.promptTitle = 'Behavior List Selection'
# Summary
# color scheme
greyFill = PatternFill(start_color='757171',
end_color='757171',
fill_type='solid')
whiteFill = PatternFill(start_color='d0cece',
end_color='d0cece',
fill_type='solid')
# text style and color
whiteheadingstyle = Font(size=14,
name='Segoe UI',
color='ffffff',
underline='single')
whitebodystyle = Font(size=11, name='Segoe UI', color='ffffff')
greybodystyle = Font(size=11, name='Segoe UI', color='757171')
# width
width = 2.8
# formatting
# change background color
# No of written Rows in sheet
# 1,048,576 rows by 16,384 columns
# No of written Rows in sheet
r = 500
# No of written Columns in sheet
c = 100
# mass format color, font, filler lines, result lines and account background sentences in excel
filler_lines_in_grey_square = ["Please fill in the blanks accordingly via the dropdown options", \
"(The inputs will be used within the sentences on the left)", \
"Description", \
"Type of client", \
"Type of business (SIC)", \
"Type of business (NAICS)", \
"Number of Installment Loans", \
"Payment Status", \
"Operating Account", \
"Liquidity Level", \
"Negative TU Information"]
result_lines_in_grey_square = [clientdv, \
'=VLOOKUP(\'Income Statement\'!$E$2, SIC!$A$4:$B$1008,2,FALSE)', \
'=VLOOKUP(\'Income Statement\'!$E$3, NAICS!$B$4:$C$2231,2,FALSE)', \
"Fill in here", \
paymentdv, \
oadv, \
liquiditydv, \
tudv]
account_background_sentences = ['=CONCATENATE("This is a ",\'User Input\'!B2," client of Citi. At present, client also incorporated bank accounts in Citi for ",$G$7," activities.")', \
'=CONCATENATE("The company engages in ",$G$6," business, sales proceeds collection was captured in bank account.")', \
'=CONCATENATE("With reference to ",\'User Input\'!B3," bank statement, about ",DOLLAR(Final!D27)," credit transaction was recorded. The annualised sales proceeds was estimated to be ",DOLLAR(Final!D28),". If subject have more than 2 banks, state the transaction figures and periods.")', \
'Such figure could serve as an estimate for annual sales of subject borrower. ', \
'=CONCATENATE("As at ",TEXT(MONTH(\'Bank Statement\'!A44),"mmm")," ",YEAR(\'Bank Statement\'!A44),", client is keeping about ",DOLLAR(\'Bank Statement\'!E43)," bank balance in Citibank. ")',\
'=CONCATENATE("No negative record could be found in TU and CCRA on ", TEXT(MONTH(\'Bank Statement\'!A44),"mmm")," ",YEAR(\'Bank Statement\'!A44),".")', \
'=CONCATENATE(G8," instalment loans was respectively booked for BankA (Loan Amount, number of years) e.g. HKD 5MM for 10 years, and it is revealed by TU repayment of these instalment has been ",G9,".")', \
'=CONCATENATE("As per bank statement from Citibank provided, it is observed that Subject received ",DOLLAR(Final!D28)," throughput (Annualized: $$$)")', \
'==CONCATENATE("AO confirmed that aggregated operating account for $$$ is considered ",G10)', \
'=CONCATENATE("Average free cash flow is around ",DOLLAR(Final!E27)," in bank statement. ", \'User Input\'!B7," liquidity is observed.")', \
'=CONCATENATE("Negative information is ",G12, " on TU.")']
types_of_data_validation = [clientdv, oadv, liquiditydv, tudv]
for i in range(1, r + 1):
for j in range(1, c + 1):
summary.cell(row=i, column=j).fill = greyFill
summary.cell(row=i, column=j).font = whitebodystyle
if j == 4:
if i == 2:
# heading style
summary.cell(row=i, column=j).font = whiteheadingstyle
# add filler lines
summary.cell(
row=i, column=j
).value = 'ACCOUNT BACKGROUND (For your designated usage)'
elif i > 3 and i < 15:
# add account background sentences
summary.cell(
row=i,
column=j).value = account_background_sentences[i - 4]
summary.cell(row=i, column=j).alignment = Alignment(
wrap_text=Alias('wrapText'), wrapText=Bool(False))
# labelling the sentences
if i > 3 and i < 15 and j == 2:
summary.cell(row=i, column=j).value = i - 3
# create grey square in excel, with designated font
for i in range(2, 14):
for j in range(5, 9):
if i == 4 and j == 6:
summary.cell(row=i, column=j).fill = greyFill
summary.cell(row=i, column=j).font = whitebodystyle
elif i == 4 and j == 7:
summary.cell(row=i, column=j).fill = greyFill
summary.cell(row=i, column=j).font = whitebodystyle
else:
summary.cell(row=i, column=j).fill = whiteFill
summary.cell(row=i, column=j).font = greybodystyle
# add filler lines and data validation
if j == 6 and i < 13:
summary.cell(
row=i, column=j).value = filler_lines_in_grey_square[i - 2]
if i > 4:
if type(result_lines_in_grey_square[i - 5]) == str:
summary.cell(row=i, column=j +
1).value = result_lines_in_grey_square[i -
5]
summary.cell(row=i,
column=j + 1).alignment = Alignment(
wrap_text=Alias('wrapText'),
wrapText=Bool(False))
else:
summary.add_data_validation(
result_lines_in_grey_square[i - 5])
result_lines_in_grey_square[i - 5].add(
summary.cell(row=i, column=j + 1))
summary.cell(row=i, column=j +
1).value = "Select from drop-down"
if j == 7 and i == 3:
summary.cell(row=i, column=j).value = "To be filled"
# set the width of the column
specialcolumns = ['A', 'B', 'C', 'E', 'H']
for i in specialcolumns:
summary.column_dimensions[i].width = width
summary.column_dimensions['D'].width = 105
summary.column_dimensions['F'].width = 29
summary.column_dimensions['G'].width = 49.64
# adjust zoom level
summary.sheet_view.zoomScale = 85
# Save the file
wb.save("static/output/output.xlsm")
