Interface IDataSort

Represents the sort of range.

Namespace: Syncfusion.XlsIO

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