How to import Excel file with ASP.NET Core

In this post, we are going to discuss and learn how we can import Excel file with ASP.NET Core project and save the data into our database. It is very simple and easy to import Excel files in ASP.NET Core. First of all, if you have not created your project you can create your ASP.NET Core 3-tier Application following this.

Tools:

Let’s get started:

Before starting our procedure we have to create a table into our database so that we can store our read data into that table. To create a table we are using the code-first approach.

we will create a Model/Class.

public class Student
{
    public Guid Id { get; set; }
    public string Name { get; set; }
    public string Class { get; set; }
    public string Roll_No { get; set; }
}	

After Creating our class we will add a reference to this class in our “ApplicationDbContext.cs” Class.

public DbSet<Student> Students { get; set; }

Now Run migration.

Add-migration

Now we will update our database.

update-database

We have to install this NuGet Package into our project.

Install-Package ClosedXML -Version 0.95.4

Our Excel file looks like this.

import Excel file with ASP.NET Core

To read this file and save records into our database we will deal with rows and cells. we will show you the code example first then we will elaborate code in detail.

public async Task<ResponseViewModel<object>> UploadData(IFormFile file)
{
  try
  {
     var fileextension = Path.GetExtension(file.FileName);
     var filename = Guid.NewGuid().ToString() + fileextension;
     var filepath = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot","files", filename);
     using (FileStream fs = System.IO.File.Create(filepath))
     {
          file.CopyTo(fs);
     }
     int rowno = 1;
     XLWorkbook workbook = XLWorkbook.OpenFromTemplate(filepath);
     var sheets = workbook.Worksheets.First();
     var rows = sheets.Rows().ToList();
     foreach (var row in rows)
     {
        if (rowno != 1)
        {
           var test = row.Cell(1).Value.ToString();
           if (string.IsNullOrWhiteSpace(test) || string.IsNullOrEmpty(test))
           {
              break;
           }
           Student student;
           student = _context.Students.Where(s => s.Name == row.Cell(1).Value.ToString()).FirstOrDefault();
           if (student == null)
           {
               student = new Student();
           }
           student.Name = row.Cell(1).Value.ToString();
           student.Class = row.Cell(2).Value.ToString();
           state.Roll_No = row.Cell(3).Value.ToString();
           if (student.Id == Guid.Empty)
               _context.Students.Add(student);
           else
               _context.Students.Update(student);
        }
        else
        {
           rowno = 2;
        }
     }
     _context.SaveChanges();
     return new ResponseViewModel<object>
     {
         Status = true,
         Message = "Data Updated Successfully",
         StatusCode = System.Net.HttpStatusCode.OK.ToString()
      };
 }
 catch (Exception e)
 {
    throw e;
  }
}

The above method that we have mentioned is receiving an Excel file as an argument. The method is saving the file in “wwwroot” and reading data. After reading the record we have applied foreach loop on rows. We are ignoring the first row intentionally because the first row will always be a header row. we check if there is a record that already exists in our database if it already exists we will update it and if it doesn’t we add this into our database. We have also initialized the test variable at the beginning that will break the loop when all records will be added and an empty cell will come.

Conclusion:

Try this tutorial “How to import Excel file with ASP.NET Core” and if you do not understand anything or face any issue while applying this method. Do not hesitate to comment below. My Code Bit team will try to respond ASAP.

See Also:

Send Emails in ASP.NET Core 5 Using Gmail API

What Is File Size Upload Limit In NFT Minting? Solution

Email Confirmation with ASP.NET Core Identity

How to Implement JWT Authentication in Asp.Net Core Web API

How to Send Emails ASP.NET Core 5 Using SMTP

Leave a Reply

Your email address will not be published.