Results 1 to 3 of 3

Thread: How to export data of a selected columns from database to excel using vb.net?

  1. #1

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    How to export data of a selected columns from database to excel using vb.net?

    I have only one button on my windows form, a button that performs exporting of data from database to excel.
    It works in exporting but all those columns in database have exported. I want to export only those specific/selected/desired columns. I don't want to export all columns. I want also to export selected columns from database to selected columns in excel. For example: column1, column4, and column 6 data of database to be exported to columns: A,C,D of excel. I hope you can help me. Thanks.

    Here is my code:
    Code:
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim dbSource As String
            Dim sql As String
            Dim sqlcon As New OleDb.OleDbConnection
            Dim ds As New DataSet
            Dim dbProvider As String
            Dim i, j As Integer
    
            Dim xlApp As Excel.Application
            Dim xlWorkBook As Excel.Workbook
            Dim xlWorkSheet As Excel.Worksheet
            Dim misValue As Object = System.Reflection.Missing.Value
    
            xlApp = New Excel.Application
            xlWorkBook = xlApp.Workbooks.Add(misValue)
            xlWorkSheet = xlWorkBook.Sheets("sheet1")
    
            dbProvider = "Provider=Microsoft.ACE.OLEDB.15.0;"
            dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"
            dbSource = "Data Source =psipop.accdb;Jet OLEDB:Database Password=cscfo13poppsi; "
            sqlcon.ConnectionString = dbProvider & dbSource
    
            sql = "SELECT * FROM publicationinfo"
            Dim dscmd = New OleDb.OleDbDataAdapter(sql, sqlcon)
            dscmd.Fill(ds)
    
            For i = 0 To ds.Tables(0).Rows.Count - 1
                For j = 0 To ds.Tables(0).Columns.Count - 1
                    xlWorkSheet.Cells(i + 18, j + 1) = ds.Tables(0).Rows(i).Item(j) '+18 here is the rownumber(i) and +1 is the columnnumber(j)
                Next
            Next
    
            xlWorkSheet.SaveAs("C:\Users\Admin\Desktop\vbexcel.xlsx")
            xlWorkBook.Close()
            xlApp.Quit()
    
            releaseObject(xlApp)
            releaseObject(xlWorkBook)
            releaseObject(xlWorkSheet)
    
            sqlcon.Close()
    
            MsgBox("You can find the file C:\Users\Admin\Desktop\vbexcel.xlsx")
        End Sub
    
        Private Sub releaseObject(ByVal obj As Object)
            Try
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
                obj = Nothing
            Catch ex As Exception
                obj = Nothing
            Finally
                GC.Collect()
            End Try
        End Sub

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    Re: How to export data of a selected columns from database to excel using vb.net?

    Quote Originally Posted by ronelpisan View Post
    It works in exporting but all those columns in database have exported. I want to export only those specific/selected/desired columns.
    You are loading the data from the database using this SQL statement:
    Code:
            sql = "SELECT * FROM publicationinfo"
    As you have used "SELECT * FROM ...", that will include all fields.

    To limit the fields, specify the ones you want, eg: "SELECT fieldA, fieldB, fieldC FROM ..."

    If you also want to limit the rows somehow, add a Where clause with appropriate conditions, eg: "...FROM publicationinfo WHERE fieldE = 7 OR fieldG = 'test' "

    I want also to export selected columns from database to selected columns in excel. For example: column1, column4, and column 6 data of database to be exported to columns: A,C,D of excel.
    You are currently picking the Excel column by simply adding 1 to the j variable, if you want to have it different to that you'll need to make some kind of mapping to what you want.

    One way you can do that is by using an array containing the target columns (with their position in the array indicating which field of the dataset should go there), eg:
    Code:
            dscmd.Fill(ds)
    
            Dim columnMapping as Integer() = {1, 3, 4}
            For i = 0 To ds.Tables(0).Rows.Count - 1
                For j = 0 To ds.Tables(0).Columns.Count - 1
                    xlWorkSheet.Cells(i + 18, columnMapping(j)) = ds.Tables(0).Rows(i).Item(j) '+18 here is the rownumber(i) and +1 is the columnnumber(j)
                Next
            Next

  3. #3

    Thread Starter
    Member
    Join Date
    Apr 2019
    Posts
    54

    Resolved Re: How to export data of a selected columns from database to excel using vb.net?

    Thanks..it works.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width