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

AliYilma...

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

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;

dharmend...

0 3

Reply
ANSWER
User: dharmendr
Replied: on Jul 05, 2022 06:47 AM
Report

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

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

Company

Explore

Follow us

Contact Us