[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 553
  • Last Modified:

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
0
FCapo
Asked:
FCapo
  • 4
  • 4
  • 3
  • +1
4 Solutions
 
CodeCruiserCommented:
0
 
PhilippeRenaudCommented:
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
 
WebDevEMCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now