We help IT Professionals succeed at work.

Datagridview to Excel

FCapo
FCapo asked
on
Medium Priority
572 Views
Last Modified: 2012-09-06
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
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
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

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

Author

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?
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
You need to download and install correct version of Office Primary Interop Assemblies. Then add reference to Microsoft.Office.Interop.Excel

Commented:
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

Author

Commented:
vb.net doesn't seem to recognize "Response" in my code even though I used Imports System.IO

Commented:
Response is in System.Web

MSDN Documentation at http://msdn.microsoft.com/en-us/library/system.web.httpresponse.aspx

Author

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
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014

Commented:
@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

Commented:
Well, that would explain why Response isn't working... Sorry to send anyone down the wrong track.
You added referecences to the solution but did you had the line in code:

Imports Excel = Microsoft.Office.Interop.Excel  


?

Author

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

Explore More ContentExplore courses, solutions, and other research materials related to this topic.