http://computertriksno1.blogspot.in/

Export multiple DataSets to multiple Excel sheets dynamically formatted according to the record's data type

26 Sept 2013 24 comments

“Export multiple DataSets to multiple Excel sheets dynamically formatted according to the record's data type”



public void DataSetsToExcel(List<DataSet> dataSets, string fileName)
    {
        Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
        Microsoft.Office.Interop.Excel.Workbook xlWorkbook = xlApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
        Sheets xlSheets = null;
        Microsoft.Office.Interop.Excel.Worksheet xlWorksheet = null;

        foreach (DataSet dataSet in dataSets)
        {
            System.Data.DataTable dataTable = dataSet.Tables[0];
            int rowNo = dataTable.Rows.Count;
            int columnNo = dataTable.Columns.Count;
            int colIndex = 0;

            //Create Excel Sheets
            xlSheets = xlWorkbook.Sheets;
            xlWorksheet = (Microsoft.Office.Interop.Excel.Worksheet)xlSheets.Add(xlSheets[1],
                           Type.Missing, Type.Missing, Type.Missing);
            xlWorksheet.Name = dataSet.DataSetName;

            //Generate Field Names
            foreach (DataColumn dataColumn in dataTable.Columns)
            {
                colIndex++;
                xlApp.Cells[1, colIndex] = dataColumn.ColumnName;
            }

            object[,] objData = new object[rowNo, columnNo];

            //Convert DataSet to Cell Data
            for (int row = 0; row < rowNo; row++)
            {
                for (int col = 0; col < columnNo; col++)
                {
                    objData[row, col] = dataTable.Rows[row][col];
                }
            }

            //Add the Data
            Range range = xlWorksheet.Range[xlApp.Cells[2, 1], xlApp.Cells[rowNo + 1, columnNo]];
            range.Value2 = objData;

            //Format Data Type of Columns
            colIndex = 0;
            foreach (DataColumn dataColumn in dataTable.Columns)
            {
                colIndex++;
                string format = "@";
                switch (dataColumn.DataType.Name)
                {
                    case "Boolean":
                        break;
                    case "Byte":
                        break;
                    case "Char":
                        break;
                    case "DateTime":
                        format = "dd/mm/yyyy";
                        break;
                    case "Decimal":
                        format = "$* #,##0.00;[Red]-$* #,##0.00";
                        break;
                    case "Double":
                        break;
                    case "Int16":
                        format = "0";
                        break;
                    case "Int32":
                        format = "0";
                        break;
                    case "Int64":
                        format = "0";
                        break;
                    case "SByte":
                        break;
                    case "Single":
                        break;
                    case "TimeSpan":
                        break;
                    case "UInt16":
                        break;
                    case "UInt32":
                        break;
                    case "UInt64":
                        break;
                    default: //String
                        break;
                }
                //Format the Column accodring to Data Type
                xlWorksheet.Range[xlApp.Cells[2, colIndex],
                      xlApp.Cells[rowNo + 1, colIndex]].NumberFormat = format;
            }
        }

        //Remove the Default Worksheet
        ((Microsoft.Office.Interop.Excel.Worksheet)xlApp.ActiveWorkbook.Sheets[xlApp.ActiveWorkbook.Sheets.Count]).Delete();

        //Save
        xlWorkbook.SaveAs(fileName, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, XlSaveAsAccessMode.xlNoChange, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value, System.Reflection.Missing.Value);
        xlWorkbook.Close();
        xlApp.Quit();
        GC.Collect();
    }





DataSet dataSet1 = new DataSet("My Data Set 1");
dataAdapter1.Fill(dataSet1);

DataSet dataSet2 = new DataSet("My Data Set 2");
dataAdapter1.Fill(dataSet2);

DataSet dataSet3 = new DataSet("My Data Set 3");
dataAdapter1.Fill(dataSet3);

