Enabling cells in a protected worksheet

[Doug]

I'm using SpreadProcessing in conjunction with a Blazor TelerikSpreadsheet component, and as I understand it from the documentation, if I want to mark certain cells as read only, the way to do that is by protecting the worksheet and then unlocking the cells I want to be editable. In my use case I need to disable the cells in the first row but leave all other cells editable and I've encountered two issues.

First, if I set up a loop to spin through the rows (other than the fist row) and mark them as unlocked, as such:

        for (var i = 1; i < worksheet.Rows.Count; i++)
        {
            worksheet.Rows[i].SetIsLocked(false);
        }

The first issue is that worksheet.Rows.Count returns 1,048,576 rows. By default the spreadsheet displays 200 rows. I don't know if that default is on the Blazor side and that it only displays 200 of the 1,048,576 rows that are actually in the worksheet or what, but it's not really reasonable to spin through all those rows attempting to unlock them when they're not even visible. So that's issue number 1.

Issue number 2 is, calling SetIsLocked(false) on each row doesn't actually unlock the row. It has no effect. However I've found that if I call SetIsLocked(false) on a column, the column does get unlocked but if I then try to lock the cell in the first row of that column it doesn't get locked. So it's like the unlock of the column overrides the lock of the individual cell in that column. The only way I've been able to get this to work is as such:

        for (var i = 1; i < 199; i++)
        {
            for (int j = 0; j < columnTitles.Count; j++)
            {
                worksheet.Cells[i, j].SetIsLocked(false);
            }
        }

columnTitles is my list of column headers so I'm really only enabling the cells under the column headers I've added and not the complete row, but that's fine. But I've had to hardcode the highest row index of 199. Again, I don't know where that default comes from but it's not good to hardcode that index because it could always change in a future version.

So my question is, is there a better, more efficient way to lock the first row but unlock all other rows?

[Yoan]

Hello Doug,

Thank you for reaching out.

The best way to achieve the desired scenario is to only iterate the used cells of the worksheet, lock each cell if its row index is 0, and unlock the rest:

Worksheet worksheet = workbook.ActiveWorksheet;

worksheet.Protect("telerik", WorksheetProtectionOptions.Default);

CellRange usedCellRange = worksheet.UsedCellRange;
for (int rowIndex = usedCellRange.FromIndex.RowIndex; rowIndex <= usedCellRange.ToIndex.RowIndex; rowIndex++)
{
    for (int columnIndex = usedCellRange.FromIndex.ColumnIndex; columnIndex <= usedCellRange.ToIndex.ColumnIndex; columnIndex++)
    {
        CellSelection cell = worksheet.Cells[rowIndex, columnIndex];

        if (rowIndex == 0)
        {
            cell.SetIsLocked(true);
        }
        else
        {
            cell.SetIsLocked(false);
        }
    }
}

Here are some related articles on the topic:

• Worksheet Protection
• Worksheet Protection
• Iterating Used Cells

I am also attaching a sample project for your disposal. Do not hesitate to let me know if you have any questions.

Regards,

Yoan

[Doug]

Yoan,

Thank you for the response however that doesn't solve the problem. You're only unlocking USED cells. I need to unlock UNUSED cells so users can type something into all cells except for the cells in the first row.

 

 

[Yoan]

Hello Doug,

Thank you for the additional clarification.

Considering the additional requirements, the most optimal way to protect only the first row of the used cells is this:

// Unlock all columns in the document (empty ones included)
for (int columnIndex = 0; columnIndex <= SpreadsheetDefaultValues.ColumnCount - 1; columnIndex++)
{
    worksheet.Columns[columnIndex].SetIsLocked(false);
}

// Get used cells
CellRange usedCellRange = worksheet.UsedCellRange;

// Protect all first row cells of used range
for (int columnIndex = usedCellRange.FromIndex.ColumnIndex; columnIndex <= usedCellRange.ToIndex.ColumnIndex; columnIndex++)
{
    CellSelection cell = worksheet.Cells[0, columnIndex];
    cell.SetIsLocked(true);
}

NOTE: The fastest way to protect/unprotect all worksheet cells is to iterate the columns (16,384), as their max number is significantly smaller than the max number of rows (1,048,576).

I hope this helps.

Regards,

Yoan

[Doug]

Yoan,

As I noted in my original post, I tried unlocking columns and subsequently locking the cells in the first row, but it seems the unlocking of the column supersedes the locking of the cells because with my original code and also with your code above, all cells are editable. The first row does not get locked.

[Yoan]

Hello Doug,

Thank you for the reminder. The results you are describing are indeed interesting, as I am not able to successfully reproduce them on my end. I am using the same project and code snippet I previously provided in this thread, along with the 2025.2.520 Document Processing version (see attached project and "example.gif").

Considering we both have the same implementation, the only remaining factor that can possibly cause different results is the product version. Can you confirm which version you are using on your end, and if my project behaves any differently (than in "example.gif") if you run it yourself?

If possible, can you provide the project that is used on your end so I can test and examine it for myself? This way, I can replicate the same results and try to understand what is causing them. However, please keep in mind that this is a Q&A Forum thread, and anything shared here is always public and available to any user. If you have any concerns, feel free to modify my project instead. You can refactor it so that it also generates the same unusual results and send it back to me.

Thank you in advance for your cooperation. Let me know how it goes.

Regards,

Yoan

[Doug]

Maybe the issue is on the Blazor side. See attached.

[Yoan]

Hello Doug,

Please accept my apologies for the misunderstanding, and thank you for the clarification. After using the attached project, I was also able to reproduce the unexpected results.

After some investigation, I can confirm that the Blazor Spreadsheet component does not yet respect the Document Processing functionality for Workbook protection or Worksheet protection. There is already a reported feature request regarding the implementation of this support in the respective Telerik Blazor Feedback Portal: Honor protected (ReadOnly) worksheet and workbook.

I once again apologize for the inconvenience and setbacks this might be causing you. However, there is no workaround I can offer in this scenario. If you are interested in this task, please know that you can cast your vote for it to increase its priority in our backlog and subscribe to track its progress and receive notifications about potential status updates in the future.

Thank you in advance for your patience and understanding.

Regards,

Yoan