文章内容

C# ajax 导入Excel

  • 2018-10-10 13:14:50
  • 235
  • 0
  • 0


HTML代码

<form   class="form-horizontal submitform" data-validator-option='{stopOnError:false, timely:1, theme:"yellow_right_effect"}' 
      style="margin-top:20px;" action="/public/ExportSupplier_Post" method="post">
    <div class="form-group">
        <label class="col-sm-1 text-left control-label">选择</label>
        <div class="col-sm-4">
            <input    name="rolename" value="" data-rule="required" type="file" placeholder="">
        </div>
    </div>
    <div class="form-group">
        <label class="col-sm-1 text-left control-label"></label>
        <div class="col-sm-4">
           <a href="~/Public/download/E_Warehouse - 供应商导入列表.xlsx" target="_blank">点我下载导入供应商列表Excel</a>
        </div>
    </div>
    
    <br />
    <div class="form-group">
        <label class="col-sm-1  text-left control-label"></label>
        <div class="col-sm-3">
            <button type="submit" class="col-sm-4 btn btn-primary">导入</button>
        </div>
    </div>
</form>


JS代码

     var options = {
                    //target: '#output',    // 把服务器返回的内容放入id为output的元素中
                    //beforeSubmit: showRequest,    // 提交前的回调函数
                    success: function (json, status) {
                        try {
                            json = JSON.parse(json);
                            switch (json.code) {
                                case "200":
                                    toastr.success(json.message);
                                    break;
                                case "202":
                                    //显示
                                    toastr.success(json.message);
                                    var i = window.setTimeout(function () {
                                        window.clearTimeout(i);
                                        //隐藏
                                        $("#loadingModal").modal('hide');
                                        window.location.href = json.url;
                                    }, 500)
                                    break;
                                case "300":
                                    toastr.error(json.message);
                                    if (json.url) {
                                        var i = window.setTimeout(function () {
                                            window.clearTimeout(i);
                                            //隐藏
                                            $("#loadingModal").modal('hide');
                                            window.location.href = json.url;
                                        }, 500)
                                    }
                                    break;
                                default:
                                    toastr.error("返回JSON格式错误!");
                                    break;
                            }
                        } catch (e) {
                            toastr.error("错误1:" + json);
                        }
                    },
                    // 提交后的回调函数
                    // url : url,    //默认是form的action,如果申明,则会覆盖
                    // type : type,    // 默认值是form的method("GET" or "POST"),如果声明,则会覆盖
                    // dataType : null,    // html(默认)、xml、script、json接受服务器端返回的类型
                    // clearForm : true,    // 成功提交后,清除所有表单元素的值
                    // resetForm : true,    // 成功提交后,重置所有表单元素的值
                    timeout: 5000    // 限制请求的时间,当请求大于3秒后,跳出请求
                }
                $(this).ajaxSubmit(options);


C#后台

 public JsonResult ExportSupplier_Post()
        {

            var files = Request.Files;
            if (files.Count > 0)
            {
                HttpPostedFileBase hfb = files[0];
                double size = hfb.ContentLength * 1.00 / (1024 * 1024);
                string filename = hfb.FileName;
                int index = filename.LastIndexOf('.');
                string houzhui = filename.Substring(index, filename.Length - index);
                if (size < 20)
                {
                    if (houzhui == ".xls" || houzhui == ".xlsx")
                    {
                        string path = "/public/upload/gys/";
                        DirectoryInfo di = new DirectoryInfo(Server.MapPath(path));
                        if (!di.Exists) { di.Create(); }
                        string saveAsFile = Server.MapPath(path) + "/" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + filename;
                        hfb.SaveAs(saveAsFile);
                        string strConn = "Provider=Microsoft.Ace.OleDb.12.0;" + "data source=" + saveAsFile + ";Extended Properties='Excel 12.0; HDR=Yes; IMEX=1'";
                        //string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
                        OleDbConnection conn = new OleDbConnection(strConn);
                        conn.Open();
                        string strExcel = "";
                        OleDbDataAdapter myCommand = null;
                        DataSet ds = null;
                        strExcel = "select * from [sheet1$]";
                        myCommand = new OleDbDataAdapter(strExcel, strConn);
                        ds = new DataSet();
                        myCommand.Fill(ds, "table1");
                        conn.Close();
                        DataTable dt = ds.Tables[0];
                        DataTable ErrorData = dt.Clone();
                        int i = 0;
                        int realdataCount = dt.Rows.Count;
                        using (var tran=ewh.Database.BeginTransaction())
                        {
                            foreach (DataRow item in dt.Rows)
                            {
                                #region
                                string gysid = item["供应商ID"].ToString() ?? "";
                                string gysmc = item["供应商名称"].ToString() ?? "";
                                string gyswl = item["物料号"].ToString() ?? "";
                                if (gysid.Length < 1 && gysmc.Length < 1 && gyswl.Length < 1)
                                {
                                    realdataCount--;
                                }
                                else
                                {
                                    var list = ewh.ewh_supplierlist.Where(a => a.supplierid == gysid).ToList();
                                    //如果不存在该客户信息
                                    if (list.Count < 1)
                                    {
                                        ewh_supplierlist sup = new ewh_supplierlist();
                                        sup.supplierid = gysid;
                                        sup.suppliername = gysmc;
                                        sup.materialid = gyswl;
                                        ewh.ewh_supplierlist.Add(sup);
                                    }
                                    else
                                    {
                                        ErrorData.Rows.Add(dt.Rows[i].ItemArray);
                                        break;
                                    }
                                }
                                #endregion
                                i++;
                            }
                            int count = ewh.SaveChanges();
                            if (count == realdataCount)
                            {
                                tran.Commit();
                                return SuccessRefresh("操作成功!", "/public/SupplierList");
                            }
                            else
                            {
                                tran.Rollback();
                                return Failed("上传文件失败,请检查数据格式!");
                            }
                        }
                        
                    }
                    else
                    {
                        return Failed("上传文件失败,请上传正确的Excel文件!");
                    }
                }
                else
                {
                    return Failed("上传文件失败,请上传正确的Excel文件!");
                }

                //Response.Write();
            }
            else
            {
                return Failed("上传文件失败,请上传正确的Excel文件!");

            }
        }