Read an excel file on asp.net core 1.0

问题:

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();
    }

回答1:

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.

回答2:

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/

回答3:

As stated in some comments and answers, there is an unnoficial EPPlus port to .Net Core (I've cheked 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

回答4:

In most cases it does not matter if you are using ASP.NET 4 or ASP.NET Core when it comes to reading Excel files. You just need to find a library that allows to do that. It is recommended to use NuGet to add such libraries into your project.

See instructions how to install a NuGet package here: https://docs.nuget.org/ndocs/guides/install-nuget

One library that I can recommend to use is EPPlus (https://www.nuget.org/packages/EPPlus).

When you are done with file uploading and have stored Excel file somewhere, you just need to open it using EPPlus for reading. Simple example follows:

var package = new ExcelPackage(new FileInfo("sample.xlsx"));

ExcelWorksheet workSheet = package.Workbook.Worksheets[0];

for (int i = workSheet.Dimension.Start.Column; i <= workSheet.Dimension.End.Column; i++)
{
    for (int j = workSheet.Dimension.Start.Row; j <= workSheet.Dimension.End.Row; j++)
    {
        object cellValue = workSheet.Cells[i, j].Value;
    }
}