Interface ISortField

Represents the sort field based on which sorting is performed.

Namespace: Syncfusion.XlsIO

Assembly: Syncfusion.XlsIO.NET.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();
        }