Пример #1
0
        public void ProcessRequest(HttpContext context)
        {
            CommonFunction.CheckUser();

            context.Response.CacheControl = "no-cache";

            string courseNo = context.Request["courseNo"];
            string classId = context.Request["classID"].Trim();
            string termtag = context.Request["termtag"].Trim();
            //取得选课信息
            DalOperationAboutCourses dalOperationAboutCourses = new DalOperationAboutCourses();
            DataTable dt = dalOperationAboutCourses.FindStudentInfoFromStudentListAndCorrelation(courseNo, classId, termtag).Tables[0];

            //取得实验提交表信息
            DalOperationAboutExperimentResources doaer = new DalOperationAboutExperimentResources();
            DataTable dtExp = doaer.FindExperimentResourcesByCourseNo(courseNo, classId, termtag).Tables[0];

            //取得对应的CourseNo已布置的全部实验统计信息
            DataTable dtExperiment = doaer.GetExperimentsResourcesByCourseNo(courseNo, classId, termtag).Tables[0];

            //取得作业提交表信息
            DalOperationAboutSchoolWorks doask = new DalOperationAboutSchoolWorks();
            DataTable dtSchwork = doask.FindSchoolWorksByCourseNo(courseNo, classId, termtag).Tables[0];

            //取得对应的CourseNo已布置的全部作业统计信息
            DataTable dtSchNotify = doask.GetSchoolWorksByCourseNo(courseNo, classId, termtag).Tables[0];

            StringBuilder sb = new StringBuilder();

            HSSFWorkbook workbook = new HSSFWorkbook();

            //创建Excel单元格样式
            HSSFCellStyle cellStyle = workbook.CreateCellStyle();
            cellStyle.Alignment = HSSFCellStyle.ALIGN_RIGHT;

            //创建WorkSheet
            HSSFSheet sheet = workbook.CreateSheet("成绩统计表");

            int rowCount = dt.Rows.Count;

            sheet.CreateRow(0).CreateCell(0).SetCellValue("学生编号");
            sheet.CreateRow(0).CreateCell(1).SetCellValue("学生姓名");
            sheet.CreateRow(0).CreateCell(2).SetCellValue("课程编号");

            for (int i = 0; i < dtExperiment.Rows.Count; i++)
            {
                sheet.CreateRow(0).CreateCell(i + 3).SetCellValue(dtExperiment.Rows[i]["experimentResourceTitle"].ToString().Trim());
            }
            for (int j = 0; j < dtSchNotify.Rows.Count; j++)
            {
                sheet.CreateRow(0).CreateCell(j + 3 + dtExperiment.Rows.Count).SetCellValue(dtSchNotify.Rows[j]["schoolWorkNotifyTitle"].ToString().Trim());
            }

            //填充每列的数据
            string studentNo = null;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                sheet.CreateRow(i + 1).CreateCell(0).SetCellValue(dt.Rows[i]["studentNo"].ToString().Trim());
                sheet.CreateRow(i + 1).CreateCell(1).SetCellValue(dt.Rows[i]["studentName"].ToString().Trim());
                sheet.CreateRow(i + 1).CreateCell(2).SetCellValue(dt.Rows[i]["courseNo"].ToString().Trim());

                studentNo = dt.Rows[i]["studentNo"].ToString().Trim();

                //局部变量,指明学生所有的实验数和作业数
                int studentExperimentsCount = 0;
                int studentSchoolWorksCount = 0;

                //添加实验列数据
                for (int j = 0; j < dtExp.Rows.Count; j++)
                {
                    if (studentNo.Equals(dtExp.Rows[j]["studentNo"].ToString().Trim()))
                    {
                        studentExperimentsCount += 1;
                        sheet.CreateRow(i + 1).CreateCell(2 + studentExperimentsCount).SetCellValue(dtExp.Rows[j]["score"].ToString().Trim());
                    }
                }
                //添加作业列数据

                for (int k = 0; k < dtSchwork.Rows.Count; k++)
                {
                    if (studentNo.Equals(dtSchwork.Rows[k]["studentNo"].ToString().Trim()))
                    {
                        studentSchoolWorksCount += 1;
                        sheet.CreateRow(i + 1).CreateCell(2 + studentSchoolWorksCount + studentExperimentsCount).SetCellValue(dtSchwork.Rows[k]["score"].ToString().Trim());
                    }
                }
            }

            IEnumerator rows = sheet.GetRowEnumerator();

            while (rows.MoveNext())
            {
                HSSFRow row = (HSSFRow)rows.Current;

                IEnumerator cols = row.GetCellEnumerator();

                while (cols.MoveNext())
                {
                    HSSFCell cell = (HSSFCell)cols.Current;

                    cell.CellStyle = cellStyle;
                }
            }

            string fileName = "成绩统计" + UploadFiles.DateTimeString();

            if (!Directory.Exists(context.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["ScoreExcelPath"])))
            {
                Directory.CreateDirectory(context.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["ScoreExcelPath"]));
            }

            System.IO.FileStream file = new System.IO.FileStream(HttpContext.Current.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["ScoreExcelPath"] + fileName + ".xls"), System.IO.FileMode.Create);
            workbook.Write(file);
            file.Dispose();

            ////插入值
            FileInfo DownloadFile = new FileInfo(context.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["ScoreExcelPath"] + fileName + ".xls"));

            context.Response.Clear();
            context.Response.ClearHeaders();
            context.Response.Buffer = false;
            Encoding code = Encoding.GetEncoding("gb2312");
            context.Response.ContentEncoding = Encoding.UTF8;
            context.Response.HeaderEncoding = code;//这句很重要
            context.Response.ContentType = "application/octet-stream";
            context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls");
            context.Response.AppendHeader("Content-Length", DownloadFile.Length.ToString());
            context.Response.WriteFile(DownloadFile.FullName);

            if (File.Exists(context.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["ScoreExcelPath"] + fileName + ".xls")))
            {
                File.Delete(context.Server.MapPath(System.Configuration.ConfigurationManager.AppSettings["ScoreExcelPath"] + fileName + ".xls"));
            }
            context.Response.Flush();
        }