Hi rani,
Check this example. Now please take its reference and correct your code.
Excel file
Namespaces
using System.Collections.Generic;
using System.Data;
using System.IO;
using ClosedXML.Excel;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.Mvc.Rendering;
Controller
public class HomeController : Controller
{
private IHostingEnvironment Environment;
public HomeController(IHostingEnvironment _environment)
{
Environment = _environment;
}
public IActionResult Index()
{
return View();
}
[HttpPost]
public IActionResult Index(IFormFile postedFile)
{
string path = Path.Combine(this.Environment.WebRootPath, "Uploads");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
string fileName = Path.GetFileName(postedFile.FileName);
string filePath = Path.Combine(path, fileName);
using (FileStream stream = new FileStream(filePath, FileMode.Create))
{
postedFile.CopyTo(stream);
}
DataTable dt = new DataTable();
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
IXLWorksheet workSheet = workBook.Worksheet(1);
bool firstRow = true;
foreach (IXLRow row in workSheet.Rows())
{
if (firstRow)
{
foreach (IXLCell cell in row.Cells())
{
dt.Columns.Add(cell.Value.ToString());
}
firstRow = false;
}
else
{
dt.Rows.Add();
int i = 0;
foreach (IXLCell cell in row.Cells())
{
dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
i++;
}
}
}
}
List<SelectListItem> countries = new List<SelectListItem>();
foreach (DataRow dr in dt.Rows)
{
countries.Add(new SelectListItem { Text = dr["Country"].ToString(), Value = dr["Id"].ToString() });
}
System.IO.File.Delete(filePath);
return View(new SelectList(countries, "Value", "Text"));
}
}
View
@model SelectList
@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
<form method="post" enctype="multipart/form-data" asp-controller="Home" asp-action="Index">
<span>Select File:</span>
<input type="file" name="postedFile" />
<input type="submit" value="Upload" />
<br />
<select id="ddlCountries" name="CountryId" asp-items="Model">
<option value="0">Please select</option>
</select>
</form>
</body>
</html>
Screenshot
