MVC excel导入

MVC excel导入

view部分

代码

@{
    Layout = "~/Views/Shared/_LayoutPage1.cshtml";
    ViewBag.Title = "Index";
}
<script src="~/scripts/jquery-1.10.2.js"></script>
<link rel="stylesheet" href="~/Content/kd.css" />

@using (Html.BeginForm("StationImport", "daoru", FormMethod.Post, new { enctype = "multipart/form-data" }))
{

    <h2  style="color:#fff;">
        信息导入
    </h2>

        <div class="daoru">
            <fieldset id="myfieldset1">

                <p>
                    选择文件：<input id="FileUpload" type="file" name="files" style="color:#fff;" />
                </p>                                                  
                <p>
                    <input id="btnImport" type="submit" value="导入" />
                </p>
                <span >@ViewBag.error</span>
            </fieldset>
        </div>
}

controller部分

代码
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Rjb.Models;

namespace Rjb.Controllers
{
public class daoruController : Controller
{
// GET: daoru
public rjbEntities ddb= new rjbEntities();
public ActionResult Index()
{
return View();
}

    [HttpPost]
    public ActionResult StationImport(HttpPostedFileBase filebase)
    {
        HttpPostedFileBase file = Request.Files["files"];//获取excel文件
        if (file == null || file.ContentLength <= 0)//判断是否为空
        {
            ViewBag.error = "文件不能为空";
            return View();
        }
        string fileExtenSion; //获取上传文件的扩展名
        fileExtenSion = Path.GetExtension(file.FileName);
        if (fileExtenSion.ToLower() != ".xls" && fileExtenSion.ToLower() != ".xlsx")
        {
            ViewBag.error = "文件类型不对，只能导入xls和xlsx格式的文件";
            return View();
        }
        string FileName = "../Content/excel" + Path.GetFileName(file.FileName);
        //删除服务器里上传的文件 
        if (System.IO.File.Exists(Server.MapPath(FileName)))
        {
            System.IO.File.Delete(Server.MapPath(FileName));
        }
        file.SaveAs(Server.MapPath(FileName));
        //HDR=Yes，这代表第一行是标题，不做为数据使用 ，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES  
        string connstr2003 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(FileName) + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
        string connstr2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath(FileName) + ";Extended Properties=\"Excel 12.0;HDR=YES\"";
        OleDbConnection conn;
        if (fileExtenSion.ToLower() == ".xls")
        {
            conn = new OleDbConnection(connstr2003);
        }
        else
        {
            conn = new OleDbConnection(connstr2007);
        }
        conn.Open();
        string sql = "select * from [Sheet1$]";
        OleDbCommand cmd = new OleDbCommand(sql, conn);
        DataTable dt = new DataTable();
        OleDbDataReader sdr = cmd.ExecuteReader();

        dt.Load(sdr);
        sdr.Close();
        conn.Close();
        //删除服务器里上传的文件  
        if (System.IO.File.Exists(Server.MapPath(FileName)))
        {
            System.IO.File.Delete(Server.MapPath(FileName));
        }
        try
        {            
            int insertcount = 0;//记录插入成功条数  
            for (int i = 0; i < dt.Rows.Count; i++)  //列数
            {
                string e_name = dt.Rows[i][0].ToString();//获取编号
                t_shop shop = new t_shop();
                shop.s_name = e_name;
                ddb.t_shop.Add(shop);
                ddb.SaveChanges();                                                               
                    insertcount++;                  
            }
            Response.Write(insertcount + "条数据导入成功！");            
        }
        catch (Exception ex)
        {
        }          
        return RedirectToAction("Index");
    }







}

}