Allow Sort

Spread.Services Documentation
Allow Sort
Collapse All Expand All

Spread.Services Documentation > Developer's Guide > Customize User Interaction > Allow Sort

Spread.Services provides the Sort method to perform data sorting based on a range of cells, range by value, color or icon in a worksheet. The Apply method is used to apply the selected sort state and display the results.

| Note: Sorting can be performed on merged cells as well, provided merged cells have the same size.

Following are the types of sorting available in Spread.Services.

### Sort by value

Sort by value performs sorting to arrange the data in order. SortOrientation property is used to specify the orientation category for sorting, that is, columns or rows.
Refer to the following code example to sort by value.

C# | Copy Code
--- | ---

    //Sort by value, use Sort() method.
    worksheet.Range["A1:B4"].Sort(worksheet.Range["A1:A4"], orientation: SortOrientation.Columns);

### Sort by value for multiple columns

Sort by value for multiple columns performs sorting on multiple columns using a single line of code. ValueSortField method is used to define multiple sort field instances in one statement. SortOrder property is used to specify the orientation of columns in either ascending order or descending order.
Refer to the following code example to sort by value for multiple columns.

C# | Copy Code
--- | ---

    //Sort by value, multi column sort.use Sort() method.
    worksheet.Range["A1:B4"].Sort(SortOrientation.Columns, false, new ValueSortField[] { new ValueSortField(worksheet.Range["A1:A4"],SortOrder.Descending), new ValueSortField(worksheet.Range["B1:B4"], SortOrder.Ascending)});

### Custom sort

Sorting is a common task, but not all data conforms to the common ascending and descending rule. For example, months cannot be sorted in a meaningful way when sorted alphabetically. In this case, Spread.Services offers a custom sort. For custom sorting, string of values are defined in ValueSortField constructor.
Refer to the following code example to implement custom sorting.

C# | Copy Code
--- | ---

    //give a custom sort values string.
    var sortkey = new ValueSortField(worksheet.Range["A1:A2"], "1,2,3");
    worksheet.Range["A2:A6"].Sort(SortOrientation.Columns, false, sortkey);

### Sort by interior

Sort by interior performs sorting on the basis of interior color, pattern, pattern color, gradient color and gradient angle. However, interior sort cannot be performed on the basis of cell color.
Refer to the following code example to sort by interior.

C# | Copy Code
--- | ---

    // Assigning pattern to the range
     worksheet.Range["A3"].Interior.Pattern = Pattern.LinearGradient;
     worksheet.Range["A4"].Interior.Pattern = Pattern.LinearGradient;
     worksheet.Range["A5"].Interior.Pattern = Pattern.LinearGradient;
     worksheet.Range["A6"].Interior.Pattern = Pattern.LinearGradient;
    // Defining values to the range
     worksheet.Range["A3"].Value = 1;
     worksheet.Range["A4"].Value = 2;
     worksheet.Range["A5"].Value = 3;
     worksheet.Range["A6"].Value = 4;
    // Assigning gradient to the range
    ( worksheet.Range["A3"].Interior.Gradient as ILinearGradient).ColorStops[0].Color = Color.FromRGB(255, 0, 0);
    ( worksheet.Range["A3"].Interior.Gradient as ILinearGradient).ColorStops[1].Color = Color.FromRGB(146, 208, 80);
    ( worksheet.Range["A3"].Interior.Gradient as ILinearGradient).Degree = 90;

    ( worksheet.Range["A4"].Interior.Gradient as ILinearGradient).ColorStops[0].Color = Color.FromRGB(255, 0, 255);
    ( worksheet.Range["A4"].Interior.Gradient as ILinearGradient).ColorStops[1].Color = Color.FromRGB(146, 208, 90);
    ( worksheet.Range["A4"].Interior.Gradient as ILinearGradient).Degree = 90;

    ( worksheet.Range["A5"].Interior.Gradient as ILinearGradient).ColorStops[0].Color = Color.FromRGB(255, 0, 255);
    ( worksheet.Range["A5"].Interior.Gradient as ILinearGradient).ColorStops[1].Color = Color.FromRGB(146, 208, 180);
    ( worksheet.Range["A5"].Interior.Gradient as ILinearGradient).Degree = 90;

    ( worksheet.Range["A6"].Interior.Gradient as ILinearGradient).ColorStops[0].Color = Color.FromRGB(255, 0, 255);
    ( worksheet.Range["A6"].Interior.Gradient as ILinearGradient).ColorStops[1].Color = Color.FromRGB(146, 208, 90);
    ( worksheet.Range["A6"].Interior.Gradient as ILinearGradient).Degree = 90;
    //
     worksheet.Sort.SortFields.Add(new CellColorSortField(worksheet.Range["A1:A2"],  worksheet.Range["A6"].DisplayFormat.Interior, SortOrder.Ascending));
     worksheet.Sort.Range =  worksheet.Range["A3:A6"];
     worksheet.Sort.Orientation = SortOrientation.Columns;
     worksheet.Sort.Apply();

