Hi Guys.
I'm New in Vb.Net and I really Need your Help Guys.
I'm trying to Export Data from a Data grid to Excel,
Does any one of you have a Code to do that?
Can you please Help me with that?
Thanking you in Anticipation for your Support and Understanding
Datagrid to an Excel Application
Page 1 of 110 Replies - 18592 Views - Last Post: 12 February 2010 - 07:21 PM
Replies To: Datagrid to an Excel Application
#2
Re: Datagrid to an Excel Application
Posted 29 August 2007 - 04:42 AM
#3
Re: Datagrid to an Excel Application
Posted 29 August 2007 - 05:35 AM
bushyt, on 29 Aug, 2007 - 04:07 AM, said:
Hi Guys.
I'm New in Vb.Net and I really Need your Help Guys.
I'm trying to Export Data from a Data grid to Excel,
Does any one of you have a Code to do that?
Can you please Help me with that?
Thanking you in Anticipation for your Support and Understanding
I'm New in Vb.Net and I really Need your Help Guys.
I'm trying to Export Data from a Data grid to Excel,
Does any one of you have a Code to do that?
Can you please Help me with that?
Thanking you in Anticipation for your Support and Understanding
Thanks a lot, but like i said i have never used VB.Net before so i was looking for something straight and forward (Code) can you help me with that?
Thanks a Lot
#4
Re: Datagrid to an Excel Application
Posted 29 August 2007 - 05:36 AM
Read the example I posted, try to implement it, when you run into problems/errors post the code you are working with and we will try and help you, I am not, however, going to just post a solution to your problem so you don't have to do anything except copy & paste to get it working

#5
Re: Datagrid to an Excel Application
Posted 29 August 2007 - 12:10 PM
bushyt, I just had to write functionality in an application to do just this, play around with the link I sent you, see what you can come up with, then once you do I can start showing you how I accomplished it

