Datagridview to Excel

Hi,

I know this has been asked a few times but my question is not as complex as the others, I have a few rows in a datagridview which I simply want to export to a new excel sheet.

I'm wondering if there's a simple way to tell vb to copy whats currently displayed in the datagridview to excel?

Thank you
FCapoAsked:
Who is Participating?
 
PhilippeRenaudConnect With a Mentor Commented:
Maybe try the comment of CodeCruiser then ...:

CodeCruiser:
@FCapo
Is your grid bound to a datatable? You can use OLEDB as well to INSERT rows to excel

http://www.codeproject.com/Articles/8500/Reading-and-Writing-Excel-using-OLEDB
0
 
CodeCruiserConnect With a Mentor Commented:
0
 
PhilippeRenaudConnect With a Mentor Commented:
Yes, you need a little bit of code for that :
at the end I release the object by calling a Sub.

Imports Excel = Microsoft.Office.Interop.Excel
...

    Dim xlApp As Excel.Application
                Dim xlWorkBook As Excel.Workbook
                Dim xlWorkSheet As Excel.Worksheet
                Dim misValue As Object = Reflection.Missing.Value
                Dim sRange As Excel.Range
                Dim count As Integer = -1

                xlApp = New Excel.Application
                xlWorkBook = xlApp.Workbooks.Add(misValue)
                xlWorkSheet = xlWorkBook.Sheets("sheet1")

                For x As Integer = 0 To DataGridView1.ColumnCount - 1
                    If DataGridView1.Columns(x).Visible = True Then
                        count += 1
                        xlWorkSheet.Cells(1, x + 1) = DataGridView1.Columns(x).HeaderText.ToString()
                        For y As Integer = 0 To DataGridView1.RowCount - 1
                            If Not IsNothing(DataGridView1.Rows(y).Cells(x).Value) Then
                                xlWorkSheet.Cells(y + 2, x + 1) = DataGridView1.Rows(y).Cells(x).Value.ToString()
                            End If
                        Next
                    End If
                Next

                xlApp.Visible = True
                xlWorkSheet = xlWorkBook.Worksheets(1)

                releaseObject(sRange)
                releaseObject(xlApp)
                releaseObject(xlWorkSheet)
                releaseObject(xlWorkBook)

    Private Sub releaseObject(ByVal obj As Object)
        Try
            Runtime.InteropServices.Marshal.ReleaseComObject(obj)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        Finally
            GC.Collect()
            GC.WaitForPendingFinalizers()
            GC.Collect()
        End Try
    End Sub

Open in new window

0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
WebDevEMConnect With a Mentor Commented:
If you assign this to a button, it'll generate an Excel and feed it to the browser as a download:
Imports System.IO
...
Protected Sub btn_ExportToExcel_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_ExportToExcel.Click
        Response.Clear()
        Response.AddHeader("content-disposition", "attachment;filename=MyFileName.xls")
        Response.Charset = ""
        ' If you want the option to open the Excel file without saving than 
        ' comment out the line below 
        ' Response.Cache.SetCacheability(HttpCacheability.NoCache);
        Response.ContentType = "application/vnd.xls"
        Dim stringWrite As System.IO.StringWriter = New System.IO.StringWriter()
        Dim htmlWrite As System.Web.UI.HtmlTextWriter = New HtmlTextWriter(stringWrite)
        GridView_Jobs.RenderControl(htmlWrite)
        Response.Write(stringWrite.ToString())
        Response.End()
End Sub

Open in new window

0
 
FCapoAuthor Commented:
vb.net doesn't seem to recognize Dim xlApp As Excel.Application

I'm probably missing some references which I need to add to my project, do you know which ones to add to make this work?
0
 
CodeCruiserCommented:
You need to download and install correct version of Office Primary Interop Assemblies. Then add reference to Microsoft.Office.Interop.Excel
0
 
WebDevEMCommented:
If all you need the Excel to have is the basic data, my code (above) shouldn't need any extra assemblies.  For more power though,  PhilippeRenaud's version allows you to do much fancier spreadsheets but does require Microsoft.Office.Interop.Excel
0
 
FCapoAuthor Commented:
vb.net doesn't seem to recognize "Response" in my code even though I used Imports System.IO
0
 
WebDevEMCommented:
Response is in System.Web

MSDN Documentation at http://msdn.microsoft.com/en-us/library/system.web.httpresponse.aspx
0
 
FCapoAuthor Commented:
using PhilippeRenaud's method, I've added a few references and it still doesn't recognize     Dim xlApp As Excel.Application

Specifically I added the following references to my project :

microsoft office excel 14.0 object library
microsoft office 14.0 object library
microsoft visual basic for applications extensibility 5.3
microsoft.office.interop.excel
0
 
CodeCruiserCommented:
@WebDevEM,
We are in Winforms world!

@FCapo
Is your grid bound to a datatable? You can use OLEDB as well to INSERT rows to excel

http://www.codeproject.com/Articles/8500/Reading-and-Writing-Excel-using-OLEDB
0
 
WebDevEMCommented:
Well, that would explain why Response isn't working... Sorry to send anyone down the wrong track.
0
 
PhilippeRenaudCommented:
You added referecences to the solution but did you had the line in code:

Imports Excel = Microsoft.Office.Interop.Excel  


?
0
 
FCapoAuthor Commented:
Yes I did declare it as : Import Excel = Microsoft.Office.Interop.Excel

but I always get the same error:
Type 'excel.Application' is not defined
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.