C# Excel导入

 

通过Excel导入，入库数据

 

cshtml代码：

<div class="modal-header">
    <h4 class="modal-title">批量入库</h4>
    <button type="button" class="close" data-dismiss="modal" aria-hidden="true">×</button>
</div>
<div class="modal-body">
    <div class="card">
        <input type="file" id="ImportFile" name="ImportFile" class="dropify" />
    </div>
    所选文件:<p id="CunFileName">未上传</p>
</div>
<div class="modal-footer">
    <button type="button" class="btn btn-info waves-effect" data-dismiss="modal" onclick="ImportCardInfo()">确定</button>
    <button type="button" class="btn btn-default waves-effect" data-dismiss="modal">取消</button>
</div>



<script>
    $("#ImportFile").change(function () {
        var FileSrcs = $("#ImportFile").val();
        var FileArr = new Array();
        FileArr = FileSrcs.split('\\');
        var FileSrc = FileArr[FileArr.length - 1];
        $("#CunFileName").text(FileSrc);
    });

    function ImportCardInfo()
    {        
        var files = $('input[name="ImportFile"]').prop('files');//获取到文件列表
        if (files.length == 0) {
            alert('请选择文件');
            return;
        } else {            
            var formFile = new FormData();            
            formFile.append("DrawingFilePath", files[0]); //加入文件对象
            $.ajax({
                url: "/Card/ImportCardInfo",
                data: formFile,
                type: "Post",
                dataType: "json",
                cache: false,//上传文件无需缓存
                processData: false,//用于对data参数进行序列化处理 这里必须false
                contentType: false, //必须
                beforeSend: function () {
                    //提示上传中…
                    alert("正在上传，请稍后…");
                },
                success: function (data) {
                    if (data.succ) {
                        alert(data.msg);
                        $("#ContentInfoDiv").load("/Card/CardPage");                        
                    }
                    else {
                        ErrorMsg(data.msg);
                        return false;
                    }
                }
            });
        }
    }
</script>

　

controller：

#region 批量入库
        
        /// <summary>
        /// 批量入库
        /// </summary>
        public JsonResult ImportCardInfo(HttpPostedFileBase[] DrawingFilePath)
        {
            string msg = null;//返回的信息
            bool succ = false;//是否成功  

            string UpFileSrc = "";
            try
            {
                if (DrawingFilePath != null && DrawingFilePath.Count() > 0)
                {
                    if (DrawingFilePath[0] != null)
                    {
                        #region excel存至本地
                        //目录
                        string directoryPathTEST = Server.MapPath("~/tempExc");
                        if (!Directory.Exists(directoryPathTEST))
                            Directory.CreateDirectory(directoryPathTEST);
                        //文件路径
                        UpFileSrc = directoryPathTEST + "/" + Guid.NewGuid() + ".xls";
                        DrawingFilePath[0].SaveAs(UpFileSrc);
                        #endregion

                        int rowscount = 0;
                        string returnInfo = ImportDataInfo(UpFileSrc,out rowscount);

                        if (returnInfo == "succ")
                        {
                            succ = true;
                            msg = "批量上传成功！";
                        }
                        else
                        {
                            succ = false;
                            if (rowscount == 0)
                            {
                                msg = "未检测到数据源！";
                            }
                            else
                            {
                                msg = "批量上传失败！";
                            }
                        }
                    }
                    else
                    {
                        succ = false;
                        msg = "文件不能为空！";
                    }
                }
                else
                {
                    succ = false;
                    msg = "文件不能为空！";
                }
            }
            catch (Exception ex)
            {
                succ = false;
                msg = ex.Message;
            }

            var json = new { msg = msg, succ = succ };
            return Json(json);//返回json
        }
        
        #region 批量上传设备
        public string ImportDataInfo(string FileSrc,out int rowscount)
        {
            rowscount = 0;
            try
            {
                if (!string.IsNullOrWhiteSpace(FileSrc))
                {
                    //以本地路径上传的excel做为数据源
                    string conStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0; Data source={0}; Extended Properties=Excel 12.0;", FileSrc);
                    using (OleDbConnection conn = new OleDbConnection(conStr))
                    {
                        conn.Open();
                        ////获取所有Sheet的相关信息
                        //DataTable dtSheet = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);
                        ////获取第一个 Sheet的名称
                        //string sheetName = dtSheet.Rows[0]["Table_Name"].ToString();
                        string sheetName = "CardTemp$";
                        string sql = string.Format("select * from [{0}]", sheetName);
                        using (OleDbDataAdapter oda = new OleDbDataAdapter(sql, conn))
                        {
                            DataTable dt = new DataTable();
                            oda.Fill(dt);
                            rowscount = dt.Rows.Count;

                            List<CardTable> cardList = new List<CardTable>();
                            //i代表行  自动剔除标题行                          
                            for (int i = 0; i < dt.Rows.Count; i++)
                            {
                                CardTable cardInfo = new CardTable();
                                cardInfo.CardID = CommHelper.CreatePKID("card");
                                cardInfo.CCID = dt.Rows[i][0].ToString();
                                cardInfo.Operator = dt.Rows[i][1].ToString();
                                cardInfo.CardType = dt.Rows[i][2].ToString();
                                cardInfo.MealID = dt.Rows[i][3].ToString();
                                cardInfo.SleepLong = Convert.ToDateTime("2000-01-01");
                                cardInfo.IOStatus = 1;
                                db.CardTable.Add(cardInfo);

                            }
                            if (db.SaveChanges() > 0)
                            {
                                return "succ";
                            }
                            else
                            {
                                return "失败";
                            }
                        }
                    }
                }
                else
                {
                    return "未检测到数据源！";
                }
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
        }
        #endregion
        #endregion