Microsoft Excel provides some good features to AutoFilter or Validate worksheet data. AutoFilter is the quickest way to select only those items from the worksheet that you want to display in a list. Moreover, AutoFilter allows a user to filter items in a list according to a set criteria. You can filter text, numbers or dates with AutoFilter. The other feature, Data Validation gives you the ability to set rules pertaining to data entered on a worksheet. For example, you might want to insure that a column labeled DATE contains only dates or that another column contains only numbers. You could even insure that a column labeled DATE contains only dates during a certain period. In short words, with Data Validation, you can control what is entered on the worksheet. Aspose.Cells fully supports Data Validation and AutoFilter features of Microsoft Excel.

 

Auto Filter Data

 

 

Using Microsoft Excel

To activate AutoFilter in Microsoft Excel, click in your heading row and go to Data | Filter | AutoFilter as shown in the figure below:

 

 

Figure: Activating AutoFilter on the heading row

 

When you apply AutoFilter to a worksheet, filter switches (black drop down arrows) will appear to the right of your column headings. Once the drop down arrows appear, click on one of the arrows to the right of a column heading and you will see a list of options as shown below:

 

 

Figure: Displaying list of options on clicking drop down arrows

The details of few of the options are listed below:

 

Options	Description
All	Shows all items in the list once
Top 10	Shows the top 10 items in the list once
Custom	Used to customise filter criteria

 

So, using these options, users filter their worksheet data in Microsoft Excel.

 

Using Aspose.Cells

Aspose.Cells is so powerful to bring such great features of Microsoft Excel to a programming environment so that developers can apply these features in their spreadsheets at runtime programmatically.

Aspose.Cells provides a class, Workbook that represents an Excel file. Workbook class contains a Worksheets collection that allows to access each worksheet in the Excel file.

A worksheet is represented by the Worksheet class. Worksheet class provides a wide range of properties and methods to manage a worksheet. But, to create an AutoFilter in a worksheet, developers may use AutoFilter property of the Worksheet class. AutoFilter property is infact an object of AutoFilter class, which further provides a Range property to specify the range of cells that make up a heading row. According to the specified range of cells, AutoFilter is created in the worksheet by Aspose.Cells.

Note: In each worksheet, you can only specify one filter range. This is limited by Microsoft Excel. For Custom data filterization, you can use AutoFilter.Custom method for your need.

In the example given below, we have created the same AutoFilter using Aspose.Cells as we created using Microsoft Excel in the above section.

Example:

