Embedding formulas, related issues

I am about to design support for sheets with embedded formulas, and among the various concerns is that of a user inadvertently overriding or altering a formula in the sheet. So I am now considering strategies through which these things could be done in a more robust way. 


Cell locking and page protection are obvious tools to apply, but as some of the calculations will involve data in hidden columns, I am wondering also about ways of protecting these columns from alteration. One thought was to place such data on a separate hidden sheet, so the displayed sheet would have no hidden columns. That notion led to the "very hidden" sheet setting which is accessible through the VB editor in Excel. Does FlexCel provide access to these settings?

I must apologize for this question being very broad, but I am only beginning to explore the possible solutions, and cannot yet narrow things much, as I am unsure of the range of tools available.

About very hidden sheets, yes, we support it. Just do:


xls.SheetVisible := TXlsSheetVisible.VeryHidden;

Now, about protecting the file, I think you are in the right track: There aren't too many tools to "protect" a file, but basically you can make the full thing readonly, you can protect cells, or yu can use hidden sheets.

Of course, none of those protections are actual "protections", and anyone with google at their disposal is going to be able to "unprotect" those protections in minutes. they can remove sheet/workbook protections even if using passwords, and they can write a macro that makes the "very hidden" sheets visible. And if they don't have the technical skills, they can just google for tools that do it.

The problem here is the same that DRM solutions have: Excel must be able to read and modify the file, but somehow the user must not be able. That is not really possible, by design, so the only choice is to do "security by obscurity" and try to make it harder for someone to unprotect it. But given the popularity of Excel, and the easy for users to google what others find, I seriously doubt Microsoft can hide or "obscure" anything in it: Someone somewhere will find it, and will publish it on the web, and some other guy will make a tool that uses that information. As an example, Excel used to encrypt the xls files with a password: "VelvetSweatshop" when you protected sheets but not the file. Of course, someone found out, and now everyone knows it too. And even FlexCel knows that when it has a protected file without password, it has to try with VelvetSweatshop. So in xlsx, they don't even try to obscure it: The protection is just a tag that says "I am protected", and there is no obfuscation. You can just unzip the xlsx file, search for the tag in the xml data, and delete it, and the file will magically be "unprotected". Or just get a tool that will do it for you. 

The only protection that actually protects anything is the "password to open", because if you don't have the password, not you or Excel will be able to read the file. (And the encryption is ok, they use AES, so if your password is strong enough, you can trust nobody will be able to open the file). But once you have the password to open, now Excel can read the file, and so can you or any other tool. So any further protection is useless.

With that advise given, protections do have a place as you said to avoid someone (it might be yourself) inadvertently modifying something. But I have to wonder if you can inadvertently unhide a column, or if that is something you do on purpose. If you think there is a chance that you might unhide columns by mistake, then a very hidden sheet is the solution I think. But if the idea is to prevent someone changing the file on purpose, neither protection is good, and you might just hide the columns instead.


Thanks, Adrian. I realize I cannot make the data secure, but I can at least make it take some effort to alter it. My chief concern is to make it hard for people to alter by accident a cell with a formula. Secondarily, I want to make the hidden content less apparent -- if on very hidden sheets, then no column IDs are "missing" in the primary sheet.