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


C# C# 
Ratings:
5 Comment(s)
 31074  View(s)
Rate this:

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 = 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.


somthing wrong in Loop

By ravi bhushan on   3 years ago
Not working

If i do so it show Header but always left First Row.

Nice article

By Nibaal Bazzi on   3 years ago

Hi, a really nice article.

I checked out a MindStick DataConverter tool which you mentioned and it really looks like a nice tool to have.

Also I came across this C# Excel component, it extremely simplified my code when I implemented this task. Here is how I achieved importing and exporting a DataTable to Excel in C#:

private void btnImport_Click(object sender, EventArgs e)
{
    
ExcelFile workbook = ExcelFile.Load("C:\\Users\\Sachindra\\Desktop\\MyExcel2003.xls");
    
ExcelWorksheet worksheet = workbook.Worksheets.ActiveWorksheet;
    
    
DataTable dtExcel = worksheet.CreateDataTable(new CreateDataTableOptions());

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

private void btnExport_Click(object sender, EventArgs e)
{
    
DataTable dtMainSQLData = new DataTable();
    
// Fill DataTable ...

    
ExcelFile workbook = new ExcelFile();
    
ExcelWorksheet worksheet = workbook.Worksheets.Add(dtMainSQLData.TableName);
    worksheet.InsertDataTable(dtMainSQLData, 
new InsertDataTableOptions());

    workbook.Save(
"C:\\Users\\Sachindra\\Desktop\\test.xls");
    
MessageBox.Show("Data Exported Successfully into Excel File");
}


Re: Not working

By Animesh Datta on   2 years ago
Hello  ravi bhushan ,

it is working if you  modify the code by this

 for (int i = 1; i < dtMainSQLData.Rows.Count +2; i++) //changes have done here ,
                                                                          from dtMainSQLData.Rows.Count +1 to dtMainSQLData.Rows.Count +2
            {

                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();

                }

            }

Error occurs! The error message is: Not enough storage is available to complete this operation. (Exception from HRESULT: 0x8007000E (E_OUTOFMEMORY))

By Pranay Bankar on   2 years ago
Hi Arun,
with below loop I'm getting the Exception of out of memory .
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();
                }
            }

But below loop I'm getting the output but the DB table has 244 rows and I'm getting 194, as if I decrease the no. form 48 to 47; Exception occurs:

for (int i = 1; i < dt.Rows.Count - 48; i++)
                {
                    //column count
                    for (int j = 1; j < dt.Columns.Count + 1; j++)
                    {
                        //create column
                        if (i == 1)
                            excelApp.Cells[i, j] = dcCollection[j - 1].ToString();
                        else
                            //create all the cells of the column
                            excelApp.Cells[i, j] = dt.Rows[i - 2][j - 1].ToString();
                    }//end of for
                }//end of for

Please do help me.

First_Rowdata_not_show

By Dinesh Maurya on   4 months ago
If i do so it show Header but always left First Row.


Don't want to miss updates? Please click the below button!

Follow MindStick