How to Export to Excel Using DataSource

[ryoka012]

Hi experts,


I'am having a problem in exporting my data from Datagridview to my Excel it takes to much time at it gives me an error that said it have to much rows (i'am exporting a Rows of 5000).

How can i export my data using may datasource

Here is my code in Retrievingmy Data from Database to Datagridview

 Dim con As New SqlConnection(sqlcon)
        Try
            con.Open()
            Dim cmd As SqlCommand = con.CreateCommand
            cmd.CommandText = "usp_GetReports"
            cmd.CommandType = CommandType.StoredProcedure
            Dim dr As SqlDataReader = cmd.ExecuteReader
            Dim dt As New DataTable
            dt.Load(dr)
            DataGridViewReportV1.DataSource = dt
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MsgBox(ex.Message & vbNewLine & "Error in Retrieving")
        Finally
            If con IsNot Nothing Then
                con.Close()
                con.Dispose()
            End If
        End Try

Here is how i'm exporting my data to excel

 Dim xlApp As Excel.Application
        Dim xlWorkBook As Excel.Workbook
        Dim xlWorkSheet As Excel.Worksheet
        Dim misValue As Object = System.Reflection.Missing.Value


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



    // This is where i have an error if my rows are to large ///
        For i As Integer = 1 To DatagridReport.Columns.Count
            xlWorkSheet.Cells(1, i) = DatagridReport.Columns(i - 1).HeaderText
        Next
        For i As Integer = 0 To DatagridReport.Rows.Count - 2
            For j As Integer = 0 To DatagridReport.Columns.Count - 1
                xlWorkSheet.Cells(i + 2, j + 1) = DatagridReport.Rows(i).Cells(j).Value.ToString()
            Next
        Next
 
        Dim sd As New SaveFileDialog 'declare save file dialog

        sd.Filter = "Excel File (*.xlsx)|*.xlsx"
        sd.Title = "Save an Excel File"
        sd.RestoreDirectory = True

        If sd.ShowDialog = Windows.Forms.DialogResult.OK Then 'check if save file dialog was close after selecting a path
            'MsgBox(sd.FileName)
            xlWorkSheet.SaveAs(sd.FileName) 'sd.filename reurns save file dialog path
            MsgBox("You can find the file " + sd.FileName)
            xlWorkBook.Close()
            xlApp.Quit()
        End If


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

Thank you for your guidance.

[incidentals]

why not export to csv and then import the csv

its clunky but will work

[Shaggy Hiker]

I moved the thread to VB.NET, which is what the language is.

I've never tried exporting that many records, but in this thread is a class I wrote for exporting records to Excel:

http://www.vbforums.com/showthread.p...ter-base-class

Frankly, it's kind of a pain. Since Excel works so well with CSVs, I tend to agree with incidentals. Alternatively, you might be able to write to Excel using ADO.NET with Excel being the 'database' in the operation. I've never tried this, but you can query either text files or spreadsheets, so it seems like you ought to be able to perform INSERT operations against a spreadsheet.

[TnTinMN]

Since you are using Excel Interop, the last thing you want to do is to iterate each cell in a table. The ideal method is to create a 2D array of the values and set it to an equally sized 2D Excel range.

I'm a bit confused as you show loading DataGridViewReportV1 from a Datatable, but in the export routine, you show using DatagridReport. I will assume that these two DGV's are the same.

Since the data is already in a DataTable, I would export from that directly. The following is a simple example of exporting from a Datatable to an Excel spreadsheet using interop. Instead of creating one large array to export, it exports by row.

Imports Microsoft.Office.Interop
Public Class Form1
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        TestExport()
    End Sub
 
    Private Sub TestExport()
        Dim app As New Excel.Application
        Dim wb As Excel.Workbook = app.Workbooks.Add
        Dim ws As Excel.Worksheet = CType(wb.Worksheets.Item(1), Excel.Worksheet)
        Dim dt As DataTable = New DemoDT
        ExportDT(dt, ws.Range("b3"))
        ' leave excel open
        app.UserControl = True
        app.Visible = True
    End Sub
 
    Private Sub ExportDT(dt As DataTable, topLeftCell As Excel.Range)
        Dim exportRange As Excel.Range = CType(topLeftCell.Cells.Item(1, 1), Excel.Range)
        exportRange = exportRange.Resize(1, dt.Columns.Count)
        ' create and export header
        Dim header As New List(Of String)
        For Each c As DataColumn In dt.Columns
            header.Add(c.ColumnName)
        Next
        exportRange.Value2 = header.ToArray
 
        'export each row
        For Each dr As DataRow In dt.Rows
            exportRange = exportRange.Offset(1, 0)
            exportRange.Value2 = dr.ItemArray
        Next
    End Sub
End Class

This code will work for any Datatable so long as the data types are compatible with Excel. For the purpose of the example, this is the DataTable used.

Friend Class DemoDT : Inherits DataTable
    Private Shared typString As Type = GetType(String), _
                        typInt32 As Type = GetType(Int32), _
                        typDateTime As Type = GetType(DateTime)
 
    Public Sub New()
        MyBase.New()
 
        With Me.Columns
            .Add("Name", typString)
            .Add("Date", typDateTime)
            .Add("Value", GetType(Single))
        End With
        With Me.Rows
            .Add("Barney", New DateTime(1992, 3, 30), 10)
            .Add("Betty", New DateTime(1998, 12, 11), 11)
            .Add("Fred", New DateTime(1985, 2, 16), 12)
            .Add("Wilma", New DateTime(1988, 5, 6), 32)
        End With
    End Sub
End Class