How to read Excel File and populate its data in Grid in MVC without storing the Excel File anywhere?

import data from excel file to database table in asp.net mvc 5
upload and read excel file in asp net c#
import data from excel to sql server using mvc 5
read excel file in c#
display excel file in mvc
read data from excel file in angularjs
how to read file in mvc
how to open excel file in mvc

I want to upload an Excel File through the Browse Button Control. I need not save it. Then, on the click of a Button, How can I read the Data in Excel and show it in a Grid View. I need to complete this task using MVC.

Here is my revised answer:

1) Download OpenXML SDK from Microsoft

2) Create a blank MVC 5 project, and name it "MVCImportExcel"

3) Add reference to DocumentFormat.OpenXML by browsing to SDK lib subdirectory

4) Add reference to WindowsBase

5) Create new Model called "MyViewModel"

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations;


namespace MVCImportExcel.Models
{
    public class MyViewModel
    {
        [Required]
        public HttpPostedFileBase MyExcelFile { get; set; }

        public string MSExcelTable { get; set; }
    }
}

6) Create a new controller called "HomeController"

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using MVCImportExcel.Models;
using System.Data;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System.IO;
namespace MVCImportExcel.Controllers
{
    public class HomeController : Controller
    {
        //
        // GET: /Home/
        public ActionResult Index()
        {
            var model = new MyViewModel();
            return View(model);
        }

        [HttpPost]
        public ActionResult Index(MyViewModel model)
        {
            if (!ModelState.IsValid)
            {
                return View(model);
            }
            DataTable dt = GetDataTableFromSpreadsheet(model.MyExcelFile.InputStream,false);
            string strContent = "<p>Thanks for uploading the file</p>" + ConvertDataTableToHTMLTable(dt);
            model.MSExcelTable = strContent;
            return View(model);
        }
        public static DataTable GetDataTableFromSpreadsheet(Stream MyExcelStream, bool ReadOnly)
        {
            DataTable dt = new DataTable();
            using (SpreadsheetDocument sDoc = SpreadsheetDocument.Open(MyExcelStream, ReadOnly))
            {
                WorkbookPart workbookPart = sDoc.WorkbookPart;
                IEnumerable<Sheet> sheets = sDoc.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
                string relationshipId = sheets.First().Id.Value;
                WorksheetPart worksheetPart = (WorksheetPart)sDoc.WorkbookPart.GetPartById(relationshipId);
                Worksheet workSheet = worksheetPart.Worksheet;
                SheetData sheetData = workSheet.GetFirstChild<SheetData>();
                IEnumerable<Row> rows = sheetData.Descendants<Row>();

                foreach (Cell cell in rows.ElementAt(0))
                {
                    dt.Columns.Add(GetCellValue(sDoc, cell));
                }

                foreach (Row row in rows) //this will also include your header row...
                {
                    DataRow tempRow = dt.NewRow();

                    for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
                    {
                        tempRow[i] = GetCellValue(sDoc, row.Descendants<Cell>().ElementAt(i));
                    }

                    dt.Rows.Add(tempRow);
                }
            }
            dt.Rows.RemoveAt(0);
            return dt;
        }
        public static string GetCellValue(SpreadsheetDocument document, Cell cell)
        {
            SharedStringTablePart stringTablePart = document.WorkbookPart.SharedStringTablePart;
            string value = cell.CellValue.InnerXml;

            if (cell.DataType != null && cell.DataType.Value == CellValues.SharedString)
            {
                return stringTablePart.SharedStringTable.ChildElements[Int32.Parse(value)].InnerText;
            }
            else
            {
                return value;
            }
        }
        public static string ConvertDataTableToHTMLTable(DataTable dt)
        {
            string ret = "";
            ret = "<table id=" + (char)34 + "tblExcel" + (char)34 + ">";
            ret+= "<tr>";
            foreach (DataColumn col in dt.Columns)
            {
                ret += "<td class=" + (char)34 + "tdColumnHeader" + (char)34 + ">" + col.ColumnName + "</td>";
            }
            ret+= "</tr>";
            foreach (DataRow row in dt.Rows)
            {
                ret+="<tr>";
                for (int i = 0;i < dt.Columns.Count;i++)
                {
                    ret+= "<td class=" + (char)34 + "tdCellData" + (char)34 + ">" + row[i].ToString() + "</td>";
                }
                ret+= "</tr>";
            }
            ret+= "</table>";
            return ret;
        }
    }
}

