In this article we can see how to export from database to excel. We can load the data from a database to dataset and then create a new Excel file and write the data into the Excel file.
First step is to Load the Product table data to data set, next is to create a new Excel file and write the data from dataset to Excel file.
For i = 0 to ds.Tables (0).Rows.Count - 1 For j = 0 to ds.Tables (0).Columns.Count - 1 xlWorkSheet.Cells (i + 1, j + 1) = _ ds.Tables (0).Rows (i).Item (j) Next
Imports System.Data Imports System.Data.SqlClient Imports Excel = Microsoft.Office.Interop.Excel Public Class Form1 Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click Dim cnn As SqlConnection Dim connectionString As String Dim sql 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.ApplicationClass xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkSheet = xlWorkBook.Sheets("sheet1") connectionString = "data source=servername;" & _ "initial catalog=databasename;user id=username;password=password;" cnn = New SqlConnection(connectionString) cnn.Open() sql = "SELECT * FROM Product" Dim dscmd As New SqlDataAdapter(sql, cnn) Dim ds As New DataSet 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 + 1, j + 1) = _ ds.Tables(0).Rows(i).Item(j) Next Next xlWorkSheet.SaveAs("C:\vbexcel.xlsx") xlWorkBook.Close() xlApp.Quit() releaseObject(xlApp) releaseObject(xlWorkBook) releaseObject(xlWorkSheet) cnn.Close() MsgBox("You can find the file C:\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 End Class
3. Girls wanted, no matter where you live! - to high paid job.
If you are young and daring women
View Tutorial By: M_Coleman at 2017-04-03 06:39:46
4. Hi Sir Can you help me, I tried your code and i worked well! but when i'm exporting a large number o
View Tutorial By: Carl at 2016-11-14 05:37:31
5. I have an error. It says that cannot find the column 21. 1 more tan the last one. How can i add a br
View Tutorial By: Alan at 2016-02-29 20:40:25
thanks for nice tutorial. I have one application, installed on client machine . when i
View Tutorial By: Chanchal Prajapat at 2015-02-09 06:10:42
7. Thanks For uploading , Can you please guide , How can i create multiple excel sheet on single button
View Tutorial By: Manish Choudhari at 2014-10-14 09:58:05
8. you do the same as after
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
View Tutorial By: John McCode at 2013-05-24 07:57:31
9. you do the same as after.
Private Sub Button1_Click(ByVal sender As System.Object, By
View Tutorial By: John McCode at 2013-05-24 07:56:04
10. Your script works beautifully. Is there a way to include the header on the first row of the workshee
View Tutorial By: JW at 2013-05-08 22:22:44
11. I was trying to copy your coding and execute it, but no data was extracted out in excel.
View Tutorial By: Guest at 2012-01-02 04:08:21
12. Imports Excel = Microsoft.Office.Interop.Excel
shows error. Which is the dll file to add as r
View Tutorial By: Sinu.S at 2010-10-09 03:55:38
Most Viewed Articles (in VB.net )
Latest Articles (in VB.net)
Comment on this tutorial
- Data Science
- Cloud Computing
- Java Beans
- Mac OS X
- Office 365
- Tech Reviews