Freigeben über


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

  • Anonymous
    October 30, 2005
    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)

  • Anonymous
    November 01, 2005
    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

  • Anonymous
    November 17, 2005
    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

  • Anonymous
    January 20, 2006
    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.

  • Anonymous
    January 31, 2006
    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?

  • Anonymous
    February 21, 2006
    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);
    }

  • Anonymous
    May 09, 2006
    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!

  • Anonymous
    July 25, 2006
    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

  • Anonymous
    August 24, 2006
    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

  • Anonymous
    November 02, 2006
    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?

  • Anonymous
    November 22, 2006
    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

  • Anonymous
    December 14, 2006
    The comment has been removed

  • Anonymous
    December 14, 2006
    Siya, This article may help http://support.microsoft.com/kb/823981 Also Office XP PIA's are available at http://www.microsoft.com/downloads/details.aspx?FamilyId=C41BD61E-3060-4F71-A6B4-01FEBA508E52&displaylang=en regards

  • Anonymous
    December 28, 2006
    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

  • Anonymous
    January 03, 2007
    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

  • Anonymous
    January 13, 2007
    How i can set the background color for a range of cells? Thanks

  • Anonymous
    January 31, 2007
    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

  • Anonymous
    May 07, 2007
    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";

  • Anonymous
    May 08, 2007
    @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";

  • Anonymous
    June 08, 2007
    The comment has been removed

  • Anonymous
    June 27, 2007
    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.

  • Anonymous
    July 17, 2007
    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

  • Anonymous
    August 03, 2007
    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?

  • Anonymous
    August 03, 2007
    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

  • Anonymous
    August 13, 2007
    Erica you are so awesome! I just realized that I was setting my columns at 0. Thanks, Carlos.

  • Anonymous
    September 26, 2007
    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

  • Anonymous
    December 27, 2007
    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);

  • Anonymous
    January 22, 2008
    Thnks a lot it is  very use full for me.....!

  • Anonymous
    February 11, 2008
    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

  • Anonymous
    March 03, 2008
    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.

  • Anonymous
    April 15, 2008
    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...

  • Anonymous
    October 20, 2008
    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.

  • Anonymous
    November 12, 2008
    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.

  • Anonymous
    November 12, 2008
    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.

  • Anonymous
    December 07, 2008
    Hi Erika, Can you suggest how to create borders around the cells(like a table format) in the Excel.

  • Shruthi
  • Anonymous
    January 10, 2009
    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!

  • Anonymous
    January 20, 2009
    How to programatically pass ALT+ENTER from c#.net

  • Anonymous
    April 08, 2009
    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.

  • Anonymous
    November 11, 2009
    please cab anyone help me this i tried various combinations but none seem to work out

  • Anonymous
    September 08, 2010
    Erika, This was such a huge help - cleared up some of my confusion and is wonderful code. THANK YOU!!!

  • Anonymous
    October 16, 2011
    The comment has been removed