|
|
![]() |
| Thread Tools | Search this Thread |
#1
| |||
| |||
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
| |||
| |||
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
| |||
| |||
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
__________________ The FIFA Manager 2009 PC Game |
![]() |
|
Tags: datatable to excel |
Thread Tools | Search this Thread |
|
![]() | ||||
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 |