Infragistics logo
메뉴
.NET and JavaScript UI Controls
Version

Sorting Tables (Infragistics Excel Engine)

Topic Overview

Purpose

This topic describes how to sort columns in tables. Columns in a table can be sorted by applying a sort condition to the WorksheetTableColumn's SortCondition property.

In this topic

This topic contains the following sections:

Introduction

Sorting is done by setting a sorting condition on a table column. When a sorting condition is set on a column, all sort conditions in the table will be reevaluated to determine the order of the cells in the table. When cells need to be moved to meet their sort criteria, the entire row of cells in the table is moved as a unit. If the data in the table is subsequently changed, the sort condition will not be reevaluated.

The sort conditions in a table are only reapplied when sort conditions are added, removed, modified, or when the ReapplySortConditions method is called on the table.

When sorting conditions are reevaluated, only the visible cells are sorted. All cells in hidden rows are kept in place.

In addition to accessing sort conditions from the table columns, they are also exposed off the WorksheetTable.SortSettings SortConditions collection. This is an ordered collection of columns/sort condition pairs. The order of this collection is the precedence of the sorting. The first sort condition in the collection will be applied first, sorting all rows in the table by the values of the cells in the column sorted by that condition. If any groups of cells in that sorting have the same values, their rows are then sorted as a sub-group with the next sort condition, and so on.

The following sort condition types are available to set on columns:

Sort condition type Description

Sort cells in an ascending or descending order based on their value.

Sort cells in a defined order based on their text or display value. This might be useful for sorting as they appear on the calendar or as they appear in a custom list defined by you, rather than alphabetically.

Sort cells based on whether their fill is a specific pattern / gradient.

Sort cells based on whether their font is a specific color.

There is also a WorksheetTable.SortSettings CaseSensitive property which allows the developer to determine whether strings should be sorted case sensitively or not.

Code Examples Overview

Overview

The following table lists the code examples included in this topic.

Example Description

Ordered Sort Condition

This code example shows how to sort cells from a table in an ascending or descending order based on their value.

Custom List Sort Condition

This code example shows how to sort cells in a defined order based on their value.

Fill Sort Condition

This code example shows how to sort cells based on their background color.

Code Example: Ordered Sort Conditions

Description

This code example shows how to sort cells from a table in an ascending order based on their value.

The code gets a named table using a helper class ExcelExampleTable. After that an ordered sort condition is created and applied to a column in a named table. Finally, the workbook with the sorted table is saved.

Prerequisites

This example uses a code snippet for programmatically creating a workbook. In the workbook a Patients named table is used.

Code

In Visual Basic:

Dim workbook As Infragistics.Documents.Excel.Workbook = ExcelExampleTable.CreateExampleWorkbook()
' Get a table.
Dim table As Infragistics.Documents.Excel.WorksheetTable = workbook.GetTable("Patients")
' Create Sort Condition
Dim orderedSort As Infragistics.Documents.Excel.Sorting.SortCondition = New Infragistics.Documents.Excel.Sorting.OrderedSortCondition(Infragistics.Documents.Excel.Sorting.SortDirection.Ascending)
' Perform the sort
table.Columns("FirstName").SortCondition = orderedSort
' Save the workbook.
workbook.Save("C:\TableSorting-Sorted.xlsx")

In C#:

Infragistics.Documents.Excel.Workbook workbook = ExcelExampleTable.CreateExampleWorkbook();
// Get a table.
Infragistics.Documents.Excel.WorksheetTable table = workbook.GetTable("Patients");
// Create Sort Condition
Infragistics.Documents.Excel.Sorting.SortCondition orderedSort = new Infragistics.Documents.Excel.Sorting.OrderedSortCondition(
    Infragistics.Documents.Excel.Sorting.SortDirection.Ascending);
// Perform the sort
table.Columns["FirstName"].SortCondition = orderedSort;
// Save the workbook.
workbook.Save("C:\\TableSorting-Sorted.xlsx");

Code Example: Custom List Sort Conditions

Description

This code example shows how to sort cells from a table in a defined order based on their value.

The code gets a named table using a helper class ExcelExampleTable. After that, a custom sort condition is created and applied to a column in the named table. Finally, the workbook with the sorted table is saved.

Prerequisites

This example uses a code snippet for programmatically creating a workbook. In the workbook a Patients named table is used.

Preview

The following screenshot is a preview of the final result.

Sorting Exported Tables 1.png

Code

In Visual Basic:

