* * *

Author Topic: Worksheet protection  (Read 619 times)

SeregaKR

  • New member
  • *
  • Posts: 14
Worksheet protection
« 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.

wp

  • Hero Member
  • *****
  • Posts: 4398
Re: Worksheet protection
« Reply #1 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?
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

SeregaKR

  • New member
  • *
  • Posts: 14
Re: Worksheet protection
« Reply #2 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?
« Last Edit: April 17, 2018, 02:43:30 pm by SeregaKR »

SeregaKR

  • New member
  • *
  • Posts: 14
Re: Worksheet protection
« Reply #3 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"

wp

  • Hero Member
  • *****
  • Posts: 4398
Re: Worksheet protection
« Reply #4 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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

SeregaKR

  • New member
  • *
  • Posts: 14
Re: Worksheet protection
« Reply #5 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.

wp

  • Hero Member
  • *****
  • Posts: 4398
Re: Worksheet protection
« Reply #6 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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

SeregaKR

  • New member
  • *
  • Posts: 14
Re: Worksheet protection
« Reply #7 on: April 17, 2018, 03:03:27 pm »
Thank you. Project was successfully compiled. Now I will experiment with protection options.

wp

  • Hero Member
  • *****
  • Posts: 4398
Re: Worksheet protection
« Reply #8 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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

SeregaKR

  • New member
  • *
  • Posts: 14
Re: Worksheet protection
« Reply #9 on: April 18, 2018, 06:52:35 am »
No it's not. Strange but I don't complain)

SeregaKR

  • New member
  • *
  • Posts: 14
Re: Worksheet protection
« Reply #10 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

« Last Edit: April 18, 2018, 03:29:27 pm by SeregaKR »

wp

  • Hero Member
  • *****
  • Posts: 4398
Re: Worksheet protection
« Reply #11 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.
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

SeregaKR

  • New member
  • *
  • Posts: 14
Re: Worksheet protection
« Reply #12 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

SeregaKR

  • New member
  • *
  • Posts: 14
Re: Worksheet protection
« Reply #13 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.
« Last Edit: April 18, 2018, 03:29:18 pm by SeregaKR »

wp

  • Hero Member
  • *****
  • Posts: 4398
Re: Worksheet protection
« Reply #14 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").
Lazarus trunk / fpc 3.0.4 / all 32-bit on Win-10

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus