locked
how to import excel data to database table in asp.net mvc entityframework db first? RRS feed

  • Question

  • User140010582 posted

    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.

    Sunday, July 19, 2020 5:45 PM

Answers

  • User1686398519 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 21, 2020 2:19 AM

All replies

  • User1686398519 posted

    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

    Monday, July 20, 2020 7:48 AM
  • User140010582 posted

    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 .

    Monday, July 20, 2020 1:12 PM
  • User1686398519 posted

    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

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 21, 2020 2:19 AM