Export (0) Print
Expand All

Creating Auto-Filtered Tables in Excel 2007

Office 2007

Summary:  Learn how to programmatically add a filter to a column of table data in Microsoft Office Excel 2007.

Office Visual How To

Applies to:  2007 Microsoft Office System, Microsoft Office Excel 2007, Microsoft Visual Studio 2008

Joel Krist, iSoftStone

June 2009

Overview

In Microsoft Office Excel 2007, you can quickly arrange your worksheet data to find the answers that you need by using enhanced filtering and sorting. This Visual How To illustrates using the Excel 2007 Primary Interop Assembly (PIA) and the Range.AutoFilter method to programmatically add a filter to a column of data in table.

See It

Creating Auto Filtered Tables in Excel

Watch the Video

Length: 03:57 | Size: 4.30 MB | Type: WMV file

Code It | Read It | Explore It

Download the sample code

This Visual How To walks through the following 5 steps to illustrate the process of adding a filter to a column of table data:

  1. Creating an Excel workbook with sample data.

  2. Creating a Windows console application solution in Visual Studio 2008.

  3. Adding a reference to the Excel 2007 Primary Interop Assembly.

  4. Importing the Excel 2007 Primary Interop Assembly namespace.

  5. Adding the sample code to the solution.

Creating an Excel Workbook with Sample Data

The sample code presented with this Visual How To assumes that an Excel 2007 workbook named AutoFilterSample.xlsx already exists and contains a worksheet named Quarterly Sales which contains a data table named SalesData.

To Create the AutoFilterSamples.xlx Sample Workbook

  1. Start Excel 2007.

  2. Create a new workbook named AutoFilterSample.xlsx.

  3. Rename the Sheet1 worksheet to Quarterly Sales.

  4. Insert a table into the Quarterly Sales worksheet that has three columns named SalesPerson, Region, and SalesAmount. Enter data into the table, specifying a range of values in the SalesAmount column that are both greater than and less than 500.

    Table 1. Sample Data

    SalesPerson

    Region

    SalesAmount

    Joe

    North

    260

    Robert

    South

    660

    Michelle

    East

    940

    Erich

    West

    410

    Dafna

    North

    800

    Rob

    South

    900

    Figure 1. Creating the Sample Workbook

    Sample Workbook
  5. On the Formulas ribbon tab, click Define Name in the Defined Names group.

    Figure 2. Creating the Sample Workbook

    Defined Names group
  6. In the New Name dialog box, create a named ranged with the name SalesData that is scoped to the Quarterly Sales worksheet and refers to the Table1 table.

    Figure 3. Creating the Named Range

    Creating the Named Range
  7. Click OK to save the named range and then save the workbook.

Creating a Windows Console Application in Visual Studio 2008

This Visual How To uses a Windows console application as the framework for the sample code. The console application type was selected only for its simplicity. Other application types could use the same approach presented here.

To Create a Windows Console Application in Visual Studio 2008

  1. Start Microsoft Visual Studio 2008.

  2. From the Project menu, click File, then click New.

  3. In the New Project dialog box, in the Project types pane click the Visual Basic Windows or Visual C# Windows type.

  4. In the Templates pane, click Console Application.

  5. Name the project and solution ExcelAutoFilter.

    Figure 4. Creating the Solution

    Creating the Solution
  6. To create the solution, click OK.

Adding a Reference to the Excel 2007 Primary Interop Assembly

This Visual How To makes use of the Excel 2007 Primary Interop Assembly (PIA) to create and manipulate an Excel workbook. The Excel 2007 Primary Interop Assembly exposes the Excel 2007 object model to managed code and is installed with Excel 2007. It is also available for download as part of the 2007 Microsoft Office System Update: Redistributable Primary Interop Assemblies.

To Add a Reference to the Excel 2007 Primary Interop Assembly

  1. From the Add Reference menu, select the Visual Studio Project

  2. Select version 12.0.0.0 of the Microsoft.Office.Interop.Excel component in the Add Reference dialog box and click OK button to add the reference.

    Figure 5. Adding the Reference

    Adding the Reference

Importing the Excel 2007 Primary Interop Assembly Namespace

Importing the Excel 2007 PIA namespace allows code to work with the objects and types defined in the namespace without specifying the fully qualified namespace path each time.

For a Visual Basic project, add the following line to the top of the Module1.vb source file.

Imports Microsoft.Office.Interop.Excel

For a C# project, add the following line to the top of the Program.cs source file below the other using statements generated by Visual Studio.

using Microsoft.Office.Interop.Excel;

Adding the Sample Code to the Solution

For a Visual Basic project, replace the contents of the Module1 module in the Module1.vb source file with the following code.

' Declare variables that hold references to Excel objects.
Dim excelApplication As ApplicationClass = Nothing
Dim excelWorkBook As Workbook = Nothing
Dim targetSheet As Worksheet = Nothing
Dim salesTable As Range = Nothing

