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