Click here to Skip to main content
14,920,935 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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
Posted
Comments
Patrice T 2-May-21 18:44pm
   
Got an error message ?
Member 14904307 3-May-21 17:18pm
   
Yes dear Patrice ,i got "failed to save " error message when i run the code relative to a bound datagridview( dgv obtained by selecting data from a table in database and put them in dataset,...... ). But when the datagridview is not bound , the code is successfully executed
CHill60 4-May-21 8:32am
   
We want the actual error message, not your message box text. I.e. ex.Description
Member 14904307 5-May-21 5:47am
   
Now i found the solution. the problem was due to data type in my datagridview. there was type nothing value in some rowcells of datagridview
i added this code : If DataGridView1(j, i).Value IsNot Nothing Then ......end if. so the new code which is working below


Private Sub btnExportToExcel_Click(sender As Object, e As EventArgs) Handles btnExportToExcel.Click
DataGridView1.ReadOnly = True
DataGridView1.AllowUserToAddRows = True
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 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 k As Integer = 1 To DataGridView1.Columns.Count
xlWorksheet.Cells(1, k).value = DataGridView1.Columns(k - 1).HeaderText

Next

For i = 0 To DataGridView1.RowCount - 2
For j = 0 To DataGridView1.ColumnCount - 1



If DataGridView1(j, i).Value IsNot Nothing Then


xlWorksheet.Cells(i + 2, j + 1).value = DataGridView1(j, i).Value.ToString
End If

Next
Next

xlWorksheet.SaveAs(SaveFileDialog1.FileName)

xlWorkbook.Close()
xlApp.Quit()

releaseobjectb(xlApp)
releaseobjectb(xlWorksheet)
releaseobjectb(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 releaseobjectb(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

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900