uploading and reading from an excel file in asp.net core 2

Question

previously Asp.Net MVC had this third party library which easily allowed uploading and reading from an excel file called Excel Data Reader. We didn't need to have the file on the local disk, which was great because my application needs to run on Azure.

However we are now porting this functionality to asp.net core 2, and it seems from searching that this is not possible. Does anybody know any libraries that would allow me to do this? Please note, I am not looking for solutions that read from a disk. I want to upload an excel file and read data from the stream directly.

Are you talking about this library: https://github.com/ExcelDataReader/ExcelDataReader If so, it looks like its compatible with netstandard 2.0 and at the bottom, there's a note regarding .NET Core — Vlince, Apr 04 '18 at 19:36
yeah but I don't think that note addresses the crux of my problem. — Riz, Apr 04 '18 at 20:12

Amin Golmahalleh · Answer 1 · 2021-02-13T15:51:21.833

I Could Read Excel File In 'Asp .Net Core' By This Code.

Import And Export Data Using EPPlus.Core.

    [HttpPost]
    public IActionResult ReadExcelFileAsync(IFormFile file)
    {
        if (file == null || file.Length == 0)
            return Content("File Not Selected");

        string fileExtension = Path.GetExtension(file.FileName);
        if (fileExtension != ".xls" && fileExtension != ".xlsx")
            return Content("File Not Selected");

        var rootFolder = @"D:\Files";
        var fileName = file.FileName;
        var filePath = Path.Combine(rootFolder, fileName);
        var fileLocation = new FileInfo(filePath);

        using (var fileStream = new FileStream(filePath, FileMode.Create))
        {
            await file.CopyToAsync(fileStream);
        }

         if (file.Length <= 0)
             return BadRequest(GlobalValidationMessage.FileNotFound);  

         using (ExcelPackage package = new ExcelPackage(fileLocation))
         {
          ExcelWorksheet workSheet = package.Workbook.Worksheets["Table1"];
          //var workSheet = package.Workbook.Worksheets.First();
          int totalRows = workSheet.Dimension.Rows;

          var DataList = new List<Customers>();

          for (int i = 2; i <= totalRows; i++)
           {
                  DataList.Add(new Customers
                    {
                   CustomerName = workSheet.Cells[i, 1].Value.ToString(),
                   CustomerEmail = workSheet.Cells[i, 2].Value.ToString(),
                   CustomerCountry = workSheet.Cells[i, 3].Value.ToString()
                   });
           }

                _db.Customers.AddRange(customerList);
                _db.SaveChanges();
            }
        return Ok();
}

Please View This Link: https://stackoverflow.com/questions/36637882/epplus-read-excel-table — Amin Golmahalleh, May 20 '19 at 04:54
Hi ammin, I didnt realize we have the same logic for reading excel data. Is it possible to read a worksheet without knowing or specifying the sheet name here: package.Workbook.Worksheets? — Veck, Jul 07 '22 at 11:45
Hi Veck. I think it's possible. just put a loop on worksheets. — Amin Golmahalleh, Nov 09 '22 at 07:48

score 1 · Answer 2 · answered May 03 '20 at 03:23

I tried this code below (without using libs) for ASP.NET Core and it worked:

public ActionResult OnPostUpload(List<IFormFile> files)
    {
        try
        {
            var file = files.FirstOrDefault();
            var inputstream = file.OpenReadStream();

            XSSFWorkbook workbook = new XSSFWorkbook(stream);

            var FIRST_ROW_NUMBER = {{firstRowWithValue}};

            ISheet sheet = workbook.GetSheetAt(0);
            // Example: var firstCellRow = (int)sheet.GetRow(0).GetCell(0).NumericCellValue;

            for (int rowIdx = 2; rowIdx <= sheet.LastRowNum; rowIdx++)
               {
                  IRow currentRow = sheet.GetRow(rowIdx);

                  if (currentRow == null || currentRow.Cells == null || currentRow.Cells.Count() < FIRST_ROW_NUMBER) break;

                  var df = new DataFormatter();                

                  for (int cellNumber = {{firstCellWithValue}}; cellNumber < {{lastCellWithValue}}; cellNumber++)
                      {
                         //business logic & saving data to DB                        
                      }               
                }
        }
        catch(Exception ex)
        {
            throw new FileFormatException($"Error on file processing - {ex.Message}");
        }
    }

