public IXLDateTimeGroupFilteredColumn AddDateGroupFilter(DateTime date, XLDateTimeGrouping dateTimeGrouping)
{
Func <Object, Boolean> condition = date2 => XLDateTimeGroupFilteredColumn.IsMatch(date, (DateTime)date2, dateTimeGrouping);
_autoFilter.IsEnabled = true;
if (_autoFilter.Filters.TryGetValue(_column, out List <XLFilter> filterList))
{
filterList.Add(
new XLFilter
{
Value = date,
Operator = XLFilterOperator.Equal,
Connector = XLConnector.Or,
Condition = condition,
DateTimeGrouping = dateTimeGrouping
}
);
}
else
{
_autoFilter.Filters.Add(
_column,
new List <XLFilter>
{
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;
SetTopBottom(value, type);
}
private static void PopulateAutoFilter(XLAutoFilter xlAutoFilter, AutoFilter autoFilter)
{
var filterRange = xlAutoFilter.Range;
autoFilter.Reference = filterRange.RangeAddress.ToString();
foreach (var kp in xlAutoFilter.Filters)
{
var filterColumn = new FilterColumn {ColumnId = (UInt32)kp.Key - 1};
var xlFilterColumn = xlAutoFilter.Column(kp.Key);
var filterType = xlFilterColumn.FilterType;
if (filterType == 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.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;
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()});
}
filterColumn.Append(filters);
}
autoFilter.Append(filterColumn);
}
if (xlAutoFilter.Sorted)
{
var sortState = new SortState
{
Reference =
filterRange.Range(filterRange.FirstCell().CellBelow(), filterRange.LastCell()).RangeAddress.
ToString()
};
var 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);
}
}