@using (Html.BeginForm("Student", "Excel", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<div style="margin-top: 20px;">
<fieldset id="myfieldset1">
<legend>学生信息导入</legend>
<p>
选择文件：<input id="FileUpload1" type="file" name="files" style="width: 250px; height: 24px;
background: White" class="easyui-validatebox" /></p>
<p>
<input id="btnImport1" type="submit" value="导入" style="width: 60px; height: 28px;" /></p>
<p style="color: Red; text-align: center;">@ViewBag.errorstu</p>
</fieldset>
</div>
}
public class ExcelController : Controller
{
ContractInfoBLL contract;
TeacherInfoBLL TIBLL;
ParentBLL PBLL;
public ExcelController()
{
if (contract == null)
{
contract = new ContractInfoBLL();
}
if (TIBLL == null)
{
TIBLL = new TeacherInfoBLL();
}
if (PBLL == null)
{
PBLL = new ParentBLL();
}
}
public ActionResult Index(bool Code = true, string Message="")
{
if (!Code)
{
ViewBag.Message = Message;
}
return View();
}
string StuExcel = ApiHelper.GetWebConfig("StuExcel", @"E:/需求/20160520 家宝贝/新建文件夹/学生用户.xlsx");
/// <summary>
/// Excel学生用户导入
/// </summary>
/// <returns></returns>
[HttpPost]
public ActionResult Student(HttpPostedFileBase filebase)
{
#region
HttpPostedFileBase file = Request.Files["files"];
string FileName;
string savePath;
if (file == null || file.ContentLength <= )
{
ViewBag.error = "文件不能为空";
return View();
}
else
{
string filename = Path.GetFileName(file.FileName);
int filesize = file.ContentLength;//获取上传文件的大小单位为字节byte
string fileEx = System.IO.Path.GetExtension(filename);//获取上传文件的扩展名
string NoFileName = System.IO.Path.GetFileNameWithoutExtension(filename);//获取无扩展名的文件名
int Maxsize = * ;//定义上传文件的最大空间大小为4M
string FileType = ".xls,.xlsx";//定义上传文件的类型字符串 FileName = NoFileName + 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 = AppDomain.CurrentDomain.BaseDirectory + "App_Data/";
savePath = Path.Combine(path, FileName);
file.SaveAs(savePath);
}
#endregion
List<Student_Excel> stuList = new ExcelBLL().GetStudentByExcel(savePath);
string Meg = "";
int ItemSchool = ;
int classID = ;
int i = ;
foreach (var item in stuList)
{ bool ishavecode = contract.IsHaveContractCode(item.ContractCode);
if (!ishavecode)
{
i++;
Meg = "学籍号重复";
LoggerFactory.Instance.Logger_Info("学生用户_序号" + item.Number + "_学籍号：" + item.ContractCode + "_学生姓名：" + item.Name + "_原因：" + Meg);
}
else
{
ItemSchool = contract.GetGardenByName(item.ItemSchoolName);
classID = contract.GetClassInfoID(ItemSchool, item.ClassName);
if (classID != )
{
DateTime da = DateTime.Now;
ContractInfoList model = new ContractInfoList();
model.ContractCode = item.ContractCode;
model.ContractStatus = (int)ContractStatus.Normal;
model.AdmissionsTeacherID = contract.GetTeacherIDbyName(item.TeacherName);
model.StudentName = item.Name;
model.StudentGender = item.Sex == "男" ? : ;
model.ParentName = item.ParentName;
model.ParenRelation = item.ParenRelation;
model.ParentTel = item.Phone;
model.EntranceTime = item.EntranceTime.HasValue ? item.EntranceTime.Value : DateTime.Now;
model.PaymentAmount = item.PaymentAmount != "" ? Convert.ToInt32(item.PaymentAmount) : ;
model.ItemSchool = ItemSchool;
model.AddTime = da;
model.UpdateTime = da;
model.Remarks = "";
model.ClassInfoID = classID; bool boo = contract.IsCreateContract(model);
if (!boo)
{
i++;
Meg = "创建失败";
LoggerFactory.Instance.Logger_Info("学生用户_序号" + item.Number + "_学籍号：" + item.ContractCode + "_学生姓名：" + item.Name + "_原因：" + Meg);
}
}
else
{
i++;
Meg = "班级不存在";
LoggerFactory.Instance.Logger_Info("学生用户_序号" + item.Number + "_学籍号：" + item.ContractCode + "_学生姓名：" + item.Name + "_原因：" + Meg);
}
}
} return Json(new { Code = Meg == "" ? true : false, Message = Meg != "" ? "未导入数据：" + i + "条" : "" });
//ViewBag.errorstu = "导入成功";
//return RedirectToAction("Index");
} }
public class ExcelBLL
{
public List<Student_Excel> GetStudentByExcel(string path)
{
List<Student_Excel> stuList = new List<Student_Excel>();
DataSet ds = Helper.GetDataSet("select * from [Sheet1$]", path);
DataTable dt = ds.Tables[];//忽略第一行表名，从第二行开始
foreach (DataRow row in dt.Rows)
{
if (row["序号"].ToString()!="")
{
DateTime ad = new DateTime();
if (row["入学日期"].ToString() != "")
{
ad = Convert.ToDateTime(row["入学日期"]);
}
stuList.Add(new Student_Excel()
{
Number = row["序号"].ToString(),
ContractCode = row["学籍号"].ToString(),
Name = row["学生姓名"].ToString(),
Sex = row["性别"].ToString(),
ClassName = row["所在班级"].ToString(),
Phone = row["紧急联系电话"].ToString(),
ParenRelation = row["与孩子关系"].ToString(),
ParentName = row["家长姓名"].ToString(),
EntranceTime = ad,//Convert.ToDateTime(row["入学日期"]),
TeacherName = row["招生老师"].ToString(),
PaymentAmount = row["收费金额"].ToString(),
ItemSchoolName = row["所在园区"].ToString(),
});
}
} return stuList;
}
}
public class Helper:BaseBLL
{
#region Excel 导入
private static string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0; HDR=yes; IMEX=2'"; public static DataSet GetDataSet(string sql, string path)
{
OleDbConnection conn = new OleDbConnection(string.Format(connstring, path));
OleDbCommand cmd = new OleDbCommand(sql, conn);
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataSet ds = new DataSet();//创建内存数据集
try
{
conn.Open();
da.Fill(ds);
return ds;
}
catch (Exception e)
{
//写日志 throw e;
}
finally
{
conn.Close();
}
}
#endregion
}