Make column or cells readonly with EPPlus

Viewed 25678

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;
7 Answers
  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"));

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