将Excel导入数据库

时间:2022-10-20 14:47:18

在Control 中:

   public ActionResult ImportExcel()
        {
            return View();
        }
        //客户导入
        [HttpPost]
        public ActionResult ImportExcel(HttpPostedFileBase importxls)
        {
            //获取文件和扩展名
            var fileName = Path.GetFileName(importxls.FileName);
            //获取文件路径地址
            var path = Path.Combine(Server.MapPath("~/App_Data/"), fileName);
            //获取文件的扩展名
            var exname = Path.GetExtension(importxls.FileName).ToLower();
            try
            {
            if (exname == ".xls" || exname == ".xlsx")
            {
                importxls.SaveAs(path);
                var connectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path.Combine(Server.MapPath("~/App_Data/"), fileName) + ";Extended Properties=Excel 12.0;");
                    var adapterbvandrev = new OleDbDataAdapter("select*from[Sheet1$]", connectionString);
                    var dsimport = new DataSet();
                    adapterbvandrev.Fill(dsimport);
                    DataTable dataimport = dsimport.Tables[0];
                    List<Customer> objectCustomerEntity = new List<Customer>();
                    for (int i = 0; i < dataimport.Rows.Count; i++)
                    {
                        objectCustomerEntity.Add(
                            new Customer
                            {
                                CustomerCode = dataimport.Rows[i]["用户编号"].ToString(),
                                Company = dataimport.Rows[i]["公司名称"].ToString(),
                                Contact = dataimport.Rows[i]["联系人"].ToString(),
                                Tel = dataimport.Rows[i]["联系电话"].ToString(),
                                Mobile = dataimport.Rows[i]["手机号码"].ToString(),
                                Address = dataimport.Rows[i]["客户地址"].ToString(),

                            });
                    }
                    SaveData(objectCustomerEntity);
                     return View();
                   }
                    else
                   {
                      return Content("<script>alert('请选择Excel文件!');history.go(-1);</script>");
                   }
            }
                catch(Exception ex)
                {
                    this.TempData["Message"] = "错误:请检查所导入的Excel格式是否符合要求?" + ex.Message; ;
                    return View();
            }
        }

        
        //public bool SaveData(List<Customer> objCustomer)
        //{
        //    try
        //    {
        //    CMSContext _db = new CMSContext();
        //    foreach (var item in objCustomer)
        //    {
        //        Customer Customer = new Customer();
        //        Customer.CustomerCode = item.CustomerCode;
        //        Customer.Company = item.Company;
        //        Customer.Contact = item.Contact;
        //        Customer.Tel = item.Tel;
        //        Customer.Mobile = item.Mobile;
        //        Customer.Address = item.Address;
        //        _db.Customers.Add(Customer);
        //        _db.SaveChanges();
        //    }
        //    this.TempData["Message"] = "恭喜," + objCustomer.Count().ToString() + "条数据导入成功!";
        //    return true;
        //    }
        //    catch (Exception ex)
        //    {
        //        this.TempData["Message"] = "错误:请检查客户编号是否有重复?" + ex.Message;
        //        return false;
        //    }
        //}

        public bool SaveData(List<Customer> customer)
        {
            try
            {
                string fmessage = "";
                int i = 0;
                foreach (var item in customer)
                {

                    var sitem = _db.Customers.SingleOrDefault(s => s.CustomerCode == item.CustomerCode);
                    if (sitem != null)
                    {
                        fmessage += sitem.CustomerCode + " /";
                        i++;
                    }
                    else
                    {
                        Customer Customer= new Customer()
                        {
                            CustomerCode = item.CustomerCode,
                            Company = item.Company,
                            Contact = item.Contact,
                            Tel = item.Tel,
                            Mobile = item.Mobile,
                            Address = item.Address,
                        };
                        _db.Customers.Add(Customer);
                        _db.SaveChanges();
                    }
                }
                if (fmessage != "")
                    this.TempData["Message"] = (customer.Count() - i).ToString() + "条数据导入成功!" + fmessage + "因为编码重复而导入失败!如果不再导入请关闭窗口。";
                else
                    this.TempData["Message"] = customer.Count().ToString() + "条数据导入成功!如果不再导入请关闭窗口。";
                return true;
            }
            catch (Exception ex)
            {
                this.TempData["Message"] = "导入失败:请检查编码是否符合规范?" + ex.Message;
                return false;
            }
        }

 在View视图中:

@model CMS.Models.Customer

@{
    ViewBag.Title = "ImportExcel";
    Layout = "~/Areas/Mana/Views/Shared/_Layout.cshtml";
}

<script type="text/javascript">
    function validate() {
        var gimport = $("#import").val();
        if (gimport == "") {
            alert("请先选择文件!");
            return false;
        }
    }
</script>
<div class="navigation"><a href="javascript:history.go(-1);" class="back">后退 </a>首页 > 客户管理 > 导入用户</div>
	<div class="easyui-tabs" id="innertab" style="width:auto;height:auto;">
		<div title="基本信息" style="padding:10px">
		<div id="contentTab">
            <div class="tab_con" style="display:block;">
            @using (Html.BeginForm("ImportExcel", "Customer", FormMethod.Post, new { enctype = "multipart/form-data" }))
            {
                @Html.ValidationSummary(true)
                <table class="form_table">
                    <tr><td><input type="file" name="importxls" id="import" /></td></tr>
                    <tr><td><input type="submit" value="开始导入!" name="save" onclick="javascript:return validate();"/> </td></tr>
                    <tr><td><span style="color:Red">@TempData["Message"] </span></td></tr>
                    <tr>
                      <td>
                       <b>导入Excel表格式说明<font color="green">(特别注意红框处标题头部文字和工作表名称是否与图例相同!)</font></b><br />
                        <img src="/Content/images/importnote.jpg" />
                           <br />如果本机没有安装Excel提供程序,请微软官方下载:
                        <a href="http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe "  target="_blank">下载地址</a>
                       </td>
                    </tr>
                </table>                
            }
            </div>            
		</div>	
		</div>		
	</div>