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