blog

Home / DeveloperSection / Blogs / How to Import or Export SQL Server table Data in MS-Excel Sheet Using C# Code

How to Import or Export SQL Server table Data in MS-Excel Sheet Using C# Code

Anonymous User53202 10-Apr-2012

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.

How to Import or Export SQL Server table Data in MS-Excel Sheet Using C# Code

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.How to Import or Export SQL Server table Data in MS-Excel Sheet Using C# Code

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 = new Microsoft.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. It is a free charge too so you can easily download it from here.


Updated 18-Sep-2014
I am a content writter !

Leave Comment

Comments

Liked By