ASP.NET MVC - excel upload using stored procedure

sir

First take the reference of Microsoft Excel 12.0 Object Library to your project to follow the below image

Then I try to read the excel the below

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Mvc.Html;
using System.Collections;
using System.Linq.Expressions;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Data;
using System.IO;
namespace multytesting.Controllers
{
public class TestController : Controller
{
//
// GET: /Test/
public ActionResult Index()
{
IList ModelList = new List();
multytesting.Models.Sample obj = new multytesting.Models.Sample();
obj.code = 1;
obj.name = "test";
ModelList.Add(obj);
ViewBag.smp = ModelList;
return View();
}
public ActionResult ImportExcel()
{
return View();
}
[HttpPost]
public ActionResult ImportExcel(FormCollection frm)
{
// OleDbConnection objConn = null;
System.Data.DataTable dt = new System.Data.DataTable();
var file = Request.Files[0];
Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
// File Pfile = Request.Files[0];
string excelFile = file.FileName;
string filesavepath = Server.MapPath("exl") + "//" + "ex.xls";
file.SaveAs(filesavepath);
string filepath = filesavepath;
//pass that to workbook object
Microsoft.Office.Interop.Excel.Workbook WB = oExcel.Workbooks.Open(filepath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
// statement get the workbookname
string ExcelWorkbookname = WB.Name;
// statement get the worksheet count
int worksheetcount = WB.Worksheets.Count;
//int shetno = sheet.SelectedIndex;
int shetno = 0;
Microsoft.Office.Interop.Excel.Worksheet wks = (Microsoft.Office.Interop.Excel.Worksheet)WB.Worksheets[shetno + 1];
Microsoft.Office.Interop.Excel.Range range;
// statement get the firstworksheetname
try
{
string firstworksheetname = wks.Name;
string str;
int rCnt;
int cCnt;
int rw = 0;
int cl = 0;
range = wks.UsedRange;
rw = range.Rows.Count;
cl = range.Columns.Count;
for (int i = 0; i < cl; i++)
{
string cna = "Field" + Convert.ToString(i + 1);
dt.Columns.Add(cna);
}
for (rCnt = 0; rCnt < rw; rCnt++)
{
DataRow dr = dt.NewRow();
for (cCnt = 0; cCnt < cl; cCnt++)
{
string s = range.Value2[rCnt, cCnt];
if (String.IsNullOrEmpty(s))
dr[cCnt] = "";
else
dr[cCnt] = s;
}
dt.Rows.Add(dr);
}
//grid.DataSource = dt;
//grid.DataBind();
WB.Close(true, null, null);
oExcel.Quit();
}
catch (Exception ex)
{
// return null;
}
finally
{
Marshal.ReleaseComObject(wks);
Marshal.ReleaseComObject(WB);
Marshal.ReleaseComObject(oExcel);
}
return RedirectToAction("ImportExcel");
}

code may work

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Laxmidhar Sahoo8y ago
sir
here it is logic oriented .So if you successfully read the excel file
so in the code behind logic will be applied.ok any way i try with my
code to sove your problem .But it will take time
Dharmraj Thakur8y ago
Please follow this

1. Select or install any excel reader in your project.
2. You will get all data in DataSet object of excel by reading specific given path file.
3. After data comes in DataSet you can read all rows of dataset by for loop.
4. In for loop get data from DataRow and write down the code of insert in database.

Thats It.
Laxmidhar Sahoo8y ago
sir
First take the reference of Microsoft Excel 12.0 Object Library to your project to follow the below image
Then I try to read the excel the below
using System;
using System.Collections.Generic;
using System.Linq;

using System.Web;
using System.Web.Mvc;
using System.Web.Mvc.Html;
using System.Collections;
using System.Linq.Expressions;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using System.Reflection;
using System.Data;
using System.IO;
namespace multytesting.Controllers
{
    public class TestController : Controller
    {
        //
        // GET: /Test/

        public ActionResult Index()
        {
            IList ModelList = new List();
            multytesting.Models.Sample obj = new multytesting.Models.Sample();
            obj.code = 1;
            obj.name = "test";
            ModelList.Add(obj);
            ViewBag.smp = ModelList;
            return View();
        }
        public ActionResult ImportExcel()
        {
            return View();
        }
        [HttpPost]
        public ActionResult ImportExcel(FormCollection frm)
        {
           // OleDbConnection objConn = null;
            System.Data.DataTable dt = new System.Data.DataTable();


            var file = Request.Files[0];

            Microsoft.Office.Interop.Excel.Application oExcel = new Microsoft.Office.Interop.Excel.Application();
          //  File Pfile = Request.Files[0];

            string excelFile = file.FileName;
            string filesavepath = Server.MapPath("exl") + "//" + "ex.xls";
            file.SaveAs(filesavepath);


            string filepath = filesavepath;
            //pass that to workbook object
            Microsoft.Office.Interop.Excel.Workbook WB = oExcel.Workbooks.Open(filepath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);


            // statement get the workbookname
            string ExcelWorkbookname = WB.Name;

            // statement get the worksheet count
            int worksheetcount = WB.Worksheets.Count;
            //int shetno = sheet.SelectedIndex;
            int shetno = 0;
            Microsoft.Office.Interop.Excel.Worksheet wks = (Microsoft.Office.Interop.Excel.Worksheet)WB.Worksheets[shetno + 1];
            Microsoft.Office.Interop.Excel.Range range;
            // statement get the firstworksheetname
            try
            {


                string firstworksheetname = wks.Name;

                string str;
                int rCnt;
                int cCnt;
                int rw = 0;
                int cl = 0;

                range = wks.UsedRange;
                rw = range.Rows.Count;
                cl = range.Columns.Count;
                for (int i = 0; i < cl; i++)
                {
                    string cna = "Field" + Convert.ToString(i + 1);
                    dt.Columns.Add(cna);
                }

                for (rCnt = 0; rCnt < rw; rCnt++)
                {
                    DataRow dr = dt.NewRow();
                    for (cCnt = 0; cCnt < cl; cCnt++)
                    {



                        string s = range.Value2[rCnt, cCnt];

                        if (String.IsNullOrEmpty(s))
                            dr[cCnt] = "";
                        else
                            dr[cCnt] = s;
                    }
                    dt.Rows.Add(dr);
                }

                //grid.DataSource = dt;
                //grid.DataBind();
                WB.Close(true, null, null);
                oExcel.Quit();

            }
            catch (Exception ex)
            {
                // return null;
            }
            finally
            {

                Marshal.ReleaseComObject(wks);
                Marshal.ReleaseComObject(WB);
                Marshal.ReleaseComObject(oExcel);
            }
            return RedirectToAction("ImportExcel");
        }
code may work
mike idowu8y ago
I dont understand. This does not answer my question.