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.deptassist.ReportPrintAction.java
/** * 1-10?/* ww w . j a v a2 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 */ private void printRc110(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); MemberManager mm = (MemberManager) getBean(MEMBER_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); Employee employee = (Employee) getUserCredential(session).getMember(); List<Empl> empls = mm.findTeacherByUnit(employee.getUnit()); String calendarYear = form.getString("calendarYear"); Rcconf rcconf = null; List<Rcconf> rcconfs = null; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("1-10"); sheet.setColumnWidth(0, 2000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 12000); sheet.setColumnWidth(3, 12000); sheet.setColumnWidth(4, 3000); 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(employee.getUnit()) + "1-10", 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; for (Empl e : empls) { rcconf = new Rcconf(); rcconf.setIdno(e.getIdno().toUpperCase()); rcconf.setSchoolYear(Short.valueOf(calendarYear)); rcconfs = mm.findRcconfsBy(rcconf); for (Rcconf rc : rcconfs) { Toolket.setCellValue(workbook, sheet, index, 0, rc.getSchoolYear().toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, e.getCname().trim(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, rc.getTitle().trim(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index, 3, rc.getJname().trim(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index++, 4, rc.getPyear().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, "printRc110.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.deptassist.ReportPrintAction.java
/** * 1-11()?//from www . 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 printRc111(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); MemberManager mm = (MemberManager) getBean(MEMBER_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); Employee employee = (Employee) getUserCredential(session).getMember(); List<Empl> empls = mm.findTeacherByUnit(employee.getUnit()); String calendarYear = form.getString("calendarYear"); Rcbook rcbook = null; List<Rcbook> rcbooks = null; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("1-11"); sheet.setColumnWidth(0, 2000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 12000); sheet.setColumnWidth(3, 8000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); 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(employee.getUnit()) + "1-11()", 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); int index = 2; for (Empl e : empls) { rcbook = new Rcbook(); rcbook.setIdno(e.getIdno().toUpperCase()); rcbook.setSchoolYear(Short.valueOf(calendarYear)); rcbooks = mm.findRcbooksBy(rcbook); for (Rcbook rc : rcbooks) { Toolket.setCellValue(workbook, sheet, index, 0, rc.getSchoolYear().toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, e.getCname().trim(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, rc.getTitle().trim(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index++, 3, rc.getPublisher().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, "printRc111.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.deptassist.ReportPrintAction.java
/** * 1-12?//w w w . j ava2 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 printRc112(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); MemberManager mm = (MemberManager) getBean(MEMBER_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); Employee employee = (Employee) getUserCredential(session).getMember(); List<Empl> empls = mm.findTeacherByUnit(employee.getUnit()); String calendarYear = form.getString("calendarYear"); Rcpet rcpet = null; List<Rcpet> rcpets = null; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("1-12"); sheet.setColumnWidth(0, 2000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 12000); sheet.setColumnWidth(3, 3000); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 6000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); 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(employee.getUnit()) + "1-12", 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); int index = 2; for (Empl e : empls) { rcpet = new Rcpet(); rcpet.setIdno(e.getIdno().toUpperCase()); rcpet.setSchoolYear(Short.valueOf(calendarYear)); rcpets = mm.findRcpetsBy(rcpet); for (Rcpet rc : rcpets) { Toolket.setCellValue(workbook, sheet, index, 0, rc.getSchoolYear().toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, e.getCname().trim(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, rc.getTitle().trim(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index, 3, rc.getBdate().trim(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, rc.getEdate().trim(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 5, rc.getInst().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, "printRc112.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.deptassist.ReportPrintAction.java
/** * 1-13???/* www . ja va 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 printRc113(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); MemberManager mm = (MemberManager) getBean(MEMBER_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); Employee employee = (Employee) getUserCredential(session).getMember(); List<Empl> empls = mm.findTeacherByUnit(employee.getUnit()); String calendarYear = form.getString("calendarYear"); Rchono rchono = null; List<Rchono> rchonos = null; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("1-13??"); sheet.setColumnWidth(0, 2000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 12000); sheet.setColumnWidth(3, 5000); sheet.setColumnWidth(4, 10000); 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(employee.getUnit()) + "1-13??", 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; for (Empl e : empls) { rchono = new Rchono(); rchono.setIdno(e.getIdno().toUpperCase()); rchono.setSchoolYear(Short.valueOf(calendarYear)); rchonos = mm.findRchonosBy(rchono); for (Rchono rc : rchonos) { Toolket.setCellValue(workbook, sheet, index, 0, rc.getSchoolYear().toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, e.getCname().trim(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, rc.getTitle().trim(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index, 3, rc.getNation().trim(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 4, rc.getInst().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, "printRc113.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.deptassist.ReportPrintAction.java
/** * //from ww w . j a v a 2 s .c o m * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printDeptStdSkillList1(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(); CodeEmpl codeEmpl = new CodeEmpl(); codeEmpl.setIdno(empl.getUnit()); Example example4CodeEmpl = Example.create(codeEmpl).ignoreCase().enableLike(MatchMode.START); List<CodeEmpl> codeEmpls = (List<CodeEmpl>) am.findSQLWithCriteria(CodeEmpl.class, example4CodeEmpl, null, null); 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 FROM StdSkill s WHERE s.deptNo = ? " + "AND s.licenseValidDate BETWEEN ? AND ? " + "GROUP BY s.licenseCode"; List<Object> ret = (List<Object>) am.find(hql, new Object[] { //codeEmpls.get(0).getIdno2().trim(), cal1.getTime(),cal2.getTime() }); empl.getUnit(), cal1.getTime(), cal2.getTime() }); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(""); sheet.setColumnWidth(0, 1500); sheet.setColumnWidth(1, 10000); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 3000); sheet.setColumnWidth(4, 8000); sheet.setColumnWidth(5, 1800); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); 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); int index = 2, totals = 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 += (Long) data[0]; skill = (StdSkill) data[1]; Toolket.setCellValue(workbook, sheet, index, 0, String.valueOf(index - 1), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 5, ((Long) data[0]).toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME); LicenseCode c = (LicenseCode) cm .hqlGetBy("FROM LicenseCode WHERE code='" + skill.getLicenseCode() + "'").get(0); //System.out.println("FROM LicenseCode WHERE code='"+skill.getLicenseCode()+"'"); codes = (List<LicenseCode>) am.findLicenseCodesBy(c); LicenseType type = new LicenseType(); if (!codes.isEmpty()) { code = codes.get(0); Toolket.setCellValue(workbook, sheet, index, 1, code.getName(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index, 2, code.getLevel(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); try { Toolket.setCellValue(workbook, sheet, index, 3, code.getType(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, code.getDeptName(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); } catch (Exception e) { e.printStackTrace(); Toolket.setCellValue(workbook, sheet, index, 3, null, fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, null, fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); } } else { code961s = (List<LicenseCode961>) am .findLicenseCode961sBy(new LicenseCode961(String.valueOf(skill.getLicenseCode()))); if (!code961s.isEmpty()) { code961 = code961s.get(0); Toolket.setCellValue(workbook, sheet, index, 1, code961.getName(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index, 2, code961.getLevel(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, code961.getType().toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, code961.getDeptName(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); } } index++; } sheet.addMergedRegion(new CellRangeAddress(index, index, 0, 4)); Toolket.setCellValue(workbook, sheet, index, 0, "?", fontSize12, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); Toolket.setCellValue(workbook, sheet, index, 5, String.valueOf(totals), 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, "DeptStdSkillList1.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.deptassist.ReportPrintAction.java
/** * // w w w. j av a 2 s . c o m * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void 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(); CodeEmpl codeEmpl = new CodeEmpl(); codeEmpl.setIdno(empl.getUnit()); Example example4CodeEmpl = Example.create(codeEmpl).ignoreCase().enableLike(MatchMode.START); List<CodeEmpl> codeEmpls = (List<CodeEmpl>) am.findSQLWithCriteria(CodeEmpl.class, example4CodeEmpl, null, null); 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 >= ? AND ss.licenseValidDate <= ? " + "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[] { //codeEmpls.get(0).getIdno().trim(), cal1.getTime(),cal2.getTime() }); empl.getUnit(), 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(String.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(String.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.deptassist.ReportPrintAction.java
/** * ???// ww w. j a 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 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(); CodeEmpl codeEmpl = new CodeEmpl(); codeEmpl.setIdno(empl.getUnit()); Example example4CodeEmpl = Example.create(codeEmpl).ignoreCase().enableLike(MatchMode.START); List<CodeEmpl> codeEmpls = (List<CodeEmpl>) am.findSQLWithCriteria(CodeEmpl.class, example4CodeEmpl, null, null); 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); } // Y boolean flag = form.getString("amountDateType").equalsIgnoreCase("y"); String hql = ""; if (flag) hql = "SELECT COUNT(*), SUM(s.amount), s FROM StdSkill s " + "WHERE s.amountDate IS NOT NULL AND s.deptNo = ? " + "AND s.amountDate IS NOT NULL AND s.licenseValidDate BETWEEN ? AND ? " + "GROUP BY s.licenseCode"; else // ? hql = "SELECT COUNT(*), SUM(s.amount), s FROM StdSkill s " + "WHERE s.amountDate IS NULL AND s.deptNo = ? " + "AND s.amountDate IS NULL AND s.licenseValidDate BETWEEN ? AND ? " + "GROUP BY s.licenseCode"; List<Object> ret = (List<Object>) am.find(hql, new Object[] { codeEmpls.get(0).getIdno2().trim(), 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()) + ") - " + (flag ? "" : ""), 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(String.valueOf(skill.getLicenseCode()))); if (!codes.isEmpty()) { code = codes.get(0); Toolket.setCellValue(workbook, sheet, index, 1, code.getCode(), 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(String.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.deptassist.ReportPrintAction.java
/** * ?//w w w. 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 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(); CodeEmpl codeEmpl = new CodeEmpl(); codeEmpl.setIdno(empl.getUnit()); Example example4CodeEmpl = Example.create(codeEmpl).ignoreCase().enableLike(MatchMode.START); List<CodeEmpl> codeEmpls = (List<CodeEmpl>) am.findSQLWithCriteria(CodeEmpl.class, example4CodeEmpl, null, null); 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[] { codeEmpls.get(0).getIdno2().trim(), 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); //((Integer) data[0]).toString() ?? "" ?? Toolket.setCellValue(workbook, sheet, index + 2, 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.deptassist.ReportPrintAction.java
/** * ?//from ww w.j av a 2 s.c o m * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printDeptStdSkillList6(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(); CodeEmpl codeEmpl = new CodeEmpl(); codeEmpl.setIdno(empl.getUnit()); Example example4CodeEmpl = Example.create(codeEmpl).ignoreCase().enableLike(MatchMode.START); List<CodeEmpl> codeEmpls = (List<CodeEmpl>) am.findSQLWithCriteria(CodeEmpl.class, example4CodeEmpl, null, null); 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); } DeptCode4Yun yun = new DeptCode4Yun(); yun.setClassNo("___" + codeEmpls.get(0).getIdno2().trim()); Example example = Example.create(yun).ignoreCase().enableLike(MatchMode.START); List<Order> orders = new LinkedList<Order>(); orders.add(Order.asc("classNo")); List<DeptCode4Yun> yuns = (List<DeptCode4Yun>) am.findSQLWithCriteria(DeptCode4Yun.class, example, null, orders); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("?"); sheet.setColumnWidth(0, 2000); sheet.setColumnWidth(1, 6000); sheet.setColumnWidth(2, 4000); sheet.setColumnWidth(3, 3000); sheet.setColumnWidth(4, 6000); 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; String sql = "SELECT s.student_no ST1, gs.student_no ST2 " + "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 ? " + "AND (s.depart_class LIKE ? OR gs.depart_class LIKE ?)"; String classNo = null; List<Map> ret = null; Set<String> set = new HashSet<String>(); for (DeptCode4Yun y : yuns) { classNo = y.getClassNo() + "%"; ret = (List<Map>) am.findBySQL(sql, new Object[] { codeEmpls.get(0).getIdno2().trim(), cal1.getTime(), cal2.getTime(), classNo, classNo }); set = new HashSet<String>(); if (!ret.isEmpty()) { for (Map o : ret) { if (o.get("ST1") != null) set.add((String) o.get("ST1")); else if (o.get("ST2") != null) set.add((String) o.get("ST2")); } if (!set.isEmpty()) { Toolket.setCellValue(workbook, sheet, index, 0, String.valueOf(index - 1), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, y.getCampusName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, y.getDeptName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, String.valueOf(set.size()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 4, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); } } } index++; Toolket.setCellValue(workbook, sheet, index, 4, "", fontSize10, HSSFCellStyle.ALIGN_RIGHT, 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, "DeptStdSkillList6.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 .ja va 2 s. co m * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printDeptStdSkillList1(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 FROM StdSkill s WHERE s.deptNo = ? " + "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, 10000); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 3000); sheet.setColumnWidth(4, 8000); sheet.setColumnWidth(5, 1800); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); 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); int index = 2, totals = 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]; skill = (StdSkill) data[1]; Toolket.setCellValue(workbook, sheet, index, 0, String.valueOf(index - 1), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 5, ((Integer) data[0]).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.getName(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index, 2, code.getLevel(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, code.getType().toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, 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.getName(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index, 2, code961.getLevel(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, code961.getType().toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, code961.getDeptName(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); } } index++; } sheet.addMergedRegion(new CellRangeAddress(index, index, 0, 4)); Toolket.setCellValue(workbook, sheet, index, 0, "?", fontSize12, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); Toolket.setCellValue(workbook, sheet, index, 5, String.valueOf(totals), 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, "DeptStdSkillList1.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); }