HOW TO IMPORT OR EXPORT SQL SERVER TABLE DATA IN MS-EXCEL SHEET USING C# CODE

In this blog I will show you how to export SQL Server table data in Excel sheet using c# code. Here I'm making application which import excels data in data table and export SQL Server data into excel sheet file.

Here I've two buttons; Import and Export which are using to import and export data from Excel to SQL Server and SQL Server to Excel.

Application Code:

private void btnImport_Click(object sender, EventArgs e)

        {

             // Create Data Table for MS-Office 2007 or 2003

            System.Data.DataTable dtExcel = new System.Data.DataTable();

            dtExcel.TableName = "MyExcelData";

            string SourceConstr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source='C:\Users\Sachindra\Desktop\MyExcel2003.xls';Extended Properties= 'Excel 8.0;HDR=Yes;IMEX=1'";

            OleDbConnection con = new OleDbConnection(SourceConstr);

            string query = "Select * from [Sheet1$]";

            OleDbDataAdapter data = new OleDbDataAdapter(query, con);

            data.Fill(dtExcel);

 

            MessageBox.Show("Data Imported Successfully into DataTable");

        }

 

        private void btnExport_Click(object sender, EventArgs e)

        {

            // Create sql connection string

             string conString = @"Data Source =  XXXX ; Initial Catalog = XXXX; User Id = XXXX; Password = XXXX;";

            SqlConnection sqlCon = new SqlConnection(conString);

            sqlCon.Open();

 

            SqlDataAdapter da = new SqlDataAdapter("select * from tblTest", sqlCon);

            System.Data.DataTable dtMainSQLData = new System.Data.DataTable();

            da.Fill(dtMainSQLData);

            DataColumnCollection dcCollection = dtMainSQLData.Columns;

             // Export Data into EXCEL Sheet

            Microsoft.Office.Interop.Excel.ApplicationClass ExcelApp = newMicrosoft.Office.Interop.Excel.ApplicationClass();

             ExcelApp.Application.Workbooks.Add(Type.Missing);

             // ExcelApp.Cells.CopyFromRecordset(objRS);

            for (int i = 1; i < dtMainSQLData.Rows.Count + 1; 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 - 1][j - 1].ToString();

                 }

             }

             ExcelApp.ActiveWorkbook.SaveCopyAs("C:\\Users\\Sachindra\\Desktop\\test.xls");

             ExcelApp.ActiveWorkbook.Saved = true;

             ExcelApp.Quit();

             MessageBox.Show("Data Exported Successfully into Excel File");

        }

     }

 So with the help of this application you could import data in SQL Server data table from Excel Sheet and export data from SQL Server table to Excel Sheet using C# code.  Thanks for reading this article.

If you're using any third party tool to perform this task or something like this task, there is one famous tool MindStick DataConverter which provides import export functionality.