3 Read Only And Protected Sheets - essenius/FitNesseFitSharpExcel GitHub Wiki

Workbooks can be made read-only, i.e. requiring a password to be able to change. If you open these normally, you would get a challenge dialog as follows:

Of course, you do not want to get user dialogs when executing automated tests. That’s why there is a function Load Read Only which bypasses this dialog if there is a write protection. If your Excel workbook is read-only, then your SuiteSetup would look as follows:

!|script      |Excel                         |
|Load Workbook|ExcelFixtureTest.xlsm|readonly|

You can also do this for read-write enabled sheets, so if you don't intend to update the sheets it's a good practice to open it read-only. That's why you saw this happen on the previous pages.

Individual worksheets can also be protected with a password, and you may need to be able to remove the protection, or enable the protection to test whether that all works as expected.

If you want to protect an unprotected sheet, you can use the Protect worksheet with password function. If the sheet was already protected, the function will fail. Likewise, you can also use Unprotect worksheet with password to remove the protection. Obviously, if the password is wrong, the operation will fail. There are also functions to check if worksheets are protected.

We assume you use the same SuiteSetup and SuiteTeardown as the previous page.

Here is an example test page that shows the capabilities:

Check initial state - neither the workbook nor the sheet  are protected

!|script                        |
|reject|workbook is protected   |
|ensure|Select Work sheet|Sheet2|
|reject|worksheet is protected  |

Protect sheet and check if indeed cells can't be changed

!|script                                         |
|ensure|protect worksheet with password|secret   |
|ensure|worksheet is protected with password     |
|reject|Set value of cell              |$a$1|to|1|

Check if unprotect with empty password fails

!|script                                           |
|reject|unprotect worksheet with password|         |
|ensure|worksheet is protected                     |
|reject|Set value of cell                |$a$1|to|2|

Check if unprotect with wrong password fails

!|script                                       |
|reject|unprotect worksheet with password|wrong|
|ensure|worksheet is protected                 |

Unprotect with the right password, and check if changing cells works

!|script                                           |
|ensure|unprotect worksheet with password|secret   |
|reject|worksheet is protected                     |
|ensure|Set value of cell                |$a$1|to|2|

Protect with an empty password, and check if changing is now blocked

!|script                                         |
|ensure|protect worksheet with password|         |
|ensure|worksheet is protected                   |
|reject|worksheet is protected with password     |
|reject|Set value of cell              |$a$1|to|4|

Try to re-protect with a password and check if that fails

!|script                                    |
|reject|protect worksheet with password|test|
|reject|worksheet is protected with password|

Unprotect with empty password should now succeed.

!|script                                           |
|ensure|unprotect worksheet with password|         |
|reject|worksheet is protected                     |
|ensure|Set value of cell                |$a$1|to|5|

Protect workbook with a password, then unprotect

!|script                                               |
|protect workbook with password  |secret               |
|ensure                          |workbook is protected|
|unprotect workbook with password|secret               |
|reject                          |workbook is protected|