ASP.NET MVC - Save Excel Contents to Database

Question

I would like to add a functionality to my web application where user can upload an excel file that would probably look like this (i will be supplying an excel file template that the user can fill up):

Name      Address      Phone
John      California   000-111
Matt      Seattle      000-222
...

And pass each line after the headings Name-Address-Phone to my registration controller or service. Now my question is what would be the best way (for user experience; speed is important) to do this?

I am not looking for a full working code, I'm just looking for the most efficient approach with regards to speed.

Answer 1

You should create a stored procedure which holds the insert logic.Once the user uploads the Excel file you can open it using Excel interop, get the captured data and call the insert stored procedure.If you need even faster insert speeds you can always use SQL Bulk Copy.

1.In your MVC project add a reference to Microsoft.Office.Interop.Excel.dll

2.Create a View which prompts the user to upload an Excel file.

@using (Html.BeginForm("Import", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    @Html.Raw(ViewBag.Error)
    <span>Excel File </span><input type="file" name="excelfile" />
    <br />
    <input type="submit" value="Import" />
}

3.Create a controller action which opens the Excel file -> loops through the rows -> inserts the data into SQL

[HttpPost]
public ActionResult Import(HttpPostedFileBase excelFile)
{
    //Add this to the using statements after adding a refrence to Microsoft.Office.Interop.Excel.dll - using Excel = Microsoft.Office.Interop.Excel; 
    if ((excelFile.ContentLength != 0) && (excelFile.FileName.EndsWith("xls") || excelFile.FileName.EndsWith("xlsx")))
    {
        string path = Server.MapPath("~/Files/" + excelFile.FileName);
        if (!System.IO.File.Exists(path))
        {
            excelFile.SaveAs(path);
            Excel.Application application = new Excel.Application();
            Excel.Workbook workbook = application.Workbooks.Open(path);
            Excel.Worksheet worksheet = (Excel.Worksheet)workbook.ActiveSheet;
            Excel.Range range = worksheet.UsedRange;

            for (int i = 2; i < range.Rows.Count + 1; i++)
            {
                string name = ((Excel.Range)range.Cells[i,1]).Text;
                string address = ((Excel.Range)range.Cells[i,2]).Text;
                string phone = ((Excel.Range)range.Cells[i,3]).Text;
                //Write the logic to add the values to the database
            }
        }
    }
    return View();
}

You could also make the action asynchronous but it doesn't really help because writing to a database is an IO operation so async won't do anything.

Answer 2

You can make use of OleDb to connect to your excel file. After successfull connection you can populate a DataTable with the rows in your excel file and then do a foreach to pass each row to your controller.

void Process(string path)
    {
        try
        {

            string connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";

            using (var connection = new OleDbConnection(connString))
            {
                using (var command = new OleDbCommand("select * from [SheetName$]", connection))
                {
                    connection.Open();
                    using (var adapter = new OleDbDataAdapter(command))
                    {
                        DataTable dataTable = new DataTable();
                        adapter.Fill(dataTable);

                        foreach (DataRow row in dataTable.Rows)
                        {
                            string Name, Address, Phone = string.Empty;
                            try
                            {
                                Name = row["Name"].ToString();
                                Address = row["Address"].ToString();
                                Phone = row["Phone"].ToString();

                                //new RegistrationController(Name, Address, Phone);

                            }

                            catch (Exception ex)
                            {
                                // Handle exceptions
                            }
                        }

                    }
                }
            }
        }

        // Might be possibly thrown when opening connection
        catch (OleDbException ex)
        {
            // Handle exceptions
        }

        // Might be possibly thrown when filling datatable
        catch (InvalidOperationException ex)
        {
            // Handle exceptions
        }

    }