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

Question

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.

Answer 1

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

Answer 2

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

Answer 3

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();
        }
    }

Answer 4

Update to Sachith's answer.

reader.IsFirstRowAsColumnNames = true; and reader.AsDataSet() are no more valid with latest ExcelDataReader v 3.6. You will need to install ExcelDataReader.DataSet via nugget as well. If your first row is header then you will need to use Dataset like this.

DataSet result = reader.AsDataSet(new ExcelDataSetConfiguration()
{
 ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
  {
   UseHeaderRow = true
  }
});