none
sort a table excel following last column RRS feed

  • Question

  • Hello 

    i use vb.net with Excel 

    i have a table in excel file, i want to sort the last column and display two column in datagridview 

    this is my table :

    i want to display this column in datagridview sorted 

    this is the code that give me just the last column in datagrid sorted , i want to add "Cell name " :

        Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
    
            Dim ShtRange As Range
            Dim LastCol As Integer
            Dim LastRow As Integer
            With Form1.xlWorkSheet
                LastCol = Form1.xlWorkSheet.Cells(1, Form1.xlWorkSheet.Columns.Count).End(XlDirection.xlToLeft).Column
                LastRow = Form1.xlWorkSheet.Range("A" & Form1.xlWorkSheet.Rows.Count).End(XlDirection.xlUp).Row
    
                Form1.xlWorkSheet.Range(.Cells(2, LastCol), .Cells(LastRow, LastCol)).Select()
                Form1.xlApp.Selection.sort(.Range(.Cells(2, LastCol), .Cells(LastRow, LastCol)), Excel.XlSortOrder.xlAscending)
    
    
                ShtRange = .Range(.Cells(1, LastCol), .Cells(LastRow, LastCol))
    
                Dim dt As New System.Data.DataTable
    
                For Cnum As Integer = 1 To ShtRange.Columns.Count
                    dt.Columns.Add(New DataColumn(TryCast(ShtRange.Cells(1, Cnum), Excel.Range).Value.ToString()))
                Next
                dt.AcceptChanges()
                Dim columnNames As String() = New String(dt.Columns.Count - 1) {}
                For i As Integer = 0 To dt.Columns.Count - 1
                    columnNames(0) = dt.Columns(i).ColumnName
                Next
                For Rnum As Integer = 2 To ShtRange.Rows.Count
                    Dim dr As DataRow = dt.NewRow()
                    For Cnum As Integer = 1 To ShtRange.Columns.Count
                        If TryCast(ShtRange.Cells(Rnum, Cnum), Excel.Range).Value2 IsNot Nothing Then
                            dr(Cnum - 1) = TryCast(ShtRange.Cells(Rnum, Cnum), Excel.Range).Value2.ToString()
                        End If
                    Next
                    dt.Rows.Add(dr)
                    dt.AcceptChanges()
                Next
                Me.DataGridView1.DataSource = dt
    
               
            End With
        End Sub

     please help me !!
    Tuesday, April 14, 2015 8:24 PM

Answers

  • Hi Tamaratheo,

    The sample above is a demo that get the data using VBA. I also wrote a sample get the data from Excel and bind it to datagridview with VB.Net for your reference:

     Dim excelApp As Excel.Application = New Excel.Application
            Dim aWorkbook As Excel.Workbook = excelApp.Workbooks.Open("D:\Book1.xlsx")
            Dim aRow As Excel.Range
    
            Dim dt As DataTable = New DataTable
            dt.Columns.Add("Cell Name")
            dt.Columns.Add("Formula QOS")
    
            For Each aRow In aWorkbook.Worksheets("Sheet1").UsedRange.Rows
                If aRow.Row <> 1 Then
                    'Debug.Print(aRow.Cells(1, 3) & aRow.Cells(1, 10))
                    'add the code here to build the datatable
                    Dim dr As DataRow = dt.NewRow()
                    dr("Cell Name") = aRow.Cells(1, 3).Value
                    dr("Formula QOS") = aRow.Cells(1, 10).Value
                    dt.Rows.Add(dr)
                End If
            Next aRow
            DataGridView1.DataSource = dt

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by tamaratheo Monday, April 27, 2015 10:13 AM
    Wednesday, April 22, 2015 5:48 AM
    Moderator

