How to programmatically generate Microsoft Excel AutoFiltered Lists with C#

Erika Ehrli Cabral

27 Oct 2005 12:10 PM

Comments 41

One of my favorite features in Microsoft Excel is AutoFilter. I love to manage lists of data in Excel where I can organize, sort, and filter my information in different columns.

Filtering is a quick and easy way to find and work with a subset of data in a range. A filtered range displays only the rows that meet custom filter criteria defined in a search query or fitler specified for a column. Microsoft Excel provides two commands for filtering ranges: AutoFilter and Advanced Filter. Unlike sorting, filtering does not rearrange a range. Filtering temporarily hides rows you do not want displayed. When Excel filters rows, you can edit, format, chart, and print your range subset without rearranging or moving it. You can learn more about autofiltering here: All About AutoFilter.

The best part is that you can programatically generate autofiltered lists from your managed applications thanks to the extensibility offered by the Microsoft Excel Primary Interop Assembly. You can use the Range.AutoFilter method to filter a list using AutoFilter. The following code sample (C# Console Application) generates an autofiltered list of all the directories and files that belong to given directory path. You can modify the code and send any path that you need.

namespace ErikaEc.OfficeTools.Excel
{
    using System;
    using System.IO;
    using Excel = Microsoft.Office.Interop.Excel;

    class DemoExcelAutoFiltering {
        [STAThread]
        static void Main(string[] args) {
            DirectoryInfo di = new DirectoryInfo(@"C:\Program Files\Microsoft Office\OFFICE11\1033");
            ExcelFileReport efr = new ExcelFileReport(di);
            efr.Generate();
        }
    }

    public class ExcelFileReport {
        private object _missing;
        private Excel.Workbook _book;
        Excel.Worksheet _sheet;
        Excel.Range _rng;
        int _row;
        private DirectoryInfo _di;
        ExcelHelper _eh = new ExcelHelper();

        public ExcelFileReport(DirectoryInfo di) {
            _di = di;
            _missing = System.Reflection.Missing.Value;
            _row = 4;
        }

        public void DocumentDirectory(DirectoryInfo di) {
            foreach (DirectoryInfo d in di.GetDirectories()) {
                DocumentDirectory(d);
            }

            foreach (FileInfo f in di.GetFiles()) {
                _row++;
                _rng = (Excel.Range)_sheet.Cells[_row, "A"];
                _rng.Value2 = di.Name;
                _rng = (Excel.Range)_sheet.Cells[_row, "B"];
                _rng.Value2 = f.FullName;
                _rng = (Excel.Range)_sheet.Cells[_row, "C"];
                _rng.Value2 = f.Name;
                _rng = (Excel.Range)_sheet.Cells[_row, "D"];
                _rng.Value2 = f.Length;
                _rng = (Excel.Range)_sheet.Cells[_row, "E"];
                _rng.Value2 = f.Extension;
                _rng = (Excel.Range)_sheet.Cells[_row, "F"];
                _rng.Value2 = f.LastWriteTime.ToLongDateString();
            }
        }

        public void Generate(){
            string caption = "File Analysis Results";
            string heading1 = "File Analysis Report for Folder " + _di.FullName;
            _book = _eh.Create(caption, heading1);
            _sheet = ((Excel.Worksheet)_book.ActiveSheet);
            WriteTableHeader();
            DocumentDirectory(_di);
            SetAutoFilter();
            _eh.Close();
        }

        private void SetAutoFilter(){
            string lastrow = "F" + _row.ToString();
            _rng = ((Excel.Worksheet)_book.ActiveSheet).get_Range("A4", lastrow);
            _rng.AutoFilter(1, _missing, Excel.XlAutoFilterOperator.xlAnd, _missing, true);
            _rng.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;
        }

        public void WriteTableHeader(){
            _rng = ((Excel.Worksheet)_book.ActiveSheet).get_Range("A4", "H3");
            _rng.Font.Bold = true;
            _rng.EntireRow.Font.Bold = true;

            _rng = (Excel.Range)_sheet.Cells[_row, "A"];
            _rng.Value2 = "Parent Directory";
            _rng = (Excel.Range)_sheet.Cells[_row, "B"];
            _rng.Value2 = "Full Path";
            _rng = (Excel.Range)_sheet.Cells[_row, "C"];
            _rng.Value2 = "File Name";
            _rng = (Excel.Range)_sheet.Cells[_row, "D"];
            _rng.Value2 = "Size";
            _rng = (Excel.Range)_sheet.Cells[_row, "E"];
            _rng.Value2 = "Type";
            _rng = (Excel.Range)_sheet.Cells[_row, "F"];
            _rng.Value2 = "Last Modified";

            _sheet.Columns.ColumnWidth = 30;
        }
    }

    class ExcelHelper {
        private Excel.Application _excelApplication;

        public ExcelHelper() {
            _excelApplication = new Excel.Application();
        }

        public Excel.Workbook Create(string caption, string heading1) {
            try {
                _excelApplication.Caption = caption;
                _excelApplication.ScreenUpdating = false;
                _excelApplication.Visible = false;

                Excel.Workbook book = _excelApplication.Workbooks.Add(Excel.XlSheetType.xlWorksheet);
                Excel.Worksheet sheet = (Excel.Worksheet)book.ActiveSheet;

                Excel.Range r = (Excel.Range)sheet.Cells[1, "A"];
                r.Value2 = heading1;
                r.EntireRow.Font.Bold = true;

                return book;

            }
            catch (Exception ex) {
                throw (ex);
            }
        }

        public void Close() {
            _excelApplication.ScreenUpdating = true;
            _excelApplication.Visible = true;
            _excelApplication.DisplayAlerts = true;

            if (_excelApplication != null) {
                _excelApplication.Quit();
                _excelApplication = null;
                GC.Collect();
                GC.WaitForPendingFinalizers();
                GC.Collect();
                GC.WaitForPendingFinalizers();
            }
        }
    }
}

Run this application and give it a try!