Make column or cells readonly with EPPlus

Question

Is there a way to make a column or group of cells locked or read only using EPPlus? I've tried the code below both separate and together however neither seems to have the desired effect. Either the entire worksheet is locked (if I include the IsProtected statement) or nothing at all.

        ws.Protection.IsProtected = true;
        ws.Column(10).Style.Locked = true;

EDIT

Here is entire block of code from my controller

        FileInfo newFile = new FileInfo("C:\\Users\\" + User.Identity.Name + "\\Desktop" + @"\\ZipCodes.xlsx");

        ExcelPackage pck = new ExcelPackage(newFile);

        var ws = pck.Workbook.Worksheets.Add("Query_" + DateTime.Now.ToString());

        //Headers
        ws.Cells["A1"].Value = "ChannelCode";
        ws.Cells["B1"].Value = "DrmTerrDesc";
        ws.Cells["C1"].Value = "IndDistrnId";
        ws.Cells["D1"].Value = "StateCode";
        ws.Cells["E1"].Value = "ZipCode";
        ws.Cells["F1"].Value = "EndDate";
        ws.Cells["G1"].Value = "EffectiveDate";
        ws.Cells["H1"].Value = "LastUpdateId";
        ws.Cells["J1"].Value = "ErrorCodes";
        ws.Cells["K1"].Value = "Status";
        ws.Cells["I1"].Value = "Id";

        //Content
        int i = 2;
        foreach (var zip in results)
        {
            ws.Cells["A" + i.ToString()].Value = zip.ChannelCode;
            ws.Cells["B" + i.ToString()].Value = zip.DrmTerrDesc;
            ws.Cells["C" + i.ToString()].Value = zip.IndDistrnId;
            ws.Cells["D" + i.ToString()].Value = zip.StateCode;
            ws.Cells["E" + i.ToString()].Value = zip.ZipCode;
            ws.Cells["F" + i.ToString()].Value = zip.EndDate.ToShortDateString();
            ws.Cells["G" + i.ToString()].Value = zip.EffectiveDate.ToShortDateString();
            ws.Cells["H" + i.ToString()].Value = zip.LastUpdateId;
            ws.Cells["J" + i.ToString()].Value = zip.ErrorCodes;
            ws.Cells["K" + i.ToString()].Value = zip.Status;
            ws.Cells["I" + i.ToString()].Value = zip.Id;

            i++;
        }

        //ws.Protection.IsProtected = true;
        ws.Column(10).Style.Locked = true;

        return new ExcelResult
            {
                FileName = "ZipCodes.xlsx",
                Package = pck
            };

ExcelResult

public class ExcelResult : ActionResult
{
    public string FileName { get; set; }
    public ExcelPackage Package { get; set; }

    public override void ExecuteResult(ControllerContext context)
    {
        context.HttpContext.Response.Buffer = true;
        context.HttpContext.Response.Clear();
        context.HttpContext.Response.AddHeader("content-disposition", "attachment; filename=" + FileName);
        context.HttpContext.Response.ContentType = "application/vnd.ms-excel";
        context.HttpContext.Response.BinaryWrite(Package.GetAsByteArray());
    }
}

Second Edit

I attempted to make the worksheet protected by setting the IsProtected value to true, then set the Locked property to false for every column except the last one. Not only was the spreadsheet not in read-only mode but I could edit the data in every column.

I did notice, however that I cannot resize the actual columns themselves, so maybe this is what I'm doing. I'd like to lock each cell in the column, however, so no new data can be entered.

        for (int a = 1; a < 10; a++)
        {
            ws.Column(a).Style.Locked = false;
        }
        ws.Protection.IsProtected = true;

Deciliter

Answer 1

EPPlus may be defaulting to all cells being locked, in which case you need to set the Locked attribute to false for the other columns, then set IsProtected to true.

Kovach

Just made an attempt according to this and added it to my post under the "Second Edit." Is that basically what you were talking about? If so can you take a look and see if you notice anything out of place? – Deciliter

Basically, yes. Though I think you can set the Locked property for the entire sheet at once, then override the one column. The other thing you need to do is set the other attributes of the Protection attribute, which control what the user can and cannot do to the locked cells. – Kovach

This seems like a really backwards way to be able to lock down a cell. In fact, why even have a Locked property if it's only use is to be able to be set to false when IsProtected is set to true? weird.... – Deciliter

This matches Excel's own functionality -- a protected sheet only protects "locked" cells, and cells are locked by default. I wish Microsoft would allow finer-grained controls, such as multiple types of protection for different ranges in the same sheet, but alas, that is not the case. – Kovach

Would you answer for this same kind of question ..? #20544541 – Hals

Answer 2

I am adding two WorkSheets and need to protect all columns except the one on third index.

This worked for me :)

worksheet2.Cells["A1"].LoadFromDataTable(dt_Data, true); //------load data from datatable
worksheet2.Protection.IsProtected = true; //--------Protect whole sheet
worksheet2.Column(3).Style.Locked = false; //-------Unlock 3rd column

Hubris

I add a password to be sure nobody can't change the content worksheet2.Protection.SetPassword(Guid.NewGuid().ToString()); – Obidiah

Answer 3

