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