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)});

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();

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();

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();

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();