Skip to main content
  • Home
  • Ideas
  • Bug: Excel Writer Removes All Worksheet Protections From Existing Excel Template
Archived

Bug: Excel Writer Removes All Worksheet Protections From Existing Excel Template

Related products:FME Form
J

***Note from Migration:***

Original Title was: Bug: Excel Writer Removes All Worksheet Protections From Existing Excel Template File


The Excel writers in FME 2016, 2017, and 2017 Beta will completely remove all existing Worksheet protections from an Excel template file. Even though FME doesn't currently support setting passwords on Worksheets to protect them (another useful feature that should be added soon please), the conversion process should not be stripping all protections from existing Worksheets, so that's a bug.

In our use case we have a customer that has protected various pricing sheets with a password, and we are using FME to write out some data to other regular unprotected worksheets in the same template workbook, but in doing so all of the existing protections are being removed.

It's easily reproduced by:

1) Create an Excel file with 2 worksheets, add protection to one of them

2) Add an Excel Reader to read it in as a template file and an Excel Writer for the same.

3) Write out some values to the unprotected sheet, and then notice that the protections on the first sheet are gone.

This post is closed to further activity.
It may be a question with a best answer, an implemented idea, or just a post needing no comment.
If you have a follow-up or related question, please post a new question or idea.
If there is a genuine update to be made, please contact us and request that the post is reopened.

8 replies

fmelizard
Contributor
Forum|alt.badge.img+17
  • fmelizardContributor
  • Contributor
  • June 20, 2017

Hi @jeremyt. I believe that in Excel passwords can apply at either the Worksheet/Cell or Workbook level. FME supports the former but not the latter as FME 2017.0. We do plan to add support for passwords and encryption of the Workbook (whole file), however this is stalled on LibXL 3rd party library support.

Since using a template or writing to an existing Workbook or Worksheet involves reading it fully into memory and then rewriting the contents again, we would only be able to preserve features that we support on both write and read.

This does seem like a shortcoming if we cannot preserve the worksheet password/encryption that exists when writing to the file. A simple workaround might be to explicitly request password protection on existing worksheets. I have notified Development and will let you know what I receive any updates.
J
  • jeremyt
  • Author
  • June 20, 2017
Hi Natalie,

 

 

Thanks for responding, and we hope Development can add this fix to the next FME release. In the meantime, the only workaround that we were able to find is very tedious, but might point your development team in a possible direction to take. We're using the ZipExtractor transformer to unzip the Excel template file that has sheet protection and manipulate the raw XML inside. We then search for and extract out the XML element named "sheetProtection" inside of the "sheet~.xml" which looks similar to this and contains the password hash:

 

<sheetProtection algorithmName="SHA-512" hashValue="qYms0oZIYZahsCoxBbANbUBL1/Y==" saltValue="OPtz7++g2hUSyPg==" spinCount="100000" sheet="1" objects="1" scenarios="1" formatCells="0" formatRows="0" insertColumns="0" insertRows="0" insertHyperlinks="0" deleteColumns="0"/>We take that element and add it manually into the appropriate "sheet~.xml" contained in the output excel file, so that the stripped out protections are reapplied. We then zip up the XML file components to reassemble the new Excel output file. It's a pain, but it works for now. ;)

 

 

Thanks,

 

Jeremy

 

fmelizard
Contributor
Forum|alt.badge.img+17
  • fmelizardContributor
  • Contributor
  • July 12, 2017
Hi @jeremyt. Development can't seem to replicate this in 2017.0 (build 17287) or 2017.1 (build 17481). In the , there is an excel file with two sheets, one protected, and one not. The workspace will write some features to the unprotected sheet. Opening the excel file after, the protected sheet is still protected.

 

If you add workbook protection to the excel workbook in the attached repro (in excel: `Review` -> `Protect Workbook`), we can still write to the unprotected sheet while leaving protections on both the protected sheet and workbook intact, so it looks like we can preserve some aspects of the excel file that we do not support on read and/or write.

 

 

What protection settings are applied to the protected sheets in your workbook (you can selectively protect various aspects of a sheet)?

 

 

Also, note that in FME "Protecting" a workbook (in excel: "Review -> Protect Workbook") is different than as "Encrypting" it (in excel: "File -> Permissions -> Protect Workbook -> Encrypt With Password"). Are you protecting or encrypting?

 

 

Thank you!

 

repro.fmwt
D
Hi Natalie I worked with Jeremy on this problem.

 

 

We were using worksheet level protection and not encryption.

 

 

Is this just a case where since we are creating a new Excel file and not editing an existing one that the worksheet protections are ignored? If that's the case this suggestion should be to add preserving the sheet protection as an option when writing with an excel template.

 

 

I believe in the above XML code, values with a "1" are the protection options that are turned on in Excel.

 

 

fmelizard
Contributor
Forum|alt.badge.img+17
  • fmelizardContributor
  • Contributor
  • July 17, 2017
Thank you for the reply @danielbarber, I have passed this information along to development and will let you know when I receive a reply.

 

fmelizard
Contributor
Forum|alt.badge.img+17
  • fmelizardContributor
  • Contributor
  • July 19, 2017
Hi @danielbarber and @jeremyt. Unfortunately Developement hasn't been able to reproduce this issue with FME 2017.0 (17287) or 2017.1 (17481). Do either of you have a repro workspace (with a small subset of sample data) (you could bundle it up as a workspace template and attach to this question) that you would be able to share with Development? Thank you.
stevenjh
Contributor
Forum|alt.badge.img+1
  • stevenjhContributor
  • Contributor
  • May 6, 2019

Hi @natalieatsafe I was just looking at this in fme 2019 and the issue still exists, seems solvable looking at what @jeremyt & @danielbarber posted. After the translate all cells are editable vs the template where only cells A3:C## are. I've tried to attached an example case but it denied the upload so a template file can be found here https://www.dropbox.com/s/4s6pp42m59e2kke/none2xlsxw.fmwt?dl=0 Thanks!

R
  • raychien
  • October 25, 2019

Hi there, we are using FME 2018.1 right now and are also running into the exact same issue: The template file contains both protected, and unprotected sheets. After FME generation, all protections are removed.

The Excel Worksheet writer has an option to 'Protect sheet', however, this option seems to get applied to all of the Worksheets within the same Workbook. Even if there are other Worksheets with 'Protect sheet' unchecked, they still end up getting protected. So, it seems to be an all-or-nothing protection.

Most helpful members this week

Community Stats

30,845
Posts
116,922
Replies
38,663
Members
Terms & ConditionsCookie settings

Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings
A product of
Safe Software respectfully acknowledges that we live, learn and work on the traditional and unceded territories of the Kwantlen, Katzie, and Semiahmoo First Nations.