locked
Converting Excel Sheet to database sheet and converting Database sheet to Excel to excel sheet RRS feed

  • Question

  • User1527485495 posted

    Greetings all, i'm design an HR application for an organization and part of there requirement is for me provide a link/button  that will convert excel sheet into database  sheet and upload to the database from front end (Index in mvc) or convert from database  (index view) to excel sheet... PLEASE NOTE : they told me they don't  want the normal import and export option from database....

     Please i want the house to enlighten me more about this ..... Thanks Dreal

    Friday, September 15, 2017 1:05 PM

Answers

  • User-1508012637 posted

    Hi Dreal1,

    i'm design an HR application for an organization and part of there requirement is for me provide a link/button  that will convert excel sheet into database  sheet and upload to the database from front end (Index in mvc) or convert from database  (index view) to excel sheet... PLEASE NOTE : they told me they don't  want the normal import and export option from database....

    According to your description, I've created a demo which has the function of:

    1. The user downloads the excel file from server and upload it after modified. 

    2. The server reads the uploaded file and save the new records to database.

    3. The server updates the excel file for users could download and modify next time.

    The screenshot as below:


    Please refer to the following sample code:

    Model

    public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public string ContactNo { get; set; }
    }

    Controller

    public class UserController : Controller
    {
        private UserContext db = new UserContext();
    
        // GET: User
        public ActionResult Index()
        {
            return View();
        }
    
        public FileResult DownloadExcel()
        {
            string path = "/Files/Users.xlsx";
            return File(path, "application/vnd.ms-excel", "Users.xlsx");
        }
    
        [HttpPost]
        public JsonResult UploadExcel(User users, HttpPostedFileBase FileUpload)
        {
            List<string> data = new List<string>();
            if (FileUpload != null)
            {
                // tdata.ExecuteCommand("truncate table OtherCompanyAssets");  
                if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                {
                    string filename = FileUpload.FileName;
                    string targetpath = Server.MapPath("~/Files/");
                    FileUpload.SaveAs(targetpath + filename);
                    string pathToExcelFile = targetpath + filename;
                    var connectionString = "";
                    if (filename.EndsWith(".xls"))
                    {
                        connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", pathToExcelFile);
                    }
                    else if (filename.EndsWith(".xlsx"))
                    {
                        connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", pathToExcelFile);
                    }
    
                    var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
                    var ds = new DataSet();
    
                    adapter.Fill(ds, "ExcelTable");
    
                    DataTable dtable = ds.Tables["ExcelTable"];
    
                    string sheetName = "Sheet1";
    
                    var excelFile = new ExcelQueryFactory(pathToExcelFile);
                    var artistAlbums = from a in excelFile.Worksheet<User>(sheetName) select a;
    
                    foreach (var a in artistAlbums)
                    {
                        try
                        {
                            if (a.Name != "" && a.Address != "" && a.ContactNo != "")
                            {
                                User TU = new User();
                                TU.Name = a.Name;
                                TU.Address = a.Address;
                                TU.ContactNo = a.ContactNo;
                                db.Users.AddOrUpdate(u => u.Name, TU);
    
                                db.SaveChanges();
                            }
                            else
                            {
                                data.Add("<ul>");
                                if (a.Name == "" || a.Name == null) data.Add("<li> name is required</li>");
                                if (a.Address == "" || a.Address == null) data.Add("<li> Address is required</li>");
                                if (a.ContactNo == "" || a.ContactNo == null) data.Add("<li>ContactNo is required</li>");
    
                                data.Add("</ul>");
                                data.ToArray();
                                return Json(data, JsonRequestBehavior.AllowGet);
                            }
                        }
    
                        catch (DbEntityValidationException ex)
                        {
                            foreach (var entityValidationErrors in ex.EntityValidationErrors)
                            {
                                foreach (var validationError in entityValidationErrors.ValidationErrors)
                                {
                                    Response.Write("Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage);
                                }
                            }
                        }
                    }
    
                    return Json("success", JsonRequestBehavior.AllowGet);
                }
                else
                {
                    //alert message for invalid file format  
                    data.Add("<ul>");
                    data.Add("<li>Only Excel file format is allowed</li>");
                    data.Add("</ul>");
                    data.ToArray();
                    return Json(data, JsonRequestBehavior.AllowGet);
                }
            }
            else
            {
                data.Add("<ul>");
                if (FileUpload == null) data.Add("<li>Please choose Excel file</li>");
                data.Add("</ul>");
                data.ToArray();
                return Json(data, JsonRequestBehavior.AllowGet);
            }
        }
    
        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                db.Dispose();
            }
            base.Dispose(disposing);
        }
    }

    Index View

    @{
        ViewBag.Title = "Index";
    }
    
    <h4>Add Users via Excel</h4>
    <hr />
    
    @using (Html.BeginForm("UploadExcel", "User", FormMethod.Post, new { enctype = "multipart/form-data", onsubmit = "return myFunction()" }))
    {
        <div class="form-horizontal">
            <div class="form-group">
                <div class="control-label col-md-2">Download Format:</div>
                <div class="col-md-10">
                    <a href="/User/DownloadExcel/">
                        <img src="../../Images/excel.ico" width="25" height="25" title="Download Excel format" alt="User.xlsx" />
                    </a>
                </div>
            </div>
    
            <div class="form-group">
                <div class="control-label col-md-2">Excel:</div>
                <div class="col-md-10">
                    <input type="file" id="FileUpload" name="FileUpload" class="" />
                </div>
            </div>
            <div class="form-group">
                <div class="col-md-offset-2 col-md-10">
                    <input type="submit" value="Upload" id="btnUpload" class="btn btn-primary" />
                </div>
            </div>
        </div>
    }  

    If you have any other questions, please feel free to contact me any time.

    Best Regards
    Velen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 18, 2017 4:22 AM