All replies

  • Hi tamaratheo,

    >>i have a table in excel file, i want to sort the last column and display two column in datagridview <<

    Based on the code, the data sorted only last column other column woudn't change the order corrsponding to the last column.

    If you want to sort the data based on the last column, we can refere to code below:

    ActiveSheet.UsedRange.Sort Range("J1"), xlAscending, Header:=xlYes
    Then we can get the column we wanted by loop the rows of usedrange and get the target column by cells property. For example, here is a demo that get the third column for your reference:
    For Each aRow In ActiveSheet.UsedRange.Rows
        Debug.Print aRow.Cells(1, 3)
    Next aRow
    Here are some helpful links about sorting and Range useage in Excel:
    Range.Sort Method (Excel)

    Cells and Ranges

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place. <br/> Click <a href="http://support.microsoft.com/common/survey.aspx?showpage=1&scid=sw%3Ben%3B3559&theme=tech"> HERE</a> to participate the survey.

    Thursday, April 16, 2015 2:39 AM
    Moderator
  • HI 

    thanks for your answer 

    i have already sort and add the last colunm in the datagridview this is the code :

      Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
    
            Dim ShtRange As Range
            Dim LastCol As Integer
            Dim LastRow As Integer
    
            With Form1.xlWorkSheet
                LastCol = Form1.xlWorkSheet.Cells(1, Form1.xlWorkSheet.Columns.Count).End(XlDirection.xlToLeft).Column
                LastRow = Form1.xlWorkSheet.Range("A" & Form1.xlWorkSheet.Rows.Count).End(XlDirection.xlUp).Row
    
                Form1.xlWorkSheet.Range(.Cells(2, LastCol), .Cells(LastRow, LastCol)).Select()
                Form1.xlApp.Selection.sort(.Range(.Cells(2, LastCol), .Cells(LastRow, LastCol)), Excel.XlSortOrder.xlAscending)
    
    
    
                ShtRange = .Range(.Cells(1, LastCol), .Cells(LastRow, LastCol))
    
                Dim dt As New System.Data.DataTable
    
                For Cnum As Integer = 1 To ShtRange.Columns.Count
                    dt.Columns.Add(New DataColumn(TryCast(ShtRange.Cells(1, Cnum), Excel.Range).Value.ToString()))
                Next
                dt.AcceptChanges()
                Dim columnNames As String() = New String(dt.Columns.Count - 1) {}
                For i As Integer = 0 To dt.Columns.Count - 1
                    columnNames(0) = dt.Columns(i).ColumnName
                Next
                For Rnum As Integer = 2 To ShtRange.Rows.Count
                    Dim dr As DataRow = dt.NewRow()
                    For Cnum As Integer = 1 To ShtRange.Columns.Count
                        If TryCast(ShtRange.Cells(Rnum, Cnum), Excel.Range).Value2 IsNot Nothing Then
                            dr(Cnum - 1) = TryCast(ShtRange.Cells(Rnum, Cnum), Excel.Range).Value2.ToString()
                        End If
                    Next
                    dt.Rows.Add(dr)
                    dt.AcceptChanges()
                Next
                Me.DataGridView1.DataSource = dt
    
            End With
        End Sub

    just i want to add the column "Cell Name" from my table which is the column C with the last column 

    and as you said : sort based on the last column 

    just like this :

    help me please

    Thursday, April 16, 2015 9:45 AM
  • Hi Tamaratheo,

    >>just i want to add the column "Cell Name" from my table which is the column C with the last column 

    and as you said : sort based on the last column <<

    Based on my understanding, we can create two column data table first and add the column name('cell name', 'formula QOS') directly.

    Then we can use code below to loop the used range get the data we wanted(column 3, column 10) from Excel:

    Sub GetData()
    Dim aRow As Range
     For Each aRow In ActiveSheet.UsedRange.Rows
     If aRow.Row <> 1 Then
        Debug.Print aRow.Cells(1, 3) & aRow.Cells(1, 10)
        'add the code here to build the datatable
      End If
    Next aRow
    End Sub

    Also here is an helpful link for you learning datatable:

    DataTable Class

    If you still have the issue to build data table based on the data, I suggest that you reopen a new thread in ADO.NET DataSet forum. And please feel free to let me know if you have any issue about Excel developing.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 20, 2015 6:09 AM
    Moderator
  • Hi 

    i try the code but i get an error in this line :

       Debug.Print(aRow.Cells(1, 3) & aRow.Cells(1, 10))

    L'opérateur '&' n'est pas défini pour type 'Range' et type 'Range'.

    Tuesday, April 21, 2015 9:29 AM
  • Hi Tamaratheo,

    The sample above is a demo that get the data using VBA. I also wrote a sample get the data from Excel and bind it to datagridview with VB.Net for your reference:

     Dim excelApp As Excel.Application = New Excel.Application
            Dim aWorkbook As Excel.Workbook = excelApp.Workbooks.Open("D:\Book1.xlsx")
            Dim aRow As Excel.Range
    
            Dim dt As DataTable = New DataTable
            dt.Columns.Add("Cell Name")
            dt.Columns.Add("Formula QOS")
    
            For Each aRow In aWorkbook.Worksheets("Sheet1").UsedRange.Rows
                If aRow.Row <> 1 Then
                    'Debug.Print(aRow.Cells(1, 3) & aRow.Cells(1, 10))
                    'add the code here to build the datatable
                    Dim dr As DataRow = dt.NewRow()
                    dr("Cell Name") = aRow.Cells(1, 3).Value
                    dr("Formula QOS") = aRow.Cells(1, 10).Value
                    dt.Rows.Add(dr)
                End If
            Next aRow
            DataGridView1.DataSource = dt

    Hope it is helpful.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by tamaratheo Monday, April 27, 2015 10:13 AM
    Wednesday, April 22, 2015 5:48 AM
    Moderator
  • HI Fei Xue

    thanks a lot for the code it works very well 

    just i want one more modification, the column of "Formule QOS" is not fixed , this column is variable , it is the last column of my table

    i try this but don't work :(

     Dim aRow As Excel.Range
    
                        Dim dt As New System.Data.DataTable
                        Dim LastCol2 As Integer
                        Dim LastRow As Integer
    
    
                        LastCol2 = Chargement_de_données.xlWorkSheet.Cells(1, Chargement_de_données.xlWorkSheet.Columns.Count).End(XlDirection.xlToLeft).Column
                        LastRow = Chargement_de_données.xlWorkSheet.Range("A" & Chargement_de_données.xlWorkSheet.Rows.Count).End(XlDirection.xlUp).Row
    
    
                        dt.Columns.Add("Cell Name")
                        dt.Columns.Add("Formula QOS")
    
                        For Each aRow In Chargement_de_données.xlWorkSheet.UsedRange.Rows
                            If aRow.Row <> 1 Then
                                'Debug.Print(aRow.Cells(1, 3) & aRow.Cells(1, 10))
                                'add the code here to build the datatable
                                Dim dr As DataRow = dt.NewRow()
                                dr("Cell Name") = aRow.Cells(1, 3).Value
                                dr("Formula QOS") = aRow.Cells(.Range(.Cells(1, LastCol2), .Cells(LastRow, LastCol2))).Value
                                dt.Rows.Add(dr)
                            End If
                        Next aRow
                        DataGridView1.DataSource = dt
                          
                       
    
    help me please
     
    Wednesday, April 22, 2015 8:22 AM
  • Hello 

    i have a code but the column"Formula QoS" didn't shown

     Dim ShtRange As Range
                        Dim LastCol5 As Integer
                        Dim LastRow As Integer
    
                        Dim aRow As Excel.Range
    
                        Dim dt As New System.Data.DataTable
    
                        LastCol5 = Chargement_de_données.xlWorkSheet.Cells(1, Chargement_de_données.xlWorkSheet.Columns.Count).End(XlDirection.xlToLeft).Column
                        LastRow = Chargement_de_données.xlWorkSheet.Range("A" & Chargement_de_données.xlWorkSheet.Rows.Count).End(XlDirection.xlUp).Row
    
                        dt.Columns.Add("Cell Name")
                        dt.Columns.Add("Formula QOS")
                        ShtRange = .Range(.Cells(1, LastCol), .Cells(LastRow, LastCol))
    
                        For Each aRow In Chargement_de_données.xlWorkSheet.UsedRange.Rows
                            If aRow.Row <> 1 Then
                                'Debug.Print(aRow.Cells(1, 3) & aRow.Cells(1, 10))
                                'add the code here to build the datatable
                                Dim dr As DataRow = dt.NewRow()
                                dr("Cell Name") = aRow.Cells(1, 3).Value
                                dr("Formula QOS") = ShtRange.Value
                                dt.Rows.Add(dr)
                            End If
                        Next aRow
                        DataGridView1.DataSource = dt
    
                      

    please help me !!!!

    Sunday, April 26, 2015 1:11 PM
  • Hi tamaratheo,

    I have noticed that you have reopen a new thread at the link below:
    https://social.msdn.microsoft.com/Forums/office/en-US/62edd062-0599-4c5a-85d3-b43a68b9123f/add-two-columns-excel-to-datagridview-vbnet?forum=exceldev#62edd062-0599-4c5a-85d3-b43a68b9123f

    And since the orignal issue was fixed, I would suggest colse this thread and follow the new issue in the new thread.

    Thanks for your understanding.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Monday, April 27, 2015 1:51 AM
    Moderator