def parse_sheet_protection(self, element):
values = element.attrib
self.ws.protection = SheetProtection(**values)
password = values.get("password")
if password is not None:
self.ws.protection.set_password(password, True)
def parse_sheet_protection(self, element):
self.ws.protection = SheetProtection.from_tree(element)
password = element.get("password")
if password is not None:
self.ws.protection.set_password(password, True)
def parse_sheet_protection(self, element):
self.ws.protection = SheetProtection.from_tree(element)
password = element.get("password")
if password is not None:
self.ws.protection.set_password(password, True)
def update_sheet(self, sheet_nm, columns, data, tf):
"""
Create or Update excel sheet with db data and cf.
:param sheet_nm: sheet name
:param columns: column names
:param data: Database data that needs to be exported
:param tf: Table cf. Can be None
:return:
"""
logging.debug(f'Creating/Updating name [{sheet_nm}]')
if not columns or not data:
logging.error(
f'[{"columns" if not columns else "data"}] required but received None'
)
sheet = self._get_sheet_by_name(ws_name=sheet_nm,
read=False,
ws_details=tf)
col_lock = False
sheet.append(columns)
if len(data) <= 0:
logging.error(f'No values to insert for [{sheet_nm}]')
sheet["$1$1"].comment = 'No data available for insert'
else:
for d in data:
sheet.append(d)
for col in columns:
cf = tf.get_column(col, default=True)
sheet.column_dimensions[
cf.column_number].width = cf.formatters.width
if cf.formatters.comment:
sheet[
f'${cf.column_number}$1'].comment = cf.formatters.comment
cr = cf.formatters.get('reference', None)
if cr and cf.formatters.get('dv', True):
dv = DataValidation(type="list",
formula1="{0}!{1}:{2}".format(
quote_sheetname(cr.sheet_name),
cr.startcell, cr.endcell))
dv.add('{0}2:{0}{1}'.format(cf.column_number,
len(data) + 1))
sheet.add_data_validation(dv)
if tf.formatters.alignment.wrapText is True:
for cell in sheet[cf.column_number]:
cell.alignment = Alignment(wrapText=True)
if cf.formatters.locked or tf.formatters.locked:
col_lock = True
cell.protection = Protection(locked=True)
else:
cell.protection = Protection(locked=False)
# Other Worksheet level settings
sheet.alignment = tf.formatters.alignment
sheet.freeze_panes = tf.formatters.freeze_panes
sheet.add_table(
openpyxl.worksheet.table.Table(
ref="%s" % sheet.dimensions,
displayName=sheet_nm.replace(" ", ""),
tableStyleInfo=tf.formatters.table_style_info))
if tf.formatters.locked:
sheet.protection.sheet = True
elif col_lock:
sheet.protection = SheetProtection(sheet=True,
selectLockedCells=False,
selectUnlockedCells=False,
objects=True,
scenarios=True,
formatCells=True,
formatRows=True,
formatColumns=True,
insertColumns=True,
insertRows=True,
insertHyperlinks=True,
deleteColumns=True,
deleteRows=True,
sort=True,
autoFilter=True,
pivotTables=True,
password=None)
self._wb.save(self._filename)
return