how to import excel data to database table in asp.net mvc entityframework db first?[Answered] RSS

• • Reply
  sandesh pokh...

  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.

• • Reply
  YihuiSun

  Participant

  930 Points

  329 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.

  • 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; }
      }

  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

• • Reply
  sandesh pokh...

  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 .

• • Reply
  YihuiSun

  Participant

  930 Points

  329 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