Lock/Unlock cells for an xlsx-worksheet lazarus / 49-FPSpreadsheet ---------------------------------------------------------------------- Hendrikus · 2024-05-05 12:36 Hello all, sorry for my (bad) english, it's not my native language. After a huge amount of time, I've decided to pick up programming again, in Lazarus. I've created a spreadsheet with the FPSpreadsheet package and I am able to format all cells, set formula's and save it in xlsx-format. After that, MS Excel is able to read my spreadsheet. So far so good. I am just not able to lock some cells from editting. var  cellprot                             : TsCellProtections;       cell                                 : PCell;  begin     cell := MyWorksheet.FindCell(1,1);                              // "DATE" must be editable      // query cell protection      cellprot := Myworksheet.ReadCellProtection(cell);      // Unlock the cell for editing, don't change the visibility of formulas      Myworksheet.WriteCellProtection(cell, cellprot - [cpLockCell]);      // Hide formula of the cell and unlock the cell.      MyWorksheet.WriteCellProtection(cell, [cpHideFormulas]);        ... end; It compiles without any errors/warnings. It runs and the xlsx has been created. But after opening it in MS Excel, I am still able to edit cell(1,1), which is desired, but also all the other cells, which have been protected by:  MyWorksheet.Protection := [spCells, spDeleteColumns, spDeleteRows, spFormatCells, spFormatColumns, spFormatRows, spInsertColumns,                                            spInsertRows, spInsertHyperlinks, spSort];   Is there someone who can point me in the right direction? Thanks in advance. Martin ---------------------------------------------------------------------- paweld · 2024-05-05 13:19 Hi,    In the FPSpreadSheet component directory you can find examples - in the examples\other\protection subdirectory you will find the following code: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;  dir: String; 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);     dir := ExtractFilePath(ParamStr(0));    book.WriteToFile(dir + 'protected.xls', sfExcel8, true);    book.WriteToFile(dir + 'protected.xlsx', sfOOXML, true);    // Note ODS does not write the excel password correctly, yet. --> protection cannot be removed.    book.WriteToFile(dir + 'protected.ods', sfOpenDocument, true);   finally    book.Free;  end;   WriteLn('Open the files "protected.*" in your spreadsheet application.');  WriteLn('Only cell A1 can be modifed.');   if ParamCount = 0 then  begin    {$IFDEF MSWINDOWS}    WriteLn('Press [ENTER] to quit...');    ReadLn;    {$ENDIF}  end; end. ---------------------------------------------------------------------- Hendrikus · 2024-05-05 13:39 Thank you very much for your reaction. I already saw that example, I was just hoping that I didn't have to use a password to secure my spreadsheet. Best regards, Martin ---------------------------------------------------------------------- wp · 2024-05-07 14:06 It's been a while since I wrote this... I think what is missing is to activate protection of the worksheet: worksheet.Protect(true). The mixup of positive and negative logic behind it is a bit unusual: After this call all cells are protected, and this is the reason why you must remove the cpLockCell element from the worksheet's cellprotection, just to unlock it for editing again. I completed the wiki example. This way your code should work: 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 var    cellprot: TsCellProtections;  cell: PCell; begin  cell := MyWorksheet.FindCell(1,1);                              // "DATE" must be editable  // query cell protection  cellprot := Myworksheet.ReadCellProtection(cell);  // Unlock the cell for editing, don't change the visibility of formulas  Myworksheet.WriteCellProtection(cell, cellprot - [cpLockCell]);  // <--- only one of the two WriteCellProtection lines needed.   // Hide formula of the cell and unlock the cell.  //MyWorksheet.WriteCellProtection(cell, [cpHideFormulas]);      MyWorksheet.Protect(true);                                       // <--- ADDED    ...end; Quote from: Hendrikus on May 05, 2024, 03:39:58 pmI was just hoping that I didn't have to use a password to secure my spreadsheet. You don't have to. Just remove the password-related line from the sample. ---------------------------------------------------------------------- Zvoni · 2024-05-07 14:13 People still use Sheet-Protection for Excel-Sheets?????? (And i'm talking about SHEET-protection, not Workbook-Protection a.k.a encryption) Blimey, the clients and suppliers of the company i work for have all surrendered, after i told them to put the blasted Sheet-Protection (password yes/no doesn't matter) where the sun doesn't shine, since it doesn't take me a minute to throw the Sheet-protection out of any Workbook, irrespective if there is a password or not. Ah well.... do whatever you like..... ---------------------------------------------------------------------- wp · 2024-05-07 14:23 Locking of cells does not have anything to do with security. It's just a way to prevent a user from unintentionally editing cells which should not be altered.