Excel Sorting

Following example shows how to sort values in a specific cell range of a worksheet.

Sorted cells are written to all output file formats. Active SortState is written only to XLSX file format.

Screenshot

Sorting Screenshot

See the full code below, use RUN EXAMPLE to execute. 

1using System;
2using System.Collections.Generic;
3using System.Text;
4using GemBox.Spreadsheet;
5using GemBox.Spreadsheet.ConditionalFormatting;
6using GemBox.Spreadsheet.PivotTables;
7
8class Sample
9{
10    [STAThread]
11    static void Main(string[] args)
12    {
13        // If using Professional version, put your serial key below.
14        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
15
16        ExcelFile ef = new ExcelFile();
17        ExcelWorksheet ws = ef.Worksheets.Add("Sorting");
18
19        Random rnd = new Random();
20
21        ws.Cells[0, 0].Value = "Sorted numbers";
22        ws.Cells.GetSubrangeAbsolute(0, 0, 0, 1).Merged = true;
23        for (int i = 1; i < 10; i++)
24            ws.Cells[i, 0].SetValue(rnd.Next(1, 100));
25
26        ws.Cells.GetSubrangeAbsolute(1, 0, 10, 0).Sort(false).By(0).Apply();
27
28        ws.Cells[0, 2].Value = "Sorted strings";
29        ws.Cells.GetSubrangeAbsolute(0, 2, 0, 3).Merged = true;
30        ws.Cells[1, 2].Value = "John";
31        ws.Cells[2, 2].Value = "Jennifer";
32        ws.Cells[3, 2].Value = "Toby";
33        ws.Cells[4, 2].Value = "Chloe";
34
35        ws.Cells.GetSubrangeAbsolute(1, 2, 4, 2).Sort(false).By(0).Apply();
36
37        ws.Cells[0, 4].Value = "Sorted by column E and after that by column F";
38        ws.Cells.GetSubrangeAbsolute(0, 4, 0, 8).Merged = true;
39        for (int i = 1; i < 10; i++)
40        {
41            ws.Cells[i, 4].SetValue(rnd.Next(1, 4));
42            ws.Cells[i, 5].SetValue(rnd.Next(0, 10));
43        }
44
45        // Sort by column E ascending and then by column F descending.
46        // These sort settings will be saved to output XLSX file because they are active (parameter value is true).
47        ws.Cells.GetSubrangeAbsolute(1, 4, 10, 5).Sort(true).By(0).By(1, true).Apply();
48
49        ef.Save("Sorting.xlsx");
50    }
51}

1Imports System
2Imports System.Collections.Generic
3Imports System.Text
4Imports GemBox.Spreadsheet
5Imports GemBox.Spreadsheet.ConditionalFormatting
6Imports GemBox.Spreadsheet.PivotTables
7
8Module Samples
9
10    Sub Main()
11
12        ' If using Professional version, put your serial key below.
13        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
14
15        Dim ef As ExcelFile = New ExcelFile
16        Dim ws As ExcelWorksheet = ef.Worksheets.Add("Sorting")
17
18        Dim rnd = New Random()
19
20        ws.Cells(0, 0).Value = "Sorted numbers"
21        For i = 1 To 10 Step 1
22            ws.Cells(i, 0).SetValue(rnd.Next(1, 100))
23        Next
24
25        ws.Cells.GetSubrangeAbsolute(1, 0, 10, 0).Sort(False).By(0).Apply()
26
27        ws.Cells(0, 2).Value = "Sorted strings"
28        ws.Cells(1, 2).Value = "John"
29        ws.Cells(2, 2).Value = "Jennifer"
30        ws.Cells(3, 2).Value = "Toby"
31        ws.Cells(4, 2).Value = "Chloe"
32
33        ws.Cells.GetSubrangeAbsolute(1, 2, 4, 2).Sort(False).By(0).Apply()
34
35        ws.Cells(0, 4).Value = "Sorted by column E and after that by column F"
36        For i = 1 To 10 Step 1
37            ws.Cells(i, 4).SetValue(rnd.Next(1, 4))
38            ws.Cells(i, 5).SetValue(rnd.Next(0, 10))
39        Next
40
41        ' Sort by column E ascending and then by column F descending.
42        ' These sort settings will be saved to output XLSX file because they are active (parameter value is True).
43        ws.Cells.GetSubrangeAbsolute(1, 4, 10, 5).Sort(True).By(0).By(1, True).Apply()
44
45        ef.Save("Sorting.xlsx")
46
47    End Sub
48
49End Module

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.