' Declare helper variables.
Dim workBookName As String = _
    "C:\Temp\AutoFilterSample.xlsx"
Dim worksheetName As String = "Quarterly Sales"
Dim rangeName As String = "'Quarterly Sales'!SalesData"

Try
    ' Create an instance of Excel 2007.
    excelApplication = New ApplicationClass()

    ' Open the source workbook.
    excelWorkBook = _
        excelApplication.Workbooks.Open(workBookName)

    ' Get the worksheet that contains the data to filter.
    targetSheet = excelWorkBook.Worksheets(worksheetName)

    ' Get a range holding the data to filter.
    salesTable = targetSheet.Names.Item(rangeName).RefersToRange

    ' Set a filter on the third column to display values
    ' greater than 500.
    salesTable.AutoFilter(3, ">500", _
        XlAutoFilterOperator.xlFilterValues, , True)

    ' Save the workbook.
    excelWorkBook.Save()
Catch ex As Exception
    Console.WriteLine(ex.Message)
Finally
    ' Release the references to the Excel objects.
    salesTable = Nothing
    targetSheet = Nothing

    ' Close the Workbook object.
    If Not excelWorkBook Is Nothing Then
        excelWorkBook = Nothing
    End If

    ' Close the ApplicationClass object.
    If Not excelApplication Is Nothing Then
        excelApplication.Quit()
        excelApplication = Nothing
    End If

    ' Garbage collection.
    GC.Collect()
    GC.WaitForPendingFinalizers()
    GC.Collect()
    GC.WaitForPendingFinalizers()
End Try

For a C# project, replace the contents of the Main method in the Program.cs source file with the following code.

// Declare variables that hold references to Excel objects.
ApplicationClass excelApplication = null;
Workbook excelWorkBook = null;
Worksheet targetSheet = null;
Range salesTable = null;

// Declare helper variables.
string workBookName = @"C:\Temp\AutoFilterSample.xlsx";
string worksheetName = "Quarterly Sales";
string rangeName = "'Quarterly Sales'!SalesData";

object useDefault = Type.Missing;

try
{
    // Create an instance of Excel.
    excelApplication = new ApplicationClass();

    // Open the source workbook.
    excelWorkBook =
        excelApplication.Workbooks.Open(workBookName,
            useDefault, useDefault, useDefault, useDefault,
            useDefault, useDefault, useDefault, useDefault,
            useDefault, useDefault, useDefault, useDefault,
            useDefault, useDefault);

    // Get the worksheet that contains the data to filter.
    targetSheet =
        (Worksheet)(excelWorkBook.Worksheets[worksheetName]);

    // Get a range holding the data to filter.
    salesTable = targetSheet.Names.Item(rangeName, useDefault,
        useDefault).RefersToRange;

    // Set a filter on the third column to display values.
    // greater than 500
    salesTable.AutoFilter(3, ">500",
        XlAutoFilterOperator.xlFilterValues, useDefault, true);

    // Save the workbook.
    excelWorkBook.Save();
}
catch (Exception ex)
{
    Console.WriteLine(ex.Message);
}
finally
{
    // Release the references to the Excel objects.
    salesTable = null;
    targetSheet = null;

    // Close the Workbook object.
    if (excelWorkBook != null)
        excelWorkBook = null;

    // Close the ApplicationClass object.
    if (excelApplication != null)
    {
        excelApplication.Quit();
        excelApplication = null;
    }

    //Garbage collection.
    GC.Collect();
    GC.WaitForPendingFinalizers();
    GC.Collect();
    GC.WaitForPendingFinalizers();
}

The sample code shown here assumes that the AutoFilterSample.xlsx workbook that you created is located in the C:\Temp folder. Modify the sample code and change the value of the workBookName variable if the workbook is located in a different folder.

To build and run the solution In Visual Studio, type Ctrl+F5. This launches the ExcelAutoFilter.exe console application and modifies the AutoFilterSample.xlsx workbook so that a filter of "Greater than 500" is added to the SalesAmount column of the SalesData table.

Figure 6. Filtered Column

Filtered column

The Range.Autofilter method exposed by the Excel 2007 PIA allows you to apply a filter to a column in a table. The sample code shown with this Visual How To works with a worksheet that contains a table as a named range with a known name. The code uses the WorkSheet.Names collection to request a range object for the table and then uses the Range.Autofilter method to set a filter on a column in the table.

// Get a range holding the data to filter.
salesTable = targetSheet.Names.Item(rangeName, useDefault,
    useDefault).RefersToRange;

// Set a filter on the third column to display values.
// greater than 500
salesTable.AutoFilter(3, ">500",
    XlAutoFilterOperator.xlFilterValues, useDefault, true);
Show:
© 2014 Microsoft