i want to export data from datagridview to excel. when i export from a datagridview bound to a datatasource,the operation is failing, but when i use a non bound datagridview, the operation is successful .So i am surprised. Please help me to solve it.
What I have tried:
here is my code:
- for a bound datagridview.
Private Sub btnExportToExcel_Click(sender As Object, e As EventArgs) Handles btnExportToExcel.Click
Try
btnExportToExcel.Text = "please wait...."
btnExportToExcel.Enabled = False
SaveFileDialog1.Filter = "Excel document (*.xlsx)|*.xlsx"
If SaveFileDialog1.ShowDialog() = System.Windows.Forms.DialogResult.OK Then
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.Application
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(SaveFileDialog1.FileName)
xlWorkbook.Close()
xlApp.Quit()
releaseobject(xlApp)
releaseobject(xlWorksheet)
releaseobject(xlWorkbook)
MsgBox("successifully saved" & vbCrLf & "File are saved As:" & SaveFileDialog1.FileName, MsgBoxStyle.Information)
btnExportToExcel.Text = " Export to Ms Excel"
btnExportToExcel.Enabled = False
End If
Catch ex As Exception
MessageBox.Show("Failed to save !!!", "Error message", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return
End Try
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
- for a non bound datagridview
the code is the same as above for btnExportToexcel, But the datagridview is filled by data like this
For i As Integer = 1 To 20
With DataGridView1.Rows
.Add(i, "user" & i, "Indonesia", "Medan", "041" & i)
End With
Next