CSHARP

convert excel to datatable without xml configuration

  		DataTable dt = new Datatable();
        /// <summary>
        /// Convert excel file to DataTable
        /// </summary>
        /// <param name="filePath">excel file path</param>
        /// <returns>datatable</returns>
        public DataTable Read(string filePath)
        {

            try
            {
                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
                    foreach (ExcelWorksheet workSheetR in package.Workbook.Worksheets)
                    {
                        for (int rN = 2; rN <= workSheetR.Dimension.End.Row; rN++)
                        {
                            ExcelRange row = workSheetR.Cells[rN, 1, rN, workSheetR.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);
            }
        }