December 2, 2009 at 4:21 am
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
January 8, 2010 at 1:37 am
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
January 11, 2010 at 1:21 pm
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 11, 2010 at 7:45 pm
Yeah, this is for a web app. Much appreciated!
January 12, 2010 at 7:37 am
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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 12, 2010 at 10:49 pm
Thanks seanlange. I'll give it a try.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply