Method to Set Filter to Filter Specified Data with C#

In order to be convenient for data analytics and statics, MS Excel provides users with function to filter data. Users can set a filter in one or more columns to select the specified data information from one column and then data in other row or column will be filtered automatically. 

According to requirement, users can select one or more columns to filter data. Therefore, users should confirm filter range at beginning. Because of this function, users can get necessary data information in worksheet easily and quickly.

In this post, I want to introduce a method about how to set filter in Excel by using C#.

In my example, I create a new workbook and write data in worksheet. Then, select all data columns as filter range. Also, I use a C# .NET Excel component, Spire.XLS for .NET. So I have added its dll file as reference in my project.

Detailed Steps:

  • Create a new workbook. Add an empty worksheet in this workbook and then initialize it. In order to have a better appearance, don’t display gridlines by using sheet.GridLinesVisible = false;
  • Invoke method CreateFilterData() which has been declared to write data in worksheet, including data information and cell format. Then, choose data range for set filters.
  • Save and Launch file.

Coding:

using System;
using Spire.Xls;
using System.Drawing;

namespace FilterData
{
    class Filter
    {
        static void Main(string[] args)
        {

            //Create Workbook
            Workbook workbook = new Workbook();
            workbook.CreateEmptySheets(1);
            Worksheet sheet = workbook.Worksheets[0];
            sheet.GridLinesVisible = false;

            //Filter Data
            CreateFilterData(sheet);
            sheet.AutoFilters.Range = sheet.Range[“A1:C1”];

            //Save and Launch
            workbook.SaveToFile(“Filter.xlsx”, ExcelVersion.Version2010);
            System.Diagnostics.Process.Start(“Filter.xlsx”);
        }

        static void CreateFilterData(Worksheet sheet)
        {

            //Customer Name
            sheet.Range[“A1”].Value = “Name”;
            sheet.Range[“A2”].Value = “Austin Brown”;
            sheet.Range[“A3”].Value = “Cathy Mill”;
            sheet.Range[“A4”].Value = “Daniel Short”;
            sheet.Range[“A5”].Value = “Fiona Hard”;

            //Customer Country
            sheet.Range[“B1”].Value = “Country”;
            sheet.Range[“B2”].Value = “Italy”;
            sheet.Range[“B3”].Value = “United Kingdom”;
            sheet.Range[“B4”].Value = “Argentina”;
            sheet.Range[“B5”].Value = “Germany”;

            //Customer Contact
            sheet.Range[“C1”].Value = “E-mail”;
            sheet.Range[“C2”].Value = “Austin.B23@gmail.com”;
            sheet.Range[“C3”].Value = “CM0927331@yahoo.com”;
            sheet.Range[“C4”].Value = “DanShortsss@hotmail.com”;
            sheet.Range[“C5”].Value = “G.Fional.H@gmail.com”;

            //Style
            sheet.Range[“A1:C1”].Style.Font.IsBold = true;
            sheet.Range[“A1:C1”].Style.Font.Size = 14;
            sheet.Range[“A1:C5”].Style.Font.FontName = “Kalinga”;
            sheet.Range[“A2:C5”].Style.Font.Size = 12;
            sheet.Range[“A2:C2”].Style.KnownColor = ExcelColors.BrightGreen;
            sheet.Range[“A3:C3”].Style.KnownColor = ExcelColors.Lavender;
            sheet.Range[“A4:C4”].Style.KnownColor = ExcelColors.PaleBlue;
            sheet.Range[“A5:C5”].Style.KnownColor = ExcelColors.Pink;

            //Border
            sheet.Range[“A1:C5”].Style.Borders[BordersLineType.EdgeTop].Color = Color.DarkBlue;
            sheet.Range[“A1:C5”].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin;
            sheet.Range[“A1:C5”].Style.Borders[BordersLineType.EdgeBottom].Color = Color.DarkBlue;
            sheet.Range[“A1:C5”].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin;
            sheet.Range[“A1:C5”].Style.Borders[BordersLineType.EdgeLeft].Color = Color.DarkBlue;
            sheet.Range[“A1:C5”].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin;
            sheet.Range[“A1:C5”].Style.Borders[BordersLineType.EdgeRight].Color = Color.DarkBlue;
            sheet.Range[“A1:C5”].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
        }
    }
}

Result Shown as Following:

Download Spire.XLS for .NET Here

Advertisements

Author: janewdaisy

.NET Program Beginner. Share methods about how to use C#/VB.NET to export data, operate Word, Excel, PDF and other useful skills.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s