[SOLVED] Protection of a specific column

[rca]

Is it possible to protect the contents of a specific column without having to do it cell by cell?

For example, I have 4 columns with this data:

Code: Pascal  [Select][+][-]

1.   sWorksheetGrid1.Cells[1,1] := 'Column1';
2.   sWorksheetGrid1.Cells[1,2] := 200;
3.   sWorksheetGrid1.Cells[1,3] := 124;
4.   sWorksheetGrid1.Cells[1,4] := 682;
5.   sWorksheetGrid1.Cells[1,5] := 456;
6.  
7.   sWorksheetGrid1.Cells[2,1] := 'Column2';
8.   sWorksheetGrid1.Cells[2,2] := 450;
9.   sWorksheetGrid1.Cells[2,3] := 218;
10.   sWorksheetGrid1.Cells[2,4] := 346;
11.   sWorksheetGrid1.Cells[2,5] := 260;
12.  
13.   sWorksheetGrid1.Cells[3,1] := 'Column3';
14.   sWorksheetGrid1.Cells[3,2] := 120;
15.   sWorksheetGrid1.Cells[3,3] := 368;
16.   sWorksheetGrid1.Cells[3,4] := 874;
17.   sWorksheetGrid1.Cells[3,5] := 240;
18.  
19.   sWorksheetGrid1.Cells[4,1] := 'Column4';
20.   sWorksheetGrid1.Cells[4,2] := 620;
21.   sWorksheetGrid1.Cells[4,3] := 485;
22.   sWorksheetGrid1.Cells[4,4] := 128;
23.   sWorksheetGrid1.Cells[4,5] := 346;
24.  

If I only want to protect the contents of Column 1 and 3, cell by cell, I do:

Code: Pascal  [Select][+][-]

1. var
2.   cellprot: TsCellProtections;
3.   cell: PCell;
4.   i: integer;
5. begin
6.   //Unlock Column 2
7.   for i:=0 to 4 do
8.   begin
9.     cell := sWorksheetGrid1.Worksheet.FindCell(i,1);
10.     cellprot := sWorksheetGrid1.Worksheet.ReadCellProtection(cell);
11.     sWorksheetGrid1.Worksheet.WriteCellProtection(cell, cellprot - [cpLockCell]);
12.   end;
13.  
14.   //Unlock Column 4
15.   for i:=0 to 4 do
16.   begin
17.     cell := sWorksheetGrid1.Worksheet.FindCell(i,3);
18.     cellprot := sWorksheetGrid1.Worksheet.ReadCellProtection(cell);
19.     sWorksheetGrid1.Worksheet.WriteCellProtection(cell, cellprot - [cpLockCell]);
20.   end;
21.  
22.   sWorksheetGrid1.Worksheet.Protect(true);
23. end;
24.  

However, I have some real cases where the number of rows is almost 1000...


I tried doing it with "WriteColFormatIndex":

Code: Pascal  [Select][+][-]

1. var
2.   fmt: TsCellFormat;
3.   idx: Integer;
4.  
5. begin
6.   InitFormatRecord(fmt);
7.   fmt.Protection:=[cpLockCell];
8.   fmt.UsedformattingFields := fmt.UsedFormattingFields + [uffProtection];
9.  
10.   idx := sWorksheetGrid1.Workbook.AddCellFormat(fmt);
11.  
12.   //Lock Column 1
13.   sWorksheetGrid1.Worksheet.WriteColFormatIndex(1, idx);
14.  
15.   //Lock Column 3
16.   sWorksheetGrid1.Worksheet.WriteColFormatIndex(3, idx);
17. end
18.  

I appreciate any help you can give me.

[wp]

Doing the protection by column is basically correct. But you must know that you must apply the column format before adding cells:

Code: Pascal  [Select][+][-]

1. // Creating a worksheet in which data can be entered only in column 1 (except for its header cell)
2. program project1;
3. uses
4.   fpSpreadsheet, fpsTypes, fpsUtils, xlsxOOXML;
5. var
6.   b: TsWorkbook;
7.   sh: TsWorksheet;
8.   r: Cardinal;
9.   header: PCell;
10.   fmt: TsCellFormat;
11.   idx: Integer;
12. begin
13.   b := TsWorkbook.Create;
14.   sh := b.AddWorksheet('Test');
15.  
16.   // Protect the worksheet (note: this affects only the file, you can still write to the sheet by code)
17.   sh.Protect(true);
18.  
19.   // Define the cell format for an unlocked cell
20.   InitFormatRecord(fmt);
21.   fmt.Protection := [];  // remove protection flags
22.   fmt.UsedFormattingFields := fmt.UsedFormattingFields + [uffProtection];
23.  
24.   // Apply the cell format to the entire column to be unlocked
25.   // Important: this must happen before any data are entered.
26.   idx := b.AddCellFormat(fmt);
27.   sh.WriteColFormatIndex(1, idx);
28.  
29.   // Add data to the cells
30.   header := sh.WriteText(0, 1, 'Data');
31.   for r := 1 to 100 do
32.     sh.WriteNumber(r, 1, Random);
33.  
34.   // The column header ("Data") should be protected
35.   fmt.Protection := [cpLockCell];   // Add cell-locking flag
36.   sh.WriteCellFormat(header, fmt);
37.  
38.   // Save
39.   b.WriteToFile('test.xlsx', true);
40.  
41.   // Clean up
42.   b.Free;
43. end.

[rca]

@wp
Thanks for your explanation and help.
It works like a charm.