All replies

  • User475983607 posted

    Greetings all, i'm design an HR application for an organization and part of there requirement is for me provide a link/button  that will convert excel sheet into database  sheet and upload to the database from front end (Index in mvc) or convert from database  (index view) to excel sheet... PLEASE NOTE : they told me they don't  want the normal import and export option from database....

     Please i want the house to enlighten me more about this ..... Thanks Dreal

    The question is not clear.  What is "normal" import and export?  What database are you using?

    Friday, September 15, 2017 1:30 PM
  • User1527485495 posted

    MICROSOFT SQL , IMPORT AND EXPORTING OF DATA FROM DATABASE 

    Friday, September 15, 2017 2:19 PM
  • User-1508012637 posted

    Hi Dreal1,

    i'm design an HR application for an organization and part of there requirement is for me provide a link/button  that will convert excel sheet into database  sheet and upload to the database from front end (Index in mvc) or convert from database  (index view) to excel sheet... PLEASE NOTE : they told me they don't  want the normal import and export option from database....

    According to your description, I've created a demo which has the function of:

    1. The user downloads the excel file from server and upload it after modified. 

    2. The server reads the uploaded file and save the new records to database.

    3. The server updates the excel file for users could download and modify next time.

    The screenshot as below:


    Please refer to the following sample code:

    Model

    public class User
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public string Address { get; set; }
        public string ContactNo { get; set; }
    }

    Controller

    public class UserController : Controller
    {
        private UserContext db = new UserContext();
    
        // GET: User
        public ActionResult Index()
        {
            return View();
        }
    
        public FileResult DownloadExcel()
        {
            string path = "/Files/Users.xlsx";
            return File(path, "application/vnd.ms-excel", "Users.xlsx");
        }
    
        [HttpPost]
        public JsonResult UploadExcel(User users, HttpPostedFileBase FileUpload)
        {
            List<string> data = new List<string>();
            if (FileUpload != null)
            {
                // tdata.ExecuteCommand("truncate table OtherCompanyAssets");  
                if (FileUpload.ContentType == "application/vnd.ms-excel" || FileUpload.ContentType == "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet")
                {
                    string filename = FileUpload.FileName;
                    string targetpath = Server.MapPath("~/Files/");
                    FileUpload.SaveAs(targetpath + filename);
                    string pathToExcelFile = targetpath + filename;
                    var connectionString = "";
                    if (filename.EndsWith(".xls"))
                    {
                        connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0; data source={0}; Extended Properties=Excel 8.0;", pathToExcelFile);
                    }
                    else if (filename.EndsWith(".xlsx"))
                    {
                        connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";", pathToExcelFile);
                    }
    
                    var adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", connectionString);
                    var ds = new DataSet();
    
                    adapter.Fill(ds, "ExcelTable");
    
                    DataTable dtable = ds.Tables["ExcelTable"];
    
                    string sheetName = "Sheet1";
    
                    var excelFile = new ExcelQueryFactory(pathToExcelFile);
                    var artistAlbums = from a in excelFile.Worksheet<User>(sheetName) select a;
    
                    foreach (var a in artistAlbums)
                    {
                        try
                        {
                            if (a.Name != "" && a.Address != "" && a.ContactNo != "")
                            {
                                User TU = new User();
                                TU.Name = a.Name;
                                TU.Address = a.Address;
                                TU.ContactNo = a.ContactNo;
                                db.Users.AddOrUpdate(u => u.Name, TU);
    
                                db.SaveChanges();
                            }
                            else
                            {
                                data.Add("<ul>");
                                if (a.Name == "" || a.Name == null) data.Add("<li> name is required</li>");
                                if (a.Address == "" || a.Address == null) data.Add("<li> Address is required</li>");
                                if (a.ContactNo == "" || a.ContactNo == null) data.Add("<li>ContactNo is required</li>");
    
                                data.Add("</ul>");
                                data.ToArray();
                                return Json(data, JsonRequestBehavior.AllowGet);
                            }
                        }
    
                        catch (DbEntityValidationException ex)
                        {
                            foreach (var entityValidationErrors in ex.EntityValidationErrors)
                            {
                                foreach (var validationError in entityValidationErrors.ValidationErrors)
                                {
                                    Response.Write("Property: " + validationError.PropertyName + " Error: " + validationError.ErrorMessage);
                                }
                            }
                        }
                    }
    
                    return Json("success", JsonRequestBehavior.AllowGet);
                }
                else
                {
                    //alert message for invalid file format  
                    data.Add("<ul>");
                    data.Add("<li>Only Excel file format is allowed</li>");
                    data.Add("</ul>");
                    data.ToArray();
                    return Json(data, JsonRequestBehavior.AllowGet);
                }
            }
            else
            {
                data.Add("<ul>");
                if (FileUpload == null) data.Add("<li>Please choose Excel file</li>");
                data.Add("</ul>");
                data.ToArray();
                return Json(data, JsonRequestBehavior.AllowGet);
            }
        }
    
        protected override void Dispose(bool disposing)
        {
            if (disposing)
            {
                db.Dispose();
            }
            base.Dispose(disposing);
        }
    }

    Index View

    @{
        ViewBag.Title = "Index";
    }
    
    <h4>Add Users via Excel</h4>
    <hr />
    
    @using (Html.BeginForm("UploadExcel", "User", FormMethod.Post, new { enctype = "multipart/form-data", onsubmit = "return myFunction()" }))
    {
        <div class="form-horizontal">
            <div class="form-group">
                <div class="control-label col-md-2">Download Format:</div>
                <div class="col-md-10">
                    <a href="/User/DownloadExcel/">
                        <img src="../../Images/excel.ico" width="25" height="25" title="Download Excel format" alt="User.xlsx" />
                    </a>
                </div>
            </div>
    
            <div class="form-group">
                <div class="control-label col-md-2">Excel:</div>
                <div class="col-md-10">
                    <input type="file" id="FileUpload" name="FileUpload" class="" />
                </div>
            </div>
            <div class="form-group">
                <div class="col-md-offset-2 col-md-10">
                    <input type="submit" value="Upload" id="btnUpload" class="btn btn-primary" />
                </div>
            </div>
        </div>
    }  

    If you have any other questions, please feel free to contact me any time.

    Best Regards
    Velen

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, September 18, 2017 4:22 AM
  • User1527485495 posted

    Thanks Valen,  Please do i have to create a new Model  and controller ? if yes bro, what step do i have to take . thanks in anticipation bro.

    Tuesday, September 19, 2017 12:51 PM
  • User-1508012637 posted

    Hi Dreal1,

    Please do i have to create a new Model  and controller ?

    It depends on you. The model User I posted above is just for reference and you could change it to the Employee, Staff, Product or any other models.

    Best Regards
    Velen

    Wednesday, September 20, 2017 1:39 AM