以前4.1做新生系统的时候为了保持基础的信息和新生的信息保持一致,曾经想做一个多个表拼接导出到excel中,然后再导入到基础表中!可是后来这件事情就不了了之了!现在在考评系统,这个类似的多表拼接导出excel的任务又分配到我的头上,看来该是我学习的知识,躲也躲不掉的!
现在就让我一一来描述吧!
现在就让我一一来描述吧!
现在业务是这样的:我们需要做一个导出,需要把所有教师被学生评教之后的成绩都导出来,他们的表的关系如下图:
其中关于对基础表的操作,需要调用基础的接口,在这里我只写出考评的逻辑,仅供参考:
B层的:
#region 李少然 2016-3-9 22:22:16 QueryAllTeacherScoreExport()导出详细的成绩
/// <summary>
/// 查询所有的教师课程ID,课程名称,课程类型,老师名称,学院名称,上课总人数
/// </summary>
/// <returns></returns>
public List<ExportScore> QueryAllTeacherScoreExport()
{
EvaluationCheckScoreBll bll = new EvaluationCheckScoreBll();
List<ExportScore> listExport = new List<ExportScore>();
List<BasicOnClassViewModel> listOnClass = new List<BasicOnClassViewModel>();
try
{
listOnClass = BasicOnClassWCF.QueryAllTeacherCourse();
//调用基础的接口查询课程类型等信息
foreach (var itemOnClass in listOnClass)
{
string onclassid = itemOnClass.OnClassID.ToString();
//查询该教师的总分数
List<EvaluationAssessScoreEntity> Allscore = this.MyBaseCurrentDal.LoadItems(p => p.EvaluationTeacherCourseEntityTeacherCourseId == onclassid).ToList();
//根据课程ID查询checkscore表
List<EvaluationCheckScoreEntity> listscore = bll.QueryCheckScore(itemOnClass.OnClassID.ToString());
String[] arrayScore =new string[15];
arrayScore = listscore[0].CheckScore.Split(new char[] { '/' });
//用构造器实例化一个评估情况数据契约实体,并给属性赋值
ExportScore enExport = new ExportScore
{
TeacherName = itemOnClass.TeacherName,
DepartmentName = itemOnClass.organizationName,
CourseNature = itemOnClass.CourseTypeName,
CourseName = itemOnClass.CourseName,
//总分
TeacherScore = Allscore[0].Score,
AllPeople = itemOnClass.studentSum,
//根据外键到评估记录实体查询已评人数信息
AccoutPeople = listscore.Count(),
//根据外键查询每个课程中题目的得分数和题目
Stem1 = arrayScore.Count() >= 1 ? arrayScore[0].ToString().Trim() : "0",
Stem2 = arrayScore.Count() >= 2 ? arrayScore[1].ToString(): "0",
Stem3 = arrayScore.Count() >= 3 ? arrayScore[2].ToString(): "0",
Stem4 = arrayScore.Count() >= 4 ? arrayScore[3].ToString(): "0",
Stem5 = arrayScore.Count() >= 5 ? arrayScore[4].ToString(): "0",
Stem6 = arrayScore.Count() >= 6 ? arrayScore[5].ToString(): "0",
Stem7 = arrayScore.Count() >= 7 ? arrayScore[6].ToString(): "0",
Stem8 = arrayScore.Count() >= 8 ? arrayScore[7].ToString(): "0",
Stem9 = arrayScore.Count() >= 9 ? arrayScore[8].ToString(): "0",
Stem10 = arrayScore.Count() >= 10 ? arrayScore[9].ToString(): "0",
Stem11 = arrayScore.Count() >= 11 ? arrayScore[10].ToString(): "0",
Stem12 = arrayScore.Count() >= 12 ? arrayScore[11].ToString(): "0",
Stem13 = arrayScore.Count() >= 13 ? arrayScore[12].ToString(): "0",
Stem14 = arrayScore.Count() >= 14 ? arrayScore[13].ToString(): "0",
Stem15 = arrayScore.Count() >= 15 ? arrayScore[14].ToString() : "0"
};
//将评估情况数据契约实体添加到评估情况数据契约集合中
listExport.Add(enExport);
}
}
catch (Exception)
{
throw new NotImplementedException();
}
return listExport;
}
#endregion
然后在前台的MVC层的controller中调用B层的逻辑:
#region 导出所有的学生成绩信息 2016-3-9 23:06:36 李少然
public ActionResult DetailScoreExport()
{
//调用B层的方法
List<ExportScore> listExport = new List<ExportScore>();
listExport = QueryResultWCF.QueryAllTeacherScoreExport();
Hashtable headNames = new Hashtable();
headNames.Add("TeacherName", "教师姓名");
headNames.Add("DepartmentName", "学院名称");
headNames.Add("CourseNature", "课程类型");
headNames.Add("CourseName", "课程名称");
headNames.Add("TeacherScore", "总成绩");
headNames.Add("AllPeople", "应评人数");
headNames.Add("AccoutPeople", "实评人数");
headNames.Add("Stem1", "题目1");
headNames.Add("Stem2", "题目2");
headNames.Add("Stem3", "题目3");
headNames.Add("Stem4", "题目4");
headNames.Add("Stem5", "题目5");
headNames.Add("Stem6", "题目6");
headNames.Add("Stem7", "题目7");
headNames.Add("Stem8", "题目8");
headNames.Add("Stem9", "题目9");
headNames.Add("Stem10", "题目10");
headNames.Add("Stem11", "题目11");
headNames.Add("Stem12", "题目12");
headNames.Add("Stem13", "题目13");
headNames.Add("Stem14", "题目14");
headNames.Add("Stem15", "题目15");
//调用以下方法就可以,泛型T,集合,汉字表头集合,是可以变的,"application/vnd.ms-excel"不用变 ,文件名也可以变。
return File(ExportManager.ExportExcel(listExport, headNames),"application/vnd.ms-excel", "所有教师评估成绩表.xls");
}
#endregion
这样,导出成绩就成功了!其实很多事情,尤其是和代码相关联的,只要需求理明白了,代码都不成问题!另一个就是要看你的代码的质量了!