List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet addMergedRegion
@Override public int addMergedRegion(CellRangeAddress region)
From source file:tw.edu.chit.struts.action.registration.ReportPrintAction.java
/** * ?// w w 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 */ private void printRegisterList(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); ScoreManager sm = (ScoreManager) getBean(IConstants.SCORE_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); String year = cm.getNowBy("School_year"); String term = form.getString("sterm"); String header = "?YEARTERM??" .replaceAll("YEAR", year).replaceAll("TERM", term); List<Clazz> clazzes = sm.findClassBy(new Clazz(processClassInfo(form)), getUserCredential(session).getClassInChargeAry(), false); if (!clazzes.isEmpty()) { List<Student> students = null; HSSFSheet sheet = null; HSSFWorkbook workbook = new HSSFWorkbook(); 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"); for (Clazz clazz : clazzes) { if (Toolket.isDelayClass(clazz.getClassNo())) continue; students = mm.findStudentsByClassNo(clazz.getClassNo()); if (!students.isEmpty()) { sheet = workbook.createSheet(clazz.getClassNo()); 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)); sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5)); // Header Toolket.setCellValue(workbook, sheet, 0, 0, header, fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); Toolket.setCellValue(workbook, sheet, 1, 0, "?:" + Toolket.getClassFullName(clazz.getClassNo()) + " ???", fontSize12, HSSFCellStyle.ALIGN_LEFT, false, null, null); // Column Header Toolket.setCellValue(workbook, sheet, 2, 0, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 2, 1, "??", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 2, 2, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 2, 3, "?", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 2, 4, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 2, 5, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 3; for (Student student : students) { Toolket.setCellValue(workbook, sheet, index, 0, student.getStudentNo(), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, student.getStudentName().trim().replaceAll("", ""), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, "", null, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, "", null, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, "", null, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 5, "", null, HSSFCellStyle.ALIGN_CENTER, true, null); if (39 == index) { // ? Toolket.setCellValue(workbook, sheet, index, 0, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, "??", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, "?", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 5, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); } } for (int i = 0; i <= 5; i++) { if (i == 0) { Toolket.setCellValue(workbook, sheet, index, i, "?", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); continue; } Toolket.setCellValue(workbook, sheet, index, i, "", null, HSSFCellStyle.ALIGN_CENTER, true, null); } sheet.addMergedRegion(new CellRangeAddress(++index, index, 0, 5)); // Toolket.setCellValue(workbook, sheet, index, 0, // " ???", // fontSize12, 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, "RegisterList.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } else { Map<String, String> param = new HashMap<String, String>(); File image = new File(context.getRealPath("/pages/images/2002chitS.jpg")); param.put("IMAGE", image.getAbsolutePath()); byte[] bytes = JasperRunManager.runReportToPdf(JasperReportUtils.getNoResultReport(context), param, new JREmptyDataSource()); JasperReportUtils.printPdfToFrontEnd(response, bytes); } }
From source file:tw.edu.chit.struts.action.registration.ReportPrintAction.java
/** * // ww w . ja v a 2 s . c o m * * @param mapping * @param form * @param request * @param response * @param sterm * @throws Exception */ @SuppressWarnings("unchecked") private void printCalculate(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); AdminManager am = (AdminManager) getBean(ADMIN_MANAGER_BEAN_NAME); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME); ScoreManager sm = (ScoreManager) getBean(IConstants.SCORE_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); String thisYear = cm.getNowBy("School_year"); String thisTerm = am.findTermBy(PARAMETER_SCHOOL_TERM); List<Clazz> clazzes = sm.findClassBy(new Clazz(processClassInfo(form)), getUserCredential(session).getClassInChargeAry(), false); int thisTermCounts = 0, lastTermCounts = 0; String departClass = null, deptCode = null, histDeptCode = null, currentDeptCode = null, chiName = null; ScoreHist scoreHist = null; List<Student> students = null; List<ScoreHist> scoreHistList = null; List<Map> seldInfo = null; List csnos = null; if (!clazzes.isEmpty()) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("?"); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 5000); sheet.setColumnWidth(3, 5000); sheet.setColumnWidth(4, 5000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4)); 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); int index = 2; for (Clazz clazz : clazzes) { departClass = clazz.getClassNo(); deptCode = clazz.getDeptNo(); if (Toolket.isDelayClass(departClass) || Toolket.isLiteracyClass(departClass)) continue; students = mm.findStudentsByClassNo(departClass); if (!students.isEmpty()) { if (thisYear.equals(request.getParameter("year")) && thisTerm.equals(sterm)) { // (Seld) for (Student student : students) { seldInfo = cm.findStudentSeldCourse(student.getStudentNo(), sterm); if (!seldInfo.isEmpty()) { for (Map m : seldInfo) { if (IConstants.CSCODE_BEHAVIOR.equals((String) m.get("cscode"))) continue; currentDeptCode = StringUtils.substring((String) m.get("depart_class"), 3, 4); if (!deptCode.equalsIgnoreCase(currentDeptCode) && !Toolket.isLiteracyClass((String) m.get("depart_class"))) { thisTermCounts++; Toolket.setCellValue(workbook, sheet, index, 0, student.getStudentNo(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, student.getStudentName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, Toolket.getClassFullName(student.getDepartClass()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, Toolket.getClassFullName((String) m.get("depart_class")), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 4, (String) m.get("chi_name"), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); // break; } } } } } else { // ?(ScoreHist) for (Student student : students) { scoreHist = new ScoreHist(student.getStudentNo()); scoreHist.setSchoolYear((short) Integer.parseInt(request.getParameter("year"))); scoreHist.setSchoolTerm(sterm); scoreHistList = sm.findScoreHistBy(scoreHist); // HIST: { if (!scoreHistList.isEmpty()) { for (ScoreHist hist : scoreHistList) { if (IConstants.CSCODE_BEHAVIOR.equals(hist.getCscode())) continue; if (StringUtils.isNotBlank(hist.getStdepartClass()) && !Toolket.isLiteracyClass(hist.getStdepartClass())) { histDeptCode = StringUtils.substring(hist.getStdepartClass(), 3, 4); if (!deptCode.equalsIgnoreCase(histDeptCode)) { lastTermCounts++; Toolket.setCellValue(workbook, sheet, index, 0, student.getStudentNo(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, student.getStudentName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, Toolket.getClassFullName(student.getDepartClass()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, Toolket.getClassFullName(hist.getStdepartClass()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); csnos = cm.getCsnameBy(hist.getCscode()); if (!csnos.isEmpty()) chiName = ((Csno) csnos.get(0)).getChiName(); else chiName = ""; Toolket.setCellValue(workbook, sheet, index++, 4, chiName, fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); // break HIST; } } } } // } } } } } 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, "Calculate.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
/** * 4-1/*ww w .j ava2 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
/** * ?/*from w ww . j a v a2 s .co m*/ * * @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 . ja v 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 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.secretary.ReportPrintAction.java
/** * ?//from w w w.j a va2s .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 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 w w . j a va 2s.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
/** * ????//from 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 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(); } }
From source file:tw.edu.chit.struts.action.secretary.ReportPrintAction.java
/** * ????/*from www. j a va2 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 printOpinionDetailList(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); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); DynaActionForm aForm = (DynaActionForm) form; ServletContext context = request.getSession().getServletContext(); String year = aForm.getStrings("year")[0]; String term = aForm.getString("sterm"); String deptCode = request.getParameter("odc"); List<StdOpinionSuggestion> ret = null; if (StringUtils.isNotBlank(deptCode)) { String hql = "FROM StdOpinionSuggestion s " + "WHERE s.schoolYear = ? AND s.schoolTerm = ? " + "AND s.target = ? ORDER BY s.lastModified"; ret = (List<StdOpinionSuggestion>) am.find(hql, new Object[] { year, term, deptCode }); } else { String hql = "FROM StdOpinionSuggestion s " + "WHERE s.schoolYear = ? AND s.schoolTerm = ? " + "ORDER BY s.lastModified"; ret = (List<StdOpinionSuggestion>) am.find(hql, new Object[] { year, term }); } if (!ret.isEmpty()) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("???"); sheet.setColumnWidth(0, 2500); sheet.setColumnWidth(1, 2500); sheet.setColumnWidth(2, 5000); sheet.setColumnWidth(3, 3000); sheet.setColumnWidth(4, 6000); sheet.setColumnWidth(5, 6000); sheet.setColumnWidth(6, 3000); sheet.setColumnWidth(7, 3000); sheet.setColumnWidth(8, 12000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8)); 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, "Email", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 5, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 6, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 7, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 8, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; Student student = null; DateFormat df = new SimpleDateFormat("yyyy/MM/dd hh:mm"); for (StdOpinionSuggestion s : ret) { 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(s.getTarget()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); student = mm.findStudentByOid(s.getStudentOid()); Toolket.setCellValue(workbook, sheet, index, 3, student == null ? "" : student.getStudentName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, s.getEmail(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index, 5, s.getTopic(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index, 6, s.getPlace(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 7, df.format(s.getLastModified()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 8, s.getContent(), 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, "OpinionDetailList.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } }
From source file:ua.com.ecotep.unianalysis.export.XLSDataExport.java
@Override public void exportData(String selectedFile, AnProperties props, ObservableList<ObservableList<Object>> exportData, List<String> columnTitles) throws Exception { if (selectedFile == null) { return;/*from w ww . j av a 2 s.c o m*/ } System.setProperty("java.awt.headless", "true"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Sheet1"); HSSFPrintSetup ps = sheet.getPrintSetup(); ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); ps.setLandscape(true); HSSFCellStyle cellStyleT = wb.createCellStyle(); HSSFFont font1 = wb.createFont(); font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font1.setFontHeightInPoints((short) 8); cellStyleT.setFont(font1); int rnumber = 0; HSSFRow row = sheet.createRow(0); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10)); HSSFCell cell = row.createCell(0); cell.setCellValue( new HSSFRichTextString(" ??? ??? - " + DateConverters.getDateToStr(LocalDate.now()))); cell.setCellStyle(cellStyleT); rnumber++; if (props != null) { String val = " : " + DateConverters.getDateToStr(props.getDateFrom()) + " " + DateConverters.getDateToStr(props.getDateTo()); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; //---------- if (props.isSalMode()) { String type = props.getSalSalerType() == AnProperties.SALER_TYPES.PROFILE ? " " : " "; val = "? " + type + "| " + props.getSaler().getNameSaler(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getSalSalerType() == AnProperties.SALER_TYPES.SALER) { type = props.isSalDirectSales() ? "? " : " "; val = " : " + type; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.isSalFixedDepartment()) { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } } if (props.isGoodMode()) { if (props.getGoodClassLev0() != null) { val = "? 1: " + props.getGoodClassLev0().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getGoodClassLev1() != null) { val = "? 2: " + props.getGoodClassLev1().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getGoodClassLev2() != null) { val = "? 3: " + props.getGoodClassLev2().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getGoodClassLev3() != null) { val = "? 4: " + props.getGoodClassLev3().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } } if (props.getGoodCustomSearch() != null && !props.getGoodCustomSearch().isEmpty()) { val = " : " + props.getGoodCustomSearch(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } if (props.isGoodPeriodAnalysis() && props.getGoodsIndateLst().size() == 2) { val = " : " + DateConverters.getDateToStr(props.getGoodsIndateLst().get(0)); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; val = " : " + DateConverters.getDateToStr(props.getGoodsIndateLst().get(1)); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } if (!props.isGoodPeriodAnalysis() && props.getGoodsIndateLst().size() > 0) { val = "+: "; for (LocalDate ld : props.getGoodsIndateLst()) { val += DateConverters.getDateToStr(ld) + "; "; } createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } } if (!props.getClLst().isEmpty()) { if (props.isClIncluded()) { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } else { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } val = "+: "; for (ClientBean cb : props.getClLst()) { val += cb.getClientCl() + "; "; } createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } if (!props.getVLst().isEmpty()) { if (props.isVIncluded()) { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } else { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } val = ""; for (String v : props.getVLst()) { val += v + "; "; } createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } //---------- HSSFCellStyle cellStyleH = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyleH.setFont(font); cellStyleH.setWrapText(true); cellStyleH.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleH.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleH.setBorderLeft((short) 1); cellStyleH.setBorderRight((short) 1); cellStyleH.setBorderTop((short) 1); cellStyleH.setBorderBottom((short) 1); HSSFCellStyle cellStyleHh = wb.createCellStyle(); font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyleHh.setFont(font); cellStyleHh.setWrapText(true); cellStyleHh.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleHh.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleHh.setBorderLeft((short) 1); cellStyleHh.setBorderRight((short) 1); cellStyleHh.setBorderTop((short) 1); cellStyleHh.setBorderBottom((short) 1); //filling table HSSFCellStyle cellStyleN = wb.createCellStyle(); cellStyleN.setAlignment(HSSFCellStyle.ALIGN_LEFT); cellStyleN.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleN.setBorderLeft((short) 1); cellStyleN.setBorderRight((short) 1); cellStyleN.setBorderTop((short) 1); cellStyleN.setBorderBottom((short) 1); HSSFCellStyle cellStyleI = wb.createCellStyle(); cellStyleI.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleI.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleI.setBorderLeft((short) 1); cellStyleI.setBorderRight((short) 1); cellStyleI.setBorderTop((short) 1); cellStyleI.setBorderBottom((short) 1); HSSFCellStyle cellStyleD = wb.createCellStyle(); cellStyleD.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleD.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT); HSSFDataFormat df = wb.createDataFormat(); cellStyleD.setDataFormat(df.getFormat("#,##0.0")); cellStyleD.setBorderLeft((short) 1); cellStyleD.setBorderRight((short) 1); cellStyleD.setBorderTop((short) 1); cellStyleD.setBorderBottom((short) 1); HSSFCellStyle cellStyleP = wb.createCellStyle(); cellStyleP.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleP.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleP.setDataFormat(df.getFormat("0.0\\%")); cellStyleP.setBorderLeft((short) 1); cellStyleP.setBorderRight((short) 1); cellStyleP.setBorderTop((short) 1); cellStyleP.setBorderBottom((short) 1); // filling column headers row = sheet.createRow(rnumber); String rowTitle = null; row = sheet.createRow(rnumber); row.setHeightInPoints(40); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString("?")); cell.setCellStyle(cellStyleH); for (int t = 0; t < columnTitles.size(); t++) { cell = row.createCell(t + 1); cell.setCellValue(new HSSFRichTextString(columnTitles.get(t))); cell.setCellStyle(cellStyleH); } // filling table with data rnumber++; for (ObservableList<Object> line : exportData) { row = sheet.createRow(rnumber); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString((String) line.get(0))); cell.setCellStyle(cellStyleN); for (int i = 1; i < line.size(); i++) { Double val = (Double) line.get(i); cell = row.createCell(i); cell.setCellStyle(cellStyleD); cell.setCellValue(val); } rnumber++; } for (int t = 0; t < columnTitles.size(); t++) { sheet.autoSizeColumn((short) t); } saveWorkBook(wb, selectedFile); execute(selectedFile); }