List<DataSet> dataSets = new List<DataSet>();
dataSets.Add(dataSet1);
dataSets.Add(dataSet2);
dataSets.Add(dataSet3);

DataSetsToExcel(dataSets, "{Your File Name}")

Share this article :

+ comments + 24 comments

14 January 2014 at 06:55

You can watch on YouTube => Click Here

!!!Demo!!! Your website is perfect. I like it.
NTips 24
!!!Demo!!!

Anonymous
25 July 2014 at 15:35

You can watch on YouTube => Click Here

!!!Demo!!! If some one needs to be updated with latest technologies afterward he must be
go to see this website and be up to date daily.


my web blog; blogging jobs online !!!Demo!!!

Anonymous
27 July 2014 at 13:05

You can watch on YouTube => Click Here

!!!Demo!!! Really when someone doesn't know after that its up to
other people that they will assist, so here it occurs.


Feel free to surf to my page - rocket languages french !!!Demo!!!

Anonymous
1 August 2014 at 18:33

You can watch on YouTube => Click Here

!!!Demo!!! Right now it appears like Expression Engine is the best blogging platform available right now.

(from what I've read) Is that what you're using on your blog?



Check out my web-site: free online blog !!!Demo!!!

Anonymous
1 August 2014 at 20:16

You can watch on YouTube => Click Here

!!!Demo!!! IT News Tv News, Bollywood News, Lyrics Songs, Blogger Widgets, Templates, PHP Scripts, SEO Tools, Games, Internet Download Manager Register Life Time, Download Mobile Software, Download Java Software, Download Android Software, Drawsms, Free Hosting PHP Script, Free TRaffic Exchange Script, Cricket Score, Blogger Guide, Adsense Guide, For More Details Pls Visit: OKxm.Blogspot.com

!!!Demo!!!

Anonymous
1 August 2014 at 23:49

You can watch on YouTube => Click Here

!!!Demo!!! Hello there! I know this is somewhat off topic but I was wondering if you knew where I could get a captcha plugin for my
comment form? I'm using the same blog platform as yours
and I'm having problems finding one? Thanks a lot!



My page - blogging websites !!!Demo!!!

Anonymous
5 August 2014 at 07:24

You can watch on YouTube => Click Here

!!!Demo!!! I was able to find good advice from your blog articles.



my weblog: suing for medical negligence !!!Demo!!!

Anonymous
6 August 2014 at 23:09

You can watch on YouTube => Click Here

!!!Demo!!! I think this is among the most important information for me.
And i'm glad reading your article. But want to remark on few general things, The web site style is ideal, the articles is
really nice : D. Good job, cheers

Here is my website ... can you buy a house with cash !!!Demo!!!

Anonymous
15 August 2014 at 02:41

You can watch on YouTube => Click Here

!!!Demo!!! constantly i used to read smaller articles or reviews which as well clear
their motive, and that is also happening with this
post which I am reading here.

Here is my weblog :: sell my house fast reviews !!!Demo!!!

Anonymous
20 August 2014 at 01:13

You can watch on YouTube => Click Here

!!!Demo!!! My spouse and I absolutely love your blog and find
almost all of your post's to be exactly I'm looking for. Would you offer guest writers to write content
for you personally? I wouldn't mind publishing a post or elaborating
on a number of the subjects you write about here. Again, awesome web log!


my website top rated carpet cleaners Vancouver !!!Demo!!!

Anonymous
30 August 2014 at 17:14

You can watch on YouTube => Click Here

!!!Demo!!! Oh my goodness! Incredible article dude! Thanks, However
I am going through issues with your RSS. I don't know the reason why
I cannot join it. Is there anybody getting identical RSS problems?
Anybody who knows the solution will you kindly respond?
Thanks!!

Also visit my homepage :: french classes online !!!Demo!!!

Anonymous
10 September 2014 at 03:41

You can watch on YouTube => Click Here

!!!Demo!!! It's awesome in support of me to have a site,
which is beneficial for my know-how. thanks
admin

Review my blog ... comptoir granit ou quartz Quebec !!!Demo!!!

Anonymous
12 September 2014 at 19:52

You can watch on YouTube => Click Here

!!!Demo!!! I am not sure where you're getting your info, but great topic.
I needs to spend some time learning more or understanding more.
Thanks for excellent info I was looking for this information for my mission.

my web-site :: travel blog sites !!!Demo!!!

Anonymous
14 September 2014 at 19:32

You can watch on YouTube => Click Here

!!!Demo!!! WOW just what I was searching for. Came here by searching for blog website

Here is my web site ... online blog !!!Demo!!!

Anonymous
16 September 2014 at 11:40

You can watch on YouTube => Click Here

!!!Demo!!! Have you ever thought about including a little bit more than just your articles?
I mean, what you say is important and everything.
But think about if you added some great photos or video clips to give your posts
more, "pop"! Your content is excellent but with pics and video
clips, this blog could certainly be one of the very best in its niche.
Amazing blog!

Feel free to visit my page http://undxr.tumblr.com !!!Demo!!!

Anonymous
18 September 2014 at 20:15

You can watch on YouTube => Click Here

!!!Demo!!! I always emailed this web site post page to all my associates, because if like to read it next my contacts will too.


Here is my page casino jeux magog !!!Demo!!!

Anonymous
21 September 2014 at 07:26

You can watch on YouTube => Click Here

!!!Demo!!! Hey There. I found your weblog the use of msn. This is a really
smartly written article. I will make sure to bookmark it
and come back to read more of your useful info.
Thanks for the post. I'll certainly return.

Also visit my web-site - online language lessons !!!Demo!!!

Anonymous
21 September 2014 at 20:40

You can watch on YouTube => Click Here

!!!Demo!!! Very descriptive blog, I loved that bit. Will there be a part 2?


My blog post Le Demenagement Martin !!!Demo!!!

Anonymous
22 September 2014 at 10:59

You can watch on YouTube => Click Here

!!!Demo!!! It is perfect time to make some plans for the long
run and it is time to be happy. I've read this publish and if I may I wish
to suggest you some interesting things or advice.
Perhaps you can write subsequent articles referring to this article.
I want to learn even more issues approximately it!

Also visit my web site - demenagement Residentiel !!!Demo!!!

Anonymous
24 September 2014 at 09:45

You can watch on YouTube => Click Here

!!!Demo!!! Hello, yup this paragraph is really fastidious and I have learned lot of things from it about blogging.
thanks.

Also visit my page: Demenagement Piano !!!Demo!!!

Anonymous
27 September 2014 at 13:30

You can watch on YouTube => Click Here

!!!Demo!!! Pretty! This was an extremely wonderful article. Thank you for supplying this information.

My blog post: private jet charter broker !!!Demo!!!

Anonymous
27 September 2014 at 21:23

You can watch on YouTube => Click Here

!!!Demo!!! There is definately a lot to learn about this
subject. I love all of the points you made.

Feel free to visit my blog post: cabela's promotion code !!!Demo!!!

12 May 2015 at 17:36

You can watch on YouTube => Click Here

!!!Demo!!! Great methods to export database from one excel to another excel. very helpful information,
but i also know about a website that's also provide computer tricks and tips in hindi language.

Learn Computer In Hindi | Computer Tricks in Hindi

!!!Demo!!!

25 May 2016 at 13:03

You can watch on YouTube => Click Here

!!!Demo!!! Awesome Article.Thanks for sharing...

https://savnpik.com/UAE-deals/autos !!!Demo!!!

Post a Comment

 
Support : GDDon | Creating Website | Gddon |
Copyright © 2013. Computer Tricks and Tips for System - All Rights Reserved
Template Created by Creating Website Modify by GDDon.Com
Proudly powered by Blogger