使用EPPlus设置Excel工作表保护 [英] Setting Excel worksheet protection with EPPlus
问题描述
我正在尝试使用EPPlus设置XLSM文件的工作表权限,但似乎我只能设置默认保护级别,而未设置单个保护。作为记录,我正在尝试以编程方式完成这篇文章。这是我正在使用的代码:
I'm trying to set worksheet permissions for an XLSM file using EPPlus but it seems I can only set the default protection level, individual protections are not being set. For the record, I'm trying to accomplish programmatically method 1 in this article. Here's the code I'm using:
using (var p = new ExcelPackage("output.xlsm"))
{
var ws = p.Workbook.Worksheets["MySheet"];
// Set some cell values here
// Filtering, sorting, protection
ws.Cells[7, 1, 10, 5].AutoFilter = true;
ws.View.FreezePanes(7, 1);
ws.ProtectedRanges.Add("FilteredCells", new ExcelAddress(7, 1, 10, 5));
// Worksheet protection
ws.Protection.AllowAutoFilter = true;
ws.Protection.AllowDeleteColumns = false;
ws.Protection.AllowDeleteRows = false;
ws.Protection.AllowEditObject = false;
ws.Protection.AllowEditScenarios = false;
ws.Protection.AllowFormatCells = false;
ws.Protection.AllowFormatColumns = false;
ws.Protection.AllowFormatRows = false;
ws.Protection.AllowInsertColumns = false;
ws.Protection.AllowInsertHyperlinks = false;
ws.Protection.AllowInsertRows = false;
ws.Protection.AllowPivotTables = false;
ws.Protection.AllowSelectLockedCells = false;
ws.Protection.AllowSelectUnlockedCells = true;
ws.Protection.AllowSort = true;
ws.Protection.IsProtected = true;
ws.Protection.SetPassword("hunter2");
p.SaveAs(new FileInfo("output.xlsm"));
}
这运行时没有错误,但是当我在Excel中打开文件或加载时它回到EPPlus,我发现已应用了不同的保护选项:
This runs without errors, but when I open the file in Excel, or load it back into EPPlus, I find that different protection options have been applied:
AllowAutoFilter = false
AllowDeleteColumns = false
AllowDeleteRows = false
AllowEditObject = true
AllowEditScenarios = true
AllowFormatCells = false
AllowFormatColumns = false
AllowFormatRows = false
AllowInsertColumns = false
AllowInsertHyperlinks = false
AllowInsertRows = false
AllowPivotTables = false
AllowSelectLockedCells = true
AllowSelectUnlockedCells = true
AllowSort = false
IsProtected = true
这些显然不是我之前设置的权限,那么如何确保设置正确?其他所有内容均已正确保存。
These obviously aren't the permissions I set before, so how can I make sure they are set correctly? Everything else is saved correctly.
推荐答案
以下是来源:
https://github.com/pruiz/EPPlus/blob/master/ EPPlus / ExcelSheetProtection.cs
设置 IsProtected
属性会覆盖您的选择:
Setting the IsProtected
property is overwriting your choices:
public bool IsProtected
{
get
{
return GetXmlNodeBool(_isProtectedPath, false);
}
set
{
SetXmlNodeBool(_isProtectedPath, value, false);
if (value)
{
AllowEditObject = true;
AllowEditScenarios = true;
}
else
{
DeleteAllNode(_isProtectedPath); //delete the whole sheetprotection node
}
}
}
将您的 IsProtected = true
调用移至代码的开头或根据需要进行处理,但是您无意中覆盖了先前的选择。我将查看该链接上的属性,以查看哪些属性将覆盖您现有的选择。
Move your IsProtected = true
call to the start of the code or handle however you want, but you are accidently overriding your previous choice. I would look at the properties at that link to see which ones are going to override your existing selections.
这篇关于使用EPPlus设置Excel工作表保护的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!