search

ggreen86 / XLSX-Workbook-Class

VFP Class to Create an XLSX Workbook without Excel Automation or Installed
45 stars 16 forks source link

How to set Sheet Protection #110

Open rawilliamsjr opened 7 months ago

rawilliamsjr commented 7 months ago

loSheetProtection =goExcel.GetSheetProtection(lnWb, lnSh) loSheetProtection.Locked = .T. loSheetProtection.PASSWORD = "abc123" goExcel.SetSheetProtection(lnWb, lnSh, loSheetProtection)

Sets the sheet protection but does not apply a password.

Can you explain to me how to set the protection and password for the sheet?

Starfiresg1 commented 6 months ago

The current protection implemenation seems to basically support reading the protection from an existing file and restoring it when saving the file. Excel does not store the password itself but only an 16bit Hash in hexadecimal notation. This is what is read from the file and stored in the "password"-property of the SheetProtection-object. Currently this class doesn't seem to have a funtion to generate a protection-hash. You can look at https://chicago.sourceforge.net/devel/docs/excel/encrypt.html for an explantion how the hash is calculated and create one yourself for your desired password. You can test this by setting the password to a 4-digit hex-value - when you try to unprotect the sheet Excel should ask for a password. If you set it to an random string that ist not a valid hash Excel will simply ignore it - as it does in your test case above.

Note: When opening an existing file you will only be able to get the hashed password - the original password ist not stored at all. You can hoewever remove it by emptying the password-property.