// Pass to param the excel file location (path)
public DataTable Read(string filePath)
{
try
{
DataTable dt = new DataTable();
FileInfo excelFile = new FileInfo(filePath);
ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
using (var package = new ExcelPackage(excelFile))
{
ExcelWorksheet workSheet = package.Workbook.Worksheets[0];
//add column header
foreach (var firstRC in workSheet.Cells[1, 1, 1, workSheet.Dimension.End.Column])
{
dt.Columns.Add(firstRC.Text);
}
// add rows
for (int rN = 2; rN <= workSheet.Dimension.End.Row; rN++)
{
ExcelRange row = workSheet.Cells[rN, 1, rN, workSheet.Dimension.End.Column];
DataRow newR = dt.NewRow();
foreach (var cell in row)
{
newR[cell.Start.Column - 1] = cell.Text;
}
dt.Rows.Add(newR);
}
}
return dt;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}