Data table to Excel file using C#.net

     
Data table to Excel file using C#.net

Note: before doing this verify your refernces for  "Microsoft.Office.Interop.Excel.dll".
1) you have to find the Microsoft.Office.Interop.Excel.dll file
2) Microsoft.Office.Interop.Excel.dll refer in your project (or) put this dll in your bin folder
3) Add this below name spacess in your class file.
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Excel;

4) Dont open your excel file when you are using to modify through code. Before running this close that excel file. 
5) If file is not exists it will take care to create new file.

C:\Program Files (x86)\Microsoft Visual Studio 11.0\Visual Studio Tools for Office\PIA\Office14

static void Main(string[] args)
        {
            string conString = "server =PRASADPA1 ;User ID=XXXX; Password=XXXXX; database=dbName";
            SqlConnection sqlCon = new SqlConnection(conString);
            sqlCon.Open();
            DataSet ds = new DataSet();
            SqlDataAdapter da1 = new SqlDataAdapter("Exec TestSP", sqlCon);
         
            da1.Fill(ds);

            SqlDataAdapter da = new SqlDataAdapter("SELECT * from PecerTestTable", sqlCon);
            System.Data.DataTable dtMainSQLData = new System.Data.DataTable();
            da.Fill(dtMainSQLData);
           // ExportToExcel(dtMainSQLData, @"c:\test456.xls");

            DataColumnCollection dcCollection = dtMainSQLData.Columns;
            // Export Data into EXCEL Sheet
            Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = new
            Microsoft.Office.Interop.Excel.ApplicationClass();
            ExcelApp.Application.Workbooks.Add(Type.Missing);


            // ExcelApp.Cells.CopyFromRecordset(objRS);
            for (int i = 1; i < dtMainSQLData.Rows.Count + 2; i++)
            {
                for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
                {
                    if (i == 1)
                    {
                        ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString();
                    }
                    else
                        ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 2][j - 1].ToString();
                }
            }

            ExcelApp.ActiveWorkbook.SaveCopyAs(@"C:\test1b.xls");

         
            foreach (System.Data.DataTable dt in ds.Tables)
            {
                AddWorksheetToExcelWorkbook(@"C:\test1b.xls",dt.TableName, dt);
            }
         
        }
        private static void AddWorksheetToExcelWorkbook(string fullFilename, string worksheetName, System.Data.DataTable dtMainSQLData)
        {
            Microsoft.Office.Interop.Excel.Application xlApp = null;
            Workbook xlWorkbook = null;
            Sheets xlSheets = null;
            Worksheet xlNewSheet = null;

            try
            {
                xlApp = new Microsoft.Office.Interop.Excel.Application();

                if (xlApp == null)
                    return;

                // Uncomment the line below if you want to see what's happening in Excel
                // xlApp.Visible = true;

                xlWorkbook = xlApp.Workbooks.Open(fullFilename, 0, false, 5, "", "",
                        false, XlPlatform.xlWindows, "",
                        true, false, 0, true, false, false);

                xlSheets = xlWorkbook.Sheets as Sheets;

                // The first argument below inserts the new worksheet as the first one
                xlNewSheet = (Worksheet)xlSheets.Add(xlSheets[1], Type.Missing, Type.Missing, Type.Missing);
                xlNewSheet.Name = worksheetName;
                DataColumnCollection dcCollection = dtMainSQLData.Columns;
                for (int i = 1; i < dtMainSQLData.Rows.Count + 2; i++)
                {
                    for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++)
                    {
                        if (i == 1)
                        {
                            xlNewSheet.Cells[i, j] = dcCollection[j - 1].ToString();
                        }
                        else
                            xlNewSheet.Cells[i, j] = dtMainSQLData.Rows[i - 2][j - 1].ToString();
                    }
                }
             

                xlWorkbook.Save();
                xlWorkbook.Close(Type.Missing, Type.Missing, Type.Missing);
                xlApp.Quit();
            }
            finally
            {
                Marshal.ReleaseComObject(xlNewSheet);
                Marshal.ReleaseComObject(xlSheets);
                Marshal.ReleaseComObject(xlWorkbook);
                Marshal.ReleaseComObject(xlApp);
                xlApp = null;
            }
        }