[bushyt]

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

[PsychoCoder]

There is some ideas on doing exactly that here

Happy Coding!

[bushyt]

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

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

[PsychoCoder]

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

[PsychoCoder]

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

[PsychoCoder]

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

• 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!

[bushyt]

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

• 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!

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

• 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!

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.

[Westwood]

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

• 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!

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

• 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!

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?

[webwired]

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 ???

[thava]

yes you need to import system.io

[Sethro117]

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.