diff --git a/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs b/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs index f13ce40..4f2851d 100644 --- a/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs +++ b/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs @@ -2,7 +2,7 @@ namespace ClosedXML.Excel { - public enum XLFilterType { Regular, Custom, TopBottom, Dynamic } + public enum XLFilterType { Regular, Custom, TopBottom, Dynamic, DateTimeGrouping } public enum XLFilterDynamicType { AboveAverage, BelowAverage } diff --git a/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs b/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs index 41376c8..106f485 100644 --- a/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs +++ b/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs @@ -1,31 +1,53 @@ using System; + namespace ClosedXML.Excel { public enum XLTopBottomType { Items, Percent } + + public enum XLDateTimeGrouping { Year, Month, Day, Hour, Minute, Second } + public interface IXLFilterColumn { void Clear(); IXLFilteredColumn AddFilter(T value) where T : IComparable; + IXLDateTimeGroupFilteredColumn AddDateGroupFilter(DateTime date, XLDateTimeGrouping dateTimeGrouping); + void Top(Int32 value, XLTopBottomType type = XLTopBottomType.Items); + void Bottom(Int32 value, XLTopBottomType type = XLTopBottomType.Items); + void AboveAverage(); + void BelowAverage(); IXLFilterConnector EqualTo(T value) where T : IComparable; + IXLFilterConnector NotEqualTo(T value) where T : IComparable; + IXLFilterConnector GreaterThan(T value) where T : IComparable; + IXLFilterConnector LessThan(T value) where T : IComparable; + IXLFilterConnector EqualOrGreaterThan(T value) where T : IComparable; + IXLFilterConnector EqualOrLessThan(T value) where T : IComparable; + void Between(T minValue, T maxValue) where T : IComparable; + void NotBetween(T minValue, T maxValue) where T : IComparable; + IXLFilterConnector BeginsWith(String value); + IXLFilterConnector NotBeginsWith(String value); + IXLFilterConnector EndsWith(String value); + IXLFilterConnector NotEndsWith(String value); + IXLFilterConnector Contains(String value); + IXLFilterConnector NotContains(String value); XLFilterType FilterType { get; set; } @@ -36,11 +58,15 @@ Double DynamicValue { get; set; } IXLFilterColumn SetFilterType(XLFilterType value); - IXLFilterColumn SetTopBottomValue(Int32 value); - IXLFilterColumn SetTopBottomType(XLTopBottomType value); - IXLFilterColumn SetTopBottomPart(XLTopBottomPart value); - IXLFilterColumn SetDynamicType(XLFilterDynamicType value); - IXLFilterColumn SetDynamicValue(Double value); + IXLFilterColumn SetTopBottomValue(Int32 value); + + IXLFilterColumn SetTopBottomType(XLTopBottomType value); + + IXLFilterColumn SetTopBottomPart(XLTopBottomPart value); + + IXLFilterColumn SetDynamicType(XLFilterDynamicType value); + + IXLFilterColumn SetDynamicValue(Double value); } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs b/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs index a01a138..3e33697 100644 --- a/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs +++ b/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs @@ -59,11 +59,18 @@ public IXLFilterColumn Column(String column) { - return Column(XLHelper.GetColumnNumberFromLetter(column)); + var columnNumber = XLHelper.GetColumnNumberFromLetter(column); + if (columnNumber < 1 || columnNumber > XLHelper.MaxColumnNumber) + throw new ArgumentOutOfRangeException(nameof(column), "Column '" + column + "' is outside the allowed column range."); + + return Column(columnNumber); } public IXLFilterColumn Column(Int32 column) { + if (column < 1 || column > XLHelper.MaxColumnNumber) + throw new ArgumentOutOfRangeException(nameof(column), "Column " + column + " is outside the allowed column range."); + XLFilterColumn filterColumn; if (!_columns.TryGetValue(column, out filterColumn)) { @@ -97,7 +104,7 @@ public XLAutoFilter Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase, Boolean ignoreBlanks) { if (!Enabled) - throw new ApplicationException("Filter has not been enabled."); + throw new InvalidOperationException("Filter has not been enabled."); var ws = Range.Worksheet as XLWorksheet; ws.SuspendEvents(); @@ -108,6 +115,7 @@ SortOrder = sortOrder; SortColumn = columnToSortBy; + // Recalculate shown / hidden rows if (Enabled) { using (var rows = Range.Rows(2, Range.RowCount())) @@ -116,20 +124,29 @@ row.WorksheetRow().Unhide(); } - foreach (KeyValuePair> kp in Filters) + foreach (var kp in Filters) { Boolean firstFilter = true; foreach (XLFilter filter in kp.Value) { - Boolean isText = filter.Value is String; + var condition = filter.Condition; + var isText = filter.Value is String; + var isDateTime = filter.Value is DateTime; + using (var rows = Range.Rows(2, Range.RowCount())) { foreach (IXLRangeRow row in rows) { - Boolean match = isText - ? filter.Condition(row.Cell(kp.Key).GetString()) - : row.Cell(kp.Key).DataType == XLDataType.Number && - filter.Condition(row.Cell(kp.Key).GetDouble()); + //TODO : clean up filter matching - it's done in different place + Boolean match; + + if (isText) + match = condition(row.Cell(kp.Key).GetFormattedString()); + else if (isDateTime) + match = row.Cell(kp.Key).DataType == XLDataType.DateTime && condition(row.Cell(kp.Key).GetDateTime()); + else + match = row.Cell(kp.Key).DataType == XLDataType.Number && condition(row.Cell(kp.Key).GetDouble()); + if (firstFilter) { if (match) @@ -162,4 +179,4 @@ return this; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/AutoFilters/XLDateTimeGroupFilteredColumn.cs b/ClosedXML/Excel/AutoFilters/XLDateTimeGroupFilteredColumn.cs new file mode 100644 index 0000000..b2c151b --- /dev/null +++ b/ClosedXML/Excel/AutoFilters/XLDateTimeGroupFilteredColumn.cs @@ -0,0 +1,58 @@ +using System; + +namespace ClosedXML.Excel +{ + public interface IXLDateTimeGroupFilteredColumn + { + IXLDateTimeGroupFilteredColumn AddDateGroupFilter(DateTime date, XLDateTimeGrouping dateTimeGrouping); + } + + internal class XLDateTimeGroupFilteredColumn : IXLDateTimeGroupFilteredColumn + { + private readonly XLAutoFilter _autoFilter; + private readonly Int32 _column; + + public XLDateTimeGroupFilteredColumn(XLAutoFilter autoFilter, Int32 column) + { + _autoFilter = autoFilter; + _column = column; + } + + public IXLDateTimeGroupFilteredColumn AddDateGroupFilter(DateTime date, XLDateTimeGrouping dateTimeGrouping) + { + Func condition = date2 => IsMatch(date, (DateTime)date2, dateTimeGrouping); + + _autoFilter.Filters[_column].Add(new XLFilter + { + Value = date, + Condition = condition, + Operator = XLFilterOperator.Equal, + Connector = XLConnector.Or, + DateTimeGrouping = dateTimeGrouping + }); + + using (var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount())) + { + foreach (IXLRangeRow row in rows) + { + if (row.Cell(_column).DataType == XLDataType.DateTime && condition(row.Cell(_column).GetDateTime())) + row.WorksheetRow().Unhide().Dispose(); + } + } + return this; + } + + internal static Boolean IsMatch(DateTime date1, DateTime date2, XLDateTimeGrouping dateTimeGrouping) + { + Boolean isMatch = true; + if (isMatch && dateTimeGrouping >= XLDateTimeGrouping.Year) isMatch &= date1.Year.Equals(date2.Year); + if (isMatch && dateTimeGrouping >= XLDateTimeGrouping.Month) isMatch &= date1.Month.Equals(date2.Month); + if (isMatch && dateTimeGrouping >= XLDateTimeGrouping.Day) isMatch &= date1.Day.Equals(date2.Day); + if (isMatch && dateTimeGrouping >= XLDateTimeGrouping.Hour) isMatch &= date1.Hour.Equals(date2.Hour); + if (isMatch && dateTimeGrouping >= XLDateTimeGrouping.Minute) isMatch &= date1.Minute.Equals(date2.Minute); + if (isMatch && dateTimeGrouping >= XLDateTimeGrouping.Second) isMatch &= date1.Second.Equals(date2.Second); + + return isMatch; + } + } +} diff --git a/ClosedXML/Excel/AutoFilters/XLFilter.cs b/ClosedXML/Excel/AutoFilters/XLFilter.cs index f91231e..e62c552 100644 --- a/ClosedXML/Excel/AutoFilters/XLFilter.cs +++ b/ClosedXML/Excel/AutoFilters/XLFilter.cs @@ -5,8 +5,8 @@ namespace ClosedXML.Excel { - public enum XLConnector { And, Or } - public enum XLFilterOperator { Equal, NotEqual, GreaterThan, LessThan, EqualOrGreaterThan, EqualOrLessThan } + internal enum XLConnector { And, Or } + internal enum XLFilterOperator { Equal, NotEqual, GreaterThan, LessThan, EqualOrGreaterThan, EqualOrLessThan } internal class XLFilter { public XLFilter(XLFilterOperator op = XLFilterOperator.Equal) @@ -18,5 +18,6 @@ public Object Value { get; set; } public XLConnector Connector { get; set; } public Func Condition { get; set; } + public XLDateTimeGrouping DateTimeGrouping { get; set; } } } diff --git a/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs b/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs index a624708..dc4bceb 100644 --- a/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs +++ b/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs @@ -24,7 +24,7 @@ _autoFilter.Filters.Remove(_column); } - public IXLFilteredColumn AddFilter(T value) where T: IComparable + public IXLFilteredColumn AddFilter(T value) where T : IComparable { if (typeof(T) == typeof(String)) { @@ -41,6 +41,61 @@ return new XLFilteredColumn(_autoFilter, _column); } + public IXLDateTimeGroupFilteredColumn AddDateGroupFilter(DateTime date, XLDateTimeGrouping dateTimeGrouping) + { + Func condition = date2 => XLDateTimeGroupFilteredColumn.IsMatch(date, (DateTime)date2, dateTimeGrouping); + + _autoFilter.Enabled = true; + + List filterList; + if (_autoFilter.Filters.TryGetValue(_column, out filterList)) + filterList.Add( + new XLFilter + { + Value = date, + Operator = XLFilterOperator.Equal, + Connector = XLConnector.Or, + Condition = condition, + DateTimeGrouping = dateTimeGrouping + } + ); + else + { + _autoFilter.Filters.Add( + _column, + new List + { + new XLFilter + { + Value = date, + Operator = XLFilterOperator.Equal, + Connector = XLConnector.Or, + Condition = condition, + DateTimeGrouping = dateTimeGrouping + } + } + ); + } + + _autoFilter.Column(_column).FilterType = XLFilterType.DateTimeGrouping; + + var ws = _autoFilter.Range.Worksheet as XLWorksheet; + ws.SuspendEvents(); + + var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount()); + + foreach (IXLRangeRow row in rows) + { + if (row.Cell(_column).DataType == XLDataType.DateTime && condition(row.Cell(_column).GetDateTime())) + row.WorksheetRow().Unhide(); + else + row.WorksheetRow().Hide(); + } + ws.ResumeEvents(); + + return new XLDateTimeGroupFilteredColumn(_autoFilter, _column); + } + public void Top(Int32 value, XLTopBottomType type = XLTopBottomType.Items) { _autoFilter.Column(_column).TopBottomPart = XLTopBottomPart.Top; @@ -63,7 +118,7 @@ ShowAverage(false); } - public IXLFilterConnector EqualTo(T value) where T: IComparable + public IXLFilterConnector EqualTo(T value) where T : IComparable { if (typeof(T) == typeof(String)) { @@ -77,7 +132,7 @@ v => v.CastTo().CompareTo(value) == 0); } - public IXLFilterConnector NotEqualTo(T value) where T: IComparable + public IXLFilterConnector NotEqualTo(T value) where T : IComparable { if (typeof(T) == typeof(String)) { @@ -86,41 +141,41 @@ !v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase)); } - + return ApplyCustomFilter(value, XLFilterOperator.NotEqual, v => v.CastTo().CompareTo(value) != 0); } - public IXLFilterConnector GreaterThan(T value) where T: IComparable + public IXLFilterConnector GreaterThan(T value) where T : IComparable { return ApplyCustomFilter(value, XLFilterOperator.GreaterThan, v => v.CastTo().CompareTo(value) > 0); } - public IXLFilterConnector LessThan(T value) where T: IComparable + public IXLFilterConnector LessThan(T value) where T : IComparable { return ApplyCustomFilter(value, XLFilterOperator.LessThan, v => v.CastTo().CompareTo(value) < 0); } - public IXLFilterConnector EqualOrGreaterThan(T value) where T: IComparable + public IXLFilterConnector EqualOrGreaterThan(T value) where T : IComparable { return ApplyCustomFilter(value, XLFilterOperator.EqualOrGreaterThan, v => v.CastTo().CompareTo(value) >= 0); } - public IXLFilterConnector EqualOrLessThan(T value) where T: IComparable + public IXLFilterConnector EqualOrLessThan(T value) where T : IComparable { return ApplyCustomFilter(value, XLFilterOperator.EqualOrLessThan, v => v.CastTo().CompareTo(value) <= 0); } - public void Between(T minValue, T maxValue) where T: IComparable + public void Between(T minValue, T maxValue) where T : IComparable { EqualOrGreaterThan(minValue).And.EqualOrLessThan(maxValue); } - public void NotBetween(T minValue, T maxValue) where T: IComparable + public void NotBetween(T minValue, T maxValue) where T : IComparable { LessThan(minValue).Or.GreaterThan(maxValue); } @@ -170,7 +225,7 @@ public XLFilterDynamicType DynamicType { get; set; } public Double DynamicValue { get; set; } - #endregion + #endregion IXLFilterColumn Members private void SetTopBottom(Int32 value, XLTopBottomType type, Boolean takeTop = true) { @@ -188,33 +243,33 @@ var ws = _autoFilter.Range.Worksheet as XLWorksheet; ws.SuspendEvents(); var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount()); - foreach (IXLRangeRow row in rows) + foreach (IXLRangeRow row in rows) + { + Boolean foundOne = false; + foreach (double val in values) { - Boolean foundOne = false; - foreach (double val in values) + Func condition = v => (v as IComparable).CompareTo(val) == 0; + if (addToList) { - Func condition = v => (v as IComparable).CompareTo(val) == 0; - if (addToList) + _autoFilter.Filters[_column].Add(new XLFilter { - _autoFilter.Filters[_column].Add(new XLFilter - { - Value = val, - Operator = XLFilterOperator.Equal, - Connector = XLConnector.Or, - Condition = condition - }); - } - - var cell = row.Cell(_column); - if (cell.DataType != XLDataType.Number || !condition(cell.GetDouble())) continue; - row.WorksheetRow().Unhide(); - foundOne = true; + Value = val, + Operator = XLFilterOperator.Equal, + Connector = XLConnector.Or, + Condition = condition + }); } - if (!foundOne) - row.WorksheetRow().Hide(); - addToList = false; + var cell = row.Cell(_column); + if (cell.DataType != XLDataType.Number || !condition(cell.GetDouble())) continue; + row.WorksheetRow().Unhide(); + foundOne = true; } + if (!foundOne) + row.WorksheetRow().Hide(); + + addToList = false; + } ws.ResumeEvents(); } @@ -257,7 +312,6 @@ : XLFilterDynamicType.BelowAverage); var values = GetAverageValues(aboveAverage); - Clear(); _autoFilter.Filters.Add(_column, new List()); @@ -265,36 +319,36 @@ var ws = _autoFilter.Range.Worksheet as XLWorksheet; ws.SuspendEvents(); var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount()); - - foreach (IXLRangeRow row in rows) - { - Boolean foundOne = false; - foreach (double val in values) - { - Func condition = v => (v as IComparable).CompareTo(val) == 0; - if (addToList) - { - _autoFilter.Filters[_column].Add(new XLFilter - { - Value = val, - Operator = XLFilterOperator.Equal, - Connector = XLConnector.Or, - Condition = condition - }); - } - var cell = row.Cell(_column); - if (cell.DataType != XLDataType.Number || !condition(cell.GetDouble())) continue; - row.WorksheetRow().Unhide(); - foundOne = true; + foreach (IXLRangeRow row in rows) + { + Boolean foundOne = false; + foreach (double val in values) + { + Func condition = v => (v as IComparable).CompareTo(val) == 0; + if (addToList) + { + _autoFilter.Filters[_column].Add(new XLFilter + { + Value = val, + Operator = XLFilterOperator.Equal, + Connector = XLConnector.Or, + Condition = condition + }); } - if (!foundOne) - row.WorksheetRow().Hide(); - - addToList = false; + var cell = row.Cell(_column); + if (cell.DataType != XLDataType.Number || !condition(cell.GetDouble())) continue; + row.WorksheetRow().Unhide(); + foundOne = true; } - + + if (!foundOne) + row.WorksheetRow().Hide(); + + addToList = false; + } + ws.ResumeEvents(); } @@ -316,14 +370,13 @@ return subColumn.CellsUsed(c => c.DataType == XLDataType.Number). Select(c => c.GetDouble()).Where(c => c < average).Distinct(); - } } } private IXLFilterConnector ApplyCustomFilter(T value, XLFilterOperator op, Func condition, XLFilterType filterType = XLFilterType.Custom) - where T: IComparable + where T : IComparable { _autoFilter.Enabled = true; if (filterType == XLFilterType.Custom) @@ -333,12 +386,12 @@ new List { new XLFilter - { - Value = value, - Operator = op, - Connector = XLConnector.Or, - Condition = condition - } + { + Value = value, + Operator = op, + Connector = XLConnector.Or, + Condition = condition + } }); } else @@ -346,7 +399,12 @@ List filterList; if (_autoFilter.Filters.TryGetValue(_column, out filterList)) filterList.Add(new XLFilter - {Value = value, Operator = op, Connector = XLConnector.Or, Condition = condition}); + { + Value = value, + Operator = op, + Connector = XLConnector.Or, + Condition = condition + }); else { _autoFilter.Filters.Add(_column, @@ -364,15 +422,21 @@ } _autoFilter.Column(_column).FilterType = filterType; Boolean isText = typeof(T) == typeof(String); + Boolean isDateTime = typeof(T) == typeof(DateTime); var ws = _autoFilter.Range.Worksheet as XLWorksheet; ws.SuspendEvents(); var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount()); foreach (IXLRangeRow row in rows) { - Boolean match = isText - ? condition(row.Cell(_column).GetString()) - : row.Cell(_column).DataType == XLDataType.Number && - condition(row.Cell(_column).GetDouble()); + Boolean match; + + if (isText) + match = condition(row.Cell(_column).GetFormattedString()); + else if (isDateTime) + match = row.Cell(_column).DataType == XLDataType.DateTime && condition(row.Cell(_column).GetDateTime()); + else + match = row.Cell(_column).DataType == XLDataType.Number && condition(row.Cell(_column).GetDouble()); + if (match) row.WorksheetRow().Unhide(); else @@ -383,11 +447,15 @@ } public IXLFilterColumn SetFilterType(XLFilterType value) { FilterType = value; return this; } - public IXLFilterColumn SetTopBottomValue(Int32 value) { TopBottomValue = value; return this; } - public IXLFilterColumn SetTopBottomType(XLTopBottomType value) { TopBottomType = value; return this; } - public IXLFilterColumn SetTopBottomPart(XLTopBottomPart value) { TopBottomPart = value; return this; } - public IXLFilterColumn SetDynamicType(XLFilterDynamicType value) { DynamicType = value; return this; } - public IXLFilterColumn SetDynamicValue(Double value) { DynamicValue = value; return this; } + public IXLFilterColumn SetTopBottomValue(Int32 value) { TopBottomValue = value; return this; } + + public IXLFilterColumn SetTopBottomType(XLTopBottomType value) { TopBottomType = value; return this; } + + public IXLFilterColumn SetTopBottomPart(XLTopBottomPart value) { TopBottomPart = value; return this; } + + public IXLFilterColumn SetDynamicType(XLFilterDynamicType value) { DynamicType = value; return this; } + + public IXLFilterColumn SetDynamicValue(Double value) { DynamicValue = value; return this; } } } diff --git a/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs b/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs index 1f11d83..2fe3555 100644 --- a/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs +++ b/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs @@ -1,5 +1,4 @@ using System; -using System.Linq; namespace ClosedXML.Excel { @@ -16,7 +15,7 @@ #region IXLFilteredColumn Members - public IXLFilteredColumn AddFilter(T value) where T: IComparable + public IXLFilteredColumn AddFilter(T value) where T : IComparable { Func condition; Boolean isText; @@ -32,12 +31,12 @@ } _autoFilter.Filters[_column].Add(new XLFilter - { - Value = value, - Condition = condition, - Operator = XLFilterOperator.Equal, - Connector = XLConnector.Or - }); + { + Value = value, + Condition = condition, + Operator = XLFilterOperator.Equal, + Connector = XLConnector.Or + }); using (var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount())) { @@ -56,6 +55,6 @@ return this; } - #endregion + #endregion IXLFilteredColumn Members } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/CalcEngine/Functions/Tally.cs b/ClosedXML/Excel/CalcEngine/Functions/Tally.cs index 78e0842..5cf212e 100644 --- a/ClosedXML/Excel/CalcEngine/Functions/Tally.cs +++ b/ClosedXML/Excel/CalcEngine/Functions/Tally.cs @@ -2,6 +2,7 @@ using System.Collections; using System.Collections.Generic; using System.Linq; +using System.Threading; namespace ClosedXML.Excel.CalcEngine { @@ -10,6 +11,9 @@ private readonly List _list = new List(); private readonly bool NumbersOnly; + private double[] _numericValues; + + public Tally() : this(false) { } @@ -32,7 +36,7 @@ } } - this.NumbersOnly = numbersOnly; + NumbersOnly = numbersOnly; } public void Add(Expression e) @@ -45,6 +49,7 @@ { _list.Add(value); } + _numericValues = null; return; } @@ -56,40 +61,45 @@ else foreach (var v in valEnumerable) _list.Add(v); + + _numericValues = null; } public void AddValue(Object v) { _list.Add(v); + _numericValues = null; } public double Count() { - return this.Count(this.NumbersOnly); + return Count(NumbersOnly); } public double Count(bool numbersOnly) { if (numbersOnly) - return NumericValues().Count(); + return NumericValuesInternal().Length; else - return _list.Where(o => !Statistical.IsBlank(o)).Count(); + return _list.Count(o => !Statistical.IsBlank(o)); } - public IEnumerable NumericValues() + IEnumerable NumericValuesEnumerable() { - var retVal = new List(); foreach (var value in _list) { - Double tmp; + double tmp; var vEnumerable = value as IEnumerable; - if (vEnumerable == null && Double.TryParse(value.ToString(), out tmp)) - yield return tmp; + if (vEnumerable == null) + { + if (double.TryParse(value.ToString(), out tmp)) + yield return tmp; + } else { foreach (var v in vEnumerable) { - if (Double.TryParse(v.ToString(), out tmp)) + if (double.TryParse(v.ToString(), out tmp)) yield return tmp; break; } @@ -97,84 +107,88 @@ } } + double[] NumericValuesInternal() + => LazyInitializer.EnsureInitialized(ref _numericValues, () => NumericValuesEnumerable().ToArray()); + + public IEnumerable NumericValues() + => NumericValuesInternal().AsEnumerable(); + public double Product() { - var nums = NumericValues(); - if (!nums.Any()) return 0; - - Double retVal = 1; - nums.ForEach(n => retVal *= n); - - return retVal; + var nums = NumericValuesInternal(); + return nums.Length == 0 + ? 0 + : nums.Aggregate(1d, (a, b) => a * b); } - public double Sum() { return NumericValues().Sum(); } + public double Sum() => NumericValuesInternal().Sum(); public double Average() { - if (NumericValues().Any()) - return NumericValues().Average(); - else - throw new ApplicationException("No values"); + var nums = NumericValuesInternal(); + if (nums.Length == 0) throw new ApplicationException("No values"); + return nums.Average(); } public double Min() { - return NumericValues().Any() ? NumericValues().Min() : 0; + var nums = NumericValuesInternal(); + return nums.Length == 0 ? 0 : nums.Min(); } public double Max() { - return NumericValues().Any() ? NumericValues().Max() : 0; + var nums = NumericValuesInternal(); + return nums.Length == 0 ? 0 : nums.Max(); } - public double Range() - { - var nums = NumericValues(); - return nums.Max() - nums.Min(); - } + public double Range() => Max() - Min(); - private double Sum2(List nums) + static double Sum2(IEnumerable nums) { return nums.Sum(d => d * d); } public double VarP() { - var nums = NumericValues(); + var nums = NumericValuesInternal(); var avg = nums.Average(); - var sum2 = nums.Sum(d => d * d); - return nums.Count() <= 1 ? 0 : sum2 / nums.Count() - avg * avg; + var sum2 = Sum2(nums); + var count = nums.Length; + return count <= 1 ? 0 : sum2 / count - avg * avg; } public double StdP() { - var nums = NumericValues(); + var nums = NumericValuesInternal(); var avg = nums.Average(); var sum2 = nums.Sum(d => d * d); - return nums.Count() <= 1 ? 0 : Math.Sqrt(sum2 / nums.Count() - avg * avg); + var count = nums.Length; + return count <= 1 ? 0 : Math.Sqrt(sum2 / count - avg * avg); } public double Var() { - var nums = NumericValues(); + var nums = NumericValuesInternal(); var avg = nums.Average(); - var sum2 = nums.Sum(d => d * d); - return nums.Count() <= 1 ? 0 : (sum2 / nums.Count() - avg * avg) * nums.Count() / (nums.Count() - 1); + var sum2 = Sum2(nums); + var count = nums.Length; + return count <= 1 ? 0 : (sum2 / count - avg * avg) * count / (count - 1); } public double Std() { - var values = NumericValues(); + var values = NumericValuesInternal(); + var count = values.Length; double ret = 0; - if (values.Any()) + if (count != 0) { //Compute the Average double avg = values.Average(); //Perform the Sum of (value-avg)_2_2 double sum = values.Sum(d => Math.Pow(d - avg, 2)); //Put it all together - ret = Math.Sqrt((sum) / (values.Count() - 1)); + ret = Math.Sqrt((sum) / (count - 1)); } else { diff --git a/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/Excel/Cells/XLCell.cs index d9d8fbe..71e0a30 100644 --- a/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/Excel/Cells/XLCell.cs @@ -105,7 +105,27 @@ get { return _worksheet; } } - public XLAddress Address { get; internal set; } + private int _rowNumber; + private int _columnNumber; + private bool _fixedRow; + private bool _fixedCol; + + public XLAddress Address + { + get + { + return new XLAddress(_worksheet, _rowNumber, _columnNumber, _fixedRow, _fixedCol); + } + internal set + { + if (value == null) + return; + _rowNumber = value.RowNumber; + _columnNumber = value.ColumnNumber; + _fixedRow = value.FixedRow; + _fixedCol = value.FixedColumn; + } + } public string InnerText { @@ -129,27 +149,14 @@ } } - public XLDataValidation DataValidation + /// + /// Get the data validation rule containing current cell or create a new one if no rule was defined for cell. + /// + public IXLDataValidation DataValidation { get { - using (var asRange = AsRange()) - { - var dv = asRange.DataValidation; // Call the data validation to break it into pieces - foreach (var d in Worksheet.DataValidations) - { - var rs = d.Ranges; - if (rs.Count == 1) - { - var r = rs.Single(); - var ra1 = r.RangeAddress.ToStringRelative(); - var ra2 = asRange.RangeAddress.ToStringRelative(); - if (ra1.Equals(ra2)) - return d as XLDataValidation; - } - } - } - return null; + return SetDataValidation(); } } @@ -324,7 +331,6 @@ if (_dataType == XLDataType.Boolean) return (cValue != "0").ToExcelFormat(format); - else if (_dataType == XLDataType.TimeSpan || _dataType == XLDataType.DateTime || IsDateFormat()) { double dTest; @@ -336,7 +342,6 @@ return cValue; } - else if (_dataType == XLDataType.Number) { double dTest; @@ -347,7 +352,6 @@ return cValue; } - else return cValue; } @@ -476,8 +480,8 @@ if (data != null && !(data is String)) { - var ro = Address.RowNumber + 1; - var fRo = Address.RowNumber; + var ro = _rowNumber + 1; + var fRo = _rowNumber; var hasTitles = false; var maxCo = 0; var isDataTable = false; @@ -487,15 +491,15 @@ if (!data.Any()) { if (itemType.IsPrimitive || itemType == typeof(String) || itemType == typeof(DateTime) || itemType.IsNumber()) - maxCo = Address.ColumnNumber + 1; + maxCo = _columnNumber + 1; else - maxCo = Address.ColumnNumber + itemType.GetFields().Length + itemType.GetProperties().Length; + maxCo = _columnNumber + itemType.GetFields().Length + itemType.GetProperties().Length; } else if (itemType.IsPrimitive || itemType == typeof(String) || itemType == typeof(DateTime) || itemType.IsNumber()) { foreach (object o in data) { - var co = Address.ColumnNumber; + var co = _columnNumber; if (!hasTitles) { @@ -505,7 +509,7 @@ _worksheet.SetValue(fieldName, fRo, co); hasTitles = true; - co = Address.ColumnNumber; + co = _columnNumber; } _worksheet.SetValue(o, ro, co); @@ -559,7 +563,7 @@ accessor = accessorCache[type]; } - var co = Address.ColumnNumber; + var co = _columnNumber; if (itemType.IsArray) { @@ -586,7 +590,7 @@ co++; } - co = Address.ColumnNumber; + co = _columnNumber; hasTitles = true; } @@ -612,7 +616,7 @@ co++; } - co = Address.ColumnNumber; + co = _columnNumber; hasTitles = true; } @@ -640,7 +644,7 @@ co++; } - co = Address.ColumnNumber; + co = _columnNumber; hasTitles = true; } @@ -666,8 +670,8 @@ ClearMerged(); var range = _worksheet.Range( - Address.RowNumber, - Address.ColumnNumber, + _rowNumber, + _columnNumber, ro - 1, maxCo - 1); @@ -703,8 +707,8 @@ throw new InvalidOperationException(String.Format("This cell '{0}' is already part of a table.", this.Address.ToString())); if (data.Rows.Cast().Any()) return InsertTable(data.Rows.Cast(), tableName, createTable); - var ro = Address.RowNumber; - var co = Address.ColumnNumber; + var ro = _rowNumber; + var co = _columnNumber; foreach (DataColumn col in data.Columns) { @@ -714,8 +718,8 @@ ClearMerged(); var range = _worksheet.Range( - Address.RowNumber, - Address.ColumnNumber, + _rowNumber, + _columnNumber, ro, co - 1); @@ -729,8 +733,8 @@ var table = this.Worksheet.Tables.FirstOrDefault(t => t.AsRange().Contains(this)); if (table == null) return XLTableCellType.None; - if (table.ShowHeaderRow && table.HeadersRow().RowNumber().Equals(this.Address.RowNumber)) return XLTableCellType.Header; - if (table.ShowTotalsRow && table.TotalsRow().RowNumber().Equals(this.Address.RowNumber)) return XLTableCellType.Total; + if (table.ShowHeaderRow && table.HeadersRow().RowNumber().Equals(this._rowNumber)) return XLTableCellType.Header; + if (table.ShowTotalsRow && table.TotalsRow().RowNumber().Equals(this._rowNumber)) return XLTableCellType.Total; return XLTableCellType.Data; } @@ -744,8 +748,8 @@ { if (data != null && !(data is String)) { - var rowNumber = Address.RowNumber; - var columnNumber = Address.ColumnNumber; + var rowNumber = _rowNumber; + var columnNumber = _columnNumber; var maxColumnNumber = 0; var maxRowNumber = 0; @@ -763,9 +767,9 @@ var itemType = m.GetType(); if (transpose) - rowNumber = Address.RowNumber; + rowNumber = _rowNumber; else - columnNumber = Address.ColumnNumber; + columnNumber = _columnNumber; if (itemType.IsPrimitive || itemType == typeof(String) || itemType == typeof(DateTime) || itemType.IsNumber()) { @@ -869,8 +873,8 @@ ClearMerged(); return _worksheet.Range( - Address.RowNumber, - Address.ColumnNumber, + _rowNumber, + _columnNumber, maxRowNumber - 1, maxColumnNumber - 1); } @@ -1029,7 +1033,10 @@ if (clearOptions == XLClearOptions.Formats || clearOptions == XLClearOptions.ContentsAndFormats) { if (HasDataValidation) - DataValidation.Clear(); + { + var validation = NewDataValidation; + Worksheet.DataValidations.Delete(validation); + } SetStyle(Worksheet.Style); } @@ -1228,11 +1235,11 @@ if (_style == null) { XLRow row; - if (Worksheet.Internals.RowsCollection.TryGetValue(Address.RowNumber, out row) && !row.Style.Equals(Worksheet.Style)) + if (Worksheet.Internals.RowsCollection.TryGetValue(_rowNumber, out row) && !row.Style.Equals(Worksheet.Style)) return false; XLColumn column; - if (Worksheet.Internals.ColumnsCollection.TryGetValue(Address.ColumnNumber, out column) && !column.Style.Equals(Worksheet.Style)) + if (Worksheet.Internals.ColumnsCollection.TryGetValue(_columnNumber, out column) && !column.Style.Equals(Worksheet.Style)) return false; } } @@ -1246,12 +1253,12 @@ public IXLColumn WorksheetColumn() { - return Worksheet.Column(Address.ColumnNumber); + return Worksheet.Column(_columnNumber); } public IXLRow WorksheetRow() { - return Worksheet.Row(Address.RowNumber); + return Worksheet.Row(_rowNumber); } public IXLCell CopyTo(IXLCell target) @@ -1289,19 +1296,38 @@ public Boolean HasDataValidation { - get + get { return GetDataValidation() != null; } + } + + /// + /// Get the data validation rule containing current cell. + /// + /// The data validation rule applying to the current cell or null if there is no such rule. + private IXLDataValidation GetDataValidation() + { + foreach (var xlDataValidation in Worksheet.DataValidations) { - using (var asRange = AsRange()) - return Worksheet.DataValidations.Any(dv => - { - using (var rngs = dv.Ranges) return dv.IsDirty() && rngs.Contains(asRange); - }); + foreach (var range in xlDataValidation.Ranges) + { + if (range.Contains(this)) + return xlDataValidation; + } } + return null; } public IXLDataValidation SetDataValidation() { - return DataValidation; + var validation = GetDataValidation(); + if (validation == null) + { + using (var range = this.AsRange()) + { + validation = new XLDataValidation(range); + Worksheet.DataValidations.Add(validation); + } + } + return validation; } public void Select() @@ -1753,7 +1779,7 @@ { var maxRows = asRange.RowCount(); var maxColumns = asRange.ColumnCount(); - using (var rng = Worksheet.Range(Address.RowNumber, Address.ColumnNumber, maxRows, maxColumns)) + using (var rng = Worksheet.Range(_rowNumber, _columnNumber, maxRows, maxColumns)) rng.Clear(); } @@ -1762,19 +1788,19 @@ foreach (var sourceCell in asRange.CellsUsed(true)) { Worksheet.Cell( - Address.RowNumber + sourceCell.Address.RowNumber - minRow, - Address.ColumnNumber + sourceCell.Address.ColumnNumber - minColumn + _rowNumber + sourceCell.Address.RowNumber - minRow, + _columnNumber + sourceCell.Address.ColumnNumber - minColumn ).CopyFromInternal(sourceCell as XLCell, true); } var rangesToMerge = (from mergedRange in (asRange.Worksheet).Internals.MergedRanges where asRange.Contains(mergedRange) let initialRo = - Address.RowNumber + + _rowNumber + (mergedRange.RangeAddress.FirstAddress.RowNumber - asRange.RangeAddress.FirstAddress.RowNumber) let initialCo = - Address.ColumnNumber + + _columnNumber + (mergedRange.RangeAddress.FirstAddress.ColumnNumber - asRange.RangeAddress.FirstAddress.ColumnNumber) select @@ -1809,23 +1835,23 @@ int cCnt = minCo - fromRange.RangeAddress.FirstAddress.ColumnNumber + 1; rCnt = Math.Min(rCnt, fromRange.RowCount()); cCnt = Math.Min(cCnt, fromRange.ColumnCount()); - var toRange = Worksheet.Range(this, Worksheet.Cell(Address.RowNumber + rCnt - 1, Address.ColumnNumber + cCnt - 1)); + var toRange = Worksheet.Range(this, Worksheet.Cell(_rowNumber + rCnt - 1, _columnNumber + cCnt - 1)); var formats = srcSheet.ConditionalFormats.Where(f => f.Range.Intersects(fromRange)); foreach (var cf in formats.ToList()) { var fmtRange = Relative(Intersection(cf.Range, fromRange), fromRange, toRange); - var c = new XLConditionalFormat((XLRange) fmtRange, true); + var c = new XLConditionalFormat((XLRange)fmtRange, true); c.CopyFrom(cf); foreach (var v in c.Values.ToList()) { var f = v.Value.Value; if (v.Value.IsFormula) { - var r1c1 = ((XLCell) cf.Range.FirstCell()).GetFormulaR1C1(f); + var r1c1 = ((XLCell)cf.Range.FirstCell()).GetFormulaR1C1(f); f = ((XLCell)fmtRange.FirstCell()).GetFormulaA1(r1c1); } - c.Values[v.Key] = new XLFormula {_value = f, IsFormula = v.Value.IsFormula}; + c.Values[v.Key] = new XLFormula { _value = f, IsFormula = v.Value.IsFormula }; } _worksheet.ConditionalFormats.Add(c); @@ -2053,7 +2079,7 @@ { string columnToReturn; if (columnPart == "C") - columnToReturn = XLHelper.GetColumnLetterFromNumber(Address.ColumnNumber + columnsToShift); + columnToReturn = XLHelper.GetColumnLetterFromNumber(_columnNumber + columnsToShift); else { var bIndex = columnPart.IndexOf("["); @@ -2061,14 +2087,14 @@ if (bIndex >= 0) { columnToReturn = XLHelper.GetColumnLetterFromNumber( - Address.ColumnNumber + + _columnNumber + Int32.Parse(columnPart.Substring(bIndex + 1, columnPart.Length - bIndex - 2)) + columnsToShift ); } else if (mIndex >= 0) { columnToReturn = XLHelper.GetColumnLetterFromNumber( - Address.ColumnNumber + Int32.Parse(columnPart.Substring(mIndex)) + columnsToShift + _columnNumber + Int32.Parse(columnPart.Substring(mIndex)) + columnsToShift ); } else @@ -2086,14 +2112,14 @@ { string rowToReturn; if (rowPart == "R") - rowToReturn = (Address.RowNumber + rowsToShift).ToString(); + rowToReturn = (_rowNumber + rowsToShift).ToString(); else { var bIndex = rowPart.IndexOf("["); if (bIndex >= 0) { rowToReturn = - (Address.RowNumber + Int32.Parse(rowPart.Substring(bIndex + 1, rowPart.Length - bIndex - 2)) + + (_rowNumber + Int32.Parse(rowPart.Substring(bIndex + 1, rowPart.Length - bIndex - 2)) + rowsToShift).ToString(); } else @@ -2140,9 +2166,9 @@ { string rowPart; rowNumber += rowsToShift; - var rowDiff = rowNumber - Address.RowNumber; + var rowDiff = rowNumber - _rowNumber; if (rowDiff != 0 || fixedRow) - rowPart = fixedRow ? String.Format("R{0}", rowNumber) : String.Format("R[{0}]", rowDiff); + rowPart = fixedRow ? "R" + rowNumber : "R[" + rowDiff + "]"; else rowPart = "R"; @@ -2153,9 +2179,9 @@ { string columnPart; columnNumber += columnsToShift; - var columnDiff = columnNumber - Address.ColumnNumber; + var columnDiff = columnNumber - _columnNumber; if (columnDiff != 0 || fixedColumn) - columnPart = fixedColumn ? String.Format("C{0}", columnNumber) : String.Format("C[{0}]", columnDiff); + columnPart = fixedColumn ? "C" + columnNumber : "C[" + columnDiff + "]"; else columnPart = "C"; @@ -2223,7 +2249,7 @@ var conditionalFormats = source.Worksheet.ConditionalFormats.Where(c => c.Range.Contains(source)).ToList(); foreach (var cf in conditionalFormats) { - var c = new XLConditionalFormat(cf as XLConditionalFormat) { Range = AsRange() }; + var c = new XLConditionalFormat(cf as XLConditionalFormat, AsRange()); var oldValues = c.Values.Values.ToList(); c.Values.Clear(); foreach (var v in oldValues) @@ -2244,9 +2270,9 @@ return this; } - internal void CopyDataValidation(XLCell otherCell, XLDataValidation otherDv) + internal void CopyDataValidation(XLCell otherCell, IXLDataValidation otherDv) { - var thisDv = DataValidation; + var thisDv = SetDataValidation() as XLDataValidation; thisDv.CopyFrom(otherDv); thisDv.Value = GetFormulaA1(otherCell.GetFormulaR1C1(otherDv.Value)); thisDv.MinValue = GetFormulaA1(otherCell.GetFormulaR1C1(otherDv.MinValue)); @@ -2263,7 +2289,7 @@ { if (XLHelper.IsNullOrWhiteSpace(formulaA1)) return String.Empty; - var value = formulaA1; // ">" + formulaA1 + "<"; + var value = formulaA1; var regex = A1SimpleRegex; @@ -2302,6 +2328,12 @@ && shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= matchRange.RangeAddress.FirstAddress.ColumnNumber && shiftedRange.RangeAddress.LastAddress.ColumnNumber >= matchRange.RangeAddress.LastAddress.ColumnNumber) { + if (useSheetName) + { + sb.Append(sheetName.EscapeSheetName()); + sb.Append('!'); + } + if (A1RowRegex.IsMatch(rangeAddress)) { var rows = rangeAddress.Split(':'); @@ -2325,115 +2357,53 @@ else row2 = (XLHelper.TrimRowNumber(Int32.Parse(row2String) + rowsShifted)).ToInvariantString(); - sb.Append(useSheetName - ? String.Format("{0}!{1}:{2}", sheetName.EscapeSheetName(), row1, row2) - : String.Format("{0}:{1}", row1, row2)); + sb.Append(row1); + sb.Append(':'); + sb.Append(row2); } else if (shiftedRange.RangeAddress.FirstAddress.RowNumber <= matchRange.RangeAddress.FirstAddress.RowNumber) { if (rangeAddress.Contains(':')) { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}:{2}", - sheetName.EscapeSheetName(), - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - LastAddress.ColumnLetter, - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}:{1}", - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - LastAddress.ColumnLetter, - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } + sb.Append( + new XLAddress( + worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), + matchRange.RangeAddress.FirstAddress.ColumnLetter, + matchRange.RangeAddress.FirstAddress.FixedRow, + matchRange.RangeAddress.FirstAddress.FixedColumn)); + sb.Append(':'); + sb.Append( + new XLAddress( + worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), + matchRange.RangeAddress.LastAddress.ColumnLetter, + matchRange.RangeAddress.LastAddress.FixedRow, + matchRange.RangeAddress.LastAddress.FixedColumn)); } else { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}", - sheetName.EscapeSheetName(), - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}", - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - FirstAddress.ColumnLetter, - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn))); - } + sb.Append( + new XLAddress( + worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.FirstAddress.RowNumber + rowsShifted), + matchRange.RangeAddress.FirstAddress.ColumnLetter, + matchRange.RangeAddress.FirstAddress.FixedRow, + matchRange.RangeAddress.FirstAddress.FixedColumn)); } } else { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}:{2}", - sheetName.EscapeSheetName(), - matchRange.RangeAddress.FirstAddress, - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - LastAddress.ColumnLetter, - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}:{1}", - matchRange.RangeAddress.FirstAddress, - new XLAddress(worksheetInAction, - XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), - matchRange.RangeAddress. - LastAddress.ColumnLetter, - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } + sb.Append(matchRange.RangeAddress.FirstAddress); + sb.Append(':'); + sb.Append( + new XLAddress( + worksheetInAction, + XLHelper.TrimRowNumber(matchRange.RangeAddress.LastAddress.RowNumber + rowsShifted), + matchRange.RangeAddress.LastAddress.ColumnLetter, + matchRange.RangeAddress.LastAddress.FixedRow, + matchRange.RangeAddress.LastAddress.FixedColumn)); } } else @@ -2448,6 +2418,7 @@ } else sb.Append(value.Substring(lastIndex, matchIndex - lastIndex + matchString.Length)); + lastIndex = matchIndex + matchString.Length; } @@ -2455,9 +2426,6 @@ sb.Append(value.Substring(lastIndex)); return sb.ToString(); - - //string retVal = sb.ToString(); - //return retVal.Substring(1, retVal.Length - 2); } internal void ShiftFormulaColumns(XLRange shiftedRange, int columnsShifted) @@ -2470,7 +2438,7 @@ { if (XLHelper.IsNullOrWhiteSpace(formulaA1)) return String.Empty; - var value = formulaA1; // ">" + formulaA1 + "<"; + var value = formulaA1; var regex = A1SimpleRegex; @@ -2513,6 +2481,12 @@ shiftedRange.RangeAddress.LastAddress.RowNumber >= matchRange.RangeAddress.LastAddress.RowNumber) { + if (useSheetName) + { + sb.Append(sheetName.EscapeSheetName()); + sb.Append('!'); + } + if (A1ColumnRegex.IsMatch(rangeAddress)) { var columns = rangeAddress.Split(':'); @@ -2550,115 +2524,53 @@ columnsShifted, true); } - sb.Append(useSheetName - ? String.Format("{0}!{1}:{2}", sheetName.EscapeSheetName(), column1, column2) - : String.Format("{0}:{1}", column1, column2)); + sb.Append(column1); + sb.Append(':'); + sb.Append(column2); } else if (shiftedRange.RangeAddress.FirstAddress.ColumnNumber <= matchRange.RangeAddress.FirstAddress.ColumnNumber) { if (rangeAddress.Contains(':')) { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}:{2}", - sheetName.EscapeSheetName(), - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}:{1}", - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn), - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } + sb.Append( + new XLAddress( + worksheetInAction, + matchRange.RangeAddress.FirstAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress.FirstAddress.FixedRow, + matchRange.RangeAddress.FirstAddress.FixedColumn)); + sb.Append(':'); + sb.Append( + new XLAddress( + worksheetInAction, + matchRange.RangeAddress.LastAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress.LastAddress.FixedRow, + matchRange.RangeAddress.LastAddress.FixedColumn)); } else { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}", - sheetName.EscapeSheetName(), - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}", - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - FirstAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - FirstAddress.FixedRow, - matchRange.RangeAddress. - FirstAddress.FixedColumn))); - } + sb.Append( + new XLAddress( + worksheetInAction, + matchRange.RangeAddress.FirstAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.FirstAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress.FirstAddress.FixedRow, + matchRange.RangeAddress.FirstAddress.FixedColumn)); } } else { - if (useSheetName) - { - sb.Append(String.Format("{0}!{1}:{2}", - sheetName.EscapeSheetName(), - matchRange.RangeAddress.FirstAddress, - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } - else - { - sb.Append(String.Format("{0}:{1}", - matchRange.RangeAddress.FirstAddress, - new XLAddress(worksheetInAction, - matchRange.RangeAddress. - LastAddress.RowNumber, - XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), - matchRange.RangeAddress. - LastAddress.FixedRow, - matchRange.RangeAddress. - LastAddress.FixedColumn))); - } + sb.Append(matchRange.RangeAddress.FirstAddress); + sb.Append(':'); + sb.Append( + new XLAddress( + worksheetInAction, + matchRange.RangeAddress.LastAddress.RowNumber, + XLHelper.TrimColumnNumber(matchRange.RangeAddress.LastAddress.ColumnNumber + columnsShifted), + matchRange.RangeAddress.LastAddress.FixedRow, + matchRange.RangeAddress.LastAddress.FixedColumn)); } } else @@ -2680,16 +2592,11 @@ sb.Append(value.Substring(lastIndex)); return sb.ToString(); - - //string retVal = sb.ToString(); - //return retVal.Substring(1, retVal.Length - 2); } - // -- - private XLCell CellShift(Int32 rowsToShift, Int32 columnsToShift) { - return Worksheet.Cell(Address.RowNumber + rowsToShift, Address.ColumnNumber + columnsToShift); + return Worksheet.Cell(_rowNumber + rowsToShift, _columnNumber + columnsToShift); } #region Nested type: FormulaConversionType diff --git a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs index 1e4cdb5..e55ec35 100644 --- a/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs +++ b/ClosedXML/Excel/ConditionalFormats/XLConditionalFormat.cs @@ -116,10 +116,10 @@ } - public XLConditionalFormat(XLConditionalFormat conditionalFormat) + public XLConditionalFormat(XLConditionalFormat conditionalFormat, IXLRange targetRange) { + Range = targetRange; Id = Guid.NewGuid(); - Range = conditionalFormat.Range; Style = new XLStyle(this, conditionalFormat.Style); Values = new XLDictionary(conditionalFormat.Values); Colors = new XLDictionary(conditionalFormat.Colors); diff --git a/ClosedXML/Excel/Coordinates/XLAddress.cs b/ClosedXML/Excel/Coordinates/XLAddress.cs index 56f3922..d8f5dcd 100644 --- a/ClosedXML/Excel/Coordinates/XLAddress.cs +++ b/ClosedXML/Excel/Coordinates/XLAddress.cs @@ -237,22 +237,19 @@ public string ToString(XLReferenceStyle referenceStyle, bool includeSheet) { - string address = string.Empty; + string address; if (referenceStyle == XLReferenceStyle.A1) - - address = ColumnLetter + _rowNumber.ToInvariantString(); - else if (referenceStyle == XLReferenceStyle.R1C1) - - address = String.Format("R{0}C{1}", _rowNumber.ToInvariantString(), ColumnNumber); - else if (HasWorksheet && Worksheet.Workbook.ReferenceStyle == XLReferenceStyle.R1C1) - - address = String.Format("R{0}C{1}", _rowNumber.ToInvariantString(), ColumnNumber); + address = GetTrimmedAddress(); + else if (referenceStyle == XLReferenceStyle.R1C1 + || HasWorksheet && Worksheet.Workbook.ReferenceStyle == XLReferenceStyle.R1C1) + address = "R" + _rowNumber.ToInvariantString() + "C" + ColumnNumber.ToInvariantString(); else - address = ColumnLetter + _rowNumber.ToInvariantString(); + address = GetTrimmedAddress(); if (includeSheet) - return String.Format("{0}!{1}", + return String.Concat( Worksheet.Name.EscapeSheetName(), + '!', address); return address; @@ -388,9 +385,11 @@ public String ToStringRelative(Boolean includeSheet) { if (includeSheet) - return String.Format("{0}!{1}", + return String.Concat( Worksheet.Name.EscapeSheetName(), - GetTrimmedAddress()); + '!', + GetTrimmedAddress() + ); return GetTrimmedAddress(); } @@ -403,18 +402,33 @@ public String ToStringFixed(XLReferenceStyle referenceStyle, Boolean includeSheet) { String address; - if (referenceStyle == XLReferenceStyle.A1) - address = String.Format("${0}${1}", ColumnLetter, _rowNumber.ToInvariantString()); - else if (referenceStyle == XLReferenceStyle.R1C1) - address = String.Format("R{0}C{1}", _rowNumber.ToInvariantString(), ColumnNumber); - else if (HasWorksheet && Worksheet.Workbook.ReferenceStyle == XLReferenceStyle.R1C1) - address = String.Format("R{0}C{1}", _rowNumber.ToInvariantString(), ColumnNumber); - else - address = String.Format("${0}${1}", ColumnLetter, _rowNumber.ToInvariantString()); + + if (referenceStyle == XLReferenceStyle.Default && HasWorksheet) + referenceStyle = Worksheet.Workbook.ReferenceStyle; + + if (referenceStyle == XLReferenceStyle.Default) + referenceStyle = XLReferenceStyle.A1; + + Debug.Assert(referenceStyle != XLReferenceStyle.Default); + + switch (referenceStyle) + { + case XLReferenceStyle.A1: + address = String.Concat('$', ColumnLetter, '$', _rowNumber.ToInvariantString()); + break; + + case XLReferenceStyle.R1C1: + address = String.Concat('R', _rowNumber.ToInvariantString(), 'C', ColumnNumber); + break; + + default: + throw new NotImplementedException(); + } if (includeSheet) - return String.Format("{0}!{1}", + return String.Concat( Worksheet.Name.EscapeSheetName(), + '!', address); return address; diff --git a/ClosedXML/Excel/DataValidation/XLDataValidation.cs b/ClosedXML/Excel/DataValidation/XLDataValidation.cs index 321e6d8..8c189db 100644 --- a/ClosedXML/Excel/DataValidation/XLDataValidation.cs +++ b/ClosedXML/Excel/DataValidation/XLDataValidation.cs @@ -4,20 +4,27 @@ { internal class XLDataValidation : IXLDataValidation { - public XLDataValidation(IXLRanges ranges) + private XLDataValidation() { - Ranges = new XLRanges(); - ranges.ForEach(r=> - { - var newR = - new XLRange(new XLRangeParameters(r.RangeAddress as XLRangeAddress, - r.Worksheet.Style) {IgnoreEvents = true}); - (Ranges as XLRanges).Add(newR); - } ); Initialize(); } + public XLDataValidation(IXLRange range) + :this() + { + Ranges.Add(new XLRange(new XLRangeParameters(range.RangeAddress as XLRangeAddress, range.Worksheet.Style))); + } + + public XLDataValidation(IXLRanges ranges) + :this() + { + ranges.ForEach(range => + { + Ranges.Add(new XLRange(new XLRangeParameters(range.RangeAddress as XLRangeAddress, range.Worksheet.Style))); + }); + } + private void Initialize() { AllowedValues = XLAllowedValues.AnyValue; @@ -191,4 +198,4 @@ Initialize(); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/DataValidation/XLDataValidations.cs b/ClosedXML/Excel/DataValidation/XLDataValidations.cs index eb95d43..fa339f6 100644 --- a/ClosedXML/Excel/DataValidation/XLDataValidations.cs +++ b/ClosedXML/Excel/DataValidation/XLDataValidations.cs @@ -51,4 +51,4 @@ _dataValidations.RemoveAll(dv => dv.Ranges.Contains(range)); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Drawings/XLPictures.cs b/ClosedXML/Excel/Drawings/XLPictures.cs index 38cac21..705ccb1 100644 --- a/ClosedXML/Excel/Drawings/XLPictures.cs +++ b/ClosedXML/Excel/Drawings/XLPictures.cs @@ -17,7 +17,7 @@ public XLPictures(XLWorksheet worksheet) { _worksheet = worksheet; - Deleted = new HashSet(); + Deleted = new HashSet(); } public int Count diff --git a/ClosedXML/Excel/EnumConverter.cs b/ClosedXML/Excel/EnumConverter.cs index af5ebe8..16a6cc9 100644 --- a/ClosedXML/Excel/EnumConverter.cs +++ b/ClosedXML/Excel/EnumConverter.cs @@ -601,6 +601,21 @@ } } + public static DateTimeGroupingValues ToOpenXml(this XLDateTimeGrouping value) + { + switch (value) + { + case XLDateTimeGrouping.Year: return DateTimeGroupingValues.Year; + case XLDateTimeGrouping.Month: return DateTimeGroupingValues.Month; + case XLDateTimeGrouping.Day: return DateTimeGroupingValues.Day; + case XLDateTimeGrouping.Hour: return DateTimeGroupingValues.Hour; + case XLDateTimeGrouping.Minute: return DateTimeGroupingValues.Minute; + case XLDateTimeGrouping.Second: return DateTimeGroupingValues.Second; + + default: + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); + } + } public static SheetViewValues ToOpenXml(this XLSheetViewOptions value) { switch (value) @@ -1357,6 +1372,22 @@ } } + public static XLDateTimeGrouping ToClosedXml(this DateTimeGroupingValues value) + { + switch (value) + { + case DateTimeGroupingValues.Year: return XLDateTimeGrouping.Year; + case DateTimeGroupingValues.Month: return XLDateTimeGrouping.Month; + case DateTimeGroupingValues.Day: return XLDateTimeGrouping.Day; + case DateTimeGroupingValues.Hour: return XLDateTimeGrouping.Hour; + case DateTimeGroupingValues.Minute: return XLDateTimeGrouping.Minute; + case DateTimeGroupingValues.Second: return XLDateTimeGrouping.Second; + + default: + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); + } + } + public static XLSheetViewOptions ToClosedXml(this SheetViewValues value) { switch (value) diff --git a/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs b/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs index f60e516..376809a 100644 --- a/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs +++ b/ClosedXML/Excel/Hyperlinks/XLHyperlink_public.cs @@ -75,14 +75,18 @@ if (_internalAddress.Contains('!')) { return _internalAddress[0] != '\'' - ? String.Format("{0}!{1}", + ? String.Concat( _internalAddress .Substring(0, _internalAddress.IndexOf('!')) .EscapeSheetName(), + '!', _internalAddress.Substring(_internalAddress.IndexOf('!') + 1)) : _internalAddress; } - return String.Format("{0}!{1}", Worksheet.Name.EscapeSheetName(), _internalAddress); + return String.Concat( + Worksheet.Name.EscapeSheetName(), + '!', + _internalAddress); } set { diff --git a/ClosedXML/Excel/PivotTables/IXLPivotField.cs b/ClosedXML/Excel/PivotTables/IXLPivotField.cs index cc6fe91..9571eb0 100644 --- a/ClosedXML/Excel/PivotTables/IXLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/IXLPivotField.cs @@ -24,12 +24,14 @@ { String SourceName { get; } String CustomName { get; set; } + String SubtotalCaption { get; set; } List Subtotals { get; } Boolean IncludeNewItemsInFilter { get; set; } - XLPivotLayout Layout { get; set; } - Boolean SubtotalsAtTop { get; set; } + Boolean Outline { get; set; } + Boolean Compact { get; set; } + Boolean? SubtotalsAtTop { get; set; } Boolean RepeatItemLabels { get; set; } Boolean InsertBlankLines { get; set; } Boolean ShowBlankItems { get; set; } @@ -38,6 +40,7 @@ XLPivotSortType SortType { get; set; } IXLPivotField SetCustomName(String value); + IXLPivotField SetSubtotalCaption(String value); IXLPivotField AddSubtotal(XLSubtotalFunction value); IXLPivotField SetIncludeNewItemsInFilter(); IXLPivotField SetIncludeNewItemsInFilter(Boolean value); diff --git a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs index ef16caf..9b35733 100644 --- a/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs +++ b/ClosedXML/Excel/PivotTables/PivotValues/XLPivotValues.cs @@ -1,11 +1,10 @@ using System; using System.Collections.Generic; using System.Linq; -using System.Text; namespace ClosedXML.Excel { - internal class XLPivotValues: IXLPivotValues + internal class XLPivotValues : IXLPivotValues { private readonly Dictionary _pivotValues = new Dictionary(); @@ -30,6 +29,7 @@ { return Add(sourceName, sourceName); } + public IXLPivotValue Add(String sourceName, String customName) { if (sourceName != XLConstants.PivotTableValuesSentinalLabel && !this._pivotTable.SourceRangeFieldsAvailable.Contains(sourceName, StringComparer.OrdinalIgnoreCase)) @@ -38,7 +38,7 @@ var pivotValue = new XLPivotValue(sourceName) { CustomName = customName }; _pivotValues.Add(customName, pivotValue); - if (_pivotValues.Count > 1 && !this._pivotTable.ColumnLabels.Any(cl => cl.SourceName == XLConstants.PivotTableValuesSentinalLabel) && !this._pivotTable.RowLabels.Any(rl => rl.SourceName == XLConstants.PivotTableValuesSentinalLabel)) + if (_pivotValues.Count > 1 && this._pivotTable.ColumnLabels.All(cl => cl.SourceName != XLConstants.PivotTableValuesSentinalLabel) && this._pivotTable.RowLabels.All(rl => rl.SourceName != XLConstants.PivotTableValuesSentinalLabel)) _pivotTable.ColumnLabels.Add(XLConstants.PivotTableValuesSentinalLabel); return pivotValue; @@ -48,6 +48,7 @@ { _pivotValues.Clear(); } + public void Remove(String sourceName) { _pivotValues.Remove(sourceName); diff --git a/ClosedXML/Excel/PivotTables/XLPivotField.cs b/ClosedXML/Excel/PivotTables/XLPivotField.cs index 64a0b99..dbdfac9 100644 --- a/ClosedXML/Excel/PivotTables/XLPivotField.cs +++ b/ClosedXML/Excel/PivotTables/XLPivotField.cs @@ -13,6 +13,7 @@ Subtotals = new List(); SelectedValues = new List(); SortType = XLPivotSortType.Default; + SetExcelDefaults(); } public String SourceName { get; private set; } @@ -20,6 +21,10 @@ public IXLPivotField SetCustomName(String value) { CustomName = value; return this; } + public String SubtotalCaption { get; set; } + + public IXLPivotField SetSubtotalCaption(String value) { SubtotalCaption = value; return this; } + public List Subtotals { get; private set; } public IXLPivotField AddSubtotal(XLSubtotalFunction value) { Subtotals.Add(value); return this; } @@ -30,11 +35,22 @@ public IXLPivotField SetIncludeNewItemsInFilter(Boolean value) { IncludeNewItemsInFilter = value; return this; } - public XLPivotLayout Layout { get; set; } + public bool Outline { get; set; } + public bool Compact { get; set; } - public IXLPivotField SetLayout(XLPivotLayout value) { Layout = value; return this; } + public IXLPivotField SetLayout(XLPivotLayout value) + { + Compact = false; + Outline = false; + switch (value) + { + case XLPivotLayout.Compact: Compact = true; break; + case XLPivotLayout.Outline: Outline = true; break; + } + return this; + } - public Boolean SubtotalsAtTop { get; set; } + public Boolean? SubtotalsAtTop { get; set; } public IXLPivotField SetSubtotalsAtTop() { SubtotalsAtTop = true; return this; } @@ -80,5 +96,18 @@ SelectedValues.Add(value); return this; } + + private void SetExcelDefaults() + { + IncludeNewItemsInFilter = false; + Outline = true; + Compact = true; + InsertBlankLines = false; + ShowBlankItems = true; + InsertPageBreaks = false; + RepeatItemLabels = false; + SubtotalsAtTop = true; + Collapsed = false; + } } } diff --git a/ClosedXML/Excel/Ranges/XLRangeAddress.cs b/ClosedXML/Excel/Ranges/XLRangeAddress.cs index b1d20c4..177677e 100644 --- a/ClosedXML/Excel/Ranges/XLRangeAddress.cs +++ b/ClosedXML/Excel/Ranges/XLRangeAddress.cs @@ -142,12 +142,17 @@ public String ToStringRelative(Boolean includeSheet) { if (includeSheet) - return String.Format("{0}!{1}:{2}", + return String.Concat( Worksheet.Name.EscapeSheetName(), + '!', _firstAddress.ToStringRelative(), + ':', _lastAddress.ToStringRelative()); - - return _firstAddress.ToStringRelative() + ":" + _lastAddress.ToStringRelative(); + else + return string.Concat( + _firstAddress.ToStringRelative(), + ":", + _lastAddress.ToStringRelative()); } public String ToStringFixed(XLReferenceStyle referenceStyle) diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index cc6a57c..cfbf9eb 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -4,6 +4,7 @@ using System.Collections.Generic; using System.Globalization; using System.Linq; +using System.Text; namespace ClosedXML.Excel { @@ -99,99 +100,46 @@ get { return RangeAddress.Worksheet; } } - public XLDataValidation NewDataValidation + public IXLDataValidation NewDataValidation { get { var newRanges = new XLRanges { AsRange() }; - var dataValidation = new XLDataValidation(newRanges); + var dataValidation = DataValidation; + if (dataValidation != null) + Worksheet.DataValidations.Delete(dataValidation); + + dataValidation = new XLDataValidation(newRanges); Worksheet.DataValidations.Add(dataValidation); return dataValidation; } } - public XLDataValidation DataValidation + /// + /// Get the data validation rule containing current range or create a new one if no rule was defined for range. + /// + public IXLDataValidation DataValidation { get { - IXLDataValidation dataValidationToCopy = null; - var dvEmpty = new List(); - foreach (IXLDataValidation dv in Worksheet.DataValidations) - { - foreach (IXLRange dvRange in dv.Ranges.Where(dvRange => dvRange.Intersects(this))) - { - if (dataValidationToCopy == null) - dataValidationToCopy = dv; - - dv.Ranges.Remove(dvRange); - foreach (var column in dvRange.Columns()) - { - if (column.Intersects(this)) - { - Int32 dvStart = column.RangeAddress.FirstAddress.RowNumber; - Int32 dvEnd = column.RangeAddress.LastAddress.RowNumber; - Int32 thisStart = RangeAddress.FirstAddress.RowNumber; - Int32 thisEnd = RangeAddress.LastAddress.RowNumber; - - if (thisStart > dvStart && thisEnd < dvEnd) - { - var r1 = Worksheet.Column(column.ColumnNumber()).Column(dvStart, thisStart - 1); - r1.Dispose(); - dv.Ranges.Add(r1); - var r2 = Worksheet.Column(column.ColumnNumber()).Column(thisEnd + 1, dvEnd); - r2.Dispose(); - dv.Ranges.Add(r2); - } - else - { - Int32 coStart; - if (dvStart < thisStart) - coStart = dvStart; - else - coStart = thisEnd + 1; - - if (coStart <= dvEnd) - { - Int32 coEnd; - if (dvEnd > thisEnd) - coEnd = dvEnd; - else - coEnd = thisStart - 1; - - if (coEnd >= dvStart) - { - var r = Worksheet.Column(column.ColumnNumber()).Column(coStart, coEnd); - r.Dispose(); - dv.Ranges.Add(r); - } - } - } - } - else - { - column.Dispose(); - dv.Ranges.Add(column); - } - } - - if (!dv.Ranges.Any()) - dvEmpty.Add(dv); - } - } - - dvEmpty.ForEach(dv => Worksheet.DataValidations.Delete(dv)); - - var newRanges = new XLRanges { AsRange() }; - var dataValidation = new XLDataValidation(newRanges); - if (dataValidationToCopy != null) - dataValidation.CopyFrom(dataValidationToCopy); - - Worksheet.DataValidations.Add(dataValidation); - return dataValidation; + return SetDataValidation(); } } + private IXLDataValidation GetDataValidation() + { + foreach (var xlDataValidation in Worksheet.DataValidations) + { + foreach (var range in xlDataValidation.Ranges) + { + if (range.ToString() == ToString()) + return xlDataValidation; + } + } + return null; + } + #region IXLRangeBase Members IXLRangeAddress IXLRangeBase.RangeAddress @@ -1113,7 +1061,6 @@ cell.ShiftFormulaColumns(asRange, numberOfColumns); } - var cellsDataValidations = new Dictionary(); var cellsToInsert = new Dictionary(); var cellsToDelete = new List(); int firstColumn = RangeAddress.FirstAddress.ColumnNumber; @@ -1160,39 +1107,21 @@ var newKey = new XLAddress(Worksheet, c.Address.RowNumber, newColumn, false, false); var newCell = new XLCell(Worksheet, newKey, c.GetStyleId()); newCell.CopyValuesFrom(c); - if (c.HasDataValidation) - { - cellsDataValidations.Add(newCell.Address, - new DataValidationToCopy - { DataValidation = c.DataValidation, SourceAddress = c.Address }); - c.DataValidation.Clear(); - } newCell.FormulaA1 = c.FormulaA1; cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(c.Address); } } - cellsDataValidations.ForEach(kp => - { - XLCell targetCell; - if (!cellsToInsert.TryGetValue(kp.Key, out targetCell)) - targetCell = Worksheet.Cell(kp.Key); - - targetCell.CopyDataValidation(Worksheet.Cell(kp.Value.SourceAddress), kp.Value.DataValidation); - }); - cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c.RowNumber, c.ColumnNumber)); cellsToInsert.ForEach( c => Worksheet.Internals.CellsCollection.Add(c.Key.RowNumber, c.Key.ColumnNumber, c.Value)); - //cellsDataValidations.ForEach(kp => Worksheet.Cell(kp.Key).CopyDataValidation(Worksheet.Cell(kp.Value.SourceAddress), kp.Value.DataValidation)); Int32 firstRowReturn = RangeAddress.FirstAddress.RowNumber; Int32 lastRowReturn = RangeAddress.LastAddress.RowNumber; Int32 firstColumnReturn = RangeAddress.FirstAddress.ColumnNumber; Int32 lastColumnReturn = RangeAddress.FirstAddress.ColumnNumber + numberOfColumns - 1; - Worksheet.BreakConditionalFormatsIntoCells(cellsToDelete.Except(cellsToInsert.Keys).ToList()); using (var asRange = AsRange()) Worksheet.NotifyRangeShiftedColumns(asRange, numberOfColumns); @@ -1355,7 +1284,6 @@ var cellsToInsert = new Dictionary(); var cellsToDelete = new List(); - var cellsDataValidations = new Dictionary(); int firstRow = RangeAddress.FirstAddress.RowNumber; int firstColumn = RangeAddress.FirstAddress.ColumnNumber; int lastColumn = Math.Min( @@ -1404,30 +1332,12 @@ var newKey = new XLAddress(Worksheet, newRow, c.Address.ColumnNumber, false, false); var newCell = new XLCell(Worksheet, newKey, c.GetStyleId()); newCell.CopyValuesFrom(c); - if (c.HasDataValidation) - { - cellsDataValidations.Add(newCell.Address, - new DataValidationToCopy - { DataValidation = c.DataValidation, SourceAddress = c.Address }); - c.DataValidation.Clear(); - } newCell.FormulaA1 = c.FormulaA1; cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(c.Address); } } - cellsDataValidations - .ForEach(kp => - { - XLCell targetCell; - if (!cellsToInsert.TryGetValue(kp.Key, out targetCell)) - targetCell = Worksheet.Cell(kp.Key); - - targetCell.CopyDataValidation( - Worksheet.Cell(kp.Value.SourceAddress), kp.Value.DataValidation); - }); - cellsToDelete.ForEach(c => Worksheet.Internals.CellsCollection.Remove(c.RowNumber, c.ColumnNumber)); cellsToInsert.ForEach(c => Worksheet.Internals.CellsCollection.Add(c.Key.RowNumber, c.Key.ColumnNumber, c.Value)); @@ -1436,7 +1346,6 @@ Int32 firstColumnReturn = RangeAddress.FirstAddress.ColumnNumber; Int32 lastColumnReturn = RangeAddress.LastAddress.ColumnNumber; - Worksheet.BreakConditionalFormatsIntoCells(cellsToDelete.Except(cellsToInsert.Keys).ToList()); using (var asRange = AsRange()) Worksheet.NotifyRangeShiftedRows(asRange, numberOfRows); @@ -1585,7 +1494,6 @@ var hyperlinksToRemove = Worksheet.Hyperlinks.Where(hl => Contains(hl.Cell.AsRange())).ToList(); hyperlinksToRemove.ForEach(hl => Worksheet.Hyperlinks.Delete(hl)); - Worksheet.BreakConditionalFormatsIntoCells(cellsToDelete.Except(cellsToInsert.Keys).ToList()); using (var shiftedRange = AsRange()) { if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) @@ -1597,7 +1505,12 @@ public override string ToString() { - return String.Format("{0}!{1}:{2}", Worksheet.Name.EscapeSheetName(), RangeAddress.FirstAddress, RangeAddress.LastAddress); + return String.Concat( + Worksheet.Name.EscapeSheetName(), + '!', + RangeAddress.FirstAddress, + ':', + RangeAddress.LastAddress); } protected void ShiftColumns(IXLRangeAddress thisRangeAddress, XLRange shiftedRange, int columnsShifted) @@ -1611,8 +1524,8 @@ return; bool shiftLeftBoundary = (columnsShifted > 0 && thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber) || - (columnsShifted < 0 && thisRangeAddress.FirstAddress.ColumnNumber > shiftedRange.RangeAddress.FirstAddress.ColumnNumber); - + (columnsShifted < 0 && thisRangeAddress.FirstAddress.ColumnNumber > shiftedRange.RangeAddress.FirstAddress.ColumnNumber); + bool shiftRightBoundary = thisRangeAddress.LastAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber; int newLeftBoundary = thisRangeAddress.FirstAddress.ColumnNumber; @@ -1656,13 +1569,13 @@ if (thisRangeAddress.IsInvalid || shiftedRange.RangeAddress.IsInvalid) return; bool allColumnsAreCovered = thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber && - thisRangeAddress.LastAddress.ColumnNumber <= shiftedRange.RangeAddress.LastAddress.ColumnNumber; + thisRangeAddress.LastAddress.ColumnNumber <= shiftedRange.RangeAddress.LastAddress.ColumnNumber; if (!allColumnsAreCovered) return; bool shiftTopBoundary = (rowsShifted > 0 && thisRangeAddress.FirstAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber) || - (rowsShifted < 0 && thisRangeAddress.FirstAddress.RowNumber > shiftedRange.RangeAddress.FirstAddress.RowNumber); + (rowsShifted < 0 && thisRangeAddress.FirstAddress.RowNumber > shiftedRange.RangeAddress.FirstAddress.RowNumber); bool shiftBottomBoundary = thisRangeAddress.LastAddress.RowNumber >= shiftedRange.RangeAddress.FirstAddress.RowNumber; @@ -1783,20 +1696,28 @@ get { return _sortColumns ?? (_sortColumns = new XLSortElements()); } } + private String DefaultSortString() + { + var sb = new StringBuilder(); + Int32 maxColumn = ColumnCount(); + if (maxColumn == XLHelper.MaxColumnNumber) + maxColumn = LastCellUsed(true).Address.ColumnNumber; + for (int i = 1; i <= maxColumn; i++) + { + if (sb.Length > 0) + sb.Append(','); + + sb.Append(i); + } + + return sb.ToString(); + } + public IXLRangeBase Sort() { if (!SortColumns.Any()) { - String columnsToSortBy = String.Empty; - Int32 maxColumn = ColumnCount(); - if (maxColumn == XLHelper.MaxColumnNumber) - maxColumn = LastCellUsed(true).Address.ColumnNumber; - for (int i = 1; i <= maxColumn; i++) - { - columnsToSortBy += i + ","; - } - columnsToSortBy = columnsToSortBy.Substring(0, columnsToSortBy.Length - 1); - return Sort(columnsToSortBy); + return Sort(DefaultSortString()); } SortRangeRows(); @@ -1808,15 +1729,7 @@ SortColumns.Clear(); if (XLHelper.IsNullOrWhiteSpace(columnsToSortBy)) { - columnsToSortBy = String.Empty; - Int32 maxColumn = ColumnCount(); - if (maxColumn == XLHelper.MaxColumnNumber) - maxColumn = LastCellUsed(true).Address.ColumnNumber; - for (int i = 1; i <= maxColumn; i++) - { - columnsToSortBy += i + ","; - } - columnsToSortBy = columnsToSortBy.Substring(0, columnsToSortBy.Length - 1); + columnsToSortBy = DefaultSortString(); } foreach (string coPairTrimmed in columnsToSortBy.Split(',').Select(coPair => coPair.Trim())) @@ -1905,6 +1818,7 @@ while (n > begPoint && RowQuick(pivot).CompareTo(RowQuick(n), SortColumns) <= 0) n--; + while (m < n) { SwapRows(m, n); @@ -1915,14 +1829,16 @@ while (n > begPoint && RowQuick(pivot).CompareTo(RowQuick(n), SortColumns) <= 0) n--; } + if (pivot != n) SwapRows(n, pivot); + return n; } private void SortingRangeRows(int beg, int end) { - if (end == beg) + if (beg == end) return; int pivot = SortRangeRows(beg, end); if (pivot > beg) @@ -2049,7 +1965,83 @@ public IXLDataValidation SetDataValidation() { - return DataValidation; + var existingValidation = GetDataValidation(); + if (existingValidation != null) return existingValidation; + + IXLDataValidation dataValidationToCopy = null; + var dvEmpty = new List(); + foreach (IXLDataValidation dv in Worksheet.DataValidations) + { + foreach (IXLRange dvRange in dv.Ranges.Where(dvRange => dvRange.Intersects(this))) + { + if (dataValidationToCopy == null) + dataValidationToCopy = dv; + + dv.Ranges.Remove(dvRange); + foreach (var column in dvRange.Columns()) + { + if (column.Intersects(this)) + { + Int32 dvStart = column.RangeAddress.FirstAddress.RowNumber; + Int32 dvEnd = column.RangeAddress.LastAddress.RowNumber; + Int32 thisStart = RangeAddress.FirstAddress.RowNumber; + Int32 thisEnd = RangeAddress.LastAddress.RowNumber; + + if (thisStart > dvStart && thisEnd < dvEnd) + { + var r1 = Worksheet.Column(column.ColumnNumber()).Column(dvStart, thisStart - 1); + r1.Dispose(); + dv.Ranges.Add(r1); + var r2 = Worksheet.Column(column.ColumnNumber()).Column(thisEnd + 1, dvEnd); + r2.Dispose(); + dv.Ranges.Add(r2); + } + else + { + Int32 coStart; + if (dvStart < thisStart) + coStart = dvStart; + else + coStart = thisEnd + 1; + + if (coStart <= dvEnd) + { + Int32 coEnd; + if (dvEnd > thisEnd) + coEnd = dvEnd; + else + coEnd = thisStart - 1; + + if (coEnd >= dvStart) + { + var r = Worksheet.Column(column.ColumnNumber()).Column(coStart, coEnd); + r.Dispose(); + dv.Ranges.Add(r); + } + } + } + } + else + { + column.Dispose(); + dv.Ranges.Add(column); + } + } + + if (!dv.Ranges.Any()) + dvEmpty.Add(dv); + } + } + + dvEmpty.ForEach(dv => Worksheet.DataValidations.Delete(dv)); + + var newRanges = new XLRanges { AsRange() }; + var dataValidation = new XLDataValidation(newRanges); + if (dataValidationToCopy != null) + dataValidation.CopyFrom(dataValidationToCopy); + + Worksheet.DataValidations.Add(dataValidation); + return dataValidation; } public IXLConditionalFormat AddConditionalFormat() diff --git a/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/Excel/Ranges/XLRangeRow.cs index 9b6be16..aeb54e1 100644 --- a/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -2,7 +2,6 @@ { using System; using System.Linq; - internal class XLRangeRow : XLRangeBase, IXLRangeRow { @@ -21,9 +20,8 @@ SetStyle(rangeParameters.DefaultStyle); } - #endregion + #endregion Constructor - public XLRangeParameters RangeParameters { get; private set; } #region IXLRangeRow Members @@ -185,7 +183,7 @@ return Worksheet.Row(RangeAddress.FirstAddress.RowNumber); } - #endregion + #endregion IXLRangeRow Members private void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) { @@ -247,16 +245,33 @@ { if (thisCell.DataType == otherCell.DataType) { - if (thisCell.DataType == XLDataType.Text) + switch (thisCell.DataType) { - comparison = e.MatchCase - ? thisCell.InnerText.CompareTo(otherCell.InnerText) - : String.Compare(thisCell.InnerText, otherCell.InnerText, true); + case XLDataType.Text: + comparison = e.MatchCase + ? thisCell.InnerText.CompareTo(otherCell.InnerText) + : String.Compare(thisCell.InnerText, otherCell.InnerText, true); + break; + + case XLDataType.TimeSpan: + comparison = thisCell.GetTimeSpan().CompareTo(otherCell.GetTimeSpan()); + break; + + case XLDataType.DateTime: + comparison = thisCell.GetDateTime().CompareTo(otherCell.GetDateTime()); + break; + + case XLDataType.Number: + comparison = thisCell.GetDouble().CompareTo(otherCell.GetDouble()); + break; + + case XLDataType.Boolean: + comparison = thisCell.GetBoolean().CompareTo(otherCell.GetBoolean()); + break; + + default: + throw new NotImplementedException(); } - else if (thisCell.DataType == XLDataType.TimeSpan) - comparison = thisCell.GetTimeSpan().CompareTo(otherCell.GetTimeSpan()); - else - comparison = Double.Parse(thisCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture).CompareTo(Double.Parse(otherCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture)); } else if (e.MatchCase) comparison = String.Compare(thisCell.GetString(), otherCell.GetString(), true); @@ -265,7 +280,7 @@ } if (comparison != 0) - return e.SortOrder == XLSortOrder.Ascending ? comparison : comparison * -1; + return e.SortOrder == XLSortOrder.Ascending ? comparison : -comparison; } return 0; @@ -280,7 +295,7 @@ RangeAddress.FirstAddress.ColumnNumber, rowNum, RangeAddress.LastAddress.ColumnNumber); - + var result = range.FirstRow(); range.Dispose(); @@ -309,7 +324,7 @@ return RowShift(step * -1); } - #endregion + #endregion XLRangeRow Above #region XLRangeRow Below @@ -333,7 +348,7 @@ return RowShift(step); } - #endregion + #endregion XLRangeRow Below public new IXLRangeRow Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) { @@ -345,6 +360,5 @@ { return Row(FirstCellUsed(includeFormats), LastCellUsed(includeFormats)); } - } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/Excel/Ranges/XLRanges.cs index 7942134..501a50c 100644 --- a/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/Excel/Ranges/XLRanges.cs @@ -82,31 +82,9 @@ return _ranges.Any(r => !r.RangeAddress.IsInvalid && r.Contains(range)); } - public IXLDataValidation DataValidation + public IEnumerable DataValidation { - get - { - foreach (XLRange range in _ranges) - { - foreach (IXLDataValidation dv in range.Worksheet.DataValidations) - { - foreach (IXLRange dvRange in dv.Ranges.Where(dvRange => dvRange.Intersects(range))) - { - dv.Ranges.Remove(dvRange); - foreach (IXLCell c in dvRange.Cells().Where(c => !range.Contains(c.Address.ToString()))) - { - var r = c.AsRange(); - r.Dispose(); - dv.Ranges.Add(r); - } - } - } - } - var dataValidation = new XLDataValidation(this); - - _ranges.First().Worksheet.DataValidations.Add(dataValidation); - return dataValidation; - } + get { return _ranges.Select(range => range.DataValidation).Where(dv => dv != null); } } public IXLRanges AddToNamed(String rangeName) @@ -234,7 +212,26 @@ public IXLDataValidation SetDataValidation() { - return DataValidation; + foreach (XLRange range in _ranges) + { + foreach (IXLDataValidation dv in range.Worksheet.DataValidations) + { + foreach (IXLRange dvRange in dv.Ranges.Where(dvRange => dvRange.Intersects(range))) + { + dv.Ranges.Remove(dvRange); + foreach (IXLCell c in dvRange.Cells().Where(c => !range.Contains(c.Address.ToString()))) + { + var r = c.AsRange(); + r.Dispose(); + dv.Ranges.Add(r); + } + } + } + } + var dataValidation = new XLDataValidation(this); + + _ranges.First().Worksheet.DataValidations.Add(dataValidation); + return dataValidation; } public void Select() @@ -243,4 +240,4 @@ range.Select(); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/Excel/Tables/XLTable.cs index 019cf5d..cd8afe7 100644 --- a/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/Excel/Tables/XLTable.cs @@ -27,8 +27,8 @@ Int32 id = 1; while (true) { - string tableName = String.Format("Table{0}", id); - if (!Worksheet.Tables.Any(t => t.Name == tableName)) + string tableName = String.Concat("Table", id); + if (Worksheet.Tables.All(t => t.Name != tableName)) { Name = tableName; AddToTables(range, addToTables); @@ -119,7 +119,7 @@ Int32 colCount = ColumnCount(); for (Int32 i = 1; i <= colCount; i++) { - if (!_fieldNames.Values.Any(f => f.Index == i - 1)) + if (_fieldNames.Values.All(f => f.Index != i - 1)) { var name = "Column" + i; @@ -501,19 +501,21 @@ else { coString = coPairTrimmed; - order = "ASC"; + order = sortOrder == XLSortOrder.Ascending ? "ASC" : "DESC"; } Int32 co; if (!Int32.TryParse(coString, out co)) co = Field(coString).Index + 1; + if (toSortBy.Length > 0) + toSortBy.Append(','); + toSortBy.Append(co); - toSortBy.Append(" "); + toSortBy.Append(' '); toSortBy.Append(order); - toSortBy.Append(","); } - return DataRange.Sort(toSortBy.ToString(0, toSortBy.Length - 1), sortOrder, matchCase, ignoreBlanks); + return DataRange.Sort(toSortBy.ToString(), sortOrder, matchCase, ignoreBlanks); } public new IXLTable Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) diff --git a/ClosedXML/Excel/Tables/XLTables.cs b/ClosedXML/Excel/Tables/XLTables.cs index 485bb7c..76e9185 100644 --- a/ClosedXML/Excel/Tables/XLTables.cs +++ b/ClosedXML/Excel/Tables/XLTables.cs @@ -6,9 +6,16 @@ { using System.Collections; - public class XLTables : IXLTables + internal class XLTables : IXLTables { - private readonly Dictionary _tables = new Dictionary(); + private readonly Dictionary _tables; + internal ICollection Deleted { get; private set; } + + public XLTables() + { + _tables = new Dictionary(); + Deleted = new HashSet(); + } #region IXLTables Members @@ -37,7 +44,7 @@ return _tables[name]; } - #endregion + #endregion IXLTables Members public IXLTables Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) { @@ -47,11 +54,18 @@ public void Remove(Int32 index) { - _tables.Remove(_tables.ElementAt(index).Key); + this.Remove(_tables.ElementAt(index).Key); } + public void Remove(String name) { + if (!_tables.ContainsKey(name)) + throw new ArgumentOutOfRangeException(nameof(name), $"Unable to delete table because the table name {name} could not be found."); + + var table = _tables[name] as XLTable; _tables.Remove(name); + + if (table.RelId != null) Deleted.Add(table.RelId); } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/XLWSContentManager.cs b/ClosedXML/Excel/XLWSContentManager.cs index f871a62..0f67b92 100644 --- a/ClosedXML/Excel/XLWSContentManager.cs +++ b/ClosedXML/Excel/XLWSContentManager.cs @@ -51,7 +51,7 @@ TableParts = 39, WorksheetExtensionList = 40 } - private Dictionary contents = new Dictionary(); + private readonly Dictionary contents = new Dictionary(); public XLWSContentManager(Worksheet opWorksheet) { diff --git a/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/Excel/XLWorkbook.cs index 941db42..26d518f 100644 --- a/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/Excel/XLWorkbook.cs @@ -545,7 +545,7 @@ private void checkForWorksheetsPresent() { - if (Worksheets.Count() == 0) + if (!Worksheets.Any()) throw new InvalidOperationException("Workbooks need at least one worksheet."); } diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index c39990e..096e459 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -18,7 +18,6 @@ namespace ClosedXML.Excel { using Ap; - using ClosedXML.Extensions; using Drawings; using Op; using System.Drawing; @@ -163,14 +162,14 @@ var sheets = dSpreadsheet.WorkbookPart.Workbook.Sheets; Int32 position = 0; - foreach (Sheet dSheet in sheets.OfType()) + foreach (var dSheet in sheets.OfType()) { position++; var sharedFormulasR1C1 = new Dictionary(); - var wsPart = dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id) as WorksheetPart; + var worksheetPart = dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id) as WorksheetPart; - if (wsPart == null) + if (worksheetPart == null) { UnsupportedSheets.Add(new UnsupportedSheet { SheetId = dSheet.SheetId.Value, Position = position }); continue; @@ -188,7 +187,7 @@ var styleList = new Dictionary();// {{0, ws.Style}}; PageSetupProperties pageSetupProperties = null; - using (var reader = OpenXmlReader.Create(wsPart)) + using (var reader = OpenXmlReader.Create(worksheetPart)) { Type[] ignoredElements = new Type[] { @@ -246,7 +245,7 @@ else if (reader.ElementType == typeof(ConditionalFormatting)) LoadConditionalFormatting((ConditionalFormatting)reader.LoadCurrentElement(), ws, differentialFormats); else if (reader.ElementType == typeof(Hyperlinks)) - LoadHyperlinks((Hyperlinks)reader.LoadCurrentElement(), wsPart, ws); + LoadHyperlinks((Hyperlinks)reader.LoadCurrentElement(), worksheetPart, ws); else if (reader.ElementType == typeof(PrintOptions)) LoadPrintOptions((PrintOptions)reader.LoadCurrentElement(), ws); else if (reader.ElementType == typeof(PageMargins)) @@ -269,17 +268,21 @@ reader.Close(); } -#region LoadTables + #region LoadTables - foreach (var tablePart in wsPart.TableDefinitionParts) + foreach (var tableDefinitionPart in worksheetPart.TableDefinitionParts) { - var dTable = tablePart.Table; + var relId = worksheetPart.GetIdOfPart(tableDefinitionPart); + var dTable = tableDefinitionPart.Table; + String reference = dTable.Reference.Value; String tableName = dTable?.Name ?? dTable.DisplayName ?? string.Empty; if (XLHelper.IsNullOrWhiteSpace(tableName)) throw new InvalidDataException("The table name is missing."); - XLTable xlTable = ws.Range(reference).CreateTable(tableName, false) as XLTable; + var xlTable = ws.Range(reference).CreateTable(tableName, false) as XLTable; + xlTable.RelId = relId; + if (dTable.HeaderRowCount != null && dTable.HeaderRowCount == 0) { xlTable._showHeaderRow = false; @@ -349,20 +352,20 @@ xlTable.AutoFilter.Range = xlTable.Worksheet.Range(xlTable.RangeAddress); } -#endregion + #endregion LoadTables - LoadDrawings(wsPart, ws); + LoadDrawings(worksheetPart, ws); -#region LoadComments + #region LoadComments - if (wsPart.WorksheetCommentsPart != null) + if (worksheetPart.WorksheetCommentsPart != null) { - var root = wsPart.WorksheetCommentsPart.Comments; + var root = worksheetPart.WorksheetCommentsPart.Comments; var authors = root.GetFirstChild().ChildElements; var comments = root.GetFirstChild().ChildElements; // **** MAYBE FUTURE SHAPE SIZE SUPPORT - XDocument xdoc = GetCommentVmlFile(wsPart); + XDocument xdoc = GetCommentVmlFile(worksheetPart); foreach (Comment c in comments) { @@ -405,7 +408,7 @@ } } -#endregion + #endregion LoadComments } var workbook = dSpreadsheet.WorkbookPart.Workbook; @@ -428,18 +431,18 @@ } LoadDefinedNames(workbook); -#region Pivot tables + #region Pivot tables // Delay loading of pivot tables until all sheets have been loaded - foreach (Sheet dSheet in sheets.OfType()) + foreach (var dSheet in sheets.OfType()) { - var wsPart = dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id) as WorksheetPart; + var worksheetPart = dSpreadsheet.WorkbookPart.GetPartById(dSheet.Id) as WorksheetPart; - if (wsPart != null) + if (worksheetPart != null) { var ws = (XLWorksheet)WorksheetsInternal.Worksheet(dSheet.Name); - foreach (var pivotTablePart in wsPart.PivotTableParts) + foreach (var pivotTablePart in worksheetPart.PivotTableParts) { var pivotTableCacheDefinitionPart = pivotTablePart.PivotTableCacheDefinitionPart; var pivotTableDefinition = pivotTablePart.PivotTableDefinition; @@ -482,7 +485,7 @@ if (!XLHelper.IsNullOrWhiteSpace(StringValue.ToString(pivotTableDefinition?.RowHeaderCaption ?? String.Empty))) pt.SetRowHeaderCaption(StringValue.ToString(pivotTableDefinition.RowHeaderCaption)); - pt.RelId = wsPart.GetIdOfPart(pivotTablePart); + pt.RelId = worksheetPart.GetIdOfPart(pivotTablePart); pt.CacheDefinitionRelId = pivotTablePart.GetIdOfPart(pivotTableCacheDefinitionPart); pt.WorkbookCacheRelId = dSpreadsheet.WorkbookPart.GetIdOfPart(pivotTableCacheDefinitionPart); @@ -538,7 +541,7 @@ var pivotTableStyle = pivotTableDefinition.GetFirstChild(); if (pivotTableStyle != null) { - pt.Theme = (XLPivotTableTheme) Enum.Parse(typeof(XLPivotTableTheme), pivotTableStyle.Name); + pt.Theme = (XLPivotTableTheme)Enum.Parse(typeof(XLPivotTableTheme), pivotTableStyle.Name); pt.ShowRowHeaders = pivotTableStyle.ShowRowHeaders; pt.ShowColumnHeaders = pivotTableStyle.ShowColumnHeaders; pt.ShowRowStripes = pivotTableStyle.ShowRowStripes; @@ -570,50 +573,27 @@ continue; var cacheField = pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheFields.ElementAt(rf.Index.Value) as CacheField; - if (pf.Name != null) - pivotField = pt.RowLabels.Add(pf.Name.Value); - else if (cacheField.Name != null) - pivotField = pt.RowLabels.Add(cacheField.Name.Value); + if (cacheField.Name != null) + pivotField = pf.Name != null + ? pt.RowLabels.Add(cacheField.Name, pf.Name.Value) + : pt.RowLabels.Add(cacheField.Name.Value); else continue; if (pivotField != null) { - if (pf.AverageSubTotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Average); - if (pf.CountASubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Count); - if (pf.CountSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.CountNumbers); - if (pf.MaxSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Maximum); - if (pf.MinSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Minimum); - if (pf.ApplyStandardDeviationPInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.PopulationStandardDeviation); - if (pf.ApplyVariancePInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.PopulationVariance); - if (pf.ApplyProductInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Product); - if (pf.ApplyStandardDeviationInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.StandardDeviation); - if (pf.SumSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Sum); - if (pf.ApplyVarianceInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Variance); - var items = pf.Items.OfType().Where(i => i.Index != null && i.Index.HasValue); - if (!items.Any(i => i.HideDetails == null || BooleanValue.ToBoolean(i.HideDetails))) - pivotField.SetCollapsed(); + LoadFieldOptions(pf, pivotField); + LoadSubtotals(pf, pivotField); if (pf.SortType != null) { pivotField.SetSort((XLPivotSortType)pf.SortType.Value); + } } } } } } - } // Column labels if (pivotTableDefinition.ColumnFields != null) @@ -630,49 +610,26 @@ continue; var cacheField = pivotTableCacheDefinitionPart.PivotCacheDefinition.CacheFields.ElementAt(cf.Index.Value) as CacheField; - if (pf.Name != null) - pivotField = pt.ColumnLabels.Add(pf.Name.Value); - else if (cacheField.Name != null) - pivotField = pt.ColumnLabels.Add(cacheField.Name.Value); + if (cacheField.Name != null) + pivotField = pf.Name != null + ? pt.ColumnLabels.Add(cacheField.Name, pf.Name.Value) + : pt.ColumnLabels.Add(cacheField.Name.Value); else continue; if (pivotField != null) { - if (pf.AverageSubTotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Average); - if (pf.CountASubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Count); - if (pf.CountSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.CountNumbers); - if (pf.MaxSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Maximum); - if (pf.MinSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Minimum); - if (pf.ApplyStandardDeviationPInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.PopulationStandardDeviation); - if (pf.ApplyVariancePInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.PopulationVariance); - if (pf.ApplyProductInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Product); - if (pf.ApplyStandardDeviationInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.StandardDeviation); - if (pf.SumSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Sum); - if (pf.ApplyVarianceInSubtotal != null) - pivotField.AddSubtotal(XLSubtotalFunction.Variance); - var items = pf.Items.OfType().Where(i => i.Index != null && i.Index.HasValue); - if (!items.Any(i => i.HideDetails == null || BooleanValue.ToBoolean(i.HideDetails))) - pivotField.SetCollapsed(); + LoadFieldOptions(pf, pivotField); + LoadSubtotals(pf, pivotField); if (pf.SortType != null) { pivotField.SetSort((XLPivotSortType)pf.SortType.Value); + } } } } } - } // Values if (pivotTableDefinition.DataFields != null) @@ -793,7 +750,54 @@ } } -#endregion + #endregion Pivot tables + } + + private static void LoadFieldOptions(PivotField pf, IXLPivotField pivotField) + { + if (pf.SubtotalCaption != null) pivotField.SubtotalCaption = pf.SubtotalCaption; + if (pf.IncludeNewItemsInFilter != null) pivotField.IncludeNewItemsInFilter = pf.IncludeNewItemsInFilter.Value; + if (pf.Outline != null) pivotField.Outline = pf.Outline.Value; + if (pf.Compact != null) pivotField.Compact = pf.Compact.Value; + if (pf.InsertBlankRow != null) pivotField.InsertBlankLines = pf.InsertBlankRow.Value; + if (pf.ShowAll != null) pivotField.ShowBlankItems = pf.ShowAll.Value; + if (pf.InsertPageBreak != null) pivotField.InsertPageBreaks = pf.InsertPageBreak.Value; + if (pf.SubtotalTop != null) pivotField.SubtotalsAtTop = pf.SubtotalTop.Value; + if (pf.AllDrilled != null) pivotField.Collapsed = !pf.AllDrilled.Value; + + var pivotFieldExtensionList = pf.GetFirstChild(); + var pivotFieldExtension = pivotFieldExtensionList?.GetFirstChild(); + var field2010 = pivotFieldExtension?.GetFirstChild(); + if (field2010?.FillDownLabels != null) pivotField.RepeatItemLabels = field2010.FillDownLabels.Value; + } + + private static void LoadSubtotals(PivotField pf, IXLPivotField pivotField) + { + if (pf.AverageSubTotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Average); + if (pf.CountASubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Count); + if (pf.CountSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.CountNumbers); + if (pf.MaxSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Maximum); + if (pf.MinSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Minimum); + if (pf.ApplyStandardDeviationPInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.PopulationStandardDeviation); + if (pf.ApplyVariancePInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.PopulationVariance); + if (pf.ApplyProductInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Product); + if (pf.ApplyStandardDeviationInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.StandardDeviation); + if (pf.SumSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Sum); + if (pf.ApplyVarianceInSubtotal != null) + pivotField.AddSubtotal(XLSubtotalFunction.Variance); + var items = pf.Items.OfType().Where(i => i.Index != null && i.Index.HasValue); + if (!items.Any(i => i.HideDetails == null || BooleanValue.ToBoolean(i.HideDetails))) + pivotField.SetCollapsed(); } private void LoadDrawings(WorksheetPart wsPart, IXLWorksheet ws) @@ -823,10 +827,10 @@ picture.Placement = XLPicturePlacement.FreeFloating; if (spPr?.Transform2D?.Extents?.Cx.HasValue ?? false) - picture.Width = ConvertFromEnglishMetricUnits(spPr.Transform2D.Extents.Cx, GraphicsUtils.Graphics.DpiX); + picture.Width = ConvertFromEnglishMetricUnits(spPr.Transform2D.Extents.Cx, GraphicsUtils.Graphics.DpiX); if (spPr?.Transform2D?.Extents?.Cy.HasValue ?? false) - picture.Height = ConvertFromEnglishMetricUnits(spPr.Transform2D.Extents.Cy, GraphicsUtils.Graphics.DpiY); + picture.Height = ConvertFromEnglishMetricUnits(spPr.Transform2D.Extents.Cy, GraphicsUtils.Graphics.DpiY); if (anchor is Xdr.AbsoluteAnchor) { @@ -880,8 +884,8 @@ private static IXLMarker LoadMarker(IXLWorksheet ws, Xdr.MarkerType marker) { - var row = Math.Max(1, Convert.ToInt32(marker.RowId.InnerText) + 1); - var column = Math.Min(XLHelper.MaxColumnNumber, Convert.ToInt32(marker.ColumnId.InnerText) + 1); + var row = Math.Min(XLHelper.MaxRowNumber, Math.Max(1, Convert.ToInt32(marker.RowId.InnerText) + 1)); + var column = Math.Min(XLHelper.MaxColumnNumber, Math.Max(1, Convert.ToInt32(marker.ColumnId.InnerText) + 1)); return new XLMarker( ws.Cell(row, column).Address, new Point( @@ -891,7 +895,7 @@ ); } -#region Comment Helpers + #region Comment Helpers private XDocument GetCommentVmlFile(WorksheetPart wsPart) { @@ -928,7 +932,7 @@ return shape; } -#endregion + #endregion Comment Helpers private String GetTableColumnName(string name) { @@ -1238,12 +1242,12 @@ var comment = definedName.Comment; if (localSheetId == null) { - if (!NamedRanges.Any(nr => nr.Name == name)) + if (NamedRanges.All(nr => nr.Name != name)) (NamedRanges as XLNamedRanges).Add(name, text, comment, true).Visible = visible; } else { - if (!Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges.Any(nr => nr.Name == name)) + if (Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges.All(nr => nr.Name != name)) (Worksheet(Int32.Parse(localSheetId) + 1).NamedRanges as XLNamedRanges).Add(name, text, comment, true).Visible = visible; } } @@ -1281,7 +1285,7 @@ foreach (var item in areas) { if (this.Range(item) != null) - SetColumnsOrRowsToRepeat(item); + SetColumnsOrRowsToRepeat(item); } } @@ -1320,8 +1324,8 @@ else { sheetName = sections[0].UnescapeSheetName(); - sheetArea = sections[1]; - } + sheetArea = sections[1]; + } } private Int32 lastCell; @@ -1436,7 +1440,7 @@ else if (cell.DataType == CellValues.Boolean) { if (cell.CellValue != null) - xlCell._cellValue = cell.CellValue.Text; + xlCell._cellValue = cell.CellValue.Text; xlCell._dataType = XLDataType.Boolean; } else if (cell.DataType == CellValues.Number) @@ -1483,7 +1487,7 @@ // Internally ClosedXML stores cells as standard 1900-based style // so if a workbook is in 1904-format, we do that adjustment here and when saving. xlCell.SetValue(xlCell.GetDateTime().AddDays(1462)); - } + } } /// @@ -1517,7 +1521,7 @@ if (!hasRuns) xlCell._cellValue = XmlEncoder.DecodeString(element.Text.InnerText); -#region Load PhoneticProperties + #region Load PhoneticProperties var pp = phoneticProperties.FirstOrDefault(); if (pp != null) @@ -1530,9 +1534,9 @@ LoadFont(pp, xlCell.RichText.Phonetics); } -#endregion + #endregion Load PhoneticProperties -#region Load Phonetic Runs + #region Load Phonetic Runs foreach (PhoneticRun pr in phoneticRuns) { @@ -1540,7 +1544,7 @@ (Int32)pr.EndingBaseIndex.Value); } -#endregion + #endregion Load Phonetic Runs } private void LoadNumberFormat(NumberingFormat nfSource, IXLNumberFormat nf) @@ -1551,7 +1555,6 @@ nf.NumberFormatId = (Int32)nfSource.NumberFormatId.Value; else if (nfSource.FormatCode != null) nf.Format = nfSource.FormatCode.Value; - } private void LoadBorder(Border borderSource, IXLBorder border) @@ -1720,7 +1723,7 @@ if (columns == null) return; var wsDefaultColumn = - columns.Elements().Where(c => c.Max == XLHelper.MaxColumnNumber).FirstOrDefault(); + columns.Elements().FirstOrDefault(c => c.Max == XLHelper.MaxColumnNumber); if (wsDefaultColumn != null && wsDefaultColumn.Width != null) ws.ColumnWidth = wsDefaultColumn.Width - ColumnWidthOffset; @@ -1833,7 +1836,7 @@ XLConnector connector = filterColumn.CustomFilters.And != null && filterColumn.CustomFilters.And.Value ? XLConnector.And : XLConnector.Or; Boolean isText = false; - foreach (CustomFilter filter in filterColumn.CustomFilters) + foreach (var filter in filterColumn.CustomFilters.OfType()) { Double dTest; String val = filter.Val.Value; @@ -1844,7 +1847,7 @@ } } - foreach (CustomFilter filter in filterColumn.CustomFilters) + foreach (var filter in filterColumn.CustomFilters.OfType()) { var xlFilter = new XLFilter { Value = filter.Val.Value, Connector = connector }; if (isText) @@ -1885,12 +1888,19 @@ } else if (filterColumn.Filters != null) { + if (filterColumn.Filters.Elements().All(element => element is Filter)) + autoFilter.Column(column).FilterType = XLFilterType.Regular; + else if (filterColumn.Filters.Elements().All(element => element is DateGroupItem)) + autoFilter.Column(column).FilterType = XLFilterType.DateTimeGrouping; + else + throw new NotSupportedException(String.Format("Mixing regular filters and date group filters in a single autofilter column is not supported. Column {0} of {1}", column, autoFilter.Range.ToString())); + var filterList = new List(); - autoFilter.Column(column).FilterType = XLFilterType.Regular; + autoFilter.Filters.Add((int)filterColumn.ColumnId.Value + 1, filterList); Boolean isText = false; - foreach (Filter filter in filterColumn.Filters.OfType()) + foreach (var filter in filterColumn.Filters.OfType()) { Double dTest; String val = filter.Val.Value; @@ -1901,7 +1911,7 @@ } } - foreach (Filter filter in filterColumn.Filters.OfType()) + foreach (var filter in filterColumn.Filters.OfType()) { var xlFilter = new XLFilter { Connector = XLConnector.Or, Operator = XLFilterOperator.Equal }; @@ -1920,6 +1930,84 @@ xlFilter.Condition = condition; filterList.Add(xlFilter); } + + foreach (var dateGroupItem in filterColumn.Filters.OfType()) + { + bool valid = true; + + if (!(dateGroupItem.DateTimeGrouping?.HasValue ?? false)) + continue; + + var xlDateGroupFilter = new XLFilter + { + Connector = XLConnector.Or, + Operator = XLFilterOperator.Equal, + DateTimeGrouping = dateGroupItem.DateTimeGrouping?.Value.ToClosedXml() ?? XLDateTimeGrouping.Year + }; + + int year = 1900; + int month = 1; + int day = 1; + int hour = 0; + int minute = 0; + int second = 0; + + if (xlDateGroupFilter.DateTimeGrouping >= XLDateTimeGrouping.Year) + { + if (dateGroupItem?.Year?.HasValue ?? false) + year = (int)dateGroupItem.Year?.Value; + else + valid &= false; + } + + if (xlDateGroupFilter.DateTimeGrouping >= XLDateTimeGrouping.Month) + { + if (dateGroupItem?.Month?.HasValue ?? false) + month = (int)dateGroupItem.Month?.Value; + else + valid &= false; + } + + if (xlDateGroupFilter.DateTimeGrouping >= XLDateTimeGrouping.Day) + { + if (dateGroupItem?.Day?.HasValue ?? false) + day = (int)dateGroupItem.Day?.Value; + else + valid &= false; + } + + if (xlDateGroupFilter.DateTimeGrouping >= XLDateTimeGrouping.Hour) + { + if (dateGroupItem?.Hour?.HasValue ?? false) + hour = (int)dateGroupItem.Hour?.Value; + else + valid &= false; + } + + if (xlDateGroupFilter.DateTimeGrouping >= XLDateTimeGrouping.Minute) + { + if (dateGroupItem?.Minute?.HasValue ?? false) + minute = (int)dateGroupItem.Minute?.Value; + else + valid &= false; + } + + if (xlDateGroupFilter.DateTimeGrouping >= XLDateTimeGrouping.Second) + { + if (dateGroupItem?.Second?.HasValue ?? false) + second = (int)dateGroupItem.Second?.Value; + else + valid &= false; + } + + var date = new DateTime(year, month, day, hour, minute, second); + xlDateGroupFilter.Value = date; + + xlDateGroupFilter.Condition = date2 => XLDateTimeGroupFilteredColumn.IsMatch(date, (DateTime)date2, xlDateGroupFilter.DateTimeGrouping); + + if (valid) + filterList.Add(xlDateGroupFilter); + } } else if (filterColumn.Top10 != null) { @@ -1999,21 +2087,25 @@ { String txt = dvs.SequenceOfReferences.InnerText; if (XLHelper.IsNullOrWhiteSpace(txt)) continue; - foreach (var dvt in txt.Split(' ').Select(rangeAddress => ws.Range(rangeAddress).DataValidation)) + foreach (var rangeAddress in txt.Split(' ')) { - if (dvs.AllowBlank != null) dvt.IgnoreBlanks = dvs.AllowBlank; - if (dvs.ShowDropDown != null) dvt.InCellDropdown = !dvs.ShowDropDown.Value; - if (dvs.ShowErrorMessage != null) dvt.ShowErrorMessage = dvs.ShowErrorMessage; - if (dvs.ShowInputMessage != null) dvt.ShowInputMessage = dvs.ShowInputMessage; - if (dvs.PromptTitle != null) dvt.InputTitle = dvs.PromptTitle; - if (dvs.Prompt != null) dvt.InputMessage = dvs.Prompt; - if (dvs.ErrorTitle != null) dvt.ErrorTitle = dvs.ErrorTitle; - if (dvs.Error != null) dvt.ErrorMessage = dvs.Error; - if (dvs.ErrorStyle != null) dvt.ErrorStyle = dvs.ErrorStyle.Value.ToClosedXml(); - if (dvs.Type != null) dvt.AllowedValues = dvs.Type.Value.ToClosedXml(); - if (dvs.Operator != null) dvt.Operator = dvs.Operator.Value.ToClosedXml(); - if (dvs.Formula1 != null) dvt.MinValue = dvs.Formula1.Text; - if (dvs.Formula2 != null) dvt.MaxValue = dvs.Formula2.Text; + using (var range = ws.Range(rangeAddress)) + { + var dvt = range.SetDataValidation(); + if (dvs.AllowBlank != null) dvt.IgnoreBlanks = dvs.AllowBlank; + if (dvs.ShowDropDown != null) dvt.InCellDropdown = !dvs.ShowDropDown.Value; + if (dvs.ShowErrorMessage != null) dvt.ShowErrorMessage = dvs.ShowErrorMessage; + if (dvs.ShowInputMessage != null) dvt.ShowInputMessage = dvs.ShowInputMessage; + if (dvs.PromptTitle != null) dvt.InputTitle = dvs.PromptTitle; + if (dvs.Prompt != null) dvt.InputMessage = dvs.Prompt; + if (dvs.ErrorTitle != null) dvt.ErrorTitle = dvs.ErrorTitle; + if (dvs.Error != null) dvt.ErrorMessage = dvs.Error; + if (dvs.ErrorStyle != null) dvt.ErrorStyle = dvs.ErrorStyle.Value.ToClosedXml(); + if (dvs.Type != null) dvt.AllowedValues = dvs.Type.Value.ToClosedXml(); + if (dvs.Operator != null) dvt.Operator = dvs.Operator.Value.ToClosedXml(); + if (dvs.Formula1 != null) dvt.MinValue = dvs.Formula1.Text; + if (dvs.Formula2 != null) dvt.MaxValue = dvs.Formula2.Text; + } } } } diff --git a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs index 35f43af..e861ae9 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.NestedTypes.cs @@ -73,7 +73,7 @@ Int32 id = _relIds[relType].Count + 1; while (true) { - String relId = String.Format("rId{0}", id); + String relId = String.Concat("rId", id); if (!_relIds[relType].Contains(relId)) { _relIds[relType].Add(relId); diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index bdb6320..e897701 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -263,15 +263,10 @@ foreach (var worksheet in WorksheetsInternal.Cast().OrderBy(w => w.Position)) { - //context.RelIdGenerator.Reset(RelType.); WorksheetPart worksheetPart; var wsRelId = worksheet.RelId; if (workbookPart.Parts.Any(p => p.RelationshipId == wsRelId)) - { worksheetPart = (WorksheetPart)workbookPart.GetPartById(wsRelId); - var wsPartsToRemove = worksheetPart.TableDefinitionParts.ToList(); - wsPartsToRemove.ForEach(tdp => worksheetPart.DeletePart(tdp)); - } else worksheetPart = workbookPart.AddNewPart(wsRelId); @@ -314,10 +309,10 @@ } // Remove any orphaned references - maybe more types? - foreach (var orphan in worksheetPart.Worksheet.OfType().Where(lg => !worksheetPart.Parts.Any(p => p.RelationshipId == lg.Id))) + foreach (var orphan in worksheetPart.Worksheet.OfType().Where(lg => worksheetPart.Parts.All(p => p.RelationshipId != lg.Id))) worksheetPart.Worksheet.RemoveChild(orphan); - foreach (var orphan in worksheetPart.Worksheet.OfType().Where(d => !worksheetPart.Parts.Any(p => p.RelationshipId == d.Id))) + foreach (var orphan in worksheetPart.Worksheet.OfType().Where(d => worksheetPart.Parts.All(p => p.RelationshipId != d.Id))) worksheetPart.Worksheet.RemoveChild(orphan); } @@ -398,22 +393,60 @@ } } - private static void GenerateTables(XLWorksheet worksheet, WorksheetPart worksheetPart, SaveContext context) + private static void GenerateTables(XLWorksheet worksheet, WorksheetPart worksheetPart, SaveContext context, XLWSContentManager cm) { - worksheetPart.Worksheet.RemoveAllChildren(); + var tables = worksheet.Tables as XLTables; - if (!worksheet.Tables.Any()) return; - - foreach (var table in worksheet.Tables) + TableParts tableParts; + if (worksheetPart.Worksheet.Elements().Any()) { - var tableRelId = context.RelIdGenerator.GetNext(RelType.Workbook); - - var xlTable = (XLTable)table; - xlTable.RelId = tableRelId; - - var tableDefinitionPart = worksheetPart.AddNewPart(tableRelId); - GenerateTableDefinitionPartContent(tableDefinitionPart, xlTable, context); + tableParts = worksheetPart.Worksheet.Elements().First(); } + else + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.TableParts); + tableParts = new TableParts(); + worksheetPart.Worksheet.InsertAfter(tableParts, previousElement); + } + cm.SetElement(XLWSContentManager.XLWSContents.TableParts, tableParts); + + foreach (var deletedTableRelId in tables.Deleted) + { + if (worksheetPart.TableDefinitionParts != null) + { + var tableDefinitionPart = worksheetPart.GetPartById(deletedTableRelId); + worksheetPart.DeletePart(tableDefinitionPart); + + var tablePartsToRemove = tableParts.OfType().Where(tp => tp.Id?.Value == deletedTableRelId).ToList(); + tablePartsToRemove.ForEach(tp => tableParts.RemoveChild(tp)); + } + } + + tables.Deleted.Clear(); + + foreach (var xlTable in worksheet.Tables.Cast()) + { + if (String.IsNullOrEmpty(xlTable.RelId)) + xlTable.RelId = context.RelIdGenerator.GetNext(RelType.Workbook); + + var relId = xlTable.RelId; + + TableDefinitionPart tableDefinitionPart; + if (worksheetPart.HasPartWithId(relId)) + tableDefinitionPart = worksheetPart.GetPartById(relId) as TableDefinitionPart; + else + tableDefinitionPart = worksheetPart.AddNewPart(relId); + + GenerateTableDefinitionPartContent(tableDefinitionPart, xlTable, context); + + if (!tableParts.OfType().Any(tp => tp.Id == xlTable.RelId)) + { + var tablePart = new TablePart { Id = xlTable.RelId }; + tableParts.AppendChild(tablePart); + } + } + + tableParts.Count = (UInt32)worksheet.Tables.Count(); } private void GenerateExtendedFilePropertiesPartContent(ExtendedFilePropertiesPart extendedFilePropertiesPart) @@ -630,14 +663,14 @@ { if (XLHelper.IsNullOrWhiteSpace(xlSheet.RelId)) { - rId = String.Format("rId{0}", xlSheet.SheetId); + rId = String.Concat("rId", xlSheet.SheetId); context.RelIdGenerator.AddValues(new List { rId }, RelType.Workbook); } else rId = xlSheet.RelId; } - if (!workbook.Sheets.Cast().Any(s => s.Id == rId)) + if (workbook.Sheets.Cast().All(s => s.Id != rId)) { var newSheet = new Sheet { @@ -1812,8 +1845,7 @@ return name; } - private static void GenerateTableDefinitionPartContent(TableDefinitionPart tableDefinitionPart, XLTable xlTable, - SaveContext context) + private static void GenerateTableDefinitionPartContent(TableDefinitionPart tableDefinitionPart, XLTable xlTable, SaveContext context) { context.TableId++; var reference = xlTable.RangeAddress.FirstAddress + ":" + xlTable.RangeAddress.LastAddress; @@ -2036,6 +2068,16 @@ { xlpf.CustomName = field.CustomName; xlpf.SortType = field.SortType; + xlpf.SubtotalCaption = field.SubtotalCaption; + xlpf.IncludeNewItemsInFilter = field.IncludeNewItemsInFilter; + xlpf.Outline = field.Outline; + xlpf.Compact = field.Compact; + xlpf.SubtotalsAtTop = field.SubtotalsAtTop; + xlpf.RepeatItemLabels = field.RepeatItemLabels; + xlpf.InsertBlankLines = field.InsertBlankLines; + xlpf.ShowBlankItems = field.ShowBlankItems; + xlpf.InsertPageBreaks = field.InsertPageBreaks; + xlpf.Collapsed = field.Collapsed; xlpf.Subtotals.AddRange(field.Subtotals); } @@ -2079,7 +2121,7 @@ sharedItems.AppendChild(new NumberItem { Val = (double)value }); if (containsBlank) sharedItems.AppendChild(new MissingItem()); - } + } sharedItems.MinValue = (double)ptfi.DistinctValues.Min(); sharedItems.MaxValue = (double)ptfi.DistinctValues.Max(); @@ -2116,7 +2158,7 @@ sharedItems.MaxDate = (DateTime)ptfi.DistinctValues.Max(); } else - { + { if (types.Any()) { ptfi.DataType = types.First(); @@ -2319,13 +2361,30 @@ { var ptfi = pti.Fields[xlpf.SourceName]; IXLPivotField labelOrFilterField = null; - var pf = new PivotField { ShowAll = false, Name = xlpf.CustomName }; + var pf = new PivotField + { + Name = xlpf.CustomName, + IncludeNewItemsInFilter = OpenXmlHelper.GetBooleanValue(xlpf.IncludeNewItemsInFilter, false), + InsertBlankRow = OpenXmlHelper.GetBooleanValue(xlpf.InsertBlankLines, false), + ShowAll = OpenXmlHelper.GetBooleanValue(xlpf.ShowBlankItems, true), + InsertPageBreak = OpenXmlHelper.GetBooleanValue(xlpf.InsertPageBreaks, false), + AllDrilled = OpenXmlHelper.GetBooleanValue(xlpf.Collapsed, false), + }; + if (!string.IsNullOrWhiteSpace(xlpf.SubtotalCaption)) + { + pf.SubtotalCaption = xlpf.SubtotalCaption; + } if (pt.ClassicPivotTableLayout) { pf.Outline = false; pf.Compact = false; } + else + { + pf.Outline = OpenXmlHelper.GetBooleanValue(xlpf.Outline, true); + pf.Compact = OpenXmlHelper.GetBooleanValue(xlpf.Compact, true); + } if (xlpf.SortType != XLPivotSortType.Default) { @@ -2344,11 +2403,15 @@ break; case XLPivotSubtotals.AtTop: - pf.DefaultSubtotal = true; - pf.SubtotalTop = true; + // at top is by default break; } + if (xlpf.SubtotalsAtTop.HasValue) + { + pf.SubtotalTop = OpenXmlHelper.GetBooleanValue(xlpf.SubtotalsAtTop.Value, true); + } + if (pt.RowLabels.Contains(xlpf.SourceName)) { labelOrFilterField = pt.RowLabels.Get(xlpf.SourceName); @@ -2383,7 +2446,7 @@ var selectedValue = labelOrFilterField.SelectedValues.Single().ToString().ToLower(); if (values.Contains(selectedValue)) pageField.Item = Convert.ToUInt32(values.IndexOf(selectedValue)); - } + } else if (ptfi.DataType == XLDataType.DateTime) { var values = ptfi.DistinctValues @@ -2434,7 +2497,7 @@ if (labelOrFilterField.SelectedValues.Count > 1 && !labelOrFilterField.SelectedValues.Contains(value)) item.Hidden = BooleanValue.FromBoolean(true); - + fieldItems.AppendChild(item); i++; @@ -2517,6 +2580,26 @@ fieldItems.Count = Convert.ToUInt32(fieldItems.Count()); pf.AppendChild(fieldItems); } + + #region Excel 2010 Features + + if (xlpf.RepeatItemLabels) + { + var pivotFieldExtensionList = new PivotFieldExtensionList(); + pivotFieldExtensionList.RemoveNamespaceDeclaration("x"); + var pivotFieldExtension = new PivotFieldExtension { Uri = "{2946ED86-A175-432a-8AC1-64E0C546D7DE}" }; + pivotFieldExtension.AddNamespaceDeclaration("x14", "http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"); + + var pivotField2 = new DocumentFormat.OpenXml.Office2010.Excel.PivotField { FillDownLabels = true }; + + pivotFieldExtension.AppendChild(pivotField2); + + pivotFieldExtensionList.AppendChild(pivotFieldExtension); + pf.AppendChild(pivotFieldExtensionList); + } + + #endregion Excel 2010 Features + pivotFields.AppendChild(pf); } @@ -2539,7 +2622,7 @@ pivotTableDefinition.AppendChild(rowItems); } - if (!pt.ColumnLabels.Any(cl => cl.CustomName != XLConstants.PivotTableValuesSentinalLabel)) + if (pt.ColumnLabels.All(cl => cl.CustomName == XLConstants.PivotTableValuesSentinalLabel)) { for (int i = 0; i < pt.Values.Count(); i++) { @@ -2750,7 +2833,7 @@ var rowNumber = c.Address.RowNumber; var columnNumber = c.Address.ColumnNumber; - var shapeId = String.Format("_x0000_s{0}", c.Comment.ShapeId); + var shapeId = String.Concat("_x0000_s", c.Comment.ShapeId); // Unique per cell (workbook?), e.g.: "_x0000_s1026" var anchor = GetAnchor(c); var textBox = GetTextBox(c.Comment.Style); @@ -2791,7 +2874,7 @@ Style = GetCommentStyle(c), FillColor = "#" + c.Comment.Style.ColorsAndLines.FillColor.Color.ToHex().Substring(2), StrokeColor = "#" + c.Comment.Style.ColorsAndLines.LineColor.Color.ToHex().Substring(2), - StrokeWeight = String.Format(CultureInfo.InvariantCulture, "{0}pt", c.Comment.Style.ColorsAndLines.LineWeight), + StrokeWeight = String.Concat(c.Comment.Style.ColorsAndLines.LineWeight.ToInvariantString(), "pt"), InsetMode = c.Comment.Style.Margins.Automatic ? InsetMarginValues.Auto : InsetMarginValues.Custom }; if (!XLHelper.IsNullOrWhiteSpace(c.Comment.Style.Web.AlternateText)) @@ -2852,7 +2935,10 @@ if (drawingsPart.HasPartWithId(pic.RelId)) imagePart = drawingsPart.GetPartById(pic.RelId) as ImagePart; else - imagePart = drawingsPart.AddImagePart(pic.Format.ToOpenXml(), context.RelIdGenerator.GetNext(RelType.Workbook)); + { + pic.RelId = context.RelIdGenerator.GetNext(RelType.Workbook); + imagePart = drawingsPart.AddImagePart(pic.Format.ToOpenXml(), pic.RelId); + } using (var stream = new MemoryStream()) { @@ -3039,11 +3125,11 @@ var retVal = new Vml.TextBox { Style = sb.ToString() }; var dm = ds.Margins; if (!dm.Automatic) - retVal.Inset = String.Format("{0}in,{1}in,{2}in,{3}in", - dm.Left.ToInvariantString(), - dm.Top.ToInvariantString(), - dm.Right.ToInvariantString(), - dm.Bottom.ToInvariantString()); + retVal.Inset = String.Concat( + dm.Left.ToInvariantString(), "in,", + dm.Top.ToInvariantString(), "in,", + dm.Right.ToInvariantString(), "in,", + dm.Bottom.ToInvariantString(), "in"); return retVal; } @@ -3080,11 +3166,11 @@ var lrOffset = Convert.ToInt32(lastCell.WorksheetRow().Height - (heightFromRows - cHeight)); return new Anchor { - Text = string.Format("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", - fcNumber, fcOffset, - frNumber, frOffset, - lcNumber, lcOffset, - lrNumber, lrOffset + Text = string.Concat( + fcNumber, ", ", fcOffset, ", ", + frNumber, ", ", frOffset, ", ", + lcNumber, ", ", lcOffset, ", ", + lrNumber, ", ", lrOffset ) }; } @@ -3336,7 +3422,7 @@ if (!style.Equals(DefaultStyle) && !context.DifferentialFormats.ContainsKey(style)) AddStyleAsDifferentialFormat(workbookStylesPart.Stylesheet.DifferentialFormats, style, context); - } + } } } @@ -3545,7 +3631,7 @@ && f.NumberFormatId != null && styleInfo.NumberFormatId == f.NumberFormatId && f.ApplyFill != null && f.ApplyFill == ApplyFill(styleInfo) && f.ApplyBorder != null && f.ApplyBorder == ApplyBorder(styleInfo) - && AlignmentsAreEqual(f.Alignment, styleInfo.Style.Alignment) + && (f.Alignment == null || AlignmentsAreEqual(f.Alignment, styleInfo.Style.Alignment)) && ProtectionsAreEqual(f.Protection, styleInfo.Style.Protection) ; } @@ -3700,6 +3786,15 @@ if (b.DiagonalDown != null) nb.DiagonalDown = b.DiagonalDown.Value; + if (b.DiagonalBorder != null) + { + if (b.DiagonalBorder.Style != null) + nb.DiagonalBorder = b.DiagonalBorder.Style.Value.ToClosedXml(); + var bColor = GetColor(b.DiagonalBorder.Color); + if (bColor.HasValue) + nb.DiagonalBorderColor = bColor; + } + if (b.LeftBorder != null) { if (b.LeftBorder.Style != null) @@ -3794,7 +3889,7 @@ var patternFill = new PatternFill(); - patternFill.PatternType = fillInfo.Fill.PatternType.ToOpenXml(); + patternFill.PatternType = fillInfo.Fill.PatternType.ToOpenXml(); BackgroundColor backgroundColor; ForegroundColor foregroundColor; @@ -3863,27 +3958,27 @@ switch (fillInfo.Fill.PatternColor.ColorType) { case XLColorType.Color: - foregroundColor.Rgb = fillInfo.Fill.PatternColor.Color.ToHex(); + foregroundColor.Rgb = fillInfo.Fill.PatternColor.Color.ToHex(); break; case XLColorType.Indexed: - foregroundColor.Indexed = (UInt32)fillInfo.Fill.PatternColor.Indexed; + foregroundColor.Indexed = (UInt32)fillInfo.Fill.PatternColor.Indexed; break; case XLColorType.Theme: - foregroundColor.Theme = (UInt32)fillInfo.Fill.PatternColor.ThemeColor; + foregroundColor.Theme = (UInt32)fillInfo.Fill.PatternColor.ThemeColor; - if (fillInfo.Fill.PatternColor.ThemeTint != 0) - foregroundColor.Tint = fillInfo.Fill.PatternColor.ThemeTint; + if (fillInfo.Fill.PatternColor.ThemeTint != 0) + foregroundColor.Tint = fillInfo.Fill.PatternColor.ThemeTint; break; - } + } - patternFill.AppendChild(foregroundColor); + patternFill.AppendChild(foregroundColor); backgroundColor = new BackgroundColor(); switch (fillInfo.Fill.BackgroundColor.ColorType) - { + { case XLColorType.Color: backgroundColor.Rgb = fillInfo.Fill.BackgroundColor.Color.ToHex(); break; @@ -3899,9 +3994,9 @@ backgroundColor.Tint = fillInfo.Fill.BackgroundColor.ThemeTint; break; - } + } - patternFill.AppendChild(backgroundColor); + patternFill.AppendChild(backgroundColor); break; } @@ -4150,8 +4245,6 @@ if (worksheetPart.Worksheet == null) worksheetPart.Worksheet = new Worksheet(); - GenerateTables(xlWorksheet, worksheetPart, context); - if ( !worksheetPart.Worksheet.NamespaceDeclarations.Contains(new KeyValuePair("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"))) @@ -4319,24 +4412,24 @@ Action populateSelection = (Selection selection) => { - if (xlWorksheet.ActiveCell != null) - selection.ActiveCell = xlWorksheet.ActiveCell.Address.ToStringRelative(false); - else if (firstSelection != null) - selection.ActiveCell = firstSelection.RangeAddress.FirstAddress.ToStringRelative(false); + if (xlWorksheet.ActiveCell != null) + selection.ActiveCell = xlWorksheet.ActiveCell.Address.ToStringRelative(false); + else if (firstSelection != null) + selection.ActiveCell = firstSelection.RangeAddress.FirstAddress.ToStringRelative(false); - var seqRef = new List { selection.ActiveCell.Value }; - seqRef.AddRange(xlWorksheet.SelectedRanges - .Select(range => - { - if (range.RangeAddress.FirstAddress.Equals(range.RangeAddress.LastAddress)) - return range.RangeAddress.FirstAddress.ToStringRelative(false); - else - return range.RangeAddress.ToStringRelative(false); - })); + var seqRef = new List { selection.ActiveCell.Value }; + seqRef.AddRange(xlWorksheet.SelectedRanges + .Select(range => + { + if (range.RangeAddress.FirstAddress.Equals(range.RangeAddress.LastAddress)) + return range.RangeAddress.FirstAddress.ToStringRelative(false); + else + return range.RangeAddress.ToStringRelative(false); + })); - selection.SequenceOfReferences = new ListValue { InnerText = String.Join(" ", seqRef.Distinct().ToArray()) }; + selection.SequenceOfReferences = new ListValue { InnerText = String.Join(" ", seqRef.Distinct().ToArray()) }; - sheetView.Append(selection); + sheetView.Append(selection); }; populateSelection(new Selection()); @@ -4347,7 +4440,7 @@ { Pane = pane.ActivePane }); - } + } } if (xlWorksheet.SheetView.ZoomScale == 100) @@ -4569,6 +4662,13 @@ xlWorksheet.Internals.CellsCollection.deleted.Remove(r.Key); } + var tableTotalCells = new HashSet( + xlWorksheet.Tables + .Where(table => table.ShowTotalsRow) + .SelectMany(table => + table.TotalsRow().CellsUsed()) + .Select(cell => cell.Address)); + var distinctRows = xlWorksheet.Internals.CellsCollection.RowsCollection.Keys.Union(xlWorksheet.Internals.RowsCollection.Keys); var noRows = !sheetData.Elements().Any(); foreach (var distinctRow in distinctRows.OrderBy(r => r)) @@ -4714,7 +4814,7 @@ cell.CellValue = null; } - else if (xlCell.TableCellType() == XLTableCellType.Total) + else if (tableTotalCells.Contains(xlCell.Address)) { var table = xlWorksheet.Tables.First(t => t.AsRange().Contains(xlCell)); field = table.Fields.First(f => f.Column.ColumnNumber() == xlCell.Address.ColumnNumber) as XLTableField; @@ -4914,7 +5014,7 @@ } var exlst = from c in xlWorksheet.ConditionalFormats where c.ConditionalFormatType == XLConditionalFormatType.DataBar && c.Colors.Count > 1 && typeof(IXLConditionalFormat).IsAssignableFrom(c.GetType()) select c; - if (exlst != null && exlst.Count() > 0) + if (exlst != null && exlst.Any()) { if (!worksheetPart.Worksheet.Elements().Any()) { @@ -5212,13 +5312,13 @@ var rowBreakCount = xlWorksheet.PageSetup.RowBreaks.Count; if (rowBreakCount > 0) { - if (!worksheetPart.Worksheet.Elements().Any()) - { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.RowBreaks); - worksheetPart.Worksheet.InsertAfter(new RowBreaks(), previousElement); - } + if (!worksheetPart.Worksheet.Elements().Any()) + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.RowBreaks); + worksheetPart.Worksheet.InsertAfter(new RowBreaks(), previousElement); + } - var rowBreaks = worksheetPart.Worksheet.Elements().First(); + var rowBreaks = worksheetPart.Worksheet.Elements().First(); var existingBreaks = rowBreaks.ChildElements.OfType(); var rowBreaksToDelete = existingBreaks @@ -5227,7 +5327,7 @@ .ToList(); foreach (var rb in rowBreaksToDelete) - { + { rowBreaks.RemoveChild(rb); } @@ -5259,13 +5359,13 @@ var columnBreakCount = xlWorksheet.PageSetup.ColumnBreaks.Count; if (columnBreakCount > 0) { - if (!worksheetPart.Worksheet.Elements().Any()) - { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ColumnBreaks); - worksheetPart.Worksheet.InsertAfter(new ColumnBreaks(), previousElement); - } + if (!worksheetPart.Worksheet.Elements().Any()) + { + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.ColumnBreaks); + worksheetPart.Worksheet.InsertAfter(new ColumnBreaks(), previousElement); + } - var columnBreaks = worksheetPart.Worksheet.Elements().First(); + var columnBreaks = worksheetPart.Worksheet.Elements().First(); var existingBreaks = columnBreaks.ChildElements.OfType(); var columnBreaksToDelete = existingBreaks @@ -5274,7 +5374,7 @@ .ToList(); foreach (var rb in columnBreaksToDelete) - { + { columnBreaks.RemoveChild(rb); } @@ -5303,20 +5403,7 @@ #region Tables - worksheetPart.Worksheet.RemoveAllChildren(); - { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.TableParts); - worksheetPart.Worksheet.InsertAfter(new TableParts(), previousElement); - } - - var tableParts = worksheetPart.Worksheet.Elements().First(); - cm.SetElement(XLWSContentManager.XLWSContents.TableParts, tableParts); - - tableParts.Count = (UInt32)xlWorksheet.Tables.Count(); - foreach ( - var tablePart in - from XLTable xlTable in xlWorksheet.Tables select new TablePart { Id = xlTable.RelId }) - tableParts.AppendChild(tablePart); + GenerateTables(xlWorksheet, worksheetPart, context, cm); #endregion Tables @@ -5340,21 +5427,22 @@ if (xlWorksheet.Pictures.Any()) RebasePictureIds(worksheetPart); + var tableParts = worksheetPart.Worksheet.Elements().First(); if (xlWorksheet.Pictures.Any() && !worksheetPart.Worksheet.OfType().Any()) { var worksheetDrawing = new Drawing { Id = worksheetPart.GetIdOfPart(worksheetPart.DrawingsPart) }; worksheetDrawing.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships"); worksheetPart.Worksheet.InsertBefore(worksheetDrawing, tableParts); + cm.SetElement(XLWSContentManager.XLWSContents.Drawing, worksheetPart.Worksheet.Elements().First()); } if (!xlWorksheet.Pictures.Any() && worksheetPart.DrawingsPart != null) { var id = worksheetPart.GetIdOfPart(worksheetPart.DrawingsPart); - worksheetPart.Worksheet.RemoveChild(worksheetPart.Worksheet.OfType().FirstOrDefault(p => p.Id == id)); + worksheetPart.Worksheet.RemoveChild(worksheetPart.Worksheet.OfType().FirstOrDefault(p => p.Id == id)); worksheetPart.DeletePart(worksheetPart.DrawingsPart); } - #endregion Drawings #region LegacyDrawing @@ -5362,13 +5450,14 @@ if (xlWorksheet.LegacyDrawingIsNew) { worksheetPart.Worksheet.RemoveAllChildren(); + + if (!String.IsNullOrWhiteSpace(xlWorksheet.LegacyDrawingId)) { - if (!XLHelper.IsNullOrWhiteSpace(xlWorksheet.LegacyDrawingId)) - { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.LegacyDrawing); - worksheetPart.Worksheet.InsertAfter(new LegacyDrawing { Id = xlWorksheet.LegacyDrawingId }, - previousElement); - } + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.LegacyDrawing); + worksheetPart.Worksheet.InsertAfter(new LegacyDrawing { Id = xlWorksheet.LegacyDrawingId }, + previousElement); + + cm.SetElement(XLWSContentManager.XLWSContents.LegacyDrawing, worksheetPart.Worksheet.Elements().First()); } } @@ -5493,49 +5582,78 @@ { var filterColumn = new FilterColumn { ColumnId = (UInt32)kp.Key - 1 }; var xlFilterColumn = xlAutoFilter.Column(kp.Key); - var filterType = xlFilterColumn.FilterType; - if (filterType == XLFilterType.Custom) + + switch (xlFilterColumn.FilterType) { - var customFilters = new CustomFilters(); - foreach (var filter in kp.Value) - { - var customFilter = new CustomFilter { Val = filter.Value.ToString() }; + case XLFilterType.Custom: + var customFilters = new CustomFilters(); + foreach (var filter in kp.Value) + { + var customFilter = new CustomFilter { Val = filter.Value.ToString() }; - if (filter.Operator != XLFilterOperator.Equal) - customFilter.Operator = filter.Operator.ToOpenXml(); + if (filter.Operator != XLFilterOperator.Equal) + customFilter.Operator = filter.Operator.ToOpenXml(); - if (filter.Connector == XLConnector.And) - customFilters.And = true; + if (filter.Connector == XLConnector.And) + customFilters.And = true; - customFilters.Append(customFilter); - } - filterColumn.Append(customFilters); - } - else if (filterType == XLFilterType.TopBottom) - { - var top101 = new Top10 { Val = (double)xlFilterColumn.TopBottomValue }; - if (xlFilterColumn.TopBottomType == XLTopBottomType.Percent) - top101.Percent = true; - if (xlFilterColumn.TopBottomPart == XLTopBottomPart.Bottom) - top101.Top = false; + customFilters.Append(customFilter); + } + filterColumn.Append(customFilters); + break; - filterColumn.Append(top101); - } - else if (filterType == XLFilterType.Dynamic) - { - var dynamicFilter = new DynamicFilter - { Type = xlFilterColumn.DynamicType.ToOpenXml(), Val = xlFilterColumn.DynamicValue }; - filterColumn.Append(dynamicFilter); - } - else - { - var filters = new Filters(); - foreach (var filter in kp.Value) - { - filters.Append(new Filter { Val = filter.Value.ToString() }); - } + case XLFilterType.TopBottom: - filterColumn.Append(filters); + var top101 = new Top10 { Val = (double)xlFilterColumn.TopBottomValue }; + if (xlFilterColumn.TopBottomType == XLTopBottomType.Percent) + top101.Percent = true; + if (xlFilterColumn.TopBottomPart == XLTopBottomPart.Bottom) + top101.Top = false; + + filterColumn.Append(top101); + break; + + case XLFilterType.Dynamic: + + var dynamicFilter = new DynamicFilter + { Type = xlFilterColumn.DynamicType.ToOpenXml(), Val = xlFilterColumn.DynamicValue }; + filterColumn.Append(dynamicFilter); + break; + + case XLFilterType.DateTimeGrouping: + var dateTimeGroupFilters = new Filters(); + foreach (var filter in kp.Value) + { + if (filter.Value is DateTime) + { + var d = (DateTime)filter.Value; + var dgi = new DateGroupItem + { + Year = (UInt16)d.Year, + DateTimeGrouping = filter.DateTimeGrouping.ToOpenXml() + }; + + if (filter.DateTimeGrouping >= XLDateTimeGrouping.Month) dgi.Month = (UInt16)d.Month; + if (filter.DateTimeGrouping >= XLDateTimeGrouping.Day) dgi.Day = (UInt16)d.Day; + if (filter.DateTimeGrouping >= XLDateTimeGrouping.Hour) dgi.Hour = (UInt16)d.Hour; + if (filter.DateTimeGrouping >= XLDateTimeGrouping.Minute) dgi.Minute = (UInt16)d.Minute; + if (filter.DateTimeGrouping >= XLDateTimeGrouping.Second) dgi.Second = (UInt16)d.Second; + + dateTimeGroupFilters.Append(dgi); + } + } + filterColumn.Append(dateTimeGroupFilters); + break; + + default: + var filters = new Filters(); + foreach (var filter in kp.Value) + { + filters.Append(new Filter { Val = filter.Value.ToString() }); + } + + filterColumn.Append(filters); + break; } autoFilter.Append(filterColumn); } diff --git a/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/Excel/XLWorksheet.cs index 2f5f028..08aecef 100644 --- a/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/Excel/XLWorksheet.cs @@ -1,13 +1,11 @@ using ClosedXML.Excel.CalcEngine; using ClosedXML.Excel.Drawings; using ClosedXML.Excel.Misc; -using ClosedXML.Extensions; using System; using System.Collections.Generic; using System.Drawing; using System.IO; using System.Linq; -using System.Text; namespace ClosedXML.Excel { @@ -628,7 +626,7 @@ targetSheet.NamedRanges.Add(nr.Name, ranges); } - foreach (XLTable t in Tables.Cast()) + foreach (var t in Tables.Cast()) { String tableName = t.Name; var table = targetSheet.Tables.Any(tt => tt.Name == tableName) @@ -665,34 +663,6 @@ return targetSheet; } - private String ReplaceRelativeSheet(string newSheetName, String value) - { - if (XLHelper.IsNullOrWhiteSpace(value)) return value; - - var newValue = new StringBuilder(); - var addresses = value.Split(','); - foreach (var address in addresses) - { - var pair = address.Split('!'); - if (pair.Length == 2) - { - String sheetName = pair[0]; - if (sheetName.StartsWith("'")) - sheetName = sheetName.Substring(1, sheetName.Length - 2); - - String name = sheetName.ToLower().Equals(Name.ToLower()) - ? newSheetName - : sheetName; - newValue.Append(String.Format("{0}!{1}", name.EscapeSheetName(), pair[1])); - } - else - { - newValue.Append(address); - } - } - return newValue.ToString(); - } - public new IXLHyperlinks Hyperlinks { get; private set; } IXLDataValidations IXLWorksheet.DataValidations @@ -1237,30 +1207,36 @@ private void ShiftConditionalFormattingColumns(XLRange range, int columnsShifted) { - Int32 firstColumn = range.RangeAddress.FirstAddress.ColumnNumber; - if (firstColumn == 1) return; + if (!ConditionalFormats.Any()) return; + Int32 firstCol = range.RangeAddress.FirstAddress.ColumnNumber; + if (firstCol == 1) return; - Int32 lastColumn = range.RangeAddress.FirstAddress.ColumnNumber + columnsShifted - 1; - Int32 firstRow = range.RangeAddress.FirstAddress.RowNumber; - Int32 lastRow = range.RangeAddress.LastAddress.RowNumber; - var insertedRange = Range(firstRow, firstColumn, lastRow, lastColumn); - var fc = insertedRange.FirstColumn(); - var model = fc.ColumnLeft(); - Int32 modelFirstRow = model.RangeAddress.FirstAddress.RowNumber; - if (ConditionalFormats.Any(cf => cf.Range.Intersects(model))) + int colNum = columnsShifted > 0 ? firstCol - 1 : firstCol; + var model = Column(colNum).AsRange(); + + foreach (var cf in ConditionalFormats.ToList()) { - for (Int32 ro = firstRow; ro <= lastRow; ro++) + var cfAddress = cf.Range.RangeAddress; + if (cf.Range.Intersects(model)) { - using (var cellModel = model.Cell(ro - modelFirstRow + 1).AsRange()) - foreach (var cf in ConditionalFormats.Where(cf => cf.Range.Intersects(cellModel)).ToList()) - { - using (var r = Range(ro, firstColumn, ro, lastColumn)) r.AddConditionalFormat(cf); - } + cf.Range = Range(cfAddress.FirstAddress.RowNumber, + cfAddress.FirstAddress.ColumnNumber, + cfAddress.LastAddress.RowNumber, + cfAddress.LastAddress.ColumnNumber + columnsShifted); } + else if (cfAddress.FirstAddress.ColumnNumber >= firstCol) + { + cf.Range = Range(cfAddress.FirstAddress.RowNumber, + Math.Max(cfAddress.FirstAddress.ColumnNumber + columnsShifted, firstCol), + cfAddress.LastAddress.RowNumber, + cfAddress.LastAddress.ColumnNumber + columnsShifted); + } + if (cf.Range.RangeAddress.IsInvalid || + cf.Range.RangeAddress.FirstAddress.ColumnNumber > cf.Range.RangeAddress.LastAddress.ColumnNumber) + ConditionalFormats.Remove(f => f == cf); } - insertedRange.Dispose(); + model.Dispose(); - fc.Dispose(); } private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) @@ -1305,69 +1281,36 @@ private void ShiftConditionalFormattingRows(XLRange range, int rowsShifted) { + if (!ConditionalFormats.Any()) return; Int32 firstRow = range.RangeAddress.FirstAddress.RowNumber; if (firstRow == 1) return; - SuspendEvents(); - IXLRangeAddress usedAddress; - using (var rangeUsed = range.Worksheet.RangeUsed(true)) + int rowNum = rowsShifted > 0 ? firstRow - 1 : firstRow; + var model = Row(rowNum).AsRange(); + + foreach (var cf in ConditionalFormats.ToList()) { - usedAddress = rangeUsed == null ? range.RangeAddress : rangeUsed.RangeAddress; - } - ResumeEvents(); - - if (firstRow < usedAddress.FirstAddress.RowNumber) firstRow = usedAddress.FirstAddress.RowNumber; - - Int32 lastRow = range.RangeAddress.FirstAddress.RowNumber + rowsShifted - 1; - if (lastRow > usedAddress.LastAddress.RowNumber) lastRow = usedAddress.LastAddress.RowNumber; - - Int32 firstColumn = range.RangeAddress.FirstAddress.ColumnNumber; - if (firstColumn < usedAddress.FirstAddress.ColumnNumber) firstColumn = usedAddress.FirstAddress.ColumnNumber; - - Int32 lastColumn = range.RangeAddress.LastAddress.ColumnNumber; - if (lastColumn > usedAddress.LastAddress.ColumnNumber) lastColumn = usedAddress.LastAddress.ColumnNumber; - - var insertedRange = Range(firstRow, firstColumn, lastRow, lastColumn); - var fr = insertedRange.FirstRow(); - var model = fr.RowAbove(); - Int32 modelFirstColumn = model.RangeAddress.FirstAddress.ColumnNumber; - if (ConditionalFormats.Any(cf => cf.Range.Intersects(model))) - { - for (Int32 co = firstColumn; co <= lastColumn; co++) + var cfAddress = cf.Range.RangeAddress; + if (cf.Range.Intersects(model)) { - using (var cellModel = model.Cell(co - modelFirstColumn + 1).AsRange()) - foreach (var cf in ConditionalFormats.Where(cf => cf.Range.Intersects(cellModel)).ToList()) - { - using (var r = Range(firstRow, co, lastRow, co)) r.AddConditionalFormat(cf); - } + cf.Range = Range(cfAddress.FirstAddress.RowNumber, + cfAddress.FirstAddress.ColumnNumber, + cfAddress.LastAddress.RowNumber + rowsShifted, + cfAddress.LastAddress.ColumnNumber); } + else if (cfAddress.FirstAddress.RowNumber >= firstRow) + { + cf.Range = Range(Math.Max(cfAddress.FirstAddress.RowNumber + rowsShifted, firstRow), + cfAddress.FirstAddress.ColumnNumber, + cfAddress.LastAddress.RowNumber + rowsShifted, + cfAddress.LastAddress.ColumnNumber); + } + if (cf.Range.RangeAddress.IsInvalid || + cf.Range.RangeAddress.FirstAddress.RowNumber > cf.Range.RangeAddress.LastAddress.RowNumber) + ConditionalFormats.Remove(f => f == cf); } - insertedRange.Dispose(); + model.Dispose(); - fr.Dispose(); - } - - internal void BreakConditionalFormatsIntoCells(List addresses) - { - var newConditionalFormats = new XLConditionalFormats(); - SuspendEvents(); - foreach (var conditionalFormat in ConditionalFormats) - { - foreach (XLCell cell in conditionalFormat.Range.Cells(c => !addresses.Contains(c.Address))) - { - var row = cell.Address.RowNumber; - var column = cell.Address.ColumnLetter; - var newConditionalFormat = new XLConditionalFormat(cell.AsRange(), true); - newConditionalFormat.CopyFrom(conditionalFormat); - newConditionalFormat.Values.Values.Where(f => f.IsFormula) - .ForEach(f => f._value = XLHelper.ReplaceRelative(f.Value, row, column)); - newConditionalFormats.Add(newConditionalFormat); - } - conditionalFormat.Range.Dispose(); - } - ResumeEvents(); - newConditionalFormats.Consolidate(); - ConditionalFormats = newConditionalFormats; } private void MoveNamedRangesRows(XLRange range, int rowsShifted, IXLNamedRanges namedRanges) @@ -1604,6 +1547,7 @@ { return Pictures.Add(imageFile, name); } + public override Boolean IsEntireRow() { return true; @@ -1623,6 +1567,5 @@ else this.Cell(ro, co).SetValue(value); } - } } diff --git a/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/Excel/XLWorksheets.cs index 0ab034c..01aa54b 100644 --- a/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/Excel/XLWorksheets.cs @@ -14,16 +14,16 @@ private readonly XLWorkbook _workbook; private readonly Dictionary _worksheets = new Dictionary(); + internal ICollection Deleted { get; private set; } #endregion Constructor - public HashSet Deleted = new HashSet(); - #region Constructor public XLWorksheets(XLWorkbook workbook) { _workbook = workbook; + Deleted = new HashSet(); } #endregion Constructor diff --git a/ClosedXML/Extensions/StringExtensions.cs b/ClosedXML/Extensions/StringExtensions.cs index 60af2bd..cd2e4db 100644 --- a/ClosedXML/Extensions/StringExtensions.cs +++ b/ClosedXML/Extensions/StringExtensions.cs @@ -1,5 +1,4 @@ using System; -using System.Linq; namespace ClosedXML.Extensions { @@ -9,9 +8,9 @@ { if (sheetName.Contains("'") || sheetName.Contains(" ")) - return string.Format("'{0}'", sheetName.Replace("'", "''")); - - return sheetName; + return string.Concat('\'', sheetName.Replace("'", "''"), '\''); + else + return sheetName; } internal static string UnescapeSheetName(this String sheetName) diff --git a/ClosedXML/Utils/OpenXmlHelper.cs b/ClosedXML/Utils/OpenXmlHelper.cs index 6f0c7a2..3e1af4a 100644 --- a/ClosedXML/Utils/OpenXmlHelper.cs +++ b/ClosedXML/Utils/OpenXmlHelper.cs @@ -11,7 +11,7 @@ public static bool GetBooleanValueAsBool(BooleanValue value, bool defaultValue) { - return value == null ? defaultValue : value.Value; + return (value?.HasValue ?? false) ? value.Value : defaultValue; } } -} \ No newline at end of file +} diff --git a/ClosedXML_Examples/AutoFilters/CustomAutoFilter.cs b/ClosedXML_Examples/AutoFilters/CustomAutoFilter.cs index 9cd1b9d..d173415 100644 --- a/ClosedXML_Examples/AutoFilters/CustomAutoFilter.cs +++ b/ClosedXML_Examples/AutoFilters/CustomAutoFilter.cs @@ -126,7 +126,7 @@ workbook.Worksheet(singleColumnMixed).AutoFilter.Sort(1, XLSortOrder.Descending); #endregion - #region Multi Column + #region Multi Column workbook.Worksheet(multiColumn).AutoFilter.Column(3).EqualTo("E"); workbook.Worksheet(multiColumn).AutoFilter.Sort(3, XLSortOrder.Descending); #endregion diff --git a/ClosedXML_Examples/AutoFilters/DateTimeGroupAutoFilter.cs b/ClosedXML_Examples/AutoFilters/DateTimeGroupAutoFilter.cs new file mode 100644 index 0000000..68905d2 --- /dev/null +++ b/ClosedXML_Examples/AutoFilters/DateTimeGroupAutoFilter.cs @@ -0,0 +1,43 @@ +using ClosedXML.Excel; +using System; + +namespace ClosedXML_Examples +{ + public class DateTimeGroupAutoFilter : IXLExample + { + public void Create(string filePath) + { + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws; + + #region Single Column Dates + + String singleColumnDates = "Single Column Dates"; + ws = wb.Worksheets.Add(singleColumnDates); + + // Add a bunch of numbers to filter + ws.Cell("A1").SetValue("Dates") + .CellBelow().SetValue(new DateTime(2018, 1, 1).AddDays(2)) + .CellBelow().SetValue(new DateTime(2018, 1, 1).AddDays(3)) + .CellBelow().SetValue(new DateTime(2018, 1, 1).AddDays(3)) + .CellBelow().SetValue(new DateTime(2018, 1, 1).AddDays(5)) + .CellBelow().SetValue(new DateTime(2018, 1, 1).AddDays(1)) + .CellBelow().SetValue(new DateTime(2018, 1, 1).AddDays(4)); + + ws.Column(1).Style.NumberFormat.Format = "d MMMM yyyy"; + + // Add filters + ws.RangeUsed().SetAutoFilter().Column(1).AddDateGroupFilter(new DateTime(2018, 1, 1).AddDays(3), XLDateTimeGrouping.Day); + + // Sort the filtered list + ws.AutoFilter.Sort(1); + + #endregion Single Column Dates + + ws.Columns().AdjustToContents(); + wb.SaveAs(filePath); + } + } + } +} diff --git a/ClosedXML_Examples/Ranges/Sorting.cs b/ClosedXML_Examples/Ranges/Sorting.cs index 67ab08f..b61c19c 100644 --- a/ClosedXML_Examples/Ranges/Sorting.cs +++ b/ClosedXML_Examples/Ranges/Sorting.cs @@ -1,166 +1,167 @@ -using System; using ClosedXML.Excel; - +using System; namespace ClosedXML_Examples.Misc { public class Sorting : 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) { - var wb = new XLWorkbook(); + using (var wb = new XLWorkbook()) + { - #region Sort Table - var wsTable = wb.Worksheets.Add("Table"); - AddTestTable(wsTable); - var header = wsTable.Row(1).InsertRowsAbove(1); - Int32 lastCo = wsTable.LastColumnUsed().ColumnNumber(); - for (Int32 co = 1; co <= lastCo; co++) - wsTable.Cell(1, co).Value = "Column" + co.ToString(); + #region Sort Table - var table = wsTable.RangeUsed().AsTable(); - table.Sort("Column2 Desc, 1, 3 Asc"); - #endregion + var wsTable = wb.Worksheets.Add("Table"); + AddTestTable(wsTable); - #region Sort Rows - var wsRows = wb.Worksheets.Add("Rows"); - AddTestTable(wsRows); - wsRows.Row(1).Sort(); - wsRows.RangeUsed().Row(2).Sort(); - wsRows.Rows(3, wsRows.LastRowUsed().RowNumber()).Delete(); - #endregion + wsTable.Row(1).InsertRowsAbove(1); + Int32 lastCo = wsTable.LastColumnUsed().ColumnNumber(); + for (Int32 co = 1; co <= lastCo; co++) + wsTable.Cell(1, co).Value = "Column" + co.ToString(); - #region Sort Columns - var wsColumns = wb.Worksheets.Add("Columns"); - AddTestTable(wsColumns); - wsColumns.LastColumnUsed().Delete(); - wsColumns.Column(1).Sort(); - wsColumns.RangeUsed().Column(2).Sort(); - #endregion + var table = wsTable.RangeUsed().AsTable(); + table.Sort("Column2 Desc, 1, 3 Asc"); - #region Sort Mixed - var wsMixed = wb.Worksheets.Add("Mixed"); - AddTestColumnMixed(wsMixed); - wsMixed.Sort(); - #endregion + // Sort table another way + wsTable = wb.Worksheets.Add("Table2"); + AddTestTable(wsTable); - #region Sort Numbers - var wsNumbers = wb.Worksheets.Add("Numbers"); - AddTestColumnNumbers(wsNumbers); - wsNumbers.Sort(); - #endregion + wsTable.Row(1).InsertRowsAbove(1); + lastCo = wsTable.LastColumnUsed().ColumnNumber(); + for (Int32 co = 1; co <= lastCo; co++) + wsTable.Cell(1, co).Value = "Column" + co.ToString(); - #region Sort TimeSpans - var wsTimeSpans = wb.Worksheets.Add("TimeSpans"); - AddTestColumnTimeSpans(wsTimeSpans); - wsTimeSpans.Sort(); - #endregion + table = wsTable.RangeUsed().AsTable(); + table.Sort("Column2", XLSortOrder.Descending, false, true); - #region Sort Dates - var wsDates = wb.Worksheets.Add("Dates"); - AddTestColumnDates(wsDates); - wsDates.Sort(); - #endregion - #region Do Not Ignore Blanks - var wsIncludeBlanks = wb.Worksheets.Add("Include Blanks"); - AddTestTable(wsIncludeBlanks); - var rangeIncludeBlanks = wsIncludeBlanks; - rangeIncludeBlanks.SortColumns.Add(1, XLSortOrder.Ascending, false, true); - rangeIncludeBlanks.SortColumns.Add(2, XLSortOrder.Descending, false, true); - rangeIncludeBlanks.Sort(); + #endregion Sort Table - var wsIncludeBlanksColumn = wb.Worksheets.Add("Include Blanks Column"); - AddTestColumn(wsIncludeBlanksColumn); - var rangeIncludeBlanksColumn = wsIncludeBlanksColumn; - rangeIncludeBlanksColumn.SortColumns.Add(1, XLSortOrder.Ascending, false, true); - rangeIncludeBlanksColumn.Sort(); + #region Sort Rows - var wsIncludeBlanksColumnDesc = wb.Worksheets.Add("Include Blanks Column Desc"); - AddTestColumn(wsIncludeBlanksColumnDesc); - var rangeIncludeBlanksColumnDesc = wsIncludeBlanksColumnDesc; - rangeIncludeBlanksColumnDesc.SortColumns.Add(1, XLSortOrder.Descending, false, true); - rangeIncludeBlanksColumnDesc.Sort(); - #endregion + var wsRows = wb.Worksheets.Add("Rows"); + AddTestTable(wsRows); + wsRows.Row(1).Sort(); + wsRows.RangeUsed().Row(2).Sort(); + wsRows.Rows(3, wsRows.LastRowUsed().RowNumber()).Delete(); - #region Case Sensitive - var wsCaseSensitive = wb.Worksheets.Add("Case Sensitive"); - AddTestTable(wsCaseSensitive); - var rangeCaseSensitive = wsCaseSensitive; - rangeCaseSensitive.SortColumns.Add(1, XLSortOrder.Ascending, true, true); - rangeCaseSensitive.SortColumns.Add(2, XLSortOrder.Descending, true, true); - rangeCaseSensitive.Sort(); + #endregion Sort Rows - var wsCaseSensitiveColumn = wb.Worksheets.Add("Case Sensitive Column"); - AddTestColumn(wsCaseSensitiveColumn); - var rangeCaseSensitiveColumn = wsCaseSensitiveColumn; - rangeCaseSensitiveColumn.SortColumns.Add(1, XLSortOrder.Ascending, true, true); - rangeCaseSensitiveColumn.Sort(); + #region Sort Columns - var wsCaseSensitiveColumnDesc = wb.Worksheets.Add("Case Sensitive Column Desc"); - AddTestColumn(wsCaseSensitiveColumnDesc); - var rangeCaseSensitiveColumnDesc = wsCaseSensitiveColumnDesc; - rangeCaseSensitiveColumnDesc.SortColumns.Add(1, XLSortOrder.Descending, true, true); - rangeCaseSensitiveColumnDesc.Sort(); - #endregion + var wsColumns = wb.Worksheets.Add("Columns"); + AddTestTable(wsColumns); + wsColumns.LastColumnUsed().Delete(); + wsColumns.Column(1).Sort(); + wsColumns.RangeUsed().Column(2).Sort(); - #region Simple Sorts - var wsSimple = wb.Worksheets.Add("Simple"); - AddTestTable(wsSimple); - wsSimple.Sort(); + #endregion Sort Columns - var wsSimpleDesc = wb.Worksheets.Add("Simple Desc"); - AddTestTable(wsSimpleDesc); - wsSimpleDesc.Sort("", XLSortOrder.Descending); + #region Sort Mixed - var wsSimpleColumns = wb.Worksheets.Add("Simple Columns"); - AddTestTable(wsSimpleColumns); - wsSimpleColumns.Sort("2, A DESC, 3"); + var wsMixed = wb.Worksheets.Add("Mixed"); + AddTestColumnMixed(wsMixed); + wsMixed.Sort(); - var wsSimpleColumn = wb.Worksheets.Add("Simple Column"); - AddTestColumn(wsSimpleColumn); - wsSimpleColumn.Sort(); + #endregion Sort Mixed - var wsSimpleColumnDesc = wb.Worksheets.Add("Simple Column Desc"); - AddTestColumn(wsSimpleColumnDesc); - wsSimpleColumnDesc.Sort(1, XLSortOrder.Descending); - #endregion + #region Sort Numbers - wb.SaveAs(filePath); + var wsNumbers = wb.Worksheets.Add("Numbers"); + AddTestColumnNumbers(wsNumbers); + wsNumbers.Sort(); + + #endregion Sort Numbers + + #region Sort TimeSpans + + var wsTimeSpans = wb.Worksheets.Add("TimeSpans"); + AddTestColumnTimeSpans(wsTimeSpans); + wsTimeSpans.Sort(); + + #endregion Sort TimeSpans + + #region Sort Dates + + var wsDates = wb.Worksheets.Add("Dates"); + AddTestColumnDates(wsDates); + wsDates.Sort(); + + #endregion Sort Dates + + #region Do Not Ignore Blanks + + var wsIncludeBlanks = wb.Worksheets.Add("Include Blanks"); + AddTestTable(wsIncludeBlanks); + var rangeIncludeBlanks = wsIncludeBlanks; + rangeIncludeBlanks.SortColumns.Add(1, XLSortOrder.Ascending, false, true); + rangeIncludeBlanks.SortColumns.Add(2, XLSortOrder.Descending, false, true); + rangeIncludeBlanks.Sort(); + + var wsIncludeBlanksColumn = wb.Worksheets.Add("Include Blanks Column"); + AddTestColumn(wsIncludeBlanksColumn); + var rangeIncludeBlanksColumn = wsIncludeBlanksColumn; + rangeIncludeBlanksColumn.SortColumns.Add(1, XLSortOrder.Ascending, false, true); + rangeIncludeBlanksColumn.Sort(); + + var wsIncludeBlanksColumnDesc = wb.Worksheets.Add("Include Blanks Column Desc"); + AddTestColumn(wsIncludeBlanksColumnDesc); + var rangeIncludeBlanksColumnDesc = wsIncludeBlanksColumnDesc; + rangeIncludeBlanksColumnDesc.SortColumns.Add(1, XLSortOrder.Descending, false, true); + rangeIncludeBlanksColumnDesc.Sort(); + + #endregion Do Not Ignore Blanks + + #region Case Sensitive + + var wsCaseSensitive = wb.Worksheets.Add("Case Sensitive"); + AddTestTable(wsCaseSensitive); + var rangeCaseSensitive = wsCaseSensitive; + rangeCaseSensitive.SortColumns.Add(1, XLSortOrder.Ascending, true, true); + rangeCaseSensitive.SortColumns.Add(2, XLSortOrder.Descending, true, true); + rangeCaseSensitive.Sort(); + + var wsCaseSensitiveColumn = wb.Worksheets.Add("Case Sensitive Column"); + AddTestColumn(wsCaseSensitiveColumn); + var rangeCaseSensitiveColumn = wsCaseSensitiveColumn; + rangeCaseSensitiveColumn.SortColumns.Add(1, XLSortOrder.Ascending, true, true); + rangeCaseSensitiveColumn.Sort(); + + var wsCaseSensitiveColumnDesc = wb.Worksheets.Add("Case Sensitive Column Desc"); + AddTestColumn(wsCaseSensitiveColumnDesc); + var rangeCaseSensitiveColumnDesc = wsCaseSensitiveColumnDesc; + rangeCaseSensitiveColumnDesc.SortColumns.Add(1, XLSortOrder.Descending, true, true); + rangeCaseSensitiveColumnDesc.Sort(); + + #endregion Case Sensitive + + #region Simple Sorts + + var wsSimple = wb.Worksheets.Add("Simple"); + AddTestTable(wsSimple); + wsSimple.Sort(); + + var wsSimpleDesc = wb.Worksheets.Add("Simple Desc"); + AddTestTable(wsSimpleDesc); + wsSimpleDesc.Sort("", XLSortOrder.Descending); + + var wsSimpleColumns = wb.Worksheets.Add("Simple Columns"); + AddTestTable(wsSimpleColumns); + wsSimpleColumns.Sort("2, A DESC, 3"); + + var wsSimpleColumn = wb.Worksheets.Add("Simple Column"); + AddTestColumn(wsSimpleColumn); + wsSimpleColumn.Sort(); + + var wsSimpleColumnDesc = wb.Worksheets.Add("Simple Column Desc"); + AddTestColumn(wsSimpleColumnDesc); + wsSimpleColumnDesc.Sort(1, XLSortOrder.Descending); + + #endregion Simple Sorts + + wb.SaveAs(filePath); + } } private void AddTestColumnMixed(IXLWorksheet ws) @@ -174,6 +175,7 @@ ws.Cell("A7").SetValue(new TimeSpan(9, 4, 30)).Style.Fill.SetBackgroundColor(XLColor.IndianRed); ws.Cell("A8").SetValue(new DateTime(2011, 4, 15)).Style.Fill.SetBackgroundColor(XLColor.DeepPink); } + private void AddTestColumnNumbers(IXLWorksheet ws) { ws.Cell("A1").SetValue(1.30).Style.Fill.SetBackgroundColor(XLColor.LightGreen); @@ -185,6 +187,7 @@ ws.Cell("A7").SetValue(4.30).Style.Fill.SetBackgroundColor(XLColor.IndianRed); ws.Cell("A8").SetValue(4.15).Style.Fill.SetBackgroundColor(XLColor.DeepPink); } + private void AddTestColumnTimeSpans(IXLWorksheet ws) { ws.Cell("A1").SetValue(new TimeSpan(0, 12, 35, 21)).Style.Fill.SetBackgroundColor(XLColor.LightGreen); @@ -196,6 +199,7 @@ ws.Cell("A7").SetValue(new TimeSpan(1, 4, 30)).Style.Fill.SetBackgroundColor(XLColor.IndianRed); ws.Cell("A8").SetValue(new TimeSpan(1, 4, 15)).Style.Fill.SetBackgroundColor(XLColor.DeepPink); } + private void AddTestColumnDates(IXLWorksheet ws) { ws.Cell("A1").SetValue(new DateTime(2011, 1, 30)).Style.Fill.SetBackgroundColor(XLColor.LightGreen); @@ -207,6 +211,7 @@ ws.Cell("A7").SetValue(new DateTime(2011, 4, 30)).Style.Fill.SetBackgroundColor(XLColor.IndianRed); ws.Cell("A8").SetValue(new DateTime(2011, 4, 15)).Style.Fill.SetBackgroundColor(XLColor.DeepPink); } + private void AddTestColumn(IXLWorksheet ws) { ws.Cell("A1").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.LightGreen); @@ -218,6 +223,7 @@ ws.Cell("A7").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.IndianRed); ws.Cell("A8").SetValue("c").Style.Fill.SetBackgroundColor(XLColor.DeepPink); } + private void AddTestTable(IXLWorksheet ws) { ws.Cell("A1").SetValue("B").Style.Fill.SetBackgroundColor(XLColor.LightGreen); @@ -247,11 +253,5 @@ ws.Cell("C7").SetValue("A").Style.Fill.SetBackgroundColor(XLColor.IndianRed); ws.Cell("C8").SetValue("").Style.Fill.SetBackgroundColor(XLColor.DeepPink); } - // Private - - // Override - - - #endregion } } diff --git a/ClosedXML_Sandbox/Program.cs b/ClosedXML_Sandbox/Program.cs index 3773e7e..1f3f681 100644 --- a/ClosedXML_Sandbox/Program.cs +++ b/ClosedXML_Sandbox/Program.cs @@ -2,7 +2,7 @@ namespace ClosedXML_Sandbox { - internal class Program + internal static class Program { private static void Main(string[] args) { @@ -10,9 +10,13 @@ PerformanceRunner.TimeAction(PerformanceRunner.OpenTestFile); Console.WriteLine(); + // Disable this block by default - I don't use it often +#if false + Console.WriteLine("Running {0}", nameof(PerformanceRunner.RunInsertTable)); PerformanceRunner.TimeAction(PerformanceRunner.RunInsertTable); Console.WriteLine(); +#endif Console.WriteLine("Press any key to continue"); Console.ReadKey(); diff --git a/ClosedXML_Tests/Examples/AutoFilterTests.cs b/ClosedXML_Tests/Examples/AutoFilterTests.cs index 0ed0489..5aaf1b9 100644 --- a/ClosedXML_Tests/Examples/AutoFilterTests.cs +++ b/ClosedXML_Tests/Examples/AutoFilterTests.cs @@ -29,5 +29,11 @@ { TestHelper.RunTestExample(@"AutoFilter\TopBottomAutoFilter.xlsx"); } + + [Test] + public void DateTimeGroupAutoFilter() + { + TestHelper.RunTestExample(@"AutoFilter\DateTimeGroupAutoFilter.xlsx"); + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatCopyTests.cs b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatCopyTests.cs new file mode 100644 index 0000000..4114bcd --- /dev/null +++ b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatCopyTests.cs @@ -0,0 +1,26 @@ +using System.Linq; +using ClosedXML.Excel; +using NUnit.Framework; + +namespace ClosedXML_Tests.Excel.ConditionalFormats +{ + [TestFixture] + public class ConditionalFormatCopyTests + { + [Test] + public void StylesAreCreatedDuringCopy() + { + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("Sheet"); + var format = ws.Range("A1:A1").AddConditionalFormat(); + format.WhenEquals("=" + format.Range.FirstCell().CellRight(4).Address.ToStringRelative()).Fill + .SetBackgroundColor(XLColor.Blue); + + var wb2 = new XLWorkbook(); + var ws2 = wb2.Worksheets.Add("Sheet2"); + ws2.FirstCell().CopyFrom(ws.FirstCell()); + Assert.That(ws2.ConditionalFormats.First().Style.Fill.BackgroundColor, Is.EqualTo(XLColor.Blue)); //Added blue style + + } + } +} diff --git a/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatShiftTests.cs b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatShiftTests.cs new file mode 100644 index 0000000..bb1caed --- /dev/null +++ b/ClosedXML_Tests/Excel/ConditionalFormats/ConditionalFormatShiftTests.cs @@ -0,0 +1,109 @@ +using ClosedXML.Excel; +using NUnit.Framework; +using System; +using System.Linq; + +namespace ClosedXML_Tests.Excel.ConditionalFormats +{ + [TestFixture] + public class ConditionalFormatShiftTests + { + [Test] + public void CFShiftedOnColumnInsert() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("CFShift"); + ws.Range("A1:A1").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AirForceBlue); + ws.Range("A2:B2").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AliceBlue); + ws.Range("A3:C3").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Alizarin); + ws.Range("B4:B6").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Almond); + ws.Range("C7:D7").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Amaranth); + ws.Cells("A1:D7").Value = 1; + + ws.Column(2).InsertColumnsAfter(2); + var cf = ws.ConditionalFormats.ToArray(); + + Assert.AreEqual(5, cf.Length); + Assert.AreEqual("A1:A1", cf[0].Range.RangeAddress.ToString()); + Assert.AreEqual("A2:D2", cf[1].Range.RangeAddress.ToString()); + Assert.AreEqual("A3:E3", cf[2].Range.RangeAddress.ToString()); + Assert.AreEqual("B4:D6", cf[3].Range.RangeAddress.ToString()); + Assert.AreEqual("E7:F7", cf[4].Range.RangeAddress.ToString()); + } + } + + [Test] + public void CFShiftedOnRowInsert() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("CFShift"); + ws.Range("A1:A1").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AirForceBlue); + ws.Range("B1:B2").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AliceBlue); + ws.Range("C1:C3").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Alizarin); + ws.Range("D2:F2").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Almond); + ws.Range("G4:G5").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Amaranth); + ws.Cells("A1:G5").Value = 1; + + ws.Row(2).InsertRowsBelow(2); + var cf = ws.ConditionalFormats.ToArray(); + + Assert.AreEqual(5, cf.Length); + Assert.AreEqual("A1:A1", cf[0].Range.RangeAddress.ToString()); + Assert.AreEqual("B1:B4", cf[1].Range.RangeAddress.ToString()); + Assert.AreEqual("C1:C5", cf[2].Range.RangeAddress.ToString()); + Assert.AreEqual("D2:F4", cf[3].Range.RangeAddress.ToString()); + Assert.AreEqual("G6:G7", cf[4].Range.RangeAddress.ToString()); + } + } + + [Test] + public void CFShiftedOnColumnDelete() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("CFShift"); + ws.Range("A1:A1").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AirForceBlue); + ws.Range("A2:B2").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AliceBlue); + ws.Range("A3:C3").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Alizarin); + ws.Range("B4:B6").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Almond); + ws.Range("C7:D7").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Amaranth); + ws.Cells("A1:D7").Value = 1; + + ws.Column(2).Delete(); + var cf = ws.ConditionalFormats.ToArray(); + + Assert.AreEqual(4, cf.Length); + Assert.AreEqual("A1:A1", cf[0].Range.RangeAddress.ToString()); + Assert.AreEqual("A2:A2", cf[1].Range.RangeAddress.ToString()); + Assert.AreEqual("A3:B3", cf[2].Range.RangeAddress.ToString()); + Assert.AreEqual("B7:C7", cf[3].Range.RangeAddress.ToString()); + } + } + + [Test] + public void CFShiftedOnRowDelete() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("CFShift"); + ws.Range("A1:A1").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AirForceBlue); + ws.Range("B1:B2").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.AliceBlue); + ws.Range("C1:C3").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Alizarin); + ws.Range("D2:F2").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Almond); + ws.Range("G4:G5").AddConditionalFormat().WhenGreaterThan(0).Fill.SetBackgroundColor(XLColor.Amaranth); + ws.Cells("A1:G5").Value = 1; + + ws.Row(2).Delete(); + var cf = ws.ConditionalFormats.ToArray(); + + Assert.AreEqual(4, cf.Length); + Assert.AreEqual("A1:A1", cf[0].Range.RangeAddress.ToString()); + Assert.AreEqual("B1:B1", cf[1].Range.RangeAddress.ToString()); + Assert.AreEqual("C1:C2", cf[2].Range.RangeAddress.ToString()); + Assert.AreEqual("G3:G4", cf[3].Range.RangeAddress.ToString()); + } + } + } +} diff --git a/ClosedXML_Tests/Excel/DataValidations/DataValidationTests.cs b/ClosedXML_Tests/Excel/DataValidations/DataValidationTests.cs index a27fd8a..2a966d7 100644 --- a/ClosedXML_Tests/Excel/DataValidations/DataValidationTests.cs +++ b/ClosedXML_Tests/Excel/DataValidations/DataValidationTests.cs @@ -75,7 +75,7 @@ Assert.AreEqual("Sheet1!A1", ws2.Cell("B1").DataValidation.Value); } - [Test] + [Test, Ignore("Wait for proper formula shifting (#686)")] public void Validation_3() { var wb = new XLWorkbook(); @@ -98,7 +98,7 @@ Assert.AreEqual("A2", ws.Cell("B2").DataValidation.Value); } - [Test] + [Test, Ignore("Wait for proper formula shifting (#686)")] public void Validation_5() { var wb = new XLWorkbook(); @@ -155,5 +155,95 @@ Assert.AreEqual("Error", ws.DataValidations.Single().ErrorTitle); } + + [Test] + [TestCase("A1:C3", 5, false, "A1:C3")] + [TestCase("A1:C3", 2, false, "A1:C4")] + [TestCase("A1:C3", 1, false, "A2:C4")] + [TestCase("A1:C3", 5, true, "A1:C3")] + [TestCase("A1:C3", 2, true, "A1:C4")] + [TestCase("A1:C3", 1, true, "A2:C4")] + public void DataValidationShiftedOnRowInsert(string initialAddress, int rowNum, bool setValue, string expectedAddress) + { + //Arrange + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("DataValidation"); + var validation = ws.Range(initialAddress).SetDataValidation(); + validation.WholeNumber.Between(0, 100); + if (setValue) + ws.Range(initialAddress).Value = 50; + + //Act + ws.Row(rowNum).InsertRowsAbove(1); + + //Assert + Assert.AreEqual(1, ws.DataValidations.Count()); + Assert.AreEqual(1, ws.DataValidations.First().Ranges.Count); + Assert.AreEqual(expectedAddress, ws.DataValidations.First().Ranges.First().RangeAddress.ToString()); + } + + [Test] + [TestCase("A1:C3", 5, false, "A1:C3")] + [TestCase("A1:C3", 2, false, "A1:D3")] + [TestCase("A1:C3", 1, false, "B1:D3")] + [TestCase("A1:C3", 5, true, "A1:C3")] + [TestCase("A1:C3", 2, true, "A1:D3")] + [TestCase("A1:C3", 1, true, "B1:D3")] + public void DataValidationShiftedOnColumnInsert(string initialAddress, int columnNum, bool setValue, string expectedAddress) + { + //Arrange + var wb = new XLWorkbook(); + var ws = wb.Worksheets.Add("DataValidation"); + var validation = ws.Range(initialAddress).SetDataValidation(); + validation.WholeNumber.Between(0, 100); + if (setValue) + ws.Range(initialAddress).Value = 50; + + //Act + ws.Column(columnNum).InsertColumnsBefore(1); + + //Assert + Assert.AreEqual(1, ws.DataValidations.Count()); + Assert.AreEqual(1, ws.DataValidations.First().Ranges.Count); + Assert.AreEqual(expectedAddress, ws.DataValidations.First().Ranges.First().RangeAddress.ToString()); + } + + [Test] + public void DataValidationClearSplitsRange() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("DataValidation"); + var validation = ws.Range("A1:C3").SetDataValidation(); + validation.WholeNumber.Between(0, 100); + + //Act + ws.Cell("B2").Clear(XLClearOptions.ContentsAndFormats); + + //Assert + Assert.IsFalse(ws.Cell("B2").HasDataValidation); + Assert.IsTrue(ws.Range("A1:C3").Cells().Where(c => c.Address.ToString() != "B2").All(c => c.HasDataValidation)); + } + } + + [Test] + public void NewDataValidationSplitsRange() + { + using (var wb = new XLWorkbook()) + { + var ws = wb.Worksheets.Add("DataValidation"); + var validation = ws.Range("A1:C3").SetDataValidation(); + validation.WholeNumber.Between(10, 100); + + //Act + ws.Cell("B2").NewDataValidation.WholeNumber.Between(-100, -0); + + //Assert + Assert.AreEqual("-100", ws.Cell("B2").DataValidation.MinValue); + Assert.IsTrue(ws.Range("A1:C3").Cells().Where(c => c.Address.ToString() != "B2").All(c => c.HasDataValidation)); + Assert.IsTrue(ws.Range("A1:C3").Cells().Where(c => c.Address.ToString() != "B2") + .All(c => c.DataValidation.MinValue == "10")); + } + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs index 8ee4606..2cb1332 100644 --- a/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs +++ b/ClosedXML_Tests/Excel/PivotTables/XLPivotTableTests.cs @@ -144,5 +144,75 @@ } } } + + [TestCase(true)] + [TestCase(false)] + public void PivotFieldOptionsSaveTest(bool withDefaults) + { + using (var stream = TestHelper.GetStreamFromResource(TestHelper.GetResourcePath(@"Examples\PivotTables\PivotTables.xlsx"))) + using (var wb = new XLWorkbook(stream)) + { + var ws = wb.Worksheet("PastrySalesData"); + var table = ws.Table("PastrySalesData"); + + var dataRange = ws.Range(ws.Range(1, 1, 1, 3).FirstCell(), table.DataRange.LastCell()); + + var ptSheet = wb.Worksheets.Add("pvtFieldOptionsTest"); + var pt = ptSheet.PivotTables.AddNew("pvtFieldOptionsTest", ptSheet.Cell(1, 1), dataRange); + + var field = pt.RowLabels.Add("Name") + .SetSubtotalCaption("Test caption") + .SetCustomName("Test name"); + SetFieldOptions(field, withDefaults); + + pt.ColumnLabels.Add("Month"); + pt.Values.Add("NumberOfOrders").SetSummaryFormula(XLPivotSummary.Sum); + //wb.SaveAs(".\\pivot.xlsx.zip", true); + + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms, true); + + ms.Position = 0; + + using (var wbassert = new XLWorkbook(ms)) + { + var wsassert = wbassert.Worksheet("pvtFieldOptionsTest"); + var ptassert = wsassert.PivotTable("pvtFieldOptionsTest"); + var pfassert = ptassert.RowLabels.Get("Name"); + Assert.AreNotEqual(null, pfassert, "name save failure"); + Assert.AreEqual("Test caption", pfassert.SubtotalCaption, "SubtotalCaption save failure"); + Assert.AreEqual("Test name", pfassert.CustomName, "CustomName save failure"); + AssertFieldOptions(pfassert, withDefaults); + } + } + } + } + + private static void SetFieldOptions(IXLPivotField field, bool withDefaults) + { + field.SubtotalsAtTop = !withDefaults; + field.ShowBlankItems = !withDefaults; + field.Outline = !withDefaults; + field.Compact = !withDefaults; + field.Collapsed = withDefaults; + field.InsertBlankLines = withDefaults; + field.RepeatItemLabels = withDefaults; + field.InsertPageBreaks = withDefaults; + field.IncludeNewItemsInFilter = withDefaults; + } + + private static void AssertFieldOptions(IXLPivotField field, bool withDefaults) + { + Assert.AreEqual(!withDefaults, field.SubtotalsAtTop, "SubtotalsAtTop save failure"); + Assert.AreEqual(!withDefaults, field.ShowBlankItems, "ShowBlankItems save failure"); + Assert.AreEqual(!withDefaults, field.Outline, "Outline save failure"); + Assert.AreEqual(!withDefaults, field.Compact, "Compact save failure"); + Assert.AreEqual(withDefaults, field.Collapsed, "Collapsed save failure"); + Assert.AreEqual(withDefaults, field.InsertBlankLines, "InsertBlankLines save failure"); + Assert.AreEqual(withDefaults, field.RepeatItemLabels, "RepeatItemLabels save failure"); + Assert.AreEqual(withDefaults, field.InsertPageBreaks, "InsertPageBreaks save failure"); + Assert.AreEqual(withDefaults, field.IncludeNewItemsInFilter, "IncludeNewItemsInFilter save failure"); + } } } diff --git a/ClosedXML_Tests/Excel/Saving/SavingTests.cs b/ClosedXML_Tests/Excel/Saving/SavingTests.cs index 51dc2a0..4b19a6b 100644 --- a/ClosedXML_Tests/Excel/Saving/SavingTests.cs +++ b/ClosedXML_Tests/Excel/Saving/SavingTests.cs @@ -1,28 +1,20 @@ using ClosedXML.Excel; +using ClosedXML.Excel.Drawings; +using ClosedXML_Tests.Utils; using NUnit.Framework; using System; -using System.Collections.Generic; +using System.Drawing; using System.Globalization; using System.IO; using System.Linq; +using System.Reflection; using System.Threading; -using System.Threading.Tasks; namespace ClosedXML_Tests.Excel.Saving { [TestFixture] public class SavingTests { - private string _tempFolder; - private List _tempFiles; - - [SetUp] - public void Setup() - { - _tempFolder = Path.GetTempPath(); - _tempFiles = new List(); - } - [Test] public void CanSaveEmptyFile() { @@ -129,77 +121,90 @@ [Test] public void CanSaveAsCopyReadOnlyFile() { - // Arrange - string id = Guid.NewGuid().ToString(); - string original = string.Format("{0}original{1}.xlsx", _tempFolder, id); - string copy = string.Format("{0}copy_of_{1}.xlsx", _tempFolder, id); - - using (var wb = new XLWorkbook()) + using (var original = new TemporaryFile()) { - var sheet = wb.Worksheets.Add("TestSheet"); - wb.SaveAs(original); - _tempFiles.Add(original); - } - System.IO.File.SetAttributes(original, FileAttributes.ReadOnly); + try + { + using (var copy = new TemporaryFile()) + { + // Arrange + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + wb.SaveAs(original.Path); + } + File.SetAttributes(original.Path, FileAttributes.ReadOnly); - // Act - using (var wb = new XLWorkbook(original)) - { - wb.SaveAs(copy); - _tempFiles.Add(copy); - } + // Act + using (var wb = new XLWorkbook(original.Path)) + { + wb.SaveAs(copy.Path); + } - // Assert - Assert.IsTrue(System.IO.File.Exists(copy)); - Assert.IsFalse(System.IO.File.GetAttributes(copy).HasFlag(FileAttributes.ReadOnly)); + // Assert + Assert.IsTrue(File.Exists(copy.Path)); + Assert.IsFalse(File.GetAttributes(copy.Path).HasFlag(FileAttributes.ReadOnly)); + } + } + finally + { + // Tear down + File.SetAttributes(original.Path, FileAttributes.Normal); + } + } } [Test] public void CanSaveAsOverwriteExistingFile() { - // Arrange - string id = Guid.NewGuid().ToString(); - string existing = string.Format("{0}existing{1}.xlsx", _tempFolder, id); - - System.IO.File.WriteAllText(existing, ""); - _tempFiles.Add(existing); - - // Act - using (var wb = new XLWorkbook()) + using (var existing = new TemporaryFile()) { - var sheet = wb.Worksheets.Add("TestSheet"); - wb.SaveAs(existing); + // Arrange + File.WriteAllText(existing.Path, ""); + + // Act + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + wb.SaveAs(existing.Path); + } + + // Assert + Assert.IsTrue(File.Exists(existing.Path)); + Assert.Greater(new FileInfo(existing.Path).Length, 0); } - - // Assert - Assert.IsTrue(System.IO.File.Exists(existing)); - Assert.Greater(new System.IO.FileInfo(existing).Length, 0); } - [Test] public void CannotSaveAsOverwriteExistingReadOnlyFile() { - // Arrange - string id = Guid.NewGuid().ToString(); - string existing = string.Format("{0}existing{1}.xlsx", _tempFolder, id); - - System.IO.File.WriteAllText(existing, ""); - _tempFiles.Add(existing); - System.IO.File.SetAttributes(existing, FileAttributes.ReadOnly); - - // Act - TestDelegate saveAs = () => + using (var existing = new TemporaryFile()) { - using (var wb = new XLWorkbook()) + try { - var sheet = wb.Worksheets.Add("TestSheet"); - wb.SaveAs(existing); - } - }; + // Arrange + File.WriteAllText(existing.Path, ""); + File.SetAttributes(existing.Path, FileAttributes.ReadOnly); - // Assert - Assert.Throws(typeof(UnauthorizedAccessException), saveAs); + // Act + TestDelegate saveAs = () => + { + using (var wb = new XLWorkbook()) + { + var sheet = wb.Worksheets.Add("TestSheet"); + wb.SaveAs(existing.Path); + } + }; + + // Assert + Assert.Throws(typeof(UnauthorizedAccessException), saveAs); + } + finally + { + // Tear down + File.SetAttributes(existing.Path, FileAttributes.Normal); + } + } } [Test] @@ -228,20 +233,26 @@ } } - - [TearDown] - public void DeleteTempFiles() + [Test] + public void CanSaveFileWithPictureAndComment() { - foreach (var fileName in _tempFiles) + using (var ms = new MemoryStream()) + using (var wb = new XLWorkbook()) + using (var resourceStream = Assembly.GetAssembly(typeof(ClosedXML_Examples.BasicTable)).GetManifestResourceStream("ClosedXML_Examples.Resources.SampleImage.jpg")) + using (var bitmap = Bitmap.FromStream(resourceStream) as Bitmap) { - try - { - System.IO.File.Delete(fileName); - } - catch - { } + var ws = wb.AddWorksheet("Sheet1"); + ws.Cell("D4").Value = "Hello world."; + + ws.AddPicture(bitmap, "MyPicture") + .WithPlacement(XLPicturePlacement.FreeFloating) + .MoveTo(50, 50) + .WithSize(200, 200); + + ws.Cell("D4").Comment.SetVisible().AddText("This is a comment"); + + wb.SaveAs(ms); } - _tempFiles.Clear(); } } -} +} \ No newline at end of file diff --git a/ClosedXML_Tests/Excel/Tables/TablesTests.cs b/ClosedXML_Tests/Excel/Tables/TablesTests.cs index 0036c7d..e616e05 100644 --- a/ClosedXML_Tests/Excel/Tables/TablesTests.cs +++ b/ClosedXML_Tests/Excel/Tables/TablesTests.cs @@ -441,6 +441,41 @@ } [Test] + public void CanDeleteTable() + { + var l = new List() + { + new TestObjectWithAttributes() { Column1 = "a", Column2 = "b", MyField = 4, UnOrderedColumn = 999 }, + new TestObjectWithAttributes() { Column1 = "c", Column2 = "d", MyField = 5, UnOrderedColumn = 777 } + }; + + using (var ms = new MemoryStream()) + { + using (var wb = new XLWorkbook()) + { + var ws = wb.AddWorksheet("Sheet1"); + ws.FirstCell().InsertTable(l); + //wb.SaveAs(ms); + + wb.SaveAs(@"c:\temp\deletetable1.xlsx"); + } + + ms.Seek(0, SeekOrigin.Begin); + + using (var wb = new XLWorkbook(@"c:\temp\deletetable1.xlsx")) + { + var ws = wb.Worksheets.First(); + var table = ws.Tables.First(); + + ws.Tables.Remove(table.Name); + Assert.AreEqual(0, ws.Tables.Count()); + //wb.Save(); + wb.SaveAs(@"c:\temp\deletetable2.xlsx"); + } + } + } + + [Test] public void CanDeleteTableField() { var l = new List() diff --git a/ClosedXML_Tests/Resource/Examples/AutoFilter/DateTimeGroupAutoFilter.xlsx b/ClosedXML_Tests/Resource/Examples/AutoFilter/DateTimeGroupAutoFilter.xlsx new file mode 100644 index 0000000..4e5ab05 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/AutoFilter/DateTimeGroupAutoFilter.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx b/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx index 1e430a1..b4d626c 100644 --- a/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx +++ b/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Loading/ChangingBasicTable.xlsx b/ClosedXML_Tests/Resource/Examples/Loading/ChangingBasicTable.xlsx index a27f51c..a13f09b 100644 --- a/ClosedXML_Tests/Resource/Examples/Loading/ChangingBasicTable.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Loading/ChangingBasicTable.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx index cd861ef..12cb876 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataSet.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx index 7c3a2ec..c3e6540 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/AddingDataTableAsWorksheet.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx index 0d98696..7c902ac 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/AdjustToContents.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/BasicTable.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/BasicTable.xlsx index fc3c33f..3eb10a3 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/BasicTable.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/BasicTable.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/Collections.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/Collections.xlsx index d628b28..b562697 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/Collections.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/Collections.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/CopyingRowsAndColumns.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/CopyingRowsAndColumns.xlsx index f313701..add061f 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/CopyingRowsAndColumns.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/CopyingRowsAndColumns.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx index 03b9b20..40d3c0f 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/CopyingWorksheets.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx index c5979d8..c118e4c 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx index f5db9c4..eb57e22 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/InsertingData.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/LambdaExpressions.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/LambdaExpressions.xlsx index 368f016..b89706c 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/LambdaExpressions.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/LambdaExpressions.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/MergeCells.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/MergeCells.xlsx index 3860d72..eb5761d 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/MergeCells.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/MergeCells.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx index f0c9c03..df272ce 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/MergeMoves.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx b/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx index d988cd1..0d7a0b2 100644 --- a/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Misc/ShowCase.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx index 4ad1e7f..0c5a347 100644 --- a/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/PivotTables/PivotTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/AddingRowToTables.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/AddingRowToTables.xlsx index 014ea30..2f0ed47 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/AddingRowToTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/AddingRowToTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx index 95daf5a..9b8602f 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/ClearingRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/CopyingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/CopyingRanges.xlsx index faa24e2..b390e9d 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/CopyingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/CopyingRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/CurrentRowColumn.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/CurrentRowColumn.xlsx index 741e75f..594d975 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/CurrentRowColumn.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/CurrentRowColumn.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx index ed8b923..b34c7d8 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/DeletingRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx index 1c6a1ed..982f7c8 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/ShiftingRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx index e426613..a3db8ac 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/Sorting.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/Sorting.xlsx index 36b889d..a38d9be 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/Sorting.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/Sorting.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRanges.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRanges.xlsx index c7f029d..d348c86 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRanges.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRanges.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx b/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx index 0303805..bce7646 100644 --- a/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Ranges/TransposeRangesPlus.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/StyleAlignment.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/StyleAlignment.xlsx index b2670c0..ec3232f 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/StyleAlignment.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/StyleAlignment.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Styles/StyleBorder.xlsx b/ClosedXML_Tests/Resource/Examples/Styles/StyleBorder.xlsx index 4d52bd6..bc01898 100644 --- a/ClosedXML_Tests/Resource/Examples/Styles/StyleBorder.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Styles/StyleBorder.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx index 05cfa2c..97daf3b 100644 --- a/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Tables/InsertingTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx index 5d223ac..c86cfe2 100644 --- a/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Tables/ResizingTables.xlsx Binary files differ diff --git a/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx b/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx index 39402fe..e18c66c 100644 --- a/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx +++ b/ClosedXML_Tests/Resource/Examples/Tables/UsingTables.xlsx Binary files differ