Dim workbook As Infragistics.Documents.Excel.Workbook = ExcelExampleTable.CreateExampleWorkbook()
' Get a table.
Dim table As Infragistics.Documents.Excel.WorksheetTable = workbook.GetTable("Patients")
' Create custom list sort condition
Dim customListSortCondition As New Infragistics.Documents.Excel.Sorting.CustomListSortCondition(Infragistics.Documents.Excel.Sorting.SortDirection.Ascending, New String() {"High", "Medium", "Low"})
' Perform the sort
table.Columns("Severity").SortCondition = customListSortCondition
workbook.Save("C:\TableSorting-Sorted.xlsx")

In C#:

Infragistics.Documents.Excel.Workbook workbook = ExcelExampleTable.CreateExampleWorkbook();
// Get a table.
Infragistics.Documents.Excel.WorksheetTable table = workbook.GetTable("Patients");
// Create custom list sort condition
Infragistics.Documents.Excel.Sorting.CustomListSortCondition customListSortCondition = new Infragistics.Documents.Excel.Sorting.CustomListSortCondition(
    Infragistics.Documents.Excel.Sorting.SortDirection.Ascending, new string[] { "High", "Medium", "Low" });
// Perform the sort
table.Columns["Severity"].SortCondition = customListSortCondition;
workbook.Save("C:\\TableSorting-Sorted.xlsx");

Code Example: Fill Sort Condition

Description

This code example shows how to sort cells based on their background color.

The code gets a named table using a helper class ExcelExampleTable. After that, a fill sort condition is created and applied to a column in the named table. Finally, the workbook with the sorted table is saved.

Prerequisites

This example uses a code snippet for programmatically creating a workbook. In the workbook a Patients named table is used.

Preview

The following screenshot is a preview of the final result.

Sorting Exported Tables 2.png

Code

In Visual Basic:

Dim myWorkbook As Infragistics.Documents.Excel.Workbook = ExcelExampleTable.CreateExampleWorkbook()
' Get a table. Table is created in separate code snippet.
Dim table As Infragistics.Documents.Excel.WorksheetTable = myWorkbook.GetTable("Patients")
Dim lastRow As Integer = table.DataAreaRegion.LastRow
Dim rand As New Random()
' Set Red background for random cells from first column
Dim i As Integer = 1
While i < lastRow
    If rand.[Next](2) = 1 Then
        myWorkbook.Worksheets(0).Rows(i).Cells(0).CellFormat.Fill = Infragistics.Documents.Excel.CellFill.CreateSolidFill(System.Drawing.Color.FromArgb(255, 255, 0, 0))
    End If
    i += 1
End While
' Create Background Color Sort Condition
Dim filterFill As Infragistics.Documents.Excel.CellFill = Infragistics.Documents.Excel.CellFill.CreateSolidFill(System.Drawing.Color.FromArgb(255, 255, 0, 0))
Dim fillSortCondition As New Infragistics.Documents.Excel.Sorting.FillSortCondition(filterFill, Infragistics.Documents.Excel.Sorting.SortDirection.Ascending)
table.Columns(0).SortCondition = fillSortCondition
myWorkbook.Save("C:\TableSorting-Sorted.xlsx")

In C#:

Infragistics.Documents.Excel.Workbook myWorkbook = ExcelExampleTable.CreateExampleWorkbook();
// Get a table. Table is created in separate code snippet.
Infragistics.Documents.Excel.WorksheetTable table = myWorkbook.GetTable("Patients");
int lastRow = table.DataAreaRegion.LastRow;
Random rand = new Random();
// Set Red background for random cells from first column
for (int i = 1; i < lastRow; i++)
{
    if (rand.Next(2) == 1)
    {
        myWorkbook.Worksheets[0].Rows[i].Cells[0].CellFormat.Fill = Infragistics.Documents.Excel.CellFill.CreateSolidFill(
        System.Drawing.Color.FromArgb(255, 255, 0, 0));
    }
}
// Create Background Color Sort Condition
Infragistics.Documents.Excel.CellFill filterFill = Infragistics.Documents.Excel.CellFill.CreateSolidFill(
    System.Drawing.Color.FromArgb(255, 255, 0, 0));
Infragistics.Documents.Excel.Sorting.FillSortCondition fillSortCondition = new Infragistics.Documents.Excel.Sorting.FillSortCondition(
   filterFill, Infragistics.Documents.Excel.Sorting.SortDirection.Ascending);
table.Columns[0].SortCondition = fillSortCondition;
myWorkbook.Save("C:\\TableSorting-Sorted.xlsx");

Related Content

Topics

The following topics provide additional information related to this topic.

Topic Purpose

This topic describes table support in Infragistics Excel Engine. A table in Microsoft Excel® is a named rectangular region of cells which contain related data organized into columns.

In this section you’ll find information that will help you to better understand the object model and the functionalities of the Infragistics Excel Engine.