Sort Excel Data by Using VB.NET Easily and Quickly

Data Sorting is one of the most frequently used functions in Excel. It focuses on adjusting order to make data information to be assigned more regularly. After sorting data, users can analyze and calculate data more conveniently.

In Excel, users can sort data by text in alphabetical order (A to Z or Z to A), by numbers from highest to lowest or lowest to highest. Also, users can sort data according to cell format, such as background color, font color etc.

In this post, I will share a method to sort data by using VB.NET. I prepare an Excel file which saves some vendors information. I want to sort the State column by ascending order. The following screenshot shows the original order.

I use a .NET Excel component, Spire.XLS for .NET in my example for realizing this function more easily and quickly. Therefore, its dll file has been added as reference in my project.

STEPS:

Firstly, I use workbook.LoadFromFile(file name string) method to load the Excel file I want to sort data. Then, initialize the worksheet which data is saved in.

Secondly, use workbook.DataSorter.SortColumns.Add(key, OrderBy type) method to confirm which column to sort and order type. Then, use workbook.DataSorter.Sort(data range) method to confirm data range to sort.

Thirdly, save the Excel file by using workbook.SaveToFile(file name string, ExcelVersion) method. Finally, launch it to get result.

CODE:

Imports Spire.Xls

 

Module Sorting

    Sub Main()

        ‘Load Document

        Dim workbook As New Workbook()

        workbook.LoadFromFile(“E:\Work\Documents\ExcelFiles\VendorInfo.xlsx”, ExcelVersion.Version2010)

        Dim sheet As Worksheet = workbook.Worksheets(0)

 

        ‘Sort Data

        workbook.DataSorter.SortColumns.Add(4, OrderBy.Ascending)

        workbook.DataSorter.Sort(sheet(“E1:E20”))

 

        ‘Save and Launch

        workbook.SaveToFile(“SortData.xlsx”, ExcelVersion.Version2010)

        System.Diagnostics.Process.Start(“SortData.xlsx”)

    End Sub

End Module

RESULT

 

DOWNLOAD Spire.XLS for .NET Here

Advertisements

One thought on “Sort Excel Data by Using VB.NET Easily and Quickly

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s