How to read excel file using EPPlus Core (.NET Core)

When you’re working on excel file uploads or populating data to an excel (for downloads) this is the tool for you if you wanted to use .NET Core. Now there are two versions of this Nuget package one would be for regular NET framework and this one specifically (plus) pertains to only NET Core.

Here’s how you do it. First you need to create a project (as we already know how to do) you can use MVC but for now we’ll stick with console to be short.

Here’s the Nuget installation command Install-Package EPPlus -Version 4.5.2.1

That is the latest version of nuget as of this writing.

Here’s the code to implement it.

class Program
{
static void Main(string[] args)
{
var dtContent = GetDataTableFromExcel(@”c:\temp\test.xlsx”);
//var res = from DataRow dr in dtContent.Rows
// where (string)dr[“Name”] == “Gil”
// select ((string)dr[“Section”]).FirstOrDefault();

foreach(DataRow dr in dtContent.Rows)
{
Console.WriteLine(dr[“Name”].ToString());
}

Console.ReadLine();
}

private static DataTable GetDataTableFromExcel(string path, bool hasHeader = true)
{
using (var pck = new OfficeOpenXml.ExcelPackage())
{
using (var stream = File.OpenRead(path))
{
pck.Load(stream);
}
var ws = pck.Workbook.Worksheets.First();
DataTable tbl = new DataTable();
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format(“Column {0}”, firstRowCell.Start.Column));
}
var startRow = hasHeader ? 2 : 1;
for (int rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
{
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
DataRow row = tbl.Rows.Add();
foreach (var cell in wsRow)
{
row[cell.Start.Column — 1] = cell.Text;
}
}
return tbl;
}
}

And there it is, hope this helps ! Happy coding!