diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index e61a62f..19f3929 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -143,6 +143,7 @@ + @@ -153,6 +154,8 @@ + + @@ -181,6 +184,7 @@ + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index fb027fc..2b0f160 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -47,6 +47,8 @@ { cellValue = value.ToString(); dataType = XLCellValues.Text; + if (cellValue.Contains(Environment.NewLine) && !Style.Alignment.WrapText) + Style.Alignment.WrapText = true; } else if (value is TimeSpan) { @@ -585,16 +587,11 @@ { val = val.Substring(1, val.Length - 1); dataType = XLCellValues.Text; + if (val.Contains(Environment.NewLine) && !Style.Alignment.WrapText) + Style.Alignment.WrapText = true; } else if (value is TimeSpan || (TimeSpan.TryParse(val, out tsTest) && !Double.TryParse(val, out dTest))) { - //if (TimeSpan.TryParse(val, out tsTest)) - // val = baseDate.Add(tsTest).ToOADate().ToString(); - //else - //{ - // TimeSpan timeSpan = (TimeSpan)value; - // val = baseDate.Add(timeSpan).ToOADate().ToString(); - //} dataType = XLCellValues.TimeSpan; if (Style.NumberFormat.Format == String.Empty && Style.NumberFormat.NumberFormatId == 0) Style.NumberFormat.NumberFormatId = 46; @@ -623,6 +620,8 @@ else { dataType = XLCellValues.Text; + if (val.Contains(Environment.NewLine) && !Style.Alignment.WrapText) + Style.Alignment.WrapText = true; } cellValue = val; @@ -1511,5 +1510,15 @@ this.AsRange().AddToNamed(rangeName, scope, comment); return this; } + + public IXLRanges RangesUsed + { + get + { + var retVal = new XLRanges(worksheet.Internals.Workbook, this.Style); + retVal.Add(this.AsRange()); + return retVal; + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs index 6e21c2f..65b5ea5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCells.cs @@ -168,5 +168,15 @@ this.ForEach(c => c.FormulaR1C1 = value); } } + + public IXLRanges RangesUsed + { + get + { + var retVal = new XLRanges(worksheet.Internals.Workbook, this.Style); + this.ForEach(c => retVal.Add(c.AsRange())); + return retVal; + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs index e67448d..45ae353 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs @@ -235,5 +235,15 @@ } return (IXLCells)cells; } + + public IXLRanges RangesUsed + { + get + { + var retVal = new XLRanges(worksheet.Internals.Workbook, this.Style); + this.ForEach(c => retVal.Add(c.AsRange())); + return retVal; + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLSheetProtection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLSheetProtection.cs new file mode 100644 index 0000000..1620aa6 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLSheetProtection.cs @@ -0,0 +1,49 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLSheetProtection + { + Boolean Protected { get; set; } + + Boolean AutoFilter { get; set; } + Boolean DeleteColumns { get; set; } + Boolean DeleteRows { get; set; } + Boolean FormatCells { get; set; } + Boolean FormatColumns { get; set; } + Boolean FormatRows { get; set; } + Boolean InsertColumns { get; set; } + Boolean InsertHyperlinks { get; set; } + Boolean InsertRows { get; set; } + Boolean Objects { get; set; } + Boolean PivotTables { get; set; } + Boolean Scenarios { get; set; } + Boolean SelectLockedCells { get; set; } + Boolean SelectUnlockedCells { get; set; } + Boolean Sort { get; set; } + + IXLSheetProtection SetAutoFilter(); IXLSheetProtection SetAutoFilter(Boolean value); + IXLSheetProtection SetDeleteColumns(); IXLSheetProtection SetDeleteColumns(Boolean value); + IXLSheetProtection SetDeleteRows(); IXLSheetProtection SetDeleteRows(Boolean value); + IXLSheetProtection SetFormatCells(); IXLSheetProtection SetFormatCells(Boolean value); + IXLSheetProtection SetFormatColumns(); IXLSheetProtection SetFormatColumns(Boolean value); + IXLSheetProtection SetFormatRows(); IXLSheetProtection SetFormatRows(Boolean value); + IXLSheetProtection SetInsertColumns(); IXLSheetProtection SetInsertColumns(Boolean value); + IXLSheetProtection SetInsertHyperlinks(); IXLSheetProtection SetInsertHyperlinks(Boolean value); + IXLSheetProtection SetInsertRows(); IXLSheetProtection SetInsertRows(Boolean value); + IXLSheetProtection SetObjects(); IXLSheetProtection SetObjects(Boolean value); + IXLSheetProtection SetPivotTables(); IXLSheetProtection SetPivotTables(Boolean value); + IXLSheetProtection SetScenarios(); IXLSheetProtection SetScenarios(Boolean value); + IXLSheetProtection SetSelectLockedCells(); IXLSheetProtection SetSelectLockedCells(Boolean value); + IXLSheetProtection SetSelectUnlockedCells(); IXLSheetProtection SetSelectUnlockedCells(Boolean value); + IXLSheetProtection SetSort(); IXLSheetProtection SetSort(Boolean value); + + IXLSheetProtection Protect(); + IXLSheetProtection Protect(String password); + IXLSheetProtection Unprotect(); + IXLSheetProtection Unprotect(String password); + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index 7fbb3c7..c2e1743 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -261,5 +261,11 @@ XLWorksheetVisibility Visibility { get; set; } IXLWorksheet Hide(); IXLWorksheet Unhide(); + + IXLSheetProtection Protection { get; } + IXLSheetProtection Protect(); + IXLSheetProtection Protect(String password); + IXLSheetProtection Unprotect(); + IXLSheetProtection Unprotect(String password); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index c3b3b7a..cd33077 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -1008,5 +1008,14 @@ } } + public IXLRanges RangesUsed + { + get + { + var retVal = new XLRanges(Worksheet.Internals.Workbook, this.Style); + retVal.Add(this.AsRange()); + return retVal; + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs index 75bad0c..98c79f9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs @@ -123,5 +123,15 @@ } return (IXLCells)cells; } + + public IXLRanges RangesUsed + { + get + { + var retVal = new XLRanges(worksheet.Internals.Workbook, this.Style); + this.ForEach(c => retVal.Add(c.AsRange())); + return retVal; + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs index 245a1a5..61c8808 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs @@ -122,5 +122,15 @@ } return (IXLCells)cells; } + + public IXLRanges RangesUsed + { + get + { + var retVal = new XLRanges(worksheet.Internals.Workbook, this.Style); + this.ForEach(c => retVal.Add(c.AsRange())); + return retVal; + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs index f92f72c..6b9677f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs @@ -215,5 +215,13 @@ ranges.ForEach(r => r.SetValue(value)); return this; } + + public IXLRanges RangesUsed + { + get + { + return this; + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs index fa55ed6..66d6d1e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs @@ -237,5 +237,15 @@ } return (IXLCells)cells; } + + public IXLRanges RangesUsed + { + get + { + var retVal = new XLRanges(worksheet.Internals.Workbook, this.Style); + this.ForEach(c => retVal.Add(c.AsRange())); + return retVal; + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLBorder.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLBorder.cs index fddbf2f..1159ba0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLBorder.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLBorder.cs @@ -25,6 +25,9 @@ } public interface IXLBorder: IEquatable { + XLBorderStyleValues OutsideBorder { set; } + IXLColor OutsideBorderColor { set; } + XLBorderStyleValues LeftBorder { get; set; } IXLColor LeftBorderColor { get; set; } XLBorderStyleValues RightBorder { get; set; } @@ -38,6 +41,9 @@ XLBorderStyleValues DiagonalBorder { get; set; } IXLColor DiagonalBorderColor { get; set; } + IXLStyle SetOutsideBorder(XLBorderStyleValues value); + IXLStyle SetOutsideBorderColor(IXLColor value); + IXLStyle SetLeftBorder(XLBorderStyleValues value); IXLStyle SetLeftBorderColor(IXLColor value); IXLStyle SetRightBorder(XLBorderStyleValues value); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLProtection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLProtection.cs new file mode 100644 index 0000000..1b4732c --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLProtection.cs @@ -0,0 +1,17 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLProtection : IEquatable + { + Boolean Locked { get; set; } + Boolean Hidden { get; set; } + + IXLStyle SetLocked(); IXLStyle SetLocked(Boolean value); + IXLStyle SetHidden(); IXLStyle SetHidden(Boolean value); + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStyle.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStyle.cs index 3bc79bc..a8b75f1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStyle.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStyle.cs @@ -13,5 +13,6 @@ IXLFont Font { get; set; } IXLNumberFormat NumberFormat { get; set; } IXLNumberFormat DateFormat { get; } + IXLProtection Protection { get; set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStylized.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStylized.cs index e82d745..6e15733 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStylized.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/IXLStylized.cs @@ -11,6 +11,7 @@ IEnumerable Styles { get; } Boolean UpdatingStyle { get; set; } IXLStyle InnerStyle { get; set; } + IXLRanges RangesUsed { get; } //Boolean IsDefault { get; set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs index 98aa7cd..a5656fe 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLBorder.cs @@ -31,6 +31,40 @@ } } + public XLBorderStyleValues OutsideBorder + { + set + { + if (container != null && !container.UpdatingStyle) + { + foreach (var r in container.RangesUsed) + { + r.FirstColumn().Style.Border.LeftBorder = value; + r.LastColumn().Style.Border.RightBorder = value; + r.FirstRow().Style.Border.TopBorder = value; + r.LastRow().Style.Border.BottomBorder = value; + } + } + } + } + + public IXLColor OutsideBorderColor + { + set + { + if (container != null && !container.UpdatingStyle) + { + foreach (var r in container.RangesUsed) + { + r.FirstColumn().Style.Border.LeftBorderColor = value; + r.LastColumn().Style.Border.RightBorderColor = value; + r.FirstRow().Style.Border.TopBorderColor = value; + r.LastRow().Style.Border.BottomBorderColor = value; + } + } + } + } + private XLBorderStyleValues leftBorder; public XLBorderStyleValues LeftBorder { @@ -291,6 +325,9 @@ ^ DiagonalDown.GetHashCode(); } + public IXLStyle SetOutsideBorder(XLBorderStyleValues value) { OutsideBorder = value; return container.Style; } + public IXLStyle SetOutsideBorderColor(IXLColor value) { OutsideBorderColor = value; return container.Style; } + public IXLStyle SetLeftBorder(XLBorderStyleValues value) { LeftBorder = value; return container.Style; } public IXLStyle SetLeftBorderColor(IXLColor value) { LeftBorderColor = value; return container.Style; } public IXLStyle SetRightBorder(XLBorderStyleValues value) { RightBorder = value; return container.Style; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLProtection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLProtection.cs new file mode 100644 index 0000000..daf5965 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLProtection.cs @@ -0,0 +1,114 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLProtection : IXLProtection + { + IXLStylized container; + + private Boolean locked; + public Boolean Locked + { + get + { + return locked; + } + set + { + if (container != null && !container.UpdatingStyle) + container.Styles.ForEach(s => s.Protection.Locked = value); + else + locked = value; + } + } + + private Boolean hidden; + public Boolean Hidden + { + get + { + return hidden; + } + set + { + if (container != null && !container.UpdatingStyle) + container.Styles.ForEach(s => s.Protection.Hidden = value); + else + hidden = value; + } + } + + #region Constructors + + public XLProtection() + : this(null, XLWorkbook.DefaultStyle.Protection) + { + } + + public XLProtection(IXLStylized container, IXLProtection defaultProtection = null) + { + this.container = container; + if (defaultProtection != null) + { + locked = defaultProtection.Locked; + hidden = defaultProtection.Hidden; + } + } + + #endregion + + public bool Equals(IXLProtection other) + { + return this.Locked.Equals(other.Locked) + && this.Hidden.Equals(other.Hidden); + } + + public override bool Equals(object obj) + { + return this.Equals((IXLProtection)obj); + } + + public override int GetHashCode() + { + if (Locked) + if (Hidden) + return 11; + else + return 10; + else + if (Hidden) + return 1; + else + return 0; + } + + public override string ToString() + { + StringBuilder sb = new StringBuilder(); + + if (this.Locked) + sb.Append("Locked"); + + if (this.Hidden) + { + if (this.Locked) + sb.Append("-"); + + sb.Append("Hidden"); + } + + if (sb.Length < 0) + sb.Append("None"); + + return sb.ToString(); + } + + public IXLStyle SetLocked() { Locked = true; return container.Style; } public IXLStyle SetLocked(Boolean value) { Locked = value; return container.Style; } + public IXLStyle SetHidden() { Hidden = true; return container.Style; } public IXLStyle SetHidden(Boolean value) { Hidden = value; return container.Style; } + + } + +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStyle.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStyle.cs index 42a2dc8..46a4316 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStyle.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStyle.cs @@ -16,6 +16,7 @@ Border = new XLBorder(container, initialStyle.Border); Fill = new XLFill(container, initialStyle.Fill); NumberFormat = new XLNumberFormat(container, initialStyle.NumberFormat); + Protection = new XLProtection(container, initialStyle.Protection); } else { @@ -24,6 +25,7 @@ Border = new XLBorder(container, null); Fill = new XLFill(container); NumberFormat = new XLNumberFormat(container, null); + Protection = new XLProtection(container, null); } DateFormat = NumberFormat; @@ -51,6 +53,8 @@ } } + public IXLProtection Protection { get; set; } + public IXLNumberFormat DateFormat { get; private set; } public override string ToString() @@ -66,6 +70,8 @@ sb.Append(NumberFormat.ToString()); sb.Append(" Alignment: "); sb.Append(Alignment.ToString()); + sb.Append(" Protection: "); + sb.Append(Protection.ToString()); return sb.ToString(); } @@ -77,6 +83,7 @@ && this.Border.Equals(other.Border) && this.NumberFormat.Equals(other.NumberFormat) && this.Alignment.Equals(other.Alignment) + && this.Protection.Equals(other.Protection) ; } @@ -91,7 +98,8 @@ ^ Fill.GetHashCode() ^ Border.GetHashCode() ^ NumberFormat.GetHashCode() - ^ Alignment.GetHashCode(); + ^ Alignment.GetHashCode() + ^ Protection.GetHashCode(); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStylizedContainer.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStylizedContainer.cs index 3b758f5..ae23c48 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStylizedContainer.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Style/XLStylizedContainer.cs @@ -12,6 +12,7 @@ { this.Style = style; this.container = container; + this.RangesUsed = container.RangesUsed; } public IXLStyle Style { get; set; } @@ -29,5 +30,7 @@ public bool UpdatingStyle { get; set; } public IXLStyle InnerStyle { get; set; } + + public IXLRanges RangesUsed { get; set; } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs index bbc63d7..3e4c0b0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs @@ -116,5 +116,15 @@ } return (IXLCells)cells; } + + public IXLRanges RangesUsed + { + get + { + var retVal = new XLRanges(worksheet.Internals.Workbook, this.Style); + this.ForEach(c => retVal.Add(c.AsRange())); + return retVal; + } + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLSheetProtection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLSheetProtection.cs new file mode 100644 index 0000000..64b701d --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLSheetProtection.cs @@ -0,0 +1,115 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLSheetProtection: IXLSheetProtection + { + public XLSheetProtection() + { + SelectLockedCells = true; + SelectUnlockedCells = true; + } + + public Boolean Protected { get; set; } + internal String PasswordHash { get; set; } + public String Password + { + set + { + PasswordHash = GetPasswordHash(value); + } + } + + public Boolean AutoFilter { get; set; } + public Boolean DeleteColumns { get; set; } + public Boolean DeleteRows { get; set; } + public Boolean FormatCells { get; set; } + public Boolean FormatColumns { get; set; } + public Boolean FormatRows { get; set; } + public Boolean InsertColumns { get; set; } + public Boolean InsertHyperlinks { get; set; } + public Boolean InsertRows { get; set; } + public Boolean Objects { get; set; } + public Boolean PivotTables { get; set; } + public Boolean Scenarios { get; set; } + public Boolean SelectLockedCells { get; set; } + public Boolean SelectUnlockedCells { get; set; } + public Boolean Sort { get; set; } + + public IXLSheetProtection SetAutoFilter() { AutoFilter = true; return this; } public IXLSheetProtection SetAutoFilter(Boolean value) { AutoFilter = value; return this; } + public IXLSheetProtection SetDeleteColumns() { DeleteColumns = true; return this; } public IXLSheetProtection SetDeleteColumns(Boolean value) { DeleteColumns = value; return this; } + public IXLSheetProtection SetDeleteRows() { DeleteRows = true; return this; } public IXLSheetProtection SetDeleteRows(Boolean value) { DeleteRows = value; return this; } + public IXLSheetProtection SetFormatCells() { FormatCells = true; return this; } public IXLSheetProtection SetFormatCells(Boolean value) { FormatCells = value; return this; } + public IXLSheetProtection SetFormatColumns() { FormatColumns = true; return this; } public IXLSheetProtection SetFormatColumns(Boolean value) { FormatColumns = value; return this; } + public IXLSheetProtection SetFormatRows() { FormatRows = true; return this; } public IXLSheetProtection SetFormatRows(Boolean value) { FormatRows = value; return this; } + public IXLSheetProtection SetInsertColumns() { InsertColumns = true; return this; } public IXLSheetProtection SetInsertColumns(Boolean value) { InsertColumns = value; return this; } + public IXLSheetProtection SetInsertHyperlinks() { InsertHyperlinks = true; return this; } public IXLSheetProtection SetInsertHyperlinks(Boolean value) { InsertHyperlinks = value; return this; } + public IXLSheetProtection SetInsertRows() { InsertRows = true; return this; } public IXLSheetProtection SetInsertRows(Boolean value) { InsertRows = value; return this; } + public IXLSheetProtection SetObjects() { Objects = true; return this; } public IXLSheetProtection SetObjects(Boolean value) { Objects = value; return this; } + public IXLSheetProtection SetPivotTables() { PivotTables = true; return this; } public IXLSheetProtection SetPivotTables(Boolean value) { PivotTables = value; return this; } + public IXLSheetProtection SetScenarios() { Scenarios = true; return this; } public IXLSheetProtection SetScenarios(Boolean value) { Scenarios = value; return this; } + public IXLSheetProtection SetSelectLockedCells() { SelectLockedCells = true; return this; } public IXLSheetProtection SetSelectLockedCells(Boolean value) { SelectLockedCells = value; return this; } + public IXLSheetProtection SetSelectUnlockedCells() { SelectUnlockedCells = true; return this; } public IXLSheetProtection SetSelectUnlockedCells(Boolean value) { SelectUnlockedCells = value; return this; } + public IXLSheetProtection SetSort() { Sort = true; return this; } public IXLSheetProtection SetSort(Boolean value) { Sort = value; return this; } + + public IXLSheetProtection Protect() + { + return Protect(String.Empty); + } + + public IXLSheetProtection Protect(String password) + { + if (Protected) + { + throw new InvalidOperationException("The worksheet is already protected"); + } + else + { + Protected = true; + PasswordHash = GetPasswordHash(password); + } + return this; + } + + public IXLSheetProtection Unprotect() + { + return Unprotect(String.Empty); + } + + public IXLSheetProtection Unprotect(String password) + { + if (Protected) + { + String hash = GetPasswordHash(password); + if (hash != PasswordHash) + throw new ArgumentException("Invalid password"); + else + { + Protected = false; + PasswordHash = String.Empty; + } + } + + return this; + } + + 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/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index d4ba608..abe6bb5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -252,7 +252,12 @@ TextRotation = 0, Vertical = XLAlignmentVerticalValues.Bottom, WrapText = false - } + }, + Protection = new XLProtection(null) + { + Locked = true, + Hidden = false + } }; return defaultStyle; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 3c1b64c..4376fba 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -386,6 +386,8 @@ } #endregion + LoadSheetProtection(worksheetPart, ws); + LoadDataValidations(worksheetPart, ws); LoadHyperlinks(worksheetPart, ws); @@ -475,6 +477,30 @@ return XLCellValues.Text; } + private void LoadSheetProtection(WorksheetPart worksheetPart, XLWorksheet ws) + { + var sheetProtectionQuery = worksheetPart.Worksheet.Descendants(); + if (sheetProtectionQuery.Count() > 0) + { + var sp = (SheetProtection)sheetProtectionQuery.First(); + if (sp.Sheet != null) ws.Protection.Protected = sp.Sheet.Value; + if (sp.Password != null) (ws.Protection as XLSheetProtection).PasswordHash = sp.Password.Value; + if (sp.FormatCells != null) ws.Protection.Protected = sp.FormatCells.Value; + if (sp.FormatColumns != null) ws.Protection.Protected = sp.FormatColumns.Value; + if (sp.FormatRows != null) ws.Protection.Protected = sp.FormatRows.Value; + if (sp.InsertColumns != null) ws.Protection.Protected = sp.InsertColumns.Value; + if (sp.InsertHyperlinks != null) ws.Protection.Protected = sp.InsertHyperlinks.Value; + if (sp.InsertRows != null) ws.Protection.Protected = sp.InsertRows.Value; + if (sp.DeleteColumns != null) ws.Protection.Protected = sp.DeleteColumns.Value; + if (sp.DeleteRows != null) ws.Protection.Protected = sp.DeleteRows.Value; + if (sp.AutoFilter != null) ws.Protection.Protected = sp.AutoFilter.Value; + if (sp.PivotTables != null) ws.Protection.Protected = sp.PivotTables.Value; + if (sp.Sort != null) ws.Protection.Protected = sp.Sort.Value; + if (sp.SelectLockedCells != null) ws.Protection.Protected = !sp.SelectLockedCells.Value; + if (sp.SelectUnlockedCells != null) ws.Protection.Protected = !sp.SelectUnlockedCells.Value; + } + } + private void LoadDataValidations(WorksheetPart worksheetPart, XLWorksheet ws) { var dataValidationList = worksheetPart.Worksheet.Descendants(); @@ -771,9 +797,20 @@ private void ApplyStyle(IXLStylized xlStylized, Int32 styleIndex, Stylesheet s, Fills fills, Borders borders, Fonts fonts, NumberingFormats numberingFormats) { - //if (fills.ContainsKey(styleIndex)) - //{ - // var fill = fills[styleIndex]; + var applyProtection = ((CellFormat)((CellFormats)s.CellFormats).ElementAt(styleIndex)).ApplyProtection; + if (applyProtection != null) + { + var protection = (Protection)((CellFormat)((CellFormats)s.CellFormats).ElementAt(styleIndex)).Protection; + + if (protection == null) + xlStylized.InnerStyle.Protection = DefaultStyle.Protection; + else + { + xlStylized.InnerStyle.Protection.Hidden = protection.Hidden != null && protection.Hidden.HasValue && protection.Hidden.Value; + xlStylized.InnerStyle.Protection.Locked = protection.Locked != null && protection.Locked.HasValue && protection.Locked.Value; + } + } + var fillId = ((CellFormat)((CellFormats)s.CellFormats).ElementAt(styleIndex)).FillId.Value; if (fillId > 0) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 44ddc38..f071385 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -971,7 +971,7 @@ } var allCellStyleFormats = ResolveCellStyleFormats(workbookStylesPart); - ResolveAlignments(workbookStylesPart); + ResolveRest(workbookStylesPart); if (!workbookStylesPart.Stylesheet.CellStyles.Elements().Where(c => c.Name == "Normal").Any()) @@ -1005,7 +1005,7 @@ //workbookStylesPart.Stylesheet.Append(tableStyles1); } - private void ResolveAlignments(WorkbookStylesPart workbookStylesPart) + private void ResolveRest(WorkbookStylesPart workbookStylesPart) { if (workbookStylesPart.Stylesheet.CellFormats == null) workbookStylesPart.Stylesheet.CellFormats = new CellFormats(); @@ -1033,7 +1033,9 @@ styleId++; } - CellFormat cellFormat = new CellFormat() { NumberFormatId = (UInt32)styleInfo.NumberFormatId, FontId = (UInt32)styleInfo.FontId, FillId = (UInt32)styleInfo.FillId, BorderId = (UInt32)styleInfo.BorderId, ApplyNumberFormat = false, ApplyFill = ApplyFill(styleInfo), ApplyBorder = ApplyBorder(styleInfo), ApplyAlignment = false, ApplyProtection = false, FormatId = (UInt32)formatId }; + //CellFormat cellFormat = new CellFormat() { NumberFormatId = (UInt32)styleInfo.NumberFormatId, FontId = (UInt32)styleInfo.FontId, FillId = (UInt32)styleInfo.FillId, BorderId = (UInt32)styleInfo.BorderId, ApplyNumberFormat = false, ApplyFill = ApplyFill(styleInfo), ApplyBorder = ApplyBorder(styleInfo), ApplyAlignment = false, ApplyProtection = false, FormatId = (UInt32)formatId }; + CellFormat cellFormat = GetCellFormat(styleInfo); + cellFormat.FormatId = (UInt32)formatId; Alignment alignment = new Alignment() { Horizontal = alignmentHorizontalValues.Single(a => a.Key == styleInfo.Style.Alignment.Horizontal).Value, @@ -1047,6 +1049,10 @@ JustifyLastLine = styleInfo.Style.Alignment.JustifyLastLine }; cellFormat.Append(alignment); + + if (cellFormat.ApplyProtection.Value) + cellFormat.Append(GetProtection(styleInfo)); + workbookStylesPart.Stylesheet.CellFormats.Append(cellFormat); } } @@ -1074,7 +1080,12 @@ } if (!foundOne) { - CellFormat cellStyleFormat = new CellFormat() { NumberFormatId = (UInt32)styleInfo.NumberFormatId, FontId = (UInt32)styleInfo.FontId, FillId = (UInt32)styleInfo.FillId, BorderId = (UInt32)styleInfo.BorderId, ApplyNumberFormat = false, ApplyFill = ApplyFill(styleInfo), ApplyBorder = ApplyBorder(styleInfo), ApplyAlignment = false, ApplyProtection = false }; + //CellFormat cellStyleFormat = new CellFormat() { NumberFormatId = (UInt32)styleInfo.NumberFormatId, FontId = (UInt32)styleInfo.FontId, FillId = (UInt32)styleInfo.FillId, BorderId = (UInt32)styleInfo.BorderId, ApplyNumberFormat = false, ApplyFill = ApplyFill(styleInfo), ApplyBorder = ApplyBorder(styleInfo), ApplyAlignment = false, ApplyProtection = false }; + CellFormat cellStyleFormat = GetCellFormat(styleInfo); + + if (cellStyleFormat.ApplyProtection.Value) + cellStyleFormat.Append(GetProtection(styleInfo)); + workbookStylesPart.Stylesheet.CellStyleFormats.Append(cellStyleFormat); } allSharedStyles.Add(styleInfo.Style, new StyleInfo() { Style = styleInfo.Style, StyleId = (UInt32)styleId }); @@ -1100,6 +1111,26 @@ || borderStyleValues.Single(b => b.Key == opBorder.TopBorder).Value != BorderStyleValues.None); } + private Boolean ApplyProtection(StyleInfo styleInfo) + { + return styleInfo.Style.Protection != null; + } + + private CellFormat GetCellFormat(StyleInfo styleInfo) + { + var cellFormat = new CellFormat() { NumberFormatId = (UInt32)styleInfo.NumberFormatId, FontId = (UInt32)styleInfo.FontId, FillId = (UInt32)styleInfo.FillId, BorderId = (UInt32)styleInfo.BorderId, ApplyNumberFormat = false, ApplyFill = ApplyFill(styleInfo), ApplyBorder = ApplyBorder(styleInfo), ApplyAlignment = false, ApplyProtection = ApplyProtection(styleInfo) }; + return cellFormat; + } + + private static Protection GetProtection(StyleInfo styleInfo) + { + return new Protection() + { + Locked = styleInfo.Style.Protection.Locked, + Hidden = styleInfo.Style.Protection.Hidden + }; + } + private bool CellFormatsAreEqual(CellFormat f, StyleInfo styleInfo) { return @@ -1109,13 +1140,27 @@ && styleInfo.NumberFormatId == f.NumberFormatId && f.ApplyNumberFormat != null && f.ApplyNumberFormat == false && f.ApplyAlignment != null && f.ApplyAlignment == false - && f.ApplyProtection != null && f.ApplyProtection == false && f.ApplyFill != null && f.ApplyFill == ApplyFill(styleInfo) && f.ApplyBorder != null && f.ApplyBorder == ApplyBorder(styleInfo) && AlignmentsAreEqual(f.Alignment, styleInfo.Style.Alignment) + && ProtectionsAreEqual(f.Protection, styleInfo.Style.Protection) ; } + private bool ProtectionsAreEqual(Protection protection, IXLProtection xlProtection) + { + var p = new XLProtection(); + if (protection != null) + { + if (protection.Locked != null) + p.Locked = protection.Locked.Value; + if (protection.Hidden != null) + p.Hidden = protection.Hidden.Value; + } + return p.Equals(xlProtection); + } + + private bool AlignmentsAreEqual(Alignment alignment, IXLAlignment xlAlignment) { var a = new XLAlignment(); @@ -1981,6 +2026,44 @@ } #endregion + #region SheetProtection + SheetProtection sheetProtection = null; + if (xlWorksheet.Protection.Protected) + { + if (worksheetPart.Worksheet.Elements().Count() == 0) + { + OpenXmlElement previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.SheetProtection); + worksheetPart.Worksheet.InsertAfter(new SheetProtection(), previousElement); + } + + sheetProtection = worksheetPart.Worksheet.Elements().First(); + cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, sheetProtection); + + var protection = (XLSheetProtection)xlWorksheet.Protection; + sheetProtection.Sheet = protection.Protected; + if (!StringExtensions.IsNullOrWhiteSpace(protection.PasswordHash)) + sheetProtection.Password = protection.PasswordHash; + sheetProtection.FormatCells = protection.FormatCells; + sheetProtection.FormatColumns = protection.FormatColumns; + sheetProtection.FormatRows = protection.FormatRows; + sheetProtection.InsertColumns = protection.InsertColumns; + sheetProtection.InsertHyperlinks = protection.InsertHyperlinks; + sheetProtection.InsertRows = protection.InsertRows; + sheetProtection.DeleteColumns = protection.DeleteColumns; + sheetProtection.DeleteRows = protection.DeleteRows; + sheetProtection.AutoFilter = protection.AutoFilter; + sheetProtection.PivotTables = protection.PivotTables; + sheetProtection.Sort = protection.Sort; + sheetProtection.SelectLockedCells = !protection.SelectLockedCells; + sheetProtection.SelectUnlockedCells = !protection.SelectUnlockedCells; + } + else + { + worksheetPart.Worksheet.RemoveAllChildren(); + cm.SetElement(XLWSContentManager.XLWSContents.SheetProtection, null); + } + #endregion + #region MergeCells MergeCells mergeCells = null; if (xlWorksheet.Internals.MergedRanges.Count() > 0) @@ -2006,6 +2089,7 @@ else { worksheetPart.Worksheet.RemoveAllChildren(); + cm.SetElement(XLWSContentManager.XLWSContents.MergeCells, null); } #endregion @@ -2015,6 +2099,7 @@ if (xlWorksheet.DataValidations.Count() == 0) { worksheetPart.Worksheet.RemoveAllChildren(); + cm.SetElement(XLWSContentManager.XLWSContents.DataValidations, null); } else { @@ -2072,6 +2157,7 @@ if (xlWorksheet.Hyperlinks.Count() == 0) { worksheetPart.Worksheet.RemoveAllChildren(); + cm.SetElement(XLWSContentManager.XLWSContents.Hyperlinks, null); } else { @@ -2350,7 +2436,10 @@ private Double GetColumnWidth(Double columnWidth) { - return columnWidth + 0.71; + if (columnWidth > 0) + return columnWidth + 0.71; + else + return columnWidth; } private void UpdateColumn(Column column, Columns columns, Dictionary sheetColumnsByMin)//, Dictionary sheetColumnsByMax) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 0acd229..08c1f91 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -32,6 +32,7 @@ Tables = new XLTables(); Hyperlinks = new XLHyperlinks(); DataValidations = new XLDataValidations(); + Protection = new XLSheetProtection(); this.workbook = workbook; style = new XLStyle(this, workbook.Style); Internals = new XLWorksheetInternals(new XLCellCollection(), new XLColumnsCollection(), new XLRowsCollection(), new XLRanges(workbook, workbook.Style) , workbook); @@ -629,7 +630,23 @@ return this; } - + public IXLSheetProtection Protection { get; private set; } + public IXLSheetProtection Protect() + { + return Protection.Protect(); + } + public IXLSheetProtection Protect(String password) + { + return Protection.Protect(password); + } + public IXLSheetProtection Unprotect() + { + return Protection.Unprotect(); + } + public IXLSheetProtection Unprotect(String password) + { + return Protection.Unprotect(password); + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs index e579d39..5d0028e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs @@ -100,7 +100,8 @@ { if (value != null) { - for (int i = 0; i < value.Length; i++) + var length = value.Length; + for (int i = 0; i < length; i++) { if (!char.IsWhiteSpace(value[i])) { diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/BasicTable.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/BasicTable.cs index 8ef2f3e..591a9f9 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/BasicTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/BasicTable.cs @@ -80,14 +80,13 @@ rngTable.Row(1).Merge(); // We could've also used: rngTable.Range("A1:E1").Merge() //Add thick borders - //Left border - rngTable.FirstColumn().Style.Border.LeftBorder = XLBorderStyleValues.Thick; - //Right border - rngTable.LastColumn().Style.Border.RightBorder = XLBorderStyleValues.Thick; - //Top border - rngTable.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Thick; - //Bottom border - rngTable.LastRow().Style.Border.BottomBorder = XLBorderStyleValues.Thick; + rngTable.Style.Border.OutsideBorder = XLBorderStyleValues.Thick; + + // You can also specify the border for each side with: + // rngTable.FirstColumn().Style.Border.LeftBorder = XLBorderStyleValues.Thick; + // rngTable.LastColumn().Style.Border.RightBorder = XLBorderStyleValues.Thick; + // rngTable.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Thick; + // rngTable.LastRow().Style.Border.BottomBorder = XLBorderStyleValues.Thick; // Adjust column widths to their content ws.Columns(2, 6).AdjustToContents(); diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index 3c96724..efddf26 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -90,6 +90,8 @@ + + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs index 1032a19..3065bf7 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Creating/CreateFiles.cs @@ -71,6 +71,7 @@ new Hyperlinks().Create(@"C:\Excel Files\Created\Hyperlinks.xlsx"); new DataValidation().Create(@"C:\Excel Files\Created\DataValidation.xlsx"); new HideSheets().Create(@"C:\Excel Files\Created\HideSheets.xlsx"); + new SheetProtection().Create(@"C:\Excel Files\Created\SheetProtection.xlsx"); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/SheetProtection.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/SheetProtection.cs new file mode 100644 index 0000000..1ea0aca --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/SheetProtection.cs @@ -0,0 +1,96 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; + +namespace ClosedXML_Examples.Misc +{ + public class SheetProtection + { + #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) + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Protected No-Password"); + + ws.Protect() // On this sheet we will only allow: + .SetFormatCells() // Cell Formatting + .SetInsertColumns() // Inserting Columns + .SetDeleteColumns() // Deleting Columns + .SetDeleteRows(); // Deleting Rows + + ws.Cell("A1").SetValue("Locked, No Hidden (Default):").Style.Font.SetBold().Fill.SetBackgroundColor(XLColor.Cyan); + ws.Cell("B1").Style + .Border.SetOutsideBorder(XLBorderStyleValues.Medium); + + ws.Cell("A2").SetValue("Locked, Hidden:").Style.Font.SetBold().Fill.SetBackgroundColor(XLColor.Cyan); + ws.Cell("B2").Style + .Protection.SetHidden() + .Border.SetOutsideBorder(XLBorderStyleValues.Medium); + + ws.Cell("A3").SetValue("Not Locked, Hidden:").Style.Font.SetBold().Fill.SetBackgroundColor(XLColor.Cyan); + ws.Cell("B3").Style + .Protection.SetLocked(false) + .Protection.SetHidden() + .Border.SetOutsideBorder(XLBorderStyleValues.Medium); + + ws.Cell("A4").SetValue("Not Locked, Not Hidden:").Style.Font.SetBold().Fill.SetBackgroundColor(XLColor.Cyan); + ws.Cell("B4").Style + .Protection.SetLocked(false) + .Border.SetOutsideBorder(XLBorderStyleValues.Medium); + + ws.Columns().AdjustToContents(); + + // Protect a sheet with a password + var protectedSheet = wb.Worksheets.Add("Protected Password = 123"); + var protection = protectedSheet.Protect("123"); + protection.InsertRows = true; + protection.InsertColumns = true; + + wb.SaveAs(filePath); + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/ShiftingFormulas.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/ShiftingFormulas.cs new file mode 100644 index 0000000..eca9b44 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/ShiftingFormulas.cs @@ -0,0 +1,96 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; +using ClosedXML.Excel; + +using System.Drawing; + +namespace ClosedXML_Examples.Misc +{ + public class ShiftingFormulas + { + #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) + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Shifting Formulas"); + ws.Cell("B2").Value = 5; + ws.Cell("B3").Value = 6; + ws.Cell("C2").Value = 1; + ws.Cell("C3").Value = 2; + ws.Cell("A4").Value = "Sum:"; + ws.Range("B4:C4").FormulaR1C1 = "Sum(R[-2]C:R[-1]C)"; + ws.Range("B4:C4").AddToNamed("WorkbookB4C4"); + ws.Range("B4:C4").AddToNamed("WorksheetB4C4", XLScope.Worksheet); + ws.Cell("E2").Value = "Avg:"; + + ws.Cell("F2").FormulaA1 = "Average(B2:C3)"; + ws.Ranges("A4,E2").Style + .Font.SetBold() + .Fill.SetBackgroundColor(XLColor.CyanProcess); + + var ws2 = wb.Worksheets.Add("WS2"); + ws2.Cell(1, 1).FormulaA1 = "='Shifting Formulas'!B2"; + ws2.Cell(1, 2).Value = ws2.Cell(1, 1).Value; + ws2.Cell(2, 1).FormulaA1 = "Average('Shifting Formulas'!$B$2:$C$3)"; + ws2.Cell(3, 1).FormulaA1 = "Average('Shifting Formulas'!$B$2:$C3)"; + ws2.Cell(4, 1).FormulaA1 = "Average('Shifting Formulas'!$B$2:C3)"; + ws2.Cell(5, 1).FormulaA1 = "Average('Shifting Formulas'!$B2:C3)"; + ws2.Cell(6, 1).FormulaA1 = "Average('Shifting Formulas'!B2:C3)"; + ws2.Cell(7, 1).FormulaA1 = "Average('Shifting Formulas'!B2:C$3)"; + ws2.Cell(8, 1).FormulaA1 = "Average('Shifting Formulas'!B2:$C$3)"; + ws2.Cell(9, 1).FormulaA1 = "Average('Shifting Formulas'!B$2:$C$3)"; + + var dataGrid = ws.Range("B2:D3"); + ws.Row(1).InsertRowsAbove(1); + var newRow = dataGrid.LastRow().InsertRowsAbove(1).First(); + newRow.Value = 1; + dataGrid.LastColumn().FormulaR1C1 = String.Format("SUM(RC[-{0}]:RC[-1])", dataGrid.ColumnCount() - 1); + ws.Cell(1, 1).InsertCellsBelow(1); + ws.Column(1).InsertColumnsBefore(1); + ws.Row(4).Delete(); + wb.SaveAs(filePath); + } + + // Private + + // Override + + + #endregion + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/ShowCase.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/ShowCase.cs index 36a6459..da5faea 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/ShowCase.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/ShowCase.cs @@ -25,29 +25,29 @@ ws.Cell("B3").Value = "FName"; ws.Cell("B4").Value = "John"; ws.Cell("B5").Value = "Hank"; - ws.Cell("B6").Value = "Dagny"; + ws.Cell("B6").SetValue("Dagny"); // Another way to set the value //Last Names ws.Cell("C3").Value = "LName"; ws.Cell("C4").Value = "Galt"; ws.Cell("C5").Value = "Rearden"; - ws.Cell("C6").Value = "Taggart"; + ws.Cell("C6").SetValue("Taggart"); // Another way to set the value //Adding more data types //Is an outcast? ws.Cell("D3").Value = "Outcast"; ws.Cell("D4").Value = true; ws.Cell("D5").Value = false; - ws.Cell("D6").Value = false; + ws.Cell("D6").SetValue(false); // Another way to set the value //Date of Birth ws.Cell("E3").Value = "DOB"; ws.Cell("E4").Value = new DateTime(1919, 1, 21); ws.Cell("E5").Value = new DateTime(1907, 3, 4); - ws.Cell("E6").Value = new DateTime(1921, 12, 15); + ws.Cell("E6").SetValue(new DateTime(1921, 12, 15)); // Another way to set the value //Income ws.Cell("F3").Value = "Income"; ws.Cell("F4").Value = 2000; ws.Cell("F5").Value = 40000; - ws.Cell("F6").Value = 10000; + ws.Cell("F6").SetValue(10000); // Another way to set the value //Defining ranges //From worksheet @@ -62,10 +62,11 @@ //Using a custom format rngNumbers.Style.NumberFormat.Format = "$ #,##0"; - //Format title cell - rngTable.Cell(1, 1).Style.Font.Bold = true; - rngTable.Cell(1, 1).Style.Fill.BackgroundColor = XLColor.CornflowerBlue; - rngTable.Cell(1, 1).Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center; + //Format title cell in one shot + rngTable.Cell(1, 1).Style + .Font.SetBold() + .Fill.SetBackgroundColor(XLColor.CornflowerBlue) + .Alignment.SetHorizontal(XLAlignmentHorizontalValues.Center); //Merge title cells rngTable.Row(1).Merge(); // We could've also used: rngTable.Range("A1:E1").Merge() @@ -95,14 +96,14 @@ var lastCell = ws.LastCellUsed(); var contents = ws.Range(firstCell.Address, lastCell.Address); - //Left border - contents.FirstColumn().Style.Border.LeftBorder = XLBorderStyleValues.Thick; - //Right border - contents.LastColumn().Style.Border.RightBorder = XLBorderStyleValues.Thick; - //Top border - contents.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Thick; - //Bottom border - contents.LastRow().Style.Border.BottomBorder = XLBorderStyleValues.Thick; + //Add thick borders + contents.Style.Border.OutsideBorder = XLBorderStyleValues.Thick; + + // You can also specify the border for each side with: + // contents.FirstColumn().Style.Border.LeftBorder = XLBorderStyleValues.Thick; + // contents.LastColumn().Style.Border.RightBorder = XLBorderStyleValues.Thick; + // contents.FirstRow().Style.Border.TopBorder = XLBorderStyleValues.Thick; + // contents.LastRow().Style.Border.BottomBorder = XLBorderStyleValues.Thick; // Adjust column widths to their content ws.Columns(2, 6).AdjustToContents(); diff --git a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index 521e332..fb7862d 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -211,6 +211,9 @@ Excel\IXLOutline.cs + + Excel\IXLSheetProtection.cs + Excel\IXLSheetView.cs @@ -364,6 +367,9 @@ Excel\Style\IXLNumberFormat.cs + + Excel\Style\IXLProtection.cs + Excel\Style\IXLStyle.cs @@ -385,6 +391,9 @@ Excel\Style\XLNumberFormat.cs + + Excel\Style\XLProtection.cs + Excel\Style\XLStyle.cs @@ -427,6 +436,9 @@ Excel\XLOutline.cs + + Excel\XLSheetProtection.cs + Excel\XLSheetView.cs diff --git a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs index 16dbd02..62c5f75 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Sandbox/Program.cs @@ -16,113 +16,16 @@ static void Main(string[] args) { //var fileName = "DifferentKinds"; - //var fileName = "Blank"; var fileName = "Sandbox"; + //var fileName = "Issue_6405"; //var wb = new XLWorkbook(String.Format(@"c:\Excel Files\ForTesting\{0}.xlsx", fileName)); - //var wb = new XLWorkbook(); - - var wb = new XLWorkbook(); - var ws = wb.Worksheets.Add("Shifting Formulas"); - ws.Cell("B2").Value = 5; - ws.Cell("B3").Value = 6; - ws.Cell("C2").Value = 1; - ws.Cell("C3").Value = 2; - ws.Cell("A4").Value = "Sum:"; - ws.Range("B4:C4").FormulaR1C1 = "Sum(R[-2]C:R[-1]C)"; - ws.Range("B4:C4").AddToNamed("WorkbookB4C4"); - ws.Range("B4:C4").AddToNamed("WorksheetB4C4", XLScope.Worksheet); - ws.Cell("E2").Value = "Avg:"; - - ws.Cell("F2").FormulaA1 = "Average(B2:C3)"; - ws.Ranges("A4,E2").Style - .Font.SetBold() - .Fill.SetBackgroundColor(XLColor.CyanProcess); - - var ws2 = wb.Worksheets.Add("WS2"); - ws2.Cell(1, 1).FormulaA1 = "='Shifting Formulas'!B2"; - ws2.Cell(1, 2).Value = ws2.Cell(1, 1).Value; - ws2.Cell(2, 1).FormulaA1 = "Average('Shifting Formulas'!$B$2:$C$3)"; - ws2.Cell(3, 1).FormulaA1 = "Average('Shifting Formulas'!$B$2:$C3)"; - ws2.Cell(4, 1).FormulaA1 = "Average('Shifting Formulas'!$B$2:C3)"; - ws2.Cell(5, 1).FormulaA1 = "Average('Shifting Formulas'!$B2:C3)"; - ws2.Cell(6, 1).FormulaA1 = "Average('Shifting Formulas'!B2:C3)"; - ws2.Cell(7, 1).FormulaA1 = "Average('Shifting Formulas'!B2:C$3)"; - ws2.Cell(8, 1).FormulaA1 = "Average('Shifting Formulas'!B2:$C$3)"; - ws2.Cell(9, 1).FormulaA1 = "Average('Shifting Formulas'!B$2:$C$3)"; - - var dataGrid = ws.Range("B2:D3"); - ws.Row(1).InsertRowsAbove(1); - var newRow = dataGrid.LastRow().InsertRowsAbove(1).First(); - newRow.Value = 1; - dataGrid.LastColumn().FormulaR1C1 = String.Format("SUM(RC[-{0}]:RC[-1])", dataGrid.ColumnCount() - 1); - ws.Cell(1, 1).InsertCellsBelow(1); - ws.Column(1).InsertColumnsBefore(1); - ws.Row(4).Delete(); + var ws = wb.Worksheets.Add("Sheet1"); + ws.Range("A1:C3").Style.Border.OutsideBorder = XLBorderStyleValues.Thick; wb.SaveAs(String.Format(@"c:\Excel Files\ForTesting\{0}_Saved.xlsx", fileName)); } - public static String GetSheetPassword(String password) - { - Int32 pLength = password.Length; - Int32 hash = 0; - if (pLength == 0) return hash.ToString("X"); - - 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"); - } - - static Boolean IsValidUri(String uri) - { - try - { - new Uri(uri, UriKind.Relative); - return true; - } - catch - { - return false; - } - } - - static Boolean TryCreate(String address) - { - Uri uri; - return Uri.TryCreate(address, UriKind.Absolute, out uri); - } - - static void Main_5961(string[] args) - { - var fi = new FileInfo(@"C:\Excel Files\ForTesting\Issue_5961.xlsx"); - XLWorkbook wb = new XLWorkbook(fi.FullName); - { - IXLWorksheet s = wb.Worksheets.Add("test1"); - s.Cell(1, 1).Value = DateTime.Now.ToString(); - } - { - IXLWorksheet s = wb.Worksheets.Add("test2"); - s.Cell(1, 1).Value = DateTime.Now.ToString(); - } - wb.Save(); - wb = new XLWorkbook(fi.FullName); - wb.Worksheets.Delete("test1"); - { - IXLWorksheet s = wb.Worksheets.Add("test3"); - s.Cell(1, 1).Value = DateTime.Now.ToString(); - } - wb.Save(); - wb = new XLWorkbook(fi.FullName); - - wb.Save(); - } - static void xMain(string[] args) { FillStyles();