Generate Excel With Multiple Sheet from DataSet

<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();

}

}

}

}