Lazarus

Programming => Packages and Libraries => FPSpreadsheet => Topic started by: SeregaKR on April 17, 2018, 02:10:05 pm

Title: Worksheet protection
Post by: SeregaKR on April 17, 2018, 02:10:05 pm
Hello.
Updated Lazarus to 1.8.2 and FPSpreadSheet to 1.8.4 recently.
After update worksheet protection stopped working.
I get an error (311,42) Error: identifier idents no member "Protect" on the line:
Code: Pascal  [Select][+][-]
  1. swDest.Workbook.GetFirstWorksheet.Protect(True);

swDest is a TsWorkbookSource component on the form

Here is my uses section:
Code: Pascal  [Select][+][-]
  1. ..., fpspreadsheetctrls, ...,   fpspreadsheetgrid, ..., fpstypes, fpspreadsheet, fpsallformats, fpsexport

I opened source\common\fpspreadsheet.pas but I didn't find Protect function there.
Title: Re: Worksheet protection
Post by: wp on April 17, 2018, 02:16:32 pm
The protection routines are not yet released, i.e. you need fpspreadsheet trunk to get them. (http://wiki.lazarus.freepascal.org/FPSpreadsheet#SVN_change_log). Which fpspreadsheet version did you use before switching to 1.8.4?
Title: Re: Worksheet protection
Post by: SeregaKR on April 17, 2018, 02:26:39 pm
I used FPSpreadSheet 1.8.2 - I was using worksheet protection without password and it worked. But now I want to protect worksheet with password  that's why I updated FPSpreadSheet to 1.8.4.

So should I downgrade and wait for next version to implement worksheet protection with password?
I thought it was already implemented
Quote
SVN change log
Workbook, worksheet and cell protection (read/write in BIFF2/BIFF5/BIFF8/OOXML/ODS, write in ExcelXML).

So it's rather stable?
Title: Re: Worksheet protection
Post by: SeregaKR on April 17, 2018, 02:51:28 pm
My project didn't compile. Got an error:
Code: Pascal  [Select][+][-]
  1. fpstypes.pas(13,2) Fatal: Cannot open include file "fps.inc"
Title: Re: Worksheet protection
Post by: wp on April 17, 2018, 02:53:43 pm
I used FPSpreadSheet 1.8.2 - I was using worksheet protection without password and it worked.
This can't be -- I checked the released source files of v1.8.0, 1.8.2, and 1.8.3, and none of the contains the protection routines. You probably somehow must have got the trunk files.

So should I downgrade and wait for next version to implement worksheet protection with password?
Or load the trunk version from CCR again - trunk is the version which is actively developed, it is always the most recent version. You should use SVN to download it.

Or wait some time until I'll have finished my current activity -- I definitely should release v1.10, the v1.8.x series is rather old...

Quote
SVN change log
Workbook, worksheet and cell protection (read/write in BIFF2/BIFF5/BIFF8/OOXML/ODS, write in ExcelXML).
The "SVN change log" refers to the current development version, i.e. trunk.
Title: Re: Worksheet protection
Post by: SeregaKR on April 17, 2018, 02:57:21 pm
Strange. I checked again. It was 1.8.2 from Sourceforge (File with change date of 15.06.2017) - not the trunc.
Title: Re: Worksheet protection
Post by: wp on April 17, 2018, 02:59:27 pm
My project didn't compile. Got an error:
Code: Pascal  [Select][+][-]
  1. fpstypes.pas(13,2) Fatal: Cannot open include file "fps.inc"
Don't add fpspreadsheet directories to the search path of the project. This is the Delphi way leading into chaos. Use the Lazarus package system: You must add the package "laz_fpspreadsheet" to the requirements of the project ("Project" > "Project inspector" > "Add" > "Add new requirement", find "laz_fpspreadsheet", select it and click OK). Then your project should be able to find all fpspreadsheet files required.
Title: Re: Worksheet protection
Post by: SeregaKR on April 17, 2018, 03:03:27 pm
Thank you. Project was successfully compiled. Now I will experiment with protection options.
Title: Re: Worksheet protection
Post by: wp on April 17, 2018, 03:10:30 pm
Strange. I checked again. It was 1.8.2 from Sourceforge (File with change date of 15.06.2017) - not the trunc.
The official 1.8.2 zip download file from CCR (https://sourceforge.net/projects/lazarus-ccr/files/FPSpreadsheet/) has date 2017-04-11. Does your file belong to the CodeTyphon distribution? This could explain it.
Title: Re: Worksheet protection
Post by: SeregaKR on April 18, 2018, 06:52:35 am
No it's not. Strange but I don't complain)
Title: Re: Worksheet protection
Post by: SeregaKR on April 18, 2018, 08:41:43 am
And the last question. I'm not sure how to use password protection of the worksheet. I found correct procedures in the source code but I'm not sure. Can anyone check please?

Code: Pascal  [Select][+][-]
  1. psw:= ExcelPasswordHash('1234567890');
  2. swDest.Workbook.GetWorksheetByIndex(0).CryptoInfo.Algorithm:= caExcel;
  3. swDest.Workbook.GetWorksheetByIndex(0).CryptoInfo.PasswordHash:=psw;
  4. swDest.Workbook.GetWorksheetByIndex(0).Protection:= [spDeleteRows, spInsertRows, spSort];
  5. swDest.Workbook.GetWorksheetByIndex(0).Protect(True);
     

I'm not sure why function CalcPasswordHash is commented in the source. It seems that ExcelPasswordHash result is word variable while CryptoInfo.PasswordHash requires string

Title: Re: Worksheet protection
Post by: wp on April 18, 2018, 10:13:54 am
What do you mean with "password protection of a worksheet"? Protect if from being opened without a password? This can't be done with fpspreadsheet which does not encrypt a spreadsheet file. The password only has the purpose to protect the protection settings, in other words: the password is required if the user wants to change cell, worksheet or workbook protection items. Please read http://wiki.lazarus.freepascal.org/FPSpreadsheet#Protection.

Protection routines are far from being complete in fpspreadsheet (like many others). fpspreadsheet was never intended to be a replacement of the big Office spreadsheet applications.

Don't care about commented code, this is from experiments which I did not want to delete because it might be needed later.
Title: Re: Worksheet protection
Post by: SeregaKR on April 18, 2018, 11:27:11 am
Yes, I want to protect worksheet content from being changed. Until now I've done it without password but right now I need to protect these settings with password, so nobody will be able to change cell contents without password.
This wiki doesn't have full information about it. I had to pull up the source code to find other details. But some commented functions got me confused. I know that I have to count password hash and then write it in CryptoInfo.PasswordHash. But I don't know how exacltly to count it. ExcelPasswordHash function was changed and gives us Word result not string as expected in CryptoInfo.PasswordHash
Title: Re: Worksheet protection
Post by: SeregaKR on April 18, 2018, 12:05:44 pm
Even if I conver the word outcome from ExcelPasswordHash functtion to string as it was done in the previous version I still get an error on the lines:
Code: Pascal  [Select][+][-]
  1. swDest.Workbook.GetWorksheetByIndex(0).CryptoInfo.Algorithm:= caExcel;
  2. swDest.Workbook.GetWorksheetByIndex(0).CryptoInfo.PasswordHash:=psw;
  3. swDest.Workbook.GetWorksheetByIndex(0).Protection:= [spDeleteRows, spInsertRows, spSort];

(311,57) Error: Argument cannot be assigned to (line 1 and 2)
(313,57) Error: Illegal expression (line 3)

All I need is to protect the worksheet content from changing. I don't really need to delete this protection in future. So even if password hash function doesn't wok now it doesn't matter. All I need is to enable password protection.
Title: Re: Worksheet protection
Post by: wp on April 18, 2018, 01:49:18 pm
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").
Title: Re: Worksheet protection
Post by: SeregaKR on April 18, 2018, 01:56:35 pm
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
Title: Re: Worksheet protection
Post by: wp on April 18, 2018, 03:12:19 pm
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.
Title: Re: Worksheet protection
Post by: SeregaKR on April 18, 2018, 03:31:35 pm
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
TinyPortal © 2005-2018