jxl导出excel

package gds.jwcore.score.prez;

import org.apache.struts.action.ActionMapping;
import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.util.List;
import jxl.write.*;
import java.util.HashMap;
import java.util.ArrayList;
import jxl.Workbook;

import gds.jwcore.score.business.ManageScoreService;
import gds.jap.common.AppAction;
import gds.jwcore.score.CourseScoreDTO;
import gds.jap.common.CodeService;
import gds.jap.constants.ScoreKindConstant;
import gds.jap.constants.ChangeClassConstants;

/**
 *
 * <p>Title: 卢沟桥工程JWCORE</p>
 * <p>Description: 查询学员成绩信息</p>
 * <p>Copyright: Copyright (c) 2013</p>
 * <p>Company: 长城数字[www.e-u.cn]</p>
 * @author $Author: jiashan $
 * @version $Revision: 1.1.2.1 $
 */

public class FindStudentScoreInfoAction extends AppAction {
    public ActionForward executeAction(ActionMapping mapping, ActionForm form,
                                       HttpServletRequest request,
                                       HttpServletResponse response) throws
        Exception {
        //1、action绑定的form
        CourseScoreForm courseScoreForm = (CourseScoreForm) form;
        //2、action默认的转向
        ActionForward forward = mapping.getInputForward();

        //3、权限校验--判断当前登录人是否有操作该资源的权限
        if (!gds.jap.jaas.ValidatePermission.isHavePermission("学历教务管理系统",
            "成绩管理", "考务成绩查询", request)) {
            throw new gds.jap.common.AppException("ERROR_PERMISSION_NO",
                                                  new String[] {"考务成绩查询"});
        }

        /********************* 4、action的业务处理代码 **************************/
        //实例化server
        ManageScoreService scoreService = ManageScoreService.getInstance();

        //查询有成绩的学年列表
        List scoreYearList = (List) scoreService.findScoreYear();
        //查询成绩视图的所有学期
        List termList = (List) scoreService.findScoreAllTerm(null);
        //查询有成绩的年级
        List gradeList = (List) scoreService.findScoreGrade();
        //查询成绩视图的开课部系
        List courseDepList = scoreService.findScoreDep();
        //查询成绩视图的开课教研室
        List courseStaffroomList = scoreService.findScoreStaffroom(null);
        //成绩性质下拉菜单
        List examScoreKindList = ScoreKindConstant.getExamScoreKindList();
        //查询学员单位数据
        List studentDepList = scoreService.findStudentDep();
        //查询学籍异动性质，只查询留级和降级码
        List studentChangeList = ChangeClassConstants.getStayDropList();

        //查询标志
        String flag = request.getParameter("flag");
        List studentScoreList = null;
        //页面提示信息
        String resultInfo = "";
        if ("find".equals(flag)) {
            HashMap hashMap = new HashMap();
            hashMap.put("SCHOOLYEAR", request.getParameter("schoolYear"));
            hashMap.put("TERMID", request.getParameter("termID"));
            hashMap.put("STAFFROOMID", request.getParameter("staffRoomID"));
            hashMap.put("COURSEDEPID", request.getParameter("courseDepID"));
            hashMap.put("COURSENAME", request.getParameter("courseName"));
            hashMap.put("COURSETYPE", request.getParameter("courseType"));
            hashMap.put("TESTMETHODCODE", request.getParameter("testMethodCode"));
            hashMap.put("EMPLOYEENAME",
                        request.getParameter("employeeName"));
            hashMap.put("SPECTYPECODE", request.getParameter("specTypeCode"));
            hashMap.put("SPECIALITYNAME", request.getParameter("specialityName"));
            hashMap.put("CLASSNAME", request.getParameter("className"));
            hashMap.put("COMPANYID", request.getParameter("companyID"));
            hashMap.put("STUDENTTYPECODE",
                        request.getParameter("studentTypeCode"));
            hashMap.put("GRADE", request.getParameter("grade"));
            hashMap.put("STUDENTNO", request.getParameter("studentNO"));
            hashMap.put("STUDENTNAME", request.getParameter("studentName"));
            hashMap.put("FROMADDRESS", request.getParameter("fromAddress"));
            hashMap.put("SCOREKIND", request.getParameter("scoreKind"));
            hashMap.put("ARMYREGION", request.getParameter("armyRegion"));
            hashMap.put("STUDENTCHANGECODE",
                        request.getParameter("studentChangeCode"));
            //查询学员成绩信息
            studentScoreList = scoreService.findStudentScoreByCon(hashMap);

            /**
             * 判断如果学员成绩信息数量大于65536，则提示"查询到的记录数大于65536，系统无法进行显示，请您优化查询条件后再进行查询"。
             * 如果查询到大于2000且小于等于65536条记录，只显示2000条记录，如果查看所有记录，请进行下载。
             * 在查询记录大于等于2000条时显示提示信息，如果小于2000条记录直接使用displayTable导出excel。
             */

            if (studentScoreList.size() >= 2000
                && studentScoreList.size() <= 65536) {
                List displayStudentScoreList = new ArrayList();
                //封装2000条数据用于页面显示
                for (int i = 0; i < studentScoreList.size(); i++) {
                    if (i < 2000) {
                        displayStudentScoreList.add(studentScoreList.get(i));
                    }
                }
                request.setAttribute("displayStudentScoreList",
                                     displayStudentScoreList);
                resultInfo =
                    "本次查询出的数据数量大于等于2000且小于等于65536条，只显示2000条记录，如果查看所有记录，请进行";
                //导出学员成绩信息
                this.exportStudentData(studentScoreList, request);
            } else if (studentScoreList.size() > 65536) {
                resultInfo = "由于查询出的数据数量大于65536条，系统无法进行显示，请您优化查询条件后再进行查询。";
            } else {
                request.setAttribute("displayStudentScoreList",
                                     studentScoreList);
            }
            request.setAttribute("resultInfo", resultInfo);
            return mapping.findForward("showCourseScore");
        }

        request.setAttribute("scoreYearList", scoreYearList);
        request.setAttribute("termList", termList);
        request.setAttribute("gradeList", gradeList);
        request.setAttribute("courseDepList", courseDepList);
        request.setAttribute("courseStaffroomList", courseStaffroomList);
        request.setAttribute("examScoreKindList", examScoreKindList);
        request.setAttribute("studentDepList", studentDepList);
        request.setAttribute("studentChangeList", studentChangeList);
        courseScoreForm.setScoreKind("");
        return forward;
    }

