Hi Readers !
In this Blog we will find how we can Export SQL Server table data to Excel using .net code .

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using Microsoft.Office.Interop.Excel ;
using Microsoft.Office.Interop;
using System.IO;
using System.Data;

namespace ExportToExcel
{
    public partial class ExportToExcel : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            //PAGE LOAD EVENT
        }

        //Here  we are using C#( ASP.Net) code and Microsoft Office original DLL file in order to extract data from a table from SQL Server and export it to standard Excel file.
        protected void ExportSQLTableDataToExcel(object sender, EventArgs e)
        {
           String strSqlConn;
           SqlConnection sqlConn = new SqlConnection("Server=localhost;Initial Catalog=SSIS_DB;Integrated security=SSPI"); // CONNECTION  USED TO OPEN SQL SERVER DATABASE
            SqlDataAdapter sqlDataAdapter = new SqlDataAdapter();
            System.Data.DataTable dataTableToExport=new System.Data.DataTable();
            try
            {
                strSqlConn = "SELECT * FROM dbo.EMP"; // SELECT QUERY FOR FETCHING DATA TO BE EXPORTED

                sqlConn.Open(); 
                SqlCommand sqlCommand = new SqlCommand(strSqlConn, sqlConn); // SQL STATEMENT TO EXECUTE AGAINST A SQL SERVER DATABASE
                sqlDataAdapter.SelectCommand = sqlCommand;

                // ADDING A WORKBOOK USING THE EXCEL APPLICATION.
                Microsoft.Office.Interop.Excel.Application appToExportToxls = new Microsoft.Office.Interop.Excel.Application();
                appToExportToxls.Workbooks.Add("");

                // ADDING A WORKSHEET.
                Microsoft.Office.Interop.Excel.Worksheet workSheetToExportToxls = default(Microsoft.Office.Interop.Excel.Worksheet);
                workSheetToExportToxls = (Microsoft.Office.Interop.Excel.Worksheet)appToExportToxls.Sheets["Sheet1"];


                sqlDataAdapter.Fill(dataTableToExport);
                sqlConn.Close();


                string xlsFilePath = Server.MapPath("ExportedFileFolder\\");

                if (!Directory.Exists(xlsFilePath))   // CHECK IF THE FOLDER EXISTS. IF NOT, CREATE A NEW FOLDER.
                        {
                            Directory.CreateDirectory(xlsFilePath);
                        }
                File.Delete(xlsFilePath + "EmpDetails.xlsx"); // DELETE THE FILE BEFORE CREATING A NEW ONE.


                //Export the Columns to excel file
                for (int irowIndex = 0; irowIndex < dataTableToExport.Columns.Count; irowIndex++)
                {
                    workSheetToExportToxls.Cells[1, irowIndex + 1] = dataTableToExport.Columns[irowIndex].ColumnName;
                }



                //Export the rows to excel file
                for (int irowIndex = 0; irowIndex < dataTableToExport.Rows.Count; irowIndex++)
               {
                   for (int iColumnIndex = 0; iColumnIndex < dataTableToExport.Columns.Count; iColumnIndex++)
                    {
                        workSheetToExportToxls.Cells[irowIndex + 2, iColumnIndex + 1] = dataTableToExport.Rows[irowIndex][dataTableToExport.Columns[iColumnIndex]];
                    }
               }

                workSheetToExportToxls.Columns.AutoFit();

                // SAVE THE FILE TO THE FOLDER.
                workSheetToExportToxls.SaveAs(xlsFilePath + "EmpDetails.xlsx");

                        // CLEAR ALL OBJECTS
                        appToExportToxls.Workbooks.Close();
                        appToExportToxls.Quit();
                        appToExportToxls = null;
                        workSheetToExportToxls = null;
            }
            catch (System.Exception ex)
            {
                // HANDLE EXCEPTION
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }
        }
    }
}