Just thought I'd post the solution in case it helps anyone else. I had to set the entire worksheet to protected but set the Locked attribute to false for each non-Id field.

        //Content
        int i = 2;
        foreach (var zip in results)
        {
            //Set cell values
            ws.Cells["A" + i.ToString()].Value = zip.ChannelCode;
            ws.Cells["B" + i.ToString()].Value = zip.DrmTerrDesc;
            ws.Cells["C" + i.ToString()].Value = zip.IndDistrnId;
            ws.Cells["D" + i.ToString()].Value = zip.StateCode;
            ws.Cells["E" + i.ToString()].Value = zip.ZipCode;
            ws.Cells["F" + i.ToString()].Value = zip.EndDate.ToShortDateString();
            ws.Cells["G" + i.ToString()].Value = zip.EffectiveDate.ToShortDateString();
            ws.Cells["H" + i.ToString()].Value = zip.LastUpdateId;
            ws.Cells["I" + i.ToString()].Value = zip.ErrorCodes;
            ws.Cells["J" + i.ToString()].Value = zip.Status;
            ws.Cells["K" + i.ToString()].Value = zip.Id;

            //Unlock non-Id fields
            ws.Cells["A" + i.ToString()].Style.Locked = false;
            ws.Cells["B" + i.ToString()].Style.Locked = false;
            ws.Cells["C" + i.ToString()].Style.Locked = false;
            ws.Cells["D" + i.ToString()].Style.Locked = false;
            ws.Cells["E" + i.ToString()].Style.Locked = false;
            ws.Cells["F" + i.ToString()].Style.Locked = false;
            ws.Cells["G" + i.ToString()].Style.Locked = false;
            ws.Cells["H" + i.ToString()].Style.Locked = false;
            ws.Cells["I" + i.ToString()].Style.Locked = false;
            ws.Cells["J" + i.ToString()].Style.Locked = false;

            i++;
        }

        //Since we have to make the whole sheet protected and unlock each cell 
        //to allow for editing this loop is necessary
        for (int a = 65000 - i; i < 65000; i++)
        {
            //Unlock non-Id fields
            ws.Cells["A" + i.ToString()].Style.Locked = false;
            ws.Cells["B" + i.ToString()].Style.Locked = false;
            ws.Cells["C" + i.ToString()].Style.Locked = false;
            ws.Cells["D" + i.ToString()].Style.Locked = false;
            ws.Cells["E" + i.ToString()].Style.Locked = false;
            ws.Cells["F" + i.ToString()].Style.Locked = false;
            ws.Cells["G" + i.ToString()].Style.Locked = false;
            ws.Cells["H" + i.ToString()].Style.Locked = false;
            ws.Cells["I" + i.ToString()].Style.Locked = false;
            ws.Cells["J" + i.ToString()].Style.Locked = false;                
        }

        //Set worksheet protection attributes
        ws.Protection.AllowInsertRows = true;
        ws.Protection.AllowSort = true;
        ws.Protection.AllowSelectUnlockedCells = true;
        ws.Protection.AllowAutoFilter = true;
        ws.Protection.AllowInsertRows = true;
        ws.Protection.IsProtected = true;

Deciliter

You should be able to accomplish the same thing with this: "ws.Cells.Style.Locked=false;ws.Cells["K"].Style.Locked=true;". That unlocks all cells on the sheet, then locks back only the cells in column K. (@Softwarehuset's code does the same as the second statement here, and I actually prefer indexing columns by number like he did, but I wanted to show you can reference them by letter as well. Either way, you can skip all the row-looping stuff.) – Kovach

Do I still need to protect the whole worksheet? One of the problems we've encountered is that is doesn't seem you can cut/paste any rows into the worksheet with it protected. – Deciliter

@Deciliter I wanted to mention, that you have defined twice - ws.Protection.AllowInsertRows = true; Is it needed? – Liddy

Answer 4

So I was referring this question and this is how I do the locking.

worksheet.Protection.IsProtected = true;
//I'm creating a template for users to fill in data.These headers
//will come from database tables later on.
//So tableHeaders is an array of strings
for (int i = 1; i <= tableHeaders.Length; i++)
            {
                worksheet.Column(i).Style.Locked = false;
            }
//And then lock the first row.
worksheet.Row(1).Style.Locked = true;
//Additionally don't allow user to change sheet names
excelPackage.Workbook.Protection.LockStructure = true;

Outbuilding

Answer 5

lock the entire sheet first and then unlock the cells which u want to unlock.

workSheet.Protection.IsProtected = true;

workSheet.Cells[2, 3, pocDeatils.CityMaster.Rows.Count + 1, 4].Style.Locked = false;

for more detail refer below link: https://epplus.codeplex.com/SourceControl/latest#SampleApp/Sample6.cs

License

Answer 6

ws.Column(10).Style.Locked = true;

should do it. please check the rest of your code for errors :)

Loney

Everything seems to be fine, as far as how I know how to use the plugin. Just added the entire block of code to the post, fyi. – Deciliter

Answer 7

1. In order to lock certain columns or group of cells, you need to first know which columns you need to be editable. Add those comma separated in 'new ExcelAddress()' which is a second parameter [Comma separation is useful when the columns editable are not in the same order]

For eg : I want to lock only columns M ws.ProtectedRanges.Add("editable", new ExcelAddress("A3:A25,L3:L25,N3:N25"));

2. Then the second part is to protect the whole worksheet ws.Protection.IsProtected = true;

Effusion