diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs index 5b1285d..8859848 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs @@ -3,15 +3,12 @@ { using System.Collections.Generic; - public interface IXLAutoFilter + public interface IXLAutoFilter: IDisposable { - //IXLAutoFilter Set(); - //List Filters { get; } - //List CustomFilters { get; } IXLFilterColumn Column(String column); IXLFilterColumn Column(Int32 column); - IXLAutoFilter Sort(Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); + IXLAutoFilter Sort(Int32 columnToSortBy = 1, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); Boolean Sorted { get; set; } XLSortOrder SortOrder { get; set; } Int32 SortColumn { get; set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLCustomFilteredColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLCustomFilteredColumn.cs index 959d3de..85cec03 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLCustomFilteredColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLCustomFilteredColumn.cs @@ -1,8 +1,6 @@ using System; namespace ClosedXML.Excel { - using System.Collections.Generic; - public interface IXLCustomFilteredColumn { void EqualTo(T value) where T : IComparable; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs index a6944c7..159faf9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs @@ -1,8 +1,6 @@ using System; namespace ClosedXML.Excel { - using System.Collections.Generic; - public enum XLTopBottomType { Items, Percent } public interface IXLFilterColumn { @@ -31,12 +29,18 @@ IXLFilterConnector NotContains(String value); XLFilterType FilterType { get; set; } - Int32 TopBottomValue { get; set; } XLTopBottomType TopBottomType { get; set; } XLTopBottomPart TopBottomPart { get; set; } - XLFilterDynamicType DynamicType { get; set; } 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); + } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilterConnector.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilterConnector.cs index edb8560..7955869 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilterConnector.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilterConnector.cs @@ -1,7 +1,4 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; namespace ClosedXML.Excel { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilteredColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilteredColumn.cs index d3f31d8..6491f55 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilteredColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilteredColumn.cs @@ -1,8 +1,6 @@ using System; namespace ClosedXML.Excel { - using System.Collections.Generic; - public interface IXLFilteredColumn { IXLFilteredColumn AddFilter(T value) where T : IComparable; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs index 3e43923..423f094 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs @@ -1,34 +1,41 @@ using System; using System.Linq; + namespace ClosedXML.Excel { using System.Collections.Generic; - internal class XLAutoFilter: IXLBaseAutoFilter, IXLAutoFilter + internal class XLAutoFilter : IXLBaseAutoFilter, IXLAutoFilter { + private readonly Dictionary _columns = new Dictionary(); + public XLAutoFilter() { Filters = new Dictionary>(); } + public Dictionary> Filters { get; private set; } + + #region IXLAutoFilter Members + + IXLAutoFilter IXLAutoFilter.Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase, + Boolean ignoreBlanks) + { + return Sort(columnToSortBy, sortOrder, matchCase, ignoreBlanks); + } + + public void Dispose() + { + if (Range != null) + Range.Dispose(); + } + + #endregion + + #region IXLBaseAutoFilter Members + public Boolean Enabled { get; set; } public IXLRange Range { get; set; } - public XLAutoFilter Set(IXLRangeBase range) - { - Range = range.AsRange(); - Enabled = true; - return this; - } - public XLAutoFilter Clear() - { - Enabled = false; - Filters.Clear(); - foreach (var row in Range.Rows().Where(r => r.RowNumber() > 1)) - { - row.WorksheetRow().Unhide(); - } - return this; - } IXLBaseAutoFilter IXLBaseAutoFilter.Clear() { @@ -40,67 +47,8 @@ return Set(range); } - public XLAutoFilter Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase, Boolean ignoreBlanks) - { - - Range.Range(Range.FirstCell().CellBelow(), Range.LastCell()).Sort(columnToSortBy, sortOrder, matchCase, ignoreBlanks); - Sorted = true; - SortOrder = sortOrder; - SortColumn = columnToSortBy; - - if (Enabled) - { - foreach (var row in Range.Rows().Where(r => r.RowNumber() > 1)) - { - row.WorksheetRow().Unhide(); - } - foreach (var kp in Filters) - { - Boolean firstFilter = true; - foreach (XLFilter filter in kp.Value) - { - Boolean isText = filter.Value is String; - foreach (var row in Range.Rows().Where(r => r.RowNumber() > 1)) - { - Boolean match = isText ? filter.Condition(row.Cell(kp.Key).GetString()) : row.Cell(kp.Key).DataType == XLCellValues.Number && filter.Condition(row.Cell(kp.Key).GetDouble()); - if (firstFilter) - { - if (match) - row.WorksheetRow().Unhide(); - else - row.WorksheetRow().Hide(); - } - else - { - if (filter.Connector == XLConnector.And) - { - if (!row.WorksheetRow().IsHidden) - { - if (match) - row.WorksheetRow().Unhide(); - else - row.WorksheetRow().Hide(); - } - } - else if (match) - row.WorksheetRow().Unhide(); - } - } - firstFilter = false; - } - - } - } - - return this; - } - - IXLAutoFilter IXLAutoFilter.Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase, Boolean ignoreBlanks) - { - return Sort(columnToSortBy, sortOrder, matchCase, ignoreBlanks); - } - - IXLBaseAutoFilter IXLBaseAutoFilter.Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase, Boolean ignoreBlanks) + IXLBaseAutoFilter IXLBaseAutoFilter.Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase, + Boolean ignoreBlanks) { return Sort(columnToSortBy, sortOrder, matchCase, ignoreBlanks); } @@ -109,17 +57,11 @@ public XLSortOrder SortOrder { get; set; } public Int32 SortColumn { get; set; } - public Dictionary> Filters { get; private set; } - - //List Filters { get; } - //List CustomFilters { get; } - //Boolean Sorted { get; } - //Int32 SortColumn { get; } public IXLFilterColumn Column(String column) { return Column(ExcelHelper.GetColumnNumberFromLetter(column)); } - Dictionary _columns = new Dictionary(); + public IXLFilterColumn Column(Int32 column) { XLFilterColumn filterColumn; @@ -131,5 +73,85 @@ return filterColumn; } + + #endregion + + public XLAutoFilter Set(IXLRangeBase range) + { + Range = range.AsRange(); + Enabled = true; + return this; + } + + public XLAutoFilter Clear() + { + Enabled = false; + Filters.Clear(); + foreach (IXLRangeRow row in Range.Rows().Where(r => r.RowNumber() > 1)) + row.WorksheetRow().Unhide(); + return this; + } + + public XLAutoFilter Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase, Boolean ignoreBlanks) + { + Range.Range(Range.FirstCell().CellBelow(), Range.LastCell()).Sort(columnToSortBy, sortOrder, matchCase, + ignoreBlanks).Dispose(); + Sorted = true; + SortOrder = sortOrder; + SortColumn = columnToSortBy; + + if (Enabled) + { + using (var rows = Range.Rows(2, Range.RowCount())) + { + foreach (IXLRangeRow row in rows) + row.WorksheetRow().Unhide().Dispose(); + } + + foreach (KeyValuePair> kp in Filters) + { + Boolean firstFilter = true; + foreach (XLFilter filter in kp.Value) + { + Boolean isText = filter.Value is String; + 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 == XLCellValues.Number && + filter.Condition(row.Cell(kp.Key).GetDouble()); + if (firstFilter) + { + if (match) + row.WorksheetRow().Unhide().Dispose(); + else + row.WorksheetRow().Hide().Dispose(); + } + else + { + if (filter.Connector == XLConnector.And) + { + if (!row.WorksheetRow().IsHidden) + { + if (match) + row.WorksheetRow().Unhide().Dispose(); + else + row.WorksheetRow().Hide().Dispose(); + } + } + else if (match) + row.WorksheetRow().Unhide().Dispose(); + } + } + firstFilter = false; + } + } + } + } + + return this; + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLCustomFilteredColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLCustomFilteredColumn.cs index 4f37c94..26fd585 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLCustomFilteredColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLCustomFilteredColumn.cs @@ -1,14 +1,14 @@ using System; using System.Linq; + namespace ClosedXML.Excel { - using System.Collections.Generic; - - internal class XLCustomFilteredColumn: IXLCustomFilteredColumn + internal class XLCustomFilteredColumn : IXLCustomFilteredColumn { - private XLAutoFilter _autoFilter; - private Int32 _column; - private XLConnector _connector; + private readonly XLAutoFilter _autoFilter; + private readonly Int32 _column; + private readonly XLConnector _connector; + public XLCustomFilteredColumn(XLAutoFilter autoFilter, Int32 column, XLConnector connector) { _autoFilter = autoFilter; @@ -16,88 +16,127 @@ _connector = connector; } - public void EqualTo(T value) where T : IComparable + #region IXLCustomFilteredColumn Members + + public void EqualTo(T value) where T: IComparable { if (typeof(T) == typeof(String)) - ApplyCustomFilter(value, XLFilterOperator.Equal, v => v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase)); + { + ApplyCustomFilter(value, XLFilterOperator.Equal, + v => + v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase)); + } else - ApplyCustomFilter(value, XLFilterOperator.Equal, v => (v.CastTo() as IComparable).CompareTo(value) == 0); + { + ApplyCustomFilter(value, XLFilterOperator.Equal, + v => v.CastTo().CompareTo(value) == 0); + } } - public void NotEqualTo(T value) where T : IComparable + public void NotEqualTo(T value) where T: IComparable { if (typeof(T) == typeof(String)) - ApplyCustomFilter(value, XLFilterOperator.NotEqual, v => !v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase)); + { + ApplyCustomFilter(value, XLFilterOperator.NotEqual, + v => + !v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase)); + } else - ApplyCustomFilter(value, XLFilterOperator.NotEqual, v => (v.CastTo() as IComparable).CompareTo(value) != 0); + { + ApplyCustomFilter(value, XLFilterOperator.NotEqual, + v => v.CastTo().CompareTo(value) != 0); + } } - public void GreaterThan(T value) where T : IComparable + public void GreaterThan(T value) where T: IComparable { - ApplyCustomFilter(value, XLFilterOperator.GreaterThan, v => (v.CastTo() as IComparable).CompareTo(value) > 0); + ApplyCustomFilter(value, XLFilterOperator.GreaterThan, + v => v.CastTo().CompareTo(value) > 0); } - public void LessThan(T value) where T : IComparable + public void LessThan(T value) where T: IComparable { - ApplyCustomFilter(value, XLFilterOperator.LessThan, v => (v.CastTo() as IComparable).CompareTo(value) < 0); + ApplyCustomFilter(value, XLFilterOperator.LessThan, v => v.CastTo().CompareTo(value) < 0); } - public void EqualOrGreaterThan(T value) where T : IComparable + public void EqualOrGreaterThan(T value) where T: IComparable { - ApplyCustomFilter(value, XLFilterOperator.EqualOrGreaterThan, v => (v.CastTo() as IComparable).CompareTo(value) >= 0); + ApplyCustomFilter(value, XLFilterOperator.EqualOrGreaterThan, + v => v.CastTo().CompareTo(value) >= 0); } - public void EqualOrLessThan(T value) where T : IComparable + public void EqualOrLessThan(T value) where T: IComparable { - ApplyCustomFilter(value, XLFilterOperator.EqualOrLessThan, v => (v.CastTo() as IComparable).CompareTo(value) <= 0); + ApplyCustomFilter(value, XLFilterOperator.EqualOrLessThan, + v => v.CastTo().CompareTo(value) <= 0); } public void BeginsWith(String value) { - ApplyCustomFilter(value.ToString() + "*", XLFilterOperator.Equal, s => ((string)s).StartsWith(value.ToString(), StringComparison.InvariantCultureIgnoreCase)); + ApplyCustomFilter(value + "*", XLFilterOperator.Equal, + s => ((string)s).StartsWith(value, StringComparison.InvariantCultureIgnoreCase)); } public void NotBeginsWith(String value) { - ApplyCustomFilter(value.ToString() + "*", XLFilterOperator.NotEqual, s => !((string)s).StartsWith(value.ToString(), StringComparison.InvariantCultureIgnoreCase)); + ApplyCustomFilter(value + "*", XLFilterOperator.NotEqual, + s => + !((string)s).StartsWith(value, StringComparison.InvariantCultureIgnoreCase)); } public void EndsWith(String value) { - ApplyCustomFilter("*" + value.ToString(), XLFilterOperator.Equal, s => ((string)s).EndsWith(value.ToString(), StringComparison.InvariantCultureIgnoreCase)); + ApplyCustomFilter("*" + value, XLFilterOperator.Equal, + s => ((string)s).EndsWith(value, StringComparison.InvariantCultureIgnoreCase)); } public void NotEndsWith(String value) { - ApplyCustomFilter("*" + value.ToString(), XLFilterOperator.NotEqual, s => !((string)s).EndsWith(value.ToString(), StringComparison.InvariantCultureIgnoreCase)); + ApplyCustomFilter("*" + value, XLFilterOperator.NotEqual, + s => !((string)s).EndsWith(value, StringComparison.InvariantCultureIgnoreCase)); } public void Contains(String value) { - ApplyCustomFilter("*" + value.ToString() + "*", XLFilterOperator.Equal, s => ((string)s).ToLower().Contains(value.ToString().ToLower())); - } - public void NotContains(String value) - { - ApplyCustomFilter("*" + value.ToString() + "*", XLFilterOperator.Equal, s => !((string)s).ToLower().Contains(value.ToString().ToLower())); + ApplyCustomFilter("*" + value + "*", XLFilterOperator.Equal, + s => ((string)s).ToLower().Contains(value.ToLower())); } - private void ApplyCustomFilter(T value, XLFilterOperator op, Func condition) where T : IComparable + public void NotContains(String value) { - _autoFilter.Filters[_column].Add(new XLFilter { Value = value, Operator = op, Connector = _connector, Condition = condition }); - foreach (var row in _autoFilter.Range.Rows().Where(r => r.RowNumber() > 1)) + ApplyCustomFilter("*" + value + "*", XLFilterOperator.Equal, + s => !((string)s).ToLower().Contains(value.ToLower())); + } + + #endregion + + private void ApplyCustomFilter(T value, XLFilterOperator op, Func condition) + where T: IComparable + { + _autoFilter.Filters[_column].Add(new XLFilter + { + Value = value, + Operator = op, + Connector = _connector, + Condition = condition + }); + using (var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount())) { - if (_connector == XLConnector.And) + foreach (IXLRangeRow row in rows) { - if (!row.WorksheetRow().IsHidden) + if (_connector == XLConnector.And) { - if (condition(row.Cell(_column).GetValue())) - row.WorksheetRow().Unhide(); - else - row.WorksheetRow().Hide(); + if (!row.WorksheetRow().IsHidden) + { + if (condition(row.Cell(_column).GetValue())) + row.WorksheetRow().Unhide().Dispose(); + else + row.WorksheetRow().Hide().Dispose(); + } } + else if (condition(row.Cell(_column).GetValue())) + row.WorksheetRow().Unhide().Dispose(); } - else if (condition(row.Cell(_column).GetValue())) - row.WorksheetRow().Unhide(); } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs index 89dafe6..62cb520 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs @@ -1,31 +1,43 @@ using System; using System.Linq; + namespace ClosedXML.Excel { using System.Collections.Generic; - internal class XLFilterColumn: IXLFilterColumn + internal class XLFilterColumn : IXLFilterColumn { - private XLAutoFilter _autoFilter; - private Int32 _column; + private readonly XLAutoFilter _autoFilter; + private readonly Int32 _column; + public XLFilterColumn(XLAutoFilter autoFilter, Int32 column) { _autoFilter = autoFilter; _column = column; } - public IXLFilterColumn Sort(XLSortOrder order = XLSortOrder.Ascending) { throw new NotImplementedException(); } - public void Clear() + #region IXLFilterColumn Members + + public void Clear() { if (_autoFilter.Filters.ContainsKey(_column)) _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)) - ApplyCustomFilter(value, XLFilterOperator.Equal, v => v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase), XLFilterType.Regular); + { + ApplyCustomFilter(value, XLFilterOperator.Equal, + v => + v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase), + XLFilterType.Regular); + } else - ApplyCustomFilter(value, XLFilterOperator.Equal, v => (v.CastTo() as IComparable).CompareTo(value) == 0, XLFilterType.Regular); + { + ApplyCustomFilter(value, XLFilterOperator.Equal, + v => v.CastTo().CompareTo(value) == 0, XLFilterType.Regular); + } return new XLFilteredColumn(_autoFilter, _column); } @@ -33,7 +45,6 @@ { _autoFilter.Column(_column).TopBottomPart = XLTopBottomPart.Top; SetTopBottom(value, type); - } public void Bottom(Int32 value, XLTopBottomType type = XLTopBottomType.Items) @@ -42,224 +53,112 @@ SetTopBottom(value, type, false); } - private void SetTopBottom(Int32 value, XLTopBottomType type, Boolean takeTop = true) - { - _autoFilter.Enabled = true; - _autoFilter.Column(_column).FilterType = XLFilterType.TopBottom; - _autoFilter.Column(_column).TopBottomValue = value; - _autoFilter.Column(_column).TopBottomType = type; - var column = _autoFilter.Range.Column(_column); - IEnumerable values; - - if (takeTop) - { - if (type == XLTopBottomType.Items) - values = column.Column(2, column.CellCount()).CellsUsed().Where(c => c.DataType == XLCellValues.Number).Select(c => c.GetDouble()).OrderByDescending(d => d).Take(value).Distinct(); - else - { - var numerics = values = column.Column(2, column.CellCount()).CellsUsed().Where(c => c.DataType == XLCellValues.Number).Select(c => c.GetDouble()); - Int32 valsToTake = numerics.Count() * value / 100; - values = numerics.OrderByDescending(d => d).Take(valsToTake).Distinct(); - } - } - else - { - if (type == XLTopBottomType.Items) - values = column.Column(2, column.CellCount()).CellsUsed().Where(c => c.DataType == XLCellValues.Number).Select(c => c.GetDouble()).OrderBy(d => d).Take(value).Distinct(); - else - { - var numerics = values = column.Column(2, column.CellCount()).CellsUsed().Where(c => c.DataType == XLCellValues.Number).Select(c => c.GetDouble()); - Int32 valsToTake = numerics.Count() * value / 100; - values = numerics.OrderBy(d => d).Take(valsToTake).Distinct(); - } - } - - Clear(); - _autoFilter.Filters.Add(_column, new List()); - - Boolean addToList = true; - foreach (var row in _autoFilter.Range.Rows().Where(r => r.RowNumber() > 1)) - { - Boolean foundOne = false; - foreach (var 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 == XLCellValues.Number && condition(cell.GetDouble())) - { - row.WorksheetRow().Unhide(); - foundOne = true; - } - } - if (!foundOne) - row.WorksheetRow().Hide(); - - addToList = false; - //ApplyCustomFilter(val.ToString(), XLFilterOperator.Equal, s => ((string)s).Equals(val.ToString(), StringComparison.InvariantCultureIgnoreCase)); - } - } - public void AboveAverage() { ShowAverage(true); } + public void BelowAverage() { ShowAverage(false); } - private void ShowAverage(Boolean aboveAverage) + public IXLFilterConnector EqualTo(T value) where T: IComparable { - _autoFilter.Enabled = true; - _autoFilter.Column(_column).FilterType = XLFilterType.Dynamic; - _autoFilter.Column(_column).DynamicType = aboveAverage ? XLFilterDynamicType.AboveAverage : XLFilterDynamicType.BelowAverage; - var column = _autoFilter.Range.Column(_column); - Double average = column.Column(2, column.CellCount()).CellsUsed().Where(c => c.DataType == XLCellValues.Number).Select(c => c.GetDouble()).Average(); - IEnumerable values; - - if (aboveAverage) - values = column.Column(2, column.CellCount()).CellsUsed().Where(c => c.DataType == XLCellValues.Number).Select(c => c.GetDouble()).Where(c => c > average).Distinct(); - else - values = column.Column(2, column.CellCount()).CellsUsed().Where(c => c.DataType == XLCellValues.Number).Select(c => c.GetDouble()).Where(c => c < average).Distinct(); - - - Clear(); - _autoFilter.Filters.Add(_column, new List()); - - Boolean addToList = true; - foreach (var row in _autoFilter.Range.Rows().Where(r => r.RowNumber() > 1)) + if (typeof(T) == typeof(String)) { - Boolean foundOne = false; - foreach (var 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 == XLCellValues.Number && condition(cell.GetDouble())) - { - row.WorksheetRow().Unhide(); - foundOne = true; - } - } - if (!foundOne) - row.WorksheetRow().Hide(); - - addToList = false; - //ApplyCustomFilter(val.ToString(), XLFilterOperator.Equal, s => ((string)s).Equals(val.ToString(), StringComparison.InvariantCultureIgnoreCase)); + return ApplyCustomFilter(value, XLFilterOperator.Equal, + v => + v.ToString().Equals(value.ToString(), + StringComparison.InvariantCultureIgnoreCase)); } + + return ApplyCustomFilter(value, XLFilterOperator.Equal, + v => v.CastTo().CompareTo(value) == 0); } - public IXLFilterConnector EqualTo(T value) where T : IComparable + public IXLFilterConnector NotEqualTo(T value) where T: IComparable { if (typeof(T) == typeof(String)) - return ApplyCustomFilter(value, XLFilterOperator.Equal, v => v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase)); - else - return ApplyCustomFilter(value, XLFilterOperator.Equal, v => (v.CastTo() as IComparable).CompareTo(value) == 0); + { + return ApplyCustomFilter(value, XLFilterOperator.NotEqual, + v => + !v.ToString().Equals(value.ToString(), + StringComparison.InvariantCultureIgnoreCase)); + } + + return ApplyCustomFilter(value, XLFilterOperator.NotEqual, + v => v.CastTo().CompareTo(value) != 0); } - public IXLFilterConnector NotEqualTo(T value) where T : IComparable + public IXLFilterConnector GreaterThan(T value) where T: IComparable { - //return ApplyCustomFilter(value.ToString(), XLFilterOperator.NotEqual, s => !((string)s).Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase)); - - if (typeof(T) == typeof(String)) - return ApplyCustomFilter(value, XLFilterOperator.NotEqual, v => !v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase)); - else - return ApplyCustomFilter(value, XLFilterOperator.NotEqual, v => (v.CastTo() as IComparable).CompareTo(value) != 0); + return ApplyCustomFilter(value, XLFilterOperator.GreaterThan, + v => v.CastTo().CompareTo(value) > 0); } - public IXLFilterConnector GreaterThan(T value) where T : IComparable + public IXLFilterConnector LessThan(T value) where T: IComparable { - return ApplyCustomFilter(value, XLFilterOperator.GreaterThan, v => (v.CastTo() as IComparable).CompareTo(value) > 0); + return ApplyCustomFilter(value, XLFilterOperator.LessThan, + v => v.CastTo().CompareTo(value) < 0); } - public IXLFilterConnector LessThan(T value) where T : IComparable + public IXLFilterConnector EqualOrGreaterThan(T value) where T: IComparable { - return ApplyCustomFilter(value, XLFilterOperator.LessThan, v => (v.CastTo() as IComparable).CompareTo(value) < 0); + return ApplyCustomFilter(value, XLFilterOperator.EqualOrGreaterThan, + v => v.CastTo().CompareTo(value) >= 0); } - public IXLFilterConnector EqualOrGreaterThan(T value) where T : IComparable + public IXLFilterConnector EqualOrLessThan(T value) where T: IComparable { - return ApplyCustomFilter(value, XLFilterOperator.EqualOrGreaterThan, v => (v.CastTo() as IComparable).CompareTo(value) >= 0); + return ApplyCustomFilter(value, XLFilterOperator.EqualOrLessThan, + v => v.CastTo().CompareTo(value) <= 0); } - public IXLFilterConnector EqualOrLessThan(T value) where T : IComparable - { - return ApplyCustomFilter(value, XLFilterOperator.EqualOrLessThan, v => (v.CastTo() as IComparable).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); } - public IXLFilterConnector BeginsWith(String value) + public IXLFilterConnector BeginsWith(String value) { - return ApplyCustomFilter(value + "*", XLFilterOperator.Equal, s => ((string)s).StartsWith(value, StringComparison.InvariantCultureIgnoreCase)); + return ApplyCustomFilter(value + "*", XLFilterOperator.Equal, + s => ((string)s).StartsWith(value, StringComparison.InvariantCultureIgnoreCase)); } public IXLFilterConnector NotBeginsWith(String value) { - return ApplyCustomFilter(value + "*", XLFilterOperator.NotEqual, s => !((string)s).StartsWith(value, StringComparison.InvariantCultureIgnoreCase)); + return ApplyCustomFilter(value + "*", XLFilterOperator.NotEqual, + s => !((string)s).StartsWith(value, StringComparison.InvariantCultureIgnoreCase)); } public IXLFilterConnector EndsWith(String value) { - return ApplyCustomFilter("*" + value, XLFilterOperator.Equal, s => ((string)s).EndsWith(value, StringComparison.InvariantCultureIgnoreCase)); + return ApplyCustomFilter("*" + value, XLFilterOperator.Equal, + s => ((string)s).EndsWith(value, StringComparison.InvariantCultureIgnoreCase)); } public IXLFilterConnector NotEndsWith(String value) { - return ApplyCustomFilter("*" + value, XLFilterOperator.NotEqual, s => !((string)s).EndsWith(value, StringComparison.InvariantCultureIgnoreCase)); + return ApplyCustomFilter("*" + value, XLFilterOperator.NotEqual, + s => !((string)s).EndsWith(value, StringComparison.InvariantCultureIgnoreCase)); } public IXLFilterConnector Contains(String value) { - return ApplyCustomFilter("*" + value + "*", XLFilterOperator.Equal, s => ((string)s).ToLower().Contains(value.ToLower())); + return ApplyCustomFilter("*" + value + "*", XLFilterOperator.Equal, + s => ((string)s).ToLower().Contains(value.ToLower())); } + public IXLFilterConnector NotContains(String value) { - return ApplyCustomFilter("*" + value + "*", XLFilterOperator.Equal, s => !((string)s).ToLower().Contains(value.ToLower())); - } - - private IXLFilterConnector ApplyCustomFilter(T value, XLFilterOperator op, Func condition, XLFilterType filterType = XLFilterType.Custom) where T : IComparable - { - _autoFilter.Enabled = true; - if (filterType == XLFilterType.Custom) - { - Clear(); - _autoFilter.Filters.Add(_column, new List { new XLFilter { Value = value, Operator = op, Connector = XLConnector.Or, Condition = condition } }); - } - else - { - List filterList; - if (_autoFilter.Filters.TryGetValue(_column, out filterList)) - filterList.Add(new XLFilter { Value = value, Operator = op, Connector = XLConnector.Or, Condition = condition }); - else - _autoFilter.Filters.Add(_column, new List { new XLFilter { Value = value, Operator = op, Connector = XLConnector.Or, Condition = condition } }); - } - _autoFilter.Column(_column).FilterType = filterType; - Boolean isText = typeof(T) == typeof(String); - foreach (var row in _autoFilter.Range.Rows().Where(r => r.RowNumber() > 1)) - { - Boolean match = isText ? condition(row.Cell(_column).GetString()) : row.Cell(_column).DataType == XLCellValues.Number && condition(row.Cell(_column).GetDouble()); - if (match) - { - row.WorksheetRow().Unhide(); - } - else - row.WorksheetRow().Hide(); - } - - return new XLFilterConnector(_autoFilter, _column); + return ApplyCustomFilter("*" + value + "*", XLFilterOperator.Equal, + s => !((string)s).ToLower().Contains(value.ToLower())); } public XLFilterType FilterType { get; set; } @@ -270,5 +169,220 @@ public XLFilterDynamicType DynamicType { get; set; } public Double DynamicValue { get; set; } + + #endregion + + private void SetTopBottom(Int32 value, XLTopBottomType type, Boolean takeTop = true) + { + _autoFilter.Enabled = true; + _autoFilter.Column(_column).SetFilterType(XLFilterType.TopBottom) + .SetTopBottomValue(value) + .SetTopBottomType(type); + + var values = GetValues(value, type, takeTop); + + Clear(); + _autoFilter.Filters.Add(_column, new List()); + + Boolean addToList = true; + using (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 != XLCellValues.Number || !condition(cell.GetDouble())) continue; + row.WorksheetRow().Unhide().Dispose(); + foundOne = true; + } + if (!foundOne) + row.WorksheetRow().Hide().Dispose(); + + addToList = false; + } + } + } + + private IEnumerable GetValues(int value, XLTopBottomType type, bool takeTop) + { + using (var column = _autoFilter.Range.Column(_column)) + { + using (var subColumn = column.Column(2, column.CellCount())) + { + var cellsUsed = subColumn.CellsUsed(c => c.DataType == XLCellValues.Number); + if (takeTop) + { + if (type == XLTopBottomType.Items) + { + return cellsUsed.Select(c => c.GetDouble()).OrderByDescending(d => d).Take(value).Distinct(); + } + var numerics1 = cellsUsed.Select(c => c.GetDouble()); + Int32 valsToTake1 = numerics1.Count() * value / 100; + return numerics1.OrderByDescending(d => d).Take(valsToTake1).Distinct(); + } + + if (type == XLTopBottomType.Items) + { + return cellsUsed.Select(c => c.GetDouble()).OrderBy(d => d).Take(value).Distinct(); + } + + var numerics = cellsUsed.Select(c => c.GetDouble()); + Int32 valsToTake = numerics.Count() * value / 100; + return numerics.OrderBy(d => d).Take(valsToTake).Distinct(); + } + } + } + + private void ShowAverage(Boolean aboveAverage) + { + _autoFilter.Enabled = true; + _autoFilter.Column(_column).SetFilterType(XLFilterType.Dynamic) + .SetDynamicType(aboveAverage + ? XLFilterDynamicType.AboveAverage + : XLFilterDynamicType.BelowAverage); + var values = GetAverageValues(aboveAverage); + + + Clear(); + _autoFilter.Filters.Add(_column, new List()); + + Boolean addToList = true; + using (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 != XLCellValues.Number || !condition(cell.GetDouble())) continue; + row.WorksheetRow().Unhide().Dispose(); + foundOne = true; + } + + if (!foundOne) + row.WorksheetRow().Hide().Dispose(); + + addToList = false; + } + } + } + + private IEnumerable GetAverageValues(bool aboveAverage) + { + using (var column = _autoFilter.Range.Column(_column)) + { + using (var subColumn = column.Column(2, column.CellCount())) + { + Double average = subColumn.CellsUsed(c => c.DataType == XLCellValues.Number).Select(c => c.GetDouble()).Average(); + + if (aboveAverage) + { + return + subColumn.CellsUsed(c => c.DataType == XLCellValues.Number). + Select(c => c.GetDouble()).Where(c => c > average).Distinct(); + } + + return + subColumn.CellsUsed(c => c.DataType == XLCellValues.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 + { + _autoFilter.Enabled = true; + if (filterType == XLFilterType.Custom) + { + Clear(); + _autoFilter.Filters.Add(_column, + new List + { + new XLFilter + { + Value = value, + Operator = op, + Connector = XLConnector.Or, + Condition = condition + } + }); + } + else + { + List filterList; + if (_autoFilter.Filters.TryGetValue(_column, out filterList)) + filterList.Add(new XLFilter + {Value = value, Operator = op, Connector = XLConnector.Or, Condition = condition}); + else + { + _autoFilter.Filters.Add(_column, + new List + { + new XLFilter + { + Value = value, + Operator = op, + Connector = XLConnector.Or, + Condition = condition + } + }); + } + } + _autoFilter.Column(_column).FilterType = filterType; + Boolean isText = typeof(T) == typeof(String); + using (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 == XLCellValues.Number && + condition(row.Cell(_column).GetDouble()); + if (match) + row.WorksheetRow().Unhide().Dispose(); + else + row.WorksheetRow().Hide().Dispose(); + } + } + return new XLFilterConnector(_autoFilter, _column); + } + + 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; } + } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilterConnector.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilterConnector.cs index 2eca0fc..b563fd5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilterConnector.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilterConnector.cs @@ -5,16 +5,29 @@ namespace ClosedXML.Excel { - internal class XLFilterConnector: IXLFilterConnector + internal class XLFilterConnector : IXLFilterConnector { - XLAutoFilter _autoFilter; - Int32 _column; + private readonly XLAutoFilter _autoFilter; + private readonly Int32 _column; + public XLFilterConnector(XLAutoFilter autoFilter, Int32 column) { _autoFilter = autoFilter; _column = column; } - public IXLCustomFilteredColumn And { get { return new XLCustomFilteredColumn(_autoFilter, _column, XLConnector.And); } } - public IXLCustomFilteredColumn Or { get { return new XLCustomFilteredColumn(_autoFilter, _column, XLConnector.Or); } } + + #region IXLFilterConnector Members + + public IXLCustomFilteredColumn And + { + get { return new XLCustomFilteredColumn(_autoFilter, _column, XLConnector.And); } + } + + public IXLCustomFilteredColumn Or + { + get { return new XLCustomFilteredColumn(_autoFilter, _column, XLConnector.Or); } + } + + #endregion } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs index 18c9210..72479d0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs @@ -1,19 +1,21 @@ using System; using System.Linq; + namespace ClosedXML.Excel { - using System.Collections.Generic; - - internal class XLFilteredColumn: IXLFilteredColumn + internal class XLFilteredColumn : IXLFilteredColumn { - XLAutoFilter _autoFilter; - Int32 _column; + private readonly XLAutoFilter _autoFilter; + private readonly Int32 _column; + public XLFilteredColumn(XLAutoFilter autoFilter, Int32 column) { _autoFilter = autoFilter; _column = column; } + #region IXLFilteredColumn Members + public IXLFilteredColumn AddFilter(T value) where T: IComparable { Func condition; @@ -25,19 +27,35 @@ } else { - condition = v => (v.CastTo() as IComparable).CompareTo(value) == 0; + condition = v => v.CastTo().CompareTo(value) == 0; isText = false; } - _autoFilter.Filters[_column].Add(new XLFilter { Value = value, Condition = condition, Operator = XLFilterOperator.Equal, Connector = XLConnector.Or }); - foreach (var row in _autoFilter.Range.Rows().Where(r => r.RowNumber() > 1)) + _autoFilter.Filters[_column].Add(new XLFilter + { + Value = value, + Condition = condition, + Operator = XLFilterOperator.Equal, + Connector = XLConnector.Or + }); + + using (var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount())) { - if ((isText && condition(row.Cell(_column).GetString())) || ( - !isText && row.Cell(_column).DataType == XLCellValues.Number && condition(row.Cell(_column).GetValue())) - ) - row.WorksheetRow().Unhide(); + foreach (IXLRangeRow row in rows) + { + if ((isText && condition(row.Cell(_column).GetString())) || ( + !isText && + row.Cell(_column).DataType == + XLCellValues.Number && + condition( + row.Cell(_column).GetValue())) + ) + row.WorksheetRow().Unhide().Dispose(); + } } return this; } + + #endregion } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs index ff5808f..71a72ce 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCell.cs @@ -11,11 +11,11 @@ using System.Text.RegularExpressions; #if NET4 using System.ComponentModel.DataAnnotations; + #endif internal class XLCell : IXLCell, IXLStylized { - public Boolean StyleChanged { get; set; } public static readonly DateTime BaseDate = new DateTime(1899, 12, 30); private static Dictionary _formatCodes; @@ -26,10 +26,11 @@ public static readonly Regex A1SimpleRegex = new Regex( // @"(?<=\W)" // Start with non word - @"(?" // Start Group to pick + @"(?" // Start Group to pick + @"(?" // Start Sheet Name, optional + @"(" - + @"\'([^\[\]\*/\\\?:\']+|\'\')\'" // Sheet name with special characters, surrounding apostrophes are required + + @"\'([^\[\]\*/\\\?:\']+|\'\')\'" + // Sheet name with special characters, surrounding apostrophes are required + @"|" + @"\'?\w+\'?" // Sheet name with letters and numbers, surrounding apostrophes are optional + @")" @@ -59,29 +60,29 @@ + @"|(?<=\W)([Rr]\[?-?\d{0,7}\]?:[Rr]\[?-?\d{0,7}\]?)(?=\W)" // R:R + @"|(?<=\W)([Cc]\[?-?\d{0,5}\]?:[Cc]\[?-?\d{0,5}\]?)(?=\W)"); // C:C + private static readonly Regex utfPattern = new Regex(@"(?() { if (!StringExtensions.IsNullOrWhiteSpace(FormulaA1)) @@ -231,12 +262,12 @@ if (typeof(T) == typeof(String)) { - var valToUse = Value.ToString(); + string valToUse = Value.ToString(); if (!utfPattern.Match(valToUse).Success) return (T)Convert.ChangeType(Value, typeof(T)); else { - StringBuilder sb = new StringBuilder(); + var sb = new StringBuilder(); Int32 lastIndex = 0; foreach (Match match in utfPattern.Matches(valToUse)) { @@ -286,7 +317,7 @@ public string GetFormattedString() { if (FormulaA1.Length > 0) return String.Empty; - + string cValue = _cellValue; if (_dataType == XLCellValues.Boolean) @@ -348,7 +379,7 @@ } if (_worksheet.Workbook.WorksheetsInternal.Any( - w => String.Compare(w.Name, sName, true)==0) + w => String.Compare(w.Name, sName, true) == 0) && ExcelHelper.IsValidA1Address(cAddress) ) return _worksheet.Workbook.Worksheet(sName).Cell(cAddress).Value; @@ -442,12 +473,13 @@ { if (!isDataTable) isDataTable = true; - + if (!hasTitles) { - foreach (string fieldName in from DataColumn column in ((DataRow)m).Table.Columns select StringExtensions.IsNullOrWhiteSpace(column.Caption) - ? column.ColumnName - : column.Caption) + foreach (string fieldName in from DataColumn column in ((DataRow)m).Table.Columns + select StringExtensions.IsNullOrWhiteSpace(column.Caption) + ? column.ColumnName + : column.Caption) { SetValue(fieldName, fRo, co); co++; @@ -485,7 +517,7 @@ for (int i = 0; i < fieldCount; i++) { - SetValue(record[i] , ro, co); + SetValue(record[i], ro, co); co++; } } @@ -648,22 +680,11 @@ return null; } - private IXLStyle GetStyle() - { - //return _style ?? (_style = new XLStyle(this, Worksheet.Workbook.GetStyleById(_styleCacheId))); - if (_style != null) - return _style; - - return _style = new XLStyle(this, Worksheet.Workbook.GetStyleById(_styleCacheId)); - } public IXLStyle Style { get { return GetStyle(); } - set - { - SetStyle(value); - } + set { SetStyle(value); } } public IXLCell SetDataType(XLCellValues dataType) @@ -713,9 +734,7 @@ } var style = GetStyleForRead(); if (style.NumberFormat.Format == String.Empty && style.NumberFormat.NumberFormatId == 0) - { Style.NumberFormat.NumberFormatId = _cellValue.Contains('.') ? 22 : 14; - } } else if (value == XLCellValues.TimeSpan) { @@ -784,7 +803,7 @@ DataValidation.Clear(); SetStyle(Worksheet.Style); } - + return this; } @@ -805,8 +824,8 @@ _formulaA1 = GetFormulaA1(_formulaR1C1); return FormulaA1; } - - return String.Empty; + + return String.Empty; } if (_formulaA1.Trim()[0] == '=') @@ -880,10 +899,6 @@ } } - public XLDataValidation DataValidation - { - get { return AsRange().DataValidation; } - } IXLDataValidation IXLCell.DataValidation { get { return DataValidation; } @@ -942,7 +957,9 @@ if (_richText == null) { var style = GetStyleForRead(); - _richText = _cellValue.Length == 0 ? new XLRichText(style.Font) : new XLRichText(GetFormattedString(), style.Font); + _richText = _cellValue.Length == 0 + ? new XLRichText(style.Font) + : new XLRichText(GetFormattedString(), style.Font); _dataType = XLCellValues.Text; } @@ -956,21 +973,9 @@ get { return _richText != null; } } - IXLComment IXLCell.Comment { get { return Comment; } } - internal XLComment Comment + IXLComment IXLCell.Comment { - get - { - if (_comment == null) - { - // MS Excel uses Tahoma 8 Swiss no matter what current style font - // var style = GetStyleForRead(); - var defaultFont = new XLFont() { FontName = "Tahoma", FontSize = 8, FontFamilyNumbering = XLFontFamilyNumberingValues.Swiss }; - _comment = new XLComment(this, defaultFont); - } - - return _comment; - } + get { return Comment; } } public bool HasComment @@ -978,10 +983,6 @@ get { return _comment != null; } } - public void DeleteComment() { - _comment = null; - } - public Boolean IsMerged() { return Worksheet.Internals.MergedRanges.Any(AsRange().Intersects); @@ -1003,10 +1004,22 @@ return true; } + public IXLColumn WorksheetColumn() + { + return Worksheet.Column(Address.ColumnNumber); + } + + public IXLRow WorksheetRow() + { + return Worksheet.Row(Address.RowNumber); + } + #endregion #region IXLStylized Members + public Boolean StyleChanged { get; set; } + public IEnumerable Styles { get @@ -1036,6 +1049,25 @@ #endregion + public XLRange AsRange() + { + return _worksheet.Range(Address, Address); + } + + private IXLStyle GetStyle() + { + //return _style ?? (_style = new XLStyle(this, Worksheet.Workbook.GetStyleById(_styleCacheId))); + if (_style != null) + return _style; + + return _style = new XLStyle(this, Worksheet.Workbook.GetStyleById(_styleCacheId)); + } + + public void DeleteComment() + { + _comment = null; + } + private bool IsDateFormat() { var style = GetStyleForRead(); @@ -1085,7 +1117,6 @@ if (asRange != null) { - if (!(asRange is XLRow || asRange is XLColumn)) { Int32 maxRows = asRange.RowCount(); @@ -1105,15 +1136,24 @@ var rangesToMerge = (from mergedRange in (asRange.Worksheet).Internals.MergedRanges where asRange.Contains(mergedRange) - let initialRo = Address.RowNumber + (mergedRange.RangeAddress.FirstAddress.RowNumber - asRange.RangeAddress.FirstAddress.RowNumber) - let initialCo = Address.ColumnNumber + (mergedRange.RangeAddress.FirstAddress.ColumnNumber - asRange.RangeAddress.FirstAddress.ColumnNumber) - select Worksheet.Range(initialRo, initialCo, initialRo + mergedRange.RowCount() - 1, initialCo + mergedRange.ColumnCount() - 1)).Cast().ToList(); + let initialRo = + Address.RowNumber + + (mergedRange.RangeAddress.FirstAddress.RowNumber - + asRange.RangeAddress.FirstAddress.RowNumber) + let initialCo = + Address.ColumnNumber + + (mergedRange.RangeAddress.FirstAddress.ColumnNumber - + asRange.RangeAddress.FirstAddress.ColumnNumber) + select + Worksheet.Range(initialRo, initialCo, initialRo + mergedRange.RowCount() - 1, + initialCo + mergedRange.ColumnCount() - 1)).Cast(). + ToList(); rangesToMerge.ForEach(r => r.Merge()); return true; } - - return false; + + return false; } private bool SetEnumerable(object collectionObject) @@ -1147,9 +1187,7 @@ string val = value == null ? String.Empty : value.ToString(); _richText = null; if (val.Length == 0) - { _dataType = XLCellValues.Text; - } else { double dTest; @@ -1183,9 +1221,7 @@ _dataType = XLCellValues.DateTime; if (style.NumberFormat.Format == String.Empty && style.NumberFormat.NumberFormatId == 0) - { Style.NumberFormat.NumberFormatId = dtTest.Date == dtTest ? 14 : 22; - } val = dtTest.ToOADate().ToString(); } @@ -1421,9 +1457,7 @@ rowNumber += rowsToShift; int rowDiff = rowNumber - Address.RowNumber; if (rowDiff != 0 || fixedRow) - { rowPart = fixedRow ? String.Format("R{0}", rowNumber) : String.Format("R[{0}]", rowDiff); - } else rowPart = "R"; @@ -1436,9 +1470,7 @@ columnNumber += columnsToShift; int columnDiff = columnNumber - Address.ColumnNumber; if (columnDiff != 0 || fixedColumn) - { columnPart = fixedColumn ? String.Format("C{0}", columnNumber) : String.Format("C[{0}]", columnDiff); - } else columnPart = "C"; @@ -1472,10 +1504,12 @@ SettingHyperlink = false; } - var asRange = source.AsRange(); - if (source.Worksheet.DataValidations.Any(dv => dv.Ranges.Contains(asRange))) - DataValidation.CopyFrom(source.DataValidation); - + using (var asRange = source.AsRange()) + { + //if (DataValidation != source.DataValidation && source.Worksheet.DataValidations.Any(dv => dv.Ranges.Contains(asRange))) + if (source.Worksheet.DataValidations.Any(dv => dv.Ranges.Contains(asRange))) + DataValidation.CopyFrom(source.DataValidation); + } return this; } @@ -1484,11 +1518,12 @@ _formulaA1 = ShiftFormulaRows(FormulaA1, Worksheet, shiftedRange, rowsShifted); } - internal static String ShiftFormulaRows(String formulaA1, XLWorksheet worksheetInAction, XLRange shiftedRange, int rowsShifted) + internal static String ShiftFormulaRows(String formulaA1, XLWorksheet worksheetInAction, XLRange shiftedRange, + int rowsShifted) { if (StringExtensions.IsNullOrWhiteSpace(formulaA1)) return String.Empty; - string value = formulaA1;// ">" + formulaA1 + "<"; + string value = formulaA1; // ">" + formulaA1 + "<"; var regex = A1SimpleRegex; @@ -1709,7 +1744,8 @@ _formulaA1 = ShiftFormulaColumns(FormulaA1, Worksheet, shiftedRange, columnsShifted); } - internal static String ShiftFormulaColumns(String formulaA1, XLWorksheet worksheetInAction, XLRange shiftedRange, int columnsShifted) + internal static String ShiftFormulaColumns(String formulaA1, XLWorksheet worksheetInAction, XLRange shiftedRange, + int columnsShifted) { if (StringExtensions.IsNullOrWhiteSpace(formulaA1)) return String.Empty; @@ -1944,117 +1980,125 @@ // -- + private XLCell CellShift(Int32 rowsToShift, Int32 columnsToShift) + { + return Worksheet.Cell(Address.RowNumber + rowsToShift, Address.ColumnNumber + columnsToShift); + } + + private static String GetFieldName(Object[] customAttributes) + { +#if NET4 + var attribute = customAttributes.FirstOrDefault(a => a is DisplayAttribute); + return attribute != null ? (attribute as DisplayAttribute).Name : null; +#else + return null; +#endif + } + #region Nested type: FormulaConversionType private enum FormulaConversionType { A1ToR1C1, R1C1ToA1 - } ; + }; #endregion #region XLCell Above - public XLCell CellAbove() - { - return CellAbove(1); - } + IXLCell IXLCell.CellAbove() { return CellAbove(); } - public XLCell CellAbove(Int32 step) - { - return CellShift(step * -1, 0); - } + IXLCell IXLCell.CellAbove(Int32 step) { return CellAbove(step); } + + public XLCell CellAbove() + { + return CellAbove(1); + } + + public XLCell CellAbove(Int32 step) + { + return CellShift(step * -1, 0); + } + #endregion #region XLCell Below - public XLCell CellBelow() - { - return CellBelow(1); - } + IXLCell IXLCell.CellBelow() { return CellBelow(); } - public XLCell CellBelow(Int32 step) - { - return CellShift(step, 0); - } + IXLCell IXLCell.CellBelow(Int32 step) { return CellBelow(step); } + + public XLCell CellBelow() + { + return CellBelow(1); + } + + public XLCell CellBelow(Int32 step) + { + return CellShift(step, 0); + } + #endregion #region XLCell Left - public XLCell CellLeft() - { - return CellLeft(1); - } + IXLCell IXLCell.CellLeft() { return CellLeft(); } - public XLCell CellLeft(Int32 step) - { - return CellShift(0, step * -1); - } + IXLCell IXLCell.CellLeft(Int32 step) { return CellLeft(step); } + + public XLCell CellLeft() + { + return CellLeft(1); + } + + public XLCell CellLeft(Int32 step) + { + return CellShift(0, step * -1); + } + #endregion #region XLCell Right - public XLCell CellRight() - { - return CellRight(1); - } + IXLCell IXLCell.CellRight() { return CellRight(); } - public XLCell CellRight(Int32 step) - { - return CellShift(0, step); - } + IXLCell IXLCell.CellRight(Int32 step) { return CellRight(step); } + + public XLCell CellRight() + { + return CellRight(1); + } + + public XLCell CellRight(Int32 step) + { + return CellShift(0, step); + } + #endregion - - private XLCell CellShift(Int32 rowsToShift, Int32 columnsToShift) - { - return Worksheet.Cell(Address.RowNumber + rowsToShift, Address.ColumnNumber + columnsToShift); - } - - public IXLColumn WorksheetColumn() - { - return Worksheet.Column(Address.ColumnNumber); - } - public IXLRow WorksheetRow() - { - return Worksheet.Row(Address.RowNumber); - } - - private String GetFieldName(Object[] customAttributes) - { -#if NET4 - var displayAttributes = customAttributes.Where(a => a is DisplayAttribute).Select(a => (a as DisplayAttribute).Name); - if (displayAttributes.Any()) - return displayAttributes.Single(); - else - return null; -#else - return null; -#endif - } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellsCollection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellsCollection.cs index d53276a..420fb9a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellsCollection.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Cells/XLCellsCollection.cs @@ -88,8 +88,9 @@ //_cells[row, column] = null; } - public IEnumerable GetCells(Int32 rowStart, Int32 columnStart, - Int32 rowEnd, Int32 columnEnd) + internal IEnumerable GetCells(Int32 rowStart, Int32 columnStart, + Int32 rowEnd, Int32 columnEnd, + Func predicate = null) { int finalRow = rowEnd > MaxRowUsed ? MaxRowUsed : rowEnd; int finalColumn = columnEnd > MaxColumnUsed ? MaxColumnUsed : columnEnd; @@ -98,43 +99,65 @@ for (int co = columnStart; co <= finalColumn; co++) { XLCell cell; - if (_cellsDictionary.TryGetValue(new XLSheetPoint(ro, co), out cell)) + if (_cellsDictionary.TryGetValue(new XLSheetPoint(ro, co), out cell) + && (predicate == null || predicate(cell))) + yield return cell; + } + } + } + + + internal IEnumerable GetCellsUsed(Int32 rowStart, Int32 columnStart, + Int32 rowEnd, Int32 columnEnd, + Boolean includeFormats, Func predicate = null) + { + int finalRow = rowEnd > MaxRowUsed ? MaxRowUsed : rowEnd; + int finalColumn = columnEnd > MaxColumnUsed ? MaxColumnUsed : columnEnd; + for (int ro = rowStart; ro <= finalRow; ro++) + { + for (int co = columnStart; co <= finalColumn; co++) + { + XLCell cell; + if (_cellsDictionary.TryGetValue(new XLSheetPoint(ro, co), out cell) + && !cell.IsEmpty(includeFormats) + && (predicate == null || predicate(cell))) yield return cell; } } } public XLSheetPoint FirstPointUsed(Int32 rowStart, Int32 columnStart, - Int32 rowEnd, Int32 columnEnd, Boolean includeFormats) + Int32 rowEnd, Int32 columnEnd, Boolean includeFormats = false, Func predicate = null) { int finalRow = rowEnd > MaxRowUsed ? MaxRowUsed : rowEnd; int finalColumn = columnEnd > MaxColumnUsed ? MaxColumnUsed : columnEnd; - var firstRow = FirstRowUsed(rowStart, columnStart, finalRow, finalColumn, includeFormats); + var firstRow = FirstRowUsed(rowStart, columnStart, finalRow, finalColumn, includeFormats, predicate); if (firstRow == 0) return new XLSheetPoint(0,0); - var firstColumn = FirstColumnUsed(rowStart, columnStart, finalRow, finalColumn, includeFormats); + var firstColumn = FirstColumnUsed(rowStart, columnStart, finalRow, finalColumn, includeFormats, predicate); if (firstColumn == 0) return new XLSheetPoint(0, 0); return new XLSheetPoint(firstRow, firstColumn); } public XLSheetPoint LastPointUsed(Int32 rowStart, Int32 columnStart, - Int32 rowEnd, Int32 columnEnd, Boolean includeFormats) + Int32 rowEnd, Int32 columnEnd, Boolean includeFormats = false, Func predicate = null) { int finalRow = rowEnd > MaxRowUsed ? MaxRowUsed : rowEnd; int finalColumn = columnEnd > MaxColumnUsed ? MaxColumnUsed : columnEnd; - var firstRow = LastRowUsed(rowStart, columnStart, finalRow, finalColumn, includeFormats); + var firstRow = LastRowUsed(rowStart, columnStart, finalRow, finalColumn, includeFormats, predicate); if (firstRow == 0) return new XLSheetPoint(0, 0); - var firstColumn = LastColumnUsed(rowStart, columnStart, finalRow, finalColumn, includeFormats); + var firstColumn = LastColumnUsed(rowStart, columnStart, finalRow, finalColumn, includeFormats, predicate); if (firstColumn == 0) return new XLSheetPoint(0, 0); return new XLSheetPoint(firstRow, firstColumn); } - public int FirstRowUsed(int rowStart, int columnStart, int rowEnd, int columnEnd, Boolean includeFormats) + public int FirstRowUsed(int rowStart, int columnStart, int rowEnd, int columnEnd, Boolean includeFormats, + Func predicate = null) { int finalRow = rowEnd > MaxRowUsed ? MaxRowUsed : rowEnd; int finalColumn = columnEnd > MaxColumnUsed ? MaxColumnUsed : columnEnd; @@ -144,14 +167,15 @@ { XLCell cell; if (_cellsDictionary.TryGetValue(new XLSheetPoint(ro, co), out cell) - && !cell.IsEmpty(includeFormats)) + && !cell.IsEmpty(includeFormats) + && (predicate == null || predicate(cell))) return ro; } } return 0; } - public int FirstColumnUsed(int rowStart, int columnStart, int rowEnd, int columnEnd, Boolean includeFormats) + public int FirstColumnUsed(int rowStart, int columnStart, int rowEnd, int columnEnd, Boolean includeFormats, Func predicate = null) { int finalRow = rowEnd > MaxRowUsed ? MaxRowUsed : rowEnd; int finalColumn = columnEnd > MaxColumnUsed ? MaxColumnUsed : columnEnd; @@ -161,7 +185,8 @@ { XLCell cell; if (_cellsDictionary.TryGetValue(new XLSheetPoint(ro, co), out cell) - && !cell.IsEmpty(includeFormats)) + && !cell.IsEmpty(includeFormats) + && (predicate == null || predicate(cell))) return co; } } @@ -169,7 +194,7 @@ } - public int LastRowUsed(int rowStart, int columnStart, int rowEnd, int columnEnd, Boolean includeFormats) + public int LastRowUsed(int rowStart, int columnStart, int rowEnd, int columnEnd, Boolean includeFormats, Func predicate = null) { int finalRow = rowEnd > MaxRowUsed ? MaxRowUsed : rowEnd; int finalColumn = columnEnd > MaxColumnUsed ? MaxColumnUsed : columnEnd; @@ -179,14 +204,15 @@ { XLCell cell; if (_cellsDictionary.TryGetValue(new XLSheetPoint(ro, co), out cell) - && !cell.IsEmpty(includeFormats)) + && !cell.IsEmpty(includeFormats) + && (predicate == null || predicate(cell))) return ro; } } return 0; } - public int LastColumnUsed(int rowStart, int columnStart, int rowEnd, int columnEnd, Boolean includeFormats) + public int LastColumnUsed(int rowStart, int columnStart, int rowEnd, int columnEnd, Boolean includeFormats, Func predicate = null) { int finalRow = rowEnd > MaxRowUsed ? MaxRowUsed : rowEnd; int finalColumn = columnEnd > MaxColumnUsed ? MaxColumnUsed : columnEnd; @@ -196,7 +222,8 @@ { XLCell cell; if (_cellsDictionary.TryGetValue(new XLSheetPoint(ro, co), out cell) - && !cell.IsEmpty(includeFormats)) + && !cell.IsEmpty(includeFormats) + && (predicate == null || predicate(cell))) return co; } } @@ -289,14 +316,14 @@ return GetCells(1, 1, MaxRowUsed, MaxColumnUsed); } - internal IEnumerable GetCells(Func predicate) + internal IEnumerable GetCells(Func predicate) { for (int ro = 1; ro <= MaxRowUsed; ro++) { for (int co = 1; co <= MaxColumnUsed; co++) { - var cell = GetCell(ro, co); - if (cell != null && predicate(cell)) + XLCell cell; + if (_cellsDictionary.TryGetValue(new XLSheetPoint(ro, co), out cell) && predicate(cell)) yield return cell; } } @@ -309,9 +336,10 @@ public Int32 MinRowInColumn(Int32 column) { + XLCell cell; for (int row = 1; row <= MaxRowUsed; row++) { - if (GetCell(row, column) != null) + if (_cellsDictionary.TryGetValue(new XLSheetPoint(row, column), out cell)) return row; } @@ -320,9 +348,10 @@ public Int32 MaxRowInColumn(Int32 column) { + XLCell cell; for (int row = MaxRowUsed; row >= 1; row--) { - if (GetCell(row, column) != null) + if (_cellsDictionary.TryGetValue(new XLSheetPoint(row, column), out cell)) return row; } @@ -331,9 +360,10 @@ public Int32 MinColumnInRow(Int32 row) { + XLCell cell; for (int column = 1; column <= MaxColumnUsed; column++) { - if (GetCell(row, column) != null) + if (_cellsDictionary.TryGetValue(new XLSheetPoint(row, column), out cell)) return column; } @@ -342,9 +372,10 @@ public Int32 MaxColumnInRow(Int32 row) { + XLCell cell; for (int column = MaxColumnUsed; column >= 1; column--) { - if (GetCell(row, column) != null) + if (_cellsDictionary.TryGetValue(new XLSheetPoint(row, column), out cell)) return column; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs index 547b75c..990ed2d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs @@ -85,10 +85,10 @@ /// /// Hides this column. /// - void Hide(); + IXLColumn Hide(); /// Unhides this column. - void Unhide(); + IXLColumn Unhide(); /// /// Gets a value indicating whether this column is hidden or not. @@ -109,45 +109,45 @@ /// /// Adds this column to the next outline level (Increments the outline level for this column by 1). /// - void Group(); + IXLColumn Group(); /// /// Adds this column to the next outline level (Increments the outline level for this column by 1). /// /// If set to true the column will be shown collapsed. - void Group(Boolean collapse); + IXLColumn Group(Boolean collapse); /// /// Sets outline level for this column. /// /// The outline level. - void Group(Int32 outlineLevel); + IXLColumn Group(Int32 outlineLevel); /// /// Sets outline level for this column. /// /// The outline level. /// If set to true the column will be shown collapsed. - void Group(Int32 outlineLevel, Boolean collapse); + IXLColumn Group(Int32 outlineLevel, Boolean collapse); /// /// Adds this column to the previous outline level (decrements the outline level for this column by 1). /// - void Ungroup(); + IXLColumn Ungroup(); /// /// Adds this column to the previous outline level (decrements the outline level for this column by 1). /// /// If set to true it will remove this column from all outline levels. - void Ungroup(Boolean fromAll); + IXLColumn Ungroup(Boolean fromAll); /// /// Show this column as collapsed. /// - void Collapse(); + IXLColumn Collapse(); /// Expands this column (if it's collapsed). - void Expand(); + IXLColumn Expand(); Int32 CellCount(); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs index 5f85030..9eef5b1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs @@ -3,7 +3,7 @@ namespace ClosedXML.Excel { - public interface IXLColumns: IEnumerable + public interface IXLColumns: IEnumerable, IDisposable { /// /// Sets the width of all columns. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs index 2c7eb6d..b7fdf51 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs @@ -11,7 +11,7 @@ private bool _collapsed; private bool _isHidden; private int _outlineLevel; - + private Double _width; #endregion @@ -28,7 +28,7 @@ IsReference = xlColumnParameters.IsReference; if (IsReference) - Worksheet.RangeShiftedColumns += WorksheetRangeShiftedColumns; + SubscribeToShiftedColumns(WorksheetRangeShiftedColumns); else { SetStyle(xlColumnParameters.DefaultStyleId); @@ -44,6 +44,8 @@ { _width = column._width; IsReference = column.IsReference; + if (IsReference) + SubscribeToShiftedColumns(WorksheetRangeShiftedColumns); _collapsed = column._collapsed; _isHidden = column._isHidden; _outlineLevel = column._outlineLevel; @@ -52,7 +54,6 @@ #endregion - public Boolean IsReference { get; private set; } public override IEnumerable Styles @@ -65,7 +66,7 @@ int column = ColumnNumber(); - foreach (var cell in Worksheet.Internals.CellsCollection.GetCellsInColumn(column)) + foreach (XLCell cell in Worksheet.Internals.CellsCollection.GetCellsInColumn(column)) yield return cell.Style; UpdatingStyle = false; @@ -78,9 +79,9 @@ { get { - return IsReference ? - Worksheet.Internals.ColumnsCollection[ColumnNumber()].InnerStyle : - GetStyle(); + return IsReference + ? Worksheet.Internals.ColumnsCollection[ColumnNumber()].InnerStyle + : GetStyle(); } set { @@ -107,10 +108,7 @@ public Double Width { - get - { - return IsReference ? Worksheet.Internals.ColumnsCollection[ColumnNumber()].Width : _width; - } + get { return IsReference ? Worksheet.Internals.ColumnsCollection[ColumnNumber()].Width : _width; } set { if (IsReference) @@ -123,7 +121,11 @@ public void Delete() { int columnNumber = ColumnNumber(); - AsRange().Delete(XLShiftDeletedCells.ShiftCellsLeft); + using (var asRange = AsRange()) + { + asRange.Delete(XLShiftDeletedCells.ShiftCellsLeft); + } + Worksheet.Internals.ColumnsCollection.Remove(columnNumber); var columnsToMove = new List(); columnsToMove.AddRange( @@ -159,6 +161,7 @@ { return CellsUsed(true); } + public IXLCells Cells(Int32 firstRow, Int32 lastRow) { return Cells(firstRow + ":" + lastRow); @@ -166,10 +169,7 @@ public override IXLStyle Style { - get - { - return IsReference ? Worksheet.Internals.ColumnsCollection[ColumnNumber()].Style : GetStyle(); - } + get { return IsReference ? Worksheet.Internals.ColumnsCollection[ColumnNumber()].Style : GetStyle(); } set { if (IsReference) @@ -210,8 +210,14 @@ { int columnNum = ColumnNumber(); Worksheet.Internals.ColumnsCollection.ShiftColumnsRight(columnNum + 1, numberOfColumns); - var range = (XLRange)Worksheet.Column(columnNum).AsRange(); - range.InsertColumnsAfter(true, numberOfColumns); + using (var column = Worksheet.Column(columnNum)) + { + using (var asRange = column.AsRange()) + { + asRange.InsertColumnsAfter(true, numberOfColumns).Dispose(); + } + } + return Worksheet.Columns(columnNum + 1, columnNum + numberOfColumns); } @@ -219,16 +225,16 @@ { int columnNum = ColumnNumber(); Worksheet.Internals.ColumnsCollection.ShiftColumnsRight(columnNum, numberOfColumns); - // We can't use this.AsRange() because we've shifted the columns - // and we want to use the old columnNum. - var range = (XLRange)Worksheet.Column(columnNum).AsRange(); - range.InsertColumnsBefore(true, numberOfColumns); - return Worksheet.Columns(columnNum, columnNum + numberOfColumns - 1); - } - public override IXLRange AsRange() - { - return Range(1, 1, ExcelHelper.MaxRowNumber, 1); + using (var column = Worksheet.Column(columnNum)) + { + using (var asRange = column.AsRange()) + { + asRange.InsertColumnsBefore(true, numberOfColumns).Dispose(); + } + } + + return Worksheet.Columns(columnNum, columnNum + numberOfColumns - 1); } public IXLColumn AdjustToContents() @@ -420,22 +426,21 @@ } - public void Hide() + public IXLColumn Hide() { IsHidden = true; + return this; } - public void Unhide() + public IXLColumn Unhide() { IsHidden = false; + return this; } public Boolean IsHidden { - get - { - return IsReference ? Worksheet.Internals.ColumnsCollection[ColumnNumber()].IsHidden : _isHidden; - } + get { return IsReference ? Worksheet.Internals.ColumnsCollection[ColumnNumber()].IsHidden : _isHidden; } set { if (IsReference) @@ -464,36 +469,38 @@ } } - public void Group() + public IXLColumn Group() { - Group(false); + return Group(false); } - public void Group(Boolean collapse) + public IXLColumn Group(Boolean collapse) { if (OutlineLevel < 8) OutlineLevel += 1; Collapsed = collapse; + return this; } - public void Group(Int32 outlineLevel) + public IXLColumn Group(Int32 outlineLevel) { - Group(outlineLevel, false); + return Group(outlineLevel, false); } - public void Group(Int32 outlineLevel, Boolean collapse) + public IXLColumn Group(Int32 outlineLevel, Boolean collapse) { OutlineLevel = outlineLevel; Collapsed = collapse; + return this; } - public void Ungroup() + public IXLColumn Ungroup() { - Ungroup(false); + return Ungroup(false); } - public void Ungroup(Boolean ungroupFromAll) + public IXLColumn Ungroup(Boolean ungroupFromAll) { if (ungroupFromAll) OutlineLevel = 0; @@ -502,18 +509,19 @@ if (OutlineLevel > 0) OutlineLevel -= 1; } + return this; } - public void Collapse() + public IXLColumn Collapse() { Collapsed = true; - Hide(); + return Hide(); } - public void Expand() + public IXLColumn Expand() { Collapsed = false; - Unhide(); + return Unhide(); } public Int32 CellCount() @@ -521,27 +529,34 @@ return RangeAddress.LastAddress.ColumnNumber - RangeAddress.FirstAddress.ColumnNumber + 1; } - public IXLColumn Sort(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) + public IXLColumn Sort(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, + Boolean ignoreBlanks = true) { - base.Sort(1, sortOrder, matchCase, ignoreBlanks); + Sort(1, sortOrder, matchCase, ignoreBlanks); return this; } IXLRangeColumn IXLColumn.CopyTo(IXLCell target) { - return AsRange().CopyTo(target).Column(1); + using (var asRange = AsRange()) + using (var copy = asRange.CopyTo(target)) + return copy.Column(1); } IXLRangeColumn IXLColumn.CopyTo(IXLRangeBase target) { - return AsRange().CopyTo(target).Column(1); + using (var asRange = AsRange()) + using (var copy = asRange.CopyTo(target)) + return copy.Column(1); } public IXLColumn CopyTo(IXLColumn column) { column.Clear(); - AsRange().CopyTo(column).Column(1); + using (var asRange = AsRange()) + using (var copy = asRange.CopyTo(column)) + copy.Column(1); var newColumn = (XLColumn)column; @@ -565,7 +580,8 @@ var retVal = new XLRangeColumns(); var columnPairs = columns.Split(','); foreach (string pair in columnPairs) - AsRange().Columns(pair.Trim()).ForEach(retVal.Add); + using (var asRange = AsRange()) + asRange.Columns(pair.Trim()).ForEach(retVal.Add); return retVal; } @@ -584,8 +600,18 @@ return this; } + public IXLRangeColumn ColumnUsed(Boolean includeFormats = false) + { + return Column(FirstCellUsed(includeFormats), LastCellUsed(includeFormats)); + } + #endregion + public override XLRange AsRange() + { + return Range(1, 1, ExcelHelper.MaxRowNumber, 1); + } + private void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) { if (range.RangeAddress.FirstAddress.ColumnNumber <= ColumnNumber()) @@ -694,10 +720,5 @@ } #endregion - - public IXLRangeColumn ColumnUsed(Boolean includeFormats = false) - { - return Column(FirstCellUsed(includeFormats), LastCellUsed(includeFormats)); - } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnCollection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnCollection.cs index 2959177..2b8f285 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnCollection.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumnCollection.cs @@ -4,7 +4,7 @@ namespace ClosedXML.Excel { - internal class XLColumnsCollection : IDictionary + internal class XLColumnsCollection : IDictionary, IDisposable { public void ShiftColumnsRight(Int32 startingColumn, Int32 columnsToShift) { @@ -114,5 +114,10 @@ { _dictionary.RemoveAll(predicate); } + + public void Dispose() + { + _dictionary.Values.ForEach(c=>c.Dispose()); + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs index 17ab1fa..56d08d1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs @@ -273,5 +273,11 @@ _columns.ForEach(c=>c.Clear(clearOptions)); return this; } + + public void Dispose() + { + if (_columns != null) + _columns.ForEach(c => c.Dispose()); + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/XLComment.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/XLComment.cs index edead12..a644ad0 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/XLComment.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Comments/XLComment.cs @@ -1,14 +1,11 @@ using System; -using System.Collections.Generic; -using System.Linq; -using System.Text; namespace ClosedXML.Excel { internal class XLComment : XLFormattedText, IXLComment { - XLWorksheet _worksheet; - XLCell _cell; + private XLCell _cell; + public XLComment(XLCell cell, IXLFontBase defaultFont) : base(defaultFont) { @@ -16,7 +13,7 @@ } public XLComment(XLCell cell, XLFormattedText defaultComment, IXLFontBase defaultFont) - : base(defaultComment, defaultFont) + : base(defaultComment, defaultFont) { Initialize(cell); } @@ -27,6 +24,123 @@ Initialize(cell); } + #region IXLComment Members + + public String Author { get; set; } + + public IXLComment SetAuthor(String value) + { + Author = value; + return this; + } + + public IXLRichString AddSignature() + { + AddText(Author + ":").SetBold(); + return AddText(Environment.NewLine); + } + + public void Delete() + { + _cell.DeleteComment(); + } + + #endregion + + #region IXLDrawing + + public String Name { get; set; } + public String Description { get; set; } + public XLDrawingAnchor Anchor { get; set; } + public Boolean HorizontalFlip { get; set; } + public Boolean VerticalFlip { get; set; } + public Int32 Rotation { get; set; } + public Int32 ExtentLength { get; set; } + public Int32 ExtentWidth { get; set; } + public Int32 ShapeId { get; internal set; } + + public Boolean Visible { get; set; } + + public IXLComment SetVisible() + { + Visible = true; + return Container; + } + + public IXLComment SetVisible(Boolean hidden) + { + Visible = hidden; + return Container; + } + + public IXLDrawingPosition Position { get; private set; } + + public Int32 ZOrder { get; set; } + + public IXLComment SetZOrder(Int32 zOrder) + { + ZOrder = zOrder; + return Container; + } + + public IXLDrawingStyle Style { get; private set; } + + public IXLComment SetName(String name) + { + Name = name; + return Container; + } + + public IXLComment SetDescription(String description) + { + Description = description; + return Container; + } + + public IXLComment SetHorizontalFlip() + { + HorizontalFlip = true; + return Container; + } + + public IXLComment SetHorizontalFlip(Boolean horizontalFlip) + { + HorizontalFlip = horizontalFlip; + return Container; + } + + public IXLComment SetVerticalFlip() + { + VerticalFlip = true; + return Container; + } + + public IXLComment SetVerticalFlip(Boolean verticalFlip) + { + VerticalFlip = verticalFlip; + return Container; + } + + public IXLComment SetRotation(Int32 rotation) + { + Rotation = rotation; + return Container; + } + + public IXLComment SetExtentLength(Int32 extentLength) + { + ExtentLength = extentLength; + return Container; + } + + public IXLComment SetExtentWidth(Int32 extentWidth) + { + ExtentWidth = extentWidth; + return Container; + } + + #endregion + private void Initialize(XLCell cell) { Author = Environment.UserName; @@ -38,158 +152,44 @@ if (pRow > 1) { pRow--; - var prevHeight = cell.CellAbove().WorksheetRow().Height; + double prevHeight = cell.CellAbove().WorksheetRow().Height; if (prevHeight > 7) pRowOffset = prevHeight - 7; } Position = new XLDrawingPosition - { - Column = cell.Address.ColumnNumber + 1, - ColumnOffset = 2, - Row = pRow, - RowOffset = pRowOffset - }; + { + Column = cell.Address.ColumnNumber + 1, + ColumnOffset = 2, + Row = pRow, + RowOffset = pRowOffset + }; ZOrder = cell.Worksheet.ZOrder++; Style - .Margins.SetLeft(0.1) - .Margins.SetRight(0.1) - .Margins.SetTop(0.05) - .Margins.SetBottom(0.05) - .Margins.SetAutomatic() - - .Size.SetHeight(59.25) - .Size.SetWidth(19.2) - - .ColorsAndLines.SetLineColor(XLColor.Black) - .ColorsAndLines.SetFillColor(XLColor.FromArgb(255,255,225)) - .ColorsAndLines.SetLineDash(XLDashStyle.Solid) - .ColorsAndLines.SetLineStyle(XLLineStyle.Single) - .ColorsAndLines.SetLineWeight(0.75) - .ColorsAndLines.SetFillTransparency(1) - .ColorsAndLines.SetLineTransparency(1) - - .Alignment.SetHorizontal(XLDrawingHorizontalAlignment.Left) - .Alignment.SetVertical(XLDrawingVerticalAlignment.Top) - .Alignment.SetDirection(XLDrawingTextDirection.Context) - .Alignment.SetOrientation(XLDrawingTextOrientation.LeftToRight) - - .Properties.SetPositioning(XLDrawingAnchor.Absolute) - - .Protection.SetLocked() - .Protection.SetLockText(); + .Margins.SetLeft(0.1) + .Margins.SetRight(0.1) + .Margins.SetTop(0.05) + .Margins.SetBottom(0.05) + .Margins.SetAutomatic() + .Size.SetHeight(59.25) + .Size.SetWidth(19.2) + .ColorsAndLines.SetLineColor(XLColor.Black) + .ColorsAndLines.SetFillColor(XLColor.FromArgb(255, 255, 225)) + .ColorsAndLines.SetLineDash(XLDashStyle.Solid) + .ColorsAndLines.SetLineStyle(XLLineStyle.Single) + .ColorsAndLines.SetLineWeight(0.75) + .ColorsAndLines.SetFillTransparency(1) + .ColorsAndLines.SetLineTransparency(1) + .Alignment.SetHorizontal(XLDrawingHorizontalAlignment.Left) + .Alignment.SetVertical(XLDrawingVerticalAlignment.Top) + .Alignment.SetDirection(XLDrawingTextDirection.Context) + .Alignment.SetOrientation(XLDrawingTextOrientation.LeftToRight) + .Properties.SetPositioning(XLDrawingAnchor.Absolute) + .Protection.SetLocked() + .Protection.SetLockText(); _cell = cell; - _worksheet = cell.Worksheet; ShapeId = cell.Worksheet.Workbook.ShapeIdManager.GetNext(); } - - public String Author { get; set; } - public IXLComment SetAuthor(String value) - { - Author = value; - return this; - } - - public IXLRichString AddSignature() - { - AddText(Author + ":").SetBold(); - return AddText(Environment.NewLine); - } - - - #region IXLDrawing - - public Int32 ShapeId { get; internal set; } - - public Boolean Visible { get; set; } - public IXLComment SetVisible() - { - Visible = true; - return Container; - } - public IXLComment SetVisible(Boolean hidden) - { - Visible = hidden; - return Container; - } - - public String Name { get; set; } - public IXLComment SetName(String name) - { - Name = name; - return Container; - } - - public String Description { get; set; } - public IXLComment SetDescription(String description) - { - Description = description; - return Container; - } - - public XLDrawingAnchor Anchor { get; set; } - - public IXLDrawingPosition Position { get; private set; } - - public Int32 ZOrder { get; set; } - public IXLComment SetZOrder(Int32 zOrder) - { - ZOrder = zOrder; - return Container; - } - - public Boolean HorizontalFlip { get; set; } - public IXLComment SetHorizontalFlip() - { - HorizontalFlip = true; - return Container; - } - public IXLComment SetHorizontalFlip(Boolean horizontalFlip) - { - HorizontalFlip = horizontalFlip; - return Container; - } - - public Boolean VerticalFlip { get; set; } - public IXLComment SetVerticalFlip() - { - VerticalFlip = true; - return Container; - } - public IXLComment SetVerticalFlip(Boolean verticalFlip) - { - VerticalFlip = verticalFlip; - return Container; - } - - public Int32 Rotation { get; set; } - public IXLComment SetRotation(Int32 rotation) - { - Rotation = rotation; - return Container; - } - - public Int32 ExtentLength { get; set; } - public IXLComment SetExtentLength(Int32 extentLength) - { - ExtentLength = extentLength; - return Container; - } - - public Int32 ExtentWidth { get; set; } - public IXLComment SetExtentWidth(Int32 extentWidth) - { - ExtentWidth = extentWidth; - return Container; - } - - public IXLDrawingStyle Style { get; private set; } - - #endregion - - public void Delete() { _cell.DeleteComment(); } - } - -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/XLCustomProperty.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/XLCustomProperty.cs index 66901d3..36e6f56 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/XLCustomProperty.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/CustomProperties/XLCustomProperty.cs @@ -3,47 +3,57 @@ namespace ClosedXML.Excel { - internal class XLCustomProperty: IXLCustomProperty + internal class XLCustomProperty : IXLCustomProperty { - XLWorkbook workbook; + private readonly XLWorkbook _workbook; + + private String name; + public XLCustomProperty(XLWorkbook workbook) { - this.workbook = workbook; + _workbook = workbook; } - private String name; + + #region IXLCustomProperty Members + public String Name { - get - { - return name; - } + get { return name; } set { - if (workbook.CustomProperties.Any(t => t.Name == value)) - throw new ArgumentException(String.Format("This workbook already contains a custom property named '{0}'", value)); + if (_workbook.CustomProperties.Any(t => t.Name == value)) + throw new ArgumentException( + String.Format("This workbook already contains a custom property named '{0}'", value)); name = value; } } - public XLCustomPropertyType Type + + public XLCustomPropertyType Type { get { Double dTest; if (Value is DateTime) return XLCustomPropertyType.Date; - else if (Value is Boolean) + + if (Value is Boolean) return XLCustomPropertyType.Boolean; - else if (Double.TryParse(Value.ToString(), out dTest)) + + if (Double.TryParse(Value.ToString(), out dTest)) return XLCustomPropertyType.Number; - else - return XLCustomPropertyType.Text; + + return XLCustomPropertyType.Text; } } + public Object Value { get; set; } + public T GetValue() { return (T)Convert.ChangeType(Value, typeof(T)); } + + #endregion } -} +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs index 47d3378..f0c9d81 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/DataValidation/XLDataValidation.cs @@ -147,9 +147,15 @@ public void CopyFrom(IXLDataValidation dataValidation) { - if (Ranges == null) - Ranges = new XLRanges(); - //dataValidation.Ranges.ForEach(r => Ranges.Add(r)); + if (dataValidation == this) return; + + if (Ranges != null) + Ranges.Dispose(); + + Ranges = new XLRanges(); + + if (dataValidation.Ranges != null) + dataValidation.Ranges.ForEach(r => Ranges.Add(r)); IgnoreBlanks = dataValidation.IgnoreBlanks; InCellDropdown = dataValidation.InCellDropdown; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs index 52a92d6..6a15138 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/NamedRanges/XLNamedRange.cs @@ -43,7 +43,7 @@ select match.Groups["Sheet"].Success ? _namedRanges.Workbook.WorksheetsInternal.Worksheet(match.Groups["Sheet"].Value).Range(match.Groups["Range"].Value) as IXLRangeBase - : _namedRanges.Workbook.Worksheets.SelectMany(sheet => sheet.Tables).Where(table => table.Name == match.Groups["Table"].Value).Single().Column(match.Groups["Column"].Value) ) + : _namedRanges.Workbook.Worksheets.SelectMany(sheet => sheet.Tables).Single(table => table.Name == match.Groups["Table"].Value).Column(match.Groups["Column"].Value) ) { ranges.Add(rangeToAdd); } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs index 973da30..56aa5d8 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs @@ -241,6 +241,9 @@ IXLRangeRows RowsUsed(Boolean includeFormats = false); IXLRangeColumns ColumnsUsed(Boolean includeFormats = false); + + IXLRangeRows Rows(Func predicate); + IXLRangeColumns Columns(Func predicate); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 6cc99a0..fb72abf 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -6,9 +6,9 @@ { Workbook, Worksheet - } ; + } - public interface IXLRangeBase + public interface IXLRangeBase: IDisposable { IXLWorksheet Worksheet { get; } @@ -108,6 +108,10 @@ /// if set to true will return all cells with a value or a style different than the default. IXLCell FirstCellUsed(Boolean includeFormats); + IXLCell FirstCellUsed(Func predicate); + + IXLCell FirstCellUsed(Boolean includeFormats, Func predicate); + /// /// Returns the last cell of this range. /// @@ -126,6 +130,10 @@ /// if set to true will return all cells with a value or a style different than the default. IXLCell LastCellUsed(Boolean includeFormats); + IXLCell LastCellUsed(Func predicate); + + IXLCell LastCellUsed(Boolean includeFormats, Func predicate); + /// /// Determines whether this range contains the specified range (completely). /// For partial matches use the range.Intersects method. @@ -230,5 +238,6 @@ //IXLChart CreateChart(Int32 firstRow, Int32 firstColumn, Int32 lastRow, Int32 lastColumn); IXLAutoFilter SetAutoFilter(); + } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs index e800bf1..7d7c7e2 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs @@ -3,7 +3,7 @@ namespace ClosedXML.Excel { - public interface IXLRangeColumns: IEnumerable + public interface IXLRangeColumns: IEnumerable, IDisposable { /// diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs index 342cc29..d52638f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs @@ -3,7 +3,7 @@ namespace ClosedXML.Excel { - public interface IXLRangeRows: IEnumerable + public interface IXLRangeRows : IEnumerable, IDisposable { /// /// Adds a row range to this group. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs index e34a5bd..50e292f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs @@ -3,7 +3,7 @@ namespace ClosedXML.Excel { - public interface IXLRanges: IEnumerable + public interface IXLRanges: IEnumerable, IDisposable { /// /// Adds the specified range to this group. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index 18ebb01..57946b3 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -15,8 +15,8 @@ if (!xlRangeParameters.IgnoreEvents) { - Worksheet.RangeShiftedRows += WorksheetRangeShiftedRows; - Worksheet.RangeShiftedColumns += WorksheetRangeShiftedColumns; + SubscribeToShiftedRows((range, rowsShifted) => WorksheetRangeShiftedRows(range, rowsShifted)); + SubscribeToShiftedColumns((range, columnsShifted) => WorksheetRangeShiftedColumns(range, columnsShifted)); xlRangeParameters.IgnoreEvents = true; } SetStyle(xlRangeParameters.DefaultStyle); @@ -112,6 +112,21 @@ return retVal; } + public IXLRangeColumns Columns(Func predicate) + { + var retVal = new XLRangeColumns(); + Int32 columnCount = ColumnCount(); + for (Int32 c = 1; c <= columnCount; c++) + { + var column = Column(c); + if (predicate(column)) + retVal.Add(column); + else + column.Dispose(); + } + return retVal; + } + public virtual IXLRangeColumns Columns(Int32 firstColumn, Int32 lastColumn) { var retVal = new XLRangeColumns(); @@ -222,6 +237,21 @@ return retVal; } + public IXLRangeRows Rows(Func predicate) + { + var retVal = new XLRangeRows(); + Int32 rowCount = RowCount(); + for (Int32 r = 1; r <= rowCount; r++) + { + var row = Row(r); + if (predicate(row)) + retVal.Add(Row(r)); + else + row.Dispose(); + } + return retVal; + } + public IXLRangeRows Rows(Int32 firstRow, Int32 lastRow) { var retVal = new XLRangeRows(); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index 90d0fd1..3acc109 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -50,6 +50,22 @@ #endregion + private Boolean _subscribedToShiftedRows; + protected void SubscribeToShiftedRows(RangeShiftedRowsDelegate worksheetRangeShiftedRows) + { + WorksheetRangeShiftedRows = worksheetRangeShiftedRows; + RangeAddress.Worksheet.RangeShiftedRows += WorksheetRangeShiftedRows; + _subscribedToShiftedRows = true; + } + + private Boolean _subscribedToShiftedColumns; + protected void SubscribeToShiftedColumns(RangeShiftedColumnsDelegate worksheetRangeShiftedColumns) + { + WorksheetRangeShiftedColumns = worksheetRangeShiftedColumns; + RangeAddress.Worksheet.RangeShiftedColumns += WorksheetRangeShiftedColumns; + _subscribedToShiftedColumns = true; + } + #region Public properties public XLRangeAddress RangeAddress { get; protected set; } @@ -63,12 +79,10 @@ { get { - var thisRange = AsRange(); - if (Worksheet.DataValidations.ContainsSingle(thisRange)) + using (var thisRange = AsRange()) { - return - Worksheet.DataValidations.Where(dv => dv.Ranges.Contains(thisRange)).Single() as - XLDataValidation; + if (Worksheet.DataValidations.ContainsSingle(thisRange)) + return Worksheet.DataValidations.Single(dv => dv.Ranges.Contains(thisRange)) as XLDataValidation; } var dvEmpty = new List(); foreach (IXLDataValidation dv in Worksheet.DataValidations) @@ -87,12 +101,8 @@ if (thisStart > dvStart && thisEnd < dvEnd) { - dv.Ranges.Add(Worksheet.Column(column.ColumnNumber()).Column( - dvStart, - thisStart - 1)); - dv.Ranges.Add(Worksheet.Column(column.ColumnNumber()).Column( - thisEnd + 1, - dvEnd)); + dv.Ranges.Add(Worksheet.Column(column.ColumnNumber()).Column(dvStart, thisStart - 1)); + dv.Ranges.Add(Worksheet.Column(column.ColumnNumber()).Column(thisEnd + 1, dvEnd)); } else { @@ -121,7 +131,7 @@ } } - if (dv.Ranges.Count() == 0) + if (!dv.Ranges.Any()) dvEmpty.Add(dv); } } @@ -288,12 +298,11 @@ public IXLRange Unmerge() { string tAddress = RangeAddress.ToString(); - if ( - Worksheet.Internals.MergedRanges.Select(m => m.RangeAddress.ToString()).Any( - mAddress => mAddress == tAddress)) - Worksheet.Internals.MergedRanges.Remove(AsRange()); + var asRange = AsRange(); + if (Worksheet.Internals.MergedRanges.Select(m => m.RangeAddress.ToString()).Any(mAddress => mAddress == tAddress)) + Worksheet.Internals.MergedRanges.Remove(asRange); - return AsRange(); + return asRange; } public IXLRangeBase Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) @@ -308,9 +317,6 @@ if (includeFormats) { ClearMerged(); - - //var hyperlinksToRemove = Worksheet.Hyperlinks.Where(hl => Contains(hl.Cell.AsRange())).ToList(); - //hyperlinksToRemove.ForEach(hl => Worksheet.Hyperlinks.Delete(hl)); } if (clearOptions == XLClearOptions.ContentsAndFormats) @@ -381,8 +387,11 @@ get { return GetStyle(); } set { Cells().ForEach(c => c.Style = value); } } - - public virtual IXLRange AsRange() + IXLRange IXLRangeBase.AsRange() + { + return AsRange(); + } + public virtual XLRange AsRange() { return Worksheet.Range(RangeAddress.FirstAddress, RangeAddress.LastAddress); } @@ -407,7 +416,7 @@ if (namedRanges.Any(nr => String.Compare(nr.Name, rangeName, true) == 0)) { - var namedRange = namedRanges.Where(nr => String.Compare(nr.Name, rangeName, true) == 0).Single(); + var namedRange = namedRanges.Single(nr => String.Compare(nr.Name, rangeName, true) == 0); namedRange.Add(Worksheet.Workbook, RangeAddress.ToStringFixed(XLReferenceStyle.A1, true)); } else @@ -474,34 +483,74 @@ public XLCell FirstCellUsed() { - return FirstCellUsed(false); + return FirstCellUsed(false, null); } public XLCell FirstCellUsed(Boolean includeFormats) { + return FirstCellUsed(includeFormats, null); + } + + IXLCell IXLRangeBase.FirstCellUsed(Func predicate) + { + return FirstCellUsed(predicate); + } + + public XLCell FirstCellUsed(Func predicate) + { + return FirstCellUsed(false, predicate); + } + + IXLCell IXLRangeBase.FirstCellUsed(Boolean includeFormats, Func predicate) + { + return FirstCellUsed(includeFormats, predicate); + } + + public XLCell FirstCellUsed(Boolean includeFormats, Func predicate) + { var sp = Worksheet.Internals.CellsCollection.FirstPointUsed( RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber, RangeAddress.LastAddress.RowNumber, RangeAddress.LastAddress.ColumnNumber, - includeFormats); + includeFormats, predicate); return sp.Row == 0 ? null : Worksheet.Cell(sp.Row, sp.Column); } public XLCell LastCellUsed() { - return LastCellUsed(false); + return LastCellUsed(false, null); } public XLCell LastCellUsed(Boolean includeFormats) { + return LastCellUsed(includeFormats, null); + } + + IXLCell IXLRangeBase.LastCellUsed(Func predicate) + { + return LastCellUsed(predicate); + } + + public XLCell LastCellUsed(Func predicate) + { + return LastCellUsed(false, predicate); + } + + IXLCell IXLRangeBase.LastCellUsed(Boolean includeFormats, Func predicate) + { + return LastCellUsed(includeFormats, predicate); + } + + public XLCell LastCellUsed(Boolean includeFormats, Func predicate) + { var sp = Worksheet.Internals.CellsCollection.LastPointUsed( RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber, RangeAddress.LastAddress.RowNumber, RangeAddress.LastAddress.ColumnNumber, - includeFormats); + includeFormats, predicate); return sp.Row == 0 ? null : Worksheet.Cell(sp.Row, sp.Column); } @@ -566,13 +615,6 @@ else if (Worksheet.Internals.ColumnsCollection.TryGetValue(absoluteAddress.ColumnNumber, out column) && column.GetStyleId() != worksheetStyleId) styleId = column.GetStyleId(); - //if (Worksheet.Internals.RowsCollection.ContainsKey(absoluteAddress.RowNumber) - // && !Worksheet.Internals.RowsCollection[absoluteAddress.RowNumber].GetStyleId().Equals(worksheetStyleId)) - // style = Worksheet.Internals.RowsCollection[absoluteAddress.RowNumber].Style; - //else if (Worksheet.Internals.ColumnsCollection.ContainsKey(absoluteAddress.ColumnNumber) - // && - // !Worksheet.Internals.ColumnsCollection[absoluteAddress.ColumnNumber].GetStyleId().Equals(worksheetStyleId)) - // style = Worksheet.Internals.ColumnsCollection[absoluteAddress.ColumnNumber].Style; } var newCell = new XLCell(Worksheet, absoluteAddress, styleId); Worksheet.Internals.CellsCollection.Add(absoluteAddress.RowNumber, absoluteAddress.ColumnNumber, newCell); @@ -612,7 +654,33 @@ public XLRange Range(IXLCell firstCell, IXLCell lastCell) { - return Range(firstCell.Address, lastCell.Address); + var newFirstCellAddress = firstCell.Address as XLAddress; + var newLastCellAddress = lastCell.Address as XLAddress; + + return GetRange(newFirstCellAddress, newLastCellAddress); + } + + private XLRange GetRange(XLAddress newFirstCellAddress, XLAddress newLastCellAddress) + { + var newRangeAddress = new XLRangeAddress(newFirstCellAddress, newLastCellAddress); + var xlRangeParameters = new XLRangeParameters(newRangeAddress, Style); + if ( + newFirstCellAddress.RowNumber < RangeAddress.FirstAddress.RowNumber + || newFirstCellAddress.RowNumber > RangeAddress.LastAddress.RowNumber + || newLastCellAddress.RowNumber > RangeAddress.LastAddress.RowNumber + || newFirstCellAddress.ColumnNumber < RangeAddress.FirstAddress.ColumnNumber + || newFirstCellAddress.ColumnNumber > RangeAddress.LastAddress.ColumnNumber + || newLastCellAddress.ColumnNumber > RangeAddress.LastAddress.ColumnNumber + ) + { + throw new ArgumentOutOfRangeException(String.Format( + "The cells {0} and {1} are outside the range '{2}'.", + newFirstCellAddress, + newLastCellAddress, + ToString())); + } + + return new XLRange(xlRangeParameters); } public XLRange Range(String firstCellAddress, String lastCellAddress) @@ -645,25 +713,7 @@ newLastCellAddress.FixedRow = rangeAddress.LastAddress.FixedRow; newLastCellAddress.FixedColumn = rangeAddress.LastAddress.FixedColumn; - var newRangeAddress = new XLRangeAddress(newFirstCellAddress, newLastCellAddress); - var xlRangeParameters = new XLRangeParameters(newRangeAddress, Style); - if ( - newFirstCellAddress.RowNumber < RangeAddress.FirstAddress.RowNumber - || newFirstCellAddress.RowNumber > RangeAddress.LastAddress.RowNumber - || newLastCellAddress.RowNumber > RangeAddress.LastAddress.RowNumber - || newFirstCellAddress.ColumnNumber < RangeAddress.FirstAddress.ColumnNumber - || newFirstCellAddress.ColumnNumber > RangeAddress.LastAddress.ColumnNumber - || newLastCellAddress.ColumnNumber > RangeAddress.LastAddress.ColumnNumber - ) - { - throw new ArgumentOutOfRangeException(String.Format( - "The cells {0} and {1} are outside the range '{2}'.", - newFirstCellAddress, - newLastCellAddress, - ToString())); - } - - return new XLRange(xlRangeParameters); + return GetRange(newFirstCellAddress, newLastCellAddress); } public IXLRanges Ranges(String ranges) @@ -792,11 +842,9 @@ { foreach (XLWorksheet ws in Worksheet.Workbook.WorksheetsInternal) { - foreach ( - XLCell cell in - ws.Internals.CellsCollection.GetCells(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1)) - ) - cell.ShiftFormulaColumns((XLRange)AsRange(), numberOfColumns); + foreach (XLCell cell in ws.Internals.CellsCollection.GetCells(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1))) + using (var asRange = AsRange()) + cell.ShiftFormulaColumns(asRange, numberOfColumns); } var cellsToInsert = new Dictionary(); @@ -854,7 +902,9 @@ cellsToInsert.ForEach( c => Worksheet.Internals.CellsCollection.Add(c.Key.RowNumber, c.Key.ColumnNumber, c.Value)); - Worksheet.NotifyRangeShiftedColumns((XLRange)AsRange(), numberOfColumns); + using (var asRange = AsRange()) + Worksheet.NotifyRangeShiftedColumns(asRange, numberOfColumns); + var rangeToReturn = Worksheet.Range( RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber - numberOfColumns, @@ -975,11 +1025,9 @@ { foreach (XLWorksheet ws in Worksheet.Workbook.WorksheetsInternal) { - foreach ( - XLCell cell in - ws.Internals.CellsCollection.GetCells(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1)) - ) - cell.ShiftFormulaRows((XLRange)AsRange(), numberOfRows); + foreach (XLCell cell in ws.Internals.CellsCollection.GetCells(c => !StringExtensions.IsNullOrWhiteSpace(c.FormulaA1))) + using (var asRange = AsRange()) + cell.ShiftFormulaRows(asRange, numberOfRows); } var cellsToInsert = new Dictionary(); @@ -1009,8 +1057,6 @@ newCell.FormulaA1 = oldCell.FormulaA1; cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(oldKey); - //if (oldKey.RowNumber < firstRow + numberOfRows) - // cellsToBlank.Add(oldKey); } } } @@ -1030,24 +1076,15 @@ newCell.FormulaA1 = c.FormulaA1; cellsToInsert.Add(newKey, newCell); cellsToDelete.Add(c.Address); - //if (c.Address.RowNumber < firstRow + numberOfRows) - // cellsToBlank.Add(c.Address); } } 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)); - //foreach (IXLAddress c in cellsToBlank) - //{ - // IXLStyle styleToUse; - - // styleToUse = Worksheet.Internals.ColumnsCollection.ContainsKey(c.ColumnNumber) - // ? Worksheet.Internals.ColumnsCollection[c.ColumnNumber].Style - // : Worksheet.Style; - // Worksheet.Cell(c.RowNumber, c.ColumnNumber).Style = styleToUse; - //} - Worksheet.NotifyRangeShiftedRows((XLRange)AsRange(), numberOfRows); + using (var asRange = AsRange()) + Worksheet.NotifyRangeShiftedRows(asRange, numberOfRows); + var rangeToReturn = Worksheet.Range( RangeAddress.FirstAddress.RowNumber - numberOfRows, RangeAddress.FirstAddress.ColumnNumber, @@ -1179,11 +1216,13 @@ var hyperlinksToRemove = Worksheet.Hyperlinks.Where(hl => Contains(hl.Cell.AsRange())).ToList(); hyperlinksToRemove.ForEach(hl => Worksheet.Hyperlinks.Delete(hl)); - var shiftedRange = (XLRange)AsRange(); - if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) - Worksheet.NotifyRangeShiftedRows(shiftedRange, rowModifier * -1); - else - Worksheet.NotifyRangeShiftedColumns(shiftedRange, columnModifier * -1); + using (var shiftedRange = AsRange()) + { + if (shiftDeleteCells == XLShiftDeletedCells.ShiftCellsUp) + Worksheet.NotifyRangeShiftedRows(shiftedRange, rowModifier * -1); + else + Worksheet.NotifyRangeShiftedColumns(shiftedRange, columnModifier * -1); + } } public override string ToString() @@ -1367,22 +1406,29 @@ } public XLPivotTable CreatePivotTable(IXLCell targetCell, String name) { - return (XLPivotTable)this.Worksheet.PivotTables.AddNew(name, targetCell, this.AsRange()); + return (XLPivotTable)Worksheet.PivotTables.AddNew(name, targetCell, AsRange()); } - public IXLAutoFilter SetAutoFilter() { - return Worksheet.AutoFilter.Set(AsRange()); + public IXLAutoFilter SetAutoFilter() + { + using (var asRange = AsRange()) + return Worksheet.AutoFilter.Set(asRange); } public IXLRangeRows RowsUsed(Boolean includeFormats = false) { var rows = new XLRangeRows(); - var rangeUsed = RangeUsed(includeFormats); - if (rangeUsed != null) + using (var rangeUsed = RangeUsed(includeFormats)) { - foreach (var row in rangeUsed.Rows().Where(r => !r.IsEmpty(includeFormats))) + if (rangeUsed != null) { - rows.Add(row); + using (var usedRows = rangeUsed.Rows(r => !r.IsEmpty(includeFormats))) + { + foreach (var row in usedRows) + { + rows.Add(row); + } + } } } return rows; @@ -1391,12 +1437,17 @@ public IXLRangeColumns ColumnsUsed(Boolean includeFormats = false) { var columns = new XLRangeColumns(); - var rangeUsed = RangeUsed(includeFormats); - if (rangeUsed != null) + using (var rangeUsed = RangeUsed(includeFormats)) { - foreach (var column in rangeUsed.Columns().Where(r => !r.IsEmpty(includeFormats))) + if (rangeUsed != null) { - columns.Add(column); + using (var usedColumns = rangeUsed.Columns(c => !c.IsEmpty(includeFormats))) + { + foreach (var column in usedColumns) + { + columns.Add(column); + } + } } } return columns; @@ -1416,7 +1467,7 @@ public IXLRangeBase Sort() { - if (SortColumns.Count() == 0) + if (!SortColumns.Any()) { String columnsToSortBy = String.Empty; Int32 maxColumn = ColumnCount(); @@ -1424,7 +1475,7 @@ maxColumn = LastCellUsed(true).Address.ColumnNumber; for (int i = 1; i <= maxColumn; i++) { - columnsToSortBy += i.ToString() + ","; + columnsToSortBy += i + ","; } columnsToSortBy = columnsToSortBy.Substring(0, columnsToSortBy.Length - 1); return Sort(columnsToSortBy); @@ -1445,7 +1496,7 @@ maxColumn = LastCellUsed(true).Address.ColumnNumber; for (int i = 1; i <= maxColumn; i++) { - columnsToSortBy += i.ToString() + ","; + columnsToSortBy += i + ","; } columnsToSortBy = columnsToSortBy.Substring(0, columnsToSortBy.Length - 1); } @@ -1463,10 +1514,7 @@ else { coString = coPairTrimmed; - if (sortOrder == XLSortOrder.Ascending) - order = "ASC"; - else - order = "DESC"; + order = sortOrder == XLSortOrder.Ascending ? "ASC" : "DESC"; } Int32 co; @@ -1665,5 +1713,24 @@ return new XLRangeRow( new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), true); } + + private RangeShiftedRowsDelegate WorksheetRangeShiftedRows; + + private RangeShiftedColumnsDelegate WorksheetRangeShiftedColumns; + + + public void Dispose() + { + if (_subscribedToShiftedRows) + { + RangeAddress.Worksheet.RangeShiftedRows -= WorksheetRangeShiftedRows; + _subscribedToShiftedRows = false; + } + + if (!_subscribedToShiftedColumns) return; + + RangeAddress.Worksheet.RangeShiftedColumns -= WorksheetRangeShiftedColumns; + _subscribedToShiftedColumns = false; + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs index 77bd036..50c6bd7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -12,8 +12,8 @@ { if (quickLoad) return; - Worksheet.RangeShiftedRows += WorksheetRangeShiftedRows; - Worksheet.RangeShiftedColumns += WorksheetRangeShiftedColumns; + SubscribeToShiftedRows((range, rowsShifted) => WorksheetRangeShiftedRows(range, rowsShifted)); + SubscribeToShiftedColumns((range, columnsShifted) => WorksheetRangeShiftedColumns(range, columnsShifted)); SetStyle(rangeParameters.DefaultStyle); } @@ -317,22 +317,26 @@ public IXLTable AsTable() { - return AsRange().AsTable(); + using (var asRange = AsRange()) + return asRange.AsTable(); } public IXLTable AsTable(string name) { - return AsRange().AsTable(name); + using (var asRange = AsRange()) + return asRange.AsTable(name); } public IXLTable CreateTable() { - return AsRange().CreateTable(); + using (var asRange = AsRange()) + return asRange.CreateTable(); } public IXLTable CreateTable(string name) { - return AsRange().CreateTable(name); + using (var asRange = AsRange()) + return asRange.CreateTable(name); } public new IXLRangeColumn Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) @@ -345,5 +349,6 @@ { return Column(FirstCellUsed(includeFormats), LastCellUsed(includeFormats)); } + } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs index 0ac0a79..18ad52a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs @@ -131,5 +131,11 @@ } #endregion + + public void Dispose() + { + if (_ranges != null) + _ranges.ForEach(r => r.Dispose()); + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs index 472511d..0a51e4f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -12,13 +12,14 @@ { RangeParameters = rangeParameters; if (quickLoad) return; - Worksheet.RangeShiftedRows += WorksheetRangeShiftedRows; - Worksheet.RangeShiftedColumns += WorksheetRangeShiftedColumns; + SubscribeToShiftedRows((range, rowsShifted) => WorksheetRangeShiftedRows(range, rowsShifted)); + SubscribeToShiftedColumns((range, columnsShifted) => WorksheetRangeShiftedColumns(range, columnsShifted)); SetStyle(rangeParameters.DefaultStyle); } #endregion + public XLRangeParameters RangeParameters { get; private set; } #region IXLRangeRow Members @@ -334,5 +335,6 @@ { return Row(FirstCellUsed(includeFormats), LastCellUsed(includeFormats)); } + } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs index 69ffdc9..09eb0c2 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs @@ -131,5 +131,12 @@ } #endregion + + public void Dispose() + { + if (_ranges != null) + _ranges.ForEach(r => r.Dispose()); + } + } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs index bd22713..59c47e9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs @@ -8,9 +8,7 @@ internal class XLRanges : IXLRanges, IXLStylized { - public Boolean StyleChanged { get; set; } private readonly List _ranges = new List(); - private Int32 _count; private IXLStyle _style; public XLRanges() @@ -28,7 +26,7 @@ public void Add(IXLRangeBase range) { - _count++; + Count++; _ranges.Add(range.AsRange() as XLRange); } @@ -37,25 +35,13 @@ Add(cell.AsRange()); } - //public void Add(String rangeAddress) - //{ - // count++; - // var byExclamation = rangeAddress.Split('!'); - // var wsName = byExclamation[0].Replace("'", ""); - // var rng = byExclamation[1]; - // var rangeToAdd = workbook.Worksheets.Worksheet(wsName).Range(rng); - // ranges.Add((XLRange)rangeToAdd); - //} public void Remove(IXLRange range) { - _count--; + Count--; _ranges.RemoveAll(r => r.ToString() == range.ToString()); } - public Int32 Count - { - get { return _count; } - } + public int Count { get; private set; } public IEnumerator GetEnumerator() { @@ -97,9 +83,7 @@ { dv.Ranges.Remove(dvRange); foreach (IXLCell c in dvRange.Cells().Where(c => !range.Contains(c.Address.ToString()))) - { dv.Ranges.Add(c.AsRange()); - } } } } @@ -167,10 +151,17 @@ return this; } + public void Dispose() + { + _ranges.ForEach(r => r.Dispose()); + } + #endregion #region IXLStylized Members + public Boolean StyleChanged { get; set; } + public IEnumerable Styles { get @@ -217,7 +208,8 @@ { var other = (XLRanges)obj; - return _ranges.Count == other._ranges.Count && _ranges.Select(thisRange => Enumerable.Contains(other._ranges, thisRange)).All(foundOne => foundOne); + return _ranges.Count == other._ranges.Count && + _ranges.Select(thisRange => Enumerable.Contains(other._ranges, thisRange)).All(foundOne => foundOne); } public override int GetHashCode() diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs index a7133f9..ae77455 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs @@ -56,10 +56,10 @@ IXLRow AdjustToContents(Int32 startColumn, Int32 endColumn, Double minHeight, Double maxHeight); /// Hides this row. - void Hide(); + IXLRow Hide(); /// Unhides this row. - void Unhide(); + IXLRow Unhide(); /// /// Gets a value indicating whether this row is hidden or not. @@ -80,43 +80,43 @@ /// /// Adds this row to the next outline level (Increments the outline level for this row by 1). /// - void Group(); + IXLRow Group(); /// /// Adds this row to the next outline level (Increments the outline level for this row by 1). /// /// If set to true the row will be shown collapsed. - void Group(Boolean collapse); + IXLRow Group(Boolean collapse); /// /// Sets outline level for this row. /// /// The outline level. - void Group(Int32 outlineLevel); + IXLRow Group(Int32 outlineLevel); /// /// Sets outline level for this row. /// /// The outline level. /// If set to true the row will be shown collapsed. - void Group(Int32 outlineLevel, Boolean collapse); + IXLRow Group(Int32 outlineLevel, Boolean collapse); /// /// Adds this row to the previous outline level (decrements the outline level for this row by 1). /// - void Ungroup(); + IXLRow Ungroup(); /// /// Adds this row to the previous outline level (decrements the outline level for this row by 1). /// /// If set to true it will remove this row from all outline levels. - void Ungroup(Boolean fromAll); + IXLRow Ungroup(Boolean fromAll); /// /// Show this row as collapsed. /// - void Collapse(); + IXLRow Collapse(); /// /// Gets the cell in the specified column. @@ -150,7 +150,7 @@ IXLCells Cells(String firstColumn, String lastColumn); /// Expands this row (if it's collapsed). - void Expand(); + IXLRow Expand(); Int32 CellCount(); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs index e5f1d9a..d99c879 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs @@ -13,7 +13,6 @@ private Boolean _isHidden; private Int32 _outlineLevel; - #endregion #region Constructor @@ -27,13 +26,9 @@ IsReference = xlRowParameters.IsReference; if (IsReference) - { - //SMELL: Leak may occur - Worksheet.RangeShiftedRows += WorksheetRangeShiftedRows; - } + SubscribeToShiftedRows(WorksheetRangeShiftedRows); else { - //_style = new XLStyle(this, xlRowParameters.DefaultStyle); SetStyle(xlRowParameters.DefaultStyleId); _height = xlRowParameters.Worksheet.RowHeight; } @@ -46,6 +41,9 @@ { _height = row._height; IsReference = row.IsReference; + if (IsReference) + SubscribeToShiftedRows(WorksheetRangeShiftedRows); + _collapsed = row._collapsed; _isHidden = row._isHidden; _outlineLevel = row._outlineLevel; @@ -66,7 +64,7 @@ int row = RowNumber(); - foreach (var cell in Worksheet.Internals.CellsCollection.GetCellsInRow(row)) + foreach (XLCell cell in Worksheet.Internals.CellsCollection.GetCellsInRow(row)) yield return cell.Style; UpdatingStyle = false; @@ -121,7 +119,9 @@ public void Delete() { int rowNumber = RowNumber(); - AsRange().Delete(XLShiftDeletedCells.ShiftCellsUp); + using (var asRange = AsRange()) + asRange.Delete(XLShiftDeletedCells.ShiftCellsUp); + Worksheet.Internals.RowsCollection.Remove(rowNumber); var rowsToMove = new List(); rowsToMove.AddRange(Worksheet.Internals.RowsCollection.Where(c => c.Key > rowNumber).Select(c => c.Key)); @@ -136,8 +136,13 @@ { int rowNum = RowNumber(); Worksheet.Internals.RowsCollection.ShiftRowsDown(rowNum + 1, numberOfRows); - var range = (XLRange)Worksheet.Row(rowNum).AsRange(); - range.InsertRowsBelow(true, numberOfRows); + using (var row = Worksheet.Row(rowNum)) + { + using (var asRange = row.AsRange()) + { + asRange.InsertRowsBelow(true, numberOfRows).Dispose(); + } + } return Worksheet.Rows(rowNum + 1, rowNum + numberOfRows); } @@ -145,10 +150,14 @@ { int rowNum = RowNumber(); Worksheet.Internals.RowsCollection.ShiftRowsDown(rowNum, numberOfRows); - // We can't use this.AsRange() because we've shifted the rows - // and we want to use the old rowNum. - var range = (XLRange)Worksheet.Row(rowNum).AsRange(); - range.InsertRowsAbove(true, numberOfRows); + using (var row = Worksheet.Row(rowNum)) + { + using (var asRange = row.AsRange()) + { + asRange.InsertRowsAbove(true, numberOfRows).Dispose(); + } + } + return Worksheet.Rows(rowNum, rowNum + numberOfRows - 1); } @@ -293,14 +302,16 @@ return this; } - public void Hide() + public IXLRow Hide() { IsHidden = true; + return this; } - public void Unhide() + public IXLRow Unhide() { IsHidden = false; + return this; } public Boolean IsHidden @@ -353,11 +364,6 @@ } } - public override IXLRange AsRange() - { - return Range(1, 1, 1, ExcelHelper.MaxColumnNumber); - } - public Int32 OutlineLevel { get { return IsReference ? Worksheet.Internals.RowsCollection[RowNumber()].OutlineLevel : _outlineLevel; } @@ -377,36 +383,38 @@ } } - public void Group() + public IXLRow Group() { - Group(false); + return Group(false); } - public void Group(Int32 outlineLevel) + public IXLRow Group(Int32 outlineLevel) { - Group(outlineLevel, false); + return Group(outlineLevel, false); } - public void Ungroup() + public IXLRow Ungroup() { - Ungroup(false); + return Ungroup(false); } - public void Group(Boolean collapse) + public IXLRow Group(Boolean collapse) { if (OutlineLevel < 8) OutlineLevel += 1; Collapsed = collapse; + return this; } - public void Group(Int32 outlineLevel, Boolean collapse) + public IXLRow Group(Int32 outlineLevel, Boolean collapse) { OutlineLevel = outlineLevel; Collapsed = collapse; + return this; } - public void Ungroup(Boolean ungroupFromAll) + public IXLRow Ungroup(Boolean ungroupFromAll) { if (ungroupFromAll) OutlineLevel = 0; @@ -415,18 +423,19 @@ if (OutlineLevel > 0) OutlineLevel -= 1; } + return this; } - public void Collapse() + public IXLRow Collapse() { Collapsed = true; - Hide(); + return Hide(); } - public void Expand() + public IXLRow Expand() { Collapsed = false; - Unhide(); + return Unhide(); } public Int32 CellCount() @@ -439,7 +448,8 @@ return SortLeftToRight(); } - public new IXLRow SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) + public new IXLRow SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, + Boolean ignoreBlanks = true) { base.SortLeftToRight(sortOrder, matchCase, ignoreBlanks); return this; @@ -447,18 +457,23 @@ IXLRangeRow IXLRow.CopyTo(IXLCell target) { - return AsRange().CopyTo(target).Row(1); + using (var asRange = AsRange()) + using (var copy = asRange.CopyTo(target)) + return copy.Row(1); } IXLRangeRow IXLRow.CopyTo(IXLRangeBase target) { - return AsRange().CopyTo(target).Row(1); + using (var asRange = AsRange()) + using (var copy = asRange.CopyTo(target)) + return copy.Row(1); } public IXLRow CopyTo(IXLRow row) { row.Clear(); - AsRange().CopyTo(row); + using (var asRange = AsRange()) + asRange.CopyTo(row).Dispose(); var newRow = (XLRow)row; newRow._height = _height; @@ -483,7 +498,8 @@ var retVal = new XLRangeRows(); var rowPairs = rows.Split(','); foreach (string pair in rowPairs) - AsRange().Rows(pair.Trim()).ForEach(retVal.Add); + using (var asRange = AsRange()) + asRange.Rows(pair.Trim()).ForEach(retVal.Add); return retVal; } @@ -499,8 +515,18 @@ return this; } + public IXLRangeRow RowUsed(Boolean includeFormats = false) + { + return Row(FirstCellUsed(includeFormats), LastCellUsed(includeFormats)); + } + #endregion + public override XLRange AsRange() + { + return Range(1, 1, 1, ExcelHelper.MaxColumnNumber); + } + private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) { if (range.RangeAddress.FirstAddress.RowNumber <= RowNumber()) @@ -614,10 +640,5 @@ } #endregion - - public IXLRangeRow RowUsed(Boolean includeFormats = false) - { - return Row(FirstCellUsed(includeFormats), LastCellUsed(includeFormats)); - } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowCollection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowCollection.cs index 2cca961..f50657d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowCollection.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRowCollection.cs @@ -6,7 +6,7 @@ { using System.Collections; - internal class XLRowsCollection : IDictionary + internal class XLRowsCollection : IDictionary, IDisposable { private readonly Dictionary _deleted = new Dictionary(); private readonly Dictionary _dictionary = new Dictionary(); @@ -139,5 +139,10 @@ _dictionary.RemoveAll(predicate); } + + public void Dispose() + { + _dictionary.Values.ForEach(r=>r.Dispose()); + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs index efa0101..47d642f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs @@ -55,10 +55,14 @@ { get { - return _showTotalsRow ? Range(2, 1, RowCount() - 1, ColumnCount()) : Range(2, 1, RowCount(), ColumnCount()); + return _showTotalsRow + ? Range(2, 1, RowCount() - 1, ColumnCount()) + : Range(2, 1, RowCount(), ColumnCount()); } } + public XLAutoFilter AutoFilter { get; private set; } + #region IXLTable Members public Boolean EmphasizeFirstColumn { get; set; } @@ -98,8 +102,8 @@ if (_showTotalsRow) { AutoFilter.Range = Worksheet.Range( - RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber, - RangeAddress.LastAddress.RowNumber - 1, RangeAddress.LastAddress.ColumnNumber); + RangeAddress.FirstAddress.RowNumber, RangeAddress.FirstAddress.ColumnNumber, + RangeAddress.LastAddress.RowNumber - 1, RangeAddress.LastAddress.ColumnNumber); } else AutoFilter.Range = Worksheet.Range(RangeAddress); @@ -131,7 +135,9 @@ public new IXLTableRow LastRow() { - return ShowTotalsRow ? new XLTableRow(this, base.Row(RowCount() - 1)) : new XLTableRow(this, base.Row(RowCount())); + return ShowTotalsRow + ? new XLTableRow(this, base.Row(RowCount() - 1)) + : new XLTableRow(this, base.Row(RowCount())); } public new IXLTableRow LastRowUsed() @@ -171,7 +177,7 @@ String lastRow; if (tPair.Contains(':') || tPair.Contains('-')) { - string[] rowRange = ExcelHelper.SplitRange(tPair); + var rowRange = ExcelHelper.SplitRange(tPair); firstRow = rowRange[0]; lastRow = rowRange[1]; @@ -391,7 +397,8 @@ return LastColumnUsed(); } - public new IXLRange Sort(String columnsToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) + public new IXLRange Sort(String columnsToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, + Boolean matchCase = false, Boolean ignoreBlanks = true) { var toSortBy = new StringBuilder(); foreach (string coPairTrimmed in columnsToSortBy.Split(',').Select(coPair => coPair.Trim())) @@ -422,6 +429,25 @@ return DataRange.Sort(toSortBy.ToString(0, toSortBy.Length - 1), sortOrder, matchCase, ignoreBlanks); } + public new IXLTable Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) + { + base.Clear(clearOptions); + return this; + } + + IXLBaseAutoFilter IXLTable.AutoFilter + { + get { return AutoFilter; } + } + + public new void Dispose() + { + if (AutoFilter != null) + AutoFilter.Dispose(); + + base.Dispose(); + } + #endregion public new XLTableRow Row(int row) @@ -440,7 +466,7 @@ ShowRowStripes = true; ShowAutoFilter = true; Theme = XLTableTheme.TableStyleLight9; - AutoFilter = new XLAutoFilter() { Range = AsRange() }; + AutoFilter = new XLAutoFilter {Range = AsRange()}; } private void AddToTables(XLRange range, Boolean addToTables) @@ -500,17 +526,5 @@ throw new ArgumentOutOfRangeException("The header row doesn't contain field name '" + name + "'."); } - - public new IXLTable Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) - { - base.Clear(clearOptions); - return this; - } - - IXLBaseAutoFilter IXLTable.AutoFilter - { - get { return AutoFilter; } - } - public XLAutoFilter AutoFilter { get; private set; } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs index 4598934..af8f3bf 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook.cs @@ -35,7 +35,7 @@ Simple = 1, } - public partial class XLWorkbook + public partial class XLWorkbook: IDisposable { #region Static @@ -601,5 +601,11 @@ } internal XLIdManager ShapeIdManager { get; private set; } + + + public void Dispose() + { + Worksheets.ForEach(w => w.Dispose()); + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index fb2936e..fda5dd1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -1151,7 +1151,9 @@ foreach (DataValidation dvs in dataValidations.Elements()) { - foreach (var dvt in dvs.SequenceOfReferences.InnerText.Split(' ').Select(rangeAddress => ws.Range(rangeAddress).DataValidation)) + String txt = dvs.SequenceOfReferences.InnerText; + if (StringExtensions.IsNullOrWhiteSpace(txt)) continue; + foreach (var dvt in txt.Split(' ').Select(rangeAddress => ws.Range(rangeAddress).DataValidation)) { if (dvs.AllowBlank != null) dvt.IgnoreBlanks = dvs.AllowBlank; if (dvs.ShowDropDown != null) dvt.InCellDropdown = !dvs.ShowDropDown.Value; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 0f298b4..8c8046d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -4,8 +4,6 @@ namespace ClosedXML.Excel { - using System.Text.RegularExpressions; - internal delegate void RangeShiftedRowsDelegate(XLRange range, Int32 rowsShifted); internal delegate void RangeShiftedColumnsDelegate(XLRange range, Int32 columnsShifted); @@ -27,12 +25,13 @@ private readonly Dictionary _columnOutlineCount = new Dictionary(); private readonly Dictionary _rowOutlineCount = new Dictionary(); + internal Int32 ZOrder = 1; private String _name; internal Int32 _position; private Double _rowHeight; private Boolean _tabActive; - internal Int32 ZOrder = 1; + #endregion #region Constructor @@ -63,10 +62,10 @@ Outline = new XLOutline(workbook.Outline); _columnWidth = workbook.ColumnWidth; _rowHeight = workbook.RowHeight; - RowHeightChanged = workbook.RowHeight != XLWorkbook.DefaultRowHeight; + RowHeightChanged = Math.Abs(workbook.RowHeight - XLWorkbook.DefaultRowHeight) > ExcelHelper.Epsilon; Name = sheetName; - RangeShiftedRows += XLWorksheetRangeShiftedRows; - RangeShiftedColumns += XLWorksheetRangeShiftedColumns; + SubscribeToShiftedRows(WorksheetRangeShiftedRows); + SubscribeToShiftedColumns(WorksheetRangeShiftedColumns); Charts = new XLCharts(); ShowFormulas = workbook.ShowFormulas; ShowGridLines = workbook.ShowGridLines; @@ -82,6 +81,10 @@ #endregion //private IXLStyle _style; + private const String InvalidNameChars = @":\/?*[]"; + public string LegacyDrawingId; + public Boolean LegacyDrawingIsNew; + private Double _columnWidth; public XLWorksheetInternals Internals { get; private set; } public override IEnumerable Styles @@ -96,16 +99,6 @@ } } - public HashSet GetStyleIds() - { - var retVal = new HashSet {GetStyleId()}; - foreach (int id in Internals.CellsCollection.GetCells().Select(c => c.GetStyleId()).Where(id => !retVal.Contains(id))) - { - retVal.Add(id); - } - return retVal; - } - public override Boolean UpdatingStyle { get; set; } public override IXLStyle InnerStyle @@ -121,30 +114,13 @@ public String RelId { get; set; } public XLDataValidations DataValidations { get; private set; } public IXLCharts Charts { get; private set; } + public XLSheetProtection Protection { get; private set; } + public XLAutoFilter AutoFilter { get; private set; } #region IXLWorksheet Members public XLWorkbook Workbook { get; private set; } - //private Int32 _styleCacheId; - //public new Int32 GetStyleId() - //{ - // if (StyleChanged) - // SetStyle(Style); - - // return _styleCacheId; - //} - //private new void SetStyle(IXLStyle styleToUse) - //{ - // _styleCacheId = Worksheet.Workbook.GetStyleId(styleToUse); - // _style = null; - // StyleChanged = false; - //} - //private new IXLStyle GetStyle() - //{ - // return _style ?? (_style = new XLStyle(this, Worksheet.Workbook.GetStyleById(_styleCacheId))); - //} - public override IXLStyle Style { get { return GetStyle(); } @@ -156,16 +132,9 @@ } } - private Double _columnWidth; - public string LegacyDrawingId; - public Boolean LegacyDrawingIsNew; - public Double ColumnWidth { - get - { - return _columnWidth; - } + get { return _columnWidth; } set { ColumnWidthChanged = true; @@ -183,14 +152,14 @@ } } - private const String InvalidNameChars = @":\/?*[]"; public String Name { get { return _name; } set { if (value.IndexOfAny(InvalidNameChars.ToCharArray()) != -1) - throw new ArgumentException("Worksheet names cannot contain any of the following characters: " + InvalidNameChars); + throw new ArgumentException("Worksheet names cannot contain any of the following characters: " + + InvalidNameChars); if (StringExtensions.IsNullOrWhiteSpace(value)) throw new ArgumentException("Worksheet names cannot be empty"); @@ -232,113 +201,61 @@ public IXLPageSetup PageSetup { get; private set; } public IXLOutline Outline { get; private set; } - public XLRow FirstRowUsed() - { - return FirstRowUsed(false); - } - IXLRow IXLWorksheet.FirstRowUsed() { return FirstRowUsed(); } - public XLRow FirstRowUsed(Boolean includeFormats) - { - var rngRow = AsRange().FirstRowUsed(includeFormats); - return rngRow != null ? Row(rngRow.RangeAddress.FirstAddress.RowNumber) : null; - } - IXLRow IXLWorksheet.FirstRowUsed(Boolean includeFormats) { return FirstRowUsed(includeFormats); } - public XLRow LastRowUsed() - { - return LastRowUsed(false); - } IXLRow IXLWorksheet.LastRowUsed() { return LastRowUsed(); } - public XLRow LastRowUsed(Boolean includeFormats) - { - var rngRow = AsRange().LastRowUsed(includeFormats); - return rngRow != null ? Row(rngRow.RangeAddress.LastAddress.RowNumber) : null; - } + IXLRow IXLWorksheet.LastRowUsed(Boolean includeFormats) { return LastRowUsed(includeFormats); } - public XLColumn LastColumn() - { - return Column(ExcelHelper.MaxColumnNumber); - } IXLColumn IXLWorksheet.LastColumn() { return LastColumn(); } - public XLColumn FirstColumn() - { - return Column(1); - } IXLColumn IXLWorksheet.FirstColumn() { return FirstColumn(); } - public XLRow FirstRow() - { - return Row(1); - } IXLRow IXLWorksheet.FirstRow() { return FirstRow(); } - public XLRow LastRow() - { - return Row(ExcelHelper.MaxRowNumber); - } IXLRow IXLWorksheet.LastRow() { return LastRow(); } - public XLColumn FirstColumnUsed() - { - return FirstColumnUsed(false); - } IXLColumn IXLWorksheet.FirstColumnUsed() { return FirstColumnUsed(); } - public XLColumn FirstColumnUsed(Boolean includeFormats) - { - var rngColumn = AsRange().FirstColumnUsed(includeFormats); - return rngColumn != null ? Column(rngColumn.RangeAddress.FirstAddress.ColumnNumber) : null; - } IXLColumn IXLWorksheet.FirstColumnUsed(Boolean includeFormats) { return FirstColumnUsed(includeFormats); } - public XLColumn LastColumnUsed() - { - return LastColumnUsed(false); - } + IXLColumn IXLWorksheet.LastColumnUsed() { return LastColumnUsed(); } - public XLColumn LastColumnUsed(Boolean includeFormats) - { - var rngColumn = AsRange().LastColumnUsed(includeFormats); - return rngColumn != null ? Column(rngColumn.RangeAddress.LastAddress.ColumnNumber) : null; - } IXLColumn IXLWorksheet.LastColumnUsed(Boolean includeFormats) { return LastColumnUsed(includeFormats); @@ -463,42 +380,16 @@ return retVal; } - public XLRow Row(Int32 row) - { - return Row(row, true); - } - IXLRow IXLWorksheet.Row(Int32 row) { return Row(row); } - public XLColumn Column(Int32 column) - { - if (column <= 0 || column > ExcelHelper.MaxColumnNumber) - throw new IndexOutOfRangeException(String.Format("Column number must be between 1 and {0}", ExcelHelper.MaxColumnNumber)); - - Int32 thisStyleId = GetStyleId(); - if (!Internals.ColumnsCollection.ContainsKey(column)) - { - // This is a new row so we're going to reference all - // cells in this row to preserve their formatting - Internals.RowsCollection.Keys.ForEach(r => Cell(r, column)); - Internals.ColumnsCollection.Add(column, new XLColumn(column, new XLColumnParameters(this, thisStyleId, false))); - } - - return new XLColumn(column, new XLColumnParameters(this, thisStyleId, true)); - } - IXLColumn IXLWorksheet.Column(Int32 column) { return Column(column); } - public IXLColumn Column(String column) - { - return Column(ExcelHelper.GetColumnNumberFromLetter(column)); - } IXLColumn IXLWorksheet.Column(String column) { return Column(column); @@ -554,11 +445,6 @@ return Range(firstCellRow, firstCellColumn, lastCellRow, lastCellColumn); } - public override IXLRange AsRange() - { - return Range(1, 1, ExcelHelper.MaxRowNumber, ExcelHelper.MaxColumnNumber); - } - public IXLWorksheet CollapseRows() { Enumerable.Range(1, 8).ForEach(i => CollapseRows(i)); @@ -624,14 +510,6 @@ Workbook.WorksheetsInternal.Delete(Name); } - public void Clear() - { - Internals.CellsCollection.Clear(); - Internals.ColumnsCollection.Clear(); - Internals.MergedRanges.Clear(); - Internals.RowsCollection.Clear(); - } - public IXLNamedRanges NamedRanges { get; private set; } public IXLNamedRange NamedRange(String rangeName) @@ -646,6 +524,7 @@ { return Tables.Table(index); } + public IXLTable Table(String name) { return Tables.Table(name); @@ -697,9 +576,9 @@ foreach (XLTable t in Tables.Cast()) { String tableName = t.Name; - XLTable table = targetSheet.Tables.Any(tt => tt.Name == tableName) - ? new XLTable(targetSheet.Range(t.RangeAddress.ToString()), true) - : new XLTable(targetSheet.Range(t.RangeAddress.ToString()), tableName, true); + var table = targetSheet.Tables.Any(tt => tt.Name == tableName) + ? new XLTable(targetSheet.Range(t.RangeAddress.ToString()), true) + : new XLTable(targetSheet.Range(t.RangeAddress.ToString()), tableName, true); table.RelId = t.RelId; table.EmphasizeFirstColumn = t.EmphasizeFirstColumn; @@ -749,9 +628,10 @@ return this; } - public XLSheetProtection Protection { get; private set; } - - IXLSheetProtection IXLWorksheet.Protection { get { return Protection; } } + IXLSheetProtection IXLWorksheet.Protection + { + get { return Protection; } + } public IXLSheetProtection Protect() { @@ -779,15 +659,20 @@ return GetRangeForSort().Sort(); } - public new IXLRange Sort(String columnsToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) + public new IXLRange Sort(String columnsToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, + Boolean matchCase = false, Boolean ignoreBlanks = true) { return GetRangeForSort().Sort(columnsToSortBy, sortOrder, matchCase, ignoreBlanks); } - public new IXLRange Sort(Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) + + public new IXLRange Sort(Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, + Boolean matchCase = false, Boolean ignoreBlanks = true) { return GetRangeForSort().Sort(columnToSortBy, sortOrder, matchCase, ignoreBlanks); } - public new IXLRange SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) + + public new IXLRange SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, + Boolean ignoreBlanks = true) { return GetRangeForSort().SortLeftToRight(sortOrder, matchCase, ignoreBlanks); } @@ -932,6 +817,80 @@ return this; } + IXLPivotTable IXLWorksheet.PivotTable(String name) + { + return PivotTable(name); + } + + public IXLPivotTables PivotTables { get; private set; } + + public Boolean RightToLeft { get; set; } + + public IXLWorksheet SetRightToLeft() + { + RightToLeft = true; + return this; + } + + public IXLWorksheet SetRightToLeft(Boolean value) + { + RightToLeft = value; + return this; + } + + public new IXLRanges Ranges(String ranges) + { + var retVal = new XLRanges(); + foreach (string rangeAddressStr in ranges.Split(',').Select(s => s.Trim())) + { + if (ExcelHelper.IsValidRangeAddress(rangeAddressStr)) + retVal.Add(Range(new XLRangeAddress(Worksheet, rangeAddressStr))); + else if (NamedRanges.Any(n => String.Compare(n.Name, rangeAddressStr, true) == 0)) + NamedRange(rangeAddressStr).Ranges.ForEach(retVal.Add); + else + { + Workbook.NamedRanges.First(n => + String.Compare(n.Name, rangeAddressStr, true) == 0 + && n.Ranges.First().Worksheet == this) + .Ranges.ForEach(retVal.Add); + } + } + return retVal; + } + + IXLBaseAutoFilter IXLWorksheet.AutoFilter + { + get { return AutoFilter; } + } + + public new IXLRows RowsUsed(Boolean includeFormats = false) + { + var rows = new XLRows(Worksheet); + foreach (IXLRangeRow row in base.RowsUsed(includeFormats)) + rows.Add(Row(row.RowNumber())); + return rows; + } + + public new IXLColumns ColumnsUsed(Boolean includeFormats = false) + { + var columns = new XLColumns(Worksheet); + foreach (IXLRangeColumn column in base.ColumnsUsed(includeFormats)) + columns.Add(Column(column.ColumnNumber())); + return columns; + } + + public new void Dispose() + { + if (AutoFilter != null) + AutoFilter.Dispose(); + + Internals.Dispose(); + + + + base.Dispose(); + } + #endregion #region Outlines @@ -986,7 +945,127 @@ #endregion - private void XLWorksheetRangeShiftedColumns(XLRange range, int columnsShifted) + public HashSet GetStyleIds() + { + var retVal = new HashSet {GetStyleId()}; + foreach ( + int id in + Internals.CellsCollection.GetCells().Select(c => c.GetStyleId()).Where(id => !retVal.Contains(id))) + retVal.Add(id); + return retVal; + } + + public XLRow FirstRowUsed() + { + return FirstRowUsed(false); + } + + public XLRow FirstRowUsed(Boolean includeFormats) + { + using (var asRange = AsRange()) + using (var rngRow = asRange.FirstRowUsed(includeFormats)) + return rngRow != null ? Row(rngRow.RangeAddress.FirstAddress.RowNumber) : null; + } + + public XLRow LastRowUsed() + { + return LastRowUsed(false); + } + + public XLRow LastRowUsed(Boolean includeFormats) + { + using (var asRange = AsRange()) + using (var rngRow = asRange.LastRowUsed(includeFormats)) + return rngRow != null ? Row(rngRow.RangeAddress.LastAddress.RowNumber) : null; + } + + public XLColumn LastColumn() + { + return Column(ExcelHelper.MaxColumnNumber); + } + + public XLColumn FirstColumn() + { + return Column(1); + } + + public XLRow FirstRow() + { + return Row(1); + } + + public XLRow LastRow() + { + return Row(ExcelHelper.MaxRowNumber); + } + + public XLColumn FirstColumnUsed() + { + return FirstColumnUsed(false); + } + + public XLColumn FirstColumnUsed(Boolean includeFormats) + { + using (var asRange = AsRange()) + using (var rngColumn = asRange.FirstColumnUsed(includeFormats)) + return rngColumn != null ? Column(rngColumn.RangeAddress.FirstAddress.ColumnNumber) : null; + } + + public XLColumn LastColumnUsed() + { + return LastColumnUsed(false); + } + + public XLColumn LastColumnUsed(Boolean includeFormats) + { + using (var asRange = AsRange()) + using (var rngColumn = asRange.LastColumnUsed(includeFormats)) + return rngColumn != null ? Column(rngColumn.RangeAddress.LastAddress.ColumnNumber) : null; + } + + public XLRow Row(Int32 row) + { + return Row(row, true); + } + + public XLColumn Column(Int32 column) + { + if (column <= 0 || column > ExcelHelper.MaxColumnNumber) + throw new IndexOutOfRangeException(String.Format("Column number must be between 1 and {0}", + ExcelHelper.MaxColumnNumber)); + + Int32 thisStyleId = GetStyleId(); + if (!Internals.ColumnsCollection.ContainsKey(column)) + { + // This is a new row so we're going to reference all + // cells in this row to preserve their formatting + Internals.RowsCollection.Keys.ForEach(r => Cell(r, column)); + Internals.ColumnsCollection.Add(column, + new XLColumn(column, new XLColumnParameters(this, thisStyleId, false))); + } + + return new XLColumn(column, new XLColumnParameters(this, thisStyleId, true)); + } + + public IXLColumn Column(String column) + { + return Column(ExcelHelper.GetColumnNumberFromLetter(column)); + } + + public override XLRange AsRange() + { + return Range(1, 1, ExcelHelper.MaxRowNumber, ExcelHelper.MaxColumnNumber); + } + + public void Clear() + { + Internals.CellsCollection.Clear(); + Internals.ColumnsCollection.Clear(); + Internals.MergedRanges.Clear(); + Internals.RowsCollection.Clear(); + } + + private void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) { var newMerge = new XLRanges(); foreach (IXLRange rngMerged in Internals.MergedRanges) @@ -1013,7 +1092,7 @@ MoveNamedRangesColumns(range, columnsShifted, Workbook.NamedRanges); } - private void XLWorksheetRangeShiftedRows(XLRange range, int rowsShifted) + private void WorksheetRangeShiftedRows(XLRange range, int rowsShifted) { var newMerge = new XLRanges(); foreach (IXLRange rngMerged in Internals.MergedRanges) @@ -1037,9 +1116,8 @@ } Internals.MergedRanges = newMerge; - Workbook.Worksheets.ForEach(ws=> MoveNamedRangesRows(range, rowsShifted, ws.NamedRanges)); + Workbook.Worksheets.ForEach(ws => MoveNamedRangesRows(range, rowsShifted, ws.NamedRanges)); MoveNamedRangesRows(range, rowsShifted, Workbook.NamedRanges); - } private void MoveNamedRangesRows(XLRange range, int rowsShifted, IXLNamedRanges namedRanges) @@ -1078,8 +1156,9 @@ public XLRow Row(Int32 row, Boolean pingCells) { - if(row <= 0 || row > ExcelHelper.MaxRowNumber) - throw new IndexOutOfRangeException(String.Format("Row number must be between 1 and {0}", ExcelHelper.MaxRowNumber)); + if (row <= 0 || row > ExcelHelper.MaxRowNumber) + throw new IndexOutOfRangeException(String.Format("Row number must be between 1 and {0}", + ExcelHelper.MaxRowNumber)); Int32 styleId; XLRow rowToUse; @@ -1115,29 +1194,10 @@ return range; } - IXLPivotTable IXLWorksheet.PivotTable(String name) - { - return PivotTable(name); - } public XLPivotTable PivotTable(String name) { return (XLPivotTable)PivotTables.PivotTable(name); } - public IXLPivotTables PivotTables { get; private set; } - - public Boolean RightToLeft { get; set; } - - public IXLWorksheet SetRightToLeft() - { - RightToLeft = true; - return this; - } - - public IXLWorksheet SetRightToLeft(Boolean value) - { - RightToLeft = value; - return this; - } public new XLCells Cells() { @@ -1146,18 +1206,17 @@ public new XLCell Cell(String cellAddressInRange) { - if (ExcelHelper.IsValidA1Address(cellAddressInRange)) return Cell(XLAddress.Create(this, cellAddressInRange)); - if (NamedRanges.Any(n=> String.Compare(n.Name, cellAddressInRange, true) == 0)) + if (NamedRanges.Any(n => String.Compare(n.Name, cellAddressInRange, true) == 0)) return (XLCell)NamedRange(cellAddressInRange).Ranges.First().FirstCell(); return (XLCell)Workbook.NamedRanges.First(n => - String.Compare(n.Name, cellAddressInRange, true) == 0 - && n.Ranges.First().Worksheet == this - && n.Ranges.Count == 1) - .Ranges.First().FirstCell(); + String.Compare(n.Name, cellAddressInRange, true) == 0 + && n.Ranges.First().Worksheet == this + && n.Ranges.Count == 1) + .Ranges.First().FirstCell(); } public XLCell CellFast(String cellAddressInRange) @@ -1174,54 +1233,10 @@ return (XLRange)NamedRange(rangeAddressStr).Ranges.First(); return (XLRange)Workbook.NamedRanges.First(n => - String.Compare(n.Name, rangeAddressStr, true) == 0 - && n.Ranges.First().Worksheet == this - && n.Ranges.Count == 1) - .Ranges.First(); - } - - public new IXLRanges Ranges(String ranges) - { - var retVal = new XLRanges(); - foreach (var rangeAddressStr in ranges.Split(',').Select(s=>s.Trim())) - { - if (ExcelHelper.IsValidRangeAddress(rangeAddressStr)) - retVal.Add(Range(new XLRangeAddress(Worksheet, rangeAddressStr))); - else if (NamedRanges.Any(n => String.Compare(n.Name, rangeAddressStr, true) == 0)) - NamedRange(rangeAddressStr).Ranges.ForEach(retVal.Add); - else - Workbook.NamedRanges.First(n => - String.Compare(n.Name, rangeAddressStr, true) == 0 - && n.Ranges.First().Worksheet == this) - .Ranges.ForEach(retVal.Add); - } - return retVal; - } - - IXLBaseAutoFilter IXLWorksheet.AutoFilter - { - get { return AutoFilter; } - } - public XLAutoFilter AutoFilter { get; private set; } - - public new IXLRows RowsUsed(Boolean includeFormats = false) - { - var rows = new XLRows(Worksheet); - foreach (var row in base.RowsUsed(includeFormats)) - { - rows.Add(Row(row.RowNumber())); - } - return rows; - } - - public new IXLColumns ColumnsUsed(Boolean includeFormats = false) - { - var columns = new XLColumns(Worksheet); - foreach (var column in base.ColumnsUsed(includeFormats)) - { - columns.Add(Column(column.ColumnNumber())); - } - return columns; + String.Compare(n.Name, rangeAddressStr, true) == 0 + && n.Ranges.First().Worksheet == this + && n.Ranges.Count == 1) + .Ranges.First(); } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs index 24f61ec..e7b7b0c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheetInternals.cs @@ -1,6 +1,7 @@ -namespace ClosedXML.Excel +using System; +namespace ClosedXML.Excel { - internal class XLWorksheetInternals + internal class XLWorksheetInternals: IDisposable { public XLWorksheetInternals( XLCellsCollection cellsCollection, @@ -19,5 +20,12 @@ public XLColumnsCollection ColumnsCollection { get; private set; } public XLRowsCollection RowsCollection { get; private set; } public XLRanges MergedRanges { get; internal set; } + + public void Dispose() + { + ColumnsCollection.Dispose(); + RowsCollection.Dispose(); + MergedRanges.Dispose(); + } } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs index c4ee6ef..35c435b 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs @@ -7,20 +7,37 @@ namespace ClosedXML.Excel { - internal class XLWorksheets : IXLWorksheets,IEnumerable + internal class XLWorksheets : IXLWorksheets, IEnumerable { #region Constructor - private readonly Dictionary m_worksheets = new Dictionary(); + private readonly XLWorkbook m_workbook; + private readonly Dictionary m_worksheets = new Dictionary(); + #endregion + public HashSet Deleted = new HashSet(); + #region Constructor + public XLWorksheets(XLWorkbook workbook) { m_workbook = workbook; } + #endregion + + #region IEnumerable Members + + public IEnumerator GetEnumerator() + { + return ((IEnumerable)m_worksheets.Values).GetEnumerator(); + } + + #endregion + #region IXLWorksheets Members + public int Count { [DebuggerStepThrough] @@ -45,7 +62,7 @@ if (m_worksheets.TryGetValue(sheetName, out w)) return w; - var wss = m_worksheets.Where(ws=> ws.Key.ToLower().Equals(sheetName.ToLower())); + var wss = m_worksheets.Where(ws => ws.Key.ToLower().Equals(sheetName.ToLower())); if (wss.Any()) return wss.First().Value; @@ -55,28 +72,17 @@ public IXLWorksheet Worksheet(Int32 position) { - var wsCount = m_worksheets.Values.Where(w => w.Position == position).Count(); + int wsCount = m_worksheets.Values.Count(w => w.Position == position); if (wsCount == 0) - { throw new Exception("There isn't a worksheet associated with that position."); - } if (wsCount > 1) { - throw new Exception("Can't retrieve a worksheet because there are multiple worksheets associated with that position."); + throw new Exception( + "Can't retrieve a worksheet because there are multiple worksheets associated with that position."); } - return m_worksheets.Values.Where(w => w.Position == position).Single(); - } - - public void Rename(String oldSheetName, String newSheetName) - { - if (!StringExtensions.IsNullOrWhiteSpace(oldSheetName) && m_worksheets.ContainsKey(oldSheetName)) - { - var ws = m_worksheets[oldSheetName]; - m_worksheets.Remove(oldSheetName); - m_worksheets.Add(newSheetName, ws); - } + return m_worksheets.Values.Single(w => w.Position == position); } public IXLWorksheet Add(String sheetName) @@ -101,51 +107,32 @@ public void Delete(Int32 position) { - var wsCount = m_worksheets.Values.Where(w => w.Position == position).Count(); + int wsCount = m_worksheets.Values.Count(w => w.Position == position); if (wsCount == 0) - { throw new Exception("There isn't a worksheet associated with that index."); - } if (wsCount > 1) - { - throw new Exception("Can't delete the worksheet because there are multiple worksheets associated with that index."); - } + throw new Exception( + "Can't delete the worksheet because there are multiple worksheets associated with that index."); - var ws = m_worksheets.Values.Where(w => w.Position == position).Single(); + var ws = m_worksheets.Values.Single(w => w.Position == position); if (!StringExtensions.IsNullOrWhiteSpace(ws.RelId) && !Deleted.Contains(ws.RelId)) - { Deleted.Add(ws.RelId); - } m_worksheets.RemoveAll(w => w.Position == position); m_worksheets.Values.Where(w => w.Position > position).ForEach(w => (w)._position -= 1); } - #endregion - #region IEnumerable Members - public IEnumerator GetEnumerator() - { - foreach (var w in m_worksheets.Values) - { - yield return w; - } - } - #endregion - #region IEnumerable Members + IEnumerator IEnumerable.GetEnumerator() { - foreach (var w in m_worksheets.Values) - { - yield return w; - } + return m_worksheets.Values.Cast().GetEnumerator(); } - #endregion - #region IEnumerable Members + IEnumerator IEnumerable.GetEnumerator() { return GetEnumerator(); } - #endregion + public IXLWorksheet Add(DataTable dataTable) { var ws = Add(dataTable.TableName); @@ -153,12 +140,22 @@ ws.Columns().AdjustToContents(1, 75); return ws; } + public void Add(DataSet dataSet) { foreach (DataTable t in dataSet.Tables) - { Add(t); - } + } + + #endregion + + public void Rename(String oldSheetName, String newSheetName) + { + if (StringExtensions.IsNullOrWhiteSpace(oldSheetName) || !m_worksheets.ContainsKey(oldSheetName)) return; + + var ws = m_worksheets[oldSheetName]; + m_worksheets.Remove(oldSheetName); + m_worksheets.Add(newSheetName, ws); } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs index fd5a38b..8338961 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs +++ b/ClosedXML/ClosedXML/ClosedXML/ExcelHelper.cs @@ -19,6 +19,7 @@ public const int MaxRowNumber = 1048576; public const int MaxColumnNumber = 16384; public const String MaxColumnLetter = "XFD"; + public const Double Epsilon = 1e-10; private const Int32 TwoT26 = 26 * 26; internal static readonly NumberFormatInfo NumberFormatForParse = CultureInfo.InvariantCulture.NumberFormat; diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataValidation.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataValidation.cs index c0f9415..5606a69 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataValidation.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Misc/DataValidation.cs @@ -117,6 +117,8 @@ rng7Validation.Decimal.EqualTo(5); rng7Validation.IgnoreBlanks = true; + ws.CopyTo(ws.Name + " - Copy"); + ws2.CopyTo(ws2.Name + " - Copy"); wb.SaveAs(filePath); } diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj index 7de7853..0aa306b 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -71,6 +71,7 @@ + diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs new file mode 100644 index 0000000..68aad02 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Excel/AutoFilters/AutoFilterTests.cs @@ -0,0 +1,31 @@ +using ClosedXML.Excel; +using Microsoft.VisualStudio.TestTools.UnitTesting; +using System.Linq; +using System; + +namespace ClosedXML_Tests +{ + [TestClass()] + public class AutoFilterTests + { + [TestMethod()] + public void AutoFilterSortWhenNotInFirstRow() + { + using (var wb = new XLWorkbook()) + { + using (var ws = wb.Worksheets.Add("Sheet1")) + { + ws.Cell(3, 3).SetValue("Names") + .CellBelow().SetValue("Manuel") + .CellBelow().SetValue("Carlos") + .CellBelow().SetValue("Dominic"); + ws.RangeUsed().SetAutoFilter().Sort(); + Assert.AreEqual(ws.Cell(4, 3).GetString(), "Carlos"); + } + + } + + } + + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx index 17f2cf8..8f8d597 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/DataValidation.xlsx Binary files differ