Import multiple Excel sheets into multiple tables in database in ASP.Net MVC

mahesh213
 
on Sep 21, 2020 12:16 AM
Sample_161099.zip
1268 Views

Hi,

In excel i have 2 tabs Orders and Items

Currently my requirement is that how to save those tabs values into 2 different tables on database using MVC

Could you please help me

 

 

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Sep 21, 2020 07:05 AM

Hi mahesh213,

Using the below article i have created the example.

Import data from Excel file to SQL Server in ASP.Net MVC

Namespaces

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

Controller

public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        return View();
    }

    [HttpPost]
    public ActionResult Index(HttpPostedFileBase postedFile)
    {
        string filePath = string.Empty;
        if (postedFile != null)
        {
            string path = Server.MapPath("~/Uploads/");
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }

            filePath = path + Path.GetFileName(postedFile.FileName);
            string extension = Path.GetExtension(postedFile.FileName);
            postedFile.SaveAs(filePath);

            string conString = string.Empty;
            switch (extension)
            {
                case ".xls": //Excel 97-03.
                    conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
                    break;
                case ".xlsx": //Excel 07 and above.
                    conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
                    break;
            }
            conString = string.Format(conString, filePath);

            using (OleDbConnection connExcel = new OleDbConnection(conString))
            {
                using (OleDbCommand cmdExcel = new OleDbCommand())
                {
                    using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
                    {
                        cmdExcel.Connection = connExcel;
                        connExcel.Open();
                        DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        for (int i = 0; i < dtExcelSchema.Rows.Count; i++)
                        {
                            DataTable dt = new DataTable();
                            string sheetName = dtExcelSchema.Rows[i]["TABLE_NAME"].ToString();
                            if (sheetName == "Customers$")
                            {
                                connExcel.Close();
                                connExcel.Open();
                                cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
                                odaExcel.SelectCommand = cmdExcel;
                                odaExcel.Fill(dt);
                                connExcel.Close();
                                InsertIntoDB(dt, "Customers");
                            }
                            if (sheetName == "Students$")
                            {
                                connExcel.Close();
                                connExcel.Open();
                                cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
                                odaExcel.SelectCommand = cmdExcel;
                                odaExcel.Fill(dt);
                                connExcel.Close();
                                InsertIntoDB(dt, "Students");
                            }
                        }
                    }
                }
            }
        }

        return View();
    }

    private static void InsertIntoDB(DataTable dt, string tableName)
    {
        string conString = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;
        using (SqlConnection con = new SqlConnection(conString))
        {
            using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
            {
                sqlBulkCopy.DestinationTableName = "dbo." + tableName;
                con.Open();
                sqlBulkCopy.WriteToServer(dt);
                con.Close();
            }
        }
    }
}

View

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>
    @using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
    {
        <input type="file" name="postedFile" />
        <input type="submit" value="Import" />
    }
</body>
</html>