|
-
May 15th, 2019, 12:25 AM
#1
Thread Starter
Member
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
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|