首页 文章

使用EPPlus使列或单元格只读

提问于
浏览 次
14

有没有办法使用EPPlus锁定或读取一列或一组单元格？我已尝试将下面的代码分开并一起使用，但似乎都没有达到预期的效果 . 整个工作表是锁定的（如果我包含 IsProtected 语句）或根本没有 .

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

EDIT

这是我的控制器的整个代码块

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

我试图通过将 IsProtected 值设置为 true 来保护工作表，然后将 Locked 属性设置为除最后一列之外的每一列的 false . 电子表格不仅不是只读模式，而且我可以编辑每列中的数据 .

我注意到，但是我无法自己调整实际列的大小，所以也许这就是我正在做的事情 . 但是，我想锁定列中的每个单元格，因此无法输入新数据 .

for (int a = 1; a < 10; a++)
        {
            ws.Column(a).Style.Locked = false;
        }
        ws.Protection.IsProtected = true;
c# .net asp.net-mvc-3 excel epplus

6 回答

  • 6

    我正在添加两个WorkSheets，需要保护除第三个索引之外的所有列 .

    这对我有用:)

    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
    回复于
  • 4

    EPPlus可能默认为所有被锁定的单元格，在这种情况下，您需要为其他列设置 Locked 属性为 false ，然后将IsProtected设置为 true .

    回复于
  • 7

    只是想我会发布解决方案，以防它帮助其他人 . 我必须将整个工作表设置为受保护，但为每个非 Id 字段将 Locked 属性设置为false .

    //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;
    回复于
  • 0

    所以我提到了这个问题，这就是我如何进行锁定 .

    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;
    回复于
  • 0
    ws.Column(10).Style.Locked = true;

    应该这样做 . 请检查代码的其余部分是否有错误:)

    回复于
  • 2

    先锁定整张纸，然后解锁要解锁的单元格 .

    workSheet.Protection.IsProtected = true;

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

    更多细节请参考以下链接：https://epplus.codeplex.com/SourceControl/latest#SampleApp/Sample6.cs

    回复于

相关问题