    //导出学员成绩数据
    private void exportStudentData(List studentScoreList,
                                   HttpServletRequest request) throws
        Exception {
        try {
            String path = request.getRealPath("/");
            //excel文件名
            String fileName = "courseScore.xls";
            //供用户下载的相对路径
            String relativePath = "/jwext/export/excelFile/" + fileName;
            path = path + relativePath;
            java.io.OutputStream os = new java.io.FileOutputStream(path);
            //excel工作簿
            WritableWorkbook wwb = Workbook.createWorkbook(os);
            //excel标签
            WritableSheet ws = wwb.createSheet(fileName, 0);
            //向excel文件中写数据
            this.writeExcel(studentScoreList, ws);
            wwb.write();
            wwb.close();
            os.close();
            request.setAttribute("relativePath", relativePath);
            request.setAttribute("fileName", fileName);
        } catch (Exception ex) {
            throw ex;
        }
    }

    //写excel文件的工作簿
    private void writeExcel(List studentScoreList,
                            WritableSheet ws
                            ) throws Exception {
        try {
            int row = 0; //行编号
            //课程成绩DTO
            CourseScoreDTO courseScoreDTO = null;
            for (int i = 0; i < studentScoreList.size(); i++) {
                courseScoreDTO = (CourseScoreDTO) studentScoreList.get(i);
                //加表头
                ws.addCell(new Label(0, 0, "学号")); //学号
                ws.addCell(new Label(1, 0, "姓名")); //姓名
                ws.addCell(new Label(2, 0, "学员单位")); //学员单位
                ws.addCell(new Label(3, 0, "专业班次")); //专业班次
                ws.addCell(new Label(4, 0, "生源地/区队")); //生源地、区队
                ws.addCell(new Label(5, 0, "学期")); //学期
                ws.addCell(new Label(6, 0, "课程")); //课程
                ws.addCell(new Label(7, 0, "主讲教员")); //主讲教员
                ws.addCell(new Label(8, 0, "开课部系")); //开课部系
                ws.addCell(new Label(9, 0, "开课教研室")); //开课教研室
                ws.addCell(new Label(10, 0, "平时成绩")); //平时成绩
                ws.addCell(new Label(11, 0, "考试成绩")); //考试成绩
                ws.addCell(new Label(12, 0, "补考成绩")); //补考成绩
                ws.addCell(new Label(13, 0, "毕业前补考成绩")); //毕业前补考成绩
                ws.addCell(new Label(14, 0, "总评成绩")); //总评成绩
                if (courseScoreDTO != null && i > 0) {
                    ws.addCell(new Label(0, i, courseScoreDTO.getStudentNO())); //学号
                    ws.addCell(new Label(1, i, courseScoreDTO.getStudentName())); //姓名
                    ws.addCell(new Label(2, i,
                                         CodeService.getInstance().
                                         getCodeNameByCode(
                        "BBM", courseScoreDTO.getCompanyName()))); //学员单位
                    ws.addCell(new Label(3, i, courseScoreDTO.getClassName())); //专业班次
                    ws.addCell(new Label(4, i,
                                         courseScoreDTO.getProvinceOrArmyregion())); //生源地、区队
                    ws.addCell(new Label(5, i, courseScoreDTO.getTermName())); //学期
                    ws.addCell(new Label(6, i, courseScoreDTO.getCourseName())); //课程
                    ws.addCell(new Label(7, i,
                                         CodeService.getInstance().
                                         getCodeNameByCode(
                        "ZGM", courseScoreDTO.getTaskPrincipalID()))); //主讲教员
                    ws.addCell(new Label(8, i, courseScoreDTO.getCourseDepName())); //开课部系
                    ws.addCell(new Label(9, i,
                                         courseScoreDTO.getCourseStaffRoomName())); //开课教研室
                    ws.addCell(new Label(10, i, courseScoreDTO.getUsualScore())); //平时成绩
                    ws.addCell(new Label(11, i, courseScoreDTO.getExamScore())); //考试成绩
                    ws.addCell(new Label(12, i, courseScoreDTO.getMakeupScore1())); //补考成绩
                    ws.addCell(new Label(13, i, courseScoreDTO.getMakeupScore2())); //毕业前补考成绩
                    if ("无效".equals(courseScoreDTO.getScoreStatus())) {
                        ws.addCell(new Label(14, i,
                                             String.valueOf(courseScoreDTO.
                            getTotalExamScore())+"（重修后"+String.valueOf(courseScoreDTO.
                            getScore100())+"）")); //总评成绩
                    } else {
                        ws.addCell(new Label(14, i,
                                             String.valueOf(courseScoreDTO.
                            getScore100()))); //总评成绩
                    }
                }
            }
        } catch (Exception ex) {
            throw ex;
        }
    }
}