CODEWITHSUNDEEP
vb.netvb.net-2010
The Newbie
The Newbie

Reputation: 389

Read column from excel and list greatest to lowest?

SO I'm able to read from an Excel file and get the value of the cell I want. What if I want to read from 1 whole column (with row 1 as the title) and list the values from greatest to least?

        Dim MyConnection As System.Data.OleDb.OleDbConnection
        Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
        Dim path As String = "C:\Users\Admin\Documents\Test\Test.xlsx"

        MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;")
        MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyConnection)

        Dim DS = New System.Data.DataSet
        MyCommand.Fill(DS)
        Dim Dt = DS.Tables(0).Rows(10)(1)

        'dataGridView1.DataSource = DS.Tables(0)


        MsgBox(Dt)


        MyConnection.Close()

My Excel file is something like:

ROW 1: NAME | AGE
ROW 2: Admin| 15
ROW 3: User | 16

That code above only gets a single cell value, I'd like to get all the Age value and sort it from highest (16) to lowest (15) and what the name is?

Upvotes: 0

Views: 56

Answers (1)

manabu
manabu

Reputation: 471

Like this?

Dim MyConnection As System.Data.OleDb.OleDbConnection
Dim MyCommand As System.Data.OleDb.OleDbDataAdapter
Dim path As String = "C:\Users\Admin\Documents\Test\Test.xlsx"

MyConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=Excel 12.0;")
MyCommand = New System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$] order by age desc", MyConnection)

Dim DS = New System.Data.DataSet
MyCommand.Fill(DS)

For Each r As DataRow In DS.Tables(0).Rows
    MsgBox(String.Format("{0}:{1}", r.Item("name"), r.Item("age")))
Next

MyConnection.Close()

Upvotes: 1

Related Questions