Protecting certain cells in Excel - C# Epplus

Question

I have a program exporting a spreadsheet, containing headers and some data. I need to protect the headers, but leave the data cells editable.

The problem is, upon setting the worksheet as protected, all the cells become read-only.

So, the approach I am using is to check each of the cells below the header to see if they are not empty, and unlock them if so.

public void formatSpreadsheet(OfficeOpenXml.ExcelWorksheet ws)
{
    // autofit columns
    for (int i = 1; i <= ws.Dimension.End.Column; i++)
    {
        ws.Column(i).AutoFit();
    }

    // protect headers/metadata
    ws.Protection.IsProtected = true;
    int row = HEADER_ROW_OFFSET;
    int col = 1;


    while (ws.Cells[row,col].Value != null)
    {

        while (ws.Cells[row,col].Value != null)
        {
            ws.Cells[row, col].Style.Locked = false;
            col++;
        }
        row++;
    }

}

Testing for null values like so:

if (ws.Cells[row,col].Value != null)  ws.Cells[row,col].Style.Locked = false;

doesn't work.

I have also tried ToString() on the cell values, and it doesn't help.

Any ideas?

Answer 1

Your problem isn't the locking, but your loop that goes through the cells:

while (ws.Cells[row,col].Value != null)

As soon as it hits an empty cell, it will immediately exit the block, and nothing else will be performed.

The following should work fine:

// Lock the worksheet. You can do this here, or in the end. Doesn't really matter.
ws.Protection.IsProtected = true;

// Assuming `HEADER_ROW_OFFSET` is the first row that's not a header,
// we first define a "data" range as starting from the first column of that row, 
// to the very last used row & column.
var dataCells = ws.Cells[HEADER_ROW_OFFSET, 1, ws.Dimension.End.Row, ws.Dimension.End.Column];

// Now go through each cell in that range,
foreach (var cel in dataCells)
{
    // and unlock when it has content.
    if (cel.Value != null) cel.Style.Locked = false;
}