Import data from Excel in Asp.Net

ExcelDataReader

Excel Data Reader is a c# library which is used for reading data from excel in table format and we can fill the data in DataTable or DataSet. So , for installing ExcelDataReader. You have to go to

Project -> Manage Nugget Packages 

Now add two nugget packages. ExcelDataReader and ExcelDataReader.DataSet . You can check in the given image

So, after you have added these two nugget packages in your project. You have to go to your c# code and use this

private DataTable ReadData()
        {
            var filePath = HttpContext.Current.Server.MapPath("~/ExcelFile/Test.xlsx");
            var dataTable = new DataTable();
            using (FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read))
            {
                IExcelDataReader excelReader;
                if (Path.GetExtension(filePath).ToUpper() == ".XLS")
                {
                    excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
                }
                else
                {
                    excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
                }

                var result = excelReader.AsDataSet();
                excelReader = ExcelReaderFactory.CreateReader(stream);

                var conf = new ExcelDataSetConfiguration
                {
                    ConfigureDataTable = _ => new ExcelDataTableConfiguration
                    {
                        UseHeaderRow = true
                    }
                };
                var dataSet = excelReader.AsDataSet(conf);
                dataTable = dataSet.Tables[0];
                dataTable.Rows.RemoveAt(0);
            }
            return dataTable;
        }

So , first of all you must place a xlsx file  in your project. 

var filePath = HttpContext.Current.Server.MapPath("~/ExcelFile/Test.xlsx");

So , this was a sample file. Placed inside Excel folder inside project root.

 var conf = new ExcelDataSetConfiguration
 {
    ConfigureDataTable = _ => new ExcelDataTableConfiguration
    {
        UseHeaderRow = true
    }
 };
 var dataSet = excelReader.AsDataSet(conf);

This , will return you data as dataSet. UseHeaderRow will take first row as column headers. So, this is how you can ready your excel data and populate in datatable or dataset in Asp.Net.