public void AddDataFilterCommand(Excel.Worksheet worksheet, string range, int columnNumber, Excel.XlAutoFilterOperator filterEnum, string[] criteria = null, string criteria2 = null) { if (!worksheet.IsRange(range)) { throw new Exception($"The range, {range}, does not exist on the current worksheet, {worksheet.Name}"); } // ExcelParse class returns 0 as integer value for blank cells and column indices start at 1 for Excel if (columnNumber == 0) { columnNumber = 1; } if (criteria != null && criteria.Length > 0) { if (string.IsNullOrEmpty(criteria2)) { worksheet.Range[range].AddAutoFilter(columnNumber, filterEnum, criteria); } else { worksheet.Range[range].AddAutoFilter(columnNumber, filterEnum, criteria, criteria2); } } else { worksheet.Range[range].AddAutoFilter(columnNumber, filterEnum); } }
/// <summary> /// Adds a data filter to a range /// </summary> /// <param name="range">Target range, including the header row</param> /// <param name="columnNumber">Column to apply filter to, index starts at 1 and relates to the first column on the left</param> /// <param name="filterEnum">Excel enum how data will be filtered</param> /// <param name="criteria">First criteria for filtering</param> /// <param name="criteria2">Second criteria for filtering</param> public static void AddAutoFilter(this Excel.Range range, int columnNumber = 1, Excel.XlAutoFilterOperator filterEnum = XlAutoFilterOperator.xlAnd, string[] criteria = null, string criteria2 = null) { int columnCount = range.Columns.Count; if (columnNumber < 1 || columnNumber > columnCount) { throw new Exception($"The range selected, {range}, only has {columnCount} columns. Please enter a value greater than or equal to 1 and less than or equal to {columnCount}"); } string singularCriteria = ""; if (criteria.Length == 1) { singularCriteria = criteria.First(); } // Adds data filter to range without filtering data if (columnNumber == 1 && filterEnum == XlAutoFilterOperator.xlAnd && criteria == null && criteria2 == null) { range.AutoFilter(1); } // If filter can be applied to a column without criteria else if (criteria == null && criteria2 == null) { range.AutoFilter(columnNumber, Type.Missing, filterEnum); } // If there is one criteria else if (criteria != null && criteria2 == null) { if (criteria.Length == 1) { range.AutoFilter(columnNumber, singularCriteria, filterEnum); } else { range.AutoFilter(columnNumber, criteria, filterEnum); } } // If there are two criteria else { if (criteria.Length == 1) { range.AutoFilter(columnNumber, singularCriteria, filterEnum, criteria2); } else { range.AutoFilter(columnNumber, criteria, filterEnum, criteria2); } } }