从Excel文件中读取内容
从Excel文件中读取内容
global::System.Web.HttpPostedFileBase file = Request.Files["txtFile"]; string FileName; string savePath; if (file == null || file.ContentLength <= 0) { ViewBag.error = "文件不能为空"; return View(); } else { string filename = global::System.IO.Path.GetFileName(file.FileName); int filesize = file.ContentLength; //获取上传文件的大小单位为字节byte string fileEx = global::System.IO.Path.GetExtension(filename); //获取上传文件的扩展名 string NoFileName = global::System.IO.Path.GetFileNameWithoutExtension(filename); //获取无扩展名的文件名 int Maxsize = 4000*1024; //定义上传文件的最大空间大小为4M string FileType = ".xls,.xlsx"; //定义上传文件的类型字符串 FileName = NoFileName + global::System.DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx; if (!FileType.Contains(fileEx)) { ViewBag.error = "文件类型不对,只能导入xls和xlsx格式的文件"; return View(); } if (filesize >= Maxsize) { ViewBag.error = "上传文件超过4M,不能上传"; return View(); } string path = global::System.AppDomain.CurrentDomain.BaseDirectory + "Download/excel/";//路径 savePath = global::System.IO.Path.Combine(path, FileName); file.SaveAs(savePath); } string strConn; strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + savePath + ";" + "Extended Properties=Excel 8.0"; var conn = new global::System.Data.OleDb.OleDbConnection(strConn); conn.Open(); var myCommand = new global::System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", strConn);//查找表名为Sheet1表内的值 var myDataSet = new global::System.Data.DataSet(); try { myCommand.Fill(myDataSet, "ExcelInfo"); } catch (global::System.Exception ex) { ViewBag.error = ex.Message; return View(); } var table = myDataSet.Tables["ExcelInfo"].DefaultView.ToTable(); var pBll = new PersonnelBLL(); var msg = string.Empty; for (int i = 0; i < table.Rows.Count; i++) { var perModel = new Personnel(); perModel.RjPerId = table.Rows[i][0].ToString();//第一列 perModel.LoginName = table.Rows[i][1].ToString(); perModel.Name = table.Rows[i][1].ToString(); perModel.Gender = table.Rows[i][4].ToString().Equals("男") ? 0 : 1; perModel.Positional = table.Rows[i][5].ToString(); perModel.Birthday = string.IsNullOrEmpty(table.Rows[i][7].ToString().Trim()) ? DateTime.Now.ToString() : table.Rows[i][7].ToString().Trim(); perModel.IDCard = table.Rows[i][8].ToString(); perModel.Mobile = table.Rows[i][10].ToString(); perModel.Status = table.Rows[i][11].ToString().Equals("有效") ? 0 : 1; perModel.Password = null; perModel.Sort = 100+i; perModel.AddTime = DateTime.Now; perModel.ID = null; perModel.Description = "数据导入"; perModel.DepartmentID = ""; var returnNum = pBll.Insert(perModel, new string[] {"9a0e6c1860aa4b22a57fb847b87fcaf7"}); }