How to export from DataGridView to excel

To export data from a DataGridView to Excel, the process involves several essential steps. First, establish a connection to the database and retrieve the desired data, subsequently populating the DataGridView with the retrieved information. Then, proceed to create a new Excel file, providing a dedicated space for transferring the data from the DataGridView to the Excel format.

With the data successfully loaded into the DataGridView, you can begin the process of transferring it to the Excel file. This is achieved by carefully extracting the data from the DataGridView and writing it to the corresponding cells within the Excel file. Through this meticulous operation, the information is seamlessly transferred to the Excel format, ensuring its preservation and accessibility for further analysis or sharing.

vb.net_export_datagridview_toexcel.GIF

By following this systematic approach, you can effectively export data from a DataGridView to Excel, facilitating seamless data transfer and fostering a smooth workflow for subsequent data manipulation and utilization.

To begin the process of exporting data from the Product table to an Excel file, the first step is to load the relevant data into a DataGridView. The Database Structure and details of the Product table can be found in the accompanying database schema documentation. By establishing a connection to the database and executing the appropriate query, the data from the Product table can be retrieved and loaded into the DataGridView, providing a visual representation of the table's contents.

Once the data is successfully loaded into the DataGridView, the subsequent step involves creating a new Excel file. This entails creating a blank workbook within the Excel application, which will serve as the destination for transferring the data from the DataGridView.

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 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) DataGridView1.DataSource = ds.Tables(0) cnn.Close() End Sub Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click Dim xlApp As Excel.Application Dim xlWorkBook As Excel.Workbook Dim xlWorkSheet As Excel.Worksheet Dim misValue As Object = System.Reflection.Missing.Value Dim i As Integer Dim j As Integer xlApp = New Excel.ApplicationClass xlWorkBook = xlApp.Workbooks.Add(misValue) xlWorkSheet = xlWorkBook.Sheets("sheet1") For i = 0 To DataGridView1.RowCount - 2 For j = 0 To DataGridView1.ColumnCount - 1 xlWorkSheet.Cells(i + 1, j + 1) = _ DataGridView1(j, i).Value.ToString() Next Next xlWorkSheet.SaveAs("C:\vbexcel.xlsx") xlWorkBook.Close() xlApp.Quit() releaseObject(xlApp) releaseObject(xlWorkBook) releaseObject(xlWorkSheet) 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

With the Excel file prepared, the final step is to transfer the data from the DataGridView to the corresponding cells within the Excel file. This process involves systematically extracting each data point from the DataGridView and writing it to the appropriate cell within the Excel workbook. By meticulously mapping the data to the correct location, the integrity and structure of the original table are maintained within the Excel file.

Conclusion

By following these steps diligently, you can effectively export the data from the Product table to an Excel file, facilitating seamless data transfer and enabling further analysis and utilization of the information in a popular and widely used format.