Has anyone found that FME unprotects excel worksheets when it writes to them?
I have a worksheet that I need to keep protected with certain cells being written to only. The rest of the sheet has loads of complex formulae which need to be kept protected.I have protected the sheet but when FME writes it unprotects it without the password.
Does anyone know how I can stop FME from unprotecting the worksheet?
@sebkingsley
Yes. Use an FME Excel template and an emailer transformer. The emailer will protect the spreadsheet when it emails it to people.
I've just run into this issue, the file I'm populating is owned by a third party and they've protected parts of the sheets/workbook, the result needs to be protected
I've tried using their original file as the template, and writing to a copy of the file (rather than a using as a template), both approaches result in an unprotected result file.
The emailer trick suggested is not a solution, is there anything I'm missing here?
edit: this is listed as a bug here https://knowledge.safe.com/content/idea/46106/bug-excel-writer-removes-all-worksheet-protections.html
I solved this issue by using a shutdown python script with openpyxl library to lock an entire sheet then unlock a specific cell.
import openpyxl
from openpyxl import Workbook
from openpyxl.styles import Protection
wb = openpyxl.load_workbook('wb.xlsx')
sheet = wb.active
sheet.protection.sheet = True
sheet.'D2'].protection = Protection(locked=False)
wb.save('wb.xlsx')