Import Excel file and display Group Sum in TextBox using Model in ASP.Net MVC

Hi mahesh213,

Refer below sample code.

Model

public class Generation
{
    public int Generation1Count { get; set; }
    public int Generation1TotalSum { get; set; }
    public int Generation2Count { get; set; }
    public int Generation2TotalSum { get; set; }
    public int Generation3Count { get; set; }
    public int Generation3TotalSum { get; set; }
}

Namespaces

using System.Configuration;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;

Controller

public class HomeController : Controller
{
    // GET: Home
    public ActionResult Index()
    {
        return View(new Generation());
    }

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

            conString = string.Format(conString, filePath);

            using (OleDbConnection connExcel = new OleDbConnection(conString))
            {
                using (OleDbCommand cmdExcel = new OleDbCommand())
                {
                    using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
                    {
                        cmdExcel.Connection = connExcel;

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

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

        DataTable result = ds.Tables[0].AsEnumerable()
        .GroupBy(r => new
        {
            MemberType = r["MemberType"],
            MemberCategory = r["MemberCategory"]
        })
        .Select(g =>
        {
            var row = g.First();
            row.SetField("TotalSum", g.Sum(r => Convert.ToInt32(r["TotalSum"])));
            row.SetField("MemberCount", g.Sum(r => Convert.ToInt32(r["MemberCount"])));
            return row;
        }).CopyToDataTable();

        Generation generation = new Generation();
        generation.Generation1Count = result.Select("MemberType='Generation1'").Length > 0 ? Convert.ToInt32(result.Select("MemberType='Generation1'")[0]["MemberCount"]) : 0;
        generation.Generation1TotalSum = result.Select("MemberType='Generation1'").Length > 0 ? Convert.ToInt32(result.Select("MemberType='Generation1'")[0]["TotalSum"]) : 0;
        generation.Generation2Count = result.Select("MemberType='Generation2'").Length > 0 ? Convert.ToInt32(result.Select("MemberType='Generation2'")[0]["MemberCount"]) : 0;
        generation.Generation2TotalSum = result.Select("MemberType='Generation2'").Length > 0 ? Convert.ToInt32(result.Select("MemberType='Generation2'")[0]["TotalSum"]) : 0;
        generation.Generation3Count = result.Select("MemberType='Generation3'").Length > 0 ? Convert.ToInt32(result.Select("MemberType='Generation3'")[0]["MemberCount"]) : 0;
        generation.Generation3TotalSum = result.Select("MemberType='Generation3'").Length > 0 ? Convert.ToInt32(result.Select("MemberType='Generation3'")[0]["TotalSum"]) : 0;

        return View("Index", generation);
    }
}

View

@model Merger_DataTable_Row_Sum_Excel_MVC.Models.Generation

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
    <script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/1.8.3/jquery.min.js"></script>
    <script type="text/javascript">
        $(function () {
            $('#fuUpload').on('change', function () {
                $('form').submit();
            });
        });
    </script>
</head>
<body>
    @using (Html.BeginForm("Upload", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
    {
        <table>
            <tr>
                <td>Select File</td>
                <td><input type="file" name="postedFile" /></td>
            </tr>
            <tr>
                <td>Generation1Count</td>
                <td>
                <td>@Html.TextBoxFor(m => m.Generation1Count, new { @id = "txtGeneration1Count" })</td>
            </tr>
            <tr>
                <td>Generation1Sum</td>
                <td>
                <td>@Html.TextBoxFor(m => m.Generation1TotalSum, new { @id = "txtGeneration1TotalSum" })</td>
            </tr>
            <tr>
                <td>Generation2Count</td>
                <td>
                <td>@Html.TextBoxFor(m => m.Generation2Count, new { @id = "txtGeneration2Count" })</td>
            </tr>
            <tr>
                <td>Generation2Sum</td>
                <td>
                <td>@Html.TextBoxFor(m => m.Generation2TotalSum, new { @id = "txtGeneration2TotalSum" })</td>
            </tr>
            <tr>
                <td>Generation3Count</td>
                <td>
                <td>@Html.TextBoxFor(m => m.Generation3Count, new { @id = "txtGeneration3Count" })</td>
            </tr>
            <tr>
                <td>Generation3Sum</td>
                <td>
                <td>@Html.TextBoxFor(m => m.Generation3TotalSum, new { @id = "txtGeneration3TotalSum" })</td>
            </tr>

        </table>
        <br />
        <input type="submit" value="Save" />
    }
</body>
</html>