How to import excel sheet in MVC

In this article, we learn how to import excel file in MVC or how to display excel sheet file in webgrid in MVC, Let’s start



Connection String

First we need to add connection string in web config. We need to add two connectionstring

1. The Excel files of version 97-2003
 <add name="ExcelConString03" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
2. The Excel files of version 2007 and above
<add name="ExcelConString07" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>

Controller:-
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace CODESOLUTIONS.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }

        [HttpPost]
        public ActionResult Index(HttpPostedFileBase postedFile)
        {


            DataSet ds = new DataSet();
            string filePath = string.Empty;
            if (postedFile != null)
            {
                string path = Server.MapPath("~/file/");
                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["ExcelConString03"].ConnectionString;
                        break;
                    case ".xlsx": //Excel 07 and above.
                        conString = ConfigurationManager.ConnectionStrings["ExcelConString07"].ConnectionString;
                        break;
                }

                conString = string.Format(conString, filePath);

                using (OleDbConnection oleconexcel = new OleDbConnection(conString))
                {
                    using (OleDbCommand cmdexcel = new OleDbCommand())
                    {
                        using (OleDbDataAdapter oleexcel = new OleDbDataAdapter())
                        {
                            cmdexcel.Connection = oleconexcel;

                            //Get the name of First Sheet.
                            oleconexcel.Open();
                            DataTable dtExcelSchema;
                            dtExcelSchema = oleconexcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                            string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                            oleconexcel.Close();

                            //Read Data from First Sheet.
                            oleconexcel.Open();
                            cmdexcel.CommandText = "SELECT * From [" + sheetName + "]";
                            oleexcel.SelectCommand = cmdexcel;
                            oleexcel.Fill(ds);
                            oleconexcel.Close();
                        }
                    }
                }
            }

            return View(ds);
        }
    }
}


View:-
@using System.Data
@using System.Linq
@model DataSet
@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <style type="text/css">
        body {
            font-family: Arial;
            font-size: 10pt;
        }

        .Grid {
            border: 1px solid #ccc;
            border-collapse: collapse;
        }

            .Grid th {
                background-color: #F7F7F7;
                font-weight: bold;
            }

            .Grid th, .Grid td {
                padding: 5px;
                border: 1px solid #ccc;
            }

            .Grid, .Grid table td {
                border: 0px solid #ccc;
            }

                .Grid th a, .Grid th a:visited {
                    color: #333;
                }
    </style>
</head>
<body>
    @using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
    {
        <input type="file" name="postedFile" />
        <input type="submit" value="Import" />
    }
    <hr />

    @if (Model != null)
    {
        WebGrid webGrid = new WebGrid(source: (from p in Model.Tables[0].AsEnumerable()
                                               select new
                                               {
                                                   Id = p.Field<object>("Id").ToString(),
                                                   Name = p.Field<object>("Name").ToString(),
                                                   Contact = p.Field<object>("Contact").ToString(),
                                                   Cource = p.Field<object>("Cource").ToString()

                                               }), canSort: false, canPage: false);
        @webGrid.GetHtml(
       
         htmlAttributes: new { @id = "WebGrid", @class = "Grid" },
        columns: webGrid.Columns(
                webGrid.Column("Id", "Id"),
                webGrid.Column("Name", "Name"),
                webGrid.Column("Contact", "Contact"),
                webGrid.Column("Cource", "Cource")));
    }
</body>
</html>

Excel file with Book1 .xlsx



Out-Put:





How to import excel sheet in MVC How to import excel sheet in MVC Reviewed by NEERAJ SRIVASTAVA on 11:21:00 PM Rating: 5

No comments:

Powered by Blogger.