List of usage examples for org.apache.poi.hssf.usermodel HSSFFont setFontName
public void setFontName(String name)
From source file:tw.edu.chit.struts.action.registration.ReportPrintAction.java
/** * 4-1//from ww w .jav a2 s.c o m * * @param mapping * @param form * @param request * @param response * @param sterm * @throws Exception */ private void printListing41(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); Map<String, Integer> info = Toolket.getPreviousYearTerm(); String lastYear = info.get(IConstants.PARAMETER_SCHOOL_YEAR).toString(); String lastTerm = info.get(IConstants.PARAMETER_SCHOOL_TERM).toString(); List<DeptCode4Yun> deptCodes = mm.findDeptCode4YunBy(new DeptCode4Yun()); if (!deptCodes.isEmpty()) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("4-1"); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 4000); sheet.setColumnWidth(3, 5000); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 3000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, lastYear + "" + lastTerm + "", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 4, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 5, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; Graduate graduate = new Graduate(); graduate.setOccurYear(Short.valueOf(lastYear)); graduate.setOccurTerm(lastTerm); graduate.setOccurStatus("6"); // graduate.setBirthDate(null); graduate.setBirthday2(null); graduate.setDepartClass2(null); graduate.setIdno(null); graduate.setInformixPass(null); graduate.setOccurStatus2(null); graduate.setPassword(null); graduate.setPriority(null); graduate.setSex2(null); graduate.setUndeleteReason(null); graduate.setUnit2(null); for (DeptCode4Yun code : deptCodes) { Toolket.setCellValue(workbook, sheet, index, 0, code.getDeptCode(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, code.getCampusCode(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, code.getDeptName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, code.getCampusName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); graduate.setDepartClass(code.getClassNo()); graduate.setSex("1"); Toolket.setCellValue(workbook, sheet, index, 4, String.valueOf(mm.findGraduatesBy(graduate).size()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); graduate.setSex("2"); Toolket.setCellValue(workbook, sheet, index++, 5, String.valueOf(mm.findGraduatesBy(graduate).size()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "List41Ntnu.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } }
From source file:tw.edu.chit.struts.action.registration.ReportPrintAction.java
/** * ?// w w w.j a va2 s .c om * * @param mapping * @param form * @param request * @param response * @param sterm * @throws Exception */ private void printStmdUnSeld(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); ScoreManager sm = (ScoreManager) getBean(IConstants.SCORE_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); List<Clazz> clazzes = sm.findClassBy(new Clazz(processClassInfo(form)), getUserCredential(session).getClassInChargeAry(), false); String thisYear = cm.getNowBy("School_year"); String thisTerm = am.findTermBy(PARAMETER_SCHOOL_TERM); if (!clazzes.isEmpty()) { List<Student> students = null; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook .createSheet(thisYear + "" + thisTerm + "?"); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 5000); sheet.setColumnWidth(3, 3500); sheet.setColumnWidth(4, 3500); sheet.setColumnWidth(5, 3500); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize12 = workbook.createFont(); fontSize12.setFontHeightInPoints((short) 12); fontSize12.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, "?", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "??", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "?", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 4, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 5, "Email", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; List<Seld> selds = null; for (Clazz clazz : clazzes) { students = mm.findStudentsByClassNo(clazz.getClassNo()); if (!students.isEmpty()) { for (Student student : students) { selds = cm.findSeldByStudentNoAndTerm(student.getStudentNo(), thisTerm); if (selds.isEmpty()) { Toolket.setCellValue(workbook, sheet, index, 0, student.getStudentNo(), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, student.getStudentName(), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, Toolket.getClassFullName(student.getDepartClass()), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, StringUtils.defaultIfEmpty(student.getTelephone(), ""), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, StringUtils.defaultIfEmpty(student.getCellPhone(), ""), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 5, StringUtils.defaultIfEmpty(student.getEmail(), ""), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); } } } } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "StmdUnSeld.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } }
From source file:tw.edu.chit.struts.action.registration.ReportPrintRegisterAction.java
/** * //from w ww .j av a 2 s . com * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printRegisterReportList1(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); HttpSession session = request.getSession(false); ServletContext context = request.getSession().getServletContext(); String year = am.findTermBy(IConstants.PARAMETER_SCHOOL_YEAR); String campusCode = form.getString("campusCode"); String schoolType = form.getString("schoolType"); boolean isAll = "A".equalsIgnoreCase(campusCode); String deptCode = form.getString("deptCodeOpt"); boolean isDept = false; if (!deptCode.trim().equals("")) { isDept = true; } File templateXLS = new File(context.getRealPath("/WEB-INF/reports/RegisterReportList1.xls")); HSSFWorkbook workbook = Toolket.getHSSFWorkbook(templateXLS); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); HSSFSheet sheet = workbook.getSheetAt(0); Toolket.setCellValue(sheet, 0, 0, "?" + year + "" + sterm + "?"); List<TempStmd> tempStmds = null; List<Student> students = null; if ("1".equals(sterm)) { if (isDept) { String classNo = "___" + deptCode + "__"; String hql = "SELECT s FROM Register r, TempStmd s " + "WHERE r.idno = s.idno AND r.type = 'N' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.virClassNo like ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + " AND r.newStudentReg IS NULL ORDER BY r.virClassNo ASC"; tempStmds = (List<TempStmd>) am.find(hql, new Object[] { year, sterm, classNo }); students = tranTempstmd2Student(year, sterm, tempStmds); hql = "SELECT s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.type = 'O' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.realClassNo like ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + " ORDER BY r.realClassNo ASC, r.realStudentNo ASC"; students.addAll((List<Student>) am.find(hql, new Object[] { year, sterm, classNo })); } else { if (!isAll) { String hql = "SELECT s FROM Register r, TempStmd s " + "WHERE r.idno = s.idno AND r.type = 'N' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.campusCode = ? AND r.schoolType = ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + "AND r.newStudentReg IS NULL ORDER BY r.virClassNo ASC, r.virStudentNo ASC"; tempStmds = (List<TempStmd>) am.find(hql, new Object[] { year, sterm, campusCode, schoolType }); students = tranTempstmd2Student(year, sterm, tempStmds); // hql = "SELECT s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.type = 'O' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.campusCode = ? AND r.schoolType = ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + "ORDER BY r.realClassNo ASC, r.realStudentNo ASC"; students.addAll( (List<Student>) am.find(hql, new Object[] { year, sterm, campusCode, schoolType })); /* Oscar written: Maybe used after transfer TempStmd to student // String hql = "SELECT s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.type = 'N' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.campusCode = ? AND r.schoolType = ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + "AND r.isRegist IS NULL ORDER BY r.realClassNo ASC"; students = (List<Student>) am.find(hql, new Object[] { year, sterm, campusCode, schoolType }); // hql = "SELECT s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.type = 'O' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.campusCode = ? AND r.schoolType = ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + "ORDER BY r.realClassNo ASC"; students.addAll((List<Student>) am.find(hql, new Object[] { year, sterm, campusCode, schoolType })); */ } else { // String hql = "SELECT s FROM Register r, TempStmd s " + "WHERE r.idno = s.idno AND r.type = 'N' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + "AND r.newStudentReg IS NULL ORDER BY r.realClassNo ASC"; tempStmds = (List<TempStmd>) am.find(hql, new Object[] { year, sterm }); students = tranTempstmd2Student(year, sterm, tempStmds); // hql = "SELECT s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.type = 'O' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + "ORDER BY r.realClassNo ASC"; students.addAll((List<Student>) am.find(hql, new Object[] { year, sterm })); } } } else if ("2".equals(sterm)) { if (isDept) { String classNo = "___" + deptCode + "__"; String hql = "SELECT s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.type = 'O' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.realClassNo like ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + " ORDER BY r.realClassNo ASC, r.realStudentNo ASC"; students.addAll((List<Student>) am.find(hql, new Object[] { year, sterm, classNo })); } else { if (!isAll) { String hql = "SELECT s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.campusCode = ? AND r.schoolType = ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + "ORDER BY r.realClassNo ASC"; students = (List<Student>) am.find(hql, new Object[] { year, sterm, campusCode, schoolType }); } else { String hql = "SELECT s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + "ORDER BY r.realClassNo ASC"; students = (List<Student>) am.find(hql, new Object[] { year, sterm }); } } } if (students != null && !students.isEmpty()) { int index = 2; Graduate graduate = null; for (Student student : students) { Toolket.setCellValue(sheet, index, 0, String.valueOf(index - 1)); Toolket.setCellValue(sheet, index, 1, Toolket.getClassFullName(student.getDepartClass())); Toolket.setCellValue(sheet, index, 2, student.getStudentNo()); Toolket.setCellValue(sheet, index, 3, student.getStudentName()); Toolket.setCellValue(sheet, index, 4, student.getTelephone()); Toolket.setCellValue(sheet, index, 5, student.getCellPhone()); Toolket.setCellValue(sheet, index, 6, student.getEmail()); Toolket.setCellValue(sheet, index, 7, student.getCurrAddr()); graduate = mm.findGraduateByStudentNo(student.getStudentNo()); Toolket.setCellValue(sheet, index++, 8, graduate == null ? "" : Toolket.getStatus(graduate.getOccurStatus(), true)); } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "RegisterReportList1.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } }
From source file:tw.edu.chit.struts.action.registration.ReportPrintRegisterAction.java
/** * /??/*w ww . j a va 2 s. co m*/ * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printRegisterReportList2(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); HttpSession session = request.getSession(false); ServletContext context = request.getSession().getServletContext(); String year = am.findTermBy(IConstants.PARAMETER_SCHOOL_YEAR); String campusCode = form.getString("campusCode"); String schoolType = form.getString("schoolType"); String reportList = request.getParameter("rl"); boolean isAll = "A".equalsIgnoreCase(campusCode); boolean isPaied = "yes".equalsIgnoreCase(request.getParameter("pt")); String reportFile = ""; String hql = null; List<Object> ret = null; if (!isAll) hql = "SELECT r, s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.campusCode = ? AND r.schoolType = ? "; else hql = "SELECT r, s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.schoolYear = ? AND r.schoolTerm = ? "; if ("1".equals(reportList)) { // if (isPaied) hql += "AND (r.tuitionAmount IS NOT NULL AND r.tuitionAmount != 0) "; else hql += "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) "; reportFile = "RegisterReportList2-1.xls"; } else if ("2".equals(reportList)) { // if (isPaied) hql += "AND (r.agencyAmount IS NOT NULL AND r.agencyAmount != 0) "; else hql += "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) "; reportFile = "RegisterReportList2-2.xls"; } else if ("3".equals(reportList)) { // ? if (isPaied) hql += "AND (r.reliefTuitionAmount IS NOT NULL AND r.reliefTuitionAmount != 0) "; else hql += "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) "; reportFile = "RegisterReportList2-3.xls"; } else if ("4".equals(reportList)) { // if (isPaied) hql += "AND (r.loanAmount IS NOT NULL AND r.loanAmount != 0) "; else hql += "AND (r.loanAmount IS NULL OR r.loanAmount = 0) "; reportFile = "RegisterReportList2-3.xls"; } else if ("5".equals(reportList)) { // if (isPaied) hql += "AND (r.vulnerableAmount IS NOT NULL AND r.vulnerableAmount != 0) "; else hql += "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) "; reportFile = "RegisterReportList2-3.xls"; } hql += "ORDER BY s.departClass, s.studentNo"; if (!isAll) ret = (List<Object>) am.find(hql, new Object[] { year, sterm, campusCode, schoolType }); else ret = (List<Object>) am.find(hql, new Object[] { year, sterm }); File templateXLS = new File(context.getRealPath("/WEB-INF/reports/" + reportFile)); HSSFWorkbook workbook = Toolket.getHSSFWorkbook(templateXLS); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); DateFormat df = new SimpleDateFormat("yyyy/MM/dd"); HSSFSheet sheet = workbook.getSheetAt(0); Toolket.setCellValue(sheet, 0, 0, "?" + year + "" + sterm + "" + (isPaied ? "" : "") + getFeeName(reportList) + ""); if (ret != null && !ret.isEmpty()) { int index = 2; int totals = 0; Register reg = null; Student student = null; Object[] o = null; for (Object data : ret) { o = (Object[]) data; reg = (Register) o[0]; student = (Student) o[1]; Toolket.setCellValue(sheet, index, 0, String.valueOf(index - 1)); Toolket.setCellValue(sheet, index, 1, Toolket.getClassFullName(student.getDepartClass())); Toolket.setCellValue(sheet, index, 2, student.getStudentNo()); Toolket.setCellValue(sheet, index, 3, student.getStudentName()); if ("1".equals(reportList)) { Toolket.setCellValue(sheet, index, 4, reg.getTuitionFee() == null ? "" : reg.getTuitionFee().toString()); if (isPaied) { Toolket.setCellValue(sheet, index, 8, reg.getTuitionAmount() == null ? "" : reg.getTuitionAmount().toString()); Toolket.setCellValue(sheet, index, 9, StringUtils.trimToEmpty(reg.getTuitionAccountNo())); Toolket.setCellValue(sheet, index, 10, reg.getTuitionDate() == null ? "" : df.format(reg.getTuitionDate())); } totals += reg.getTuitionFee(); Toolket.setCellValue(sheet, index, 5, reg.getReliefTuitionAmount() == null ? "" : reg.getReliefTuitionAmount().toString()); Toolket.setCellValue(sheet, index, 6, reg.getLoanAmount() == null ? "" : reg.getLoanAmount().toString()); Toolket.setCellValue(sheet, index, 7, reg.getVulnerableAmount() == null ? "" : reg.getVulnerableAmount().toString()); } else if ("2".equals(reportList)) { Toolket.setCellValue(sheet, index, 4, reg.getAgencyFee() == null ? "" : reg.getAgencyFee().toString()); if (isPaied) { Toolket.setCellValue(sheet, index, 8, reg.getAgencyAmount() == null ? "" : reg.getAgencyAmount().toString()); Toolket.setCellValue(sheet, index, 9, StringUtils.trimToEmpty(reg.getAgencyAccountNo())); Toolket.setCellValue(sheet, index, 10, reg.getAgencyDate() == null ? "" : df.format(reg.getAgencyDate())); } totals += reg.getAgencyFee(); Toolket.setCellValue(sheet, index, 5, reg.getReliefTuitionAmount() == null ? "" : reg.getReliefTuitionAmount().toString()); Toolket.setCellValue(sheet, index, 6, reg.getLoanAmount() == null ? "" : reg.getLoanAmount().toString()); Toolket.setCellValue(sheet, index, 7, reg.getVulnerableAmount() == null ? "" : reg.getVulnerableAmount().toString()); } else { Toolket.setCellValue(sheet, index, 4, reg.getTuitionAmount() == null ? "" : reg.getAgencyAmount().toString()); Toolket.setCellValue(sheet, index, 5, reg.getAgencyAmount() == null ? "" : reg.getAgencyAmount().toString()); Toolket.setCellValue(sheet, index, 6, reg.getReliefTuitionAmount() == null ? "" : reg.getReliefTuitionAmount().toString()); Toolket.setCellValue(sheet, index, 7, reg.getLoanAmount() == null ? "" : reg.getLoanAmount().toString()); Toolket.setCellValue(sheet, index, 8, reg.getVulnerableAmount() == null ? "" : reg.getVulnerableAmount().toString()); } index++; } index++; sheet.addMergedRegion(new CellRangeAddress(index, index, 0, 1)); Toolket.setCellValue(workbook, sheet, index, 0, "??:" + ret.size() + "", fontSize10, HSSFCellStyle.ALIGN_LEFT, false, null); sheet.addMergedRegion(new CellRangeAddress(index, index, 2, 4)); Toolket.setCellValue(workbook, sheet, index, 2, "???:" + totals + "", fontSize10, HSSFCellStyle.ALIGN_LEFT, false, null); } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "RegisterReportList2.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); }
From source file:tw.edu.chit.struts.action.registration.ReportPrintRegisterAction.java
/** * /*from www . ja v a2 s. c o m*/ * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printRegisterReportListSum(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); StudAffairJdbcDAO dao = (StudAffairJdbcDAO) getBean("studAffairJdbcDAO"); HttpSession session = request.getSession(false); ServletContext context = request.getSession().getServletContext(); String year = am.findTermBy(IConstants.PARAMETER_SCHOOL_YEAR); String campusCode = form.getString("campusCode"); String schoolType = form.getString("schoolType"); boolean isAll = "A".equalsIgnoreCase(campusCode); String deptCode = form.getString("deptCodeOpt"); String newReg = request.getParameter("nr"); boolean fillRegCard = true; if (newReg.equals("0")) { fillRegCard = false; } boolean isDept = false; if (!deptCode.trim().equals("")) { isDept = true; } int totalAll = 0; //() int total1D = 0; //?() int total1N = 0; int total1H = 0; int total2A = 0; int noRegAll = 0, noReg1D = 0, noReg1N = 0, noReg1H = 0, noReg2A = 0; int newAll = 0, new1D = 0, new1N = 0, new1H = 0, new2A = 0; int newnoAll = 0, newno1D = 0, newno1N = 0, newno1H = 0, newno2A = 0; String subsql = " AND (TuitionAmount IS NULL OR TuitionAmount = 0) " + "AND (AgencyAmount IS NULL OR AgencyAmount = 0) " + "AND (ReliefTuitionAmount IS NULL OR ReliefTuitionAmount = 0) " + "AND (LoanAmount IS NULL OR LoanAmount = 0) " + "AND (VulnerableAmount IS NULL OR VulnerableAmount = 0) "; String subsql2 = subsql + " AND NewStudentReg IS NULL"; totalAll = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' And Type='O'"); total1D = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='D' And Type='O'"); total1N = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='N' And Type='O'"); total1H = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='H' And Type='O' "); total2A = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='2' And Type='O'"); noRegAll = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' And Type='O'" + subsql); noReg1D = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='D' And Type='O' " + subsql); noReg1N = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='N' And Type='O' " + subsql); noReg1H = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='H' And Type='O' " + subsql); noReg2A = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='2' And Type='O'" + subsql); newAll = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' And Type='N'"); new1D = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='D' And Type='N'"); new1N = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='N' And Type='N'"); new1H = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='H' And Type='N'"); new2A = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='2' And Type='N'"); newnoAll = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' And Type='N'" + (fillRegCard ? subsql2 : subsql)); newno1D = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='D' And Type='N'" + (fillRegCard ? subsql2 : subsql)); newno1N = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='N' And Type='N'" + (fillRegCard ? subsql2 : subsql)); newno1H = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='H' And Type='N'" + (fillRegCard ? subsql2 : subsql)); newno2A = dao .getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='2' And Type='N'" + (fillRegCard ? subsql2 : subsql)); totalAll += newAll; total1D += new1D; total1N += new1N; total1H += new1H; total2A += new2A; noRegAll += newnoAll; noReg1D += newno1D; noReg1N += newno1N; noReg1H += newno1H; noReg2A += newno2A; List<Map> depts = Toolket.getCollegeDepartment(false); int deptAll[] = new int[depts.size() + 1]; int deptNo[] = new int[depts.size() + 1]; int idx = 0, depnewNo = 0, depoldNo = 0; for (Map dept : depts) { idx = depts.indexOf(dept); deptAll[idx] = dao.getRecordsCount( "Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' And VirClassNo like '___" + dept.get("idno").toString() + "__'"); depnewNo = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' And VirClassNo like '___" + dept.get("idno").toString() + "__' And Type='N' " + (fillRegCard ? subsql2 : subsql)); depoldNo = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' And VirClassNo like '___" + dept.get("idno").toString() + "__' And Type='O'" + subsql); deptNo[idx] = depnewNo + depoldNo; } File templateXLS = new File(context.getRealPath("/WEB-INF/reports/RegisterReportListSum.xls")); HSSFWorkbook workbook = Toolket.getHSSFWorkbook(templateXLS); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); HSSFSheet sheet = workbook.getSheetAt(0); Toolket.setCellValue(sheet, 0, 0, "?" + year + "" + sterm + "?"); int index = 2; NumberFormat nf = new DecimalFormat("###0.00%"); Toolket.setCellValueInt(sheet, index, 1, total1D); Toolket.setCellValueInt(sheet, index, 2, (total1D - noReg1D)); Toolket.setCellValueInt(sheet, index, 3, noReg1D); Toolket.setCellValueDbl(sheet, index, 4, (noReg1D / Double.parseDouble("" + total1D))); Toolket.setCellValueInt(sheet, index, 5, new1D); Toolket.setCellValueInt(sheet, index, 6, (new1D - newno1D)); Toolket.setCellValueInt(sheet, index, 7, newno1D); Toolket.setCellValueDbl(sheet, index, 8, (newno1D / Double.parseDouble("" + new1D))); index++; Toolket.setCellValueInt(sheet, index, 1, total1N); Toolket.setCellValueInt(sheet, index, 2, (total1N - noReg1N)); Toolket.setCellValueInt(sheet, index, 3, noReg1N); Toolket.setCellValueDbl(sheet, index, 4, (noReg1N / Double.parseDouble("" + total1N))); Toolket.setCellValueInt(sheet, index, 5, new1N); Toolket.setCellValueInt(sheet, index, 6, (new1N - newno1N)); Toolket.setCellValueInt(sheet, index, 7, newno1N); Toolket.setCellValueDbl(sheet, index, 8, (newno1N / Double.parseDouble("" + new1N))); index++; Toolket.setCellValueInt(sheet, index, 1, total1H); Toolket.setCellValueInt(sheet, index, 2, (total1H - noReg1H)); Toolket.setCellValueInt(sheet, index, 3, noReg1H); Toolket.setCellValueDbl(sheet, index, 4, (noReg1H / Double.parseDouble("" + total1H))); Toolket.setCellValueInt(sheet, index, 5, new1H); Toolket.setCellValueInt(sheet, index, 6, (new1H - newno1H)); Toolket.setCellValueInt(sheet, index, 7, newno1H); Toolket.setCellValueDbl(sheet, index, 8, (newno1H / Double.parseDouble("" + new1H))); index++; Toolket.setCellValueInt(sheet, index, 1, total2A); Toolket.setCellValueInt(sheet, index, 2, (total2A - noReg2A)); Toolket.setCellValueInt(sheet, index, 3, noReg2A); Toolket.setCellValueDbl(sheet, index, 4, (noReg2A / Double.parseDouble("" + total2A))); Toolket.setCellValueInt(sheet, index, 5, new2A); Toolket.setCellValueInt(sheet, index, 6, (new2A - newno2A)); Toolket.setCellValueInt(sheet, index, 7, newno2A); Toolket.setCellValueDbl(sheet, index, 8, (newno2A / Double.parseDouble("" + new2A))); index++; Toolket.setCellValueInt(sheet, index, 1, totalAll); Toolket.setCellValueInt(sheet, index, 2, (totalAll - noRegAll)); Toolket.setCellValueInt(sheet, index, 3, noRegAll); Toolket.setCellValueDbl(sheet, index, 4, (noRegAll / Double.parseDouble("" + totalAll))); Toolket.setCellValueInt(sheet, index, 5, newAll); Toolket.setCellValueInt(sheet, index, 6, (newAll - newnoAll)); Toolket.setCellValueInt(sheet, index, 7, newnoAll); Toolket.setCellValueDbl(sheet, index, 8, (newnoAll / Double.parseDouble("" + newAll))); index++; idx = 0; int sum = depts.size(); for (Map dept : depts) { idx = depts.indexOf(dept); Toolket.setCellValue(sheet, index, 0, dept.get("name").toString()); Toolket.setCellValueInt(sheet, index, 1, deptAll[idx]); Toolket.setCellValueInt(sheet, index, 2, (deptAll[idx] - deptNo[idx])); Toolket.setCellValueInt(sheet, index, 3, deptNo[idx]); if (deptAll[idx] > 0) Toolket.setCellValueDbl(sheet, index, 4, (deptNo[idx] / Double.parseDouble("" + deptAll[idx]))); else Toolket.setCellValueDbl(sheet, index, 4, 0.0d); deptAll[sum] += deptAll[idx]; deptNo[sum] += deptNo[idx]; index++; } Toolket.setCellValue(sheet, index, 0, ""); Toolket.setCellValueInt(sheet, index, 1, deptAll[sum]); Toolket.setCellValueInt(sheet, index, 2, (deptAll[sum] - deptNo[sum])); Toolket.setCellValueInt(sheet, index, 3, deptNo[sum]); Toolket.setCellValueDbl(sheet, index, 4, (deptNo[sum] / Double.parseDouble("" + deptAll[sum]))); File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "RegisterReportListSum.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); }
From source file:tw.edu.chit.struts.action.registration.ReportPrintRegisterAction.java
/** * ??/* ww w. j a v a 2 s . c om*/ * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printRegisterReportNew(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); HttpSession session = request.getSession(false); ServletContext context = request.getSession().getServletContext(); String year = am.findTermBy(IConstants.PARAMETER_SCHOOL_YEAR); String campusCode = form.getString("campusCode"); String schoolType = form.getString("schoolType"); boolean isAll = "A".equalsIgnoreCase(campusCode); String deptCode = form.getString("deptCodeOpt"); boolean isDept = false; if (!deptCode.trim().equals("")) { isDept = true; } File templateXLS = new File(context.getRealPath("/WEB-INF/reports/RegisterReportListNew.xls")); HSSFWorkbook workbook = Toolket.getHSSFWorkbook(templateXLS); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); HSSFSheet sheet = workbook.getSheetAt(0); Toolket.setCellValue(sheet, 0, 0, "?" + year + "" + sterm + " ??"); List<Register> registers = null; List<Student> students = null; if ("1".equals(sterm)) { if (!isAll) { String hql = "SELECT r FROM Register r " + "WHERE r.type = 'N' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.campusCode = ? AND r.schoolType = ? " + "And (tuitionAmount>0 Or agencyAmount>0 Or reliefTuitionAmount>0 Or loanAmount>0 Or vulnerableAmount>0 Or newStudentReg is not null) " + " ORDER BY r.virClassNo ASC, r.virStudentNo ASC"; registers = (List<Register>) am.find(hql, new Object[] { year, sterm, campusCode, schoolType }); } else { // String hql = "SELECT s FROM Register r " + "WHERE r.type = 'N' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "And (tuitionAmount>0 Or agencyAmount>0 Or reliefTuitionAmount>0 Or loanAmount>0 Or vulnerableAmount>0 Or newStudentReg is not null) " + " ORDER BY r.virClassNo ASC, r.virStudentNo ASC"; registers = (List<Register>) am.find(hql, new Object[] { year, sterm }); } } if (registers != null && !registers.isEmpty()) { int index = 2; for (Register student : registers) { Toolket.setCellValue(sheet, index, 0, String.valueOf(index - 1)); if (student.getRealClassNo() != null) Toolket.setCellValue(sheet, index, 1, student.getRealClassNo()); //Toolket.setCellValue(sheet, index, 1, Toolket.getClassFullName(student.getRealClassNo())); if (student.getRealStudentNo() != null) Toolket.setCellValue(sheet, index, 2, student.getRealStudentNo()); Toolket.setCellValue(sheet, index, 3, student.getStudentName()); Toolket.setCellValue(sheet, index, 4, student.getIdno()); Toolket.setCellValue(sheet, index, 5, student.getVirClassNo()); Toolket.setCellValue(sheet, index, 6, student.getVirStudentNo()); if (student.getTuitionAmount() != null) Toolket.setCellValue(sheet, index, 7, student.getTuitionAmount() > 0 ? "" : "" + student.getTuitionAmount()); if (student.getAgencyAmount() != null) Toolket.setCellValue(sheet, index, 8, student.getAgencyAmount() > 0 ? "" : "" + student.getAgencyAmount()); if (student.getReliefTuitionAmount() != null) Toolket.setCellValue(sheet, index, 9, student.getReliefTuitionAmount() > 0 ? "" : "" + student.getReliefTuitionAmount()); if (student.getLoanAmount() != null) Toolket.setCellValue(sheet, index, 10, student.getLoanAmount() > 0 ? "" : "" + student.getLoanAmount()); if (student.getVulnerableAmount() != null) Toolket.setCellValue(sheet, index, 11, student.getVulnerableAmount() > 0 ? "" : "" + student.getVulnerableAmount()); Toolket.setCellValue(sheet, index, 12, student.getNewStudentReg() == null ? "" : ""); index++; } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "RegisterReportListNew.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } }
From source file:tw.edu.chit.struts.action.secretary.ReportPrintAction.java
/** * ?// w w w. ja v a2s .c o m * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printStayTimeList(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); String year = cm.getNowBy("School_year"); String term = form.getString("sterm"); List<Empl> empls = mm.findAllTeacher(term); if (!empls.isEmpty()) { File templateXLS = new File(context.getRealPath("/WEB-INF/reports/TeachSchedAll.xls")); HSSFWorkbook workbook = Toolket.getHSSFWorkbook(templateXLS); HSSFFont fontSize12 = workbook.createFont(); fontSize12.setFontHeightInPoints((short) 12); fontSize12.setFontName(""); int sheetIndex = 0; int colOffset = 1, col = 0; boolean isLocationNull = false; HSSFSheet sheet = null; List<Map> map = null; List<TeacherStayTime> tsts = null; List<LifeCounseling> lcs = null; Map content = null; Short colorForStayTime = HSSFColor.AUTOMATIC.index; Short colorForLifeCounseling = HSSFColor.LIGHT_GREEN.index; for (Empl empl : empls) { if ("1".equalsIgnoreCase(empl.getCategory())) { sheet = workbook.getSheetAt(sheetIndex); workbook.setSheetName(sheetIndex++, empl.getCname()); isLocationNull = empl.getLocation() == null; Toolket.setCellValue(sheet, 0, 1, year + "" + term + "" + empl.getCname() + "?" + " (:" + (isLocationNull ? "" : StringUtils.defaultIfEmpty(empl.getLocation().getExtension(), "")) + " ?:" + (isLocationNull ? "" : StringUtils.defaultIfEmpty(empl.getLocation().getRoomId(), "")) + ")"); map = cm.findCourseByTeacherTermWeekdaySched(empl.getIdno(), term.toString()); for (int i = 0; i < 14; i++) { for (int j = 0; j < 7; j++) { content = map.get(j * 15 + i); if (!CollectionUtils.isEmpty(content)) { Toolket.setCellValue(sheet, i + 2, j + 2, (String) content.get("ClassName") + "\n" + (String) content.get("chi_name") + "\n" + (String) content.get("place")); } } } //tsts = empl.getStayTime(); List<TeacherStayTime> myTsts = cm.ezGetBy( " Select Week, Node1, Node2, Node3, Node4, Node5, Node6, Node7, Node8, Node9, Node10, " + " Node11, Node12, Node13, Node14 " + " From TeacherStayTime " + " Where SchoolYear='" + year + "'" + " And SchoolTerm='" + term + "' " + " And parentOid='" + empl.getOid() + "'"); List myTsts2 = new ArrayList(); for (int i = 0; i < myTsts.size(); i++) { //for (TeacherStayTime tst : tsts) { myTsts2.add(myTsts.get(i)); String s = myTsts2.get(i).toString(); col = Integer.parseInt(s.substring(6, 7)) + colOffset; //Week //col = tst.getWeek() + colOffset; //if (tst.getNode1() != null && tst.getNode1() == 1) { if (Integer.parseInt(s.substring(15, 16)) == 1) { //Node1 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 2, col))) Toolket.setCellValue(workbook, sheet, 2, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode2() != null && tst.getNode2() == 1) { if (Integer.parseInt(s.substring(24, 25)) == 1) { //Node2 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 3, col))) Toolket.setCellValue(workbook, sheet, 3, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode3() != null && tst.getNode3() == 1) { if (Integer.parseInt(s.substring(33, 34)) == 1) { //Node3 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 4, col))) Toolket.setCellValue(workbook, sheet, 4, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode4() != null && tst.getNode4() == 1) { if (Integer.parseInt(s.substring(42, 43)) == 1) { //Node4 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 5, col))) Toolket.setCellValue(workbook, sheet, 5, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode5() != null && tst.getNode5() == 1) { if (Integer.parseInt(s.substring(51, 52)) == 1) { //Node5 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 6, col))) Toolket.setCellValue(workbook, sheet, 6, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode6() != null && tst.getNode6() == 1) { if (Integer.parseInt(s.substring(60, 61)) == 1) { //Node6 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 7, col))) Toolket.setCellValue(workbook, sheet, 7, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode7() != null && tst.getNode7() == 1) { if (Integer.parseInt(s.substring(69, 70)) == 1) { //Node7 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 8, col))) Toolket.setCellValue(workbook, sheet, 8, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode8() != null && tst.getNode8() == 1) { if (Integer.parseInt(s.substring(78, 79)) == 1) { //Node8 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 9, col))) Toolket.setCellValue(workbook, sheet, 9, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode9() != null && tst.getNode9() == 1) { if (Integer.parseInt(s.substring(87, 88)) == 1) { //Node9 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 10, col))) Toolket.setCellValue(workbook, sheet, 10, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode10() != null && tst.getNode10() == 1) { if (Integer.parseInt(s.substring(97, 98)) == 1) { //Node10 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 11, col))) Toolket.setCellValue(workbook, sheet, 11, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode11() != null && tst.getNode11() == 1) { if (Integer.parseInt(s.substring(107, 108)) == 1) { //Node11 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 12, col))) Toolket.setCellValue(workbook, sheet, 12, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode12() != null && tst.getNode12() == 1) { if (Integer.parseInt(s.substring(117, 118)) == 1) { //Node12 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 13, col))) Toolket.setCellValue(workbook, sheet, 13, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode13() != null && tst.getNode13() == 1) { if (Integer.parseInt(s.substring(127, 128)) == 1) { //Node13 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 14, col))) Toolket.setCellValue(workbook, sheet, 14, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode14() != null && tst.getNode14() == 1) { if (Integer.parseInt(s.substring(137, 138)) == 1) { //Node14 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 15, col))) Toolket.setCellValue(workbook, sheet, 15, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } } //lcs = empl.getLifeCounseling(); List<LifeCounseling> myLcs = cm.ezGetBy( " Select Week, Node1, Node2, Node3, Node4, Node5, Node6, Node7, Node8, Node9, Node10, " + " Node11, Node12, Node13, Node14 " + " From LifeCounseling Where ParentOid='" + empl.getOid() + "'"); List myLcs2 = new ArrayList(); colOffset = 1; col = 0; //for (LifeCounseling lc : lcs) { for (int y = 0; y < myLcs.size(); y++) { myLcs2.add(myLcs.get(y)); String st = myLcs2.get(y).toString(); col = Integer.parseInt(st.substring(6, 7)) + colOffset; //col = lc.getWeek() + colOffset; //if (lc.getNode1() != null && lc.getNode1() == 1) { if (Integer.parseInt(st.substring(15, 16)) == 1) { //Node1 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 2, col))) Toolket.setCellValue(workbook, sheet, 2, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode2() != null && lc.getNode2() == 1) { if (Integer.parseInt(st.substring(24, 25)) == 1) { //Node2 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 3, col))) Toolket.setCellValue(workbook, sheet, 3, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode3() != null && lc.getNode3() == 1) { if (Integer.parseInt(st.substring(33, 34)) == 1) { //Node3 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 4, col))) Toolket.setCellValue(workbook, sheet, 4, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode4() != null && lc.getNode4() == 1) { if (Integer.parseInt(st.substring(42, 43)) == 1) { //Node4 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 5, col))) Toolket.setCellValue(workbook, sheet, 5, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode5() != null && lc.getNode5() == 1) { if (Integer.parseInt(st.substring(51, 52)) == 1) { //Node5 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 6, col))) Toolket.setCellValue(workbook, sheet, 6, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode6() != null && lc.getNode6() == 1) { if (Integer.parseInt(st.substring(60, 61)) == 1) { //Node6 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 7, col))) Toolket.setCellValue(workbook, sheet, 7, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode7() != null && lc.getNode7() == 1) { if (Integer.parseInt(st.substring(69, 70)) == 1) { //Node7 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 8, col))) Toolket.setCellValue(workbook, sheet, 8, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode8() != null && lc.getNode8() == 1) { if (Integer.parseInt(st.substring(78, 79)) == 1) { //Node8 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 9, col))) Toolket.setCellValue(workbook, sheet, 9, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode9() != null && lc.getNode9() == 1) { if (Integer.parseInt(st.substring(87, 88)) == 1) { //Node9 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 10, col))) Toolket.setCellValue(workbook, sheet, 10, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode10() != null && lc.getNode10() == 1) { if (Integer.parseInt(st.substring(97, 98)) == 1) { //Node10 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 11, col))) Toolket.setCellValue(workbook, sheet, 11, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode11() != null && lc.getNode11() == 1) { if (Integer.parseInt(st.substring(107, 108)) == 1) { //Node11 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 12, col))) Toolket.setCellValue(workbook, sheet, 12, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode12() != null && lc.getNode12() == 1) { if (Integer.parseInt(st.substring(117, 118)) == 1) { //Node12 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 13, col))) Toolket.setCellValue(workbook, sheet, 13, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode13() != null && lc.getNode13() == 1) { if (Integer.parseInt(st.substring(127, 128)) == 1) { //Node13 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 14, col))) Toolket.setCellValue(workbook, sheet, 14, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode14() != null && lc.getNode14() == 1) { if (Integer.parseInt(st.substring(137, 138)) == 1) { //Node14 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 15, col))) Toolket.setCellValue(workbook, sheet, 15, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } } } } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "StayTimeList.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } }
From source file:tw.edu.chit.struts.action.secretary.ReportPrintAction.java
/** * ?//from www.ja v a 2 s .c o m * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printStdSkill1List(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); String hql = "SELECT COUNT(*), s.deptNo FROM StdSkill s " + "GROUP BY s.deptNo ORDER BY s.deptNo"; List<Object> skills = (List<Object>) am.find(hql, null); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("?"); sheet.setColumnWidth(0, 5000); sheet.setColumnWidth(1, 3000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 1)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, "?", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Object[] obj = null; int index = 2; for (Object o : skills) { obj = (Object[]) o; Toolket.setCellValue(workbook, sheet, index, 0, "0".equals(((String) obj[1])) ? "" : Toolket.getDept((String) obj[1]), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 1, ((Integer) obj[0]).toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "StdSkill1List.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); }
From source file:tw.edu.chit.struts.action.secretary.ReportPrintAction.java
/** * ?//from w ww . j a v a 2 s . c om * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ private void printCalendarList(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); DynaActionForm aForm = (DynaActionForm) form; ServletContext context = request.getSession().getServletContext(); ActionMessages messages = new ActionMessages(); Date from = null, to = null; if (StringUtils.isNotBlank(aForm.getString("licenseValidDateStart")) || StringUtils.isNotBlank(aForm.getString("licenseValidDateEnd"))) { from = StringUtils.isBlank(aForm.getString("licenseValidDateStart")) ? null : Toolket.parseNativeDate(aForm.getString("licenseValidDateStart")); // ??? to = StringUtils.isBlank(aForm.getString("licenseValidDateEnd")) ? Calendar.getInstance().getTime() : Toolket.parseNativeDate(aForm.getString("licenseValidDateEnd")); } Calendar cal = Calendar.getInstance(); cal.setTime(to); cal.add(Calendar.DAY_OF_MONTH, 1); try { IConstants.GOOGLE_SERVICES.setUserCredentials(IConstants.GOOGLE_EMAIL_USERNAME, IConstants.GOOGLE_EMAIL_PASSWORD); } catch (AuthenticationException ae) { log.error(ae.getMessage(), ae); messages.add(ActionMessages.GLOBAL_MESSAGE, new ActionMessage("Course.errorN1", "??,??,,??!")); saveMessages(request, messages); } DateTime rangeFrom = Toolket.parseDateToGoogleDateTime(from); DateTime rangeTo = Toolket.parseDateToGoogleDateTime(cal.getTime()); CalendarEventEntry[] entries = am.findCalendarEventBy(IConstants.GOOGLE_SERVICES, rangeFrom, rangeTo); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(""); sheet.setColumnWidth(0, 5000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 8000); sheet.setColumnWidth(4, 8000); sheet.setColumnWidth(5, 8000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, "", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 4, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 5, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; List<ExtendedProperty> props = null; boolean flag = false; DateFormat dt = new SimpleDateFormat("kk:mm"); DateTime d = null; Calendar c = null; if (entries.length != 0) { for (CalendarEventEntry entry : entries) { props = entry.getExtendedProperty(); flag = false; for (ExtendedProperty prop : props) { if ("host".equalsIgnoreCase(prop.getName())) { Toolket.setCellValue(workbook, sheet, index, 2, StringUtils.trimToEmpty(prop.getValue()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); flag = true; } } if (!flag) Toolket.setCellValue(workbook, sheet, index, 2, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); d = entry.getTimes().get(0).getStartTime(); c = Calendar.getInstance(); c.setTimeInMillis(d.getValue()); Toolket.setCellValue(workbook, sheet, index, 0, Toolket.printNativeDate(c.getTime()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, dt.format(c.getTime()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, entry.getTitle().getPlainText(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index, 4, StringUtils.defaultIfEmpty(entry.getLocations().get(0).getValueString(), ""), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); if (StringUtils.contains(entry.getPlainTextContent(), "?")) Toolket.setCellValue(workbook, sheet, index++, 5, StringUtils.substringAfter(entry.getPlainTextContent().trim(), "\n"), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); else Toolket.setCellValue(workbook, sheet, index++, 5, entry.getPlainTextContent().trim(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); } } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "CalendarList.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); }
From source file:tw.edu.chit.struts.action.secretary.ReportPrintAction.java
/** * ????//w w w. java 2 s . co m * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printOpinionList(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); DynaActionForm aForm = (DynaActionForm) form; ServletContext context = request.getSession().getServletContext(); String year = aForm.getStrings("year")[0]; String term = aForm.getString("sterm"); String hql = "SELECT COUNT(*), s.target FROM StdOpinionSuggestion s " + "WHERE s.schoolYear = ? AND s.schoolTerm = ? GROUP BY s.target"; List<Object> ret = (List<Object>) am.find(hql, new Object[] { year, term }); if (!ret.isEmpty()) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("???"); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 5000); sheet.setColumnWidth(3, 2000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, "???", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; Object[] obj = null; for (Object o : ret) { obj = (Object[]) o; Toolket.setCellValue(workbook, sheet, index, 0, year, fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, term, fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, Toolket.getOpinionDeptName((String) obj[1]), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index++, 3, ((Integer) obj[0]).toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "OpinionList.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } }