用EPPlus使列或单元格无效 [英] Make column or cells readonly with EPPlus

查看:236
本文介绍了用EPPlus使列或单元格无效的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

有没有办法使列或一组单元格锁定或仅使用EPPlus?我已经尝试过这两个单独的代码在一起,但两者似乎都没有预期的效果。整个工作表都被锁定(如果我包括 IsProtected 语句)或者根本没有。

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

编辑



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

  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());

//标题
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]。值=状态;
ws.Cells [I1]。Value =Id;

//内容
int i = 2;
foreach(结果中的var zip)
{
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;

返回新的ExcelResult
{
FileName =ZipCodes.xlsx,
Package = pck
};

ExcelResult

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

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());
}
}

第二次修改 p>

我尝试通过将 IsProtected 值设置为 true ,然后将锁定属性设置为 false ,除了最后一列之外的每一列。不仅电子表格不是只读模式,而且我可以编辑每一列中的数据。



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

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


解决方案

EPPlus可能会默认为 em>单元格被锁定,在这种情况下,您需要将 Locked 属性设置为 false 列,然后将IsProtected设置为 true


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;

解决方案

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.

这篇关于用EPPlus使列或单元格无效的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