How to programmatically generate Microsoft Excel AutoFiltered Lists with C#


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!


Comments (41)
  1. Rahman Hadi says:

    Hi erika,

    I’ve a problem that’s still related with your topics and it makes me so stressfull … i developed infopath form and C# as codebehind. While I send a value to the excel that was created before on your programming way is still OK. But, if I try to read from Excel cell from infopath code always display error:

    Exception from HRESULT: 0x800A03EC.

    at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData)

    at Microsoft.Office.Interop.Excel.Range.get_Item(Object RowIndex, Object ColumnIndex)

    Do you know about this error??

    thank you very much …

    Rahman Hadi(rahman_hadi@fmi.com)

  2. Hi Rahman,

    I have not seen that error, but I made some research and I learned that when you are working with Excel 2003 and other Visual Studio solutions you might need to consider globalization and localization aspects. I found some articles that might help:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dv_wrcore/html/wrconGlobalizingLocalizingOfficeSolutions.asp

    http://www.devx.com/OfficeProDev/Article/29507?trk=DXRSS_LATEST

    http://blogs.msdn.com/eric_carter/archive/2005/06/15/429515.aspx

  3. Fernando Ferreira says:

    One way to workaround the problem of using an English version of Excel with a culture ID other than English, is to add these lines in your code:

    System.Globalization.CultureInfo enUS = new System.Globalization.CultureInfo("en-US");

    System.Threading.Thread.CurrentThread.CurrentCulture = enUS;

    Fernando Ferreira (fferre)

    Microsoft Consulting Services

  4. vytas says:

    Hello,

    I have an Excel template with inserted database query. When I open a template programically from my application, I want to set AutoFilter according to values that user enters in my application. The code is as follows:

    // open template, then:

    Excel.Range _range = (Excel.Range)sheet.get_Range("A3", "Q3");

    _range.AutoFilter(3, "=Petras", Excel.XlAutoFilterOperator.xlAnd, vk_missing, true);

    _range.AutoFilter(5, "<>Jonas", Excel.XlAutoFilterOperator.xlAnd, vk_missing, true);

    // show worksheet

    the funny thing is that AutoFiltering works 4 times out of 5 – sometimes autofiltering is not set though no errors are displayed. Is that some kind if bug or my code isn’t correct?

    Thank you very much in advance.

  5. I am generating an XLS file via the PIAs using JSCRIPT .NET. I have carefully called Marshall.Release on each object and have even included a call to GC.Collect. EXCEL.EXE refuses to quit … I don’t know what to do. Can you help?

  6. Guntur Juliantoro says:

    You have to release ALL objects that you created using excel. Then call garbage collection.

    Sample code below i am releasing each object using ReleaseComObject function.

    object delete = oSheet;   // assign worksheet as object

    ReleaseComObject(ref delete); //delete worksheet

    delete = oWB; // assign workbook as object

    ReleaseComObject(ref delete); // delete workbook

    delete = oXL;  // assign excel application as obejct

    ReleaseComObject(ref delete); //delete excel

    GC.Collect();

    function

    —————-

    private void ReleaseComObject(ref Object o)

    {

        while (System.Runtime.InteropServices.Marshal.ReleaseComObject(o) > 0);

    }

  7. Adam says:

    I got the 0x800A03EC error too.

    I tried the Globalization fix, but that didn’t work (I didn’t really expect it to – as far as I’m aware, there’s no difference in the way the Brits and the Americans reference Excel cells and columns!

    I found I got the error when selecting a range using oWB.Columns[1,2]. I used Columns because I wanted to set the ColumnWidth.

    I change it to oWB.Cells[1,2] and it worked fine. I guess .ColumnWidth (and no doubt .AutoFilter) don’t really care HOW the Range was created, just that it is a range!

  8. sina says:

    i wanted to know if there is any online resource available where i can learn more abt the excel 11.0 object library. something that starts from basics and moves on to a more advanced level

  9. Hi Sina,

    You can find the VBA reference for Excel 11.0 here:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbaxl11/html/xltocOMMap_HV01049651.asp

    You can find more VBA reference for other products and versions here:

    http://msdn.microsoft.com/office/reference/vba/default.aspx

  10. Gagan says:

    I have an xls file created and i want toopen it add new sheet in existing file and save the xls file is  there any one who can help me in this?

  11. abhimanyusirohi says:

    Hi Erika,

    I am developing an Excel Add-In in Visual Studio 2005 (C#) for Excel XP and higher.

    I read somewhere that I need to explicitly release all objects even the Range object that I get from get_Range() method.

    What else need to be released in an Add-In ?

    regards

    Abhimanyu Sirohi

  12. Siya says:

    I am having problems with excel reference in my .NET project.

    I added reference to Excel object in my .NET application by including Excel 11.0 object library. My app worked fine until the following line was added to one of the classes where the excel operations are performed.

    using Excel = Microsoft.Office.Interop.Excel;

    When the above line is added the build throws error in my machine saying

    I:TransformSpreadsheetExcelLoader.cs(12): Namespace ” already contains a definition for ‘Excel’

    If the above line is removed, it works fine in my machine, but throws a different error in my colleague’s machine.

    Can somebody help me in telling me under what circumstance the above line should be added, and why the .NET environment in my machine is complaining about the line.

    Any help is greatly appreciated.

    -Siya

  13. Ram says:

    Hi Erika,

    I am developing an Excel Add-In in Visual Studio 2005 (C#) for Excel 2007.

    I would like to know how to dynamically remove the Excel Cells in Memory.

    Thannks,

    Ram

  14. Nilesh Upadhyay says:

    Hi,

    I m facing one problem

    How to Catch  Any Excel File Which user opens..

    I want to catch that file from my program and if any changes made in that file then i want to save those changes as well as old changes also i want …

    I want to make program in C#.NET or ASP.NET  can you help me about this?

    Contact me on:

    nileshupadhyay10582@gmail.com

    Thanks in advance

  15. Ahmed says:

    How i can set the background color for a range of cells?

    Thanks

  16. Anand says:

    hai friends,

    I need help from u. i.e.,How to programmatically generate Microsoft Excel sheets with having dorpdown Lists in some columns through C#.net 2005.

    can any one know please tell me the solution

  17. sfuqua says:

    I got to this post by searching for "0x800A03EC". In my case, I had something like:

    xlSheet.Cells[0,0] = "a value";

    The error went away when I switched this to:

    xlSheet.Cells[1,"A"] = "a value";

  18. The_Assimilator says:

    @Ahmed: it’s simple 😉

    Excel.Range range = worksheet.get_Range("A1", "I9");

    range.Interior.Color = System.Drawing.Color.Green.ToArgb();

    Note that the Color property must be set to an RGB integer value, or you will get an exception.

    @sfuqua: you got that error because Excel indices start at 1, not 0 as you might expect.

    Thus, to get the first (top-left) cell in a worksheet, you would use:

    xlSheet.Cells[1, 1] = "a value";

  19. Efrain Juarez says:

    I am developing a web aplication where the user uses a editor to store text on a database. then this information is exported to a excel file.

    the problem I am facing is that when I set the text on a cell with new lines, it shows the text on one line and the new line characters are shown as small boxes. I need to show this text as if the user has enter Alt + Enter on the cell and the text viewed on multiples fows on the same cell

    Any information will be appreciated

  20. Erika says:

    For everyone who is getting the 0x800A03EC exception:

    Excel cell indexing starts from 1 (NOT 0), if you try to access a cell like [0,x] or [x,0], the exception will be raised.

  21. Mousum says:

    Hi,

    I am creating some UDFs (in C# automation) and these are working fine. But i can’t put descriptions of the functions and arguments.

    Please help me.

    Thanks

    Mousum

  22. Marcelo says:

    Hi Erika, I have this situation, I need to name, at the moment of the creation, every Sheet in my Worksheet, is that possible? i.e:

    Microsoft.Office.Interop.Excel.Application excel;

    excel.ActiveWorkbook.Worksheets.Add( missing ,excel.ActiveWorkbook.Worksheets[ excel.ActiveWorkbook.Worksheets.Count ] , missing , missing );

    ///below I’m creating the necessary Sheets in my Worksheet; if the aplication needs 3 sheets, the code below, automatically will generate Sheet1, Sheet2, Sheet3. It is possible to generate it with other names?

  23. Drummond Gow says:

    I am struggling to obtain argb colors from an excel sheet to store in a database, to be used later.

    below is the closest i got but it gives me wrong colors back (vb6 stored a value and it worked, same code in vs2005 errors)

    Dim Col As Color

    Col = Color.FromArgb(worksheet.Cells(ExcelRow, 4).interior.color.GetHashCode)

               Dim a As Byte = Col.A

               Dim r As Byte = Col.R

               Dim g As Byte = Col.G

               Dim b As Byte = Col.B

               fld = Format(a, "000") & "," & Format(r, "000") & "," & Format(g, "000") & "," & Format(b, "000")

    ”””””

    2nd program

    acell = Excel.ActiveSheet.Cells(row, col)

    Fld = data1("rowcolor").Value

    acell.BackColor = Color.FromArgb(Mid(Fld, 1, 3), Mid(Fld, 5, 3), Mid(Fld, 9, 3), Mid(Fld, 13, 3))

    thanks any help appreciated

  24. Carlos. says:

    Erica you are so awesome! I just realized that I was setting my columns at 0.

    Thanks,

    Carlos.

  25. gopi says:

    Can any 1 tell me how to delete a row in excel through .Net(VB/c#)

    i’ve tried with

    excl(Excel object).Rows(i).Delete()

    but it is giving error

  26. Peter says:

    You need to get a range object and then delete the range as below. (I don’t have experience delete rows, but this is how I delete cells.

    r1.Delete(XlDeleteShiftDirection.xlShiftUp);

  27. Anushka K Rajasingha says:

    Thnks a lot it is  very use full for me…..!

  28. Sumy says:

    Can someone tell me how to format the data in Excel File. For example i have data in the format "0527" in the dataset.But in the Excel file it is being displayed as "527" only. It is skipping the prefixed zeros. Please help

  29. Anand py says:

    Hi friends,

     i want to display the data in asp.net along with cell colors  as it is present in excel sheet, plz can any one help me. i’m using c# as code behind.

  30. Biff MaGriff says:

    I hate excel co-ords, so I made this.

    Helps with looping…

    <code>

           private static string ConvertToExcelCoord(int Col, int Row)

           {

               int c1 = -1;

               while (((int)’A’) + Col > ((int)’Z’))

               {

                   Col -= 26;

                   c1++;

               }

               return (c1 >= 0 ? ((char)(((int)’A’) + c1)).ToString() : "") + ((char)(((int)’A’) + Col)).ToString() + ((int)(Row + 1)).ToString();

           }

    </code>

    There should probably be a check and an exception if the coords are too big…

  31. Daman says:

    I need to insert alt+enter in excel programmatically to show data in separate lines within a cell.

    Efrain Juarez asked this as well, but nobody replied so far.

  32. Moneitor says:

    As my friends wrote before me, i need to insert Alt+Enter in Excel to show data in separate lines within a cell.

    Anyone knows a solution?

    Thanks a lot everybody.

  33. Moneitor says:

    Well, I have de solution to represent Alt + Enter programmatically With VB.NET.

    WorkSheet(Row, Column).Value = "TextForFirstLine" & Chr(10) & "TextForSecondLine"

    So, Chr(10) represents Alt+Enter.

    I hope this solution helps you.

    Salutations everybody.

  34. Neelima says:

    Hi Erika,

    Can you suggest how to create borders around the cells(like a table format) in the Excel.

    – Shruthi

  35. Kevin says:

    Is there a way to filter a field with more than two criteria.  In VBA you can specify an array list of criteria and I have been trying to implement something similar in C# with no luck.  Below is the VBA example:

    ActiveSheet.ListObjects("tableOpenedData").Range.AutoFilter Field:=8, _

           Criteria1:=Array("BLT / Desktop Tool", "Delivery", "Editorial"), Operator:= _

           xlFilterValues

    Any suggestions on doing this in C# would be appreciated.

    Thanks!

  36. jhabi says:

    How to programatically pass ALT+ENTER from c#.net

  37. gever says:

    Can you suggest how to create borders around the cells(like a table format) in the Excel ?

    chartRange.BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, Excel.XlColorIndex.xlColorIndexAutomatic);

    ttp://csharp.net-informations.com/excel/csharp-format-excel.htm

    tks.

  38. Pavan says:

    please cab anyone help me this i tried various combinations but none seem to work out

  39. Will says:

    Erika,

    This was such a huge help – cleared up some of my confusion and is wonderful code.

    THANK YOU!!!

  40. cherry says:

    British reporters are known for doing almost anything to get a Mulberry Bags. But reports that a newspaper secretly listened to telephone messages of murdered schoolgirls and other private citizens have produced Mulberry Handbags and anger.

    On Friday, British police arrested Andy Coulson, former editor of Mulberry Bag Britain's best-selling newspaper, News of Mulberry Outlet the World. The investigation led him to Mulberry UK Sale resign in January as communications director to Prime Minister David Cameron.

    The arrest came in a widening investigation of Mulberry UK telephone hacking. Other accusations include paying police for mulberry shoulder bags information on stories. The Reuters news agency reported that Mr. Coulson was released on Mulberry Bag UK until a date in October.

    Prime Minister Cameron promised Men's Mulberry Bags Friday that a judge will lead a full public inquiry into Women's Mulberry Bags the case after police complete their investigation.

    DAVID CAMERON: "Murder victims, terrorist victims, families who have lost loved ones, sometimes defending our country, that these people could have had their phones hacked into, in order to generate stories for Mulberry Bags  Mulberry Handbags  Mulberry Bag   Mulberry Outlet  Mulberry UK  mulberry bayswater bag  Mulberry Alexa Bag, is simply disgusting."

Comments are closed.

Skip to main content