List of usage examples for org.apache.poi.ss.util CellRangeAddress CellRangeAddress
public CellRangeAddress(int firstRow, int lastRow, int firstCol, int lastCol)
From source file:tw.edu.chit.struts.action.language.ReportPrintAction.java
/** * // w ww . ja v a 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 printDeptStdSkillList3(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(MEMBER_MANAGER_BEAN_NAME); Member member = (Member) getUserCredential(session).getMember(); Empl empl = mm.findEmplByOid(member.getOid()); ServletContext context = request.getSession().getServletContext(); Calendar cal = Calendar.getInstance(); Calendar cal1 = (Calendar) cal.clone(); Calendar cal2 = (Calendar) cal.clone(); if (StringUtils.isNotBlank(form.getString("licenseValidDateStart")) || StringUtils.isNotBlank(form.getString("licenseValidDateEnd"))) { Date from = StringUtils.isBlank(form.getString("licenseValidDateStart")) ? null : Toolket.parseNativeDate(form.getString("licenseValidDateStart")); // ??? Date to = StringUtils.isBlank(form.getString("licenseValidDateEnd")) ? Calendar.getInstance().getTime() : Toolket.parseNativeDate(form.getString("licenseValidDateEnd")); cal1.setTime(from); cal1.set(Calendar.HOUR_OF_DAY, 0); cal1.set(Calendar.MINUTE, 0); cal1.set(Calendar.SECOND, 0); cal1.set(Calendar.MILLISECOND, 0); cal2.setTime(to); cal2.set(Calendar.HOUR_OF_DAY, 23); cal2.set(Calendar.MINUTE, 59); cal2.set(Calendar.SECOND, 59); cal2.set(Calendar.MILLISECOND, 999); } String hql = "SELECT COUNT(*) CT, ss.licenseValidDate LVD, ss.licenseCode LC, " + "s.depart_class DC1, s.sex S1, gs.depart_class DC2, gs.sex S2 " + "FROM StdSkill ss LEFT JOIN stmd s ON ss.studentNo = s.student_no " + "LEFT JOIN Gstmd gs ON ss.studentNo = gs.student_no " + "WHERE ss.deptNo = ? AND ss.licenseValidDate BETWEEN ? AND ? " + "GROUP BY ss.licenseValidDate, s.depart_class, s.sex, " + "gs.depart_class, gs.sex " + "ORDER BY ss.licenseValidDate, s.depart_class, " + "s.sex, gs.depart_class, gs.sex"; List<Map> ret = (List<Map>) am.findBySQL(hql, new Object[] { "0", cal1.getTime(), cal2.getTime() }); // DateFormat df = new SimpleDateFormat("yyyy/MM/dd"); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(""); sheet.setColumnWidth(0, 1800); sheet.setColumnWidth(1, 2000); sheet.setColumnWidth(2, 2000); sheet.setColumnWidth(3, 3000); sheet.setColumnWidth(4, 5000); sheet.setColumnWidth(5, 3000); sheet.setColumnWidth(6, 8000); sheet.setColumnWidth(7, 3000); sheet.setColumnWidth(8, 4000); sheet.setColumnWidth(9, 2200); sheet.setColumnWidth(10, 2200); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10)); HSSFFont fontSize12 = workbook.createFont(); fontSize12.setFontHeightInPoints((short) 12); fontSize12.setFontName("Arial Unicode MS"); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, "?" + Toolket.getEmpUnit(empl.getUnit()) + " (" + df.format(cal1.getTime()) + "~" + df.format(cal2.getTime()) + ")", fontSize12, 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); 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); Toolket.setCellValue(workbook, sheet, 1, 9, "()", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 10, "()", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); // Toolket.setCellValue(workbook, sheet, 1, 11, "?", fontSize10, // HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; boolean isGraduate = false; String departClass = null; List<LicenseCode> codes = null; List<LicenseCode961> code961s = null; List<DeptCode4Yun> yuns = null; LicenseCode code = null; LicenseCode961 code961 = null; DeptCode4Yun yun = null; Example example = null; for (Map data : ret) { Toolket.setCellValue(workbook, sheet, index, 0, String.valueOf(index - 1), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); if (data.get("DC1") != null) { departClass = (String) data.get("DC1"); isGraduate = false; } else { departClass = (String) data.get("DC2"); isGraduate = true; } yun = new DeptCode4Yun(); yun.setClassNo(StringUtils.substring(departClass, 0, 4)); example = Example.create(yun).ignoreCase().enableLike(MatchMode.START); yuns = (List<DeptCode4Yun>) am.findSQLWithCriteria(DeptCode4Yun.class, example, null, null); if (!yuns.isEmpty() && yuns.size() == 1) { yun = yuns.get(0); Toolket.setCellValue(workbook, sheet, index, 1, yun.getDeptCode(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, yun.getCampusCode(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, yun.getCampusName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); } Toolket.setCellValue(workbook, sheet, index, 4, Toolket.getClassFullName(departClass), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 5, (String) data.get("LC"), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); codes = (List<LicenseCode>) am .findLicenseCodesBy(new LicenseCode(Integer.valueOf((String) data.get("LC")))); if (!codes.isEmpty()) { code = codes.get(0); Toolket.setCellValue(workbook, sheet, index, 6, code.getName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 7, code.getLevel(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); } else { code961s = (List<LicenseCode961>) am .findLicenseCode961sBy(new LicenseCode961(Integer.valueOf((String) data.get("LC")))); if (!code961s.isEmpty()) { code961 = code961s.get(0); Toolket.setCellValue(workbook, sheet, index, 6, code961.getName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 7, code961.getLevel(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); } } Toolket.setCellValue(workbook, sheet, index, 8, df.format((Date) data.get("LVD")), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); if (!isGraduate) { if ("1".equals((String) data.get("S1"))) { Toolket.setCellValue(workbook, sheet, index, 9, ((Long) data.get("CT")).toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 10, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); } else if ("2".equals((String) data.get("S1"))) { Toolket.setCellValue(workbook, sheet, index, 9, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 10, ((Long) data.get("CT")).toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); } } else { if ("1".equals((String) data.get("S2"))) { Toolket.setCellValue(workbook, sheet, index, 9, ((Long) data.get("CT")).toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 10, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); } else if ("2".equals((String) data.get("S2"))) { Toolket.setCellValue(workbook, sheet, index, 9, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 10, ((Long) data.get("CT")).toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); } } index++; // Toolket.setCellValue(workbook, sheet, index++, 11, ((Long) m // .get("CT")).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, "DeptStdSkillList3.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.language.ReportPrintAction.java
/** * ?//from w w w . j a va 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 printDeptStdSkillList4(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(MEMBER_MANAGER_BEAN_NAME); Member member = (Member) getUserCredential(session).getMember(); Empl empl = mm.findEmplByOid(member.getOid()); ServletContext context = request.getSession().getServletContext(); DateFormat df = new SimpleDateFormat("yyyy/MM/dd"); Calendar cal = Calendar.getInstance(); Calendar cal1 = (Calendar) cal.clone(); Calendar cal2 = (Calendar) cal.clone(); if (StringUtils.isNotBlank(form.getString("licenseValidDateStart")) || StringUtils.isNotBlank(form.getString("licenseValidDateEnd"))) { Date from = StringUtils.isBlank(form.getString("licenseValidDateStart")) ? null : Toolket.parseNativeDate(form.getString("licenseValidDateStart")); // ??? Date to = StringUtils.isBlank(form.getString("licenseValidDateEnd")) ? Calendar.getInstance().getTime() : Toolket.parseNativeDate(form.getString("licenseValidDateEnd")); cal1.setTime(from); cal1.set(Calendar.HOUR_OF_DAY, 0); cal1.set(Calendar.MINUTE, 0); cal1.set(Calendar.SECOND, 0); cal1.set(Calendar.MILLISECOND, 0); cal2.setTime(to); cal2.set(Calendar.HOUR_OF_DAY, 23); cal2.set(Calendar.MINUTE, 59); cal2.set(Calendar.SECOND, 59); cal2.set(Calendar.MILLISECOND, 999); } String hql = "SELECT COUNT(*), SUM(s.amount), s FROM StdSkill s WHERE s.deptNo = ? " + "AND s.amountDate IS NOT NULL AND s.licenseValidDate BETWEEN ? AND ? " + "GROUP BY s.licenseCode"; List<Object> ret = (List<Object>) am.find(hql, new Object[] { "0", cal1.getTime(), cal2.getTime() }); // HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("?"); sheet.setColumnWidth(0, 1500); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 10000); sheet.setColumnWidth(3, 3000); sheet.setColumnWidth(4, 5000); sheet.setColumnWidth(5, 2400); sheet.setColumnWidth(6, 8000); sheet.setColumnWidth(7, 1800); sheet.setColumnWidth(8, 2200); sheet.setColumnWidth(9, 2400); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9)); HSSFFont fontSize12 = workbook.createFont(); fontSize12.setFontHeightInPoints((short) 12); fontSize12.setFontName("Arial Unicode MS"); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, "?" + Toolket.getEmpUnit(empl.getUnit()) + "? (" + df.format(cal1.getTime()) + "~" + df.format(cal2.getTime()) + ")", fontSize12, 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); 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); Toolket.setCellValue(workbook, sheet, 1, 9, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2, totals = 0, sum = 0; StdSkill skill = null; List<LicenseCode> codes = null; List<LicenseCode961> code961s = null; LicenseCode code = null; LicenseCode961 code961 = null; Object[] data = null; for (Object o : ret) { data = (Object[]) o; totals += (Integer) data[0]; sum += (Integer) data[1]; skill = (StdSkill) data[2]; Toolket.setCellValue(workbook, sheet, index, 0, String.valueOf(index - 1), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 7, ((Integer) data[0]).toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 8, skill.getAmount().toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 9, ((Integer) data[1]).toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); codes = (List<LicenseCode>) am .findLicenseCodesBy(new LicenseCode(Integer.valueOf(skill.getLicenseCode()))); if (!codes.isEmpty()) { code = codes.get(0); Toolket.setCellValue(workbook, sheet, index, 1, code.getCode().toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, code.getName(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index, 3, code.getLocale().toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, code.getLevel(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 5, code.getType().toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 6, code.getDeptName(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); } else { code961s = (List<LicenseCode961>) am .findLicenseCode961sBy(new LicenseCode961(Integer.valueOf(skill.getLicenseCode()))); if (!code961s.isEmpty()) { code961 = code961s.get(0); Toolket.setCellValue(workbook, sheet, index, 1, code961.getCode().toString(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index, 2, code961.getName(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index, 3, code961.getLocale().toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, code961.getLevel(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 5, code961.getType().toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 6, code961.getDeptName(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); } } index++; } Toolket.setCellValue(workbook, sheet, index, 6, "?", fontSize12, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); Toolket.setCellValue(workbook, sheet, index, 7, String.valueOf(totals), fontSize12, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); Toolket.setCellValue(workbook, sheet, index, 9, String.valueOf(sum), fontSize12, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, 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, "DeptStdSkillList4.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.language.ReportPrintAction.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 printDeptStdSkillList5(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(MEMBER_MANAGER_BEAN_NAME); Member member = (Member) getUserCredential(session).getMember(); Empl empl = mm.findEmplByOid(member.getOid()); ServletContext context = request.getSession().getServletContext(); DateFormat df = new SimpleDateFormat("yyyy/MM/dd"); Calendar cal = Calendar.getInstance(); Calendar cal1 = (Calendar) cal.clone(); Calendar cal2 = (Calendar) cal.clone(); if (StringUtils.isNotBlank(form.getString("licenseValidDateStart")) || StringUtils.isNotBlank(form.getString("licenseValidDateEnd"))) { Date from = StringUtils.isBlank(form.getString("licenseValidDateStart")) ? null : Toolket.parseNativeDate(form.getString("licenseValidDateStart")); // ??? Date to = StringUtils.isBlank(form.getString("licenseValidDateEnd")) ? Calendar.getInstance().getTime() : Toolket.parseNativeDate(form.getString("licenseValidDateEnd")); cal1.setTime(from); cal1.set(Calendar.HOUR_OF_DAY, 0); cal1.set(Calendar.MINUTE, 0); cal1.set(Calendar.SECOND, 0); cal1.set(Calendar.MILLISECOND, 0); cal2.setTime(to); cal2.set(Calendar.HOUR_OF_DAY, 23); cal2.set(Calendar.MINUTE, 59); cal2.set(Calendar.SECOND, 59); cal2.set(Calendar.MILLISECOND, 999); } String hql = "SELECT COUNT(*), s.techIdno, s.licenseCode FROM StdSkill s " + "WHERE s.deptNo = ? AND s.techIdno IS NOT NULL " + "AND s.licenseValidDate BETWEEN ? AND ? " + "GROUP BY s.techIdno, s.licenseCode"; List<Object> ret = (List<Object>) am.find(hql, new Object[] { "0", cal1.getTime(), cal2.getTime() }); // HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("?"); sheet.setColumnWidth(0, 4000); sheet.setColumnWidth(1, 6000); sheet.setColumnWidth(2, 4000); sheet.setColumnWidth(3, 3000); sheet.setColumnWidth(4, 4000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4)); HSSFFont fontSize12 = workbook.createFont(); fontSize12.setFontHeightInPoints((short) 12); fontSize12.setFontName("Arial Unicode MS"); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, "?" + Toolket.getEmpUnit(empl.getUnit()) + "? (" + df.format(cal1.getTime()) + "~" + df.format(cal2.getTime()) + ")", fontSize12, 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; Object[] data = null; DEmpl dempl = null; for (Object o : ret) { data = (Object[]) o; sheet.addMergedRegion(new CellRangeAddress(index, index + 2, 0, 0)); empl = mm.findEmplByIdno((String) data[1]); if (empl == null) { dempl = mm.findDEmplByIdno((String) data[1]); Toolket.setCellValue(workbook, sheet, index, 0, dempl.getCname(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); } else { Toolket.setCellValue(workbook, sheet, index, 0, empl.getCname(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); } Toolket.setCellValue(workbook, sheet, index + 1, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index + 2, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index + 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index + 2, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, "10", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index + 1, 2, "5", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index + 2, 2, "2", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index + 1, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index + 2, 3, ((Integer) data[0]).toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index + 1, 4, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index + 2, 4, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); index += 3; } index++; sheet.addMergedRegion(new CellRangeAddress(index, index, 0, 1)); Toolket.setCellValue(workbook, sheet, index, 0, " : ?20", 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, "DeptStdSkillList5.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 w w . ja v a 2s . 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 */ 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
/** * //from w w w .j a v a 2 s .c om * * @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//from w w w.java 2s. 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
/** * ?// www .ja va 2s . c o 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 www .j a v a 2s . 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.secretary.ReportPrintAction.java
/** * ?// www .j av a 2s. 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(); }