Interface IDataSort
Represents the sort of range.
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.NET.dll
Syntax
public interface IDataSort
Properties
Algorithm
Represents the algorithm to sort.
Declaration
SortingAlgorithms Algorithm { get; set; }
Property Value
Type |
---|
SortingAlgorithms |
Examples
By default the alogrithm to perform sorting is QuickSort. Here for example, we set MergeSort to Algorithm property.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Load data
worksheet["A1"].Text = "Products";
worksheet["A2"].Text = "Alfreds Futterkiste";
worksheet["A3"].Text = "Antonio Moreno Taqueria";
worksheet["A4"].Text = "Around the Horn";
worksheet["A5"].Text = "Bon app";
worksheet["A6"].Text = "Eastern Connection";
worksheet["A7"].Text = "Ernst Handel";
worksheet["B1"].Text = "Qtr1";
worksheet["B2"].Number = 744.6;
worksheet["B3"].Number = 5079.6;
worksheet["B4"].Number = 1267.5;
worksheet["B5"].Number = 1418;
worksheet["B6"].Number = 4728;
worksheet["B7"].Number = 943.89;
worksheet["C1"].Text = "Qtr2";
worksheet["C2"].Number = 162.56;
worksheet["C3"].Number = 1249.2;
worksheet["C4"].Number = 1062.5;
worksheet["C5"].Number = 756;
worksheet["C6"].Number = 4547.92;
worksheet["C7"].Number = 349.6;
//Create style for table number format
IStyle style1 = workbook.Styles.Add("CurrencyFormat");
style1.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \" - \"??_);_(@_)";
//Apply number format
worksheet["B2:C8"].CellStyleName = "CurrencyFormat";
//Create sorter
IDataSort sorter = workbook.CreateDataSorter();
//Set Range
sorter.SortRange = worksheet["A1:C7"];
//Add sortfield
ISortField sortField = sorter.SortFields.Add(1, SortOn.Values, OrderBy.Ascending);
//Set algorithm
sorter.Algorithm = SortingAlgorithms.MergeSort;
//Perform sort
sorter.Sort();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Datasort.xlsx");
workbook.Close();
}
HasHeader
Indicates whether the range has header.
Declaration
bool HasHeader { get; set; }
Property Value
Type |
---|
System.Boolean |
Examples
If HasHeader property is set to "true", then the first row within the sorter range is ignored and the same will be treated as column name. By default HasHeader is set to "true". Here for example, we set HasHeader property to "false". So the sorter sorts all the rows within the given range.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Load data
worksheet["A2"].Text = "banana";
worksheet["A3"].Text = "Cherry";
worksheet["A4"].Text = "Banana";
worksheet["A5"].Text = "Apple";
worksheet["A6"].Text = "cherry";
worksheet["A7"].Text = "apple";
worksheet["B2"].Number = 744.6;
worksheet["B3"].Number = 5079.6;
worksheet["B4"].Number = 1267.5;
worksheet["B5"].Number = 1418;
worksheet["B6"].Number = 4728;
worksheet["B7"].Number = 943.89;
worksheet["C2"].Number = 162.56;
worksheet["C3"].Number = 1249.2;
worksheet["C4"].Number = 1062.5;
worksheet["C5"].Number = 756;
worksheet["C6"].Number = 4547.92;
worksheet["C7"].Number = 349.6;
//Create style for table number format
IStyle style1 = workbook.Styles.Add("CurrencyFormat");
style1.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \" - \"??_);_(@_)";
//Apply number format
worksheet["B2:C8"].CellStyleName = "CurrencyFormat";
//Create sorter
IDataSort sorter = workbook.CreateDataSorter();
//Set Range
sorter.SortRange = worksheet["A2:C7"];
//Add sortfield
ISortField sortField = sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending);
//Set case sensitive
sorter.HasHeader = false;
//Perform sort
sorter.Sort();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Datasort.xlsx");
workbook.Close();
}
IsCaseSensitive
Indicates whether to perform case sensitive sort.
Declaration
bool IsCaseSensitive { get; set; }
Property Value
Type |
---|
System.Boolean |
Examples
By default, the sorter is case insensitive. If similar texts with different casing are to be sorted then the sorter treats both the texts same. But if we set IsCaseSensitive property to "true" then it sorts and arranges texts in lower case to upper case order. Here for example, we set same texts twice with first letters vary in casing, then set IsCaseSensitive to "true" and sort.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Load data
worksheet["A1"].Text = "Fruits";
worksheet["A2"].Text = "banana";
worksheet["A3"].Text = "Cherry";
worksheet["A4"].Text = "Banana";
worksheet["A5"].Text = "Apple";
worksheet["A6"].Text = "cherry";
worksheet["A7"].Text = "apple";
worksheet["B1"].Text = "Qtr1";
worksheet["B2"].Number = 744.6;
worksheet["B3"].Number = 5079.6;
worksheet["B4"].Number = 1267.5;
worksheet["B5"].Number = 1418;
worksheet["B6"].Number = 4728;
worksheet["B7"].Number = 943.89;
worksheet["C1"].Text = "Qtr2";
worksheet["C2"].Number = 162.56;
worksheet["C3"].Number = 1249.2;
worksheet["C4"].Number = 1062.5;
worksheet["C5"].Number = 756;
worksheet["C6"].Number = 4547.92;
worksheet["C7"].Number = 349.6;
//Create style for table number format
IStyle style1 = workbook.Styles.Add("CurrencyFormat");
style1.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \" - \"??_);_(@_)";
//Apply number format
worksheet["B2:C8"].CellStyleName = "CurrencyFormat";
//Create sorter
IDataSort sorter = workbook.CreateDataSorter();
//Set Range
sorter.SortRange = worksheet["A1:C7"];
//Add sortfield
ISortField sortField = sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending);
//Set case sensitive
sorter.IsCaseSensitive = true;
//Perform sort
sorter.Sort();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Datasort.xlsx");
workbook.Close();
}
Orientation
Represents the sort orientation.
Declaration
SortOrientation Orientation { get; set; }
Property Value
Type |
---|
SortOrientation |
Examples
In some cases data in worksheet may be given in left to right format instead of top to bottom. For this case sorter can be used to sort by setting Orientation to LeftToRight. By default it is TopToBottom. Here for example, we load data in rows instead of columns and use the sorter to sort the data.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Load data
worksheet["A1"].Text = "Products";
worksheet["B1"].Text = "Alfreds Futterkiste";
worksheet["C1"].Text = "Antonio Moreno Taqueria";
worksheet["D1"].Text = "Around the Horn";
worksheet["E1"].Text = "Bon app";
worksheet["F1"].Text = "Eastern Connection";
worksheet["G1"].Text = "Ernst Handel";
worksheet["A2"].Text = "Qtr1";
worksheet["B2"].Number = 744.6;
worksheet["C2"].Number = 5079.6;
worksheet["D2"].Number = 1267.5;
worksheet["E2"].Number = 1418;
worksheet["F2"].Number = 4728;
worksheet["G2"].Number = 943.89;
worksheet["A3"].Text = "Qtr2";
worksheet["B3"].Number = 162.56;
worksheet["C3"].Number = 1249.2;
worksheet["D3"].Number = 1062.5;
worksheet["E3"].Number = 756;
worksheet["F3"].Number = 4547.92;
worksheet["G3"].Number = 349.6;
//Create style for table number format
IStyle style1 = workbook.Styles.Add("CurrencyFormat");
style1.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \" - \"??_);_(@_)";
//Apply number format
worksheet["B2:C8"].CellStyleName = "CurrencyFormat";
//Create sorter
IDataSort sorter = workbook.CreateDataSorter();
//Set Range
sorter.SortRange = worksheet["A1:G3"];
//Add sortfield
ISortField sortField = sorter.SortFields.Add(1, SortOn.Values, OrderBy.Ascending);
//Set orientation
sorter.Orientation = SortOrientation.LeftToRight;
//Perform sort
sorter.Sort();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Datasort.xlsx");
workbook.Close();
}
SortFields
Represents the ISortFields Collection.
Declaration
ISortFields SortFields { get; set; }
Property Value
Type |
---|
ISortFields |
Examples
ISortFields is used to add, remove and get ISortField from collection. Here for example, we add ISortField to ISortFields collection.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Load data
worksheet["A1"].Text = "Products";
worksheet["A2"].Text = "Alfreds Futterkiste";
worksheet["A3"].Text = "Antonio Moreno Taqueria";
worksheet["A4"].Text = "Around the Horn";
worksheet["A5"].Text = "Bon app";
worksheet["A6"].Text = "Eastern Connection";
worksheet["A7"].Text = "Ernst Handel";
worksheet["B1"].Text = "Qtr1";
worksheet["B2"].Number = 744.6;
worksheet["B3"].Number = 5079.6;
worksheet["B4"].Number = 1267.5;
worksheet["B5"].Number = 1418;
worksheet["B6"].Number = 4728;
worksheet["B7"].Number = 943.89;
worksheet["C1"].Text = "Qtr2";
worksheet["C2"].Number = 162.56;
worksheet["C3"].Number = 1249.2;
worksheet["C4"].Number = 1062.5;
worksheet["C5"].Number = 756;
worksheet["C6"].Number = 4547.92;
worksheet["C7"].Number = 349.6;
//Create style for number format
IStyle style1 = workbook.Styles.Add("CurrencyFormat");
style1.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \" - \"??_);_(@_)";
//Apply number format
worksheet["B2:C7"].CellStyleName = "CurrencyFormat";
//Create sorter
IDataSort sorter = workbook.CreateDataSorter();
//Set sort range
sorter.SortRange = worksheet["A1:C7"];
//Add sortfield
ISortField sortField = sorter.SortFields.Add(1, SortOn.Values, OrderBy.Ascending);
//Perform sort
sorter.Sort();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Datasort.xlsx");
workbook.Close();
}
SortRange
Represents the sort range.
Declaration
IRange SortRange { get; set; }
Property Value
Type |
---|
IRange |
Examples
The following code illustrates how to set range for sorter.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Load data
worksheet["A1"].Text = "Products";
worksheet["A2"].Text = "Alfreds Futterkiste";
worksheet["A3"].Text = "Antonio Moreno Taqueria";
worksheet["A4"].Text = "Around the Horn";
worksheet["A5"].Text = "Bon app";
worksheet["A6"].Text = "Eastern Connection";
worksheet["A7"].Text = "Ernst Handel";
worksheet["B1"].Text = "Qtr1";
worksheet["B2"].Number = 744.6;
worksheet["B3"].Number = 5079.6;
worksheet["B4"].Number = 1267.5;
worksheet["B5"].Number = 1418;
worksheet["B6"].Number = 4728;
worksheet["B7"].Number = 943.89;
worksheet["C1"].Text = "Qtr2";
worksheet["C2"].Number = 162.56;
worksheet["C3"].Number = 1249.2;
worksheet["C4"].Number = 1062.5;
worksheet["C5"].Number = 756;
worksheet["C6"].Number = 4547.92;
worksheet["C7"].Number = 349.6;
//Create style for number format
IStyle style1 = workbook.Styles.Add("CurrencyFormat");
style1.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \" - \"??_);_(@_)";
//Apply number format
worksheet["B2:C7"].CellStyleName = "CurrencyFormat";
//Create sorter
IDataSort sorter = workbook.CreateDataSorter();
//Set sort range
sorter.SortRange = worksheet["A1:C7"];
//Add sortfield
ISortField sortField = sorter.SortFields.Add(1, SortOn.Values, OrderBy.Ascending);
//Perform sort
sorter.Sort();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Datasort.xlsx");
workbook.Close();
}
Methods
Sort()
Sorts the range based on the sort fields.
Declaration
void Sort()
Examples
To sort the ISortFields in the ISortFields collection sort method is used.
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Create worksheet
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
IWorksheet worksheet = workbook.Worksheets[0];
//Load data
worksheet["A1"].Text = "Products";
worksheet["A2"].Text = "Alfreds Futterkiste";
worksheet["A3"].Text = "Antonio Moreno Taqueria";
worksheet["A4"].Text = "Around the Horn";
worksheet["A5"].Text = "Bon app";
worksheet["A6"].Text = "Eastern Connection";
worksheet["A7"].Text = "Ernst Handel";
worksheet["B1"].Text = "Qtr1";
worksheet["B2"].Number = 744.6;
worksheet["B3"].Number = 5079.6;
worksheet["B4"].Number = 1267.5;
worksheet["B5"].Number = 1418;
worksheet["B6"].Number = 4728;
worksheet["B7"].Number = 943.89;
worksheet["C1"].Text = "Qtr2";
worksheet["C2"].Number = 162.56;
worksheet["C3"].Number = 1249.2;
worksheet["C4"].Number = 1062.5;
worksheet["C5"].Number = 756;
worksheet["C6"].Number = 4547.92;
worksheet["C7"].Number = 349.6;
//Create style for number format
IStyle style1 = workbook.Styles.Add("CurrencyFormat");
style1.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* \" - \"??_);_(@_)";
//Apply number format
worksheet["B2:C7"].CellStyleName = "CurrencyFormat";
//Create sorter
IDataSort sorter = workbook.CreateDataSorter();
//Set sort range
sorter.SortRange = worksheet["A1:C7"];
//Add sortfield
ISortField sortField = sorter.SortFields.Add(1, SortOn.Values, OrderBy.Ascending);
//Perform sort
sorter.Sort();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Datasort.xlsx");
workbook.Close();
}