Answered by:
Converting Excel Sheet to database sheet and converting Database sheet to Excel to excel sheet

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
VelenWednesday, September 20, 2017 1:39 AM