7) Create a new view under Home, and call it "Index"

@model MVCImportExcel.Models.MyViewModel
@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title></title>
    <style type="text/css">
        #tblExcel
        {
            width: 1000px;
            border: none;
        background-color: #000000;
    }
    .tdColumnHeader
    {
        padding: 2px 2px 2px 2px;
        text-align: center;
        font-family: Verdana;
        font-size: 12px;
        font-weight: bold;
        background-color: cornflowerblue;
        color: #FFFFFF;
    }
    .tdCellData
    {
        padding: 2px 2px 2px 2px;
        font-family: Verdana;
        font-size: 12px;
        background-color: aqua;
        color: #000000;
    }
</style>
</head>
<body>
    @using (Html.BeginForm(null,null,FormMethod.Post,new { enctype = "multipart/form-data" }))
    {
        <div>
            @Html.LabelFor(x => x.MyExcelFile)
            @Html.TextBoxFor(x => x.MyExcelFile, new { type = "file" })
            @Html.ValidationMessageFor(x => x.MyExcelFile)
        </div>
        <button type="submit">Upload</button>

        <br /><br />
        @Html.Raw(Model.MSExcelTable)
    }
</body>

</html>

Like I said in my comment, this will only work for XLSX files. Hope this helps you or somebody else down the road.

:) David

Get Data From An Uploaded Excel File Without Saving to File System, I found a great lightweight open source API on Codeplex for doing this called ExcelDataReader. It can  Here I will create an application where users will be able to Read Excel files programmatically. First they will upload an excel file through a file upload control, then the ASP.NET MVC code will read this excel file and show it’s content on the View in Grid form. This application contains only a Model, a Controller, a View and few CSS classes.

I do know this answer is too late. I just wanted to put this answer for all those who land on this page after googling problem. This is how to do it using ClosedXML.Excel In Visual Studio click on tools menu and expand NuGet Package Manager and then run Package manager console.Type the following command:

Install-Package ClosedXML

The Model:

namespace ExcelUploadFileDemo.Models
    {
        public class UploadFile
        {
            [Required]
            public HttpPostedFileBase ExcelFile { get; set; }
        }
    }

The Controller:

namespace ExcelUploadFileDemo.Controllers
    {
        public class HomeController : Controller
        {
            public ActionResult Index()
            {
                UploadFile UploadFile = new UploadFile();
                return View(UploadFile);
            }

            [HttpPost]
            public ActionResult Index(UploadFile UploadFile)
            {
                if (ModelState.IsValid)
                {

                    if (UploadFile.ExcelFile.ContentLength > 0)
                    {
                        if (UploadFile.ExcelFile.FileName.EndsWith(".xlsx") || UploadFile.ExcelFile.FileName.EndsWith(".xls"))
                        {
                            XLWorkbook Workbook;
                            Try//incase if the file is corrupt
                            {
                                Workbook = new XLWorkbook(UploadFile.ExcelFile.InputStream);
                            }
                            catch (Exception ex)
                            {
                                ModelState.AddModelError(String.Empty, $"Check your file. {ex.Message}");
                                return View();
                            }
                            IXLWorksheet WorkSheet = null;
                            Try//incase if the sheet you are looking for is not found
                            {
                                WorkSheet = Workbook.Worksheet("sheet1");

                            }
                            catch
                            {
                                ModelState.AddModelError(String.Empty, "sheet not found!");
                                return View();
                            }
                            WorkSheet.FirstRow().Delete();//if you want to remove ist row

                            foreach (var row in WorkSheet.RowsUsed())
                            {
                                //do something here
                                row.Cell(1).Value.ToString();//Get ist cell. 1 represent column number

                            }
                        }
                        else
                        {
                            ModelState.AddModelError(String.Empty, "Only .xlsx and .xls files are allowed");
                            return View();
                        }
                    }
                    else
                    {
                        ModelState.AddModelError(String.Empty, "Not a valid file");
                        return View();
                    }
                }
                return View();
            }
        }
    }

