Hi rani,
Check this example. Now please take its reference and correct your code.
Install ClosedXML library from nuget.
Namespaces
using System.Data;
using System.IO;
using ClosedXML.Excel;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Configuration;
Controller
public class HomeController : Controller
{
private IHostingEnvironment Environment;
private IConfiguration Configuration;
public HomeController(IHostingEnvironment _environment, IConfiguration _configuration)
{
Environment = _environment;
Configuration = _configuration;
}
public IActionResult Index()
{
return View();
}
[HttpPost]
public IActionResult Index(List<IFormFile> postedFiles)
{
string path = Path.Combine(this.Environment.WebRootPath, "Uploads");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
DataSet ds = new DataSet();
foreach (IFormFile postedFile in postedFiles)
{
string fileName = Path.GetFileName(postedFile.FileName);
string filePath = Path.Combine(path, fileName);
using (FileStream stream = new FileStream(filePath, FileMode.Create))
{
postedFile.CopyTo(stream);
}
using (XLWorkbook workBook = new XLWorkbook(filePath))
{
IXLWorksheet workSheet = workBook.Worksheet(1);
DataTable dt = new DataTable();
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++;
}
}
}
ds.Tables.Add(dt);
}
}
DataTable dtMerge = ds.Tables[0].Clone();
foreach (DataTable table in ds.Tables)
{
dtMerge.Merge(table);
}
using (XLWorkbook wb = new XLWorkbook())
{
wb.Worksheets.Add(dtMerge);
using (MemoryStream stream = new MemoryStream())
{
wb.SaveAs(stream);
return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Merge.xlsx");
}
}
}
}
View
@addTagHelper*, Microsoft.AspNetCore.Mvc.TagHelpers
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
</head>
<body>
<form asp-controller="Home" asp-action="Index" method="post" enctype="multipart/form-data">
<input type="file" name="postedFiles" multiple />
<input type="submit" value="Import" />
</form>
</body>
</html>
