To import excel to sql server database I did as following: 1. Add Reference Add Microsoft Excel 15.0 Object Library to the reference. excelref Too refer it in controller I added it as following: useref 2. Create model I have created a student model. Highlighted part is to auto generate id. excelmodel 3. Upload File View To upload a file I wrote my view like so: fileupview This will be posted in Import method of Student controller. 4. Handle on Post Now on post to ensure that an excel file is uploaded I wrote the following piece of code: handleexcel Now I read the file row wise and made my model instance and save it. Also I made a list of students. To use the first row as column name and prevent it from inserting on database I started from row=2. Since I want my id column to be auto-generated I skipped reading from first cell. readexc That's pretty much it. Full code looks like this: public class StudentController : Controller { ProjectDbContext db = new ProjectDbContext(); public ActionResult Index() { return View(); } [HttpPost] public ActionResult Import(HttpPostedFileBase excelfile) { //To ensure the file is uploaded if (excelfile == null || excelfile.ContentLength == 0) { ViewBag.Error = "Please select a excel file"; return View("Index"); } else { //check if excel file if (excelfile.FileName.EndsWith("xls") || excelfile.FileName.EndsWith("xlsx")) { //If exists a file with same name, delete previous one string path = Server.MapPath("~/Content/" + excelfile.FileName); if (System.IO.File.Exists(path)) { System.IO.File.Delete(path); } //save the file excelfile.SaveAs(path); excelfile.InputStream.Close(); //Read data from excel file Excel.Application application = new Excel.Application(); Excel.Workbook workbook = application.Workbooks.Open(path); Excel.Worksheet worksheet = workbook.ActiveSheet; Excel.Range range = worksheet.UsedRange; List<Student> liststudents = new List<Student>(); for (int row = 2; row <= range.Rows.Count; row++) { Student s = new Student(); s.name = ((Excel.Range)range.Cells[row, 2]).Text; s.address = ((Excel.Range)range.Cells[row, 3]).Text; liststudents.Add(s); db.Student.Add(s); db.SaveChanges(); } workbook.Close(); application.Quit(); ViewBag.ListDoctors = liststudents; return Content("Successful"); } else { ViewBag.Error = "File type is incorrct"; return View("Index"); } } } }

Total Views 61, Views Today 1

3 thoughts on "Import excel to sql server using Asp.net MVC"

  1. Am we legally responsible for content on my blogs & community forums that others have created?

  2. Olga Coston says:

    Hey there! Someone in my Facebook group shared this site with us so I came to check it out. I’m definitely loving the information. I’m bookmarking and will be tweeting this to my followers! Superb blog and amazing style and design.

  3. DomenicX says:

    I must say you have very interesting articles here.
    Your content can go viral. You need initial traffic only.
    How to get massive traffic? Search for: Murgrabia’s tools go viral

Leave a Reply

Your email address will not be published. Required fields are marked *