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


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

        objConn.Open();

        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();
            i++;
        }

   //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";
            }
            con.Open();
            sqlBulk.WriteToServer(dReader);
            con.Close();
          
        }
     objConn.Close();
    }


EmoticonEmoticon