Lock/Unlock cells for an xlsx-worksheet

[Hendrikus]

Hello all,

sorry for my (bad) english, it's not my native language. After a huge amount of time, I've decided to pick up programming again, in Lazarus.
I've created a spreadsheet with the FPSpreadsheet package and I am able to format all cells, set formula's and save it in xlsx-format.
After that, MS Excel is able to read my spreadsheet. So far so good. I am just not able to lock some cells from editting.

var  cellprot                             : TsCellProtections;
      cell                                 : PCell; 
begin
    cell := MyWorksheet.FindCell(1,1);                              // "DATE" must be editable
     // query cell protection
     cellprot := Myworksheet.ReadCellProtection(cell);
     // Unlock the cell for editing, don't change the visibility of formulas
     Myworksheet.WriteCellProtection(cell, cellprot - [cpLockCell]);
     // Hide formula of the cell and unlock the cell.
     MyWorksheet.WriteCellProtection(cell, [cpHideFormulas]);   

    ...
end;

It compiles without any errors/warnings. It runs and the xlsx has been created. But after opening it in MS Excel, I am still able to
edit cell(1,1), which is desired, but also all the other cells, which have been protected by:
 MyWorksheet.Protection := [spCells, spDeleteColumns, spDeleteRows, spFormatCells, spFormatColumns, spFormatRows, spInsertColumns,
                                           spInsertRows, spInsertHyperlinks, spSort];

 
Is there someone who can point me in the right direction?
Thanks in advance.
Martin   

[paweld]

Hi,     
In the FPSpreadSheet component directory you can find examples - in the examples\other\protection subdirectory you will find the following code:

Code: Pascal  [Select][+][-]

1. program demo_protection;
2. {$mode objfpc}{$H+}
3.  
4. uses
5.   Classes, SysUtils,
6.   fpstypes, fpspreadsheet, fpsallformats, fpsutils, fpscrypto;
7.  
8. const
9.   PASSWORD = 'lazarus';
10.  
11. var
12.   book: TsWorkbook;
13.   sheet: TsWorksheet;
14.   cell: PCell;
15.   c: TsCryptoInfo;
16.   dir: String;
17.  
18. begin
19.   book := TsWorkbook.Create;
20.   try
21.     sheet := book.AddWorksheet('Sheet1');
22.  
23.     // Add an unprotected cell
24.     cell := sheet.WriteText(0, 0, 'Unprotected cell');
25.     sheet.WriteCellProtection(cell, []);
26.  
27.     // Add a protected cell
28.     sheet.WriteText(1, 0, 'Protected cell');
29.  
30.     // Activate worksheet protection such that a password is required to
31.     // change the protection state
32.     InitCryptoInfo(c);
33.     c.Algorithm := caExcel;
34.     c.PasswordHash := Format('%.4x', [ExcelPasswordHash(PASSWORD)]);
35.     sheet.CryptoInfo := c;
36.     sheet.Protection := [spDeleteRows, spDeleteColumns, spInsertRows, spInsertColumns];
37.     sheet.Protect(true);
38.  
39.     dir := ExtractFilePath(ParamStr(0));
40.     book.WriteToFile(dir + 'protected.xls', sfExcel8, true);
41.     book.WriteToFile(dir + 'protected.xlsx', sfOOXML, true);
42.     // Note ODS does not write the excel password correctly, yet. --> protection cannot be removed.
43.     book.WriteToFile(dir + 'protected.ods', sfOpenDocument, true);
44.  
45.   finally
46.     book.Free;
47.   end;
48.  
49.   WriteLn('Open the files "protected.*" in your spreadsheet application.');
50.   WriteLn('Only cell A1 can be modifed.');
51.  
52.   if ParamCount = 0 then
53.   begin
54.     {$IFDEF MSWINDOWS}
55.     WriteLn('Press [ENTER] to quit...');
56.     ReadLn;
57.     {$ENDIF}
58.   end;
59.  
60. end.
61.  
62.  
63.  

[Hendrikus]

Thank you very much for your reaction.
I already saw that example, I was just hoping that I didn't have to use a password to secure my spreadsheet.
Best regards,
Martin

[wp]

It's been a while since I wrote this... I think what is missing is to activate protection of the worksheet: worksheet.Protect(true). The mixup of positive and negative logic behind it is a bit unusual: After this call all cells are protected, and this is the reason why you must remove the cpLockCell element from the worksheet's cellprotection, just to unlock it for editing again. I completed the wiki example.

This way your code should work:

Code: Pascal  [Select][+][-]

1. var  
2.   cellprot: TsCellProtections;
3.   cell: PCell;
4. begin
5.   cell := MyWorksheet.FindCell(1,1);                              // "DATE" must be editable
6.   // query cell protection
7.   cellprot := Myworksheet.ReadCellProtection(cell);
8.   // Unlock the cell for editing, don't change the visibility of formulas
9.   Myworksheet.WriteCellProtection(cell, cellprot - [cpLockCell]);  // <--- only one of the two WriteCellProtection lines needed.
10.   // Hide formula of the cell and unlock the cell.
11.   //MyWorksheet.WriteCellProtection(cell, [cpHideFormulas]);    
12.   MyWorksheet.Protect(true);                                       // <--- ADDED
13.     ...
14. end;

I was just hoping that I didn't have to use a password to secure my spreadsheet.

You don't have to. Just remove the password-related line from the sample.

[Zvoni]

People still use Sheet-Protection for Excel-Sheets?????? (And i'm talking about SHEET-protection, not Workbook-Protection a.k.a encryption)

Blimey, the clients and suppliers of the company i work for have all surrendered, after i told them to put the blasted Sheet-Protection (password yes/no doesn't matter) where the sun doesn't shine,
since it doesn't take me a minute to throw the Sheet-protection out of any Workbook, irrespective if there is a password or not.

Ah well.... do whatever you like.....

[wp]

Locking of cells does not have anything to do with security. It's just a way to prevent a user from unintentionally editing cells which should not be altered.