It is possible to lock rows or columns based on some condition. For example, we could search each row for cells with a specific background color, then lock each row containing cells that match our query.
Bear in mind that you will need to protect your worksheet(s) and unlock all cells first in order for this to work. Then, you will need to enable any protectionOptions that you wish to still be available.
// Configure Workbook and Worksheet
var spread = new GC.Spread.Sheets.Workbook("ss");
var sheet = spread.getActiveSheet();
// Unlock all the cells in the worksheet via the styles
let style = new GC.Spread.Sheets.Style();
style.locked = false;
// Set style as default style for all the cells in the sheet
sheet.setDefaultStyle(style);
// Separately lock the cell range you want to be read-only
new GC.Spread.Sheets.CellRange(sheet, 0, 0, 13, 4).locked(true);
// Set sheet to be protected
sheet.options.isProtected = true;
// Enable protectionOptions to allow features like filtering (optional)
// sheet.options.protectionOptions.allowFilter = true;
For the conditional row locking, consider the following code snippet:
function runFilter(){
// Loop through each row
for (var rowIndex = 0; rowIndex < sheet.getRowCount(); rowIndex++) {
var lockRow = false;
// Loop through each cell in row
for (var colIndex = 0; colIndex < sheet.getColumnCount(); colIndex++) {
var cell = sheet.getCell(rowIndex, colIndex);
//Check for condition
if (cell.backColor() === 'red') {
lockRow = true;
break;
}
}
// Lock the row
if (lockRow) {
for (var colIndex = 0; colIndex < sheet.getColumnCount(); colIndex++) {
var cell = sheet.getCell(rowIndex, colIndex);
cell.locked(true);
//highlight rows (optional)
cell.backColor("yellow");
cell.setBorder(new GC.Spread.Sheets.LineBorder("black", GC.Spread.Sheets.LineStyle.thin), {all:true});
}
}
}
};
In this example, we first establish a variable that will be used to determine which rows should be locked. We then loop through each cell in each row, checking for our specified condition. In this case, we are looking for any cells with a background color set to red. Finally, if the cell meets the conditions and is flagged as true to our variable, we lock the row that the cell is located in. We can optionally apply a different background color to the row or some other styling to indicate which rows have been locked.
Tye Glenz