Read an excel file on asp.net core 1.0

Question

Hello I`m trying to upload and read an excel file on my asp.net project but all the documentation I find is for ASP MVC 5. My goal is to read the excel sheet and pass the values to an list of objects.

This is my controller, it works for upload the file to my wwwroot/uploads

public class HomeController : Controller
{
    private IHostingEnvironment _environment;

    public HomeController(IHostingEnvironment environment)
    {
        _environment = environment;
    }

    public IActionResult index()
    {
        return View();
    }



    [HttpPost]
    public async Task<IActionResult> Index(ICollection<IFormFile> files)
    {
        var uploads = Path.Combine(_environment.WebRootPath, "uploads");
        foreach (var file in files)
        {
            if (file.Length > 0)
            {
                using (var fileStream = new FileStream(Path.Combine(uploads,    file.FileName), FileMode.Create))
                {
                    await file.CopyToAsync(fileStream);
                }
            }
        }
        return View();
    }

Solution 1

Open the package manager console in Visual Studio and type:

PM> Install-Package EPPlus.Core

Writing files is then as simple as:

public void WriteExcel(string fileName)
{


    FileInfo file = new FileInfo(fileName);
    /// overwrite old file
    if (file.Exists)
    {
        file.Delete();
        file = new FileInfo(fileName);
    }
    using (ExcelPackage package = new ExcelPackage(file))
    {
        // add a new worksheet to the empty workbook
        ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Employee");
        worksheet.Cells["A1"].Value = "HELLO WORLD!!!";
        package.Save(); 
    }
}

More examples here: http://www.talkingdotnet.com/import-export-xlsx-asp-net-core/

Solution 2

In .NET Core OleDb and DataTables are gone. This makes it harder for some projects to port themselves to netcore.

If you're reading OpenXml Excel files (xlsx), the unofficial Epplus.Core can help you.

But for the older 97-2003 formats (xls) we couldn't find a solution yet.

I would hope either NPOI or ExcelDataReader would get a core version later this year, but i don't see much activity on that direction.

Solution 3

As stated in some comments and answers, there is an unnoficial EPPlus port to .Net Core (I've checked right now and it supports Core 1.0, 1.1 and 2.0)...

This code demonstrates how to do it

var filePath = @"D:/test.xlsx";
FileInfo file = new FileInfo(filePath);

using (ExcelPackage package = new ExcelPackage(file))
{       
    ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
    int rowCount = worksheet.Dimension.Rows;
    int ColCount = worksheet.Dimension.Columns;

    var rawText = string.Empty;
    for (int row = 1; row <= rowCount; row++)
    {
        for (int col = 1; col <= ColCount; col++)
        {   
            // This is just for demo purposes
            rawText += worksheet.Cells[row, col].Value.ToString() + "\t";    
        }
        rawText+="\r\n";
    }
    _logger.LogInformation(rawText);
}

Note: today I used at work and I created a dictionary mathing column numbers with column names to access directly to each column, given the name, row by row and the performance was better than using some other libraries aimed to read csv files.

Few links:

• EPPlus.Core: https://github.com/VahidN/EPPlus.Core
• A HowTo with this concrete example: https://www.codeproject.com/Articles/1217036/Console-Logging-and-Reading-Excel-Files-with-NET-C

I hope it helps,

Juan

Solution 4

Add the following package:

Install-Package Syncfusion.XlsIO.Net.Core -Version 17.2.0.49

Method to read excel file:

 private DataTable ConvertExcelToDataTable(string path)
    {
            using (Stream inputStream = File.OpenRead(path))
            {
                using (ExcelEngine excelEngine = new ExcelEngine())
                {
                    IApplication application = excelEngine.Excel;
                    IWorkbook workbook = application.Workbooks.Open(inputStream);
                    IWorksheet worksheet = workbook.Worksheets[0];

                    DataTable dataTable = worksheet.ExportDataTable(worksheet.UsedRange, ExcelExportDataTableOptions.ColumnNames);
                    return dataTable;
                }
            }
    }

View more solutions