#6
Re: Datagrid to an Excel Application
Posted 29 August 2007 - 03:08 PM
To export data to Excel I use the following method. THis method takes your DataTable (which is what I assume you're binding to your DataGridView) and populates an Excel file with it. To make this work you need to add a reference to the Microsoft.Excel11 Interop (or 12 for 2007, 10 for XP, etc). To do this follow these steps
Once you've done that, add the following import statements to your file
I have a function and a sub procedure for this, one to actually create the Excel file, and one to dump the data from the DataTable into the Excel file. The function, aptly named CreateExcelFile takes 2 parameters, the name you want to save the file as and the DataTable that contains your data.
Now the PopulateSheet procedure
There are other ways to accomplish this task, this is just one of many ways.
Hope this helps
Happy Coding!
- Click Project on the menu
- Select Add Reference from the drop down menu
- Once the dialog opens click the COM tab
- Scroll down to Microsoft Excel 12.0 (or whatever version you're running) and highlight it
- Click OK
Once you've done that, add the following import statements to your file
Imports System.Runtime.InteropServices.Marshal Imports Microsoft.Office.Interop.Excel
I have a function and a sub procedure for this, one to actually create the Excel file, and one to dump the data from the DataTable into the Excel file. The function, aptly named CreateExcelFile takes 2 parameters, the name you want to save the file as and the DataTable that contains your data.
Public Shared Function CreateExcelFile(fileName as String, dt As DataTable) As Boolean Dim excelExport As New Microsoft.Office.Interop.Excel.Application() Dim excelBook As Microsoft.Office.Interop.Excel.Workbook Dim excelSheets As Microsoft.Office.Interop.Excel.Sheets Dim excelSheet As Microsoft.Office.Interop.Excel.Worksheet Dim excelCells As Microsoft.Office.Interop.Excel.Range Dim location As Integer = System.Windows.Forms.Application.ExecutablePath.LastIndexOf("\") Dim exPath As String = System.Windows.Forms.Application.ExecutablePath Dim filePath As String Try If Not isInstalled(ApiEnums.OfficeApplications.Excel) Then MsgBox("Microsoft Excel is required for this functionality." & vbCrLf & _ "Contact your Help Desk about getting " & vbCrLf & "this installed. Thank You.") Else filePath = exPath.Substring(0, (location + 1)) + "tmpFiles\" & fileName If Not Directory.Exists(exPath.Substring(0, (location + 1)) + "tmpFiles\") Then Directory.CreateDirectory(exPath.Substring(0, (location + 1)) + "tmpFiles\") End If excelExport.Visible = False : excelExport.DisplayAlerts = False excelBook = excelExport.Workbooks.Add excelSheets = excelBook.Worksheets excelSheet = CType(excelSheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet) excelSheet.Name = "YourSheetName - " & Date.Now.Day & Date.Now.ToString("MMM") & Date.Now.ToString("yy") excelCells = excelSheet.Cells PopulateSheet(dt, excelCells) excelSheet.SaveAs(filePath) excelBook.Close() excelExport.Quit() ReleaseComObject(excelCells) : ReleaseComObject(excelSheet) : ReleaseComObject(excelSheets) ReleaseComObject(excelBook) : ReleaseComObject(excelExport) excelExport = Nothing : excelBook = Nothing : excelSheets = Nothing excelSheet = Nothing : excelCells = Nothing System.GC.Collect() Return True End If Catch ex As Exception MsgBox(ex.Message,"Error exporting data") Return False End Try End Function
Now the PopulateSheet procedure
Private Shared Sub PopulateSheet(ByVal dt As System.Data.DataTable, ByVal oCells As Microsoft.Office.Interop.Excel.Range) Dim dRow As DataRow Dim dataArray() As Object Dim count As Integer Dim column_count As Integer 'Output Column Headers For column_count = 0 To dt.Columns.Count - 1 oCells(2, column_count + 1) = dt.Columns(column_count).ToString Next 'Output Data For count = 0 To dt.Rows.Count - 1 dRow = dt.Rows.Item(count) dataArray = dRow.ItemArray For column_count = 0 To UBound(dataArray) oCells(count + 3, column_count + 1) = dataArray(column_count).ToString Next Next End Sub
There are other ways to accomplish this task, this is just one of many ways.
Hope this helps

Happy Coding!
#7
Re: Datagrid to an Excel Application
Posted 30 August 2007 - 01:52 AM
PsychoCoder, on 29 Aug, 2007 - 03:08 PM, said:
To export data to Excel I use the following method. THis method takes your DataTable (which is what I assume you're binding to your DataGridView) and populates an Excel file with it. To make this work you need to add a reference to the Microsoft.Excel11 Interop (or 12 for 2007, 10 for XP, etc). To do this follow these steps
I have a function and a sub procedure for this, one to actually create the Excel file, and one to dump the data from the DataTable into the Excel file. The function, aptly named CreateExcelFile takes 2 parameters, the name you want to save the file as and the DataTable that contains your data.
Now the PopulateSheet procedure
There are other ways to accomplish this task, this is just one of many ways.
Hope this helps
Happy Coding!
- Click Project on the menu
- Select Add Reference from the drop down menu
- Once the dialog opens click the COM tab
- Scroll down to Microsoft Excel 12.0 (or whatever version you're running) and highlight it
- Click OK
Imports System.Runtime.InteropServices.Marshal Imports Microsoft.Office.Interop.Excel
I have a function and a sub procedure for this, one to actually create the Excel file, and one to dump the data from the DataTable into the Excel file. The function, aptly named CreateExcelFile takes 2 parameters, the name you want to save the file as and the DataTable that contains your data.
Public Shared Function CreateExcelFile(fileName as String, dt As DataTable) As Boolean Dim excelExport As New Microsoft.Office.Interop.Excel.Application() Dim excelBook As Microsoft.Office.Interop.Excel.Workbook Dim excelSheets As Microsoft.Office.Interop.Excel.Sheets Dim excelSheet As Microsoft.Office.Interop.Excel.Worksheet Dim excelCells As Microsoft.Office.Interop.Excel.Range Dim location As Integer = System.Windows.Forms.Application.ExecutablePath.LastIndexOf("\") Dim exPath As String = System.Windows.Forms.Application.ExecutablePath Dim filePath As String Try If Not isInstalled(ApiEnums.OfficeApplications.Excel) Then MsgBox("Microsoft Excel is required for this functionality." & vbCrLf & _ "Contact your Help Desk about getting " & vbCrLf & "this installed. Thank You.") Else filePath = exPath.Substring(0, (location + 1)) + "tmpFiles\" & fileName If Not Directory.Exists(exPath.Substring(0, (location + 1)) + "tmpFiles\") Then Directory.CreateDirectory(exPath.Substring(0, (location + 1)) + "tmpFiles\") End If excelExport.Visible = False : excelExport.DisplayAlerts = False excelBook = excelExport.Workbooks.Add excelSheets = excelBook.Worksheets excelSheet = CType(excelSheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet) excelSheet.Name = "YourSheetName - " & Date.Now.Day & Date.Now.ToString("MMM") & Date.Now.ToString("yy") excelCells = excelSheet.Cells PopulateSheet(dt, excelCells) excelSheet.SaveAs(filePath) excelBook.Close() excelExport.Quit() ReleaseComObject(excelCells) : ReleaseComObject(excelSheet) : ReleaseComObject(excelSheets) ReleaseComObject(excelBook) : ReleaseComObject(excelExport) excelExport = Nothing : excelBook = Nothing : excelSheets = Nothing excelSheet = Nothing : excelCells = Nothing System.GC.Collect() Return True End If Catch ex As Exception MsgBox(ex.Message,"Error exporting data") Return False End Try End Function
Now the PopulateSheet procedure
Private Shared Sub PopulateSheet(ByVal dt As System.Data.DataTable, ByVal oCells As Microsoft.Office.Interop.Excel.Range) Dim dRow As DataRow Dim dataArray() As Object Dim count As Integer Dim column_count As Integer 'Output Column Headers For column_count = 0 To dt.Columns.Count - 1 oCells(2, column_count + 1) = dt.Columns(column_count).ToString Next 'Output Data For count = 0 To dt.Rows.Count - 1 dRow = dt.Rows.Item(count) dataArray = dRow.ItemArray For column_count = 0 To UBound(dataArray) oCells(count + 3, column_count + 1) = dataArray(column_count).ToString Next Next End Sub
There are other ways to accomplish this task, this is just one of many ways.
Hope this helps

Happy Coding!
bushyt, on 30 Aug, 2007 - 01:48 AM, said:
PsychoCoder, on 29 Aug, 2007 - 03:08 PM, said:
To export data to Excel I use the following method. THis method takes your DataTable (which is what I assume you're binding to your DataGridView) and populates an Excel file with it. To make this work you need to add a reference to the Microsoft.Excel11 Interop (or 12 for 2007, 10 for XP, etc). To do this follow these steps
I have a function and a sub procedure for this, one to actually create the Excel file, and one to dump the data from the DataTable into the Excel file. The function, aptly named CreateExcelFile takes 2 parameters, the name you want to save the file as and the DataTable that contains your data.
Now the PopulateSheet procedure
There are other ways to accomplish this task, this is just one of many ways.
Hope this helps
Happy Coding!
- Click Project on the menu
- Select Add Reference from the drop down menu
- Once the dialog opens click the COM tab
- Scroll down to Microsoft Excel 12.0 (or whatever version you're running) and highlight it
- Click OK
Imports System.Runtime.InteropServices.Marshal Imports Microsoft.Office.Interop.Excel
I have a function and a sub procedure for this, one to actually create the Excel file, and one to dump the data from the DataTable into the Excel file. The function, aptly named CreateExcelFile takes 2 parameters, the name you want to save the file as and the DataTable that contains your data.
Public Shared Function CreateExcelFile(fileName as String, dt As DataTable) As Boolean Dim excelExport As New Microsoft.Office.Interop.Excel.Application() Dim excelBook As Microsoft.Office.Interop.Excel.Workbook Dim excelSheets As Microsoft.Office.Interop.Excel.Sheets Dim excelSheet As Microsoft.Office.Interop.Excel.Worksheet Dim excelCells As Microsoft.Office.Interop.Excel.Range Dim location As Integer = System.Windows.Forms.Application.ExecutablePath.LastIndexOf("\") Dim exPath As String = System.Windows.Forms.Application.ExecutablePath Dim filePath As String Try If Not isInstalled(ApiEnums.OfficeApplications.Excel) Then MsgBox("Microsoft Excel is required for this functionality." & vbCrLf & _ "Contact your Help Desk about getting " & vbCrLf & "this installed. Thank You.") Else filePath = exPath.Substring(0, (location + 1)) + "tmpFiles\" & fileName If Not Directory.Exists(exPath.Substring(0, (location + 1)) + "tmpFiles\") Then Directory.CreateDirectory(exPath.Substring(0, (location + 1)) + "tmpFiles\") End If excelExport.Visible = False : excelExport.DisplayAlerts = False excelBook = excelExport.Workbooks.Add excelSheets = excelBook.Worksheets excelSheet = CType(excelSheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet) excelSheet.Name = "YourSheetName - " & Date.Now.Day & Date.Now.ToString("MMM") & Date.Now.ToString("yy") excelCells = excelSheet.Cells PopulateSheet(dt, excelCells) excelSheet.SaveAs(filePath) excelBook.Close() excelExport.Quit() ReleaseComObject(excelCells) : ReleaseComObject(excelSheet) : ReleaseComObject(excelSheets) ReleaseComObject(excelBook) : ReleaseComObject(excelExport) excelExport = Nothing : excelBook = Nothing : excelSheets = Nothing excelSheet = Nothing : excelCells = Nothing System.GC.Collect() Return True End If Catch ex As Exception MsgBox(ex.Message,"Error exporting data") Return False End Try End Function
Now the PopulateSheet procedure
Private Shared Sub PopulateSheet(ByVal dt As System.Data.DataTable, ByVal oCells As Microsoft.Office.Interop.Excel.Range) Dim dRow As DataRow Dim dataArray() As Object Dim count As Integer Dim column_count As Integer 'Output Column Headers For column_count = 0 To dt.Columns.Count - 1 oCells(2, column_count + 1) = dt.Columns(column_count).ToString Next 'Output Data For count = 0 To dt.Rows.Count - 1 dRow = dt.Rows.Item(count) dataArray = dRow.ItemArray For column_count = 0 To UBound(dataArray) oCells(count + 3, column_count + 1) = dataArray(column_count).ToString Next Next End Sub
There are other ways to accomplish this task, this is just one of many ways.
Hope this helps

Happy Coding!
Thanks a Lot, this really Helped me but i'm still working on it to suite what i'm busy doing.
Once i'm done with everything i'll let you know.
Thanks again.
#8
Re: Datagrid to an Excel Application
Posted 20 September 2007 - 07:07 AM
bushyt, on 30 Aug, 2007 - 01:52 AM, said:
PsychoCoder, on 29 Aug, 2007 - 03:08 PM, said:
To export data to Excel I use the following method. THis method takes your DataTable (which is what I assume you're binding to your DataGridView) and populates an Excel file with it. To make this work you need to add a reference to the Microsoft.Excel11 Interop (or 12 for 2007, 10 for XP, etc). To do this follow these steps
I have a function and a sub procedure for this, one to actually create the Excel file, and one to dump the data from the DataTable into the Excel file. The function, aptly named CreateExcelFile takes 2 parameters, the name you want to save the file as and the DataTable that contains your data.
Now the PopulateSheet procedure
There are other ways to accomplish this task, this is just one of many ways.
Hope this helps
Happy Coding!
- Click Project on the menu
- Select Add Reference from the drop down menu
- Once the dialog opens click the COM tab
- Scroll down to Microsoft Excel 12.0 (or whatever version you're running) and highlight it
- Click OK
Imports System.Runtime.InteropServices.Marshal Imports Microsoft.Office.Interop.Excel
I have a function and a sub procedure for this, one to actually create the Excel file, and one to dump the data from the DataTable into the Excel file. The function, aptly named CreateExcelFile takes 2 parameters, the name you want to save the file as and the DataTable that contains your data.
Public Shared Function CreateExcelFile(fileName as String, dt As DataTable) As Boolean Dim excelExport As New Microsoft.Office.Interop.Excel.Application() Dim excelBook As Microsoft.Office.Interop.Excel.Workbook Dim excelSheets As Microsoft.Office.Interop.Excel.Sheets Dim excelSheet As Microsoft.Office.Interop.Excel.Worksheet Dim excelCells As Microsoft.Office.Interop.Excel.Range Dim location As Integer = System.Windows.Forms.Application.ExecutablePath.LastIndexOf("\") Dim exPath As String = System.Windows.Forms.Application.ExecutablePath Dim filePath As String Try If Not isInstalled(ApiEnums.OfficeApplications.Excel) Then MsgBox("Microsoft Excel is required for this functionality." & vbCrLf & _ "Contact your Help Desk about getting " & vbCrLf & "this installed. Thank You.") Else filePath = exPath.Substring(0, (location + 1)) + "tmpFiles\" & fileName If Not Directory.Exists(exPath.Substring(0, (location + 1)) + "tmpFiles\") Then Directory.CreateDirectory(exPath.Substring(0, (location + 1)) + "tmpFiles\") End If excelExport.Visible = False : excelExport.DisplayAlerts = False excelBook = excelExport.Workbooks.Add excelSheets = excelBook.Worksheets excelSheet = CType(excelSheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet) excelSheet.Name = "YourSheetName - " & Date.Now.Day & Date.Now.ToString("MMM") & Date.Now.ToString("yy") excelCells = excelSheet.Cells PopulateSheet(dt, excelCells) excelSheet.SaveAs(filePath) excelBook.Close() excelExport.Quit() ReleaseComObject(excelCells) : ReleaseComObject(excelSheet) : ReleaseComObject(excelSheets) ReleaseComObject(excelBook) : ReleaseComObject(excelExport) excelExport = Nothing : excelBook = Nothing : excelSheets = Nothing excelSheet = Nothing : excelCells = Nothing System.GC.Collect() Return True End If Catch ex As Exception MsgBox(ex.Message,"Error exporting data") Return False End Try End Function
Now the PopulateSheet procedure
Private Shared Sub PopulateSheet(ByVal dt As System.Data.DataTable, ByVal oCells As Microsoft.Office.Interop.Excel.Range) Dim dRow As DataRow Dim dataArray() As Object Dim count As Integer Dim column_count As Integer 'Output Column Headers For column_count = 0 To dt.Columns.Count - 1 oCells(2, column_count + 1) = dt.Columns(column_count).ToString Next 'Output Data For count = 0 To dt.Rows.Count - 1 dRow = dt.Rows.Item(count) dataArray = dRow.ItemArray For column_count = 0 To UBound(dataArray) oCells(count + 3, column_count + 1) = dataArray(column_count).ToString Next Next End Sub
There are other ways to accomplish this task, this is just one of many ways.
Hope this helps

Happy Coding!
bushyt, on 30 Aug, 2007 - 01:48 AM, said:
PsychoCoder, on 29 Aug, 2007 - 03:08 PM, said:
To export data to Excel I use the following method. THis method takes your DataTable (which is what I assume you're binding to your DataGridView) and populates an Excel file with it. To make this work you need to add a reference to the Microsoft.Excel11 Interop (or 12 for 2007, 10 for XP, etc). To do this follow these steps
I have a function and a sub procedure for this, one to actually create the Excel file, and one to dump the data from the DataTable into the Excel file. The function, aptly named CreateExcelFile takes 2 parameters, the name you want to save the file as and the DataTable that contains your data.
Now the PopulateSheet procedure
There are other ways to accomplish this task, this is just one of many ways.
Hope this helps
Happy Coding!
- Click Project on the menu
- Select Add Reference from the drop down menu
- Once the dialog opens click the COM tab
- Scroll down to Microsoft Excel 12.0 (or whatever version you're running) and highlight it
- Click OK
Imports System.Runtime.InteropServices.Marshal Imports Microsoft.Office.Interop.Excel
I have a function and a sub procedure for this, one to actually create the Excel file, and one to dump the data from the DataTable into the Excel file. The function, aptly named CreateExcelFile takes 2 parameters, the name you want to save the file as and the DataTable that contains your data.
Public Shared Function CreateExcelFile(fileName as String, dt As DataTable) As Boolean Dim excelExport As New Microsoft.Office.Interop.Excel.Application() Dim excelBook As Microsoft.Office.Interop.Excel.Workbook Dim excelSheets As Microsoft.Office.Interop.Excel.Sheets Dim excelSheet As Microsoft.Office.Interop.Excel.Worksheet Dim excelCells As Microsoft.Office.Interop.Excel.Range Dim location As Integer = System.Windows.Forms.Application.ExecutablePath.LastIndexOf("\") Dim exPath As String = System.Windows.Forms.Application.ExecutablePath Dim filePath As String Try If Not isInstalled(ApiEnums.OfficeApplications.Excel) Then MsgBox("Microsoft Excel is required for this functionality." & vbCrLf & _ "Contact your Help Desk about getting " & vbCrLf & "this installed. Thank You.") Else filePath = exPath.Substring(0, (location + 1)) + "tmpFiles\" & fileName If Not Directory.Exists(exPath.Substring(0, (location + 1)) + "tmpFiles\") Then Directory.CreateDirectory(exPath.Substring(0, (location + 1)) + "tmpFiles\") End If excelExport.Visible = False : excelExport.DisplayAlerts = False excelBook = excelExport.Workbooks.Add excelSheets = excelBook.Worksheets excelSheet = CType(excelSheets.Item(1), Microsoft.Office.Interop.Excel.Worksheet) excelSheet.Name = "YourSheetName - " & Date.Now.Day & Date.Now.ToString("MMM") & Date.Now.ToString("yy") excelCells = excelSheet.Cells PopulateSheet(dt, excelCells) excelSheet.SaveAs(filePath) excelBook.Close() excelExport.Quit() ReleaseComObject(excelCells) : ReleaseComObject(excelSheet) : ReleaseComObject(excelSheets) ReleaseComObject(excelBook) : ReleaseComObject(excelExport) excelExport = Nothing : excelBook = Nothing : excelSheets = Nothing excelSheet = Nothing : excelCells = Nothing System.GC.Collect() Return True End If Catch ex As Exception MsgBox(ex.Message,"Error exporting data") Return False End Try End Function
Now the PopulateSheet procedure
Private Shared Sub PopulateSheet(ByVal dt As System.Data.DataTable, ByVal oCells As Microsoft.Office.Interop.Excel.Range) Dim dRow As DataRow Dim dataArray() As Object Dim count As Integer Dim column_count As Integer 'Output Column Headers For column_count = 0 To dt.Columns.Count - 1 oCells(2, column_count + 1) = dt.Columns(column_count).ToString Next 'Output Data For count = 0 To dt.Rows.Count - 1 dRow = dt.Rows.Item(count) dataArray = dRow.ItemArray For column_count = 0 To UBound(dataArray) oCells(count + 3, column_count + 1) = dataArray(column_count).ToString Next Next End Sub
There are other ways to accomplish this task, this is just one of many ways.
Hope this helps

Happy Coding!
Thanks a Lot, this really Helped me but i'm still working on it to suite what i'm busy doing.
Once i'm done with everything i'll let you know.
Thanks again.
I'm having some issue with this part: isInstalled(ApiEnums.OfficeApplications.Excel)
Is there a reference or an import I should be using to get isInstalled to be recognized? Also I'm assuming that ApiEnums is something that you wrote. Would you be able to post that as well?
#9
Re: Datagrid to an Excel Application
Posted 16 December 2007 - 10:44 AM
Quote
If Not Directory.Exists(exPath.Substring(0, (location + 1)) + "tmpFiles\") Then Directory.CreateDirectory(exPath.Substring(0, (location + 1)) + "tmpFiles\") End If
I get "Name 'Directory' is not declared."
I have spent three days googling, reading forums and tutorials, I can't wait to find out if this actually works... but I'm not sure if this code requires another import or ???
#10
Re: Datagrid to an Excel Application
Posted 12 February 2010 - 06:42 PM
yes you need to import system.io
#11
Re: Datagrid to an Excel Application
Posted 12 February 2010 - 07:21 PM
Thava I would assume you would know by now that if a topic hasnt been touched in almost three years then it's probably been resolved. No need to necro it.
Page 1 of 1