【库房】批量导入Excel表格

如何制作导入Excel表格

Controllers

public ActionResult PostExcel(string files)
        {
            TimeSpan ts1 = new TimeSpan(DateTime.Now.Ticks);
            List<t_storage> storage = new List<t_storage>();
            System.Data.DataTable tb = new System.Data.DataTable();
            HttpPostedFileBase file = Request.Files["files"];

            string strFileName;
            string strSavaPath;
            string ClientPath = AppDomain.CurrentDomain.BaseDirectory + "模板\\";
            string strPaperld = "sheet1";
            if (file == null || file.ContentLength <= 0)
            {
                ViewBag.error = "文件不能为空";
            }
            strFileName = Path.GetFileName(file.FileName);
            int intFilesize = file.ContentLength;
            string strNoFilename = System.IO.Path.GetFileNameWithoutExtension(strFileName);
            strSavaPath = Path.Combine(ClientPath, strFileName);
            file.SaveAs(strSavaPath);
            string tablename = "t_storage";
            List<VM_Storage> list = istoragebll.ExcelToDataTable(strSavaPath, strPaperld, tablename);
            return Json(list, JsonRequestBehavior.AllowGet);
        }

BLL

 public List<VM_Storage> ExcelToDataTable(string strSavaPath, string strSheetName, string tablename)
        {
            List<VM_Storage> list = istoragedal.ExcelToDataTable(strSavaPath, strSheetName, tablename);
            //如果模板或没有找到文档返回空
            if (list == null)
            {
                return null;
            }

            //把失败记录放入失败的list集合中
            List<VM_Storage> listfail = new List<VM_Storage>();

            //设置成功N条失败N条记录
            int fail = 0;

            //提取文件信息
            t_storage t = new t_storage();

            //设置标记Log   0:数据验证成功。1：数据验证失败
            int log = 0;

            if (list.Count > 0)
            {
                for (int i = 0; i < list.Count; i++)
                {
                    log = 0;
                    t.storageID = Guid.NewGuid().ToString();
                    t.storageName = list[i].StorageName;
                    t.place = list[i].Place;
                    t.capacity = list[i].Capacity;
                    t.remark = list[i].Remark;


                    //在数据库中添加记录
                    if (log == 0)
                    {
                        //查询ID是否重复
                        if (istoragedal.isable(t).Count == 1)
                        {
                            fail++;
                            //把失败记录放入失败的list集合中
                            listfail.Add(list[i]);
                            continue;
                        }
                        istoragedal.insertStorage(t);
                    }
                }

            }

            //把成功N条和失败N条记录存入listfail下载最后一个commentied中
            list[0].Commentid = list.Count - fail;//成功的条数
            list[0].Postcommentid = fail.ToString();//失败条数
            listfail.Add(list[0]);

            return listfail;
        }

DAL

//添加引用
using System.Data.OleDb;

 public List<VM_Storage> ExcelToDataTable(string strSavaPath, string strSheetName, string tablename)
        {
            try
            {
                string strConn;
                string strFileType = System.IO.Path.GetExtension(strSavaPath);
                if (string.IsNullOrEmpty(strFileType)) return null;
                if (strFileType == ".xls")
                {
                    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strSavaPath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
                }
                else
                {
                    strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + strSavaPath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
                }
                OleDbConnection conn = new OleDbConnection(strConn);
                conn.Open();
                OleDbDataAdapter adpter = new OleDbDataAdapter("select * from [" + strSheetName + "$]", strConn);
                DataSet myDataset = new DataSet();
                try
                {
                    adpter.Fill(myDataset, tablename);
                }
                catch (Exception)
                {

                    throw new Exception("配置文件的sheet名称配置错误");
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                        adpter.Dispose();
                        conn.Dispose();
                    }
                }
                System.Data.DataTable table = myDataset.Tables[tablename].DefaultView.ToTable();
                table.Columns["库房名称"].ColumnName = "storageName";
                table.Columns["库房位置"].ColumnName = "place";
                table.Columns["库房面积(㎡)"].ColumnName = "capacity";
                table.Columns["备注"].ColumnName = "remark";

                DataTable newtable = table.Copy();
                List<VM_Storage> liststorage = new List<VM_Storage>();
                liststorage = DatatableToList.ConvertToList<VM_Storage>(newtable);
                return liststorage;
            }
            catch (Exception)
            {
                return null;
            }

        }

JS

function importExcelTest() {
    //var aaa = $('#FileUpload').
    var file = $('#FileUpload').filebox("getText");
    var files = $('#FileUpload').filebox("getText");
    //判断文件上传是否为空
    if (file == null || file == "") {
        $.messager.alert('系统提示', '请选择将要上传的文件!');

    }

    //分割文件的类型
    var file_typename = file.substring(file.lastIndexOf('.'), file.length);
    if (file_typename == '.xlsx' || file_typename == 'xls') {

        var options = {
            method: 'POST',
            url: '/Storage/PostExcel',
            data: file,
            dataType: 'json',
            success: function (data) {
                if (data.length == 1) {
                    $.messager.show({
                        title: '提示',
                        msg: '仓库成功导入' + data[0].Commentid + '条记录！',
                        showType: 'fade',
                        timeout: 1000,
                        style: { left: 500, top: 200 }
                    });
                    $('#importDiv').dialog('close');

                } else if (data.length > 1) {

                    var stringTitle = '';
                    for (var i = 0; i < data.length - 1; i++) {
                        stringTitle += '第' + (i + 1) + '条——仓库名称：' + data[i].StorageName + '仓库位置：' + data[i].Place + '仓库面积(m²)：' + data[i].Capacity + '备注：' + data[i].Remark  + '\r\n';
                    }

                    $.messager.show({
                        title: '提示',
                        msg: '<textarea id="wartitle" style="height: 100px; width:600px;outline:none;border:none; text-align:center,overflow:scroll;" wrap="off">' + "仓库成功导入" + data[data.length - 1].Commentid + '条记录，失败' + data[data.length - 1].Postcommentid + '条记录！' + '\r\n' + stringTitle + '</textarea> ',
                        showType: 'fade',
                        width: 600,
                        height: '50%'

                    });

                    //关闭弹出框
                    $("#importDiv").dialog('close');

                } else {
                    $.messager.alert("操作提示", "仓库信息失败，请使用指定模板，不要修改模板格式！", "warning");
                }
            }, error: function (data) {
                $.messager.alert("操作提示", "仓库信息失败，请使用指定模板，不要修改模板格式！", "warning");
            }
        }
        $('#testform').ajaxSubmit(options);
    } else {
        $.messager.alert('提示', '请选择正确的文件类型')
    }
}

总结
先让Me消化一下，积累助成长。