diff --git a/ClosedXML/ClosedXML.csproj b/ClosedXML/ClosedXML.csproj index 6f10f82..e2302de 100644 --- a/ClosedXML/ClosedXML.csproj +++ b/ClosedXML/ClosedXML.csproj @@ -70,6 +70,7 @@ + diff --git a/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs b/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs index f13ce40..4f2851d 100644 --- a/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs +++ b/ClosedXML/Excel/AutoFilters/IXLBaseAutoFilter.cs @@ -2,7 +2,7 @@ namespace ClosedXML.Excel { - public enum XLFilterType { Regular, Custom, TopBottom, Dynamic } + public enum XLFilterType { Regular, Custom, TopBottom, Dynamic, DateTimeGrouping } public enum XLFilterDynamicType { AboveAverage, BelowAverage } diff --git a/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs b/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs index 41376c8..106f485 100644 --- a/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs +++ b/ClosedXML/Excel/AutoFilters/IXLFilterColumn.cs @@ -1,31 +1,53 @@ using System; + namespace ClosedXML.Excel { public enum XLTopBottomType { Items, Percent } + + public enum XLDateTimeGrouping { Year, Month, Day, Hour, Minute, Second } + public interface IXLFilterColumn { void Clear(); IXLFilteredColumn AddFilter(T value) where T : IComparable; + IXLDateTimeGroupFilteredColumn AddDateGroupFilter(DateTime date, XLDateTimeGrouping dateTimeGrouping); + void Top(Int32 value, XLTopBottomType type = XLTopBottomType.Items); + void Bottom(Int32 value, XLTopBottomType type = XLTopBottomType.Items); + void AboveAverage(); + void BelowAverage(); IXLFilterConnector EqualTo(T value) where T : IComparable; + IXLFilterConnector NotEqualTo(T value) where T : IComparable; + IXLFilterConnector GreaterThan(T value) where T : IComparable; + IXLFilterConnector LessThan(T value) where T : IComparable; + IXLFilterConnector EqualOrGreaterThan(T value) where T : IComparable; + IXLFilterConnector EqualOrLessThan(T value) where T : IComparable; + void Between(T minValue, T maxValue) where T : IComparable; + void NotBetween(T minValue, T maxValue) where T : IComparable; + IXLFilterConnector BeginsWith(String value); + IXLFilterConnector NotBeginsWith(String value); + IXLFilterConnector EndsWith(String value); + IXLFilterConnector NotEndsWith(String value); + IXLFilterConnector Contains(String value); + IXLFilterConnector NotContains(String value); XLFilterType FilterType { get; set; } @@ -36,11 +58,15 @@ Double DynamicValue { get; set; } IXLFilterColumn SetFilterType(XLFilterType value); - IXLFilterColumn SetTopBottomValue(Int32 value); - IXLFilterColumn SetTopBottomType(XLTopBottomType value); - IXLFilterColumn SetTopBottomPart(XLTopBottomPart value); - IXLFilterColumn SetDynamicType(XLFilterDynamicType value); - IXLFilterColumn SetDynamicValue(Double value); + IXLFilterColumn SetTopBottomValue(Int32 value); + + IXLFilterColumn SetTopBottomType(XLTopBottomType value); + + IXLFilterColumn SetTopBottomPart(XLTopBottomPart value); + + IXLFilterColumn SetDynamicType(XLFilterDynamicType value); + + IXLFilterColumn SetDynamicValue(Double value); } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs b/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs index a01a138..3e33697 100644 --- a/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs +++ b/ClosedXML/Excel/AutoFilters/XLAutoFilter.cs @@ -59,11 +59,18 @@ public IXLFilterColumn Column(String column) { - return Column(XLHelper.GetColumnNumberFromLetter(column)); + var columnNumber = XLHelper.GetColumnNumberFromLetter(column); + if (columnNumber < 1 || columnNumber > XLHelper.MaxColumnNumber) + throw new ArgumentOutOfRangeException(nameof(column), "Column '" + column + "' is outside the allowed column range."); + + return Column(columnNumber); } public IXLFilterColumn Column(Int32 column) { + if (column < 1 || column > XLHelper.MaxColumnNumber) + throw new ArgumentOutOfRangeException(nameof(column), "Column " + column + " is outside the allowed column range."); + XLFilterColumn filterColumn; if (!_columns.TryGetValue(column, out filterColumn)) { @@ -97,7 +104,7 @@ public XLAutoFilter Sort(Int32 columnToSortBy, XLSortOrder sortOrder, Boolean matchCase, Boolean ignoreBlanks) { if (!Enabled) - throw new ApplicationException("Filter has not been enabled."); + throw new InvalidOperationException("Filter has not been enabled."); var ws = Range.Worksheet as XLWorksheet; ws.SuspendEvents(); @@ -108,6 +115,7 @@ SortOrder = sortOrder; SortColumn = columnToSortBy; + // Recalculate shown / hidden rows if (Enabled) { using (var rows = Range.Rows(2, Range.RowCount())) @@ -116,20 +124,29 @@ row.WorksheetRow().Unhide(); } - foreach (KeyValuePair> kp in Filters) + foreach (var kp in Filters) { Boolean firstFilter = true; foreach (XLFilter filter in kp.Value) { - Boolean isText = filter.Value is String; + var condition = filter.Condition; + var isText = filter.Value is String; + var isDateTime = filter.Value is DateTime; + using (var rows = Range.Rows(2, Range.RowCount())) { foreach (IXLRangeRow row in rows) { - Boolean match = isText - ? filter.Condition(row.Cell(kp.Key).GetString()) - : row.Cell(kp.Key).DataType == XLDataType.Number && - filter.Condition(row.Cell(kp.Key).GetDouble()); + //TODO : clean up filter matching - it's done in different place + Boolean match; + + if (isText) + match = condition(row.Cell(kp.Key).GetFormattedString()); + else if (isDateTime) + match = row.Cell(kp.Key).DataType == XLDataType.DateTime && condition(row.Cell(kp.Key).GetDateTime()); + else + match = row.Cell(kp.Key).DataType == XLDataType.Number && condition(row.Cell(kp.Key).GetDouble()); + if (firstFilter) { if (match) @@ -162,4 +179,4 @@ return this; } } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/AutoFilters/XLDateTimeGroupFilteredColumn.cs b/ClosedXML/Excel/AutoFilters/XLDateTimeGroupFilteredColumn.cs new file mode 100644 index 0000000..b2c151b --- /dev/null +++ b/ClosedXML/Excel/AutoFilters/XLDateTimeGroupFilteredColumn.cs @@ -0,0 +1,58 @@ +using System; + +namespace ClosedXML.Excel +{ + public interface IXLDateTimeGroupFilteredColumn + { + IXLDateTimeGroupFilteredColumn AddDateGroupFilter(DateTime date, XLDateTimeGrouping dateTimeGrouping); + } + + internal class XLDateTimeGroupFilteredColumn : IXLDateTimeGroupFilteredColumn + { + private readonly XLAutoFilter _autoFilter; + private readonly Int32 _column; + + public XLDateTimeGroupFilteredColumn(XLAutoFilter autoFilter, Int32 column) + { + _autoFilter = autoFilter; + _column = column; + } + + public IXLDateTimeGroupFilteredColumn AddDateGroupFilter(DateTime date, XLDateTimeGrouping dateTimeGrouping) + { + Func condition = date2 => IsMatch(date, (DateTime)date2, dateTimeGrouping); + + _autoFilter.Filters[_column].Add(new XLFilter + { + Value = date, + Condition = condition, + Operator = XLFilterOperator.Equal, + Connector = XLConnector.Or, + DateTimeGrouping = dateTimeGrouping + }); + + using (var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount())) + { + foreach (IXLRangeRow row in rows) + { + if (row.Cell(_column).DataType == XLDataType.DateTime && condition(row.Cell(_column).GetDateTime())) + row.WorksheetRow().Unhide().Dispose(); + } + } + return this; + } + + internal static Boolean IsMatch(DateTime date1, DateTime date2, XLDateTimeGrouping dateTimeGrouping) + { + Boolean isMatch = true; + if (isMatch && dateTimeGrouping >= XLDateTimeGrouping.Year) isMatch &= date1.Year.Equals(date2.Year); + if (isMatch && dateTimeGrouping >= XLDateTimeGrouping.Month) isMatch &= date1.Month.Equals(date2.Month); + if (isMatch && dateTimeGrouping >= XLDateTimeGrouping.Day) isMatch &= date1.Day.Equals(date2.Day); + if (isMatch && dateTimeGrouping >= XLDateTimeGrouping.Hour) isMatch &= date1.Hour.Equals(date2.Hour); + if (isMatch && dateTimeGrouping >= XLDateTimeGrouping.Minute) isMatch &= date1.Minute.Equals(date2.Minute); + if (isMatch && dateTimeGrouping >= XLDateTimeGrouping.Second) isMatch &= date1.Second.Equals(date2.Second); + + return isMatch; + } + } +} diff --git a/ClosedXML/Excel/AutoFilters/XLFilter.cs b/ClosedXML/Excel/AutoFilters/XLFilter.cs index f91231e..e62c552 100644 --- a/ClosedXML/Excel/AutoFilters/XLFilter.cs +++ b/ClosedXML/Excel/AutoFilters/XLFilter.cs @@ -5,8 +5,8 @@ namespace ClosedXML.Excel { - public enum XLConnector { And, Or } - public enum XLFilterOperator { Equal, NotEqual, GreaterThan, LessThan, EqualOrGreaterThan, EqualOrLessThan } + internal enum XLConnector { And, Or } + internal enum XLFilterOperator { Equal, NotEqual, GreaterThan, LessThan, EqualOrGreaterThan, EqualOrLessThan } internal class XLFilter { public XLFilter(XLFilterOperator op = XLFilterOperator.Equal) @@ -18,5 +18,6 @@ public Object Value { get; set; } public XLConnector Connector { get; set; } public Func Condition { get; set; } + public XLDateTimeGrouping DateTimeGrouping { get; set; } } } diff --git a/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs b/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs index a624708..dc4bceb 100644 --- a/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs +++ b/ClosedXML/Excel/AutoFilters/XLFilterColumn.cs @@ -24,7 +24,7 @@ _autoFilter.Filters.Remove(_column); } - public IXLFilteredColumn AddFilter(T value) where T: IComparable + public IXLFilteredColumn AddFilter(T value) where T : IComparable { if (typeof(T) == typeof(String)) { @@ -41,6 +41,61 @@ return new XLFilteredColumn(_autoFilter, _column); } + public IXLDateTimeGroupFilteredColumn AddDateGroupFilter(DateTime date, XLDateTimeGrouping dateTimeGrouping) + { + Func condition = date2 => XLDateTimeGroupFilteredColumn.IsMatch(date, (DateTime)date2, dateTimeGrouping); + + _autoFilter.Enabled = true; + + List filterList; + if (_autoFilter.Filters.TryGetValue(_column, out filterList)) + filterList.Add( + new XLFilter + { + Value = date, + Operator = XLFilterOperator.Equal, + Connector = XLConnector.Or, + Condition = condition, + DateTimeGrouping = dateTimeGrouping + } + ); + else + { + _autoFilter.Filters.Add( + _column, + new List + { + new XLFilter + { + Value = date, + Operator = XLFilterOperator.Equal, + Connector = XLConnector.Or, + Condition = condition, + DateTimeGrouping = dateTimeGrouping + } + } + ); + } + + _autoFilter.Column(_column).FilterType = XLFilterType.DateTimeGrouping; + + var ws = _autoFilter.Range.Worksheet as XLWorksheet; + ws.SuspendEvents(); + + var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount()); + + foreach (IXLRangeRow row in rows) + { + if (row.Cell(_column).DataType == XLDataType.DateTime && condition(row.Cell(_column).GetDateTime())) + row.WorksheetRow().Unhide(); + else + row.WorksheetRow().Hide(); + } + ws.ResumeEvents(); + + return new XLDateTimeGroupFilteredColumn(_autoFilter, _column); + } + public void Top(Int32 value, XLTopBottomType type = XLTopBottomType.Items) { _autoFilter.Column(_column).TopBottomPart = XLTopBottomPart.Top; @@ -63,7 +118,7 @@ ShowAverage(false); } - public IXLFilterConnector EqualTo(T value) where T: IComparable + public IXLFilterConnector EqualTo(T value) where T : IComparable { if (typeof(T) == typeof(String)) { @@ -77,7 +132,7 @@ v => v.CastTo().CompareTo(value) == 0); } - public IXLFilterConnector NotEqualTo(T value) where T: IComparable + public IXLFilterConnector NotEqualTo(T value) where T : IComparable { if (typeof(T) == typeof(String)) { @@ -86,41 +141,41 @@ !v.ToString().Equals(value.ToString(), StringComparison.InvariantCultureIgnoreCase)); } - + return ApplyCustomFilter(value, XLFilterOperator.NotEqual, v => v.CastTo().CompareTo(value) != 0); } - public IXLFilterConnector GreaterThan(T value) where T: IComparable + public IXLFilterConnector GreaterThan(T value) where T : IComparable { return ApplyCustomFilter(value, XLFilterOperator.GreaterThan, v => v.CastTo().CompareTo(value) > 0); } - public IXLFilterConnector LessThan(T value) where T: IComparable + public IXLFilterConnector LessThan(T value) where T : IComparable { return ApplyCustomFilter(value, XLFilterOperator.LessThan, v => v.CastTo().CompareTo(value) < 0); } - public IXLFilterConnector EqualOrGreaterThan(T value) where T: IComparable + public IXLFilterConnector EqualOrGreaterThan(T value) where T : IComparable { return ApplyCustomFilter(value, XLFilterOperator.EqualOrGreaterThan, v => v.CastTo().CompareTo(value) >= 0); } - public IXLFilterConnector EqualOrLessThan(T value) where T: IComparable + public IXLFilterConnector EqualOrLessThan(T value) where T : IComparable { return ApplyCustomFilter(value, XLFilterOperator.EqualOrLessThan, v => v.CastTo().CompareTo(value) <= 0); } - public void Between(T minValue, T maxValue) where T: IComparable + public void Between(T minValue, T maxValue) where T : IComparable { EqualOrGreaterThan(minValue).And.EqualOrLessThan(maxValue); } - public void NotBetween(T minValue, T maxValue) where T: IComparable + public void NotBetween(T minValue, T maxValue) where T : IComparable { LessThan(minValue).Or.GreaterThan(maxValue); } @@ -170,7 +225,7 @@ public XLFilterDynamicType DynamicType { get; set; } public Double DynamicValue { get; set; } - #endregion + #endregion IXLFilterColumn Members private void SetTopBottom(Int32 value, XLTopBottomType type, Boolean takeTop = true) { @@ -188,33 +243,33 @@ var ws = _autoFilter.Range.Worksheet as XLWorksheet; ws.SuspendEvents(); var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount()); - foreach (IXLRangeRow row in rows) + foreach (IXLRangeRow row in rows) + { + Boolean foundOne = false; + foreach (double val in values) { - Boolean foundOne = false; - foreach (double val in values) + Func condition = v => (v as IComparable).CompareTo(val) == 0; + if (addToList) { - Func condition = v => (v as IComparable).CompareTo(val) == 0; - if (addToList) + _autoFilter.Filters[_column].Add(new XLFilter { - _autoFilter.Filters[_column].Add(new XLFilter - { - Value = val, - Operator = XLFilterOperator.Equal, - Connector = XLConnector.Or, - Condition = condition - }); - } - - var cell = row.Cell(_column); - if (cell.DataType != XLDataType.Number || !condition(cell.GetDouble())) continue; - row.WorksheetRow().Unhide(); - foundOne = true; + Value = val, + Operator = XLFilterOperator.Equal, + Connector = XLConnector.Or, + Condition = condition + }); } - if (!foundOne) - row.WorksheetRow().Hide(); - addToList = false; + var cell = row.Cell(_column); + if (cell.DataType != XLDataType.Number || !condition(cell.GetDouble())) continue; + row.WorksheetRow().Unhide(); + foundOne = true; } + if (!foundOne) + row.WorksheetRow().Hide(); + + addToList = false; + } ws.ResumeEvents(); } @@ -257,7 +312,6 @@ : XLFilterDynamicType.BelowAverage); var values = GetAverageValues(aboveAverage); - Clear(); _autoFilter.Filters.Add(_column, new List()); @@ -265,36 +319,36 @@ var ws = _autoFilter.Range.Worksheet as XLWorksheet; ws.SuspendEvents(); var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount()); - - foreach (IXLRangeRow row in rows) - { - Boolean foundOne = false; - foreach (double val in values) - { - Func condition = v => (v as IComparable).CompareTo(val) == 0; - if (addToList) - { - _autoFilter.Filters[_column].Add(new XLFilter - { - Value = val, - Operator = XLFilterOperator.Equal, - Connector = XLConnector.Or, - Condition = condition - }); - } - var cell = row.Cell(_column); - if (cell.DataType != XLDataType.Number || !condition(cell.GetDouble())) continue; - row.WorksheetRow().Unhide(); - foundOne = true; + foreach (IXLRangeRow row in rows) + { + Boolean foundOne = false; + foreach (double val in values) + { + Func condition = v => (v as IComparable).CompareTo(val) == 0; + if (addToList) + { + _autoFilter.Filters[_column].Add(new XLFilter + { + Value = val, + Operator = XLFilterOperator.Equal, + Connector = XLConnector.Or, + Condition = condition + }); } - if (!foundOne) - row.WorksheetRow().Hide(); - - addToList = false; + var cell = row.Cell(_column); + if (cell.DataType != XLDataType.Number || !condition(cell.GetDouble())) continue; + row.WorksheetRow().Unhide(); + foundOne = true; } - + + if (!foundOne) + row.WorksheetRow().Hide(); + + addToList = false; + } + ws.ResumeEvents(); } @@ -316,14 +370,13 @@ return subColumn.CellsUsed(c => c.DataType == XLDataType.Number). Select(c => c.GetDouble()).Where(c => c < average).Distinct(); - } } } private IXLFilterConnector ApplyCustomFilter(T value, XLFilterOperator op, Func condition, XLFilterType filterType = XLFilterType.Custom) - where T: IComparable + where T : IComparable { _autoFilter.Enabled = true; if (filterType == XLFilterType.Custom) @@ -333,12 +386,12 @@ new List { new XLFilter - { - Value = value, - Operator = op, - Connector = XLConnector.Or, - Condition = condition - } + { + Value = value, + Operator = op, + Connector = XLConnector.Or, + Condition = condition + } }); } else @@ -346,7 +399,12 @@ List filterList; if (_autoFilter.Filters.TryGetValue(_column, out filterList)) filterList.Add(new XLFilter - {Value = value, Operator = op, Connector = XLConnector.Or, Condition = condition}); + { + Value = value, + Operator = op, + Connector = XLConnector.Or, + Condition = condition + }); else { _autoFilter.Filters.Add(_column, @@ -364,15 +422,21 @@ } _autoFilter.Column(_column).FilterType = filterType; Boolean isText = typeof(T) == typeof(String); + Boolean isDateTime = typeof(T) == typeof(DateTime); var ws = _autoFilter.Range.Worksheet as XLWorksheet; ws.SuspendEvents(); var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount()); foreach (IXLRangeRow row in rows) { - Boolean match = isText - ? condition(row.Cell(_column).GetString()) - : row.Cell(_column).DataType == XLDataType.Number && - condition(row.Cell(_column).GetDouble()); + Boolean match; + + if (isText) + match = condition(row.Cell(_column).GetFormattedString()); + else if (isDateTime) + match = row.Cell(_column).DataType == XLDataType.DateTime && condition(row.Cell(_column).GetDateTime()); + else + match = row.Cell(_column).DataType == XLDataType.Number && condition(row.Cell(_column).GetDouble()); + if (match) row.WorksheetRow().Unhide(); else @@ -383,11 +447,15 @@ } public IXLFilterColumn SetFilterType(XLFilterType value) { FilterType = value; return this; } - public IXLFilterColumn SetTopBottomValue(Int32 value) { TopBottomValue = value; return this; } - public IXLFilterColumn SetTopBottomType(XLTopBottomType value) { TopBottomType = value; return this; } - public IXLFilterColumn SetTopBottomPart(XLTopBottomPart value) { TopBottomPart = value; return this; } - public IXLFilterColumn SetDynamicType(XLFilterDynamicType value) { DynamicType = value; return this; } - public IXLFilterColumn SetDynamicValue(Double value) { DynamicValue = value; return this; } + public IXLFilterColumn SetTopBottomValue(Int32 value) { TopBottomValue = value; return this; } + + public IXLFilterColumn SetTopBottomType(XLTopBottomType value) { TopBottomType = value; return this; } + + public IXLFilterColumn SetTopBottomPart(XLTopBottomPart value) { TopBottomPart = value; return this; } + + public IXLFilterColumn SetDynamicType(XLFilterDynamicType value) { DynamicType = value; return this; } + + public IXLFilterColumn SetDynamicValue(Double value) { DynamicValue = value; return this; } } } diff --git a/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs b/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs index 1f11d83..2fe3555 100644 --- a/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs +++ b/ClosedXML/Excel/AutoFilters/XLFilteredColumn.cs @@ -1,5 +1,4 @@ using System; -using System.Linq; namespace ClosedXML.Excel { @@ -16,7 +15,7 @@ #region IXLFilteredColumn Members - public IXLFilteredColumn AddFilter(T value) where T: IComparable + public IXLFilteredColumn AddFilter(T value) where T : IComparable { Func condition; Boolean isText; @@ -32,12 +31,12 @@ } _autoFilter.Filters[_column].Add(new XLFilter - { - Value = value, - Condition = condition, - Operator = XLFilterOperator.Equal, - Connector = XLConnector.Or - }); + { + Value = value, + Condition = condition, + Operator = XLFilterOperator.Equal, + Connector = XLConnector.Or + }); using (var rows = _autoFilter.Range.Rows(2, _autoFilter.Range.RowCount())) { @@ -56,6 +55,6 @@ return this; } - #endregion + #endregion IXLFilteredColumn Members } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/EnumConverter.cs b/ClosedXML/Excel/EnumConverter.cs index af5ebe8..16a6cc9 100644 --- a/ClosedXML/Excel/EnumConverter.cs +++ b/ClosedXML/Excel/EnumConverter.cs @@ -601,6 +601,21 @@ } } + public static DateTimeGroupingValues ToOpenXml(this XLDateTimeGrouping value) + { + switch (value) + { + case XLDateTimeGrouping.Year: return DateTimeGroupingValues.Year; + case XLDateTimeGrouping.Month: return DateTimeGroupingValues.Month; + case XLDateTimeGrouping.Day: return DateTimeGroupingValues.Day; + case XLDateTimeGrouping.Hour: return DateTimeGroupingValues.Hour; + case XLDateTimeGrouping.Minute: return DateTimeGroupingValues.Minute; + case XLDateTimeGrouping.Second: return DateTimeGroupingValues.Second; + + default: + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); + } + } public static SheetViewValues ToOpenXml(this XLSheetViewOptions value) { switch (value) @@ -1357,6 +1372,22 @@ } } + public static XLDateTimeGrouping ToClosedXml(this DateTimeGroupingValues value) + { + switch (value) + { + case DateTimeGroupingValues.Year: return XLDateTimeGrouping.Year; + case DateTimeGroupingValues.Month: return XLDateTimeGrouping.Month; + case DateTimeGroupingValues.Day: return XLDateTimeGrouping.Day; + case DateTimeGroupingValues.Hour: return XLDateTimeGrouping.Hour; + case DateTimeGroupingValues.Minute: return XLDateTimeGrouping.Minute; + case DateTimeGroupingValues.Second: return XLDateTimeGrouping.Second; + + default: + throw new ArgumentOutOfRangeException(nameof(value), "Not implemented value!"); + } + } + public static XLSheetViewOptions ToClosedXml(this SheetViewValues value) { switch (value) diff --git a/ClosedXML/Excel/Ranges/XLRangeBase.cs b/ClosedXML/Excel/Ranges/XLRangeBase.cs index 27caf49..18509dd 100644 --- a/ClosedXML/Excel/Ranges/XLRangeBase.cs +++ b/ClosedXML/Excel/Ranges/XLRangeBase.cs @@ -4,6 +4,7 @@ using System.Collections.Generic; using System.Globalization; using System.Linq; +using System.Text; namespace ClosedXML.Excel { @@ -1612,7 +1613,7 @@ bool shiftLeftBoundary = (columnsShifted > 0 && thisRangeAddress.FirstAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber) || (columnsShifted < 0 && thisRangeAddress.FirstAddress.ColumnNumber > shiftedRange.RangeAddress.FirstAddress.ColumnNumber); - + bool shiftRightBoundary = thisRangeAddress.LastAddress.ColumnNumber >= shiftedRange.RangeAddress.FirstAddress.ColumnNumber; int newLeftBoundary = thisRangeAddress.FirstAddress.ColumnNumber; @@ -1783,40 +1784,42 @@ get { return _sortColumns ?? (_sortColumns = new XLSortElements()); } } + private String DefaultSortString() + { + var sb = new StringBuilder(); + Int32 maxColumn = ColumnCount(); + if (maxColumn == XLHelper.MaxColumnNumber) + maxColumn = LastCellUsed(true).Address.ColumnNumber; + for (int i = 1; i <= maxColumn; i++) + { + if (sb.Length > 0) + sb.Append(','); + + sb.Append(i); + } + + return sb.ToString(); + + } + public IXLRangeBase Sort() { if (!SortColumns.Any()) { - String columnsToSortBy = String.Empty; - Int32 maxColumn = ColumnCount(); - if (maxColumn == XLHelper.MaxColumnNumber) - maxColumn = LastCellUsed(true).Address.ColumnNumber; - for (int i = 1; i <= maxColumn; i++) - { - columnsToSortBy += i + ","; - } - columnsToSortBy = columnsToSortBy.Substring(0, columnsToSortBy.Length - 1); - return Sort(columnsToSortBy); + return Sort(DefaultSortString()); } SortRangeRows(); return this; } + public IXLRangeBase Sort(String columnsToSortBy, XLSortOrder sortOrder = XLSortOrder.Ascending, Boolean matchCase = false, Boolean ignoreBlanks = true) { SortColumns.Clear(); if (String.IsNullOrWhiteSpace(columnsToSortBy)) { - columnsToSortBy = String.Empty; - Int32 maxColumn = ColumnCount(); - if (maxColumn == XLHelper.MaxColumnNumber) - maxColumn = LastCellUsed(true).Address.ColumnNumber; - for (int i = 1; i <= maxColumn; i++) - { - columnsToSortBy += i + ","; - } - columnsToSortBy = columnsToSortBy.Substring(0, columnsToSortBy.Length - 1); + columnsToSortBy = DefaultSortString(); } foreach (string coPairTrimmed in columnsToSortBy.Split(',').Select(coPair => coPair.Trim())) @@ -1905,6 +1908,7 @@ while (n > begPoint && RowQuick(pivot).CompareTo(RowQuick(n), SortColumns) <= 0) n--; + while (m < n) { SwapRows(m, n); @@ -1915,8 +1919,10 @@ while (n > begPoint && RowQuick(pivot).CompareTo(RowQuick(n), SortColumns) <= 0) n--; } + if (pivot != n) SwapRows(n, pivot); + return n; } diff --git a/ClosedXML/Excel/Ranges/XLRangeRow.cs b/ClosedXML/Excel/Ranges/XLRangeRow.cs index 9b6be16..d35a77c 100644 --- a/ClosedXML/Excel/Ranges/XLRangeRow.cs +++ b/ClosedXML/Excel/Ranges/XLRangeRow.cs @@ -2,7 +2,6 @@ { using System; using System.Linq; - internal class XLRangeRow : XLRangeBase, IXLRangeRow { @@ -21,9 +20,8 @@ SetStyle(rangeParameters.DefaultStyle); } - #endregion + #endregion Constructor - public XLRangeParameters RangeParameters { get; private set; } #region IXLRangeRow Members @@ -185,7 +183,7 @@ return Worksheet.Row(RangeAddress.FirstAddress.RowNumber); } - #endregion + #endregion IXLRangeRow Members private void WorksheetRangeShiftedColumns(XLRange range, int columnsShifted) { @@ -247,16 +245,33 @@ { if (thisCell.DataType == otherCell.DataType) { - if (thisCell.DataType == XLDataType.Text) + switch (thisCell.DataType) { - comparison = e.MatchCase - ? thisCell.InnerText.CompareTo(otherCell.InnerText) - : String.Compare(thisCell.InnerText, otherCell.InnerText, true); + case XLDataType.Text: + comparison = e.MatchCase + ? thisCell.InnerText.CompareTo(otherCell.InnerText) + : String.Compare(thisCell.InnerText, otherCell.InnerText, true); + break; + + case XLDataType.TimeSpan: + comparison = thisCell.GetTimeSpan().CompareTo(otherCell.GetTimeSpan()); + break; + + case XLDataType.DateTime: + comparison = thisCell.GetDateTime().CompareTo(otherCell.GetDateTime()); + break; + + case XLDataType.Number: + comparison = Double.Parse(thisCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture).CompareTo(Double.Parse(otherCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture)); + break; + + case XLDataType.Boolean: + comparison = thisCell.GetBoolean().CompareTo(otherCell.GetBoolean()); + break; + + default: + throw new NotImplementedException(); } - else if (thisCell.DataType == XLDataType.TimeSpan) - comparison = thisCell.GetTimeSpan().CompareTo(otherCell.GetTimeSpan()); - else - comparison = Double.Parse(thisCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture).CompareTo(Double.Parse(otherCell.InnerText, XLHelper.NumberStyle, XLHelper.ParseCulture)); } else if (e.MatchCase) comparison = String.Compare(thisCell.GetString(), otherCell.GetString(), true); @@ -265,7 +280,7 @@ } if (comparison != 0) - return e.SortOrder == XLSortOrder.Ascending ? comparison : comparison * -1; + return e.SortOrder == XLSortOrder.Ascending ? comparison : -comparison; } return 0; @@ -280,7 +295,7 @@ RangeAddress.FirstAddress.ColumnNumber, rowNum, RangeAddress.LastAddress.ColumnNumber); - + var result = range.FirstRow(); range.Dispose(); @@ -309,7 +324,7 @@ return RowShift(step * -1); } - #endregion + #endregion XLRangeRow Above #region XLRangeRow Below @@ -333,7 +348,7 @@ return RowShift(step); } - #endregion + #endregion XLRangeRow Below public new IXLRangeRow Clear(XLClearOptions clearOptions = XLClearOptions.ContentsAndFormats) { @@ -345,6 +360,5 @@ { return Row(FirstCellUsed(includeFormats), LastCellUsed(includeFormats)); } - } -} \ No newline at end of file +} diff --git a/ClosedXML/Excel/XLWorkbook_Load.cs b/ClosedXML/Excel/XLWorkbook_Load.cs index 2528cef..0899294 100644 --- a/ClosedXML/Excel/XLWorkbook_Load.cs +++ b/ClosedXML/Excel/XLWorkbook_Load.cs @@ -1838,7 +1838,7 @@ XLConnector connector = filterColumn.CustomFilters.And != null && filterColumn.CustomFilters.And.Value ? XLConnector.And : XLConnector.Or; Boolean isText = false; - foreach (CustomFilter filter in filterColumn.CustomFilters) + foreach (var filter in filterColumn.CustomFilters.OfType()) { Double dTest; String val = filter.Val.Value; @@ -1849,7 +1849,7 @@ } } - foreach (CustomFilter filter in filterColumn.CustomFilters) + foreach (var filter in filterColumn.CustomFilters.OfType()) { var xlFilter = new XLFilter { Value = filter.Val.Value, Connector = connector }; if (isText) @@ -1890,12 +1890,19 @@ } else if (filterColumn.Filters != null) { + if (filterColumn.Filters.Elements().All(element => element is Filter)) + autoFilter.Column(column).FilterType = XLFilterType.Regular; + else if (filterColumn.Filters.Elements().All(element => element is DateGroupItem)) + autoFilter.Column(column).FilterType = XLFilterType.DateTimeGrouping; + else + throw new NotSupportedException(String.Format("Mixing regular filters and date group filters in a single autofilter column is not supported. Column {0} of {1}", column, autoFilter.Range.ToString())); + var filterList = new List(); - autoFilter.Column(column).FilterType = XLFilterType.Regular; + autoFilter.Filters.Add((int)filterColumn.ColumnId.Value + 1, filterList); Boolean isText = false; - foreach (Filter filter in filterColumn.Filters.OfType()) + foreach (var filter in filterColumn.Filters.OfType()) { Double dTest; String val = filter.Val.Value; @@ -1906,7 +1913,7 @@ } } - foreach (Filter filter in filterColumn.Filters.OfType()) + foreach (var filter in filterColumn.Filters.OfType()) { var xlFilter = new XLFilter { Connector = XLConnector.Or, Operator = XLFilterOperator.Equal }; @@ -1925,6 +1932,84 @@ xlFilter.Condition = condition; filterList.Add(xlFilter); } + + foreach (var dateGroupItem in filterColumn.Filters.OfType()) + { + bool valid = true; + + if (!(dateGroupItem.DateTimeGrouping?.HasValue ?? false)) + continue; + + var xlDateGroupFilter = new XLFilter + { + Connector = XLConnector.Or, + Operator = XLFilterOperator.Equal, + DateTimeGrouping = dateGroupItem.DateTimeGrouping?.Value.ToClosedXml() ?? XLDateTimeGrouping.Year + }; + + int year = 1900; + int month = 1; + int day = 1; + int hour = 0; + int minute = 0; + int second = 0; + + if (xlDateGroupFilter.DateTimeGrouping >= XLDateTimeGrouping.Year) + { + if (dateGroupItem?.Year?.HasValue ?? false) + year = (int)dateGroupItem.Year?.Value; + else + valid &= false; + } + + if (xlDateGroupFilter.DateTimeGrouping >= XLDateTimeGrouping.Month) + { + if (dateGroupItem?.Month?.HasValue ?? false) + month = (int)dateGroupItem.Month?.Value; + else + valid &= false; + } + + if (xlDateGroupFilter.DateTimeGrouping >= XLDateTimeGrouping.Day) + { + if (dateGroupItem?.Day?.HasValue ?? false) + day = (int)dateGroupItem.Day?.Value; + else + valid &= false; + } + + if (xlDateGroupFilter.DateTimeGrouping >= XLDateTimeGrouping.Hour) + { + if (dateGroupItem?.Hour?.HasValue ?? false) + hour = (int)dateGroupItem.Hour?.Value; + else + valid &= false; + } + + if (xlDateGroupFilter.DateTimeGrouping >= XLDateTimeGrouping.Minute) + { + if (dateGroupItem?.Minute?.HasValue ?? false) + minute = (int)dateGroupItem.Minute?.Value; + else + valid &= false; + } + + if (xlDateGroupFilter.DateTimeGrouping >= XLDateTimeGrouping.Second) + { + if (dateGroupItem?.Second?.HasValue ?? false) + second = (int)dateGroupItem.Second?.Value; + else + valid &= false; + } + + var date = new DateTime(year, month, day, hour, minute, second); + xlDateGroupFilter.Value = date; + + xlDateGroupFilter.Condition = date2 => XLDateTimeGroupFilteredColumn.IsMatch(date, (DateTime)date2, xlDateGroupFilter.DateTimeGrouping); + + if (valid) + filterList.Add(xlDateGroupFilter); + } } else if (filterColumn.Top10 != null) { diff --git a/ClosedXML/Excel/XLWorkbook_Save.cs b/ClosedXML/Excel/XLWorkbook_Save.cs index db763b6..a547d98 100644 --- a/ClosedXML/Excel/XLWorkbook_Save.cs +++ b/ClosedXML/Excel/XLWorkbook_Save.cs @@ -5473,49 +5473,77 @@ { var filterColumn = new FilterColumn { ColumnId = (UInt32)kp.Key - 1 }; var xlFilterColumn = xlAutoFilter.Column(kp.Key); - var filterType = xlFilterColumn.FilterType; - if (filterType == XLFilterType.Custom) + + switch (xlFilterColumn.FilterType) { - var customFilters = new CustomFilters(); - foreach (var filter in kp.Value) - { - var customFilter = new CustomFilter { Val = filter.Value.ToString() }; + case XLFilterType.Custom: + var customFilters = new CustomFilters(); + foreach (var filter in kp.Value) + { + var customFilter = new CustomFilter { Val = filter.Value.ToString() }; - if (filter.Operator != XLFilterOperator.Equal) - customFilter.Operator = filter.Operator.ToOpenXml(); + if (filter.Operator != XLFilterOperator.Equal) + customFilter.Operator = filter.Operator.ToOpenXml(); - if (filter.Connector == XLConnector.And) - customFilters.And = true; + if (filter.Connector == XLConnector.And) + customFilters.And = true; - customFilters.Append(customFilter); - } - filterColumn.Append(customFilters); - } - else if (filterType == XLFilterType.TopBottom) - { - var top101 = new Top10 { Val = (double)xlFilterColumn.TopBottomValue }; - if (xlFilterColumn.TopBottomType == XLTopBottomType.Percent) - top101.Percent = true; - if (xlFilterColumn.TopBottomPart == XLTopBottomPart.Bottom) - top101.Top = false; + customFilters.Append(customFilter); + } + filterColumn.Append(customFilters); + break; - filterColumn.Append(top101); - } - else if (filterType == XLFilterType.Dynamic) - { - var dynamicFilter = new DynamicFilter - { Type = xlFilterColumn.DynamicType.ToOpenXml(), Val = xlFilterColumn.DynamicValue }; - filterColumn.Append(dynamicFilter); - } - else - { - var filters = new Filters(); - foreach (var filter in kp.Value) - { - filters.Append(new Filter { Val = filter.Value.ToString() }); - } + case XLFilterType.TopBottom: - filterColumn.Append(filters); + var top101 = new Top10 { Val = (double)xlFilterColumn.TopBottomValue }; + if (xlFilterColumn.TopBottomType == XLTopBottomType.Percent) + top101.Percent = true; + if (xlFilterColumn.TopBottomPart == XLTopBottomPart.Bottom) + top101.Top = false; + + filterColumn.Append(top101); + break; + case XLFilterType.Dynamic: + + var dynamicFilter = new DynamicFilter + { Type = xlFilterColumn.DynamicType.ToOpenXml(), Val = xlFilterColumn.DynamicValue }; + filterColumn.Append(dynamicFilter); + break; + case XLFilterType.DateTimeGrouping: + var dateTimeGroupFilters = new Filters(); + foreach (var filter in kp.Value) + { + if (filter.Value is DateTime) + { + var d = (DateTime)filter.Value; + var dgi = new DateGroupItem + { + Year = (UInt16)d.Year, + DateTimeGrouping = filter.DateTimeGrouping.ToOpenXml() + }; + + if (filter.DateTimeGrouping >= XLDateTimeGrouping.Month) dgi.Month = (UInt16)d.Month; + if (filter.DateTimeGrouping >= XLDateTimeGrouping.Day) dgi.Day = (UInt16)d.Day; + if (filter.DateTimeGrouping >= XLDateTimeGrouping.Hour) dgi.Hour = (UInt16)d.Hour; + if (filter.DateTimeGrouping >= XLDateTimeGrouping.Minute) dgi.Minute = (UInt16)d.Minute; + if (filter.DateTimeGrouping >= XLDateTimeGrouping.Second) dgi.Second = (UInt16)d.Second; + + dateTimeGroupFilters.Append(dgi); + } + } + filterColumn.Append(dateTimeGroupFilters); + break; + + default: + var filters = new Filters(); + foreach (var filter in kp.Value) + { + filters.Append(new Filter { Val = filter.Value.ToString() }); + } + + filterColumn.Append(filters); + break; + } autoFilter.Append(filterColumn); } diff --git a/ClosedXML_Examples/AutoFilters/CustomAutoFilter.cs b/ClosedXML_Examples/AutoFilters/CustomAutoFilter.cs index 9cd1b9d..d173415 100644 --- a/ClosedXML_Examples/AutoFilters/CustomAutoFilter.cs +++ b/ClosedXML_Examples/AutoFilters/CustomAutoFilter.cs @@ -126,7 +126,7 @@ workbook.Worksheet(singleColumnMixed).AutoFilter.Sort(1, XLSortOrder.Descending); #endregion - #region Multi Column + #region Multi Column workbook.Worksheet(multiColumn).AutoFilter.Column(3).EqualTo("E"); workbook.Worksheet(multiColumn).AutoFilter.Sort(3, XLSortOrder.Descending); #endregion diff --git a/ClosedXML_Examples/AutoFilters/DateTimeGroupAutoFilter.cs b/ClosedXML_Examples/AutoFilters/DateTimeGroupAutoFilter.cs new file mode 100644 index 0000000..68905d2 --- /dev/null +++ b/ClosedXML_Examples/AutoFilters/DateTimeGroupAutoFilter.cs @@ -0,0 +1,43 @@ +using ClosedXML.Excel; +using System; + +namespace ClosedXML_Examples +{ + public class DateTimeGroupAutoFilter : IXLExample + { + public void Create(string filePath) + { + using (var wb = new XLWorkbook()) + { + IXLWorksheet ws; + + #region Single Column Dates + + String singleColumnDates = "Single Column Dates"; + ws = wb.Worksheets.Add(singleColumnDates); + + // Add a bunch of numbers to filter + ws.Cell("A1").SetValue("Dates") + .CellBelow().SetValue(new DateTime(2018, 1, 1).AddDays(2)) + .CellBelow().SetValue(new DateTime(2018, 1, 1).AddDays(3)) + .CellBelow().SetValue(new DateTime(2018, 1, 1).AddDays(3)) + .CellBelow().SetValue(new DateTime(2018, 1, 1).AddDays(5)) + .CellBelow().SetValue(new DateTime(2018, 1, 1).AddDays(1)) + .CellBelow().SetValue(new DateTime(2018, 1, 1).AddDays(4)); + + ws.Column(1).Style.NumberFormat.Format = "d MMMM yyyy"; + + // Add filters + ws.RangeUsed().SetAutoFilter().Column(1).AddDateGroupFilter(new DateTime(2018, 1, 1).AddDays(3), XLDateTimeGrouping.Day); + + // Sort the filtered list + ws.AutoFilter.Sort(1); + + #endregion Single Column Dates + + ws.Columns().AdjustToContents(); + wb.SaveAs(filePath); + } + } + } +} diff --git a/ClosedXML_Examples/ClosedXML_Examples.csproj b/ClosedXML_Examples/ClosedXML_Examples.csproj index 2f42066..dca9ada 100644 --- a/ClosedXML_Examples/ClosedXML_Examples.csproj +++ b/ClosedXML_Examples/ClosedXML_Examples.csproj @@ -69,6 +69,7 @@ Properties\AssemblyVersionInfo.cs + diff --git a/ClosedXML_Tests/ClosedXML_Tests.csproj b/ClosedXML_Tests/ClosedXML_Tests.csproj index 72509bc..2555467 100644 --- a/ClosedXML_Tests/ClosedXML_Tests.csproj +++ b/ClosedXML_Tests/ClosedXML_Tests.csproj @@ -252,6 +252,7 @@ + diff --git a/ClosedXML_Tests/Examples/AutoFilterTests.cs b/ClosedXML_Tests/Examples/AutoFilterTests.cs index 0ed0489..5aaf1b9 100644 --- a/ClosedXML_Tests/Examples/AutoFilterTests.cs +++ b/ClosedXML_Tests/Examples/AutoFilterTests.cs @@ -29,5 +29,11 @@ { TestHelper.RunTestExample(@"AutoFilter\TopBottomAutoFilter.xlsx"); } + + [Test] + public void DateTimeGroupAutoFilter() + { + TestHelper.RunTestExample(@"AutoFilter\DateTimeGroupAutoFilter.xlsx"); + } } -} \ No newline at end of file +} diff --git a/ClosedXML_Tests/Resource/Examples/AutoFilter/DateTimeGroupAutoFilter.xlsx b/ClosedXML_Tests/Resource/Examples/AutoFilter/DateTimeGroupAutoFilter.xlsx new file mode 100644 index 0000000..4e5ab05 --- /dev/null +++ b/ClosedXML_Tests/Resource/Examples/AutoFilter/DateTimeGroupAutoFilter.xlsx Binary files differ