Import data from Excel to HTML Table in ASP.Net Core MVC using ClosedXML

AliYilmaz
 
on Jun 08, 2022 11:04 PM
Sample_204861.zip
1788 Views
Answered

My goal here is to read data from excel and show the data to the following html table.

In other words, I will pull the data from the Model and put it in the List from this model, and then I will draw the data to the table. In this case, how should the class construction be?

I shared my codes. Can you make an example accordingly?

Also, how should I research to learn such structures?

Classes 

public class Dusumler : BaseEntity
{
    public string BeyannameNO { get; set; }
    public string StokKodu { get; set; }
    public int KoliAdet { get; set; }
}

public class DusumDto
{
    public string BeyannameNO { get; set; }
    public string StokKodu { get; set; }
    public int KoliAdet { get; set; }
}

Web Project Models
public class VM_Dusum
{
    public List<Dusumlers> Dusumler { get; set; }
}

Controller 

[HttpPost]
public IActionResult Index(IFormFile file)
{
    string path = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot\\Files");
    if (!Directory.Exists(path))
        Directory.CreateDirectory(path);

    FileInfo fileInfo = new FileInfo(file.FileName);
    string fileName = file.FileName;

    string fileNameWithPath = Path.Combine(path, fileName);
    //var stream = new FileStream(fileNameWithPath, FileMode.Create);
    using (var stream = new FileStream(fileNameWithPath, FileMode.Create))
    {
        file.CopyToAsync(stream);
    }

    WorkBook workbook = WorkBook.Load(System.IO.File.OpenRead(fileNameWithPath));
    WorkSheet sheet = workbook.WorkSheets.First();

    string stok = sheet["A2"].StringValue;
    string artikeladi = sheet["B2"].StringValue;
    string bno = sheet["C2"].StringValue;
    string ticaritanim = sheet["D2"].StringValue;
    string karisim = sheet["E2"].StringValue;
    decimal agirlik = sheet["F2"].DecimalValue;
    string mensei = sheet["G2"].StringValue;
    string gtip = sheet["H2"].StringValue;
    string marka = sheet["I2"].StringValue;
    string artikel = sheet["J2"].StringValue;
    string kumaskodu = sheet["K2"].StringValue;
    decimal birimfiyat = sheet["L2"].DecimalValue;
    decimal toplamfiyat = sheet["M2"].DecimalValue;
    int adet = sheet["N2"].IntValue;
    int koliadet = sheet["O2"].IntValue;

    Dusumlers dusum = new Dusumlers();

    dusum.StokKodu = stok;
    dusum.ArtikelName = artikeladi;
    dusum.BeyannameNO = bno;
    dusum.TicariTanim = ticaritanim;
    dusum.Karisim = karisim;
    dusum.Agirlik = agirlik;
    dusum.Mensei = mensei;
    dusum.GtipNo = gtip;
    dusum.Marka = marka;
    dusum.Artikel = artikel;
    dusum.KumasKodu = kumaskodu;
    dusum.BirimFiyat = birimfiyat;
    dusum.ToplamFiyat = toplamfiyat;
    dusum.Adet = adet;
    dusum.KoliAdet = koliadet;

    VM_Dusum dusums = new VM_Dusum();
    dusums.Dusumler.Add(dusum);

    return View();
}

View 

@{
    ViewData["Title"] = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
}
<div class="content-page">
    <div class="content">
        <div class="container-fluid card-box table-responsive">
            <div class="col-md-6">
                <form asp-controller="Dusum" asp-action="Index" enctype="multipart/form-data">
                    <div class="form-group">
                        <p>Düşüm ekle</p>
                        <input type="file" name="file" class="filestyle" id="filestyleicon">
                        <button type="submit" style="margin-left:10px;" class="text-right btn btn-primary">Önizleme</button>
                    </div>
                </form>
            </div>
            <div class="col-md-12">
                <div class="table-responsive">
                    <div class="table-responsive">
                        <table class="table table-bordered mb-0">
                            <thead>
                                <tr>
                                    <th>Stok Kodu</th>
                                    <th>Artikel Adı</th>
                                    <th>Beyanname No</th>
                                    <th>Ticari Tanım</th>
                                    <th>Karışım</th>
                                    <th>Ağırlık</th>
                                    <th>Menşei</th>
                                    <th>Gtip No</th>
                                    <th>Marka</th>
                                    <th>Artikel</th>
                                    <th>Kumaş Kodu</th>
                                    <th>Birim Fiyat</th>
                                    <th>Toplam Fiyat</th>
                                    <th>Adet</th>
                                    <th>Koli Adet</th>
                                </tr>
                            </thead>
                            <tbody>                         
                                @if (ViewBag.Dusum != null)
                                {
                                    @foreach (var item in ViewBag.Dusum)
                                    {
                                        <tr class="bg-dark text-white">
                                            <td></td>
                                            <td></td>
                                            <td></td>
                                            <td>Gtip</td>
                                            <td>KalemSayisi</td>
                                            <td>Adet</td>
                                        </tr>
                                    }
                                }
                            </tbody>
                        </table>
                    </div>
                </div>
            </div>
        </div>
    </div>
