How to export from database to excel

Exporting data from a database to an Excel file involves retrieving the data from the database and transferring it to an Excel file. To accomplish this, we first load the data from the database into a dataset, which serves as an intermediary container for the data. This allows us to efficiently manage and manipulate the data before exporting it.

First step is to Load the Product table data to data set , for detail of Product table please refer to Database Structure .

Once the data is loaded into the dataset, we proceed to create a new Excel file. This involves initializing the necessary components and establishing a connection with the Excel application. With the Excel file set up, we then proceed to write the data from the dataset into the corresponding cells of the 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 Next

By performing these steps, we ensure that the data from the database is accurately transferred to the Excel file, maintaining the integrity and structure of the original data. This process provides a convenient and reliable means of exporting database data to Excel, facilitating further analysis, reporting, and sharing of information.

Full Source VB.NET
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





Related Topics
  1. How to create an Excel file in VB.NET
  2. How to open or read an existing Excel file in VB.NET
  3. How to read an Excel file using VB.Net
  4. Add new worksheet in Excel file - VB.Net
  1. Delete worksheet from an excel file - VB.Net
  2. How to Format Excel Page in VB.NET
  3. How to insert a Picture in Excel through programing on VB.NET
  4. How to insert a background Picture in Excel through VB.NET
  5. How to create a Chart in Excel in VB.NET
  6. How to export a Chart in Excel as Picture file from VB.NET
  7. How to Excel Chart in VB.NET Picture Box
  8. How to Excel DataBar in VB.NET
  9. How to Excel Data Validation Input Message
  10. Read and Import Excel File into DataSet or DataTable in vb.net
  11. How to insert cell data in an Excel file using OLEDB
  12. How to update cell data in an Excel file using OLEDB
  13. How to export from DataGridView to excel in VB.Net