• Excel 数据导入（OleDb）

     1 @using (Html.BeginForm("Student", "Excel", FormMethod.Post, new { enctype = "multipart/form-data" }))
 2 {
 3     
 4     <div style="margin-top: 20px;">
 5         <fieldset id="myfieldset1">
 6             <legend>学生信息导入</legend>
 7             <p>
 8                 选择文件：<input id="FileUpload1" type="file" name="files" style=" 250px; height: 24px;
 9                     background: White" class="easyui-validatebox" /></p>
10             <p>
11                 <input id="btnImport1" type="submit" value="导入" style=" 60px; height: 28px;" /></p>
12             <p style="color: Red; text-align: center;">@ViewBag.errorstu</p>
13         </fieldset>
14     </div>
15 }
    View Code
      1     public class ExcelController : Controller
  2     {
  3         ContractInfoBLL contract;
  4         TeacherInfoBLL TIBLL;
  5         ParentBLL PBLL;
  6         public ExcelController()
  7         {
  8             if (contract == null)
  9             {
 10                 contract = new ContractInfoBLL();
 11             }
 12             if (TIBLL == null)
 13             {
 14                 TIBLL = new TeacherInfoBLL();
 15             }
 16             if (PBLL == null)
 17             {
 18                 PBLL = new ParentBLL();
 19             }
 20         }
 21         public ActionResult Index(bool Code = true, string Message="")
 22         {
 23             if (!Code) 
 24             {
 25                 ViewBag.Message = Message;
 26             }
 27             return View();
 28         }
 29         string StuExcel = ApiHelper.GetWebConfig("StuExcel", @"E:/需求/20160520 家宝贝/新建文件夹/学生用户.xlsx");
 30         /// <summary>
 31         /// Excel学生用户导入
 32         /// </summary>
 33         /// <returns></returns>
 34         [HttpPost]
 35         public ActionResult Student(HttpPostedFileBase filebase)
 36         {
 37             #region
 38             HttpPostedFileBase file = Request.Files["files"];
 39             string FileName;
 40             string savePath;
 41             if (file == null || file.ContentLength <= 0)
 42             {
 43                 ViewBag.error = "文件不能为空";
 44                 return View();
 45             }
 46             else
 47             {
 48                 string filename = Path.GetFileName(file.FileName);
 49                 int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte
 50                 string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
 51                 string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
 52                 int Maxsize = 4000 * 1024;//定义上传文件的最大空间大小为4M
 53                 string FileType = ".xls,.xlsx";//定义上传文件的类型字符串
 54 
 55                 FileName = NoFileName + DateTime.Now.ToString("yyyyMMddhhmmss") + fileEx;
 56                 if (!FileType.Contains(fileEx))
 57                 {
 58                     ViewBag.error = "文件类型不对，只能导入xls和xlsx格式的文件";
 59                     return View();
 60                 }
 61                 if (filesize >= Maxsize)
 62                 {
 63                     ViewBag.error = "上传文件超过4M，不能上传";
 64                     return View();
 65                 }
 66                 string path = AppDomain.CurrentDomain.BaseDirectory + "App_Data/";
 67                 savePath = Path.Combine(path, FileName);
 68                 file.SaveAs(savePath);
 69             }
 70             #endregion
 71             List<Student_Excel> stuList = new ExcelBLL().GetStudentByExcel(savePath);
 72             string Meg = "";
 73             int ItemSchool = 0;
 74             int classID = 0;
 75             int i = 0;
 76             foreach (var item in stuList)
 77             {
 78 
 79                 bool ishavecode = contract.IsHaveContractCode(item.ContractCode);
 80                 if (!ishavecode)
 81                 {
 82                     i++;
 83                     Meg = "学籍号重复";
 84                     LoggerFactory.Instance.Logger_Info("学生用户_序号" + item.Number + "_学籍号：" + item.ContractCode + "_学生姓名：" + item.Name + "_原因：" + Meg);
 85                 }
 86                 else
 87                 {
 88                     ItemSchool = contract.GetGardenByName(item.ItemSchoolName);
 89                     classID = contract.GetClassInfoID(ItemSchool, item.ClassName);
 90                     if (classID != 0)
 91                     {
 92                         DateTime da = DateTime.Now;
 93                         ContractInfoList model = new ContractInfoList();
 94                         model.ContractCode = item.ContractCode;
 95                         model.ContractStatus = (int)ContractStatus.Normal;
 96                         model.AdmissionsTeacherID = contract.GetTeacherIDbyName(item.TeacherName);
 97                         model.StudentName = item.Name;
 98                         model.StudentGender = item.Sex == "男" ? 0 : 1;
 99                         model.ParentName = item.ParentName;
100                         model.ParenRelation = item.ParenRelation;
101                         model.ParentTel = item.Phone;
102                         model.EntranceTime = item.EntranceTime.HasValue ? item.EntranceTime.Value : DateTime.Now;
103                         model.PaymentAmount = item.PaymentAmount != "" ? Convert.ToInt32(item.PaymentAmount) : 0;
104                         model.ItemSchool = ItemSchool;
105                         model.AddTime = da;
106                         model.UpdateTime = da;
107                         model.Remarks = "";
108                         model.ClassInfoID = classID;
109 
110                         bool boo = contract.IsCreateContract(model);
111                         if (!boo)
112                         {
113                             i++;
114                             Meg = "创建失败";
115                             LoggerFactory.Instance.Logger_Info("学生用户_序号" + item.Number + "_学籍号：" + item.ContractCode + "_学生姓名：" + item.Name + "_原因：" + Meg);
116                         }
117                     }
118                     else
119                     {
120                         i++;
121                         Meg = "班级不存在";
122                         LoggerFactory.Instance.Logger_Info("学生用户_序号" + item.Number + "_学籍号：" + item.ContractCode + "_学生姓名：" + item.Name + "_原因：" + Meg);
123                     }
124                 }
125             }
126 
127             return Json(new { Code = Meg == "" ? true : false, Message = Meg != "" ? "未导入数据：" + i + "条" : "" });
128             //ViewBag.errorstu = "导入成功";
129             //return RedirectToAction("Index");
130         }
131      
132      }
    View Code
     1     public class ExcelBLL
 2     {
 3         public List<Student_Excel> GetStudentByExcel(string path)
 4         {
 5             List<Student_Excel> stuList = new List<Student_Excel>();
 6             DataSet ds = Helper.GetDataSet("select * from [Sheet1$]", path);
 7             DataTable dt = ds.Tables[0];//忽略第一行表名，从第二行开始
 8             foreach (DataRow row in dt.Rows)
 9             {
10                 if (row["序号"].ToString()!="")
11                 {
12                     DateTime ad = new DateTime();
13                     if (row["入学日期"].ToString() != "")
14                     {
15                         ad = Convert.ToDateTime(row["入学日期"]);
16                     }
17                     stuList.Add(new Student_Excel()
18                     {
19                         Number = row["序号"].ToString(),
20                         ContractCode = row["学籍号"].ToString(),
21                         Name = row["学生姓名"].ToString(),
22                         Sex = row["性别"].ToString(),
23                         ClassName = row["所在班级"].ToString(),
24                         Phone = row["紧急联系电话"].ToString(),
25                         ParenRelation = row["与孩子关系"].ToString(),
26                         ParentName = row["家长姓名"].ToString(),
27                         EntranceTime = ad,//Convert.ToDateTime(row["入学日期"]),
28                         TeacherName = row["招生老师"].ToString(),
29                         PaymentAmount = row["收费金额"].ToString(),
30                         ItemSchoolName = row["所在园区"].ToString(),
31                     });
32                 }
33             }
34 
35             return stuList;
36         }
37     }
    View Code
     1      public class Helper:BaseBLL
 2     {
 3 #region Excel 导入
 4          private static string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=yes; IMEX=2'";
 5 
 6          public static DataSet GetDataSet(string sql, string path)
 7          {
 8              OleDbConnection conn = new OleDbConnection(string.Format(connstring, path));
 9              OleDbCommand cmd = new OleDbCommand(sql, conn);
10              OleDbDataAdapter da = new OleDbDataAdapter(cmd);
11              DataSet ds = new DataSet();//创建内存数据集
12              try
13              {
14                  conn.Open();
15                  da.Fill(ds);
16                  return ds;
17              }
18              catch (Exception e)
19              {
20                  //写日志
21 
22                  throw e;
23              }
24              finally
25              {
26                  conn.Close();
27              }
28          }
29          #endregion
30     }
    View Code
  • 相关阅读:
    autojspro常用的代码和公共函数搜集整理，史上最全最完整
    C# List类型复制克隆副本以及插入数据，按照某个字段进行排序，去掉某些不符合要求的数据
    C# 将double类型值截取小数点后两位或者多位
    Excel表格中引用另外一格数据。写sql用的
    最全的Resharper快捷键汇总
    easyui datagrid没有ID情况下,easyui datagrid 删除行信息方法
    C# 通过JObject解析json对象
    c#中string字符串转为json与json转对象
    前端js拼接Json字符串，全
    Java+IDEA环境配置整合
  • 原文地址：https://www.cnblogs.com/xhyang/p/5757538.html
Copyright © 2020-2023  润新知