Interface ISortField
Represents the sort field based on which sorting is performed.
Namespace: Syncfusion.XlsIO
Assembly: Syncfusion.XlsIO.Portable.dll
Syntax
public interface ISortField
Properties
Color
Represents the color to sort. Throws exception when SortOn type is Values.
Declaration
Color Color { get; set; }
Property Value
Type |
---|
Color |
Remarks
To know more about sorting refer Data Sorting.
Examples
If SortOn property is set CellColor or FontColor, then the Color property should be set. Here for example, we set green from System.Drawing.Color to the Color property to sort the cells based on CellColor.
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";
//Set colors
worksheet["C2:C3"].CellStyle.Color = System.Drawing.Color.Red;
worksheet["C4:C5"].CellStyle.Color = System.Drawing.Color.Blue;
worksheet["C6:C7"].CellStyle.Color = System.Drawing.Color.Green;
//Create sorter
IDataSort sorter = workbook.CreateDataSorter();
//Set sort range
sorter.SortRange = worksheet["A1:C7"];
//Add sortfield
ISortField sortField = sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending);
//Set sortfield properties
sortField.SortOn = SortOn.CellColor;
sortField.Order = OrderBy.Descending;
sortField.Key = 2;
sortField.Color = System.Drawing.Color.Green;
//Perform sort
sorter.Sort();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Datasort.xlsx");
workbook.Close();
}
Key
Represents the column to be sorted on.
Declaration
int Key { get; set; }
Property Value
Type |
---|
System.Int32 |
Examples
The following code illustrates how the the column to be sorted can be set using the Key property. Here for example, we set 2 to the Key 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";
//Set colors
worksheet["C2:C3"].CellStyle.Color = System.Drawing.Color.Red;
worksheet["C4:C5"].CellStyle.Color = System.Drawing.Color.Blue;
worksheet["C6:C7"].CellStyle.Color = System.Drawing.Color.Green;
//Create sorter
IDataSort sorter = workbook.CreateDataSorter();
//Set sort range
sorter.SortRange = worksheet["A1:C7"];
//Add sortfield
ISortField sortField = sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending);
//Set sortfield properties
sortField.SortOn = SortOn.CellColor;
sortField.Order = OrderBy.Descending;
sortField.Key = 2;
sortField.Color = System.Drawing.Color.Green;
//Perform sort
sorter.Sort();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Datasort.xlsx");
workbook.Close();
}
Order
Represents the sort order.
Declaration
OrderBy Order { get; set; }
Property Value
Type |
---|
OrderBy |
Remarks
To know more about sorting refer Data Sorting.
Examples
Value set to Order property determines the sorting order. OnTop and OnBottom are only applicable if SortOn is set to CellColor or FontColor. We can either use Ascending or Descending if SortOn is set Values.
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";
//Set colors
worksheet["C2:C3"].CellStyle.Color = System.Drawing.Color.Red;
worksheet["C4:C5"].CellStyle.Color = System.Drawing.Color.Blue;
worksheet["C6:C7"].CellStyle.Color = System.Drawing.Color.Green;
//Create sorter
IDataSort sorter = workbook.CreateDataSorter();
//Set sort range
sorter.SortRange = worksheet["A1:C7"];
//Add sortfield
ISortField sortField = sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending);
//Set sortfield properties
sortField.SortOn = SortOn.CellColor;
sortField.Order = OrderBy.Descending;
sortField.Key = 2;
sortField.Color = System.Drawing.Color.Green;
//Perform sort
sorter.Sort();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Datasort.xlsx");
workbook.Close();
}
SortOn
Represents the sort by in the range.
Declaration
SortOn SortOn { get; set; }
Property Value
Type |
---|
SortOn |
Remarks
To know more about sorting refer Data Sorting.
Examples
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";
//Set colors
worksheet["C2:C3"].CellStyle.Color = System.Drawing.Color.Red;
worksheet["C4:C5"].CellStyle.Color = System.Drawing.Color.Blue;
worksheet["C6:C7"].CellStyle.Color = System.Drawing.Color.Green;
//Create sorter
IDataSort sorter = workbook.CreateDataSorter();
//Set sort range
sorter.SortRange = worksheet["A1:C7"];
//Add sortfield
ISortField sortField = sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending);
//Set sortfield properties
sortField.SortOn = SortOn.CellColor;
sortField.Order = OrderBy.Descending;
sortField.Key = 2;
sortField.Color = System.Drawing.Color.Green;
//Perform sort
sorter.Sort();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Datasort.xlsx");
workbook.Close();
}
Methods
SetPriority(Int32)
Sets sorting priority.
Declaration
void SetPriority(int priority)
Parameters
Type | Name | Description |
---|---|---|
System.Int32 | priority | integer priority value. 0 represents high priority. |
Remarks
To know more about sorting refer Data Sorting.
Examples
If more than one ISortField is used then we can set priority for a particular ISortField. The following code illustrates how priority can be set for a ISortField.
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";
//Set colors
worksheet["C2:C3"].CellStyle.Color = System.Drawing.Color.Red;
worksheet["C4:C5"].CellStyle.Color = System.Drawing.Color.Blue;
worksheet["C6:C7"].CellStyle.Color = System.Drawing.Color.Green;
//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);
ISortField sortField2 = sorter.SortFields.Add(2, SortOn.CellColor, OrderBy.Descending);
//Set sortfiled color
sortField2.Color = System.Drawing.Color.Green;
//Set priority
sortField2.SetPriority(0);
//Perform sort
sorter.Sort();
//Apply AutoFit
worksheet.UsedRange.AutofitColumns();
//Save and Dispose
workbook.SaveAs("Datasort.xlsx");
workbook.Close();
}