How do I use rubyXL to lock the existing cells in a worksheet but leave all empty cells unlocked? (I don't want to make the user jump through any hoops like requiring him or her to create a new column before entering new data.)
I figured out how to lock existing cells using the technique presented in this StackOverflow post: How can we protect some parts of a sheet using rubyXL? (The basic idea is to (1) Create a new xf, (2) register the new xf with the workbook, (3) set the style_index
of all existing cells to use this xf, then (4) set the WorksheetProtection
for the sheet.)
However, when I do this and open the workbook in Excel, all the empty cells in the worksheet are also locked.
In principle, I need to add an "unlocked" xf to all unused cells; however, since these cells don't exist yet, there is no Cell object to which to set the "unlocked" xf.
There is a way to do this in Excel:
- Click the upper-left corner of the worksheet to select all cells.
- Go to "Format" -> "Format Cells", select the "protection" tab, and uncheck the "locked" box.
- Select the existing, occupied cells, got to "Format" -> "Format Cells", select the "protection" tab, and check the "locked" box.
- Go to "Tools" -> "Protection" -> "protect sheet" and protect the worksheet.
When I follows these steps then examine the worksheet using rubyXL, I see that there are three XL objects:
- The default XL object
- An XL object with protection enabled
- An XL object with protection specifically disabled.
The existing cells all use style/XL 2. So, I'm thinking that there must be a way to specify that all unused cells in the worksheet default to style/XL 3; but, I don't see how to specify this using rubyXL.
Any ideas?
I figured it out:
(I also put a copy of this code here: https://github.com/kurmasz/rubyXL-recipes)