MVC 从Excel导入到DataTable-白红宇

MVC 从Excel导入到DataTable

阅读量：6854 次

发布时间：2019-06-26

本文共 12481 字，大约阅读时间需要 41 分钟。

1、前台界面需要引用：

1 <script src="~/Scripts/jquery-1.10.2.js"></script>

2 <script src="~/Scripts/ajaxfileupload.js"></script>

　ajaxfileupload下载地址在这里：http://files.cnblogs.com/files/kissdodog/ajaxfileupload_JS_File.rar　

2、前台代码

1 2

3、JS代码

1 $(function () { 2         $(":button").click(function () { 3             if ($("#file").val().length > 0) { 4                 ajaxFileUpload(); 5             } 6             else { 7                 $.messager.alert("提示", "请选择Excel文档！"); 8             } 9         })10     })11 12 13     //上传控件14     function ajaxFileUpload() {15         $.ajaxFileUpload16         (17             {18                 url: host_key + '/Handler/Upload.ashx', //用于文件上传的服务器端请求地址19                 secureuri: false, //一般设置为false20                 fileElementId: 'file', //文件上传空间的id属性  21                 dataType: 'json', //返回值类型 一般设置为json22                 data: { action: "upload" },23                 success: function (data)  //服务器成功响应处理函数24                 {25                     debugger26                     if (data.error == 1) {27                         $.messager.alert("提示", data.result);28                         return;29                     }30                     $("#grid").datagrid("reload");31                     //InitGrid(data);32                     $.messager.alert("提示", "文件已上传，数据加载完毕！");33                 },34                 error: function (data, e)//服务器响应失败处理函数35                 {36                     $.messager.alert("提示", "上传失败！");37                 }38             }39         )40         return false;41     }

4、请求的一般处理程序代码（可根据自己的情况更改）