</div>
Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jun 30, 2022 07:02 AM
Answer

Hi AliYilmaz,

You need to read the excel data using ClosedXml to DataTable. Then add the Rows to Generic List of class object.

Then pass the Generic List to View page.

For configuring and installing ClosedXml refer below article.

ASP.Net Core MVC: Export DataSet (DataTable) to Excel file

Refer below example.

Model

public class Customer
{
    public int CustomerId { get; set; }
    public string Name { get; set; }
    public string Country { get; set; }
}

Namespaces

using System.IO;
using System.Data;
using System.Linq;
using ClosedXML.Excel;
using Microsoft.AspNetCore.Http;

Controller

public class HomeController : Controller
{
    public IActionResult Index()
    {
        return View();
    }

    [HttpPost]
    public IActionResult Index(IFormFile posteFile)
    {
        DataTable dt = new DataTable();
        using (MemoryStream stream = new MemoryStream())
        {
            posteFile.CopyTo(stream);
            using (XLWorkbook workBook = new XLWorkbook(stream))
            {
                IXLWorksheet workSheet = workBook.Worksheet(1);
                bool firstRow = true;
                foreach (IXLRow row in workSheet.Rows())
                {
                    if (firstRow)
                    {
                        foreach (IXLCell cell in row.Cells())
                        {
                            dt.Columns.Add(cell.Value.ToString());
                        }
                        firstRow = false;
                    }
                    else
                    {
                        dt.Rows.Add();
                        int i = 0;
                        foreach (IXLCell cell in row.Cells())
                        {
                            dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                            i++;
                        }
                    }
                }
            }
        }

        List<Customer> customers = (from DataRow dr in dt.Rows
                                    select new Customer()
                                    {
                                        CustomerId = Convert.ToInt32(dr["Id"]),
                                        Name = dr["Name"].ToString(),
                                        Country = dr["Country"].ToString()
                                    }).ToList();

        return View(customers);
    }
}

View

@using Export_Excel_Core_MVC.Models;
@model IEnumerable<Customer>
@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers

@{
    Layout = null;
}

<!DOCTYPE html>

<html>
<head>
    <meta name="viewport" content="width=device-width" />
    <title>Index</title>
</head>
<body>

    <form method="post" asp-controller="Home" asp-action="Index" enctype="multipart/form-data">
        <input type="file" name="posteFile" accept=".xlsx">
        <input type="submit" value="Import" />
    </form>
    <hr />
    @if (Model != null)
    {
        <table cellpadding="0" cellspacing="0">
            <tr>
                <th>Id</th>
                <th>Name</th>
                <th>Country</th>
            </tr>
            @foreach (Customer customer in Model)
            {
                <tr>
                    <td>@customer.CustomerId</td>
                    <td>@customer.Name</td>
                    <td>@customer.Country</td>
                </tr>
            }
        </table>
    }
</body>
</html>

Screenshot

The Excel File

Displaying Excel data

Disclaimer
This site is started with intent to serve the ASP.Net Community by providing forums (question-answer) site where people can help each other. The content posted here is free for public and is the content of its poster. The site does not provide any warranties for the posted content. If you feel any content is violating any terms please contact.
© 2023 www.aspsnippets.com All rights reserved | Privacy Policy | Powered by Excelasoft Solutions Excelasoft Solutions