Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Make column or cells readonly with EPPlus

Tags:

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; 
like image 771
NealR Avatar asked Dec 23 '13 22:12

NealR


People also ask

Can EPPlus read XLS?

EPPlus does not work with the XLS format. Only XLSX. You'll need to find a new library.


2 Answers

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 
like image 158
Harmeet Singh Bhamra Avatar answered Sep 21 '22 23:09

Harmeet Singh Bhamra


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.

like image 29
richardtallent Avatar answered Sep 22 '22 23:09

richardtallent