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
Re: How to export data of a selected columns from database to excel using vb.net?
Quote:
Originally Posted by
ronelpisan
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' "
Quote:
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
Re: How to export data of a selected columns from database to excel using vb.net?
