How to export Data Table from SQL Server to MS Excel using C#

Export Data from SQL Server to MS Excel:

To export the data from any table of SQL Server, This is the method which performing the task to export data from SQL server to MS Excel.

These are the steps:

• Create a connection with SQL server database by providing proper credential and server and database name.
• Create a SqlCommand object to define the SQL Command.
• Fill the data adapter with the datatable object values.
• Create Excel Application object for exporting the data.
• Insert data into Excel Application Worksheet.
• Save the Excel file.
• Clean the Excel COM object.

  protected DataTable ExportDataFromSQLServer()
        {
            DataTable dataTable = new DataTable();

            using (SqlConnection connection = new SqlConnection("Server=moon;Database=VipDB;Uid=vip;Pwd=DFkodj3dn"))
            {
                connection.Open();
  
                // Define the query to be performed to export desired data
                SqlCommand command = new SqlCommand("select * from Partners", connection);

                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);

                dataAdapter.Fill(dataTable);

                var excelApplication = new Excel.Application();

                var excelWorkBook = excelApplication.Application.Workbooks.Add(Type.Missing);

                DataColumnCollection dataColumnCollection = dataTable.Columns;
               
                for (int i = 1; i <= dataTable.Rows.Count + 1; i++)
                {
                    for (int j = 1; j <= dataTable.Columns.Count; j++)
                    {
                        if (i == 1)
                            excelApplication.Cells[i, j] = dataColumnCollection[j - 1].ToString();
                        else
                            excelApplication.Cells[i, j] = dataTable.Rows[i - 2][j - 1].ToString();
                    }
                }

                // Save the excel file at specified location
                excelApplication.ActiveWorkbook.SaveCopyAs(@"C:\Users\Vip\Desktop\test.xlsx");

                excelApplication.ActiveWorkbook.Saved = true;

                // Close the Excel Application
                excelApplication.Quit();

                connection.Close();

                //Release or clear the COM object
                releaseObject(excelWorkBook);
                releaseObject(excelApplication);

                MessageBox.Show("Your data is exported Successfully into Excel File.");
            }
            return dataTable;
        }

Clean the Excel interop objects: 

At last, we have to clean or release the Excel COM objects. This below method is used to clean the object of Excel Application, Excel Workbook and Excel Worksheet objects.

    private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }