Protected Cells

FLX TMS FlexCel for VCL & FMX
1

Hi,


I'm using Flexcel 6.1.1 and Delphi XE5.

I'm not able to protect cells when I create my worksheet.
I used the ApiMate and applied the code, but the user still can modified the protected cell values.

each column has the following code :


  fmt := xls.GetCellVisibleFormatDef(6, 16);
  fmt.Font.Color := TExcelColor.FromTheme(TThemeColor.Background1);
  fmt.FillPattern.Pattern := TFlxPatternStyle.Solid;
  fmt.FillPattern.FgColor := TExcelColor.FromTheme(TThemeColor.Foreground2, -0.249977111117893);
  fmt.FillPattern.BgColor := TExcelColor.Automatic;
  fmt.Locked := false; <--
  xls.SetCellFormat(6, 16, xls.AddFormat(fmt));





 SheetProtectionOptions := TSheetProtectionOptions.Create(false);
    SheetProtectionOptions.Contents := true;
    SheetProtectionOptions.Objects := true;
    SheetProtectionOptions.Scenarios := true;
    SheetProtectionOptions.SelectUnlockedCells := true;
  xls.Protection.SetSheetProtection('1234', SheetProtectionOptions);

But the user can change those values. What am I missing?

TIA,
Clément
2

Hi,

I think the issue is here:
fmt.Locked := false; <--
In order to lock the cell, it should be:
fmt.Locked := true;

Just a note: By default all cells in Excel are locked. So if you want to only protect a couple of cells, you need to unlock all the cells first, then lock the ones you want.

Excel has 3 different kind of formats that you can apply to a cell:
1)Cell format
2)Row format
3)Column format

If you tried to unlock the full sheet by setting the cell format cell by cell, that would mean creating 1million x 16000 cells, which will give an out of memory error.

Excel doesn’t have an option to apply format to the full sheet, but the best next thing is to apply it to all the columns. You could also apply it to all the rows, but there are 1 million rows (opposed to just 16000 columns), and also, columns with the same formatting are compressed into a single entity, so you won’t end up with 16000 columns, just with one that applies from column 1 to 16000. Rows on the other hand aren’t compressed, so trying to apply a format by rows would mean to create 1 million rows. (which would still be better than trying to format every cell).

As always, you can find out about this kind of stuff with APIMate.
Excel allows you to unlock all the cells, so what is it doing under the hood?

I’ve just created an empty file in Excel, selected the full grid, and unlocked it. Then opened it in APIMate, and this is what it reports:

   TFlxFormat ColFmt;
    ColFmt = xls.GetFormat(xls.GetColFormat(1));
    ColFmt.Locked = false;
    xls.SetColFormat(1, 16384, xls.AddFormat(ColFmt));

So, Excel also unlocks the sheet by columns. The code above would unlock the full sheet, after that you need to lock the cell 6, 16. 
3
I will try that
Thanks!!