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

AliYilma...

SOLVED CLOSED ACTIVE ANSWERED
User: AliYilmaz
Posted: on Jun 08, 2022 11:04 PM
Forum: ASP.Net Core MVC
Answer: 1
Views: 6760
Sample Code: Download

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

dharmend...

0 9

Reply
ANSWER
User: dharmendr
Replied: on Jun 30, 2022 07:02 AM
Report

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.

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

I agree, here is the link: https://www.e-iceblue.com/Introduce/spire-office-for-net-free.html

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

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

Company

Explore

Follow us

Contact Us