ASP.NET Core

import excel in dotnet core

upload excel and save it in database dotnet core

This explains you how to import data from excel and upload same in database with bulk upload in ASP.Net Core. We always think of third party library for this type of task.

For upload excel one is simple library that is OfficeOpenXml so for that you need to install EPPlus

pm> Install-Package EPPlus -Version 4.5.3.1

Follow the below code for import excel and bulk upload to database in ASP.NET Core

Your Index.cshtml

<form method="post" asp-action="ImportUpload" enctype="multipart/form-data">
    <div class="row">
        <div class="col-md-4">
            <input type="file" id="reportfile" name="reportfile" class="form-control" />
        </div>
        <div class="col-md-8">
            <input type="submit" id="btnUpload" value="Upload" />
        </div>
    </div>
</form>

Your controller

public class FImportExcelController : Controller
    {
        private readonly EntitiesContext _db;
        private readonly IHostingEnvironment _hostingEnvironment;
        public FImportExcelController(IHostingEnvironment hostingEnvironment, EntitiesContext db)
        {
            _db = db;
            _hostingEnvironment=hostingEnvironment;
        }
        public IActionResult Index()
        {
            return View();
        }
        [HttpPost]
        [Route("ImportUpload")]
        public string ImportUpload(IFormFile reportfile)
        {
            string folderName = "Upload";
            string webRootPath = _hostingEnvironment.WebRootPath;
            string newPath = Path.Combine(webRootPath, folderName);
            // Delete Files from Directory
            System.IO.DirectoryInfo di = new DirectoryInfo(newPath);
            foreach (FileInfo filesDelete in di.GetFiles())
            {
                filesDelete.Delete();
            }// End Deleting files form directories

            if (!Directory.Exists(newPath))// Crate New Directory if not exist as per the path
            {
                Directory.CreateDirectory(newPath);
            }
            var fiName = Guid.NewGuid().ToString() + Path.GetExtension(reportfile.FileName);
            using (var fileStream = new FileStream(Path.Combine(newPath, fiName), FileMode.Create))
            {
                reportfile.CopyTo(fileStream);
            }
            // Get uploaded file path with root
            string rootFolder = _hostingEnvironment.WebRootPath;
            string fileName = @"Upload/" + fiName;
            FileInfo file = new FileInfo(Path.Combine(rootFolder, fileName));
            
            using (ExcelPackage package = new ExcelPackage(file))
            {
                ExcelWorksheet workSheet = package.Workbook.Worksheets[1];
                int totalRows = workSheet.Dimension.Rows;
                List<Category> reportList = new List<Category>();
                for (int i = 2; i <= totalRows; i++)
                {
                    try
                    {
                        string Title = workSheet?.Cells[i, 1]?.Value?.ToString();
                        string Url = workSheet?.Cells[i, 2]?.Value?.ToString();                       
                        reportList.Add(new Category
                        {  
                            Title = Title,
                            Url = Url,
                        });
                    }
                    catch (Exception Ex)
                    {
                        // Exception
                    }
                }
                _db.Category.AddRange(reportList);
                _db.SaveChanges();
                return "Uploaded";
            }
        }
    }

 

Tags
Show More

Related Articles

Leave a Reply

Your email address will not be published. Required fields are marked *

Close