Import Data from Multiple Excelsheets to Multiple table in database in Asp.Net.

Aman Sharma
In this article I will explain how to Import data from Excel sheet with multiple worksheets to multiple table.

In this article I will Import data from Two worksheets to Two Table in SQL database.

 1.     Create a excel sheet with two worksheets, as given in example.
 2.     Now create two same tables in SQL database.
 3.     Keep in mind, Column name must be same in SQL Table & Excel Sheets.
 4.     Now write given code on button click event to import data from excel sheet to database tables.

Excel Sheet Example:

SQL Tables:


Employee Table                                                                 Dept Table

ASP.NET code to Import Data from Multiple Excel Sheets to Multiple Tables:

Add Following Namespaces:

using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;

Write code on Button Click:

protected void Button1_Click(object sender, EventArgs e)
        OleDbConnection objConn = null;
        System.Data.DataTable dt = null;
        string excelCon = "";
        string strFile = "C:\\Users\\Aman Sharma\\Desktop\\Test.xlsx";
        if (strFile.Trim().EndsWith(".xlsx"))
            excelCon = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", strFile);
        else if (strFile.Trim().EndsWith(".xls"))
            excelCon = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";", strFile);

        objConn = new OleDbConnection(excelCon);


        dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

        String[] excelSheets = new String[dt.Rows.Count];
        int i = 0;

        foreach (DataRow row in dt.Rows)
            excelSheets[i] = row["TABLE_NAME"].ToString();

   //ifthere is empty sheet i.e. Sheet3 other dont decrease lenth by one.

        for (int j = 0; j < excelSheets.Length-1; j++)
            OleDbCommand cmd = new OleDbCommand("Select * from [" + excelSheets [j]+ "]",objConn);
            OleDbDataReader dReader;
            dReader = cmd.ExecuteReader();
            SqlBulkCopy sqlBulk = new SqlBulkCopy(con);
            if (j == 0)
                sqlBulk.DestinationTableName = "Employee";
            else if (j == 1)
                sqlBulk.DestinationTableName = "Dept";

Post a Comment

Post a Comment (0)

#buttons=(Accept !) #days=(20)

Our website uses cookies to enhance your experience. Learn More
Accept !