导出数据到Excel表格

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

开发工具与关键技术：Visual Studio

作者：黄海浪

撰写时间：2019年5月17日

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

将数据导出到Excel表格要通过查询条件查询筛选数据，然后创建工作表，工作簿，设计表头，写入数据

导出数据到Excel表格

控制器代码

public ActionResult ExportStudentAchievement(int AcademeID, int GradeID, int ClassID){

var listAchievement = from tbAchievement in myModels.PW_Achievement

group tbAchievement by tbAchievement.UserID into tbStudent

select new{

UserID = tbStudent.Key,

AchievementID = tbStudent.OrderByDescending(m => m.Achievement).FirstOrDefault().AchievementID,

Achievement = tbStudent.OrderByDescending(m => m.Achievement).FirstOrDefault().Achievement,

ExamNumber = tbStudent.OrderByDescending(m => m.ExamNumber).FirstOrDefault().ExamNumber,

EligibleTypeID = tbStudent.OrderByDescending(m => m.Achievement).FirstOrDefault().EligibleTypeID

};

List<AchievementVo> listResult = (from tbAchievement in listAchievement

join tbStudent in myModels.PW_Student on tbAchievement.UserID equals tbStudent.UserID

join tbClass in myModels.SYS_Class on tbStudent.ClassID equals tbClass.ClassID

join tbEligibleType in myModels.SYS_EligibleType on tbAchievement.EligibleTypeID equals tbEligibleType.EligibleTypeID

orderby tbAchievement.Achievement descending

select new AchievementVo{

StudentNumber = tbStudent.StudentNumber,//学号

StudentName = tbStudent.StudentName,//姓名

StudentSex = tbStudent.StudentSex,//性别

Class = tbClass.ClassName,//班级

Achievement = tbAchievement.Achievement,//最优成绩

EligibleType = tbEligibleType.EligibleTypeName,//合格类型

AcademeID = tbStudent.AcademeID,

GradeID = tbStudent.GradeID,

ClassID = tbStudent.ClassID

}).ToList();

//条件筛选

if (AcademeID > 0){

listResult = listResult.Where(m => m.AcademeID == AcademeID).ToList();

}

if (GradeID > 0){

listResult = listResult.Where(m => m.GradeID == GradeID).ToList();

}

if (ClassID > 0){

listResult = listResult.Where(m => m.ClassID == ClassID).ToList();

}

//1、创建工作簿

HSSFWorkbook exBook = new HSSFWorkbook();

//2、创建工作表

ISheet sheet = exBook.CreateSheet("考生成绩");

//3、设计表头

//3.1、创建表头

IRow headRow = sheet.CreateRow(0);

//3.2、设计表头字段

headRow.CreateCell(0).SetCellValue("学号");

headRow.CreateCell(1).SetCellValue("姓名");

headRow.CreateCell(2).SetCellValue("性别");

headRow.CreateCell(3).SetCellValue("班级");

headRow.CreateCell(4).SetCellValue("成绩");

headRow.CreateCell(5).SetCellValue("合格类型");

//4、写入表格数据

for (int i = 0; i < listResult.Count(); i++){

//4.1、创建行

IRow rowTemp = sheet.CreateRow(i + 1);

//4.2、写入数据

rowTemp.CreateCell(0).SetCellValue(listResult[i].StudentNumber);

rowTemp.CreateCell(1).SetCellValue(listResult[i].StudentName);

rowTemp.CreateCell(2).SetCellValue(listResult[i].StudentSex);

rowTemp.CreateCell(3).SetCellValue(listResult[i].Class);

rowTemp.CreateCell(4).SetCellValue(listResult[i].Achievement.ToString());

rowTemp.CreateCell(5).SetCellValue(listResult[i].EligibleType);

}

//5、将Excel文件转化为文件流输出

MemoryStream exStream = new MemoryStream();

exBook.Write(exStream);

//6、输出之前调用Seek（偏移量，游标位置）方法：确定流开始的位置

exStream.Seek(0, SeekOrigin.Begin);

//7、为下载的Excel文件命名

string exFileName = "学生安全教育测试成绩" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xls";

//返回

return File(exStream, "application/vnd.ms-excel", exFileName);

}

我们看到在查询和筛选出数据之后就创建表和设计表头，然后就通过for循环一条一条的把数据录入进去，完成之后就将Excel文件转化为文件流输出，还要给它设置流开始的位置，最后为下载的Excel文件命名

导出数据到Excel表格

相关推荐