wwwMarvscom wwwMarvscom - 2 months ago 11
Vb.net Question

Exporting all records from datagridview to Excel VB.NET

I used this code to export my records to excel and it works fine, but it only exports one row. Is there anyway to record all my data in the datagridview? Also, I'm not familiar with crystal report in VB so I want to use a button click event..

Dim xlApp As Microsoft.Office.Interop.Excel.Application
Dim xlWorkBook As Microsoft.Office.Interop.Excel.Workbook
Dim xlWorkSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim misValue As Object = System.Reflection.Missing.Value
Dim i As Integer
Dim j As Integer

xlApp = New Microsoft.Office.Interop.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
For k As Integer = 1 To DataGridView1.Columns.Count
xlWorkSheet.Cells(1, k) = DataGridView1.Columns(k - 1).HeaderText
xlWorkSheet.Cells(i + 2, j + 1) = DataGridView1(j, i).Value.ToString()
Next
Next
Next

xlWorkSheet.SaveAs("D:\vbexcel.xlsx")
xlWorkBook.Close()
xlApp.Quit()

releaseObject(xlApp)
releaseObject(xlWorkBook)
releaseObject(xlWorkSheet)

MsgBox("You can find the file D:\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


I can't comment to the one who posted this code cause I have few reputation...

Answer

Change your For Loop.

   'FOR HEADERS
    For i = 1 To DataGridView1.ColumnCount
        xlWorkSheet.Cells(1, i) = DataGridView1.Columns(i - 1).HeaderText
        'FOR ITEMS
        For j = 1 To DataGridView1.RowCount
            xlWorkSheet.Cells(j + 1, i) = DataGridView1(i - 1, j - 1).Value.ToString()
        Next
    Next