Generate Excel With Multiple Sheet from DataSet

 Step 1

 First of all we have to create a new Console Application ; let us see the description with images of how to create it.
 

• Open Visual Studio
• File>New>Project
• Choose Visual C#> Windows > Select Console Application 

Step 2

Select >> Project Menu >> Click on Add References >> Select COM Tab >>Add “Microsoft Excel 12.0 Object Library”

Step 3

Add App.config file for Sql Connection
 

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

  <appSettings>

    <add key="SqlConnnectionString" value="server=127.0.0.1;database=AdventureWorks;uid=[your SQL UserName];pwd=[your SQL Password];"/>

  </appSettings>

</configuration>

Step 3:  Write below code to generate Excel File with multiple Sheets. 
 

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Text;

using System.Diagnostics;

using System.Runtime.InteropServices;

using Microsoft.Office.Interop;

using Microsoft.Office.Interop.Excel;

using System.Data;

using System.IO;

using System.Data.SqlClient;

 

namespace ConsoleApplication1

{

    class Class2

    {

        public static void Main(string[] ar)

        {

            string FileName = "D:\\Testing.xslx";

            string SQLQuery = "Select Top 100  A.EmployeeID,CASE WHEN Gender = 'M' THEN 'Mr.' ELSE 'Mrs.' END Title, " +

                            " A.FirstName,A.LastName,B.Gender,A.JobTitle,A.Phone,A.EmailAddress,A.EmailPromotion," +

                            " A.AddressLine1,A.City,A.StateProvinceName,A.PostalCode,A.CountryRegionName  " +

                            " from AdventureWorks.HumanResources.vEmployee A  " +

                            " inner join " +

                            " AdventureWorks.HumanResources.Employee B " +

                            " on A.EmployeeID = B.EmployeeID ;" +

                            " Select top 100 * from Sales.vIndividualCustomer; " +

                            " Select top 100 * from Person.Contact; ";

 

            SqlConnection con = new SqlConnection(ConfigurationSettings.AppSettings["SqlConnnectionString"]);

            DataSet ds = new DataSet("myDataset");

            SqlDataAdapter da = new SqlDataAdapter(SQLQuery, con);

            da.Fill(ds);

            Application ExcelApp = new Application();

            Workbook ExcelWorkBook = null;

            Worksheet ExcelWorkSheet = null;

 

            ExcelApp.Visible = true;

            ExcelWorkBook = ExcelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);

 

            List<string> SheetNames = new List<string>();

            SheetNames.Add("Employee Details");

            SheetNames.Add("IndividualCustomer Details");

            SheetNames.Add("Contact Details");

 

            try

            {

                for (int i = 1; i < ds.Tables.Count; i++)

                    ExcelWorkBook.Worksheets.Add(); //Adding New sheet in Excel Workbook

 

                for (int i = 0; i < ds.Tables.Count; i++)

                {

                    int r = 1; // Initialize Excel Row Start Position  = 1

 

                    ExcelWorkSheet = ExcelWorkBook.Worksheets[i + 1];

 

                    //Writing Columns Name in Excel Sheet

 

                    for (int col = 1; col < ds.Tables[i].Columns.Count; col++)

                        ExcelWorkSheet.Cells[r, col] = ds.Tables[i].Columns[col - 1].ColumnName;

                    r++;

 

                    //Writing Rows into Excel Sheet

                    for (int row = 0; row < ds.Tables[i].Rows.Count; row++) //r stands for ExcelRow and col for ExcelColumn

                    {

                        // Excel row and column start positions for writing Row=1 and Col=1

                        for (int col = 1; col < ds.Tables[i].Columns.Count; col++)

                            ExcelWorkSheet.Cells[r, col] = ds.Tables[i].Rows[row][col - 1].ToString();

                        r++;

                    }

                    ExcelWorkSheet.Name = SheetNames[i];//Renaming the ExcelSheets

 

                }

 

                ExcelWorkBook.SaveAs(FileName);

                ExcelWorkBook.Close();

                ExcelApp.Quit();

                Marshal.ReleaseComObject(ExcelWorkSheet);

                Marshal.ReleaseComObject(ExcelWorkBook);

                Marshal.ReleaseComObject(ExcelApp);

            }

            catch (Exception exHandle)

            {

 

                Console.WriteLine("Exception: " + exHandle.Message);

                Console.ReadLine();

            }

            finally

            {

 

                foreach (Process process in Process.GetProcessesByName("Excel"))

                    process.Kill();

            }

 

        }

    }

}