diff --git a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj index f4f73d2..80bba6a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML/ClosedXML/ClosedXML.csproj @@ -124,7 +124,18 @@ + + + + + + + + + + + diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs index e942fba..5b1285d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLAutoFilter.cs @@ -1,9 +1,19 @@ using System; -namespace ClosedXML.Excel.AutoFilters +namespace ClosedXML.Excel { + using System.Collections.Generic; + public interface IXLAutoFilter { - IXLRange Range { get; } + //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); + Boolean Sorted { get; set; } + XLSortOrder SortOrder { get; set; } + Int32 SortColumn { get; set; } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs new file mode 100644 index 0000000..b3f8d66 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs @@ -0,0 +1,25 @@ +using System; +namespace ClosedXML.Excel +{ + using System.Collections.Generic; + public enum XLFilterType { Regular, Custom, TopBottom, Dynamic } + public enum XLFilterDynamicType { AboveAverage, BelowAverage } + public enum XLTopBottomPart { Top, Bottom} + public interface IXLBaseAutoFilter + { + Boolean Enabled { get; set; } + IXLRange Range { get; set; } + IXLBaseAutoFilter Set(IXLRangeBase range); + IXLBaseAutoFilter Clear(); + + IXLFilterColumn Column(String column); + IXLFilterColumn Column(Int32 column); + + IXLBaseAutoFilter Sort(Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); + Boolean Sorted { get; set; } + XLSortOrder SortOrder { get; set; } + Int32 SortColumn { get; set; } + + + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLCustomFilteredColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLCustomFilteredColumn.cs new file mode 100644 index 0000000..959d3de --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLCustomFilteredColumn.cs @@ -0,0 +1,21 @@ +using System; +namespace ClosedXML.Excel +{ + using System.Collections.Generic; + + public interface IXLCustomFilteredColumn + { + void EqualTo(T value) where T : IComparable; + void NotEqualTo(T value) where T : IComparable; + void GreaterThan(T value) where T : IComparable; + void LessThan(T value) where T : IComparable; + void EqualOrGreaterThan(T value) where T : IComparable; + void EqualOrLessThan(T value) where T : IComparable; + void BeginsWith(String value); + void NotBeginsWith(String value); + void EndsWith(String value); + void NotEndsWith(String value); + void Contains(String value); + void NotContains(String value); + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs new file mode 100644 index 0000000..a6944c7 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs @@ -0,0 +1,42 @@ +using System; +namespace ClosedXML.Excel +{ + using System.Collections.Generic; + + public enum XLTopBottomType { Items, Percent } + public interface IXLFilterColumn + { + void Clear(); + + IXLFilteredColumn AddFilter(T value) where T : IComparable; + + void Top(Int32 value, XLTopBottomType type = XLTopBottomType.Items); + void Bottom(Int32 value, XLTopBottomType type = XLTopBottomType.Items); + void AboveAverage(); + void BelowAverage(); + + IXLFilterConnector EqualTo(T value) where T : IComparable; + IXLFilterConnector NotEqualTo(T value) where T : IComparable; + IXLFilterConnector GreaterThan(T value) where T : IComparable; + IXLFilterConnector LessThan(T value) where T : IComparable; + IXLFilterConnector EqualOrGreaterThan(T value) where T : IComparable; + IXLFilterConnector EqualOrLessThan(T value) where T : IComparable; + void Between(T minValue, T maxValue) where T : IComparable; + void NotBetween(T minValue, T maxValue) where T : IComparable; + IXLFilterConnector BeginsWith(String value); + IXLFilterConnector NotBeginsWith(String value); + IXLFilterConnector EndsWith(String value); + IXLFilterConnector NotEndsWith(String value); + IXLFilterConnector Contains(String value); + IXLFilterConnector NotContains(String value); + + XLFilterType FilterType { get; set; } + + Int32 TopBottomValue { get; set; } + XLTopBottomType TopBottomType { get; set; } + XLTopBottomPart TopBottomPart { get; set; } + + XLFilterDynamicType DynamicType { get; set; } + Double DynamicValue { get; set; } + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilterConnector.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilterConnector.cs new file mode 100644 index 0000000..edb8560 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilterConnector.cs @@ -0,0 +1,13 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public interface IXLFilterConnector + { + IXLCustomFilteredColumn And { get; } + IXLCustomFilteredColumn Or { get; } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilteredColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilteredColumn.cs new file mode 100644 index 0000000..d3f31d8 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/IXLFilteredColumn.cs @@ -0,0 +1,10 @@ +using System; +namespace ClosedXML.Excel +{ + using System.Collections.Generic; + + public interface IXLFilteredColumn + { + IXLFilteredColumn AddFilter(T value) where T : IComparable; + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs new file mode 100644 index 0000000..75cf8f0 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs @@ -0,0 +1,136 @@ +using System; +using System.Linq; +namespace ClosedXML.Excel +{ + using System.Collections.Generic; + + internal class XLAutoFilter: IXLBaseAutoFilter, IXLAutoFilter + { + public XLAutoFilter() + { + Filters = new Dictionary>(); + } + + 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() + { + return Clear(); + } + + IXLBaseAutoFilter IXLBaseAutoFilter.Set(IXLRangeBase range) + { + 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; + } + + } + Enabled = true; + } + + 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) + { + return Sort(columnToSortBy, sortOrder, matchCase, ignoreBlanks); + } + + public Boolean Sorted { get; set; } + 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; + if (!_columns.TryGetValue(column, out filterColumn)) + { + filterColumn = new XLFilterColumn(this, column); + _columns.Add(column, filterColumn); + } + + return filterColumn; + } + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLCustomFilteredColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLCustomFilteredColumn.cs new file mode 100644 index 0000000..4f37c94 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLCustomFilteredColumn.cs @@ -0,0 +1,104 @@ +using System; +using System.Linq; +namespace ClosedXML.Excel +{ + using System.Collections.Generic; + + internal class XLCustomFilteredColumn: IXLCustomFilteredColumn + { + private XLAutoFilter _autoFilter; + private Int32 _column; + private XLConnector _connector; + public XLCustomFilteredColumn(XLAutoFilter autoFilter, Int32 column, XLConnector connector) + { + _autoFilter = autoFilter; + _column = column; + _connector = connector; + } + + 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)); + else + ApplyCustomFilter(value, XLFilterOperator.Equal, v => (v.CastTo() as IComparable).CompareTo(value) == 0); + } + + 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)); + else + ApplyCustomFilter(value, XLFilterOperator.NotEqual, v => (v.CastTo() as IComparable).CompareTo(value) != 0); + } + + public void GreaterThan(T value) where T : IComparable + { + ApplyCustomFilter(value, XLFilterOperator.GreaterThan, v => (v.CastTo() as IComparable).CompareTo(value) > 0); + } + + public void LessThan(T value) where T : IComparable + { + ApplyCustomFilter(value, XLFilterOperator.LessThan, v => (v.CastTo() as IComparable).CompareTo(value) < 0); + } + + public void EqualOrGreaterThan(T value) where T : IComparable + { + ApplyCustomFilter(value, XLFilterOperator.EqualOrGreaterThan, v => (v.CastTo() as IComparable).CompareTo(value) >= 0); + } + + public void EqualOrLessThan(T value) where T : IComparable + { + ApplyCustomFilter(value, XLFilterOperator.EqualOrLessThan, v => (v.CastTo() as IComparable).CompareTo(value) <= 0); + } + + public void BeginsWith(String value) + { + ApplyCustomFilter(value.ToString() + "*", XLFilterOperator.Equal, s => ((string)s).StartsWith(value.ToString(), StringComparison.InvariantCultureIgnoreCase)); + } + + public void NotBeginsWith(String value) + { + ApplyCustomFilter(value.ToString() + "*", XLFilterOperator.NotEqual, s => !((string)s).StartsWith(value.ToString(), StringComparison.InvariantCultureIgnoreCase)); + } + + public void EndsWith(String value) + { + ApplyCustomFilter("*" + value.ToString(), XLFilterOperator.Equal, s => ((string)s).EndsWith(value.ToString(), StringComparison.InvariantCultureIgnoreCase)); + } + + public void NotEndsWith(String value) + { + ApplyCustomFilter("*" + value.ToString(), XLFilterOperator.NotEqual, s => !((string)s).EndsWith(value.ToString(), 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())); + } + + 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 }); + foreach (var row in _autoFilter.Range.Rows().Where(r => r.RowNumber() > 1)) + { + if (_connector == XLConnector.And) + { + if (!row.WorksheetRow().IsHidden) + { + if (condition(row.Cell(_column).GetValue())) + row.WorksheetRow().Unhide(); + else + row.WorksheetRow().Hide(); + } + } + else if (condition(row.Cell(_column).GetValue())) + row.WorksheetRow().Unhide(); + } + } + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilter.cs new file mode 100644 index 0000000..1f0f639 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilter.cs @@ -0,0 +1,22 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + public enum XLConnector { And, Or } + internal enum XLFilterOperator { Equal, NotEqual, GreaterThan, LessThan, EqualOrGreaterThan, EqualOrLessThan } + internal class XLFilter + { + public XLFilter(XLFilterOperator op = XLFilterOperator.Equal) + { + Operator = op; + } + + public XLFilterOperator Operator { get; set; } + public Object Value { get; set; } + public XLConnector Connector { get; set; } + public Func Condition { get; set; } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs new file mode 100644 index 0000000..751d646 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs @@ -0,0 +1,271 @@ +using System; +using System.Linq; +namespace ClosedXML.Excel +{ + using System.Collections.Generic; + + internal class XLFilterColumn: IXLFilterColumn + { + private XLAutoFilter _autoFilter; + private 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() + { + if (_autoFilter.Filters.ContainsKey(_column)) + _autoFilter.Filters.Remove(_column); + } + 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); + else + ApplyCustomFilter(value, XLFilterOperator.Equal, v => (v.CastTo() as IComparable).CompareTo(value) == 0, XLFilterType.Regular); + return new XLFilteredColumn(_autoFilter, _column); + } + + public void Top(Int32 value, XLTopBottomType type = XLTopBottomType.Items) + { + _autoFilter.Column(_column).TopBottomPart = XLTopBottomPart.Top; + SetTopBottom(value, type); + + } + + public void Bottom(Int32 value, XLTopBottomType type = XLTopBottomType.Items) + { + _autoFilter.Column(_column).TopBottomPart = XLTopBottomPart.Bottom; + SetTopBottom(value, type, false); + } + + private void SetTopBottom(Int32 value, XLTopBottomType type, Boolean takeTop = 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) + { + _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)) + { + 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 IXLFilterConnector EqualTo(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); + } + + public IXLFilterConnector NotEqualTo(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); + } + + public IXLFilterConnector GreaterThan(T value) where T : IComparable + { + return ApplyCustomFilter(value, XLFilterOperator.GreaterThan, v => (v.CastTo() as IComparable).CompareTo(value) > 0); + } + + public IXLFilterConnector LessThan(T value) where T : IComparable + { + return ApplyCustomFilter(value, XLFilterOperator.LessThan, v => (v.CastTo() as IComparable).CompareTo(value) < 0); + } + + public IXLFilterConnector EqualOrGreaterThan(T value) where T : IComparable + { + return ApplyCustomFilter(value, XLFilterOperator.EqualOrGreaterThan, v => (v.CastTo() as IComparable).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 + { + EqualOrGreaterThan(minValue).And.EqualOrLessThan(maxValue); + } + public void NotBetween(T minValue, T maxValue) where T : IComparable + { + LessThan(minValue).Or.GreaterThan(maxValue); + } + + public IXLFilterConnector BeginsWith(String value) + { + 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)); + } + + public IXLFilterConnector EndsWith(String value) + { + 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)); + } + + public IXLFilterConnector Contains(String value) + { + 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 + { + 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); + } + + public XLFilterType FilterType { get; set; } + + public Int32 TopBottomValue { get; set; } + public XLTopBottomType TopBottomType { get; set; } + public XLTopBottomPart TopBottomPart { get; set; } + + public XLFilterDynamicType DynamicType { get; set; } + public Double DynamicValue { get; set; } + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilterConnector.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilterConnector.cs new file mode 100644 index 0000000..2eca0fc --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilterConnector.cs @@ -0,0 +1,20 @@ +using System; +using System.Collections.Generic; +using System.Linq; +using System.Text; + +namespace ClosedXML.Excel +{ + internal class XLFilterConnector: IXLFilterConnector + { + XLAutoFilter _autoFilter; + 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); } } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs new file mode 100644 index 0000000..18c9210 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs @@ -0,0 +1,43 @@ +using System; +using System.Linq; +namespace ClosedXML.Excel +{ + using System.Collections.Generic; + + internal class XLFilteredColumn: IXLFilteredColumn + { + XLAutoFilter _autoFilter; + Int32 _column; + public XLFilteredColumn(XLAutoFilter autoFilter, Int32 column) + { + _autoFilter = autoFilter; + _column = column; + } + + public IXLFilteredColumn AddFilter(T value) where T: IComparable + { + Func condition; + Boolean isText; + if (typeof(T) == typeof(String)) + { + condition = v => v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase); + isText = true; + } + else + { + condition = v => (v.CastTo() as IComparable).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)) + { + if ((isText && condition(row.Cell(_column).GetString())) || ( + !isText && row.Cell(_column).DataType == XLCellValues.Number && condition(row.Cell(_column).GetValue())) + ) + row.WorksheetRow().Unhide(); + } + return this; + } + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs index 5b467ae..547b75c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumn.cs @@ -155,13 +155,7 @@ IXLRangeColumn CopyTo(IXLRangeBase range); IXLColumn CopyTo(IXLColumn column); - void SetAutoFilter(); - void SetAutoFilter(Boolean autoFilter); - - IXLColumn Sort(); - IXLColumn Sort(Boolean matchCase); - IXLColumn Sort(XLSortOrder sortOrder); - IXLColumn Sort(XLSortOrder sortOrder, Boolean matchCase); + IXLColumn Sort(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); IXLRangeColumn Column(Int32 start, Int32 end); IXLRangeColumn Column(IXLCell start, IXLCell end); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs index 017dbfe..5f85030 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/IXLColumns.cs @@ -102,8 +102,8 @@ /// /// Returns the collection of cells that have a value. /// - /// if set to true will return all cells with a value or a style different than the default. - IXLCells CellsUsed(Boolean includeStyles); + /// if set to true will return all cells with a value or a style different than the default. + IXLCells CellsUsed(Boolean includeFormats); IXLStyle Style { get; set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs index cca5504..2c7eb6d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumn.cs @@ -521,27 +521,9 @@ return RangeAddress.LastAddress.ColumnNumber - RangeAddress.FirstAddress.ColumnNumber + 1; } - public IXLColumn Sort() + public IXLColumn Sort(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) { - RangeUsed().Sort(); - return this; - } - - public IXLColumn Sort(XLSortOrder sortOrder) - { - RangeUsed().Sort(sortOrder); - return this; - } - - public IXLColumn Sort(Boolean matchCase) - { - AsRange().Sort(matchCase); - return this; - } - - public IXLColumn Sort(XLSortOrder sortOrder, Boolean matchCase) - { - AsRange().Sort(sortOrder, matchCase); + base.Sort(1, sortOrder, matchCase, ignoreBlanks); return this; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs index 1855dd0..17ab1fa 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Columns/XLColumns.cs @@ -191,9 +191,9 @@ return cells; } - public IXLCells CellsUsed(Boolean includeStyles) + public IXLCells CellsUsed(Boolean includeFormats) { - var cells = new XLCells(true, includeStyles); + var cells = new XLCells(true, includeFormats); foreach (XLColumn container in _columns) cells.Add(container.RangeAddress); return cells; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/EnumConverter.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/EnumConverter.cs index 9232c44..e3a3897 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/EnumConverter.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/EnumConverter.cs @@ -476,6 +476,36 @@ #endregion } } + public static FilterOperatorValues ToOpenXml(this XLFilterOperator value) + { + switch(value) + { + case XLFilterOperator.Equal: return FilterOperatorValues.Equal; + case XLFilterOperator.NotEqual: return FilterOperatorValues.NotEqual; + case XLFilterOperator.GreaterThan: return FilterOperatorValues.GreaterThan; + case XLFilterOperator.EqualOrGreaterThan: return FilterOperatorValues.GreaterThanOrEqual; + case XLFilterOperator.LessThan: return FilterOperatorValues.LessThan; + case XLFilterOperator.EqualOrLessThan: return FilterOperatorValues.LessThanOrEqual; + #region default + default: + throw new ApplicationException("Not implemented value!"); + #endregion + } + } + + public static DynamicFilterValues ToOpenXml(this XLFilterDynamicType value) + { + switch (value) + { + case XLFilterDynamicType.AboveAverage: return DynamicFilterValues.AboveAverage; + case XLFilterDynamicType.BelowAverage: return DynamicFilterValues.BelowAverage; + #region default + default: + throw new ApplicationException("Not implemented value!"); + #endregion + } + } + #endregion #region To ClosedXml public static XLFontUnderlineValues ToClosedXml(this UnderlineValues value) @@ -946,6 +976,37 @@ #endregion } } + public static XLFilterOperator ToClosedXml(this FilterOperatorValues value) + { + switch (value) + { + case FilterOperatorValues.Equal: return XLFilterOperator.Equal; + case FilterOperatorValues.NotEqual: return XLFilterOperator.NotEqual; + case FilterOperatorValues.GreaterThan: return XLFilterOperator.GreaterThan; + case FilterOperatorValues.LessThan: return XLFilterOperator.LessThan; + case FilterOperatorValues.GreaterThanOrEqual: return XLFilterOperator.EqualOrGreaterThan; + case FilterOperatorValues.LessThanOrEqual: return XLFilterOperator.EqualOrLessThan; + + #region default + default: + throw new ApplicationException("Not implemented value!"); + #endregion + } + } + public static XLFilterDynamicType ToClosedXml(this DynamicFilterValues value) + { + switch (value) + { + case DynamicFilterValues.AboveAverage: return XLFilterDynamicType.AboveAverage; + case DynamicFilterValues.BelowAverage: return XLFilterDynamicType.BelowAverage; + + #region default + default: + throw new ApplicationException("Not implemented value!"); + #endregion + } + } + #endregion } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs index 5315b49..5039b6c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/IXLWorksheet.cs @@ -295,7 +295,7 @@ IXLWorksheet CopyTo(XLWorkbook workbook, String newSheetName, Int32 position); IXLRange RangeUsed(); - IXLRange RangeUsed(bool includeStyles); + IXLRange RangeUsed(bool includeFormats); IXLDataValidations DataValidations { get; } @@ -309,24 +309,14 @@ IXLSheetProtection Unprotect(); IXLSheetProtection Unprotect(String password); - IXLRangeBase AutoFilterRange { get; set; } - IXLSortElements SortRows { get; } IXLSortElements SortColumns { get; } IXLRange Sort(); - IXLRange Sort(Boolean matchCase); - IXLRange Sort(XLSortOrder sortOrder); - IXLRange Sort(XLSortOrder sortOrder, Boolean matchCase); - IXLRange Sort(String columnsToSortBy); - IXLRange Sort(String columnsToSortBy, Boolean matchCase); - IXLRange Sort(XLSortOrientation sortOrientation); - IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder); - IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy); - IXLRange Sort(XLSortOrientation sortOrientation, Boolean matchCase); - IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder, Boolean matchCase); - IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy, Boolean matchCase); + IXLRange Sort(String columnsToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); + IXLRange Sort(Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); + IXLRange SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); //IXLCharts Charts { get; } @@ -362,6 +352,9 @@ Boolean RightToLeft { get; set; } IXLWorksheet SetRightToLeft(); IXLWorksheet SetRightToLeft(Boolean value); - + IXLBaseAutoFilter AutoFilter { get; } + + IXLRows RowsUsed(Boolean includeFormats = false); + IXLColumns ColumnsUsed(Boolean includeFormats = false); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLBaseCollection.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLBaseCollection.cs index 115776c..85d6c6f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLBaseCollection.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLBaseCollection.cs @@ -63,8 +63,8 @@ /// /// Returns the collection of cells that have a value. /// - /// if set to true will return all cells with a value or a style different than the default. - IXLCells CellsUsed(Boolean includeStyles); + /// if set to true will return all cells with a value or a style different than the default. + IXLCells CellsUsed(Boolean includeFormats); TMultiple SetDataType(XLCellValues dataType); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs index 726b951..973da30 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRange.cs @@ -221,25 +221,15 @@ IXLRange CopyTo(IXLCell target); IXLRange CopyTo(IXLRangeBase target); - void SetAutoFilter(); - void SetAutoFilter(Boolean autoFilter); - IXLSortElements SortRows { get; } IXLSortElements SortColumns { get; } IXLRange Sort(); - IXLRange Sort(Boolean matchCase); - IXLRange Sort(XLSortOrder sortOrder); - IXLRange Sort(XLSortOrder sortOrder, Boolean matchCase); - IXLRange Sort(String columnsToSortBy); - IXLRange Sort(String columnsToSortBy, Boolean matchCase); - IXLRange Sort(XLSortOrientation sortOrientation); - IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder); - IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy); - IXLRange Sort(XLSortOrientation sortOrientation, Boolean matchCase); - IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder, Boolean matchCase); - IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy, Boolean matchCase); + IXLRange Sort(String columnsToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); + IXLRange Sort(Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); + IXLRange SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); + IXLRange SetDataType(XLCellValues dataType); @@ -248,6 +238,9 @@ /// /// Specify what you want to clear. new IXLRange Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + + IXLRangeRows RowsUsed(Boolean includeFormats = false); + IXLRangeColumns ColumnsUsed(Boolean includeFormats = false); } } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs index 7f67727..1119c2d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeBase.cs @@ -222,5 +222,7 @@ IXLPivotTable CreatePivotTable(IXLCell targetCell, String name); //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/IXLRangeColumn.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs index 16ebea1..5de5a7f 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumn.cs @@ -78,13 +78,7 @@ IXLRangeColumn CopyTo(IXLCell target); IXLRangeColumn CopyTo(IXLRangeBase target); - void SetAutoFilter(); - void SetAutoFilter(Boolean autoFilter); - - IXLRangeColumn Sort(); - IXLRangeColumn Sort(Boolean matchCase); - IXLRangeColumn Sort(XLSortOrder sortOrder); - IXLRangeColumn Sort(XLSortOrder sortOrder, Boolean matchCase); + IXLRangeColumn Sort(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); IXLRangeColumn Column(Int32 start, Int32 end); IXLRangeColumn Column(IXLCell start, IXLCell end); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs index ea05fd0..e800bf1 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeColumns.cs @@ -25,8 +25,8 @@ /// /// Returns the collection of cells that have a value. /// - /// if set to true will return all cells with a value or a style different than the default. - IXLCells CellsUsed(Boolean includeStyles); + /// if set to true will return all cells with a value or a style different than the default. + IXLCells CellsUsed(Boolean includeFormats); /// /// Deletes all columns and shifts the columns at the right of them accordingly. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs index b69a706..8e641a9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRow.cs @@ -85,14 +85,8 @@ IXLRangeRow CopyTo(IXLCell target); IXLRangeRow CopyTo(IXLRangeBase target); - void SetAutoFilter(); - void SetAutoFilter(Boolean autoFilter); - IXLRangeRow Sort(); - IXLRangeRow Sort(Boolean matchCase); - IXLRangeRow Sort(XLSortOrder sortOrder); - IXLRangeRow Sort(XLSortOrder sortOrder, Boolean matchCase); - + IXLRangeRow SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); IXLRangeRow Row(Int32 start, Int32 end); IXLRangeRow Row(IXLCell start, IXLCell end); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs index e9b1247..342cc29 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRangeRows.cs @@ -24,8 +24,8 @@ /// /// Returns the collection of cells that have a value. /// - /// if set to true will return all cells with a value or a style different than the default. - IXLCells CellsUsed(Boolean includeStyles); + /// if set to true will return all cells with a value or a style different than the default. + IXLCells CellsUsed(Boolean includeFormats); /// /// Deletes all rows and shifts the rows below them accordingly. diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs index 5aa181a..e34a5bd 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/IXLRanges.cs @@ -79,8 +79,8 @@ /// /// Returns the collection of cells that have a value. /// - /// if set to true will return all cells with a value or a style different than the default. - IXLCells CellsUsed(Boolean includeStyles); + /// if set to true will return all cells with a value or a style different than the default. + IXLCells CellsUsed(Boolean includeFormats); IXLRanges SetDataType(XLCellValues dataType); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs index 42323f0..18ebb01 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRange.cs @@ -6,13 +6,6 @@ { internal class XLRange : XLRangeBase, IXLRange { - #region Fields - - private IXLSortElements _mSortColumns; - private IXLSortElements _mSortRows; - - #endregion - #region Constructor public XLRange(XLRangeParameters xlRangeParameters) @@ -333,246 +326,6 @@ return new XLTable(this, name, true); } - public IXLSortElements SortRows - { - get { return _mSortRows ?? (_mSortRows = new XLSortElements()); } - } - - public IXLSortElements SortColumns - { - get { return _mSortColumns ?? (_mSortColumns = new XLSortElements()); } - } - - public IXLRange Sort() - { - if (SortColumns.Count() == 0) - return Sort(XLSortOrder.Ascending); - SortRangeRows(); - return this; - } - - public IXLRange Sort(Boolean matchCase) - { - if (SortColumns.Count() == 0) - return Sort(XLSortOrder.Ascending, false); - SortRangeRows(); - return this; - } - - public IXLRange Sort(XLSortOrder sortOrder) - { - if (SortColumns.Count() == 0) - { - Int32 columnCount = ColumnCount(); - for (Int32 co = 1; co <= columnCount; co++) - SortColumns.Add(co, sortOrder); - } - else - SortColumns.ForEach(sc => sc.SortOrder = sortOrder); - SortRangeRows(); - return this; - } - - public IXLRange Sort(XLSortOrder sortOrder, Boolean matchCase) - { - if (SortColumns.Count() == 0) - { - Int32 columnCount = ColumnCount(); - for (Int32 co = 1; co <= columnCount; co++) - SortColumns.Add(co, sortOrder, true, matchCase); - } - else - { - SortColumns.ForEach(sc => - { - sc.SortOrder = sortOrder; - sc.MatchCase = matchCase; - }); - } - SortRangeRows(); - return this; - } - - public IXLRange Sort(String columnsToSortBy) - { - SortColumns.Clear(); - foreach (string coPairTrimmed in columnsToSortBy.Split(',').Select(coPair => coPair.Trim())) - { - String coString; - String order; - if (coPairTrimmed.Contains(' ')) - { - var pair = coPairTrimmed.Split(' '); - coString = pair[0]; - order = pair[1]; - } - else - { - coString = coPairTrimmed; - order = "ASC"; - } - - Int32 co; - if (!Int32.TryParse(coString, out co)) - co = ExcelHelper.GetColumnNumberFromLetter(coString); - - SortColumns.Add(co, String.Compare(order, "ASC", true) == 0 ? XLSortOrder.Ascending : XLSortOrder.Descending); - } - - SortRangeRows(); - return this; - } - - public IXLRange Sort(String columnsToSortBy, Boolean matchCase) - { - SortColumns.Clear(); - foreach (string coPairTrimmed in columnsToSortBy.Split(',').Select(coPair => coPair.Trim())) - { - String coString; - String order; - if (coPairTrimmed.Contains(' ')) - { - var pair = coPairTrimmed.Split(' '); - coString = pair[0]; - order = pair[1]; - } - else - { - coString = coPairTrimmed; - order = "ASC"; - } - - Int32 co; - if (!Int32.TryParse(coString, out co)) - co = ExcelHelper.GetColumnNumberFromLetter(coString); - - SortColumns.Add(co, String.Compare(order, "ASC", true) == 0 ? XLSortOrder.Ascending : XLSortOrder.Descending, true, - matchCase); - } - - SortRangeRows(); - return this; - } - - public IXLRange Sort(XLSortOrientation sortOrientation) - { - if (sortOrientation == XLSortOrientation.TopToBottom) - return Sort(); - if (SortRows.Count() == 0) - return Sort(sortOrientation, XLSortOrder.Ascending); - SortRangeColumns(); - return this; - } - - public IXLRange Sort(XLSortOrientation sortOrientation, Boolean matchCase) - { - if (sortOrientation == XLSortOrientation.TopToBottom) - return Sort(matchCase); - if (SortRows.Count() == 0) - return Sort(sortOrientation, XLSortOrder.Ascending, matchCase); - SortRangeColumns(); - return this; - } - - public IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder) - { - if (sortOrientation == XLSortOrientation.TopToBottom) - return Sort(sortOrder); - if (SortRows.Count() == 0) - { - Int32 rowCount = RowCount(); - for (Int32 co = 1; co <= rowCount; co++) - SortRows.Add(co, sortOrder); - } - else - SortRows.ForEach(sc => sc.SortOrder = sortOrder); - SortRangeColumns(); - return this; - } - - public IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder, Boolean matchCase) - { - if (sortOrientation == XLSortOrientation.TopToBottom) - return Sort(sortOrder, matchCase); - if (SortRows.Count() == 0) - { - Int32 rowCount = RowCount(); - for (Int32 co = 1; co <= rowCount; co++) - SortRows.Add(co, sortOrder, matchCase); - } - else - { - SortRows.ForEach(sc => - { - sc.SortOrder = sortOrder; - sc.MatchCase = matchCase; - }); - } - SortRangeColumns(); - return this; - } - - public IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy) - { - if (sortOrientation == XLSortOrientation.TopToBottom) - return Sort(elementsToSortBy); - SortRows.Clear(); - foreach (string roPairTrimmed in elementsToSortBy.Split(',').Select(roPair => roPair.Trim())) - { - String roString; - String order; - if (roPairTrimmed.Contains(' ')) - { - var pair = roPairTrimmed.Split(' '); - roString = pair[0]; - order = pair[1]; - } - else - { - roString = roPairTrimmed; - order = "ASC"; - } - - Int32 ro = Int32.Parse(roString); - - SortRows.Add(ro, String.Compare(order, "ASC",true)==0 ? XLSortOrder.Ascending : XLSortOrder.Descending); - } - - SortRangeColumns(); - return this; - } - - public IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy, Boolean matchCase) - { - if (sortOrientation == XLSortOrientation.TopToBottom) - return Sort(elementsToSortBy, matchCase); - SortRows.Clear(); - foreach (string roPairTrimmed in elementsToSortBy.Split(',').Select(roPair => roPair.Trim())) - { - String roString; - String order; - if (roPairTrimmed.Contains(' ')) - { - var pair = roPairTrimmed.Split(' '); - roString = pair[0]; - order = pair[1]; - } - else - { - roString = roPairTrimmed; - order = "ASC"; - } - - Int32 ro = Int32.Parse(roString); - - SortRows.Add(ro, String.Compare(order, "ASC", true) == 0 ? XLSortOrder.Ascending : XLSortOrder.Descending, true, - matchCase); - } - - SortRangeColumns(); - return this; - } - public new IXLRange CopyTo(IXLCell target) { base.CopyTo(target); @@ -613,129 +366,25 @@ return this; } - #endregion - #region Sort Rows - - private void SortRangeRows() + public new IXLRange Sort() { - SortingRangeRows(1, RowCount()); + return base.Sort().AsRange(); } - - private void SwapRows(Int32 row1, Int32 row2) + + public new IXLRange Sort(String columnsToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) { - int row1InWs = RangeAddress.FirstAddress.RowNumber + row1 - 1; - int row2InWs = RangeAddress.FirstAddress.RowNumber + row2 - 1; - - Int32 firstColumn = RangeAddress.FirstAddress.ColumnNumber; - Int32 lastColumn = RangeAddress.LastAddress.ColumnNumber; - - var range1Sp1 = new XLSheetPoint(row1InWs, firstColumn); - var range1Sp2 = new XLSheetPoint(row1InWs, lastColumn); - var range2Sp1 = new XLSheetPoint(row2InWs, firstColumn); - var range2Sp2 = new XLSheetPoint(row2InWs, lastColumn); - - Worksheet.Internals.CellsCollection.SwapRanges(new XLSheetRange(range1Sp1, range1Sp2), - new XLSheetRange(range2Sp1, range2Sp2)); + return base.Sort(columnsToSortBy, sortOrder, matchCase, ignoreBlanks).AsRange(); } - - private int SortRangeRows(int begPoint, int endPoint) + public new IXLRange Sort(Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) { - int pivot = begPoint; - int m = begPoint + 1; - int n = endPoint; - while ((m < endPoint) && RowQuick(pivot).CompareTo(RowQuick(m), SortColumns) >= 0) - m++; - - while (n > begPoint && RowQuick(pivot).CompareTo(RowQuick(n), SortColumns) <= 0) - n--; - while (m < n) - { - SwapRows(m, n); - - while (m < endPoint && RowQuick(pivot).CompareTo(RowQuick(m), SortColumns) >= 0) - m++; - - while (n > begPoint && RowQuick(pivot).CompareTo(RowQuick(n), SortColumns) <= 0) - n--; - } - if (pivot != n) - SwapRows(n, pivot); - return n; + return base.Sort(columnToSortBy, sortOrder, matchCase, ignoreBlanks).AsRange(); } - - private void SortingRangeRows(int beg, int end) + public new IXLRange SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) { - if (end == beg) - return; - int pivot = SortRangeRows(beg, end); - if (pivot > beg) - SortingRangeRows(beg, pivot - 1); - if (pivot < end) - SortingRangeRows(pivot + 1, end); + return base.SortLeftToRight(sortOrder, matchCase, ignoreBlanks).AsRange(); } - - #endregion - - #region Sort Columns - - private void SortRangeColumns() - { - SortingRangeColumns(1, ColumnCount()); - } - - private void SwapColumns(Int32 column1, Int32 column2) - { - int col1InWs = RangeAddress.FirstAddress.ColumnNumber + column1 - 1; - int col2InWs = RangeAddress.FirstAddress.ColumnNumber + column2 - 1; - - Int32 firstRow = RangeAddress.FirstAddress.RowNumber; - Int32 lastRow = RangeAddress.LastAddress.RowNumber; - - var range1Sp1 = new XLSheetPoint(firstRow, col1InWs); - var range1Sp2 = new XLSheetPoint(lastRow, col1InWs); - var range2Sp1 = new XLSheetPoint(firstRow, col2InWs); - var range2Sp2 = new XLSheetPoint(lastRow, col2InWs); - - Worksheet.Internals.CellsCollection.SwapRanges(new XLSheetRange(range1Sp1, range1Sp2), - new XLSheetRange(range2Sp1, range2Sp2)); - } - - private int SortRangeColumns(int begPoint, int endPoint) - { - int pivot = begPoint; - int m = begPoint + 1; - int n = endPoint; - while ((m < endPoint) && ColumnQuick(pivot).CompareTo((ColumnQuick(m)), SortRows) >= 0) - m++; - - while ((n > begPoint) && ((ColumnQuick(pivot)).CompareTo((ColumnQuick(n)), SortRows) <= 0)) - n--; - while (m < n) - { - SwapColumns(m, n); - - while ((m < endPoint) && (ColumnQuick(pivot)).CompareTo((ColumnQuick(m)), SortRows) >= 0) - m++; - - while ((n > begPoint) && (ColumnQuick(pivot)).CompareTo((ColumnQuick(n)), SortRows) <= 0) - n--; - } - if (pivot != n) - SwapColumns(n, pivot); - return n; - } - - private void SortingRangeColumns(int beg, int end) - { - if (end == beg) - return; - int pivot = SortRangeColumns(beg, end); - if (pivot > beg) - SortingRangeColumns(beg, pivot - 1); - if (pivot < end) - SortingRangeColumns(pivot + 1, end); - } + #endregion @@ -856,21 +505,7 @@ new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), false); } - public XLRangeRow RowQuick(Int32 row) - { - var firstCellAddress = new XLAddress(Worksheet, - RangeAddress.FirstAddress.RowNumber + row - 1, - RangeAddress.FirstAddress.ColumnNumber, - false, - false); - var lastCellAddress = new XLAddress(Worksheet, - RangeAddress.FirstAddress.RowNumber + row - 1, - RangeAddress.LastAddress.ColumnNumber, - false, - false); - return new XLRangeRow( - new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), true); - } + public XLRangeColumn Column(Int32 column) { @@ -896,21 +531,7 @@ return Column(ExcelHelper.GetColumnNumberFromLetter(column)); } - public XLRangeColumn ColumnQuick(Int32 column) - { - var firstCellAddress = new XLAddress(Worksheet, - RangeAddress.FirstAddress.RowNumber, - RangeAddress.FirstAddress.ColumnNumber + column - 1, - false, - false); - var lastCellAddress = new XLAddress(Worksheet, - RangeAddress.LastAddress.RowNumber, - RangeAddress.FirstAddress.ColumnNumber + column - 1, - false, - false); - return new XLRangeColumn( - new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), true); - } + private void TransposeRange(int squareSide) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs index 8c189b7..91b640d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -11,6 +11,8 @@ #region Fields private IXLStyle _style; + private XLSortElements _sortRows; + private XLSortElements _sortColumns; #endregion @@ -1303,12 +1305,12 @@ return RangeUsed(false); } - public IXLRange RangeUsed(bool includeStyles) + public IXLRange RangeUsed(bool includeFormats) { - var firstCell = FirstCellUsed(includeStyles); + var firstCell = FirstCellUsed(includeFormats); if (firstCell == null) return null; - var lastCell = LastCellUsed(includeStyles); + var lastCell = LastCellUsed(includeFormats); return Worksheet.Range(firstCell, lastCell); } @@ -1322,16 +1324,6 @@ target.Value = this; } - public void SetAutoFilter() - { - SetAutoFilter(true); - } - - public void SetAutoFilter(Boolean autoFilter) - { - Worksheet.AutoFilterRange = autoFilter ? this : null; - } - //public IXLChart CreateChart(Int32 firstRow, Int32 firstColumn, Int32 lastRow, Int32 lastColumn) //{ // IXLChart chart = new XLChartWorksheet; @@ -1359,5 +1351,293 @@ { return (XLPivotTable)this.Worksheet.PivotTables.AddNew(name, targetCell, this.AsRange()); } + + public IXLAutoFilter SetAutoFilter() { + return Worksheet.AutoFilter.Set(AsRange()); + } + + public IXLRangeRows RowsUsed(Boolean includeFormats = false) + { + var rows = new XLRangeRows(); + foreach(var row in RangeUsed(includeFormats).Rows().Where(r=>!r.IsEmpty(includeFormats))) + { + rows.Add(row); + } + return rows; + } + + public IXLRangeColumns ColumnsUsed(Boolean includeFormats = false) + { + var columns = new XLRangeColumns(); + foreach(var column in RangeUsed(includeFormats).Columns().Where(r=>!r.IsEmpty(includeFormats))) + { + columns.Add(column); + } + return columns; + } + + #region Sort + + public IXLSortElements SortRows + { + get { return _sortRows ?? (_sortRows = new XLSortElements()); } + } + + public IXLSortElements SortColumns + { + get { return _sortColumns ?? (_sortColumns = new XLSortElements()); } + } + + public IXLRangeBase Sort() + { + if (SortColumns.Count() == 0) + { + String columnsToSortBy = String.Empty; + Int32 maxColumn = ColumnCount(); + if (maxColumn == ExcelHelper.MaxColumnNumber) + maxColumn = LastCellUsed(true).Address.ColumnNumber; + for (int i = 1; i <= maxColumn; i++) + { + columnsToSortBy += i.ToString() + ","; + } + columnsToSortBy = columnsToSortBy.Substring(0, columnsToSortBy.Length - 1); + return Sort(columnsToSortBy); + } + + SortRangeRows(); + return this; + } + + public IXLRangeBase Sort(String columnsToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) + { + SortColumns.Clear(); + if (StringExtensions.IsNullOrWhiteSpace(columnsToSortBy)) + { + columnsToSortBy = String.Empty; + Int32 maxColumn = ColumnCount(); + if (maxColumn == ExcelHelper.MaxColumnNumber) + maxColumn = LastCellUsed(true).Address.ColumnNumber; + for (int i = 1; i <= maxColumn; i++) + { + columnsToSortBy += i.ToString() + ","; + } + columnsToSortBy = columnsToSortBy.Substring(0, columnsToSortBy.Length - 1); + } + + foreach (string coPairTrimmed in columnsToSortBy.Split(',').Select(coPair => coPair.Trim())) + { + String coString; + String order; + if (coPairTrimmed.Contains(' ')) + { + var pair = coPairTrimmed.Split(' '); + coString = pair[0]; + order = pair[1]; + } + else + { + coString = coPairTrimmed; + if (sortOrder == XLSortOrder.Ascending) + order = "ASC"; + else + order = "DESC"; + } + + Int32 co; + if (!Int32.TryParse(coString, out co)) + co = ExcelHelper.GetColumnNumberFromLetter(coString); + + SortColumns.Add(co, String.Compare(order, "ASC", true) == 0 ? XLSortOrder.Ascending : XLSortOrder.Descending, ignoreBlanks, matchCase); + } + + SortRangeRows(); + return this; + } + + public IXLRangeBase Sort(Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) + { + return Sort(columnToSortBy.ToString(), sortOrder, matchCase, ignoreBlanks); + } + + public IXLRangeBase SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) + { + SortRows.Clear(); + Int32 maxColumn = ColumnCount(); + if (maxColumn == ExcelHelper.MaxColumnNumber) + maxColumn = LastCellUsed(true).Address.ColumnNumber; + + for (int i = 1; i <= maxColumn; i++) + { + SortRows.Add(i, sortOrder, ignoreBlanks, matchCase); + } + + SortRangeColumns(); + return this; + } + + + #region Sort Rows + + private void SortRangeRows() + { + Int32 maxRow = RowCount(); + if (maxRow == ExcelHelper.MaxRowNumber) + maxRow = LastCellUsed(true).Address.RowNumber; + SortingRangeRows(1, maxRow); + } + + private void SwapRows(Int32 row1, Int32 row2) + { + int row1InWs = RangeAddress.FirstAddress.RowNumber + row1 - 1; + int row2InWs = RangeAddress.FirstAddress.RowNumber + row2 - 1; + + Int32 firstColumn = RangeAddress.FirstAddress.ColumnNumber; + Int32 lastColumn = RangeAddress.LastAddress.ColumnNumber; + + var range1Sp1 = new XLSheetPoint(row1InWs, firstColumn); + var range1Sp2 = new XLSheetPoint(row1InWs, lastColumn); + var range2Sp1 = new XLSheetPoint(row2InWs, firstColumn); + var range2Sp2 = new XLSheetPoint(row2InWs, lastColumn); + + Worksheet.Internals.CellsCollection.SwapRanges(new XLSheetRange(range1Sp1, range1Sp2), + new XLSheetRange(range2Sp1, range2Sp2)); + } + + private int SortRangeRows(int begPoint, int endPoint) + { + int pivot = begPoint; + int m = begPoint + 1; + int n = endPoint; + while ((m < endPoint) && RowQuick(pivot).CompareTo(RowQuick(m), SortColumns) >= 0) + m++; + + while (n > begPoint && RowQuick(pivot).CompareTo(RowQuick(n), SortColumns) <= 0) + n--; + while (m < n) + { + SwapRows(m, n); + + while (m < endPoint && RowQuick(pivot).CompareTo(RowQuick(m), SortColumns) >= 0) + m++; + + while (n > begPoint && RowQuick(pivot).CompareTo(RowQuick(n), SortColumns) <= 0) + n--; + } + if (pivot != n) + SwapRows(n, pivot); + return n; + } + + private void SortingRangeRows(int beg, int end) + { + if (end == beg) + return; + int pivot = SortRangeRows(beg, end); + if (pivot > beg) + SortingRangeRows(beg, pivot - 1); + if (pivot < end) + SortingRangeRows(pivot + 1, end); + } + + #endregion + + #region Sort Columns + + private void SortRangeColumns() + { + Int32 maxColumn = ColumnCount(); + if (maxColumn == ExcelHelper.MaxColumnNumber) + maxColumn = LastCellUsed(true).Address.ColumnNumber; + SortingRangeColumns(1, maxColumn); + } + + private void SwapColumns(Int32 column1, Int32 column2) + { + int col1InWs = RangeAddress.FirstAddress.ColumnNumber + column1 - 1; + int col2InWs = RangeAddress.FirstAddress.ColumnNumber + column2 - 1; + + Int32 firstRow = RangeAddress.FirstAddress.RowNumber; + Int32 lastRow = RangeAddress.LastAddress.RowNumber; + + var range1Sp1 = new XLSheetPoint(firstRow, col1InWs); + var range1Sp2 = new XLSheetPoint(lastRow, col1InWs); + var range2Sp1 = new XLSheetPoint(firstRow, col2InWs); + var range2Sp2 = new XLSheetPoint(lastRow, col2InWs); + + Worksheet.Internals.CellsCollection.SwapRanges(new XLSheetRange(range1Sp1, range1Sp2), + new XLSheetRange(range2Sp1, range2Sp2)); + } + + private int SortRangeColumns(int begPoint, int endPoint) + { + int pivot = begPoint; + int m = begPoint + 1; + int n = endPoint; + while ((m < endPoint) && ColumnQuick(pivot).CompareTo((ColumnQuick(m)), SortRows) >= 0) + m++; + + while ((n > begPoint) && ((ColumnQuick(pivot)).CompareTo((ColumnQuick(n)), SortRows) <= 0)) + n--; + while (m < n) + { + SwapColumns(m, n); + + while ((m < endPoint) && (ColumnQuick(pivot)).CompareTo((ColumnQuick(m)), SortRows) >= 0) + m++; + + while ((n > begPoint) && (ColumnQuick(pivot)).CompareTo((ColumnQuick(n)), SortRows) <= 0) + n--; + } + if (pivot != n) + SwapColumns(n, pivot); + return n; + } + + private void SortingRangeColumns(int beg, int end) + { + if (end == beg) + return; + int pivot = SortRangeColumns(beg, end); + if (pivot > beg) + SortingRangeColumns(beg, pivot - 1); + if (pivot < end) + SortingRangeColumns(pivot + 1, end); + } + + #endregion + + #endregion + + public XLRangeColumn ColumnQuick(Int32 column) + { + var firstCellAddress = new XLAddress(Worksheet, + RangeAddress.FirstAddress.RowNumber, + RangeAddress.FirstAddress.ColumnNumber + column - 1, + false, + false); + var lastCellAddress = new XLAddress(Worksheet, + RangeAddress.LastAddress.RowNumber, + RangeAddress.FirstAddress.ColumnNumber + column - 1, + false, + false); + return new XLRangeColumn( + new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), true); + } + + public XLRangeRow RowQuick(Int32 row) + { + var firstCellAddress = new XLAddress(Worksheet, + RangeAddress.FirstAddress.RowNumber + row - 1, + RangeAddress.FirstAddress.ColumnNumber, + false, + false); + var lastCellAddress = new XLAddress(Worksheet, + RangeAddress.FirstAddress.RowNumber + row - 1, + RangeAddress.LastAddress.ColumnNumber, + false, + false); + return new XLRangeRow( + new XLRangeParameters(new XLRangeAddress(firstCellAddress, lastCellAddress), Worksheet.Style), true); + } } } \ 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 513c218..77bd036 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumn.cs @@ -67,32 +67,15 @@ public int CellCount() { - return RangeAddress.LastAddress.ColumnNumber - RangeAddress.FirstAddress.ColumnNumber + 1; + return RangeAddress.LastAddress.RowNumber - RangeAddress.FirstAddress.RowNumber + 1; } - public IXLRangeColumn Sort() + public IXLRangeColumn Sort(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) { - AsRange().Sort(); + base.Sort(1, sortOrder, matchCase, ignoreBlanks); return this; } - public IXLRangeColumn Sort(XLSortOrder sortOrder) - { - AsRange().Sort(sortOrder); - return this; - } - - public IXLRangeColumn Sort(bool matchCase) - { - AsRange().Sort(matchCase); - return this; - } - - public IXLRangeColumn Sort(XLSortOrder sortOrder, bool matchCase) - { - AsRange().Sort(sortOrder, matchCase); - return this; - } public new IXLRangeColumn CopyTo(IXLCell target) { diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs index cd9fd51..0ac0a79 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeColumns.cs @@ -74,9 +74,9 @@ return cells; } - public IXLCells CellsUsed(Boolean includeStyles) + public IXLCells CellsUsed(Boolean includeFormats) { - var cells = new XLCells(true, includeStyles); + var cells = new XLCells(true, includeFormats); foreach (XLRangeColumn container in _ranges) cells.Add(container.RangeAddress); return cells; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs index 077b861..472511d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -83,27 +83,14 @@ return RangeAddress.LastAddress.ColumnNumber - RangeAddress.FirstAddress.ColumnNumber + 1; } - public IXLRangeRow Sort() + public new IXLRangeRow Sort() { - AsRange().Sort(XLSortOrientation.LeftToRight); - return this; + return SortLeftToRight(); } - public IXLRangeRow Sort(XLSortOrder sortOrder) + public new IXLRangeRow SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) { - AsRange().Sort(XLSortOrientation.LeftToRight, sortOrder); - return this; - } - - public IXLRangeRow Sort(bool matchCase) - { - AsRange().Sort(XLSortOrientation.LeftToRight, matchCase); - return this; - } - - public IXLRangeRow Sort(XLSortOrder sortOrder, bool matchCase) - { - AsRange().Sort(XLSortOrientation.LeftToRight, sortOrder, matchCase); + base.SortLeftToRight(sortOrder, matchCase, ignoreBlanks); return this; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs index 49b7cf0..69ffdc9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRangeRows.cs @@ -74,9 +74,9 @@ return cells; } - public IXLCells CellsUsed(Boolean includeStyles) + public IXLCells CellsUsed(Boolean includeFormats) { - var cells = new XLCells(true, includeStyles); + var cells = new XLCells(true, includeFormats); foreach (XLRangeRow container in _ranges) cells.Add(container.RangeAddress); return cells; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs index dc8374e..bd22713 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Ranges/XLRanges.cs @@ -153,9 +153,9 @@ return cells; } - public IXLCells CellsUsed(Boolean includeStyles) + public IXLCells CellsUsed(Boolean includeFormats) { - var cells = new XLCells(true, includeStyles); + var cells = new XLCells(true, includeFormats); foreach (XLRange container in _ranges) cells.Add(container.RangeAddress); return cells; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs index 39ffec0..a7133f9 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRow.cs @@ -158,13 +158,8 @@ IXLRangeRow CopyTo(IXLRangeBase range); IXLRow CopyTo(IXLRow row); - void SetAutoFilter(); - void SetAutoFilter(Boolean autoFilter); - IXLRow Sort(); - IXLRow Sort(Boolean matchCase); - IXLRow Sort(XLSortOrder sortOrder); - IXLRow Sort(XLSortOrder sortOrder, Boolean matchCase); + IXLRow SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); IXLRangeRow Row(Int32 start, Int32 end); IXLRangeRow Row(IXLCell start, IXLCell end); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs index 0f695bf..c05cdcb 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/IXLRows.cs @@ -102,8 +102,8 @@ /// /// Returns the collection of cells that have a value. /// - /// if set to true will return all cells with a value or a style different than the default. - IXLCells CellsUsed(Boolean includeStyles); + /// if set to true will return all cells with a value or a style different than the default. + IXLCells CellsUsed(Boolean includeFormats); IXLStyle Style { get; set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs index c9842c1..e5f1d9a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRow.cs @@ -434,27 +434,14 @@ return RangeAddress.LastAddress.ColumnNumber - RangeAddress.FirstAddress.ColumnNumber + 1; } - public IXLRow Sort() + public new IXLRow Sort() { - RangeUsed().Sort(XLSortOrientation.LeftToRight); - return this; + return SortLeftToRight(); } - public IXLRow Sort(XLSortOrder sortOrder) + public new IXLRow SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) { - RangeUsed().Sort(XLSortOrientation.LeftToRight, sortOrder); - return this; - } - - public IXLRow Sort(Boolean matchCase) - { - AsRange().Sort(XLSortOrientation.LeftToRight, matchCase); - return this; - } - - public IXLRow Sort(XLSortOrder sortOrder, bool matchCase) - { - AsRange().Sort(XLSortOrientation.LeftToRight, sortOrder, matchCase); + base.SortLeftToRight(sortOrder, matchCase, ignoreBlanks); return this; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs index e27a222..f02aa96 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Rows/XLRows.cs @@ -190,9 +190,9 @@ return cells; } - public IXLCells CellsUsed(Boolean includeStyles) + public IXLCells CellsUsed(Boolean includeFormats) { - var cells = new XLCells(true, includeStyles); + var cells = new XLCells(true, includeFormats); foreach (XLRow container in _rows) cells.Add(container.RangeAddress); return cells; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs index 2f7aa12..19e25dd 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTable.cs @@ -131,11 +131,10 @@ IXLTableRows Rows(string rows); IXLRange Sort(); - IXLRange Sort(bool matchCase); - IXLRange Sort(XLSortOrder sortOrder); - IXLRange Sort(XLSortOrder sortOrder, bool matchCase); - IXLRange Sort(string columnsToSortBy); - IXLRange Sort(string columnsToSortBy, bool matchCase); + + IXLRange Sort(String columnsToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); + IXLRange Sort(Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); + IXLRange SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); /// /// Gets the cell at the specified row and column. @@ -356,5 +355,7 @@ /// /// Specify what you want to clear. new IXLTable Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats); + + IXLBaseAutoFilter AutoFilter { get; } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs index 305197d..83eca6a 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRow.cs @@ -8,9 +8,7 @@ IXLCell Field(String name); new IXLTableRow Sort(); - new IXLTableRow Sort(Boolean matchCase); - new IXLTableRow Sort(XLSortOrder sortOrder); - new IXLTableRow Sort(XLSortOrder sortOrder, Boolean matchCase); + new IXLTableRow SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true); new IXLTableRow RowAbove(); new IXLTableRow RowAbove(Int32 step); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRows.cs index 5408847..f0d2ce5 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/IXLTableRows.cs @@ -24,8 +24,8 @@ /// /// Returns the collection of cells that have a value. /// - /// if set to true will return all cells with a value or a style different than the default. - IXLCells CellsUsed(Boolean includeStyles); + /// if set to true will return all cells with a value or a style different than the default. + IXLCells CellsUsed(Boolean includeFormats); IXLStyle Style { get; set; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs index b091377..b03642e 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTable.cs @@ -294,37 +294,6 @@ } } - public new IXLRange Sort(String elementsToSortBy) - { - var toSortBy = new StringBuilder(); - foreach (string coPairTrimmed in elementsToSortBy.Split(',').Select(coPair => coPair.Trim())) - { - String coString; - String order; - if (coPairTrimmed.Contains(' ')) - { - var pair = coPairTrimmed.Split(' '); - coString = pair[0]; - order = pair[1]; - } - else - { - coString = coPairTrimmed; - order = "ASC"; - } - - Int32 co; - if (!Int32.TryParse(coString, out co)) - co = Field(coString).Index + 1; - - toSortBy.Append(co); - toSortBy.Append(" "); - toSortBy.Append(order); - toSortBy.Append(","); - } - return DataRange.Sort(toSortBy.ToString(0, toSortBy.Length - 1)); - } - public IXLTable SetEmphasizeFirstColumn() { EmphasizeFirstColumn = true; @@ -413,6 +382,37 @@ return LastColumnUsed(); } + 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())) + { + String coString; + String order; + if (coPairTrimmed.Contains(' ')) + { + var pair = coPairTrimmed.Split(' '); + coString = pair[0]; + order = pair[1]; + } + else + { + coString = coPairTrimmed; + order = "ASC"; + } + + Int32 co; + if (!Int32.TryParse(coString, out co)) + co = Field(coString).Index + 1; + + toSortBy.Append(co); + toSortBy.Append(" "); + toSortBy.Append(order); + toSortBy.Append(","); + } + return DataRange.Sort(toSortBy.ToString(0, toSortBy.Length - 1), sortOrder, matchCase, ignoreBlanks); + } + #endregion public new XLTableRow Row(int row) @@ -431,6 +431,7 @@ ShowRowStripes = true; ShowAutoFilter = true; Theme = XLTableTheme.TableStyleLight9; + AutoFilter = new XLAutoFilter(); } private void AddToTables(XLRange range, Boolean addToTables) @@ -496,5 +497,11 @@ 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/Tables/XLTableRow.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs index 37b915b..17b332c 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRow.cs @@ -27,25 +27,12 @@ public new IXLTableRow Sort() { - AsRange().Sort(XLSortOrientation.LeftToRight); - return this; + return SortLeftToRight(); } - public new IXLTableRow Sort(XLSortOrder sortOrder) + public new IXLTableRow SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) { - AsRange().Sort(XLSortOrientation.LeftToRight, sortOrder); - return this; - } - - public new IXLTableRow Sort(Boolean matchCase) - { - AsRange().Sort(XLSortOrientation.LeftToRight, matchCase); - return this; - } - - public new IXLTableRow Sort(XLSortOrder sortOrder, Boolean matchCase) - { - AsRange().Sort(XLSortOrientation.LeftToRight, sortOrder, matchCase); + base.SortLeftToRight(sortOrder, matchCase, ignoreBlanks); return this; } diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs index 082e07c..fdb1cb7 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/Tables/XLTableRows.cs @@ -110,9 +110,9 @@ return cells; } - public IXLCells CellsUsed(Boolean includeStyles) + public IXLCells CellsUsed(Boolean includeFormats) { - var cells = new XLCells(false, includeStyles); + var cells = new XLCells(false, includeFormats); foreach (XLTableRow container in _ranges) cells.Add(container.RangeAddress); return cells; diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs index 14b88fc..f2b8306 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Load.cs @@ -62,12 +62,9 @@ sharedStrings = shareStringPart.SharedStringTable.Elements().ToArray(); } - if (dSpreadsheet.WorkbookPart.GetPartsOfType().Count() > 0) + if (dSpreadsheet.CustomFilePropertiesPart != null) { - var customFilePropertiesPart = - dSpreadsheet.WorkbookPart.GetPartsOfType().First(); - foreach ( - CustomDocumentProperty m in customFilePropertiesPart.Properties.Elements()) + foreach (var m in dSpreadsheet.CustomFilePropertiesPart.Properties.Elements()) { String name = m.Name.Value; if (m.VTLPWSTR != null) @@ -228,7 +225,16 @@ (XLTableTheme) Enum.Parse(typeof (XLTableTheme), dTable.TableStyleInfo.Name.Value); } - xlTable.ShowAutoFilter = dTable.AutoFilter != null; + + if (dTable.AutoFilter != null) + { + xlTable.ShowAutoFilter = true; + LoadAutoFilterColumns( dTable.AutoFilter, (xlTable as XLTable).AutoFilter); + } + else + xlTable.ShowAutoFilter = false; + + if (xlTable.ShowTotalsRow) { @@ -716,7 +722,161 @@ private static void LoadAutoFilter(AutoFilter af, XLWorksheet ws) { if (af != null) + { ws.Range(af.Reference.Value).SetAutoFilter(); + var autoFilter = ws.AutoFilter; + LoadAutoFilterSort(af, ws, autoFilter); + LoadAutoFilterColumns(af, autoFilter); + } + } + + private static void LoadAutoFilterColumns(AutoFilter af, XLAutoFilter autoFilter) + { + foreach (var filterColumn in af.Elements()) + { + Int32 column = (int)filterColumn.ColumnId.Value + 1; + if (filterColumn.CustomFilters != null) + { + var filterList = new List(); + autoFilter.Column(column).FilterType = XLFilterType.Custom; + autoFilter.Filters.Add(column, filterList); + XLConnector connector = filterColumn.CustomFilters.And != null && filterColumn.CustomFilters.And.Value ? XLConnector.And : XLConnector.Or; + + Boolean isText = false; + foreach (CustomFilter filter in filterColumn.CustomFilters) + { + Double dTest; + String val = filter.Val.Value; + if (!Double.TryParse(val, out dTest)) + { + isText = true; + break; + } + } + + foreach (CustomFilter filter in filterColumn.CustomFilters) + { + var xlFilter = new XLFilter { Value = filter.Val.Value, Connector = connector }; + if (isText) + xlFilter.Value = filter.Val.Value; + else + xlFilter.Value = Double.Parse(filter.Val.Value); + + if (filter.Operator != null) + xlFilter.Operator = filter.Operator.Value.ToClosedXml(); + else + xlFilter.Operator = XLFilterOperator.Equal; + + Func condition = null; + switch (xlFilter.Operator) + { + case XLFilterOperator.Equal: + if (isText) + condition = o => o.ToString().Equals(xlFilter.Value.ToString(), StringComparison.InvariantCultureIgnoreCase); + else + condition = o => (o as IComparable).CompareTo(xlFilter.Value) == 0; + break; + case XLFilterOperator.EqualOrGreaterThan: condition = o => (o as IComparable).CompareTo(xlFilter.Value) >= 0; break; + case XLFilterOperator.EqualOrLessThan: condition = o => (o as IComparable).CompareTo(xlFilter.Value) <= 0; break; + case XLFilterOperator.GreaterThan: condition = o => (o as IComparable).CompareTo(xlFilter.Value) > 0; break; + case XLFilterOperator.LessThan: condition = o => (o as IComparable).CompareTo(xlFilter.Value) < 0; break; + case XLFilterOperator.NotEqual: + if (isText) + condition = o => !o.ToString().Equals(xlFilter.Value.ToString(), StringComparison.InvariantCultureIgnoreCase); + else + condition = o => (o as IComparable).CompareTo(xlFilter.Value) != 0; + break; + } + + xlFilter.Condition = condition; + filterList.Add(xlFilter); + } + } + else if (filterColumn.Filters != null) + { + var filterList = new List(); + autoFilter.Column(column).FilterType = XLFilterType.Regular; + autoFilter.Filters.Add((int)filterColumn.ColumnId.Value + 1, filterList); + + Boolean isText = false; + foreach (Filter filter in filterColumn.Filters) + { + Double dTest; + String val = filter.Val.Value; + if (!Double.TryParse(val, out dTest)) + { + isText = true; + break; + } + } + + foreach (Filter filter in filterColumn.Filters) + { + var xlFilter = new XLFilter { Connector = XLConnector.Or, Operator = XLFilterOperator.Equal }; + + Func condition; + if (isText) + { + xlFilter.Value = filter.Val.Value; + condition = o => o.ToString().Equals(xlFilter.Value.ToString(), StringComparison.InvariantCultureIgnoreCase); + } + else + { + xlFilter.Value = Double.Parse(filter.Val.Value); + condition = o => (o as IComparable).CompareTo(xlFilter.Value) == 0; + } + + xlFilter.Condition = condition; + filterList.Add(xlFilter); + } + + } + else if (filterColumn.Top10 != null) + { + var xlFilterColumn = autoFilter.Column(column); + autoFilter.Filters.Add(column, null); + xlFilterColumn.FilterType = XLFilterType.TopBottom; + if (filterColumn.Top10.Percent != null && filterColumn.Top10.Percent.Value) + xlFilterColumn.TopBottomType = XLTopBottomType.Percent; + else + xlFilterColumn.TopBottomType = XLTopBottomType.Items; + + if (filterColumn.Top10.Top != null && !filterColumn.Top10.Top.Value) + xlFilterColumn.TopBottomPart = XLTopBottomPart.Bottom; + else + xlFilterColumn.TopBottomPart = XLTopBottomPart.Top; + + xlFilterColumn.TopBottomValue = (int)filterColumn.Top10.Val.Value; + } + else if (filterColumn.DynamicFilter != null) + { + autoFilter.Filters.Add(column, null); + var xlFilterColumn = autoFilter.Column(column); + xlFilterColumn.FilterType = XLFilterType.Dynamic; + if (filterColumn.DynamicFilter.Type != null) + xlFilterColumn.DynamicType = filterColumn.DynamicFilter.Type.Value.ToClosedXml(); + else + xlFilterColumn.DynamicType = XLFilterDynamicType.AboveAverage; + + xlFilterColumn.DynamicValue = filterColumn.DynamicFilter.Val.Value; + } + } + } + + private static void LoadAutoFilterSort(AutoFilter af, XLWorksheet ws, IXLBaseAutoFilter autoFilter) + { + var sort = af.Elements().FirstOrDefault(); + if (sort != null) + { + var condition = sort.Elements().FirstOrDefault(); + if (condition != null) + { + Int32 column = ws.Range(condition.Reference.Value).FirstCell().Address.ColumnNumber - autoFilter.Range.FirstCell().Address.ColumnNumber + 1 ; + autoFilter.SortColumn = column; + autoFilter.Sorted = true; + autoFilter.SortOrder = condition.Descending != null && condition.Descending.Value ? XLSortOrder.Descending : XLSortOrder.Ascending; + } + } } private static void LoadSheetProtection(SheetProtection sp, XLWorksheet ws) diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs index 880e8fe..9cbe188 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorkbook_Save.cs @@ -408,9 +408,6 @@ SheetId = (UInt32)xlSheet.SheetId }; - if (xlSheet.Visibility != XLWorksheetVisibility.Visible) - newSheet.State = xlSheet.Visibility.ToOpenXml(); - workbook.Sheets.AppendChild(newSheet); } @@ -436,6 +433,11 @@ } workbook.Sheets.RemoveChild(sheet); + + var xlSheet = Worksheet(sheet.Name); + if (xlSheet.Visibility != XLWorksheetVisibility.Visible) + sheet.State = xlSheet.Visibility.ToOpenXml(); + workbook.Sheets.AppendChild(sheet); if (foundVisible) continue; @@ -505,16 +507,16 @@ definedNames.AppendChild(definedName); } - if (worksheet.AutoFilterRange != null) + if (worksheet.AutoFilter.Enabled) { var definedName = new DefinedName { Name = "_xlnm._FilterDatabase", LocalSheetId = sheetId }; - definedName.Text = "'" + worksheet.Name + "'!" + worksheet.AutoFilterRange.RangeAddress.FirstAddress.ToStringFixed() + - ":" + worksheet.AutoFilterRange.RangeAddress.LastAddress.ToStringFixed(); + definedName.Text = "'" + worksheet.Name + "'!" + worksheet.AutoFilter.Range.RangeAddress.FirstAddress.ToStringFixed() + + ":" + worksheet.AutoFilter.Range.RangeAddress.LastAddress.ToStringFixed(); definedName.Hidden = BooleanValue.FromBoolean(true); definedNames.AppendChild(definedName); } - foreach (IXLNamedRange nr in worksheet.NamedRanges) + foreach (IXLNamedRange nr in worksheet.NamedRanges.Where(n=>n.Name != "_xlnm._FilterDatabase")) { var definedName = new DefinedName { @@ -699,7 +701,7 @@ String s = c.Value.ToString(); var sharedStringItem = new SharedStringItem(); var text = new Text {Text = s}; - if (s.StartsWith(" ") || s.EndsWith(" ")) + if (!s.Trim().Equals(s)) text.Space = SpaceProcessingModeValues.Preserve; sharedStringItem.Append(text); sharedStringTablePart.SharedStringTable.Append(sharedStringItem); @@ -1584,16 +1586,16 @@ if (xlTable.ShowAutoFilter) { var autoFilter1 = new AutoFilter(); - if (xlTable.ShowTotalsRow) { - autoFilter1.Reference = xlTable.RangeAddress.FirstAddress + ":" + - ExcelHelper.GetColumnLetterFromNumber( - xlTable.RangeAddress.LastAddress.ColumnNumber) + - (xlTable.RangeAddress.LastAddress.RowNumber - 1).ToStringLookup(); + xlTable.AutoFilter.Range = xlTable.Worksheet.Range( + xlTable.RangeAddress.FirstAddress.RowNumber, xlTable.RangeAddress.FirstAddress.ColumnNumber, + xlTable.RangeAddress.LastAddress.RowNumber - 1, xlTable.RangeAddress.LastAddress.ColumnNumber); } else - autoFilter1.Reference = reference; + xlTable.AutoFilter.Range = xlTable.Worksheet.Range(xlTable.RangeAddress); + + PopulateAutoFilter(xlTable.AutoFilter, autoFilter1); table.AppendChild(autoFilter1); } @@ -2940,23 +2942,20 @@ #endregion #region AutoFilter - - if (xlWorksheet.AutoFilterRange != null) + worksheetPart.Worksheet.RemoveAllChildren(); + if (xlWorksheet.AutoFilter.Enabled) { - if (!worksheetPart.Worksheet.Elements().Any()) - { - var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.AutoFilter); - worksheetPart.Worksheet.InsertAfter(new AutoFilter(), previousElement); - } + var previousElement = cm.GetPreviousElementFor(XLWSContentManager.XLWSContents.AutoFilter); + worksheetPart.Worksheet.InsertAfter(new AutoFilter(), previousElement); + var autoFilter = worksheetPart.Worksheet.Elements().First(); cm.SetElement(XLWSContentManager.XLWSContents.AutoFilter, autoFilter); - autoFilter.Reference = xlWorksheet.AutoFilterRange.RangeAddress.ToString(); + PopulateAutoFilter(xlWorksheet.AutoFilter, autoFilter); } else { - worksheetPart.Worksheet.RemoveAllChildren(); cm.SetElement(XLWSContentManager.XLWSContents.AutoFilter, null); } @@ -3343,6 +3342,74 @@ #endregion } + private static void PopulateAutoFilter(XLAutoFilter xlAutoFilter, AutoFilter autoFilter) + { + var filterRange = xlAutoFilter.Range; + autoFilter.Reference = filterRange.RangeAddress.ToString(); + + foreach (var kp in xlAutoFilter.Filters) + { + FilterColumn filterColumn = new FilterColumn() { ColumnId = (UInt32)kp.Key - 1 }; + var xlFilterColumn = xlAutoFilter.Column(kp.Key); + var filterType = xlFilterColumn.FilterType; + if (filterType == XLFilterType.Custom) + { + CustomFilters customFilters = new CustomFilters(); + foreach (var filter in kp.Value) + { + CustomFilter customFilter = new CustomFilter() { Val = filter.Value.ToString() }; + + if (filter.Operator != XLFilterOperator.Equal) + customFilter.Operator = filter.Operator.ToOpenXml(); + + if (filter.Connector == XLConnector.And) + customFilters.And = true; + + customFilters.Append(customFilter); + } + filterColumn.Append(customFilters); + } + else if (filterType == XLFilterType.TopBottom) + { + Top10 top101 = new Top10() { Val = (double)xlFilterColumn.TopBottomValue }; + if (xlFilterColumn.TopBottomType == XLTopBottomType.Percent) + top101.Percent = true; + if (xlFilterColumn.TopBottomPart == XLTopBottomPart.Bottom) + top101.Top = false; + + filterColumn.Append(top101); + } + else if (filterType == XLFilterType.Dynamic) + { + DynamicFilter dynamicFilter = new DynamicFilter() { Type = xlFilterColumn.DynamicType.ToOpenXml(), Val = xlFilterColumn.DynamicValue }; + filterColumn.Append(dynamicFilter); + } + else + { + Filters filters = new Filters(); + foreach (var filter in kp.Value) + { + filters.Append(new Filter() { Val = filter.Value.ToString() }); + } + + filterColumn.Append(filters); + } + autoFilter.Append(filterColumn); + } + + + if (xlAutoFilter.Sorted) + { + SortState sortState = new SortState() { Reference = filterRange.Range(filterRange.FirstCell().CellBelow(), filterRange.LastCell()).RangeAddress.ToString() }; + SortCondition sortCondition = new SortCondition() { Reference = filterRange.Range(1, xlAutoFilter.SortColumn, filterRange.RowCount(), xlAutoFilter.SortColumn).RangeAddress.ToString() }; + if (xlAutoFilter.SortOrder == XLSortOrder.Descending) + sortCondition.Descending = true; + + sortState.Append(sortCondition); + autoFilter.Append(sortState); + } + } + private static BooleanValue GetBooleanValue(bool value, bool defaultValue) { return value == defaultValue ? null : new BooleanValue(value); diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs index 66c7071..0a6017d 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheet.cs @@ -31,8 +31,6 @@ internal Int32 _position; private Double _rowHeight; - private IXLSortElements _sortColumns; - private IXLSortElements _sortRows; private Boolean _tabActive; #endregion @@ -56,6 +54,7 @@ DataValidations = new XLDataValidations(); PivotTables = new XLPivotTables(); Protection = new XLSheetProtection(); + AutoFilter = new XLAutoFilter(); Workbook = workbook; SetStyle(workbook.Style); Internals = new XLWorksheetInternals(new XLCellsCollection(), new XLColumnsCollection(), @@ -158,7 +157,7 @@ } private Double _columnWidth; - public string LegacyDrawingId; + //public string LegacyDrawingId; public Double ColumnWidth { @@ -720,8 +719,8 @@ } } - if (AutoFilterRange != null) - targetSheet.Range(AutoFilterRange.RangeAddress).SetAutoFilter(); + if (AutoFilter.Enabled) + targetSheet.Range(AutoFilter.Range.RangeAddress).SetAutoFilter(); return targetSheet; } @@ -771,88 +770,23 @@ return Protection.Unprotect(password); } - public IXLRangeBase AutoFilterRange { get; set; } - public IXLSortElements SortRows + public new IXLRange Sort() { - get { return _sortRows ?? (_sortRows = new XLSortElements()); } + return GetRangeForSort().Sort(); } - public IXLSortElements SortColumns + public new IXLRange Sort(String columnsToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) { - get { return _sortColumns ?? (_sortColumns = new XLSortElements()); } + return GetRangeForSort().Sort(columnsToSortBy, sortOrder, matchCase, ignoreBlanks); } - - public IXLRange Sort() + public new IXLRange Sort(Int32 columnToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) { - var range = GetRangeForSort(); - return range.Sort(); + return GetRangeForSort().Sort(columnToSortBy, sortOrder, matchCase, ignoreBlanks); } - - public IXLRange Sort(Boolean matchCase) + public new IXLRange SortLeftToRight(XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) { - var range = GetRangeForSort(); - return range.Sort(matchCase); - } - - public IXLRange Sort(XLSortOrder sortOrder) - { - var range = GetRangeForSort(); - return range.Sort(sortOrder); - } - - public IXLRange Sort(XLSortOrder sortOrder, Boolean matchCase) - { - var range = GetRangeForSort(); - return range.Sort(sortOrder, matchCase); - } - - public IXLRange Sort(String columnsToSortBy) - { - var range = GetRangeForSort(); - return range.Sort(columnsToSortBy); - } - - public IXLRange Sort(String columnsToSortBy, Boolean matchCase) - { - var range = GetRangeForSort(); - return range.Sort(columnsToSortBy, matchCase); - } - - public IXLRange Sort(XLSortOrientation sortOrientation) - { - var range = GetRangeForSort(); - return range.Sort(sortOrientation); - } - - public IXLRange Sort(XLSortOrientation sortOrientation, Boolean matchCase) - { - var range = GetRangeForSort(); - return range.Sort(sortOrientation, matchCase); - } - - public IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder) - { - var range = GetRangeForSort(); - return range.Sort(sortOrientation, sortOrder); - } - - public IXLRange Sort(XLSortOrientation sortOrientation, XLSortOrder sortOrder, Boolean matchCase) - { - var range = GetRangeForSort(); - return range.Sort(sortOrientation, sortOrder, matchCase); - } - - public IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy) - { - var range = GetRangeForSort(); - return range.Sort(sortOrientation, elementsToSortBy); - } - - public IXLRange Sort(XLSortOrientation sortOrientation, String elementsToSortBy, Boolean matchCase) - { - var range = GetRangeForSort(); - return range.Sort(sortOrientation, elementsToSortBy, matchCase); + return GetRangeForSort().SortLeftToRight(sortOrder, matchCase, ignoreBlanks); } public Boolean ShowFormulas { get; set; } @@ -1260,5 +1194,31 @@ } 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 RangeUsed(includeFormats).Rows().Where(r => !r.IsEmpty(includeFormats))) + { + rows.Add(Row(row.RowNumber())); + } + return rows; + } + + public new IXLColumns ColumnsUsed(Boolean includeFormats = false) + { + var columns = new XLColumns(Worksheet); + foreach (var column in RangeUsed(includeFormats).Columns().Where(r => !r.IsEmpty(includeFormats))) + { + columns.Add(Column(column.ColumnNumber())); + } + return columns; + } } } \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs index 34094f3..c4ee6ef 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Excel/XLWorksheets.cs @@ -41,7 +41,16 @@ public IXLWorksheet Worksheet(String sheetName) { - return m_worksheets[sheetName]; + XLWorksheet w; + if (m_worksheets.TryGetValue(sheetName, out w)) + return w; + + var wss = m_worksheets.Where(ws=> ws.Key.ToLower().Equals(sheetName.ToLower())); + + if (wss.Any()) + return wss.First().Value; + + throw new Exception("There isn't a worksheet named '" + sheetName + "'."); } public IXLWorksheet Worksheet(Int32 position) diff --git a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs index fdfb26f..fc0c440 100644 --- a/ClosedXML/ClosedXML/ClosedXML/Extensions.cs +++ b/ClosedXML/ClosedXML/ClosedXML/Extensions.cs @@ -82,6 +82,12 @@ } return false; } + + public static T CastTo(this Object o) + { + return (T)Convert.ChangeType(o, typeof(T)); + } + } public static class DictionaryExtensions diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/AutoFilters/CustomAutoFilter.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/AutoFilters/CustomAutoFilter.cs new file mode 100644 index 0000000..6dcdaa4 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/AutoFilters/CustomAutoFilter.cs @@ -0,0 +1,139 @@ +using System; +using System.IO; +using ClosedXML.Excel; + +namespace ClosedXML_Examples +{ + public class CustomAutoFilter : IXLExample + { + public void Create(string filePath) + { + var wb = new XLWorkbook(); + IXLWorksheet ws; + + #region Single Column Numbers + String singleColumnNumbers = "Single Column Numbers"; + ws = wb.Worksheets.Add(singleColumnNumbers); + + // Add a bunch of numbers to filter + ws.Cell("A1").SetValue("Numbers") + .CellBelow().SetValue(2) + .CellBelow().SetValue(3) + .CellBelow().SetValue(3) + .CellBelow().SetValue(5) + .CellBelow().SetValue(1) + .CellBelow().SetValue(4); + + // Add filters + ws.RangeUsed().SetAutoFilter().Column(1).EqualTo(3).Or.GreaterThan(4); + + // Sort the filtered list + ws.AutoFilter.Sort(1); + #endregion + + #region Single Column Strings + String singleColumnStrings = "Single Column Strings"; + ws = wb.Worksheets.Add(singleColumnStrings); + + // Add a bunch of strings to filter + ws.Cell("A1").SetValue("Strings") + .CellBelow().SetValue("B") + .CellBelow().SetValue("C") + .CellBelow().SetValue("C") + .CellBelow().SetValue("E") + .CellBelow().SetValue("A") + .CellBelow().SetValue("D"); + + // Add filters + ws.RangeUsed().SetAutoFilter().Column(1).Between("B", "D"); + + // Sort the filtered list + ws.AutoFilter.Sort(1); + #endregion + + #region Single Column Mixed + String singleColumnMixed = "Single Column Mixed"; + ws = wb.Worksheets.Add(singleColumnMixed); + + // Add a bunch of items to filter + ws.Cell("A1").SetValue("Mixed") + .CellBelow().SetValue("B") + .CellBelow().SetValue(3) + .CellBelow().SetValue("C") + .CellBelow().SetValue("E") + .CellBelow().SetValue(1) + .CellBelow().SetValue(4); + + // Add filters + ws.RangeUsed().SetAutoFilter().Column(1).EqualTo(3).Or.EqualTo("C"); + + // Sort the filtered list + ws.AutoFilter.Sort(1); + #endregion + + #region Multi Column + String multiColumn = "Multi Column"; + ws = wb.Worksheets.Add(multiColumn); + + ws.Cell("A1").SetValue("First") + .CellBelow().SetValue("B") + .CellBelow().SetValue("C") + .CellBelow().SetValue("C") + .CellBelow().SetValue("E") + .CellBelow().SetValue("A") + .CellBelow().SetValue("D"); + + ws.Cell("B1").SetValue("Numbers") + .CellBelow().SetValue(2) + .CellBelow().SetValue(3) + .CellBelow().SetValue(3) + .CellBelow().SetValue(5) + .CellBelow().SetValue(1) + .CellBelow().SetValue(4); + + ws.Cell("C1").SetValue("Strings") + .CellBelow().SetValue("B") + .CellBelow().SetValue("C") + .CellBelow().SetValue("C") + .CellBelow().SetValue("E") + .CellBelow().SetValue("A") + .CellBelow().SetValue("D"); + + // Add filters + ws.RangeUsed().SetAutoFilter().Column(2).EqualTo(3).Or.GreaterThan(4); + ws.RangeUsed().SetAutoFilter().Column(3).Between("B", "D"); + + // Sort the filtered list + ws.AutoFilter.Sort(3); + #endregion + + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms); + + var workbook = new XLWorkbook(ms); + + #region Single Column Numbers + workbook.Worksheet(singleColumnNumbers).AutoFilter.Sort(1, XLSortOrder.Descending); + #endregion + + #region Single Column Strings + workbook.Worksheet(singleColumnStrings).AutoFilter.Sort(1, XLSortOrder.Descending); + #endregion + + #region Single Column Mixed + workbook.Worksheet(singleColumnMixed).AutoFilter.Column(1).EqualOrGreaterThan("D"); + workbook.Worksheet(singleColumnMixed).AutoFilter.Sort(1, XLSortOrder.Descending); + #endregion + + #region Multi Column + workbook.Worksheet(multiColumn).AutoFilter.Column(3).EqualTo("E"); + workbook.Worksheet(multiColumn).AutoFilter.Sort(3, XLSortOrder.Descending); + #endregion + + workbook.SaveAs(filePath); + ms.Close(); + } + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/AutoFilters/DynamicAutoFilter.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/AutoFilters/DynamicAutoFilter.cs new file mode 100644 index 0000000..3791185 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/AutoFilters/DynamicAutoFilter.cs @@ -0,0 +1,88 @@ +using System; +using System.IO; +using ClosedXML.Excel; + +namespace ClosedXML_Examples +{ + public class DynamicAutoFilter : IXLExample + { + public void Create(string filePath) + { + var wb = new XLWorkbook(); + IXLWorksheet ws; + + #region Single Column Numbers + String singleColumnNumbers = "Single Column Numbers"; + ws = wb.Worksheets.Add(singleColumnNumbers); + + // Add a bunch of numbers to filter + ws.Cell("A1").SetValue("Numbers") + .CellBelow().SetValue(2) + .CellBelow().SetValue(3) + .CellBelow().SetValue(3) + .CellBelow().SetValue(5) + .CellBelow().SetValue(1) + .CellBelow().SetValue(4); + + // Add filters + ws.RangeUsed().SetAutoFilter().Column(1).AboveAverage(); + + // Sort the filtered list + //ws.AutoFilter.Sort(1); + #endregion + + #region Multi Column + String multiColumn = "Multi Column"; + ws = wb.Worksheets.Add(multiColumn); + + ws.Cell("A1").SetValue("First") + .CellBelow().SetValue("B") + .CellBelow().SetValue("C") + .CellBelow().SetValue("C") + .CellBelow().SetValue("E") + .CellBelow().SetValue("A") + .CellBelow().SetValue("D"); + + ws.Cell("B1").SetValue("Numbers") + .CellBelow().SetValue(2) + .CellBelow().SetValue(3) + .CellBelow().SetValue(3) + .CellBelow().SetValue(5) + .CellBelow().SetValue(1) + .CellBelow().SetValue(4); + + ws.Cell("C1").SetValue("Strings") + .CellBelow().SetValue("B") + .CellBelow().SetValue("C") + .CellBelow().SetValue("C") + .CellBelow().SetValue("E") + .CellBelow().SetValue("A") + .CellBelow().SetValue("D"); + + // Add filters + ws.RangeUsed().SetAutoFilter().Column(2).BelowAverage(); + + // Sort the filtered list + //ws.AutoFilter.Sort(3); + #endregion + + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms); + + var workbook = new XLWorkbook(ms); + + #region Single Column Numbers + //workbook.Worksheet(singleColumnNumbers).AutoFilter.Sort(1, XLSortOrder.Descending); + #endregion + + #region Multi Column + //workbook.Worksheet(multiColumn).AutoFilter.Sort(3, XLSortOrder.Descending); + #endregion + + workbook.SaveAs(filePath); + ms.Close(); + } + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/AutoFilters/RegularAutoFilter.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/AutoFilters/RegularAutoFilter.cs new file mode 100644 index 0000000..9a26244 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/AutoFilters/RegularAutoFilter.cs @@ -0,0 +1,145 @@ +using System; +using System.IO; +using ClosedXML.Excel; + +namespace ClosedXML_Examples +{ + public class RegularAutoFilter : IXLExample + { + public void Create(string filePath) + { + var wb = new XLWorkbook(); + IXLWorksheet ws; + + #region Single Column Numbers + String singleColumnNumbers = "Single Column Numbers"; + ws = wb.Worksheets.Add(singleColumnNumbers); + + // Add a bunch of numbers to filter + ws.Cell("A1").SetValue("Numbers") + .CellBelow().SetValue(2) + .CellBelow().SetValue(3) + .CellBelow().SetValue(3) + .CellBelow().SetValue(5) + .CellBelow().SetValue(1) + .CellBelow().SetValue(4); + + // Add filters + ws.RangeUsed().SetAutoFilter().Column(1).AddFilter(3) + .AddFilter(1); + + // Sort the filtered list + ws.AutoFilter.Sort(1); + #endregion + + #region Single Column Strings + String singleColumnStrings = "Single Column Strings"; + ws = wb.Worksheets.Add(singleColumnStrings); + + // Add a bunch of strings to filter + ws.Cell("A1").SetValue("Strings") + .CellBelow().SetValue("B") + .CellBelow().SetValue("C") + .CellBelow().SetValue("C") + .CellBelow().SetValue("E") + .CellBelow().SetValue("A") + .CellBelow().SetValue("D"); + + // Add filters + ws.RangeUsed().SetAutoFilter().Column(1).AddFilter("C") + .AddFilter("A"); + + // Sort the filtered list + ws.AutoFilter.Sort(1); + #endregion + + #region Single Column Mixed + String singleColumnMixed = "Single Column Mixed"; + ws = wb.Worksheets.Add(singleColumnMixed); + + // Add a bunch of items to filter + ws.Cell("A1").SetValue("Mixed") + .CellBelow().SetValue("B") + .CellBelow().SetValue(3) + .CellBelow().SetValue("C") + .CellBelow().SetValue("E") + .CellBelow().SetValue(1) + .CellBelow().SetValue(4); + + // Add filters + ws.RangeUsed().SetAutoFilter().Column(1).AddFilter("C") + .AddFilter(1); + + // Sort the filtered list + ws.AutoFilter.Sort(1); + #endregion + + #region Multi Column + String multiColumn = "Multi Column"; + ws = wb.Worksheets.Add(multiColumn); + + ws.Cell("A1").SetValue("First") + .CellBelow().SetValue("B") + .CellBelow().SetValue("C") + .CellBelow().SetValue("C") + .CellBelow().SetValue("E") + .CellBelow().SetValue("A") + .CellBelow().SetValue("D"); + + ws.Cell("B1").SetValue("Numbers") + .CellBelow().SetValue(2) + .CellBelow().SetValue(3) + .CellBelow().SetValue(3) + .CellBelow().SetValue(5) + .CellBelow().SetValue(1) + .CellBelow().SetValue(4); + + ws.Cell("C1").SetValue("Strings") + .CellBelow().SetValue("B") + .CellBelow().SetValue("C") + .CellBelow().SetValue("C") + .CellBelow().SetValue("E") + .CellBelow().SetValue("A") + .CellBelow().SetValue("D"); + + // Add filters + ws.RangeUsed().SetAutoFilter().Column(2).AddFilter(3) + .AddFilter(1); + + // Sort the filtered list + ws.AutoFilter.Sort(3); + #endregion + + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms); + + var workbook = new XLWorkbook(ms); + + #region Single Column Numbers + workbook.Worksheet(singleColumnNumbers).AutoFilter.Column(1).AddFilter(5); + workbook.Worksheet(singleColumnNumbers).AutoFilter.Sort(1, XLSortOrder.Descending); + #endregion + + #region Single Column Strings + workbook.Worksheet(singleColumnStrings).AutoFilter.Column(1).AddFilter("E"); + workbook.Worksheet(singleColumnStrings).AutoFilter.Sort(1, XLSortOrder.Descending); + #endregion + + #region Single Column Mixed + workbook.Worksheet(singleColumnMixed).AutoFilter.Column(1).AddFilter("E"); + workbook.Worksheet(singleColumnMixed).AutoFilter.Column(1).AddFilter(3); + workbook.Worksheet(singleColumnMixed).AutoFilter.Sort(1, XLSortOrder.Descending); + #endregion + + #region Multi Column + workbook.Worksheet(multiColumn).AutoFilter.Column(3).AddFilter("C"); + workbook.Worksheet(multiColumn).AutoFilter.Sort(3, XLSortOrder.Descending); + #endregion + + workbook.SaveAs(filePath); + ms.Close(); + } + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/AutoFilters/TopBottomAutoFilter.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/AutoFilters/TopBottomAutoFilter.cs new file mode 100644 index 0000000..dbd8f88 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/AutoFilters/TopBottomAutoFilter.cs @@ -0,0 +1,88 @@ +using System; +using System.IO; +using ClosedXML.Excel; + +namespace ClosedXML_Examples +{ + public class TopBottomAutoFilter : IXLExample + { + public void Create(string filePath) + { + var wb = new XLWorkbook(); + IXLWorksheet ws; + + #region Single Column Numbers + String singleColumnNumbers = "Single Column Numbers"; + ws = wb.Worksheets.Add(singleColumnNumbers); + + // Add a bunch of numbers to filter + ws.Cell("A1").SetValue("Numbers") + .CellBelow().SetValue(2) + .CellBelow().SetValue(3) + .CellBelow().SetValue(3) + .CellBelow().SetValue(5) + .CellBelow().SetValue(1) + .CellBelow().SetValue(4); + + // Add filters + ws.RangeUsed().SetAutoFilter().Column(1).Top(2); + + // Sort the filtered list + //ws.AutoFilter.Sort(1); + #endregion + + #region Multi Column + String multiColumn = "Multi Column"; + ws = wb.Worksheets.Add(multiColumn); + + ws.Cell("A1").SetValue("First") + .CellBelow().SetValue("B") + .CellBelow().SetValue("C") + .CellBelow().SetValue("C") + .CellBelow().SetValue("E") + .CellBelow().SetValue("A") + .CellBelow().SetValue("D"); + + ws.Cell("B1").SetValue("Numbers") + .CellBelow().SetValue(2) + .CellBelow().SetValue(3) + .CellBelow().SetValue(3) + .CellBelow().SetValue(5) + .CellBelow().SetValue(1) + .CellBelow().SetValue(4); + + ws.Cell("C1").SetValue("Strings") + .CellBelow().SetValue("B") + .CellBelow().SetValue("C") + .CellBelow().SetValue("C") + .CellBelow().SetValue("E") + .CellBelow().SetValue("A") + .CellBelow().SetValue("D"); + + // Add filters + ws.RangeUsed().SetAutoFilter().Column(2).Bottom(50, XLTopBottomType.Percent); + + // Sort the filtered list + //ws.AutoFilter.Sort(3); + #endregion + + using (var ms = new MemoryStream()) + { + wb.SaveAs(ms); + + var workbook = new XLWorkbook(ms); + + #region Single Column Numbers + //workbook.Worksheet(singleColumnNumbers).AutoFilter.Sort(1, XLSortOrder.Descending); + #endregion + + #region Multi Column + //workbook.Worksheet(multiColumn).AutoFilter.Sort(3, XLSortOrder.Descending); + #endregion + + workbook.SaveAs(filePath); + ms.Close(); + } + } + } +} diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj index e8cfb87..a6578cd 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -124,6 +124,10 @@ + + + + diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/SortExample.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/SortExample.cs index 575b653..9569b72 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/SortExample.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/SortExample.cs @@ -71,11 +71,11 @@ var rangeLeftToRight = wsLeftToRight.RangeUsed(); var copyLeftToRight = rangeLeftToRight.CopyTo(wsLeftToRight.Row(wsLeftToRight.LastRowUsed().RowNumber() + 3)); - copyLeftToRight.Sort(XLSortOrientation.LeftToRight); + copyLeftToRight.SortLeftToRight(); wsLeftToRight.Row(1).InsertRowsAbove(2); wsLeftToRight.Cell(1, 1) - .SetValue(".Sort(XLSortOrientation.LeftToRight) = Sort Range Left to Right, Ascendingly, Ignore Blanks, Ignore Case") + .SetValue(".SortLeftToRight() = Sort Range Left to Right, Ascendingly, Ignore Blanks, Ignore Case") .Style.Font.SetBold(); #endregion @@ -104,11 +104,11 @@ var rangeComplex1 = wsComplex1.RangeUsed(); var copyComplex1 = rangeComplex1.CopyTo(wsComplex1.Column(wsComplex1.LastColumnUsed().ColumnNumber() + 3)); - copyComplex1.Sort("2, 1 DESC", true); + copyComplex1.Sort("2, 1 DESC", XLSortOrder.Ascending, true); wsComplex1.Row(1).InsertRowsAbove(2); wsComplex1.Cell(1, 1) - .SetValue(".Sort(\"2, 1 DESC\", true) = Sort Range Top to Bottom, Col 2 Asc, Col 1 Desc, Ignore Blanks, Match Case").Style.Font.SetBold(); + .SetValue(".Sort(\"2, 1 DESC\", XLSortOrder.Ascending, true) = Sort Range Top to Bottom, Col 2 Asc, Col 1 Desc, Ignore Blanks, Match Case").Style.Font.SetBold(); #endregion #region Sort a simple column @@ -117,7 +117,7 @@ var rangeSimpleColumn = wsSimpleColumn.RangeUsed(); var copySimpleColumn = rangeSimpleColumn.CopyTo(wsSimpleColumn.Column(wsSimpleColumn.LastColumnUsed().ColumnNumber() + 3)); - copySimpleColumn.Sort(XLSortOrder.Descending, true); + copySimpleColumn.FirstColumn().Sort(XLSortOrder.Descending, true); wsSimpleColumn.Row(1).InsertRowsAbove(2); wsSimpleColumn.Cell(1, 1) diff --git a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/Sorting.cs b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/Sorting.cs index 0fa398c..4d24204 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/Sorting.cs +++ b/ClosedXML/ClosedXML/ClosedXML_Examples/Ranges/Sorting.cs @@ -145,7 +145,7 @@ var wsSimpleDesc = wb.Worksheets.Add("Simple Desc"); AddTestTable(wsSimpleDesc); - wsSimpleDesc.Sort(XLSortOrder.Descending); + wsSimpleDesc.Sort("", XLSortOrder.Descending); var wsSimpleColumns = wb.Worksheets.Add("Simple Columns"); AddTestTable(wsSimpleColumns); @@ -157,7 +157,7 @@ var wsSimpleColumnDesc = wb.Worksheets.Add("Simple Column Desc"); AddTestColumn(wsSimpleColumnDesc); - wsSimpleColumnDesc.Sort(XLSortOrder.Descending); + wsSimpleColumnDesc.Sort(1, XLSortOrder.Descending); #endregion wb.SaveAs(filePath); diff --git a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj index d8859e6..d4fe958 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Net3.5/ClosedXML_Net3.5.csproj @@ -124,6 +124,42 @@ ExcelHelper.cs + + Excel\AutoFilters\IXLAutoFilter.cs + + + Excel\AutoFilters\IXLBaseAutoFilter.cs + + + Excel\AutoFilters\IXLCustomFilteredColumn.cs + + + Excel\AutoFilters\IXLFilterColumn.cs + + + Excel\AutoFilters\IXLFilterConnector.cs + + + Excel\AutoFilters\IXLFilteredColumn.cs + + + Excel\AutoFilters\XLAutoFilter.cs + + + Excel\AutoFilters\XLCustomFilteredColumn.cs + + + Excel\AutoFilters\XLFilter.cs + + + Excel\AutoFilters\XLFilterColumn.cs + + + Excel\AutoFilters\XLFilterConnector.cs + + + Excel\AutoFilters\XLFilteredColumn.cs + Excel\Cells\IXLCell.cs diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj index e42c7cc..4778098 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -65,6 +65,7 @@ + @@ -196,6 +197,10 @@ + + + + diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Examples/AutoFilterTests.cs b/ClosedXML/ClosedXML/ClosedXML_Tests/Examples/AutoFilterTests.cs new file mode 100644 index 0000000..da3c747 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Examples/AutoFilterTests.cs @@ -0,0 +1,33 @@ +using ClosedXML_Examples; +using Microsoft.VisualStudio.TestTools.UnitTesting; + +namespace ClosedXML_Tests.Examples +{ + [TestClass] + public class AutoFilterTests + { + + [TestMethod] + public void RegularAutoFilter() + { + TestHelper.RunTestExample(@"AutoFilter\RegularAutoFilter.xlsx"); + } + [TestMethod] + public void CustomAutoFilter() + { + TestHelper.RunTestExample(@"AutoFilter\CustomAutoFilter.xlsx"); + } + + [TestMethod] + public void TopBottomAutoFilter() + { + TestHelper.RunTestExample(@"AutoFilter\TopBottomAutoFilter.xlsx"); + } + + [TestMethod] + public void DynamicAutoFilter() + { + TestHelper.RunTestExample(@"AutoFilter\DynamicAutoFilter.xlsx"); + } + } +} \ No newline at end of file diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/AutoFilter/CustomAutoFilter.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/AutoFilter/CustomAutoFilter.xlsx new file mode 100644 index 0000000..facf59d --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/AutoFilter/CustomAutoFilter.xlsx Binary files differ diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/AutoFilter/DynamicAutoFilter.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/AutoFilter/DynamicAutoFilter.xlsx new file mode 100644 index 0000000..5ec35d5 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/AutoFilter/DynamicAutoFilter.xlsx Binary files differ diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx new file mode 100644 index 0000000..8648a38 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/AutoFilter/RegularAutoFilter.xlsx Binary files differ diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/AutoFilter/TopBottomAutoFilter.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/AutoFilter/TopBottomAutoFilter.xlsx new file mode 100644 index 0000000..0154726 --- /dev/null +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/AutoFilter/TopBottomAutoFilter.xlsx Binary files differ diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/AutoFilter.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/AutoFilter.xlsx index 29c8ee4..8bc8259 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/AutoFilter.xlsx +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/AutoFilter.xlsx Binary files differ diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProperties.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProperties.xlsx index ea8b1dd..a40f5c4 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProperties.xlsx +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Misc/WorkbookProperties.xlsx Binary files differ diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx index c7eb377..e472a17 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/SortExample.xlsx Binary files differ diff --git a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/Sorting.xlsx b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/Sorting.xlsx index c02f271..cad7541 100644 --- a/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/Sorting.xlsx +++ b/ClosedXML/ClosedXML/ClosedXML_Tests/Resource/Examples/Ranges/Sorting.xlsx Binary files differ