[C#]

 

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Creating a file stream containing the Excel file to be opened

FileStream fstream = new FileStream("C:\\book1.xls",FileMode.Open);

 

 

//Opening the Excel file through the file stream

workbook.Open(fstream);

 

 

//Accessing the first worksheet in the Excel file

Worksheet worksheet = workbook.Worksheets[0];

 

 

//Creating AutoFilter by giving the cells range of the heading row

worksheet.AutoFilter.Range = "A1:B1";

 

 

//Saving the modified Excel file in default (that is Excel 2000) format

workbook.Save("C:\\output.xls",FileFormatType.Default);

 

 

//Closing the file stream to free all resources

fstream.Close();

 

 

[VB.NET]

 

'Instantiating a Workbook object

Dim workbook As Workbook = New Workbook()

 

 

'Creating a file stream containing the Excel file to be opened

Dim fstream As FileStream = New FileStream("C:\\book1.xls",FileMode.Open)

 

 

'Opening the Excel file through the file stream

workbook.Open(fstream)

 

 

'Accessing the first worksheet in the Excel file

Dim worksheet As Worksheet = workbook.Worksheets(0)

 

 

'Creating AutoFilter by giving the cells range of the heading row

worksheet.AutoFilter.Range = "A1:B1"

 

 

'Saving the modified Excel file in default (that is Excel 2000) format

workbook.Save("C:\\output.xls",FileFormatType.Default)

 

 

'Closing the file stream to free all resources

fstream.Close()

 

 

[JAVA]

 

//Instantiating a Workbook object

Workbook workbook = new Workbook();

 

 

//Opening the Excel file through the file stream

workbook.open("C:\\book1.xls");

 

 

//Accessing the first worksheet in the Excel file

Worksheet worksheet = workbook.getWorksheets().getSheet(0);

 

 

//Creating AutoFilter by giving the cells range

AutoFilter autoFilter = worksheet.getAutoFilter();

autoFilter.setRange(new CellArea(0,0,4,1));

 

 

//Saving the modified Excel file

workbook.save("C:\\output.xls");

 

Moreover, to filter columns with specified values, developers may also call Filter method of the AutoFilter class.

Example:

[C#]

 

 

//Creating AutoFilter by giving the cells range of the heading row

worksheet.AutoFilter.Range = "A1:B1";

 

 

//Filtering columns with specified values

worksheet.AutoFilter.Filter(1, "Bananas");

 

 

[VB.NET]

 

 

'Creating AutoFilter by giving the cells range of the heading row

worksheet.AutoFilter.Range = "A1:B1"

 

 

'Filtering columns with specified values

worksheet.AutoFilter.Filter(1, "Bananas")

 

 

[JAVA]

//Creating AutoFilter by giving the cells range

AutoFilter autoFilter = worksheet.getAutoFilter();

autoFilter.setRange(new CellArea(0,0,4,1));

 

 

//Filtering columns with specified values

autoFilter.filter(1, "Bananas");

 

Data Validation Types and Execution

 

Using Microsoft Excel

To create validations using Microsoft Excel, select the cells (in the worksheet) to which you want to apply the Data Validation then select Data | Validation menu item from the Microsoft Excel menu. Click the Settings tab and complete the dialog box as shown below:

 

 

Figure: Data Validation Settings

 

This way, you can apply desired data validations using Microsoft Excel.

 

Using Aspose.Cells

Data validation is a strong feature by Aspose.Cells that helps developers to validate information that is entered in their worksheets. With data validation, developers can provide users with a list of choices, restrict data entries to a specific type or size etc.

In Aspose.Cells, each Worksheet class has a Validations object which represents a collection of Validation objects. To create a validation, we may need to set some properties of the Validation class as follows:

• Type , represents the type of the validation, which may be specified by using one of the pre-defined values in ValidationType enumeration
• Operator , represents the operator to be used in the validation, which may be specified by using one of the pre-defined values in OperatorType enumeration
• Formula1 , represents the value or expression associated with the first part of the data validation
• Formula2 , represents the value or expression associated with the second part of the data validation

After all above properties of the Validation object are configured, developers can use CellArea structure to store information about the range of the cells whose contents will be validated using the created validation.

 

Types of Data Validation

Data Validation allows you to build business rules into each cell so that grossly incorrect entries result in error messages. Business rules are the policies and procedures that govern how business operates. Aspose.Cells supports all the important types of Data Validation based on your business rules and polices. ValidationType enumeration denotes all the validation types as its members and you can choose any based on your requirements.

Following are the members of ValidationType enumeration:

 

Member Name	Description
AnyValue	Denotes a value of any type.
WholeNumber	Denotes validation type for whole numbers.
Decimal	Denotes validation type for decimal numbers.
List	Denotes validation type for drop down list.
Date	Denotes validation type for dates.
Time	Denotes validation type for Time.
TextLength	Denotes validation type for the length of the text.
Custom	Denotes custom validation type.

 

Perform Whole Number Data Validation

 

With this type of validation, you can allow the user enter whole numbers into the related cells within a specified range or crieteria. Following is the example, which shows how to implment WholeNumber ValidationType. In the example, we have created the same Data Validation using Aspose.Cells as we created using Microsoft Excel in the above section.

Example:

[C#]

 

 

//Accessing the Validations collection of the worksheet

Validations validations = workbook.Worksheets[0].Validations;

 

 

//Creating a Validation object

Validation validation = validations[validations.Add()];

 

 

//Setting the validation type to whole number

validation.Type = ValidationType.WholeNumber;

 

 

//Setting the operator for validation to Between

validation.Operator = OperatorType.Between;

 

 

//Setting the minimum value for the validation

validation.Formula1 = "10";

 

 

//Setting the maximum value for the validation

validation.Formula2 = "1000";

 

 

//Applying the validation to a range of cells from A1 to B2 using the

//CellArea structure

CellArea area;

area.StartRow = 0;

area.EndRow = 1;

area.StartColumn = 0;

area.EndColumn = 1;

 

 

//Adding the cell area to Validation

validation.AreaList.Add(area);

 

 

[VB.NET]

 

 

'Accessing the Validations collection of the worksheet

Dim validations As Validations = workbook.Worksheets(0).Validations

 

 

'Creating a Validation object

Dim validation As Validation =  validations(validations.Add(__0__))

 

 

'Setting the validation type to whole number