ASP.NET MVC

Import, Read And Display Excel File Data Using WebGrid In ASP.Net MVC

Import read display excel file data using webgrid in ASP.NET MVC

Import Excel To WebGrid View

Import To Excel in ASP.NET MVC

For import read display excel file data using webgrid in ASP.NET MVC

View:

@using System.Data
@using System.Linq

@model DataSet
@{
    Layout = null;
}

@{
    ViewBag.Title = "Home Page";
}

<style type="text/css">
    body {
        font-family: Arial;
        font-size: 10pt;
    }

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

        table th {
            background-color: #cf4d35;
            color: #333;
            font-weight: bold;
        }

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

        table, table table td {
            border: 0px solid #ccc;
        }
</style>

@using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    <input type="file" name="excelPostedFile" />
    <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(),
                                               Country = p.Field<object>("Country").ToString()
                                           }), canSort: false, canPage: false);
    @webGrid.GetHtml(
             htmlAttributes: new { @id = "WebGrid" },
             columns: webGrid.Columns(
                     webGrid.Column("Id", "Id"),
                     webGrid.Column("Name", "Name"),
                     webGrid.Column("Country", "Country")));
}

 

Controller:  Here I am using home controller for demo.

 

using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Web;
using System.Web.Mvc;

namespace ImportExcelInWebGridInASP.NETMVC.Controllers
{
    public class HomeController : Controller
    {

        public ActionResult Index()
        {
            return View();
        }

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

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

                string conString = string.Empty;

                if (extension == ".xls")
                {
                    conString = "Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;";
                }
                else if (extension == ".xlsx")
                {
                    conString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties=Excel 8.0;";
                }

                conString = string.Format(conString, filePath);

                OleDbConnection conn = new OleDbConnection(conString);
                OleDbCommand cmdExcel = new OleDbCommand();
                OleDbDataAdapter odaExcel = new OleDbDataAdapter();
                cmdExcel.Connection = conn;

                conn.Open();
                DataTable dtExcelSchema;
                dtExcelSchema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
                conn.Close();

                conn.Open();
                cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
                odaExcel.SelectCommand = cmdExcel;
                odaExcel.Fill(ds);
                conn.Close();
            }
            return View(ds);
        }
    }
}

 

You can also check: 

How To Import Excel In Database Using ASP.NET MVC Entity Framework

 

Also Read  How To Import Excel In Database Using ASP.NET MVC Entity Framework

Sign Up for getting more new articles directly in your mail Inbox. 

About the author

Avtar

I am a blogger and I always believes in sharing a knowledge and happiness because happiness is the last journey of life.

Leave a Comment

Share
Tweet
Pin
+1
Share
0 Shares