Worksheet protection lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- SeregaKR · 2018-04-17 12:10 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i swDest.Workbook.GetFirstWorksheet.Protect(True); swDest is a TsWorkbookSource component on the form Here is my uses section: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i ..., fpspreadsheetctrls, ...,   fpspreadsheetgrid, ..., fpstypes, fpspreadsheet, fpsallformats, fpsexport I opened source\common\fpspreadsheet.pas but I didn't find Protect function there. ---------------------------------------------------------------------- wp · 2018-04-17 12:16 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 · 2018-04-17 12:26 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 QuoteSVN change log Workbook, worksheet and cell protection (read/write in BIFF2/BIFF5/BIFF8/OOXML/ODS, write in ExcelXML). So it's rather stable? ---------------------------------------------------------------------- SeregaKR · 2018-04-17 12:51 My project didn't compile. Got an error: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i fpstypes.pas(13,2) Fatal: Cannot open include file "fps.inc" ---------------------------------------------------------------------- wp · 2018-04-17 12:53 Quote from: SeregaKR on April 17, 2018, 02:26:39 pmI 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. Quote from: SeregaKR on April 17, 2018, 02:26:39 pmSo 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 from: SeregaKR on April 17, 2018, 02:26:39 pmQuoteSVN 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 · 2018-04-17 12:57 Strange. I checked again. It was 1.8.2 from Sourceforge (File with change date of 15.06.2017) - not the trunc. ---------------------------------------------------------------------- wp · 2018-04-17 12:59 Quote from: SeregaKR on April 17, 2018, 02:51:28 pmMy project didn't compile. Got an error: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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 · 2018-04-17 13:03 Thank you. Project was successfully compiled. Now I will experiment with protection options. ---------------------------------------------------------------------- wp · 2018-04-17 13:10 Quote from: SeregaKR on April 17, 2018, 02:57:21 pmStrange. 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 · 2018-04-18 04:52 No it's not. Strange but I don't complain) ---------------------------------------------------------------------- SeregaKR · 2018-04-18 06:41 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i psw:= ExcelPasswordHash('1234567890'); swDest.Workbook.GetWorksheetByIndex(0).CryptoInfo.Algorithm:= caExcel; swDest.Workbook.GetWorksheetByIndex(0).CryptoInfo.PasswordHash:=psw;sw Dest.Workbook.GetWorksheetByIndex(0).Protection:= [spDeleteRows, spInsertRows, spSort];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 · 2018-04-18 08:13 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 · 2018-04-18 09:27 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 · 2018-04-18 10:05 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i swDest.Workbook.GetWorksheetByIndex(0).CryptoInfo.Algorithm:= caExcel; swDest.Workbook.GetWorksheetByIndex(0).CryptoInfo.PasswordHash:=psw;sw Dest.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 · 2018-04-18 11:49 Quote from: SeregaKR on April 17, 2018, 02:51:28 pmMy project didn't compile. Got an error: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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"). ---------------------------------------------------------------------- SeregaKR · 2018-04-18 11:56 Quote from: wp on April 18, 2018, 01:49:18 pmQuote from: SeregaKR on April 17, 2018, 02:51:28 pmMy project didn't compile. Got an error: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i swDest.Workbook.GetWorksheetByIndex(0).CryptoInfo.Algorithm:= caExcel; swDest.Workbook.GetWorksheetByIndex(0).CryptoInfo.PasswordHash:=psw; (311,57) Error: Argument cannot be assigned to Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i swDest.Workbook.GetWorksheetByIndex(0).Protection:= [spDeleteRows, spInsertRows, spSort]; (313,57) Error: Illegal expression ---------------------------------------------------------------------- wp · 2018-04-18 13:12 Quote from: SeregaKR on April 18, 2018, 01:56:35 pmSo 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. Quote from: SeregaKR on April 18, 2018, 01:56:35 pmThe problem I have now is with the lines: Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i swDest.Workbook.GetWorksheetByIndex(0).CryptoInfo.Algorithm:= caExcel; swDest.Workbook.GetWorksheetByIndex(0).CryptoInfo.PasswordHash:=psw; (311,57) Error: Argument cannot be assigned to Code: Pascal  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i program demo_protection; {$mode objfpc}{$H+} uses  Classes, SysUtils,  fpstypes, fpspreadsheet, fpsallformats, fpsutils, fpscrypto; const  PASSWORD = 'lazarus'; var  book: TsWorkbook;  sheet: TsWorksheet;  cell: PCell;  c: TsCryptoInfo;begin  book := TsWorkbook.Create;  try  sheet := book.AddWorksheet('Sheet1');     // Add an unprotected cell    cell := sheet.WriteText(0, 0, 'Unprotected cell');    sheet.WriteCellProtection(cell, []);     // Add a protected cell    sheet.WriteText(1, 0, 'Protected cell');     // Activate worksheet protection such that a password is required to    // change the protection state    InitCryptoInfo(c);    c.Algorithm := caExcel;    c.PasswordHash := Format('%.4x', [ExcelPasswordHash(PASSWORD)]);    sheet.CryptoInfo := c;    sheet.Protection := [spDeleteRows, spDeleteColumns, spInsertRows, spInsertColumns];    sheet.Protect(true);     book.WriteToFile('protected.xls', sfExcel8, true);    book.WriteToFile('protected.xlsx', sfOOXML, true);    // Note ODS does not write the excel password correctly, yet. --> protection cannot be removed.    book.WriteToFile('protected.ods', sfOpenDocument, true);   finally    book.Free;  end;   WriteLn('Open the files "protected.*" in your spreadsheet application.');  WriteLn('Only cell A1 can be modifed.');  WriteLn('Press [ENTER] to quit...');  ReadLn; 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 · 2018-04-18 13:31 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][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i swDest.Workbook.GetWorksheetByIndex(0).Protection:= [spDeleteRows, spInsertRows, spSort]; Thank you for your help