jxl导出excel

发布时间:2016-12-8 15:54:32 编辑:www.fx114.net 分享查询网我要评论
本篇文章主要介绍了"jxl导出excel",主要涉及到jxl导出excel方面的内容,对于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;         }     } }

上一篇:HDU2019
下一篇:【python】 字符串转换大小写

相关文章

关键词: jxl导出excel

相关评论