1 ///   2     /// Upload 的摘要说明  3     /// 
  4     public class Upload : IHttpHandler, System.Web.SessionState.IRequiresSessionState  5     {  6         public void ProcessRequest(HttpContext context)  7         {  8             context.Response.ContentType = "text/plain";  9             10             HttpPostedFile file = context.Request.Files["file"]; 11             string saname = context.Request["action"]; 12             string resule = ""; 13             string fileExtenSion; 14             fileExtenSion = Path.GetExtension(file.FileName); 15              16             if (fileExtenSion.ToLower() != ".xls" && fileExtenSion.ToLower() != ".xlsx") 17             { 18                 resule = "上传的文件格式不正确！"; 19                 context.Response.Write("{\"error\":1,\"url\":\"" + file.FileName + "\",\"result\":\"" + resule + "\"}"); 20             } 21             else if (file.ContentLength > 1048576) 22             { 23                 resule= "文件不能超过1M！"; 24                 context.Response.Write("{\"error\":1,\"url\":\"" + file.FileName + "\",\"result\":\"" + resule + "\"}"); 25             } 26             else 27             { 28  29                 DataTable dt = xsldata(file, saname, fileExtenSion);//excle转换成datatable 30                 resule = DataInSql(dt, context); 31                 context.Response.Write("{\"error\":0,\"url\":\"" + file.FileName + "\",\"result\":\"" + resule + "\"}"); 32             } 33         } 34  35         OperContext oc = OperContext.CurrentContext; 36         private string DataInSql(DataTable dt, HttpContext context) 37         { 38             int errorcont = 0; 39             int insertcount = 0;//记录插入成功条数   40             int updatecount = 0;//记录更新信息条数   41  42             int sumcount = dt.Rows.Count; 43  44             #region 获取导入的文件名 45             HttpPostedFile file = context.Request.Files["file"]; 46             string fileExtenSion; 47             fileExtenSion = Path.GetExtension(file.FileName); 48             //if (file.ContentLength > 100000) 49             //{ 50             //    return "文件不能超过100KB！"; 51             //} 52             #endregion 53  54             //System.Data.DataTable sumdt = dt.Copy(); 55             DataRow dr = dt.NewRow(); 56  57             tb_Detail detail = new tb_Detail(); 58             string guid = GenerateRandomCode(5); 59             for (int i = 0; i < sumcount; i++) 60             { 61                 //判断 如果某一条数据执行失败，进入Catch循环后，继续执行for循环 62                 try 63                 { 64                     #region 65  66                     int len = dt.Rows[i][3].ToString().Length; 67                     string code = dt.Rows[i][1].ToString() == null ? "" : dt.Rows[i][1].ToString(); 68                     string idtype = dt.Rows[i][6].ToString() == null ? "" : dt.Rows[i][6].ToString(); 69                     string grade = dt.Rows[i][8].ToString() == null ? "" : dt.Rows[i][8].ToString(); 70                     string name = dt.Rows[i][3].ToString() == null ? "" : dt.Rows[i][3].ToString().Substring(1, len - 1); 71                     string nFirstName = dt.Rows[i][3].ToString() == null ? "" : dt.Rows[i][3].ToString().Substring(0, 1); 72                     string gender = dt.Rows[i][4].ToString() == null ? "" : dt.Rows[i][4].ToString(); 73                     string birth = Convert.ToString(dt.Rows[i][5]) == "" ? "" : Convert.ToString(dt.Rows[i][5]); 74                     string mobile = dt.Rows[i][13].ToString() == null ? "" : dt.Rows[i][13].ToString(); 75                     string address = dt.Rows[i][12].ToString() == null ? "" : dt.Rows[i][12].ToString(); 76                     string c_time = DateTime.Now.ToString("yyyy-MM-dd"); 77                     string idCard = dt.Rows[i][7].ToString() == null ? "" : dt.Rows[i][7].ToString(); 78                     int creatorId = AdminSystemInfo.CurrentUser.Uid == null ? 0 : AdminSystemInfo.CurrentUser.Uid; 79  80                     dr = dt.Rows[i]; 81  82                     tb_User_Info info = new tb_User_Info(); 83                     info.Name = name; 84                     info.nFisrtName = nFirstName; 85                     info.Gender = gender; 86                     if (string.IsNullOrWhiteSpace(birth)) info.Birth = null; 87                     else info.Birth = Convert.ToDateTime(birth); 88                     info.Mobile = mobile; 89                     info.address = address; 90                     info.C_Time = Convert.ToDateTime(c_time); 91                     info.IDcard = idCard; 92                     info.CreatorId = creatorId; 93                     info.IsDel = 0; 94  95                     List
     
       appList = oc.iBllSession.Itb_User_Info_HH_BLL.GetListBy(p => p.IsDel == 0); 96                     #region 判断是或否存在患者信息问题 97  98                     if (!string.IsNullOrWhiteSpace(mobile) || !string.IsNullOrWhiteSpace(idCard)) 99                     {100                         appList = appList.Where(o => o.Mobile == mobile || o.IDcard == idCard).ToList();101                     }102                     if (!string.IsNullOrWhiteSpace(dt.Rows[i][3].ToString()))103                     {104                         appList = appList.Where(o => (o.nFisrtName == nFirstName && o.Name == name)).ToList();105                     }106                     if (!string.IsNullOrWhiteSpace(gender))107                     {108                         appList = appList.Where(o => o.Gender == gender).ToList();109                     }110                     if (appList.Count > 0)111                     {112                         updatecount++;113                     }114                     else115                     {116                         int num = oc.iBllSession.Itb_User_Info_HH_BLL.Add(info);117                         if (num > 0)118                         {119                             tb_User_Member member = new tb_User_Member();120 121                             member.uId = info.Id;122                             member.ParentMemberId = 0;123                             member.Mtype = 1;124 125                             int nember = oc.iBllSession.Itb_User_Member_HH_BLL.Add(member);126                         }127                         insertcount++;128 129                     }130                     #endregion131                 }132                 catch (Exception ex)133                 {134                     135                     detail.Code = dr[1].ToString() == null ? "" : dr[1].ToString();136                     detail.Name = dr[3].ToString() == null ? "" : dr[3].ToString();137                     detail.Gender = dr[4].ToString() == null ? "" : dr[4].ToString();138                     DateTime datatime=new DateTime();139                     if (string.IsNullOrWhiteSpace(dr[5].ToString())) detail.Birthday = null;140                     else if (DateTime.TryParse(dr[5].ToString(), out datatime)) detail.Birthday = Convert.ToDateTime(dr[5].ToString());141                     else detail.Birthday = null;142                     detail.IDType = dr[6].ToString() == null ? "" : dr[6].ToString();143                     detail.IDCard = dr[7].ToString() == null ? "" : dr[7].ToString();144                     detail.Grade = dr[8].ToString() == null ? "" : dr[8].ToString();145                     detail.Address = dr[12].ToString() == null ? "" : dr[12].ToString();146                     detail.CreatorId = AdminSystemInfo.CurrentUser.Uid;147                     detail.Creator = AdminSystemInfo.CurrentUser.uName;148                     detail.C_time = Convert.ToDateTime(DateTime.Now.ToString("yyyy-MM-dd"));149                     detail.Mobile = dr[13].ToString() == null ? "" : dr[13].ToString();150                     detail.State = 0;151                     detail.UploadId = Convert.ToInt32(guid);152                     oc.iBllSession.Itb_Detail_HH_BLL.Add(detail);153 154                     LogHelper.Error(ex);155                     continue;156                 }157             }158            159             return "成功上传：(" + insertcount + ")条数据！重复数据：(" + updatecount + ")条";160         }161 162         /// 
      163         /// 讲excle转换成datatable164         /// 
165         /// 
      excle文件166         /// 
      登录名167         /// 
      扩展名168         /// 
      169         private DataTable xsldata(HttpPostedFile file, string saname, string fileExtenSion)170         {171             try172             {173                 if (!Directory.Exists(serverPath))174                 {175                     Directory.CreateDirectory(serverPath);176                 }177                 string filePath = Path.Combine(serverPath, DateTime.Now.ToString("yyyyMMddhhmmss") + "_" + file.FileName);178                 file.SaveAs(filePath);179                 //HDR=Yes，这代表第一行是标题，不做为数据使用 ，如果用HDR=NO，则表示第一行不是标题，做为数据来使用。系统默认的是YES  180                 string connstr2003 = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";181                 string connstr2007 = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES\"";182                 OleDbConnection conn;183                 if (fileExtenSion.ToLower() == ".xls")184                 {185                     conn = new OleDbConnection(connstr2003);186                 }187                 else188                 {189                     conn = new OleDbConnection(connstr2007);190                 }191                 conn.Open();192                 DataTable dt = new DataTable();193                 try194                 {
     　　　　　　　　　　　DataTable table = conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);                     string tableName;                      tableName = table.Rows[0]["Table_Name"].ToString();                     string sql = "select * from" + "[" + tableName + "]";196                     OleDbCommand cmd = new OleDbCommand(sql, conn);197 198                     OleDbDataReader sdr = cmd.ExecuteReader();199                     dt.Load(sdr);200                     sdr.Close();201                     conn.Close();202 203                 }204                 catch (Exception ex)205                 {206                     LogHelper.Error(ex);207                 }208 209                 //删除服务器里上传的文件  210                 if (File.Exists(filePath))211                 {212                     File.Delete(filePath);213                 }214                 return dt;215             }216             catch (Exception ex)217             {218                 LogHelper.Error(ex);219                 return null;220             }221         }222 223         public bool IsReusable224         {225             get226             {227                 return false;228             }229         }230     }