search

ucsd-ccbb / qiimp

Web application to collect metadata specifications from an experimenter and produce metadata input files with appropriate constraints
3 stars 7 forks source link

Possible to unhide hidden tabs without inputting password #140

Open AmandaBirmingham opened 6 years ago

adswafford commented 5 years ago

@AmandaBirmingham I think this issue is no longer a problem

adswafford commented 5 years ago

Nevermind, just tested and it is still an issue.

AmandaBirmingham commented 5 years ago

@adswafford TL;DR: Implementing this would either slow down performance considerably or require a major rewrite, and thus probably isn't worth it. Can I interest you in a "DO NOT DELETE" message of some kind on the hidden sheets instead? :)

Details: I've done some looking into this. Excel doesn't have a facility to JUST password protect hiding/showing sheet(s); what it has is a "Protect Workbook" option with a "Protect Structure" setting. When this is turned on, NO sheet in the workbook can be moved, deleted, hidden, or unhidden, and no new sheets can be added:

screen shot 2018-08-31 at 4 53 28 pm

So this is sort of the nuclear option.

It does not appear that workbook protection (as opposed to worksheet protection, which is already in use in QIIMP) is implemented in the xlsxwriter library that QIIMP uses to create new excel spreadsheets. It IS implemented in openpyxl (which QIIMP uses to read in existing workbooks); see https://openpyxl.readthedocs.io/en/stable/protection.html :

screen shot 2018-08-31 at 5 27 51 pm

However, loading large workbooks (like those created by QIIMP) into openpyxlis slow--on the order of seconds. This means that writing a workbook with existing code (using xlsxwriter) and then opening it with openpxyl to protect its structure is a non-starter from a performance perspective. It might be theoretically possible to refactor QIIMP to do all its excel creation with openpyxl instead of with xlsxwriter but (a) I don't know if openpyxl supports all the functionality we need, and (b) it would probably be a lot of coding work.

adswafford commented 5 years ago

Based on what you described, the DND option sounds reasonable. While I am okay with preventing users from creating or deleting new sheets inside the workbook (the nuclear option), it sounds like it would still be too much effort at this time to refactor it, especially since doing so would potentially lead to new and no-doubt interesting bugs..