def test_workbook_security(self, datadir, WorkbookParser):
expected_protection = WorkbookProtection()
expected_protection.workbookPassword = '******'
expected_protection.lockStructure = True
datadir.chdir()
archive = ZipFile("workbook_security.xlsx")
parser = WorkbookParser(archive, ARC_WORKBOOK)
parser.parse()
assert parser.wb.security == expected_protection
def __generateExcel(self, *args):
from openpyxl import load_workbook
from openpyxl.styles import Protection
from openpyxl.workbook.protection import WorkbookProtection
workbook = load_workbook('resources/format/prepare.xlsx')
for item in args[1]:
data = self.__filter.loc[self.__filter['Jabatan'] == item]
for i in range(len(data)):
workbook.active.protection.sheet = True
workbook.active.protection.password = '******'
for x in range(3, 35):
workbook.active['G' + str(x)].protection = Protection(locked=False)
workbook.active['L' + str(x)].protection = Protection(locked=False)
workbook.active['Q' + str(x)].protection = Protection(locked=False)
workbook.active['B1'].value = data.Nama.values[i]
workbook.active['G1'].value = data.NIM.values[i]
workbook.active['J1'].value = data.Jabatan.values[i]
workbook.active['N1'].value = data.Nama.values[i]
workbook.active['S1'].value = data.Nama.values[i]
workbook.active.title = str(data.NIM.values[i])
workbook.copy_worksheet(workbook.active)
workbook.active = len(workbook.sheetnames) - 1
workbook.remove(workbook.active)
workbook.security = WorkbookProtection(workbookPassword='******', lockWindows=True, lockStructure=True)
workbook.save("resources/output/prepare/{}.xlsx".format(args[0]))
workbook.close()
def protect_book_without_password():
""" ブックをパスワード無しで保護する """
wb = _load(NO_PROTECTION_FILE)
# ブックを保護
wb.security = WorkbookProtection()
wb.security.lockStructure = True
# 保存
_save(wb, f'No_1_PROTECT_book_using_{NO_PROTECTION_FILE}')
def protect_book_with_password():
""" ブックをパスワード付きで保護する """
wb = _load(NO_PROTECTION_FILE)
# ブックを保護
wb.security = WorkbookProtection()
wb.security.lockStructure = True
wb.security.workbook_password = PASSWORD_FOR_BOOK
# 保存
_save(wb, f'No_2_PROTECT_book_using_{NO_PROTECTION_FILE}')
from openpyxl import load_workbook
from openpyxl.workbook.protection import WorkbookProtection
wb = load_workbook('16.quote.xlsx')
# protect a workdbook
wb.security = WorkbookProtection(
workbookPassword='******', lockStructure=True)
wb.save('16.quote1_changed.xlsx')
def fillDimension(self):
from openpyxl import load_workbook
from openpyxl.styles import Protection
from openpyxl.workbook.protection import WorkbookProtection
workbook = load_workbook('resources/output/process/Summaries.xlsx')
for pop in self.__popRes:
for _ in pop:
for index in pop[_]:
try:
for k, v in index.items():
init = 3
workbook.active = workbook[str(k)]
while workbook.active['E{}'.format(
str(init))].value != None:
init += 1
workbook.active.protection.sheet = True
workbook.active.protection.password = '******'
for x in range(3, 100):
workbook.active[
'H' + str(x)].protection = Protection(
locked=False)
workbook.active[
'I' + str(x)].protection = Protection(
locked=False)
workbook.active[
'J' + str(x)].protection = Protection(
locked=False)
workbook.active[
'K' + str(x)].protection = Protection(
locked=False)
workbook.active[
'L' + str(x)].protection = Protection(
locked=False)
workbook.active[
'M' + str(x)].protection = Protection(
locked=False)
workbook.active[
'N' + str(x)].protection = Protection(
locked=False)
workbook.active[
'O' + str(x)].protection = Protection(
locked=False)
workbook.active['E{}'.format(
str(init))].value = v[8][0]
workbook.active['F{}'.format(
str(init))].value = v[8][1]
workbook.active['G{}'.format(
str(init))].value = v[8][2]
workbook.active['H{}'.format(
str(init))].value = v[0]
workbook.active['I{}'.format(
str(init))].value = v[1]
workbook.active['J{}'.format(
str(init))].value = v[2]
workbook.active['K{}'.format(
str(init))].value = v[3]
workbook.active['L{}'.format(
str(init))].value = v[4]
workbook.active['M{}'.format(
str(init))].value = v[5]
workbook.active['N{}'.format(
str(init))].value = v[6]
workbook.active['O{}'.format(
str(init))].value = v[7]
except Exception as e:
print(e)
workbook.security = WorkbookProtection(workbookPassword='******',
lockWindows=True,
lockStructure=True)
workbook.save("resources/output/process/Summaries.xlsx")
workbook.close()
from openpyxl import load_workbook
from openpyxl.workbook.protection import WorkbookProtection
workbook = load_workbook("C:\\testtemp\\Jim.xlsx",
read_only=False,
keep_vba=True)
workbook.security = WorkbookProtection(workbookPassword='******',
lockStructure=True)
workbook.save("C:\\testtemp\\Harry.xlsx")
from openpyxl import load_workbook
from openpyxl.workbook.protection import WorkbookProtection
# security
licence_workbook = load_workbook("database-Licence.xlsx",
read_only=False,
keep_vba=True)
licence_workbook.security = WorkbookProtection(
workbookPassword='******',
revisionsPassword='******',
lockWindows=True,
lockStructure=True,
lockRevision=True)
licence_workbook.save("database-Licence.xlsx")
licence_workbook.close()