MVC 从Excel导入到DataTable
1、前台界面需要引用：
1 <script src="~/Scripts/jquery-1.10.2.js"></script>
2 <script src="~/Scripts/ajaxfileupload.js"></script>
ajaxfileupload下载地址在这里：https://files.cnblogs.com/files/kissdodog/ajaxfileupload_JS_File.rar
2、前台代码
1 <input type="file" id="file" name="file" class="easyui-linkbutton" /> 2 <input type="button" name="file" data-options="iconCls:'icon-up'" value="上传Excel" />
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 $.ajaxFileUpload 16 ( 17 { 18 url: host_key + '/Handler/Upload.ashx', //用于文件上传的服务器端请求地址 19 secureuri: false, //一般设置为false 20 fileElementId: 'file', //文件上传空间的id属性 <input type="file" id="file" name="file" /> 21 dataType: 'json', //返回值类型 一般设置为json 22 data: { action: "upload" }, 23 success: function (data) //服务器成功响应处理函数 24 { 25 debugger 26 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 /// <summary> 2 /// Upload 的摘要说明 3 /// </summary> 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<tb_User_Info> 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 else 115 { 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 #endregion 131 } 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 /// <summary> 163 /// 讲excle转换成datatable 164 /// </summary> 165 /// <param name="file">excle文件</param> 166 /// <param name="saname">登录名</param> 167 /// <param name="fileExtenSion">扩展名</param> 168 /// <returns></returns> 169 private DataTable xsldata(HttpPostedFile file, string saname, string fileExtenSion) 170 { 171 try 172 { 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 else 188 { 189 conn = new OleDbConnection(connstr2007); 190 } 191 conn.Open(); 192 DataTable dt = new DataTable(); 193 try 194 {
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 IsReusable 224 { 225 get 226 { 227 return false; 228 } 229 } 230 }
