Import Excel Sheet Data in Database in MVC

As we know that we create any application to reduce the human effort or save the time so many times we need time we need to save the data in bulk form or client demand that they can save data in database using excel sheet so this article help us .In this article we learn how to import excel sheet values in database in MVC. As we learn also how to bind the webgrid with database in MVC and also if you want to send the notification via email then you can also see send a mail in MVC

Database

Script

CREATE TABLE [dbo].[record](

          [record_id] [int] IDENTITY(1,1) NOT NULL,

          [Name] [nvarchar](50) NULL,

          [Contact_no] [nvarchar](50) NULL,

          [Email] [nvarchar](50) NULL,

 CONSTRAINT [PK_record] PRIMARY KEY CLUSTERED

(

          [record_id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

Excel sheet Format:-

Controller

using Newtonsoft.Json.Linq;

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.OleDb;

using System.Data.SqlClient;

using System.Linq;

using System.Net;

using System.Web;

using System.Web.Mvc;

using System.Xml;

namespace importexceldata.Controllers

{

    public class HomeController : Controller

    {

        public ActionResult Index()

        {

            return View();

        }

        [HttpPost]

        public ActionResult Index(HttpPostedFileBase file)

        {

            DataSet ds = new DataSet();

            if (Request.Files["file"].ContentLength > 0)

            {

                string fileExtension =

                                     System.IO.Path.GetExtension(Request.Files["file"].FileName);

                if (fileExtension == ".xls" || fileExtension == ".xlsx")

                {

                    string fileLocation = Server.MapPath("~/Content/") + Request.Files["file"].FileName;

                    if (System.IO.File.Exists(fileLocation))

                    {

                        System.IO.File.Delete(fileLocation);

                    }

                    Request.Files["file"].SaveAs(fileLocation);

                    string excelConnectionString = string.Empty;

                    excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

                    //connection String for xls file format.

                    if (fileExtension == ".xls")

                    {

                        excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";

                    }

                    //connection String for xlsx file format.

                    else if (fileExtension == ".xlsx")

                    {

                        excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

                    }

                    //Create Connection to Excel work book and add oledb namespace

                    OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);

                    excelConnection.Open();

                    DataTable dt = new DataTable();

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

                    if (dt == null)

                    {

                        return null;

                    }

                    String[] excelSheets = new String[dt.Rows.Count];

                    int t = 0;

                    //excel data saves in temp file here.

                    foreach (DataRow row in dt.Rows)

                    {

                        excelSheets[t] = row["TABLE_NAME"].ToString();

                        t++;

                    }

                    OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);

                    string query = string.Format("Select * from [{0}]", excelSheets[0]);

                    using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))

                    {

                        dataAdapter.Fill(ds);

                    }

                }

                if (fileExtension.ToString().ToLower().Equals(".xml"))

                {

                    string fileLocation = Server.MapPath("~/Content/") + Request.Files["FileUpload"].FileName;

                    if (System.IO.File.Exists(fileLocation))

                    {

                        System.IO.File.Delete(fileLocation);

                    }

                    Request.Files["FileUpload"].SaveAs(fileLocation);

                    XmlTextReader xmlreader = new XmlTextReader(fileLocation);

                    ds.ReadXml(xmlreader);

                    xmlreader.Close();

                }

                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)

                {

                    string constr = "Data Source=NEERAJ-PC;Initial Catalog=CodeSolution;Persist Security Info=True;User ID=sa; password=12345678";

                    SqlConnection con = new SqlConnection(constr);

                    string query = "Insert into record(Name,Contact_no,Email) Values('" + ds.Tables[0].Rows[i][0].ToString() + "','" + ds.Tables[0].Rows[i][1].ToString() + "','" + ds.Tables[0].Rows[i][2].ToString() + "')";

                    SqlCommand cmd = new SqlCommand(query, con);

                    con.Open();

                    cmd.ExecuteNonQuery();

                    con.Close();

                    ViewBag.Message = "value inserted ";

                }

            }

            return View();

        }

    }

}

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" }))

            {

        <fieldset style="width:350px;">

            <legend>Import Excel Sheet Data in Database in MVC</legend>

            <input type="file" name="file" />

            <br />

            <br />

            <input type="submit" value="save" />

        </fieldset>

        <script type="text/javascript" src="//ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>

        <script type="text/javascript">

        var message = "@ViewBag.Message";

        $(function () {

            if (message != "") {

                alert(message);

            }

        });

        </script>

    }

</body>

</html>

Out-put:-

Download