### Sort by font color

Sort by font color performs sorting by cell's display format font color. However, sorting is not performed on the basis of cell color.
Refer to the following code example to sort by font color.

C# | Copy Code
--- | ---

    // Assigning Value to the range
     worksheet.Range["A1"].Value = 2;
     worksheet.Range["A2"].Value = 1;
     worksheet.Range["A3"].Value = 1;
     worksheet.Range["A4"].Value = 3;

     worksheet.Range["B1"].Value = 2;
     worksheet.Range["B2"].Value = 1;
     worksheet.Range["B3"].Value = 1;
     worksheet.Range["B4"].Value = 3;
    // Assigning Color to the range
     worksheet.Range["B1"].Font.Color = Color.FromRGB(0, 128, 0);
     worksheet.Range["B2"].Font.Color = Color.FromRGB(128, 0, 0);
     worksheet.Range["B3"].Font.Color = Color.FromRGB(0, 0, 128);
     worksheet.Range["B4"].Font.Color = Color.FromRGB(128, 128, 0);
    // Defining Sort by Color
     worksheet.Sort.SortFields.Add(new FontColorSortField(worksheet.Range["B1:B4"],  worksheet.Range["B1"].DisplayFormat.Font.Color, SortOrder.Descending));
     worksheet.Sort.Range =  worksheet.Range["A1:B4"];
     worksheet.Sort.Orientation = SortOrientation.Columns;
     worksheet.Sort.Apply();

### Sort by Icon

Sort by icon performs sorting on the basis of cell's conditional format icons.
Refer to the following code example to sort by icon.

C# | Copy Code
--- | ---

    // Assigning Value to the range
     worksheet.Range["A1"].Value = 2;
     worksheet.Range["A2"].Value = 1;
     worksheet.Range["A3"].Value = 1;
     worksheet.Range["A4"].Value = 3;

     worksheet.Range["B1"].Value = 2;
     worksheet.Range["B2"].Value = 1;
     worksheet.Range["B3"].Value = 1;
     worksheet.Range["B4"].Value = 3;
    // Defining Sort by Icon
    IIconSetCondition iconset =  worksheet.Range["B1:B4"].FormatConditions.AddIconSetCondition();
    iconset.IconSet =  workbook.IconSets[IconSetType.Icon3TrafficLights1];

     worksheet.Sort.SortFields.Add(new IconSortField(worksheet.Range["B1:B4"],  workbook.IconSets[IconSetType.Icon3TrafficLights1][0], SortOrder.Descending));
     worksheet.Sort.Range =  worksheet.Range["A1:B4"];
     worksheet.Sort.Orientation = SortOrientation.Columns;
     worksheet.Sort.Apply();

See Also
Apply Table Sort

Copyright © GrapeCity, inc. All rights reserved.