private IEnumerable <double> GetValues(int value, XLTopBottomType type, bool takeTop)
{
using (var column = _autoFilter.Range.Column(_column))
{
using (var subColumn = column.Column(2, column.CellCount()))
{
var cellsUsed = subColumn.CellsUsed(c => c.DataType == XLCellValues.Number);
if (takeTop)
{
if (type == XLTopBottomType.Items)
{
return(cellsUsed.Select(c => c.GetDouble()).OrderByDescending(d => d).Take(value).Distinct());
}
var numerics1 = cellsUsed.Select(c => c.GetDouble());
Int32 valsToTake1 = numerics1.Count() * value / 100;
return(numerics1.OrderByDescending(d => d).Take(valsToTake1).Distinct());
}
if (type == XLTopBottomType.Items)
{
return(cellsUsed.Select(c => c.GetDouble()).OrderBy(d => d).Take(value).Distinct());
}
var numerics = cellsUsed.Select(c => c.GetDouble());
Int32 valsToTake = numerics.Count() * value / 100;
return(numerics.OrderBy(d => d).Take(valsToTake).Distinct());
}
}
}
public IXLStyle WhenIsBottom(Int32 value, XLTopBottomType topBottomType = XLTopBottomType.Items)
{
Values.Initialize(new XLFormula(value));
Percent = topBottomType == XLTopBottomType.Percent;
ConditionalFormatType = XLConditionalFormatType.Top10;
Bottom = true;
return(Style);
}
public IXLStyle WhenIsTop(Int32 value, XLTopBottomType topBottomType = XLTopBottomType.Items)
{
Values.Initialize(new XLFormula {
Value = value.ToString()
});
Percent = topBottomType == XLTopBottomType.Percent;
ConditionalFormatType = XLConditionalFormatType.Top10;
Bottom = false;
return(Style);
}
private void SetTopBottom(Int32 value, XLTopBottomType type, Boolean takeTop = true)
{
_autoFilter.Enabled = true;
_autoFilter.Column(_column).SetFilterType(XLFilterType.TopBottom)
.SetTopBottomValue(value)
.SetTopBottomType(type);
var values = GetValues(value, type, takeTop);
Clear();
_autoFilter.Filters.Add(_column, new List <XLFilter>());
Boolean addToList = true;
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 <Object, Boolean> condition = v => (v as IComparable).CompareTo(val) == 0;
if (addToList)
{
_autoFilter.Filters[_column].Add(new XLFilter
{
Value = val,
Operator = XLFilterOperator.Equal,
Connector = XLConnector.Or,
Condition = condition
});
}
var cell = row.Cell(_column);
if (cell.DataType != XLCellValues.Number || !condition(cell.GetDouble()))
{
continue;
}
row.WorksheetRow().Unhide();
foundOne = true;
}
if (!foundOne)
{
row.WorksheetRow().Hide();
}
addToList = false;
}
ws.ResumeEvents();
}
public void Bottom(Int32 value, XLTopBottomType type = XLTopBottomType.Items)
{
_autoFilter.Column(_column).TopBottomPart = XLTopBottomPart.Bottom;
SetTopBottom(value, type, false);
}
public IXLFilterColumn SetTopBottomType(XLTopBottomType value)
{
TopBottomType = value; return(this);
}
