GcExcel .NET Overview
Key Features
Getting Started
Features
- Worksheet
- Workbook
- Comments
- Hyperlinks
- Sort
- Filter
- Group
- Conditional Formatting
- Data Validations
- Data Binding
- Digital Signatures
- Formulas
- Custom Functions
- Shapes and Pictures
- Document Properties
- Styles
- Form Controls
- Barcodes
- Theme
- Chart
- Table
- Pivot Table
- Pivot Chart
- Sparkline
- Slicer
- Print
- Logging
- Defined Names
Templates
File Operations
Release Notes

Sort

GcExcel 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 GcExcel.

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.

//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.

//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, GcExcel 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.

//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.

// 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.FromArgb(255, 0, 0);
( worksheet.Range["A3"].Interior.Gradient as ILinearGradient).ColorStops[1].Color = Color.FromArgb(146, 208, 80);
( worksheet.Range["A3"].Interior.Gradient as ILinearGradient).Degree = 90;

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

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

( worksheet.Range["A6"].Interior.Gradient as ILinearGradient).ColorStops[0].Color = Color.FromArgb(255, 0, 255);
( worksheet.Range["A6"].Interior.Gradient as ILinearGradient).ColorStops[1].Color = Color.FromArgb(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.

// 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.FromArgb(0, 128, 0);
 worksheet.Range["B2"].Font.Color = Color.FromArgb(128, 0, 0);
 worksheet.Range["B3"].Font.Color = Color.FromArgb(0, 0, 128);
 worksheet.Range["B4"].Font.Color = Color.FromArgb(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.

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