Worksheet protection

[SeregaKR]

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.

[wp]

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?

[SeregaKR]

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

SVN change log
Workbook, worksheet and cell protection (read/write in BIFF2/BIFF5/BIFF8/OOXML/ODS, write in ExcelXML).

So it's rather stable?

[SeregaKR]

My project didn't compile. Got an error:

Code: Pascal  [Select]

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

[wp]

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...

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.

[SeregaKR]

Strange. I checked again. It was 1.8.2 from Sourceforge (File with change date of 15.06.2017) - not the trunc.

[wp]

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.

[SeregaKR]

Thank you. Project was successfully compiled. Now I will experiment with protection options.

[wp]

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.

[SeregaKR]

No it's not. Strange but I don't complain)

[SeregaKR]

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

[wp]

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.

[SeregaKR]

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

[SeregaKR]

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.

[wp]

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").