diff --git a/.gitignore b/.gitignore index 14f56ef..9c4786a 100644 --- a/.gitignore +++ b/.gitignore @@ -28,3 +28,4 @@ _ReSharper*/ [Tt]est[Rr]esult* /packages +/.vs diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs index 645d536..39af711 100644 --- a/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/Excel/XLWorkbook.cs @@ -864,12 +864,18 @@ public XLWorkbook SetLockStructure(Boolean value) { LockStructure = value; return this; } public Boolean LockWindows { get; set; } public XLWorkbook SetLockWindows(Boolean value) { LockWindows = value; return this; } - + internal HexBinaryValue LockPassword { get; set; } + public void Protect() { Protect(true); } + public void Protect(string password) + { + Protect(true, false, password); + } + public void Protect(Boolean lockStructure) { Protect(lockStructure, false); @@ -877,8 +883,77 @@ public void Protect(Boolean lockStructure, Boolean lockWindows) { + Protect(lockStructure, lockWindows, null); + } + + public void Protect(Boolean lockStructure, Boolean lockWindows, String lockPassword) + { + if (lockPassword != null) + { + var hashPassword = GetPasswordHash(lockPassword); + if (LockPassword != null) + { + if (LockPassword != hashPassword) + { + throw new ArgumentException("Invalid password"); + } + else + { + if (lockStructure || lockWindows) + { + throw new InvalidOperationException("The workbook is already protected"); + } + else + { + //Unprotect workbook using password. + LockPassword = null; + } + } + } + else + { + if (lockStructure || lockWindows) + { + //Protect workbook using password. + LockPassword = hashPassword; + } + } + } + else + { + if (LockPassword != null) + { + throw new InvalidOperationException("The workbook is password protected"); + } + } LockStructure = lockStructure; - LockWindows = LockWindows; + LockWindows = lockWindows; + } + + public void Unprotect() + { + Protect(false, false); + } + + public void Unprotect(string password) + { + Protect(false, false, password); + } + + private String GetPasswordHash(String password) + { + Int32 pLength = password.Length; + Int32 hash = 0; + if (pLength == 0) return String.Empty; + + for (Int32 i = pLength - 1; i >= 0; i--) + { + hash ^= password[i]; + hash = hash >> 14 & 0x01 | hash << 1 & 0x7fff; + } + hash ^= 0x8000 | 'N' << 8 | 'K'; + hash ^= pLength; + return hash.ToString("X"); } } } diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 76c27ec..440a937 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -95,6 +95,8 @@ LockStructure = wbProtection.LockStructure.Value; if (wbProtection.LockWindows != null) LockWindows = wbProtection.LockWindows.Value; + if (wbProtection.WorkbookPassword != null) + LockPassword = wbProtection.WorkbookPassword.Value; } var calculationProperties = dSpreadsheet.WorkbookPart.Workbook.CalculationProperties; diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index 9b099a8..ac60a1a 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -571,6 +571,8 @@ #endregion + #region WorkbookProtection + if (LockStructure || LockWindows) { if (workbook.WorkbookProtection == null) @@ -578,12 +580,17 @@ workbook.WorkbookProtection.LockStructure = LockStructure; workbook.WorkbookProtection.LockWindows = LockWindows; + + if (LockPassword != null) + workbook.WorkbookProtection.WorkbookPassword = LockPassword; } else { workbook.WorkbookProtection = null; } + #endregion + if (workbook.BookViews == null) workbook.BookViews = new BookViews(); @@ -623,9 +630,9 @@ { if (XLHelper.IsNullOrWhiteSpace(xlSheet.RelId)) { - rId = String.Format("rId{0}", xlSheet.SheetId); + rId = String.Format("rId{0}", xlSheet.SheetId); context.RelIdGenerator.AddValues(new List { rId }, RelType.Workbook); - } + } else rId = xlSheet.RelId; } diff --git a/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML_Examples/ClosedXML_Examples.csproj index de7cb8e..a424f68 100644 --- a/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -85,6 +85,7 @@ + diff --git a/ClosedXML_Examples/Misc/WorkbookProtection.cs b/ClosedXML_Examples/Misc/WorkbookProtection.cs new file mode 100644 index 0000000..6f2dfba --- /dev/null +++ b/ClosedXML_Examples/Misc/WorkbookProtection.cs @@ -0,0 +1,59 @@ +using System; +using ClosedXML.Excel; + +namespace ClosedXML_Examples.Misc +{ + public class WorkbookProtection : IXLExample + { + #region Variables + + // Public + + // Private + + + #endregion + + #region Properties + + // Public + + // Private + + // Override + + + #endregion + + #region Events + + // Public + + // Private + + // Override + + + #endregion + + #region Methods + + // Public + public void Create(String filePath) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Workbook Protection"); + wb.Protect(true, false, "Abc@123"); + wb.SaveAs(filePath); + } + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML_Sandbox/PerformanceRunner.cs b/ClosedXML_Sandbox/PerformanceRunner.cs index 2d4f570..06c84e8 100644 --- a/ClosedXML_Sandbox/PerformanceRunner.cs +++ b/ClosedXML_Sandbox/PerformanceRunner.cs @@ -47,7 +47,16 @@ var ws = wb.Worksheets.First(); var cell = ws.FirstCellUsed(); Console.WriteLine(cell.Value); + wb.Protect(false, true, "AAA"); + wb.Save(); } + + //using (var wb = new XLWorkbook("test.xlsx")) + //{ + // wb.Worksheets.Add("Sheet2"); + // wb.Protect("BBB"); + // wb.Save(); + //} } private static void CreateMergedCell(IXLWorksheet worksheet) diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index 47cca6a..9ddb48c 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -48,9 +48,8 @@ True - - ..\packages\NUnit.3.4.1\lib\net45\nunit.framework.dll - True + + ..\packages\NUnit.3.6.1\lib\net45\nunit.framework.dll @@ -269,9 +268,12 @@ - + + Designer + + diff --git a/ClosedXML_Tests/Examples/MiscTests.cs b/ClosedXML_Tests/Examples/MiscTests.cs index 47865bc..9e5ad27 100644 --- a/ClosedXML_Tests/Examples/MiscTests.cs +++ b/ClosedXML_Tests/Examples/MiscTests.cs @@ -198,5 +198,11 @@ { TestHelper.RunTestExample(@"Misc\WorkbookProperties.xlsx"); } + + [Test] + public void WorkbookProtection() + { + TestHelper.RunTestExample(@"Misc\WorkbookProtection.xlsx"); + } } } diff --git a/ClosedXML_Tests/Excel/Misc/XLWorkbookTests.cs b/ClosedXML_Tests/Excel/Misc/XLWorkbookTests.cs index 601bc06..2be3864 100644 --- a/ClosedXML_Tests/Excel/Misc/XLWorkbookTests.cs +++ b/ClosedXML_Tests/Excel/Misc/XLWorkbookTests.cs @@ -255,5 +255,72 @@ Assert.AreEqual("$A$1:$A$1", wsRanges.First().RangeAddress.ToStringFixed()); Assert.AreEqual("$A$3:$A$3", wsRanges.Last().RangeAddress.ToStringFixed()); } + + [Test] + public void WbProtect1() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Sheet1"); + wb.Protect(); + Assert.IsTrue(wb.LockStructure); + Assert.IsFalse(wb.LockWindows); + } + } + + [Test] + public void WbProtect2() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Sheet1"); + wb.Protect(true, false); + Assert.IsTrue(wb.LockStructure); + Assert.IsFalse(wb.LockWindows); + } + } + + [Test] + public void WbProtect3() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Sheet1"); + wb.Protect("Abc@123"); + Assert.IsTrue(wb.LockStructure); + Assert.IsFalse(wb.LockWindows); + Assert.Catch(wb.Unprotect); + } + } + + [Test] + public void WbProtect4() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Sheet1"); + wb.Protect(); + Assert.IsTrue(wb.LockStructure); + Assert.IsFalse(wb.LockWindows); + wb.Protect("Abc@123"); + Assert.IsTrue(wb.LockStructure); + Assert.IsFalse(wb.LockWindows); + } + } + + [Test] + public void WbProtect5() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("Sheet1"); + wb.Protect(true, false, "Abc@123"); + Assert.IsTrue(wb.LockStructure); + Assert.IsFalse(wb.LockWindows); + wb.Unprotect("Abc@123"); + Assert.IsFalse(wb.LockStructure); + Assert.IsFalse(wb.LockWindows); + } + } } } \ No newline at end of file diff --git a/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProtection.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProtection.xlsx new file mode 100644 index 0000000..f410f48 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProtection.xlsx Binary files differ