Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DataTable to Excel using C# Expand / Collapse
Author
Message
Posted Wednesday, December 02, 2009 4:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 12, 2010 10:45 PM
Points: 3, Visits: 4
Hi Guys,

I already established connection to Excel 2007 using the following connection string:
string ExcelConnectionString =
"Provider=Microsoft.ACE.OLEDB.12.0;" +
@"Data Source=" + DBExcel.ExportPath + ";" +
"Extended Properties=\"Excel 12.0;HDR=YES\";";
excel_con = new OleDbConnection(ExcelConnectionString);

I also had the Excel file (export.xlsx) under my DBExcel.ExportPath path.

My question is how will i populate my excel file with the data from my datatable?

Thank You
Post #827314
Posted Friday, January 08, 2010 1:37 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Today @ 1:34 AM
Points: 1,492, Visits: 1,691
Try the list below. I have used this myself as it help with formatting too.

http://www.c-sharpcorner.com/UploadFile/mgold/Query2Excel12032005011029AM/Query2Excel.aspx
Post #844151
Posted Monday, January 11, 2010 1:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 12,034, Visits: 11,062
Is this for a windows app or a web app? It makes a big difference because most people will not allow Office to be installed on a live webserver. The solution posted above requires Office to be installed on the machine that executes the code. It actually instantiates an Excel application (which can really difficult to kill the thread).

Using the OWC toolset is much cleaner in this case. I can post some sample code for doing this. It will take in a dataset and generate a new excel worksheet for each table in the dataset and then stream it to the request object as an attachment. And no COM objects!!!


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #845674
Posted Monday, January 11, 2010 7:45 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 12, 2010 10:45 PM
Points: 3, Visits: 4
Yeah, this is for a web app. Much appreciated!
Post #845817
Posted Tuesday, January 12, 2010 7:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 3:28 PM
Points: 12,034, Visits: 11,062
This is a class I got from somewhere on the Internet. I modified a bit to suit my needs. If there are more rows than will fit in an excel worksheet it will export the entire dataset to xml instead. Given the huge amount of rows in Excel this isn't going to be an issue on many datasets. I included a screenshot of the references you will need also. Hope this works for you.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #846158
Posted Tuesday, January 12, 2010 10:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 12, 2010 10:45 PM
Points: 3, Visits: 4
Thanks seanlange. I'll give it a try.
Post #846688
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse