Worksheet protection

[SeregaKR]

My project didn't compile. Got an error:

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

1. fpstypes.pas(13,2) Fatal: Cannot open include file "fps.inc"

Correct. I found a lot of these among the demos which come with fpspreadsheet. Not 100% sure, but I guess that the handling of include files has been changed. As I already wrote this happens if the laz_fpspreadsheet package is not added to the project requirements. If this cannot be done for some reason you must add the path to (fpspreadsheet)/source (which contains fps.inc) to the Include Files (2nd box in "Project options" > "Compiler options" > "Path").

Already removed everything from Other paths -Fu. So the problem this problem is already solved (you helped me earlier with it). The problem I have now is with the lines:

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

1. swDest.Workbook.GetWorksheetByIndex(0).CryptoInfo.Algorithm:= caExcel;
2. swDest.Workbook.GetWorksheetByIndex(0).CryptoInfo.PasswordHash:=psw;

(311,57) Error: Argument cannot be assigned to

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

1. swDest.Workbook.GetWorksheetByIndex(0).Protection:= [spDeleteRows, spInsertRows, spSort];

(313,57) Error: Illegal expression

[wp]

So the problem this problem is already solved (you helped me earlier with it).

I know. Just wanted to document that your observation was correct. All the demos had been working correctly with Laz 1.8.2/fpc 3.0.2, but did not compile with 1.8.4/fpc3.0.4. This has been fixed in the current fpspreadsheet revision.

The problem I have now is with the lines:

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

1. swDest.Workbook.GetWorksheetByIndex(0).CryptoInfo.Algorithm:= caExcel;
2. swDest.Workbook.GetWorksheetByIndex(0).CryptoInfo.PasswordHash:=psw;

(311,57) Error: Argument cannot be assigned to

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

1. swDest.Workbook.GetWorksheetByIndex(0).Protection: = [spDeleteRows, spInsertRows, spSort];

(313,57) Error: Illegal expression

This is because CryptoInfo is a record, and the worksheet only has a getter/setter for the entire record, not for its individual elements. Create a local variable "c: TsCryptoInfo", assign the individual record elements, and then assign the entire record to the CryptoInfo of the worksheet.

I don't see anything wrong with the instruction "...Protection := [...]"

Here is a tested example with cell and sheet protection:

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

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

Note that protecting the protection settings by a password does not work for ODS files because I did not yet sort out how the stored password hash is created from the Excel hash or password. Ideas are welcome.

[SeregaKR]

I needed it for xlsx file so it's fine (I don't need ods file). I found a typo in my program so now that I fixed it the following line works fine:

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

1. swDest.Workbook.GetWorksheetByIndex(0).Protection:= [spDeleteRows, spInsertRows, spSort];

Thank you for your help