Irf · Answer 3 · 2020-07-08T20:59:38.680

if we are talking about Razor Pages, here's a simple sample that I tested today..

Environ: .NET Core 3.1, VS 2019

A simple class

public class UserModel
{
    public string Name { get; set; }
    public string City { get; set; }
}

Index.cshtml.cs

usings..
using ExcelDataReader;

public void OnPost(IFormFile file)
    {
        List<UserModel> users = new List<UserModel>();
        System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
        using (var stream = new MemoryStream())
        {
            file.CopyTo(stream);
            stream.Position = 0;
            using (var reader = ExcelReaderFactory.CreateReader(stream))
            {
                while (reader.Read()) //Each row of the file
                {
                    users.Add(new UserModel { Name = reader.GetValue(0).ToString(), City = reader.GetValue(1).ToString()});
                }
            }
        }
        //users // you got the values here
}

Mark up in View

<form id="form1" method="post" enctype="multipart/form-data">
<div class="text-center">

    <input type="file" id="file1" name="file" />

</div>

<script>
    document.getElementById('file1').onchange = function () {
       document.getElementById('form1').submit();
     };
        </script>

You would require ExcelDataReader nuget package, I used 3.6.0 version

github working code

Thanks you so much! Saved my day. Wanted to highlight the importance of line System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance); in above sample. — user2330678, Nov 23 '20 at 22:47

score 0 · Answer 4 · answered Apr 05 '18 at 15:16

0

Latest versions of ExcelDataReader support netstandard2.0, thus work with ASP.NET Core 2. It also targets netstandard1.3, so works with ASP.NET Core 1.x as well.

(not sure what you searched that said it is not possible, but that is clearly wrong)

answered Apr 05 '18 at 15:16

user8728340

646
5
7

score -1 · Answer 5 · answered Oct 08 '20 at 16:30

First upload your excel file and read the excel file record using asp.net core 3.1.

using System;
using Microsoft.AspNetCore.Mvc;
using ExcelFileRead.Models;
using Microsoft.AspNetCore.Hosting;
using System.IO;
using OfficeOpenXml;
using System.Linq;

namespace ExcelFileRead.Controllers
 {
 public class HomeController : Controller
  {

    private readonly IHostingEnvironment _hostingEnvironment;
    public HomeController(IHostingEnvironment hostingEnvironment)
    {
        _hostingEnvironment = hostingEnvironment;
    }
    public ActionResult File()
    {
        FileUploadViewModel model = new FileUploadViewModel();
        return View(model);
    }
    [HttpPost]
    public ActionResult File(FileUploadViewModel model)
    {
        string rootFolder = _hostingEnvironment.WebRootPath;
        string fileName = Guid.NewGuid().ToString() + model.XlsFile.FileName;
        FileInfo file = new FileInfo(Path.Combine(rootFolder, fileName));
        using (var stream = new MemoryStream())
        {
            model.XlsFile.CopyToAsync(stream);
            using (var package = new ExcelPackage(stream))
            {
                package.SaveAs(file);
            }
        }

        using (ExcelPackage package = new ExcelPackage(file))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets.FirstOrDefault();
            if (worksheet == null)
            {

                //return or alert message here
            }
            else
            {

                var rowCount = worksheet.Dimension.Rows;
                for (int row = 2; row <= rowCount; row++)
                {
                    model.StaffInfoViewModel.StaffList.Add(new StaffInfoViewModel
                    {
                        FirstName = (worksheet.Cells[row, 1].Value ?? string.Empty).ToString().Trim(),
                        LastName = (worksheet.Cells[row, 2].Value ?? string.Empty).ToString().Trim(),
                        Email = (worksheet.Cells[row, 3].Value ?? string.Empty).ToString().Trim(),
                    });
                }
                
            }
        }
        return View(model);
    }

  }
}

For more details(step by step)

https://findandsolve.com/articles/how-to-read-column-value-from-excel-in-aspnet-core-or-best-way-to-read-write-excel-file-in-dotnet-core

uploading and reading from an excel file in asp.net core 2

5 Answers5

Linked