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



Import Excel Sheet Data in Database in MVC Import Excel Sheet Data in Database in MVC Reviewed by NEERAJ SRIVASTAVA on 6:06:00 PM Rating: 5

2 comments:

  1. Excellent and very cool idea and the subject at the top of magnificence and I am happy to this post..Interesting post! Thanks for writing it.What's wrong with this kind of post exactly? It follows your previous guideline for post length as well as clarity.
    Java Training in Chennai

    ReplyDelete
  2. Needed to compose one little word yet thanks for the suggestions that you are contributed here, willing to read this blog regularly to get more important stuff...
    Best Online Software Training Institute | SQL Server Training

    ReplyDelete

Powered by Blogger.