Go Back   TechArena Community > Software > Software Development
Become a Member!
Forgot your username/password?
Tags Active Topics RSS Search Mark Forums Read

Sponsored Links



Exporting datatable to excel with ADO.net

Software Development


Reply
 
Thread Tools Search this Thread
  #1  
Old 03-02-2009
Member
 
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,

Reply With Quote
  #2  
Old 03-02-2009
Member
 
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!
Reply With Quote
  #3  
Old 03-02-2009
Member
 
Join Date: May 2008
Posts: 2,383
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.
__________________
The FIFA Manager 2009 PC Game
Reply With Quote
Reply

  TechArena Community > Software > Software Development
Tags:



Thread Tools Search this Thread
Search this Thread:

Advanced Search


Similar Threads for: "Exporting datatable to excel with ADO.net"
Thread Thread Starter Forum Replies Last Post
Exporting Project files in form of Excel Sheet aelmalki Microsoft Project 10 20-04-2011 07:49 PM
Remove Time from Start/Finish Date Columns When Exporting to Excel Cic!cone Microsoft Project 2 15-04-2010 02:57 AM
Exporting assignment information to Excel Jhal Microsoft Project 7 06-01-2010 08:20 AM
How can i export datatable to excel Leena Software Development 3 30-01-2009 11:45 PM
Exporting project information to excel pivot table and workbook Itsme Microsoft Project 3 15-02-2008 02:45 AM


All times are GMT +5.5. The time now is 01:42 PM.