首先使用ajax以POST的方式提交excel文件:$("#btnUploadExcelSave").click(function () {var fileObj = document.getElementById("file").files[0]; // js 获取文件对象var FileController = "../FileHandler.ashx?type=3";// 接收上传文件的后台地址// FormData 对象var form = new FormData();form.append("author", "hooyes");// 可以增加表单数据form.append("file", fileObj);// 文件对象// XMLHttpRequest 对象var xhr = new XMLHttpRequest();xhr.open("post", FileController, true);xhr.onload = function () {// alert("上传完成!");};xhr.upload.addEventListener("progress", progressFunction, false);xhr.send(form);});
然后定义后台方法处理Excel:
FileHandler.ashx/// <summary>/// Excel导入到数据库/// </summary>/// <param name="context"></param>public string UploadExcelData(HttpContext context){var flist = context.Request.Files;for (int i = 0; i < flist.Count; i++){var c = flist[i];string IsXls = System.IO.Path.GetExtension(c.FileName).ToString().ToLower();if (IsXls != ".xls"){return "格式不正确!";}string savePath = System.Configuration.ConfigurationManager.AppSettings["temFilePath"].ToString() + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";c.SaveAs(context.Server.MapPath(savePath));//将文件保存到服务器,,因为火狐浏览器获取不到客户端的路径。DataSet ds = ExcelSqlConnection(context.Server.MapPath(savePath), "tranportTask");//将excel文件转换为DataSet。//这里处理你业务逻辑… }return "";} public static System.Data.DataSet ExcelSqlConnection(string filepath, string tableName) { string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties='Excel 8.0;HDR=YES;IMEX=1'"; System.Data.OleDb.OleDbConnection ExcelConn = new System.Data.OleDb.OleDbConnection(strCon); try { string strCom = string.Format("SELECT * FROM [Sheet1$]"); ExcelConn.Open(); System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, ExcelConn); DataSet ds = new DataSet(); myCommand.Fill(ds, "[" + tableName + "$]"); ExcelConn.Close(); return ds; } catch { ExcelConn.Close(); return null; } }
泪,一种痛苦的雨滴,不知从什么时候开始已在我的世界下个不停。