public ActionResult UploadScore(IEnumerable <HttpPostedFileBase> files, int id)
{
if (files != null)
{
foreach (var file in files)
{
string fileExtentian = file.FileName.Substring(file.FileName.LastIndexOf(".")).ToLower();
if (fileExtentian == ".xls" || fileExtentian == ".xlsx")
{
string newFileName = DateTime.Now.ToString("yyyyMMddhhmmss") + fileExtentian;
string path = Server.MapPath("~/Content/UploadFiles/Score_" + newFileName);
file.SaveAs(path);
var course = db.Courses.SingleOrDefault(c => c.Id == id);
if (course != null)
{
//从Excel中导入学生考核课程的成绩，同一学生同一课程的成绩可以重复导入，后导入的覆盖先导入的。
ImportReturnModel b = ImportScoresFromExcel(path, course);
if (b.Code == 1)
{
return(Json(new { code = "101", msg = "成绩数据导入成功。" }));
}
else
{
string others = "未找到对应信息，无法导入其成绩。\n";
foreach (string s in b.Others)
{
others += $"[{s}]";
}
return(Json(new { code = "102", msg = "导入遇到一些问题。", data = others }));
}
}
else
{
return(Json(new { code = "100", msg = $"未找到相应课程" }));
}
}
}
}
return(Json(new { code = "100", msg = "成绩数据上传失败" }));
}
/// <summary>
/// 从上传的学生成绩Excel中导入学生的指定课程成绩信息
/// </summary>
/// <param name="path"></param>
/// <param name="cid">课程编号</param>
/// <returns></returns>
private ImportReturnModel ImportScoresFromExcel(string path, Course course)
{
//int result = 0;//0：失败
var result = new ImportReturnModel
{
Code = 0,
Others = new List <string>()
};
string connectionString = "Provider=Microsoft.Jet.OleDb.4.0; Data Source=" + path + "; Extended Properties=Excel 8.0;";
using (OleDbConnection Connection = new OleDbConnection(connectionString))
{
DataTable dt = new DataTable();
Connection.Open();
using (OleDbCommand command = new OleDbCommand())
{
command.Connection = Connection;
command.CommandText = "SELECT * FROM [成绩表$]";
OleDbDataAdapter adapter = new OleDbDataAdapter(command);
adapter.Fill(dt);
foreach (DataRow dr in dt.Rows)
{
string number = dr["学号"].ToString().Trim();
string studentName = dr["姓名"].ToString().Trim();
//string courseName = dr["课程"].ToString().Trim();
float.TryParse(dr["成绩"].ToString().Trim(), out float scoreValue);
//判断导入的学生成绩是否有对应学生信息。
var student = db.Students.SingleOrDefault(stu => stu.Number == number);
if (student == null)
{
result.Others.Add(number + studentName); //如果没有，则收集学生学号和姓名。
continue;
}
//查找该学生该门课程成绩是否存在，如果存在则更新数据，不存在则新增数据。
var score = db.Scores.SingleOrDefault(ss => ss.Student.Number == number && ss.Course.CourseName == course.CourseName);
if (score == null)
{
Score s = new Score
{
Student = student,
Course = course,
Value = scoreValue
};
db.Scores.Add(s);
}
else
{
score.Value = scoreValue;
db.Entry <Score>(score).State = System.Data.Entity.EntityState.Modified;
}
}
adapter.Dispose();
int i = db.SaveChanges();
if (i > 0)
{
result.Code = 1; //1：成功
}
}
}
return(result);
}
