基于Layui + .net mvc Excel导入功能

html

js

/*从本地添加excel文件方法*/
layui.use('upload', function () {
    var $ = layui.jquery
        , upload = layui.upload
        , form = layui.form;
    upload.render({
        elem: '#Excel导入'//附件上传按钮ID
        , url: '/tools/excel/'//附件上传后台地址
        , multiple: true
        , accept: 'file'
        , exts: 'xls'//(允许的类别)
        , before: function (obj) {/*上传前执行的部分*/ }
        , done: function excel(res) {
            /*上传后执行的部分*/
            $.post("/ImportTool/excel", { newname: res.data.newname }, function (res) { layer.msg(res.msg); }, "json");
        }
        , allDone: function (res) {/*全部文件上传完毕执行该方法*/ }
    });
});//上传事件结束

Controller      /tools/excel/

        /// 

        /// 导入excel的方法
        /// 
        /// 
        /// 
        public string excel(FormCollection fc)
        {
            try
            {
                string guid = Guid.NewGuid().ToString();
                var file = Request.Files[0];
                if (file == null ||
                  String.IsNullOrEmpty(file.FileName) ||
                  file.ContentLength == 0)
                {
                    return "{\"code\":1,\"msg\":\"文件上传失败！\" ,\"data\":{\"src\":\"\"}}";
                }

                string filename = System.IO.Path.GetFileName(file.FileName);
                string newName = buildFileName(guid, filename);
                string virtualPath = String.Format("/upload/{0}", newName);

                string path = Server.MapPath(virtualPath);
                file.SaveAs(path);                

                return "{\"code\":0,\"msg\":\"文件上传成功！\" ,\"data\":{\"src\":\"\",\"filename\":\"" + filename + "\",\"newname\":\"" + newName + "\",\"size\":\"" + (file.ContentLength/1024) + "\"}}";
            }
            catch (Exception ex)
            {
                return "{\"code\":1,\"msg\":\"文件上传失败！\" ,\"data\":{\"src\":\"\"}}";
            }
        }
        /// 

        /// 根据文件名称生产新的文件名称；
        /// 
        /// 
        /// 
        public static string buildFileName(string newname, string filename)
        {
            string postfix = filename.Substring(filename.LastIndexOf('.'));
            string newName = newname + postfix;

            return newName;
        }

Controller     /ImportTool/excel

/*excel导入方法*/
        public string excel()
        {
            string newname = Request["newname"];
            string virtualPath = String.Format("/upload/{0}", newname);
            string path = Server.MapPath(virtualPath);
            DataTable dt = ExcelHelper.exceltoTable(path);//excel中的数据在dt中
			//导入到数据库示例
            病虫害管理S dal = new 病虫害管理S();
            病虫害管理 bean = new 病虫害管理();
            string ret = "";
            bool res = true;
            for (int i = 0; i < dt.Rows.Count; i++)//行
            {
                DataRow row = dt.Rows[i];

                bean.项目名称 = row[0].ToString();
                bean.病虫害名称 = row[1].ToString();
                bean.发生日期 = Convert.ToDateTime(row[2].ToString());
                bean.具体位置 = row[3].ToString();
                bean.危害植株 = row[4].ToString();
                bean.危害面积 = row[5].ToString();
                bean.防治措施 = row[6].ToString();


                bean.Id = Guid.NewGuid().ToString();
                bean.status = 0;
                SUser user = (SUser)Session["USER"];
                bean.Author = user.Uid == null ? "异常用户" : user.Uid.ToString();
                bean.上传来源 = user.Name == null ? "异常用户" : user.Name.ToString(); ;
                bean.序号 = "BC" + DateTime.Now.ToString("yyyyMMddHHmmssff");
                bean.Created = DateTime.Now;
                bean.orgId = user.OrgId;

                res = dal.update(bean);
            }
            if (res)
                ret = "{\"msg\":\"数据导入成功！\"}";
            else
                ret = "{\"msg\":\"数据导入异常！\"}";

            return ret;
		//示例结束
        }

 ExcelHelper.exceltoTable

using System;
using System.Data;
using System.Data.OleDb;
using System.IO;


namespace COMMON
{
    /// 

    /// Excel导出工具类
    /// 
    public static class ExcelHelper
    {
        public static string OledbConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=Excel 8.0;";

        public static DataTable exceltoTable(string path)
        {
            try
            {
                if (File.Exists(path))
                {
                    //获取excel数据
                    DataTable dt1 = new DataTable("excelTable");
                    string strConn = string.Format(OledbConnString, path);
                    OleDbConnection conn = new OleDbConnection(strConn);
                    conn.Open();
                    DataTable dt = conn.GetSchema("Tables");
                    //判断excel的sheet页数量，查询第1页
                    if (dt.Rows.Count > 0)
                    {
                        string selSqlStr = string.Format("select * from [{0}]", dt.Rows[0]["TABLE_NAME"]);
                        OleDbDataAdapter oleDa = new OleDbDataAdapter(selSqlStr, conn);
                        oleDa.Fill(dt1);
                    }
                    conn.Close();
                    return dt1;
                }
            }
            catch (Exception ex)
            {                
                return null;
            }

            return null;
        }
    }
}

注：一列中不能同时存在文字和数字，不然读取不到