Results 1 to 3 of 3

Thread: Exporting datatable to excel with ADO.net

  1. #1
    Join Date
    Jan 2009
    Posts
    19

    Exporting datatable to excel with ADO.net

    Hi,

    Well I am trying to use a blank or new excel application & fill data to it but the problem is its working very slow & I need to export this datatable to excel faster.

    Thanks,

  2. #2
    Join Date
    May 2008
    Posts
    72

    Re: Exporting datatable to excel with ADO.net

    Hello Sanket07,

    There are a few ways to do this...the best of which is to query the DataBase directly from Excel.
    Please check www.aspnetPRO.com they discuss Exporting to
    Excel in depth, primarily from the perspective of ASP.NET.
    I hope this helps you!

  3. #3
    Join Date
    May 2008
    Posts
    2,389

    Re: Exporting datatable to excel with ADO.net

    Here's a function that I use to do exactly what you are trying to do. You
    also will need at least some of these import statements at the top of you

    Code:
    Imports Excel.XlFileFormat
    
    Imports System.Data
    
    Imports System.Data.SqlClient
    
    Imports System.Data.SqlTypes
    
    Imports System.IO
    
    
    Public Function sqltabletocsvorxls(ByVal dt As DataTable, ByRef strpath As
    String, ByVal dtype As String, ByVal includeheader As Boolean) As Integer
    
    ' signature:
    
    ' dim funcs as new imcfunctionlib.functions
    
    ' dim xint as integer
    
    ' xint = funcs.sqltabletocsvorxls(dsmanifest.tables(0),mstrpath,
    "csv",false)
    
    ' where mstrpath = , say, "f:\imcapps\xlsfiles\test.xls"
    
    sqltabletocsvorxls = 0
    
    Dim objxl As Excel.Application
    
    Dim objwbs As Excel.Workbooks
    
    Dim objwb As Excel.Workbook
    
    Dim objws As Excel.Worksheet
    
    Dim mrow As DataRow
    
    Dim colindex As Integer
    
    Dim rowindex As Integer
    
    Dim col As DataColumn
    
    Dim fi As FileInfo = New FileInfo(strpath)
    
    If fi.Exists = True Then
    
    Kill(strpath)
    
    End If
    
    objxl = New Excel.Application
    
    'objxl.Visible = False ' i may not need to do this
    
    objwbs = objxl.Workbooks
    
    objwb = objwbs.Add
    
    objws = CType(objwb.Worksheets(1), Excel.Worksheet)
    
    ' i many want to change this to pass in a variable to determine
    
    ' if i want to have a column name row or not
    
    If includeheader Then
    
    For Each col In dt.Columns
    
    colindex += 1
    
    objws.Cells(1, colindex) = col.ColumnName
    
    Next
    
    rowindex = 1
    
    Else
    
    rowindex = 0
    
    End If
    
    For Each mrow In dt.Rows
    
    rowindex += 1
    
    colindex = 0
    
    For Each col In dt.Columns
    
    colindex += 1
    
    objws.Cells(rowindex, colindex) = mrow(col.ColumnName).ToString()
    
    Next
    
    Next
    
    If dtype = "csv" Then
    
    objwb.SaveAs(strpath, xlCSV)
    
    Else
    
    objwb.SaveAs(strpath)
    
    End If
    
    objxl.DisplayAlerts = False
    
    objws.Close()
    
    objxl.DisplayAlerts = True
    
    Marshal.ReleaseComObject(objws)
    
    objxl.Quit()
    
    Marshal.ReleaseComObject(objxl)
    
    objws = Nothing
    
    objwb = Nothing
    
    objwbs = Nothing
    
    objxl = Nothing
    
    End Function
    Hope this helps.

Similar Threads

  1. Exporting Project files in form of Excel Sheet
    By aelmalki in forum Microsoft Project
    Replies: 10
    Last Post: 20-04-2011, 07:49 PM
  2. Replies: 2
    Last Post: 15-04-2010, 02:57 AM
  3. Exporting assignment information to Excel
    By Jhal in forum Microsoft Project
    Replies: 7
    Last Post: 06-01-2010, 08:20 AM
  4. How can i export datatable to excel
    By Leena in forum Software Development
    Replies: 3
    Last Post: 30-01-2009, 11:45 PM
  5. Replies: 3
    Last Post: 15-02-2008, 02:45 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,642,850,736.20265 seconds with 17 queries