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”);
}
}

}

}

(Visited 92 times, 1 visits today)

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