def _validate_list(cell, data, _list, blank=True, _var=None, _sh=None):
from openpyxl.worksheet.datavalidation import DataValidation
cell.value = data
dv = DataValidation(type="list", formula1='"{}"'.format(_list), allow_blank=blank)
_sh.add_data_validation(dv)
dv.add(cell)
return cell.value
def parse_data_validation(self, element):
for node in safe_iterator(element, "{%s}dataValidation" % SHEET_MAIN_NS):
dv = DataValidation.from_tree(node)
self.ws._data_validations.append(dv)
def _set_validation_(data_sheet: openpyxl.workbook.workbook.Worksheet,
idx: str,
vsheet,
is_global=False):
"""
Sets the validation for data_sheet, limiting values to those found in column A of vsheet
:param data_sheet: The data sheet to which validation is to be applied
:param idx: the column letter (if is_global=False) or row number (if is_global=True) of the cells to validate
:param vsheet: The worksheet from which valid values are to be drawn (Drawn from column A)
:param is_global: Whether data_sheet contains global data. Default False
:return:
"""
validation_title = vsheet.title
# This DataValidation object specifies that a list rule is to be used,
# drawing its values from column A of vsheet, and allowing null values
dv = DataValidation(
type="list",
formula1=
f"{quote_sheetname(validation_title)}!$A$2:$A${vsheet.max_row}",
allow_blank=True)
# Cell address needs to be different for globals vs utterances
# For globals, each row's B cell has it's own validation rule
# For utterances, each column that has codes has its own validation rule.
# Set cell address accordingly.
column = idx if not is_global else "B"
cell_address = f"B{idx}" if is_global else f"{column}2:{column}{data_sheet.max_row}"
dv.add(cell_address)
data_sheet.add_data_validation(dv)
def __set_value(self, cell, str):
""" determine the number format of the value in str and add it to a cell
:param cell:
:param str:
:return: None
"""
str = str.strip()
if str.endswith('%'):
value = float(str[:-1]) / 100
cell.value = value
cell.number_format = '0.00%'
return
parts = str.split('-')
if len(parts) == 3:
value = v = datetime(int(parts[0]), int(parts[1]), int(parts[2]))
cell.value = value
cell.number_format = "YYYY-MM-DD"
return
if str.startswith('['):
i0 = str.find('[')
i1 = str.find(']')
list_str = str[i0 + 1:i1]
value = str[i1 + 1:]
dv = DataValidation(type="list", formula1=f'"{list_str}"', allow_blank=False)
dv.add(cell)
self.ws.add_data_validation(dv)
cell.value = value
return
cell.value = float(str)
cell.number_format = "0.00"
def _validate_list(cell, data, _list, blank=True, _var=None, _sh=None):
cell.value = data
dv = DataValidation(type="list",
formula1='"{}"'.format(_list),
allow_blank=blank)
_sh.add_data_validation(dv)
dv.add(cell)
return cell.value
def _add_validation(worksheet):
operations = [""] + VALID_OPERATIONS + OPERATIONS_TO_IGNORE
operation_data_validation = DataValidation(type="list", formula1='"%s"' % (','.join(operations)))
worksheet.add_data_validation(operation_data_validation)
for header_cell in worksheet[1]:
if header_cell.value == OPERATION_COLUMN:
letter = header_cell.column_letter
operation_data_validation.add(f"{letter}2:{letter}{worksheet.max_row}")
def _validate_list(cell, data, _list, blank=True, _var=None, _sh=None):
from openpyxl.worksheet.datavalidation import DataValidation
cell.value = data
dv = DataValidation(type="list",
formula1='"{}"'.format(_list),
allow_blank=blank)
_sh.add_data_validation(dv)
dv.add(cell)
return cell.value
def add_validation(self, validation_string):
self.sheet.insert_rows(1)
dv = DataValidation(type="list",
formula1=validation_string,
allow_blank=True)
self.sheet.add_data_validation(dv)
cell = self.sheet["A1"]
#cell.value = "Dog"
dv.add(cell)
def setDropDown(ws, pullTab=None, colName=None, colStr=None, fm1=None):
if fm1 == None:
s = ",".join(pullTab[colName].dropna().unique())
fm1 = f'"{s}"'
dv = DataValidation(type="list", formula1=fm1)
dv.ranges = f'{colStr}3:{colStr}1048576'
ws.add_data_validation(dv)
return (ws)
def generate_input_form(db: Db, path):
# retrieve data
db_d = dict()
for table in db:
table_d = dict()
db_d[table.get_ref()] = table_d
schema = table._dev_schema
for k, v in schema.declared_fields.items():
field_d = dict()
table_d[k] = field_d
choices = None
if getattr(v, "validate", None) is not None and isinstance(
v.validate, validate.OneOf):
choices = v.validate.choices
field_d["choices"] = choices
wb = Workbook()
wb.remove_sheet(wb.active) # remove default sheet
# choices worksheet
ws = wb.create_sheet("choices")
ws.cell(1, 1).value = "Table ref"
ws.cell(1, 2).value = "Var name"
ws.cell(1, 3).value = "Choices"
# initialize and loop
i = 1
choices_d = dict() # (table_ref, field_name): range
for table_ref, table_data in db_d.items():
for field_name, field_d in table_data.items():
if field_d["choices"] is not None:
i += 1
ws.cell(i, 1).value = table_ref
ws.cell(i, 2).value = field_name
choices_nb = len(field_d["choices"])
for k in range(choices_nb):
ws.cell(i, 3 + k).value = field_d["choices"][k]
choices_d[(
table_ref, field_name
)] = f"choices!$C${i}:${get_column_letter(choices_nb + 3)}${i}"
# table worksheets
for table_ref, table_data in db_d.items():
ws = wb.create_sheet(table_ref)
for i, (field_name, field_data) in enumerate(table_data.items()):
ws.cell(i + 1, 1).value = field_name
if field_data["choices"] is not None:
dv = DataValidation(type="list",
formula1=choices_d[(table_ref,
field_name)],
allow_blank=True)
ws.add_data_validation(dv)
dv.add(ws.cell(i + 1, 2))
ws.cell(i + 1, 2).fill = dropdown_fill
# save
wb.save(path)
def _create_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_excel(path, rows=1000, sheet_name="Sheet1"):
if len(path) > 3:
if path[-5:] == ".xlsx":
excel_path = path
else:
log.error("文件格式不正确")
return 0
else:
log.error("文件不正确")
return 0
wb = Workbook()
# wb = load_workbook(path)
wb.active
sheet = wb.create_sheet(sheet_name, 0)
sheet2 = wb.create_sheet("系统数据", 1)
# sheet = wb["Sheet1"]
titles = ["用例标题", "页面操作", "元素路径", "输入内容", "其他说明"]
types = [
"get", "input_text", "assertElementisExist", "assertText",
"assertElementText", "wait", "wait_elements", "refresh", "click",
"slow_click"
]
# sheet2=wb["Sheet2"]
for k, t in enumerate(types):
sheet2["A{}".format(k + 1)].value = t
border = Border(left=Side(border_style="thin", color="00000000"),
right=Side(border_style="thin", color="00000000"),
top=Side(border_style="thin", color="00000000"),
bottom=Side(border_style="thin", color="00000000"))
fill = PatternFill("solid", fgColor="4472C4")
font = Font(name="等线", size=11, bold=False, italic=False, color='00FFFFFF')
for k, title in enumerate(titles):
col = chr(ord("A") + k) + "1"
cell = sheet[col]
cell.border = border
cell.fill = fill
cell.font = font
cell.value = title
sheet.column_dimensions[chr(ord("A") + k)].width = 22
sheet.row_dimensions[1].height = 22
dv = DataValidation(type="list",
formula1="{0}!$A$1:$A${1}".format(
quote_sheetname("系统数据"), len(types)),
allow_blank=True)
sheet.add_data_validation(dv)
row = 2
for _ in range(rows):
dv.add(sheet["B{}".format(row)])
row += 1
wb.save(excel_path)
def makeDuplicateCheck(column_letter, sheet):
# an excel function to check for duplicates
# https://www.ablebits.com/office-addins-blog/2013/10/20/prevent-duplicates-in-excel-column/
formula = f"=(COUNTIF(${column_letter}:${column_letter}, {column_letter}2) = 1)"
# ensure ids are greater than zero
formula += f"AND(=${column_letter} > 0)"
_range = f"{column_letter}2:{column_letter}{max_rows}"
rule = DataValidation(type="custom", formula1=formula)
rule.add(_range)
sheet.add_data_validation(rule)
def _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 _alter_general_sheet(ws):
for row_idx in range(1, ws.max_row + 1): # pragma: no branch
if ws.cell(row_idx, 1).value.lower() == 'auto-translation':
disabled_enabled = DataValidation(
type='list',
formula1='"Disabled,Enabled"',
allow_blank=False,
)
ws.add_data_validation(disabled_enabled)
disabled_enabled.add(ws.cell(row_idx, 2))
break
def _set_global_validation_(data_sheet: openpyxl.workbook.workbook.Worksheet,
row_number: int, vsheet):
validation_title = vsheet.title
dv = DataValidation(
type="list",
formula1=
f"{quote_sheetname(validation_title)}!$A$2:$A${vsheet.max_row}",
allow_blank=True)
dv.add(f"B{row_number}")
data_sheet.add_data_validation(dv)
def main():
cm.sheet.insert_rows(1)
dv = DataValidation(type="list",
formula1='"alex,hannah,frog"',
allow_blank=True)
cm.sheet.add_data_validation(dv)
c1 = cm.sheet["A1"]
c1.value = "Dog"
dv.add(c1)
cm.save_doc(FILE_PATH)
def __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_source_column(first_file, destws):
for cell in destws['V']:
cell.value = "Ruffalo Cody"
dv = DataValidation(type="list", formula1='"Registrar: SIS Import, Ruffalo Cody"', allow_blank=True)
# Add the data-validation object to the worksheet
destws.add_data_validation(dv)
dv.add('V2:V1048576')
return 0
def _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 custom_action_type(self):
"""
[メソッド概要]
アクション部のバリデーション
アクション種別のセルに使用可能なアクション種別のリストを作成する。
<builtin_formats 参照>
https://openpyxl.readthedocs.io/en/stable/_modules/openpyxl/styles/numbers.html
"""
tables_ws = self.wb['Tables']
header_row = tables_ws[11]
target_header_text = 'アクション種別\n（必須）'
action_type_col = 0
for cell in header_row:
if cell.col_idx == 1:
# A列は無視
continue
if cell.value == target_header_text:
# hitしたら終了
action_type_col = cell.col_idx
break
if cell.value is None or cell.value == '':
# 空白を終端とする
break
if action_type_col == 0:
# hitできずに来たらエラー
raise Exception('No match action type col.')
dti = ActionType.objects.filter(disuse_flag='0').values_list(
'driver_type_id', flat=True)
rs = DriverType.objects.filter(driver_type_id__in=dti).values(
'name', 'driver_major_version')
ati = []
for r in rs:
name_version = r['name'] + '(ver' + str(
r['driver_major_version']) + ')'
ati.append(name_version)
acts = ','.join(ati)
acts_type_dv = DataValidation(type="list",
formula1='"{}"'.format(acts))
for r in range(12, 19):
acts_type_dv.add(tables_ws.cell(row=r, column=action_type_col))
for data_validation in tables_ws.data_validations.dataValidation:
# TODO 1シート内に入力規則が複数来たらバグる
tables_ws.data_validations.dataValidation.remove(data_validation)
tables_ws.add_data_validation(acts_type_dv)
def main():
filename = 'MadSkillz.xlsx'
role_pattern = '^## .+'
role_description_pattern = '>.+'
behaviour_pattern = '^- .+'
behaviour_description_pattern = '^#### .+'
row_number = 0
wb = Workbook()
active_ws = wb.active
data_validation = DataValidation(
type="list",
formula1=
'"Cannot Assess,Need Coaching,Maturing,No Brainer,Outstanding"',
allow_blank=False)
f = import_file_from_readify_github()
logging.info('Parsing...')
for line in f:
line = line.decode('unicode-escape')
row_number += 1
role = re.search(role_pattern, line)
if role:
active_ws = wb.create_sheet(0)
row_number = 1
active_ws.add_data_validation(data_validation)
active_ws.title = line.strip('#')
line = line.strip('#')
change_cell_value(row_number, 1, active_ws, line)
change_cell_style_header(row_number, 1, active_ws)
change_cell_style_header(row_number, 2, active_ws)
change_cell_value(row_number, 2, active_ws, "Rating")
behaviour = re.search(behaviour_pattern, line)
if behaviour:
change_cell_value(row_number, 1, active_ws, line)
rating_cell = active_ws.cell(row=row_number, column=2)
data_validation.add(rating_cell)
role_description = re.search(role_description_pattern, line)
if role_description:
line = line.strip('>')
change_cell_value(row_number, 1, active_ws, line)
change_cell_style_role_description(row_number, 1, active_ws)
c = active_ws.cell(row=row_number, column=1)
c.value = line
logging.info('Parsing...Done!')
wb.save(filename)
logging.info('Saved "%s"', filename)
def 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 set_applicable_experiments_in_xls(
applicable_experiments, spreadsheet, aggregate_ws_name):
"""Write drop-down list of applicable experiments into aggregate tabs."""
appl_exps_as_comma_delim_string = ", ".join(applicable_experiments)
# Note this requires string enclosed in quotes internally
cell_formula = '"{}"'.format(appl_exps_as_comma_delim_string)
aggregate_ws = spreadsheet[aggregate_ws_name]
# Clear the placeholder value in the answer cell
aggregate_ws["B27"].value = None
# Add the drop-down with the available options to that cell
appl_exps_dropdown = DataValidation(
type="list", formula1=cell_formula, allow_blank=True)
aggregate_ws.add_data_validation(appl_exps_dropdown)
appl_exps_dropdown.add("B27")
def addDataValidation(worksheet: Worksheet, cellFormulaDict: dict):
"""Adds data validation to the specified worksheet
on the listed cells mapped to the formula.
Arguments:
worksheet {Worksheet} -- [description]
cellFormulaDict {dict} -- Cell address is the key and the formula is the value
"""
if cellFormulaDict[CELL_IDENTIFIER["taskName"]] is not None:
taskDataValidation = DataValidation(
type="list",
formula1=cellFormulaDict[CELL_IDENTIFIER["taskName"]],
allow_blank=False)
worksheet.add_data_validation(taskDataValidation)
taskDataValidation.add(CELL_IDENTIFIER["taskName"])
def __init__(self,
header=None,
choices=None,
list_validation=None,
**kwargs):
self.choices = tuple(choices) if choices else None
self.list_validation = list_validation
self.to_excel_map = {
internal: excel
for internal, excel in self.choices
}
self.from_excel_map = {
excel: internal
for internal, excel in self.choices
}
# Setup maps before super().__init__() to validation of default value.
super(ChoiceColumn, self).__init__(header=header, **kwargs)
if self.list_validation and not self.data_validation:
self.data_validation = DataValidation(
type="list",
formula1="\"%s\"" %
",".join('%s' % str(excel)
for internal, excel in self.choices))
def 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 test_data_validations(WriteOnlyWorksheet):
from openpyxl.worksheet.datavalidation import DataValidation
ws = WriteOnlyWorksheet
dv = DataValidation(sqref="A1")
ws.data_validations.append(dv)
ws.close()
with open(ws.filename) as src:
xml = src.read()
expected = """
<worksheet xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships" xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
<sheetPr>
<outlinePr summaryRight="1" summaryBelow="1"/>
<pageSetUpPr/>
</sheetPr>
<sheetViews>
<sheetView workbookViewId="0">
<selection sqref="A1" activeCell="A1"/>
</sheetView>
</sheetViews>
<sheetFormatPr baseColWidth="8" defaultRowHeight="15"/>
<sheetData />
<dataValidations count="1">
<dataValidation allowBlank="0" showErrorMessage="1" showInputMessage="1" sqref="A1" />
</dataValidations>
</worksheet>"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def add_validation(self, validation_string):
'''
Inserts a new row then adds a data validation drop down list to cell 'A1'.
'validation_string' parameter contains list of values added to drop down list.
'''
try:
self.sheet.insert_rows(1)
dv = DataValidation(type="list", formula1=validation_string, allow_blank=True)
self.sheet.add_data_validation(dv)
cell = self.sheet["A1"]
dv.add(cell)
error_logger.logger('Drop down menu added')
except Exception as traceback_error:
statement = "Trouble with adding validation drop-down menu"
error_logger.logger(statement, traceback_error)
def write(self, data, factory, outputFilename=''):
# create worksheet
wb = Workbook()
ws = wb.active
formula = '"Profissão,Habitação,Transporte,Dependentes,Saúde,Bem-estar,Outros"'
# create data validation
dv = DataValidation(type="list", formula1=formula, allow_blank=True)
ws.add_data_validation(dv)
writerBS = factory.createWriterBankStatement()
for bs in data:
writerBS.write(bs, factory, [ws, dv])
if (outputFilename == ''):
with NamedTemporaryFile() as tmp:
wb.save(tmp.name)
tmp.seek(0)
stream = tmp.read()
wb.close()
return stream
else:
wb.save(filename=outputFilename + '.xlsx')
def _update_job_order_sheet(self, workbook):
try:
JobOrder_Sheet = workbook.get_sheet_by_name('Job Oder')
JobOrder_Sheet.protection.sheet = True
JobOrder_Sheet.protection.set_password('pabi2')
TahomaFont = Font(bold=False, name='Tahoma', size=10)
joborders = self.env['cost.control'].search([])
# Add cost control from odoo in cost control data sheet
row = 2
for joborder in joborders:
JobOrder_Sheet.cell(
row=row,
column=1,
value=joborder.cost_control_type_id.name).font = TahomaFont
JobOrder_Sheet.cell(row=row, column=2,
value=joborder.code).font = TahomaFont
JobOrder_Sheet.cell(row=row, column=3,
value=joborder.name).font = TahomaFont
JobOrder_Sheet.cell(
row=row, column=4,
value=joborder.name_short).font = TahomaFont
JobOrder_Sheet.cell(row=row, column=5,
value='').font = TahomaFont
JobOrder_Sheet.cell(row=row, column=6,
value='').font = TahomaFont
row += 1
formula1 = "{0}!$C$2:$C$%s" % (row)
JobOrdeListFormula = DataValidation(
type="list",
formula1=formula1.format(quote_sheetname('Job Oder')))
SHEET_FORMULAS.update({'job_order_formula': JobOrdeListFormula})
except:
pass
return True
def update_budget_xls(self, budget_ids, template_id=None):
if not template_id:
raise ValidationError(_('Please add .xlsx template.'))
if not budget_ids:
raise ValidationError(_('No budget to export.'))
budgets = self.env['budget.plan.unit'].browse(budget_ids)
for budget in budgets:
exp_file = self.attachment_id.datas.decode('base64')
stream = cStringIO.StringIO(exp_file)
workbook = openpyxl.load_workbook(stream)
ChargeType = DataValidation(type="list",
formula1='"External,Internal"')
SHEET_FORMULAS.update({'charge_type': ChargeType})
self._update_activity_group_sheet(workbook)
self._update_job_order_sheet(workbook)
self._update_non_joborder_sheets(budget, workbook)
stream1 = cStringIO.StringIO()
workbook.save(stream1)
filename = self._get_filename(budget)
self.env.cr.execute(""" DELETE FROM budget_xls_output """)
attachement_id = self.env['ir.attachment'].create({
'name':
filename,
'datas':
stream1.getvalue().encode('base64'),
'datas_fname':
filename,
'res_model':
'budget.plan.unit',
'res_id':
budget.id,
'budget_plan_id':
budget.id,
'description':
'Export',
})
attach_id = self.env['budget.xls.output'].create({
'name':
filename,
'xls_output':
base64.encodestring(stream1.getvalue()),
})
self.env['budget.plan.history'].create({
'user_id':
self.env.user.id,
'operation_date':
fields.Datetime.now(),
'operation_type':
'export',
'plan_id':
budget.id,
'attachement_id':
attachement_id.id
})
return attach_id
def 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 format_phone_number(worksheet):
"""Formats phone number by removing extra spaces and unnecessary characters"""
for col in worksheet.iter_rows(min_row=2, min_col=column_index_from_string('O'), max_col=column_index_from_string('O')):
for cell in col:
"""Excel rounds integers longers than 15 digits hence the large value in if statement below"""
if (type(cell.value) == float) or (type(cell.value) == int and cell.value > 100000000000000):
cell.fill = PatternFill(fgColor='FDAB9F', fill_type='solid')
break
elif cell.value is not None:
phone = str(cell.value)
cell.value = phone.replace('(', '').replace('-', '').replace(')', '').replace(' ', '').replace(' ', '').\
replace('#', '').replace('.', '').replace('+','').replace('=','')
if len(cell.value) > 10:
if cell.value.startswith('1') and (
cell.offset(row=0, column=-3).value == "Canada" or cell.offset(row=0,
column=-3).value == "United States Of America"):
cell.value = cell.value.replace('1', '')
else:
for key in country_codes:
if cell.value.startswith(key):
try:
if country_codes[key] == cell.offset(row=0, column=-4).value:
cell.value = cell.value.replace(key, '')
break
except:
pass
else:
cell.fill = PatternFill(fgColor='FDAB9F', fill_type='solid')
break
if len(str(cell.value)) > 10:
cell.fill = PatternFill(fgColor='FDAB9F', fill_type='solid')
try:
cell.value = int(cell.value)
except:
pass
for cell in worksheet['P']:
if (cell.offset(row=0, column=-1).value) is None:
continue
else:
cell.value = 'H'
(cell.offset(row=0, column=1).value) = 1
# Creates a data validation (drop down) object
dv = DataValidation(type="list", formula1='"H,B,C"', allow_blank=True)
dv2 = DataValidation(type="list", formula1='"0,1"', allow_blank=True)
# Add the data-validation object to the worksheet
worksheet.add_data_validation(dv)
worksheet.add_data_validation(dv2)
dv.add('P2:P1048576')
dv2.add('Q2:Q1048576')
return 0
def test_data_validation(worksheet):
from ..worksheet import write_datavalidation
from openpyxl.worksheet.datavalidation import DataValidation, ValidationType
ws = worksheet
dv = DataValidation(ValidationType.LIST, formula1='"Dog,Cat,Fish"')
dv.add_cell(ws["A1"])
ws.add_data_validation(dv)
xml = write_datavalidation(worksheet)
xml = tostring(xml)
expected = """
<dataValidations xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" count="1">
<dataValidation allowBlank="0" showErrorMessage="1" showInputMessage="1" sqref="A1" type="list">
<formula1>"Dog,Cat,Fish"</formula1>
</dataValidation>
</dataValidations>
"""
diff = compare_xml(xml, expected)
assert diff is None, diff
def _validate_list(cell, data, _list, blank=True, _var=None, _sh=None):
cell.value = data
dv = DataValidation(type="list", formula1='"{}"'.format(_list), allow_blank=blank)
_sh.add_data_validation(dv)
dv.add(cell)
return cell.value
def _format_sheet(self, Sheet):
# Background Color
CyanFill = PatternFill(
start_color='E0FFFF',
end_color='E0FFFF',
fill_type='solid',
)
SkyBlueFill = PatternFill(
start_color='71acd6',
end_color='71acd6',
fill_type='solid',
)
# Border Style
thin = Side(
border_style="thin",
color="000000"
)
border = Border(
top=thin,
left=thin,
right=thin,
bottom=thin
)
# Font Style
TahomaFont = Font(
bold=False,
name='Tahoma',
size=10
)
TahomaRedFont = Font(
bold=False,
name='Tahoma',
size=10,
color='FF0000'
)
TahomaBoldFont = Font(
bold=True,
name='Tahoma',
size=10
)
# Cell Type
DecimalNumber = DataValidation(
type="decimal",
operator="greaterThanOrEqual",
formula1=0
)
num_format = '#,##0.00'
ChargeTypeFormula = SHEET_FORMULAS.get('charge_type', False)
ExpenseAGFormula = SHEET_FORMULAS.get('expense_ag_list', False)
RevenueAGFormula = SHEET_FORMULAS.get('revenue_ag_list', False)
JobOrderFormula = SHEET_FORMULAS.get('job_order_formula', False)
# Adding drop-down formula to sheet
Sheet.add_data_validation(ChargeTypeFormula)
Sheet.add_data_validation(ExpenseAGFormula)
Sheet.add_data_validation(RevenueAGFormula)
Sheet.add_data_validation(JobOrderFormula)
Sheet.add_data_validation(DecimalNumber)
protection = Protection(locked=False)
startrow = 10
lastrow = self.editable_lines + startrow
# Formating Lines
for row in range(startrow, lastrow):
for col in range(1, 26):
Sheet.cell(row=row, column=col).fill = CyanFill
Sheet.cell(row=row, column=col).border = border
Sheet.cell(row=row, column=col).font = TahomaFont
if col == 1: # Charge Type Column
ChargeTypeFormula.add(Sheet.cell(row=row, column=col))
Sheet.cell(row=row, column=col).protection = protection
elif col == 2: # Activity Group Column
if Sheet.title == 'Expense':
ExpenseAGFormula.add(Sheet.cell(row=row, column=col))
else:
RevenueAGFormula.add(Sheet.cell(row=row, column=col))
Sheet.cell(row=row, column=col).protection = protection
elif col == 3:
JobOrderFormula.add(Sheet.cell(row=row, column=col))
Sheet.cell(row=row, column=col).protection = protection
elif col == 9: # Total Budget Column
col_F = get_column_letter(6)
col_G = get_column_letter(7)
col_H = get_column_letter(8)
value = "=%s%s*$%s$%s*$%s$%s" % (col_F, row,
col_G, row,
col_H, row)
Sheet.cell(row=row, column=col).value = value
Sheet.cell(row=row, column=col).number_format = num_format
elif col == 23: # Total of phased entries Column
col_K = get_column_letter(11)
col_V = get_column_letter(22)
value = "=SUM(%s%s:$%s$%s)" % (col_K, row,
col_V, row)
Sheet.cell(row=row, column=col).value = value
Sheet.cell(row=row, column=col).number_format = num_format
elif col == 24: # Total minus phased total Column
col_I = get_column_letter(9)
col_W = get_column_letter(23)
value = "=IF(%s%s-%s%s<>0,%s%s-%s%s,0)" % (col_I, row,
col_W, row,
col_I, row,
col_W, row)
Sheet.cell(row=row, column=col).value = value
Sheet.cell(row=row, column=col).number_format = num_format
elif col == 25: # Error Column
col_X = get_column_letter(24)
value = '=IF(ABS(%s%s)>0,"Error","")' % (col_X, row)
Sheet.cell(row=row, column=col).value = value
Sheet.cell(row=row, column=col).font = TahomaRedFont
elif col not in (4, 5):
Sheet.cell(row=row, column=col).protection = protection
DecimalNumber.add(Sheet.cell(row=row, column=col))
Sheet.cell(row=row, column=col).number_format = num_format
else:
Sheet.cell(row=row, column=col).protection = protection
# Formating TOTAL line
for col in range(1, 25):
Sheet.cell(row=lastrow, column=col).fill = SkyBlueFill
Sheet.cell(row=lastrow, column=col).border = border
Sheet.cell(row=lastrow, column=col).font = TahomaFont
if col == 1:
Sheet.cell(row=lastrow, column=col).value = 'TOTAL'
Sheet.cell(row=lastrow, column=col).font = TahomaBoldFont
if col > 7:
col_letter = get_column_letter(col)
value = "=SUM(%s%s:$%s$%s)" % (col_letter, startrow,
col_letter, row)
Sheet.cell(row=lastrow, column=col).value = value
Sheet.cell(row=lastrow, column=col).number_format = num_format
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