This link has many examples showing different ways of handling diverse stuff of excel.

https://github.com/ClosedXML/ClosedXML/tree/9ac4d868a313f308b82e94617b9cc2d28baeb1c3/ClosedXML

The View

@model ExcelUploadFileDemo.Models.UploadFile


@{
    ViewBag.Title = "Upload Excel File";
}
<h2>Upload an Excel File</h2>

@using (Html.BeginForm(null, null, FormMethod.Post, new { enctype = "multipart/form-data" }))
{

    @Html.AntiForgeryToken();

    <div class="form-horizontal">
        @Html.ValidationSummary("", new { @class = "text-danger" });
        <div class="form-group">
            @Html.LabelFor(model => model.ExcelFile, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.TextBoxFor(model => model.ExcelFile, new { type = "file", @class = "form-control" })
                @Html.ValidationMessageFor(model => model.ExcelFile, "", new { @class = "text-danger" })
            </div>
        </div>
        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type = "submit" value="Submit" class="btn btn-default" />
            </div>
        </div>
  </div>
}

Read Data From Excel File (xls, xlsx, csv) In ASP.NET MVC, Read Data From Excel File (xls, xlsx, csv) In ASP. ImportExcel1 and decorate it with [HttpPost] Attribute. The following is the code to read excel files. Count;; <tr>; <td colspan='@count' style="color:red;" >; No Data Found. Many a times we are in the need of reading the MS Excel file and populate them into the GridView or validating the data of the excel sheet. In this article we are going to see how to populate the MS Excel data into the GridView and loop through them.

To read that data from Excel file you need to install a nuget package called ExcelDataReader package.When installing ExcelDataReader choose V2.1.2.3. Otherwise somtimes getting error with following code.

1.Create controller called ReadExcel

2.And your Index.cshtml view add following code line.

@{
    ViewBag.Title = "Read data from excel and view";
}

<h2>Read data from excel and view</h2>

@using (Html.BeginForm("Index", "ReadExcel", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    @Html.AntiForgeryToken();
    @Html.ValidationSummary();

    <label class="text-info">Upload Excel File</label>
    <input type="file" class="form-control" name="uploadfile" id="uploadfile" />
    <input type="submit" value="submit" class="btn btn-default" />

    if (Model != null)
    {
        <table class="table table-responsive table-bordered">
            <thead>
                <tr>
                    @foreach(DataColumn column in Model.Columns)
                    {
                        <th>@column.ColumnName</th>
                    }
                </tr>
            </thead>
            <tbody>
                @foreach(DataRow row in Model.Rows)
                {
                    <tr>
                        @foreach(DataColumn col in Model.Columns)
                        {
                            <td>@row[col.ColumnName]</td>
                        }
                    </tr>
                }
            </tbody>
        </table>
    }
}

In your controller add following code snippet.

public class ReadExcelController : Controller
    {
        // GET: ReadExcel
        public ActionResult Index()
        {
            return View();
        }
        [HttpPost]
        [ValidateAntiForgeryToken]
        public ActionResult Index(HttpPostedFileBase uploadfile)
        {
            if (ModelState.IsValid)
            {
                if (uploadfile != null && uploadfile.ContentLength > 0)
                {
                    //ExcelDataReader works on binary excel file
                    Stream stream = uploadfile.InputStream;
                    //We need to written the Interface.
                    IExcelDataReader reader = null;
                    if (uploadfile.FileName.EndsWith(".xls"))
                    {
                        //reads the excel file with .xls extension
                        reader = ExcelReaderFactory.CreateBinaryReader(stream);
                    }
                    else if (uploadfile.FileName.EndsWith(".xlsx"))
                    {
                        //reads excel file with .xlsx extension
                        reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                    }
                    else
                    {
                        //Shows error if uploaded file is not Excel file
                        ModelState.AddModelError("File", "This file format is not supported");
                        return View();
                    }
                    //treats the first row of excel file as Coluymn Names
                    reader.IsFirstRowAsColumnNames = true;
                    //Adding reader data to DataSet()
                    DataSet result = reader.AsDataSet();
                    reader.Close();
                    //Sending result data to View
                    return View(result.Tables[0]);
                }
            }
            else
            {
                ModelState.AddModelError("File","Please upload your file");
            }
            return View();
        }
    }

c How to read Excel File and populate its data in Grid in MVC , c# - How to read Excel File and populate its data in Grid in MVC without storing the Excel File anywhere? c#. Here Mudassar Ahmed Khan has explained with an example, how to upload, read and display Excel file data using WebGrid in ASP.Net MVC Razor. The uploaded Excel file data will be read using OLEDB library into a DataSet and then the DataSet will be used to populate WebGrid in ASP.Net MVC Razor. TAGs: ASP.Net, Excel, DataTable, DataSet, MVC, WebGrid, OLEDB

How to Read Excel in ASP.NET MVC and show it in Grid manner , c How to read Excel File and populate its data in Grid in MVC without storing the Excel File anywhere? Question. In this post, we will see how to upload and read both .xls and .xlsx excel files without any server dependencies in ASP.NET MVC. We will use ExcelDataReader, an Open Source project to read excel files. 1. Create ASP.NET MVC 5 Empty Project. 2. To install ExcelDataReader, run the following command in the Package Manager Console:

Upload and Read Excel File (.xls, .xlsx) in ASP.NET MVC, NET MVC code will read this excel file and show it's content to Import Excel file's Data to Database and Export Data from Fill(dataTable); This HTML table is stored in a ViewBag. This will allow you to upload Excel file for import. Now for importing Excel file, we will use "Microsoft.Jet.OLEDB.4.0" (for Excel fiel 97-03 format) & "Microsoft.ACE.OLEDB.12.0". The core part of importing Excel file is to use the below two connections strings:

Import and Export excel in ASP.NET Core 2.0 Razor Pages, How to upload and read both .xls and .xlsx excel files in ASP.NET MVC and C# without ACE Database  In this article, we will see how to display data from an Excel spreadsheet (xlx, xlsx, csv) using ASP.NET MVC. We will connect to a Microsoft Excel workbook using the OLEDB.NET data provider, extract data and then display the data in a View. Firstly, we will create an ImportExcel in Home Controller which returns a View.This method will return a

Comments
  • And what have you tried so far?
  • Are you asking how to read excel data from a stream in general, or how to upload files using controller actions? Have you created an Upload action, as shown here ? This question is too broad, it's actually asking 4 different things
  • jumpingcode..I am new to MVC henace finding it bit difficult with Views and Controllers.. I have just created the controls and am browsing to how to do the needful..
  • Panagiotis Kanavos ... Thanks for your help... I have clicked on the Link u mentioned. It helps some how.. My Question is.. after we browse for the Excel sheet we want to upload, There will be another button. On Click of this button, Whatever value be there in the Excel sheet, should be Populated in a Grid in the UI.
  • I want data to be populated in UI same as an excel file. I mean if excel file has multiple sheets, I should be able to select the sheet and also I be able to modify the data in that file. How can I achieve this? Are there any open source tools for this?
  • Dear Friends,I need a Solution for this... Everywhere I searched, this task is being achived using OLEDB or Database Connection. I have to achive it without using any connection. Please help me out. I have searched nearly 100 Sites by now.
  • I am sorry I didn't see your comment about using MVC. I can post you some code on how to do what you need, but there are two caveats: 1) OpenXML SDK only uses .XLSX files. If you need support for .XLS files I am afraid I don't have an answer for you with OpenXML. 2) You can't use a traditional ASP.NET Gridview with MVC. I can post code on how to upload your .XLSX file and convert it to a DataSet. However, you are going to have to still have to manually scroll through the rows of the DataTable and create an HTML table. Would this be sufficient for you?
  • Thanks David P. It has worked. Now I am able to Upload the Excel file and read it in a Grid using MVC. The Colors of the Grid are funky though !! :D
  • Glad it worked. You can modify the CSS in the view to change the table's appearance.
  • Ya I can change the colors too now !! :)