According to your description, I make a small demo to import excel and store data into database.
It used NPOI, you can download it in NuGet.
Model
public class book
{
[Key]
public int id { get; set; }
public string dld { get; set; }
public string BO { get; set; }
public string paymentstatus { get; set; }
}
public class bookContext : DbContext
{
public DbSet<book> books { get; set; }
}
public class excel
{
public static DataTable Import(DataTable dt,XSSFWorkbook workbook,bookContext db)
{
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
for(int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(sheet.GetRow(0).Cells[j].ToString());
}
while (rows.MoveNext())
{
XSSFRow row = (XSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for(int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
dt.Rows.RemoveAt(0);
if (dt != null && dt.Rows.Count != 0)
{
for(int i = 0; i < dt.Rows.Count; i++)
{
book b = new book();
b.dld = dt.Rows[i]["dld"].ToString();
b.BO = dt.Rows[i]["BO"].ToString();
b.paymentstatus = dt.Rows[i]["paymentstatus"].ToString();
var flag = db.books.Where(x => x.BO == b.BO).FirstOrDefault();
if (flag != null&&flag.paymentstatus!=b.paymentstatus)
{
flag.paymentstatus = b.paymentstatus;
db.books.AddOrUpdate(flag);
}
if (flag != null)
{
db.books.AddOrUpdate(flag);
}
else
{
db.books.AddOrUpdate(b);
}
}
}
db.SaveChanges();
return dt;
}
public static DataTable Import(DataTable dt,HSSFWorkbook workbook,bookContext db)
{
NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0);
IEnumerator rows = sheet.GetRowEnumerator();
for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++)
{
dt.Columns.Add(sheet.GetRow(0).Cells[j].ToString());
}
while (rows.MoveNext())
{
HSSFRow row = (HSSFRow)rows.Current;
DataRow dr = dt.NewRow();
for (int i = 0; i < row.LastCellNum; i++)
{
NPOI.SS.UserModel.ICell cell = row.GetCell(i);
if (cell == null)
{
dr[i] = null;
}
else
{
dr[i] = cell.ToString();
}
}
dt.Rows.Add(dr);
}
dt.Rows.RemoveAt(0);
if (dt != null && dt.Rows.Count != 0)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
book b = new book();
b.dld = dt.Rows[i]["dld"].ToString();
b.BO = dt.Rows[i]["BO"].ToString();
b.paymentstatus = dt.Rows[i]["paymentstatus"].ToString();
var flag = db.books.Where(x => x.BO == b.BO).FirstOrDefault();
if (flag != null && flag.paymentstatus != b.paymentstatus)
{
flag.paymentstatus = b.paymentstatus;
db.books.AddOrUpdate(flag);
}
if (flag != null)
{
db.books.AddOrUpdate(flag);
}
else
{
db.books.AddOrUpdate(b);
}
}
}
db.SaveChanges();
return dt;
}
}
Here is the result.
Best regards,
Yihui Sun
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
Member
4 Points
16 Posts
how to import excel data to database table in asp.net mvc entityframework db first?
Jul 19, 2020 05:45 PM|sandesh pokhrel|LINK
I want to import data
dId BO paymentstatus
1232 32321 Unpaid
234 44423 Unpaid
43 42333 Unpaid
1232 32321 Unpaid
234 44423 Unpaid
43 42333 Unpaid
I want to upload excel file and import data to sql server database table.
also when I upload another excel file after imported above data I want following which has following record like
dId BO paymentstatus
234 44423 paid
43 42333 paid
I want to change paymentstatus paid of respective BO according to file uploaded next time. excel file may contains hundreds of data.
any example or solution for this kind of senario.
Contributor
2770 Points
789 Posts
Re: how to import excel data to database table in asp.net mvc entityframework db first?
Jul 20, 2020 07:48 AM|YihuiSun|LINK
Hi sandesh pokhrel,
According to your description, I make a small demo to import excel and store data into database.
Model
public class book { [Key] public int id { get; set; } public string dld { get; set; } public string BO { get; set; } public string paymentstatus { get; set; } } public class bookContext : DbContext { public DbSet<book> books { get; set; } }Controller
[ActionName("Index")] [HttpPost] public ActionResult import() { HttpPostedFileBase file = Request.Files["file"]; if (file == null || file.ContentLength <= 0) { return Json("please select excel file", JsonRequestBehavior.AllowGet); } Stream streamfile = file.InputStream; DataTable dt = new DataTable(); string FileName = Path.GetExtension(file.FileName); if (FileName != ".xls" && FileName != ".xlsx") { return Json("Only excel file", JsonRequestBehavior.AllowGet); } else { try { if (FileName == ".xls") { HSSFWorkbook workbook = new HSSFWorkbook(streamfile); dt = excel.Import(dt, workbook,db); } else { XSSFWorkbook workbook = new XSSFWorkbook(streamfile); dt = excel.Import(dt, workbook,db); } return Json("OK", JsonRequestBehavior.AllowGet); }catch(Exception e) { return Json("fail", JsonRequestBehavior.AllowGet); } } // return View(); }View
@using (Html.BeginForm("Index","Home",FormMethod.Post,new { enctype= "multipart/form-data" })) { <h4>select excel file</h4> <input name="file" type="file" id="file" /> <input type="submit" id="fileup" value="submit" /> }DAL
public class excel { public static DataTable Import(DataTable dt,XSSFWorkbook workbook,bookContext db) { NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); IEnumerator rows = sheet.GetRowEnumerator(); for(int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { dt.Columns.Add(sheet.GetRow(0).Cells[j].ToString()); } while (rows.MoveNext()) { XSSFRow row = (XSSFRow)rows.Current; DataRow dr = dt.NewRow(); for(int i = 0; i < row.LastCellNum; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } dt.Rows.RemoveAt(0); if (dt != null && dt.Rows.Count != 0) { for(int i = 0; i < dt.Rows.Count; i++) { book b = new book(); b.dld = dt.Rows[i]["dld"].ToString(); b.BO = dt.Rows[i]["BO"].ToString(); b.paymentstatus = dt.Rows[i]["paymentstatus"].ToString(); var flag = db.books.Where(x => x.BO == b.BO).FirstOrDefault(); if (flag != null&&flag.paymentstatus!=b.paymentstatus) { flag.paymentstatus = b.paymentstatus; db.books.AddOrUpdate(flag); } if (flag != null) { db.books.AddOrUpdate(flag); } else { db.books.AddOrUpdate(b); } } } db.SaveChanges(); return dt; } public static DataTable Import(DataTable dt,HSSFWorkbook workbook,bookContext db) { NPOI.SS.UserModel.ISheet sheet = workbook.GetSheetAt(0); IEnumerator rows = sheet.GetRowEnumerator(); for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { dt.Columns.Add(sheet.GetRow(0).Cells[j].ToString()); } while (rows.MoveNext()) { HSSFRow row = (HSSFRow)rows.Current; DataRow dr = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { NPOI.SS.UserModel.ICell cell = row.GetCell(i); if (cell == null) { dr[i] = null; } else { dr[i] = cell.ToString(); } } dt.Rows.Add(dr); } dt.Rows.RemoveAt(0); if (dt != null && dt.Rows.Count != 0) { for (int i = 0; i < dt.Rows.Count; i++) { book b = new book(); b.dld = dt.Rows[i]["dld"].ToString(); b.BO = dt.Rows[i]["BO"].ToString(); b.paymentstatus = dt.Rows[i]["paymentstatus"].ToString(); var flag = db.books.Where(x => x.BO == b.BO).FirstOrDefault(); if (flag != null && flag.paymentstatus != b.paymentstatus) { flag.paymentstatus = b.paymentstatus; db.books.AddOrUpdate(flag); } if (flag != null) { db.books.AddOrUpdate(flag); } else { db.books.AddOrUpdate(b); } } } db.SaveChanges(); return dt; } }Here is the result.
Best regards,
Yihui Sun
Member
4 Points
16 Posts
Re: how to import excel data to database table in asp.net mvc entityframework db first?
Jul 20, 2020 01:12 PM|sandesh pokhrel|LINK
thank you for replay Yihuisun,
you are doing it in one browse file.
I want two browse file option one for import excel data to database.
next for update paymentstatus column of respective BO in the excel file .
Contributor
2770 Points
789 Posts
Re: how to import excel data to database table in asp.net mvc entityframework db first?
Jul 21, 2020 02:19 AM|YihuiSun|LINK
Hi sandesh pokhrel,
I do some change so that have two import option. The next one can update paymentstatus.
View
@using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" })) { <h4>select excel file</h4> <input name="postedFiles1" type="file" id="postedFiles1" /> <input name="postedFiles2" type="file" id="postedFiles2" /> <input type="submit" id="fileup" value="submit" /> }Controller
[ActionName("Index")] [HttpPost] public ActionResult import() { HttpPostedFileBase file1 = Request.Files["postedFiles1"]; HttpPostedFileBase file2 = Request.Files["postedFiles2"]; if (file1 == null || file1.ContentLength <= 0 || file2 == null || file2.ContentLength <= 0) { return Json("please select excel file", JsonRequestBehavior.AllowGet); } Stream streamfile1 = file1.InputStream; Stream streamfile2 = file2.InputStream; DataTable dt1 = new DataTable(); DataTable dt2 = new DataTable(); string FileName1 = Path.GetExtension(file1.FileName); string FileName2 = Path.GetExtension(file1.FileName); if (FileName1 != ".xls" && FileName1 != ".xlsx"&& FileName2 != ".xls" && FileName2 != ".xlsx") { return Json("Only excel file", JsonRequestBehavior.AllowGet); } else { try { if (FileName1 == ".xls"|| FileName2 == ".xls") { HSSFWorkbook workbook1 = new HSSFWorkbook(streamfile1); HSSFWorkbook workbook2= new HSSFWorkbook(streamfile2); dt1 = excel.Import(dt1, workbook1,db); dt2 = excel.Import(dt2, workbook2,db); } else { XSSFWorkbook workbook1 = new XSSFWorkbook(streamfile1); XSSFWorkbook workbook2 = new XSSFWorkbook(streamfile2); dt1 = excel.Import(dt1, workbook1, db); dt2 = excel.Import(dt2, workbook2, db); } return Json("OK", JsonRequestBehavior.AllowGet); }catch(Exception e) { return Json("fail", JsonRequestBehavior.AllowGet); } } }Here is the result.
Best regards,
Yihui Sun