[Solved] ASP.Net Core MVC: Skip Empty rows while reading data from Excel to DataTable using ClosedXML

AliYilmaz
 
on Jul 04, 2022 11:42 PM
Sample_942516.zip
1925 Views
Answered

Hi,

In Core MVC i am reading data from Excel, but there is a problem. Even though there is no row in Excel, it throws blank rows.

What is the reason for this and how can I prevent it. It just returns the filled rows.

For example, when there are 4 rows. When I check, 1 line or more than 1 line results, the data always comes null.

Controller

XLWorkbook workbook = new XLWorkbook(fileNameWithPath);
IXLWorksheet workSheet = workbook.Worksheet(2);
 
DataTable dt = new DataTable();
bool firstRow = true;
foreach (var row in workSheet.Rows())
{
    if (firstRow)
    {
 
        foreach (var item in row.Cells())
        {
            dt.Columns.Add(item.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<DusumDto> dusum = (from DataRow dr in dt.Rows
                        select new DusumDto()
                        {
                            StokKodu = dr["STOK KODU"].ToString(),
                            ArtikelName = dr["ARTIKEL ADI"].ToString(),
                            BeyannameNO = dr["BEYANNAME"].ToString(),
 
                            TicariTanim = dr["TICARI TANIM"].ToString(),
                            Karisim = dr["KARISIM"].ToString(),                                  
 
                            Agirlik = (decimal)(dr["AGIRLIK"]),
 
                            Mensei = dr["MENSEI"].ToString(),
                            GtipNo = dr["GTIP NO"].ToString(),
                            Marka = dr["MARKA"].ToString(),
 
                            Artikel = dr["ARTIKEL"].ToString(),
                            KumasKodu = dr["KUMAS KODU"].ToString(),
                            //BirimFiyat = Convert.ToDecimal(dr["BIRIM FIYAT"]),
 
 
                            //ToplamFiyat = Convert.ToDecimal(dr["TOPLAM FIYAT"]),
                            //Adet = Convert.ToInt32(dr["ADET"]),
                            //KoliAdet = Convert.ToInt32(dr["KOLİ ADET"]),
 
                        }).ToList();
VM_Dusum dusums = new VM_Dusum();
 
dusums.Dusumler = dusum;

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Jul 05, 2022 06:47 AM
Answer

Hi AliYilmaz,

To overcome this issue you need to verify each cell of the row is empty or not.

If its empty then don't add the row to DataTable.

Refer below code.

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
                    {
                        bool isEmpty = true;
                        foreach (IXLCell cell in row.Cells())
                        {
                            if (!string.IsNullOrEmpty(cell.Value.ToString().Trim()))
                            {
                                isEmpty = false;
                                break;
                            }
                        }
                        if (!isEmpty)
                        {
                            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 = dr["Id"].ToString(),
                                        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