Exporting data from datagrid view to excel
i've made a small tool that searches an access DB in back end & dumps the results in a Datagridview, now i want to export those results to excel, after googling i found a code that does it for me , it's working partially for me. It writes the Headers to a new excel file but then for some reason i getting below error (on the blue line in code):
Conversion from type 'DBNull' to type 'String' is not valid.
Code:
Private Sub btnExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExport.Click
Dim objExcel As New Microsoft.Office.Interop.Excel.Application
Dim oXL As New Microsoft.Office.Interop.Excel.Application
Dim oWB As Microsoft.Office.Interop.Excel.Workbook
Dim oSheet As Microsoft.Office.Interop.Excel.Worksheet
Dim oRng As Microsoft.Office.Interop.Excel.Range
'oXL = CType(CreateObject("Excel.Application"), a)
oXL.Visible = True
' Get a new workbook.
oWB = oXL.Workbooks.Add
oSheet = CType(oWB.ActiveSheet, Worksheet)
' Add table headers going cell by cell.
For Each cols As DataGridViewColumn In Me.DataGridView1.Columns
oSheet.Cells.Columns.CurrentRegion.Columns(cols.Index + 1) = cols.HeaderText
Next
' Format A1:D1 as bold, vertical alignment = center.
With oSheet.Range("A1", "Z1")
.Font.Bold = True
.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter
End With
' Create an array to set multiple values at once.
Dim rowcount As Integer = Me.DataGridView1.Rows.Count
Dim colcount As Integer = Me.DataGridView1.Columns.Count
Dim GridData(rowcount, colcount) As String
For Each row As DataGridViewRow In Me.DataGridView1.Rows
Dim rowindex As Integer = row.Index
For Each cells As DataGridViewCell In row.Cells
Dim cellindex As String = cells.ColumnIndex
GridData(rowindex, cellindex) = CStr(cells.Value)
Next
Next
' Fill A2:B6 with an array of values (First and Last Names).
oSheet.Range("A2", "G" & Me.DataGridView1.Rows.Count).Value = GridData
' Fill C2:C6 with a relative formula (=A2 & " " & B2). if you want to add a formula
oRng = oSheet.Range("C2", "C6")
oRng.Formula = "=A2 & "" "" & B2"
' Fill D2:D6 with a formula(=RAND()*100000) and apply format.if you want to apply a format
oRng = oSheet.Range("D2", "D6")
oRng.Formula = "=RAND()*100000"
oRng.NumberFormat = "$0.00"
' AutoFit columns A:D.
oRng = oSheet.Range("A1", "Z1")
oRng.EntireColumn.AutoFit()
' Make sure Excel is visible and give the user control
' of Excel's lifetime.
oXL.Visible = True
oXL.UserControl = True
' Make sure that you release object references.
oRng = Nothing
oSheet = Nothing
oWB = Nothing
oXL.Quit()
oXL = Nothing
pls help ?
Re: Exporting data from datagrid view to excel
vb Code:
Dim cellindex As String = cells.ColumnIndex
GridData(rowindex, cellindex) = CStr(cells.Value)
Try :
vb Code:
Dim cellindex As Integer = cells.ColumnIndex
Justin
Re: Exporting data from datagrid view to excel
no luck :(
still the same error