static void ProtectRange(IWorkbook workbook)
{
#region #ProtectRange
workbook.BeginUpdate();
Worksheet worksheet = workbook.Worksheets[0];
worksheet["C3:E8"].Borders.SetAllBorders(Color.Black, BorderLineStyle.Thin);
// Give specific user permission to edit a range in a protected worksheet
ProtectedRange protectedRange = worksheet.ProtectedRanges.Add("My Range", worksheet["C3:E8"]);
EditRangePermission permission = new EditRangePermission();
permission.UserName = Environment.UserName;
permission.DomainName = Environment.UserDomainName;
permission.Deny = false;
protectedRange.SecurityDescriptor = protectedRange.CreateSecurityDescriptor(new EditRangePermission[] { permission });
protectedRange.SetPassword("123");
// Protect a worksheet
if (!worksheet.IsProtected)
{
worksheet.Protect("password", WorksheetProtectionPermissions.Default);
}
worksheet.ActiveView.ShowGridlines = false;
workbook.EndUpdate();
#endregion #ProtectRange
}
/// <inheritdoc />
public int CompareTo(ExceptionHandlerRange other)
{
// Most common shortcut: If the protected range is equal, we just sort by handler start.
if (ProtectedRange == other.ProtectedRange)
{
return(HandlerRange.Start.CompareTo(other.HandlerRange.Start));
}
// Check if current EH encloses the other, and if so, order the current EH before the other.
if (ProtectedRange.Contains(other.ProtectedRange))
{
return(-1);
}
if (other.ProtectedRange.Contains(ProtectedRange))
{
return(1);
}
if (HandlerRange.Contains(other.HandlerRange))
{
return(-1);
}
if (other.HandlerRange.Contains(HandlerRange))
{
return(1);
}
// EH is not overlapping in any way, just order by start offset.
return(ProtectedRange.Start.CompareTo(other.ProtectedRange.Start));
}
/// <inheritdoc />
public override int GetHashCode()
{
unchecked
{
return((ProtectedRange.GetHashCode() * 397) ^ HandlerRange.GetHashCode());
}
}
static void ProtectRange(Workbook workbook)
{
#region #ProtectRange
Worksheet worksheet = workbook.Worksheets["ProtectionSample"];
workbook.Worksheets.ActiveWorksheet = worksheet;
worksheet["B2:J5"].Borders.SetOutsideBorders(Color.Red, BorderLineStyle.Thin);
// Specify user permission to edit a range in a protected worksheet.
ProtectedRange protectedRange = worksheet.ProtectedRanges.Add("My Range", worksheet["B2:J5"]);
EditRangePermission permission = new EditRangePermission();
permission.UserName = Environment.UserName;
permission.DomainName = Environment.UserDomainName;
permission.Deny = false;
protectedRange.SecurityDescriptor = protectedRange.CreateSecurityDescriptor(new EditRangePermission[] { permission });
protectedRange.SetPassword("123");
// Protect the worksheet with a password.
if (!worksheet.IsProtected)
{
worksheet.Protect("password", WorksheetProtectionPermissions.Default);
}
// Add a note.
worksheet["B2"].Value = "This cell range is protected with a password. \n You cannot edit or format it until protection is removed." +
"\nTo remove protection, double-click the range and enter \"123\".";
worksheet.Visible = true;
#endregion #ProtectRange
}
public void AddProtectedRanges(int startColumn, int endColumn, int startRow, int endRow)
{
var protectedRange = new ProtectedRange();
protectedRange.WarningOnly = true;
protectedRange.Range = new GridRange()
{
StartColumnIndex = startColumn,
EndColumnIndex = endColumn,
StartRowIndex = startRow,
EndRowIndex = endRow,
SheetId = _sheet.Properties.SheetId,
};
_sheet.ProtectedRanges.Add(protectedRange);
}
static void ProtectRange(IWorkbook workbook)
{
#region #ProtectRange
Worksheet worksheet = workbook.Worksheets["ProtectionSample"];
workbook.Worksheets.ActiveWorksheet = worksheet;
worksheet["B2:J5"].Borders.SetOutsideBorders(Color.Red, BorderLineStyle.Thin);
ProtectedRange protectedRange = worksheet.ProtectedRanges.Add("My Range", worksheet["B2:J5"]);
EditRangePermission permission = new EditRangePermission();
permission.UserName = "******";
permission.DomainName = "MyDomain";
permission.Deny = false;
protectedRange.SecurityDescriptor = protectedRange.CreateSecurityDescriptor(new EditRangePermission[] { permission });
protectedRange.SetPassword("letmeedit");
if (!worksheet.IsProtected)
{
worksheet.Protect("password", WorksheetProtectionPermissions.Default);
}
worksheet["B2"].Value = "This cell range is now protected by password. \n You cannot edit or format it until protection is removed." +
"\nTo remove protection, double click the range and enter \"letmeedit\".";
worksheet.Visible = true;
#endregion #ProtectRange
}
/// <summary>
/// Protect the sheet.
/// </summary>
/// <param name="Worksheet"></param>
/// <param name="pageM"></param>
/// <param name="sheetProtection"></param>
/// <param name="pRanges"></param>
/// <param name="lockedColumns"></param>
private void ProtectSheet(Worksheet Worksheet, out PageMargins pageM, out SheetProtection sheetProtection,
out ProtectedRanges pRanges, string[] editableColumns, DataTable dt)
{
pageM = Worksheet.GetFirstChild <PageMargins>();
sheetProtection = new SheetProtection();
sheetProtection.Sheet = true;
sheetProtection.Objects = true;
sheetProtection.Scenarios = true;
// Set the password.
sheetProtection.Password = new HexBinaryValue()
{
Value = "CC1A"
};
pRanges = new ProtectedRanges();
if (editableColumns.Length > 0)
{
int i = 0;
foreach (string columnName in editableColumns)
{
i++;
ProtectedRange pRange = new ProtectedRange();
ListValue <StringValue> lValue = new ListValue <StringValue>();
// Get Excel Column Number.
string columnindex = GetExcelColumnNumber(columnName, dt);
lValue.InnerText = columnindex + "1:" + columnindex + (dt.Rows.Count + 1).ToString();
// Assign the editable columns.
pRange.SequenceOfReferences = lValue;
pRange.Name = "Editable_" + i.ToString();
pRanges.Append(pRange);
}
}
}
/// <summary> /// Determines whether two exception handlers are considered equal. /// </summary> /// <param name="other">The other exception handler.</param> /// <returns><c>true</c> if the handler is equal, <c>false</c> otherwise.</returns> public bool Equals(ExceptionHandlerRange other) => ProtectedRange.Equals(other.ProtectedRange) && HandlerRange.Equals(other.HandlerRange);
