List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createFont
@Override
public HSSFFont createFont()
From source file:tw.edu.chit.struts.action.registration.ReportPrintRegisterAction.java
/** * /*w w w .ja va 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 printRegisterReportList1(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); HttpSession session = request.getSession(false); ServletContext context = request.getSession().getServletContext(); String year = am.findTermBy(IConstants.PARAMETER_SCHOOL_YEAR); String campusCode = form.getString("campusCode"); String schoolType = form.getString("schoolType"); boolean isAll = "A".equalsIgnoreCase(campusCode); String deptCode = form.getString("deptCodeOpt"); boolean isDept = false; if (!deptCode.trim().equals("")) { isDept = true; } File templateXLS = new File(context.getRealPath("/WEB-INF/reports/RegisterReportList1.xls")); HSSFWorkbook workbook = Toolket.getHSSFWorkbook(templateXLS); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); HSSFSheet sheet = workbook.getSheetAt(0); Toolket.setCellValue(sheet, 0, 0, "?" + year + "" + sterm + "?"); List<TempStmd> tempStmds = null; List<Student> students = null; if ("1".equals(sterm)) { if (isDept) { String classNo = "___" + deptCode + "__"; String hql = "SELECT s FROM Register r, TempStmd s " + "WHERE r.idno = s.idno AND r.type = 'N' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.virClassNo like ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + " AND r.newStudentReg IS NULL ORDER BY r.virClassNo ASC"; tempStmds = (List<TempStmd>) am.find(hql, new Object[] { year, sterm, classNo }); students = tranTempstmd2Student(year, sterm, tempStmds); hql = "SELECT s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.type = 'O' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.realClassNo like ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + " ORDER BY r.realClassNo ASC, r.realStudentNo ASC"; students.addAll((List<Student>) am.find(hql, new Object[] { year, sterm, classNo })); } else { if (!isAll) { String hql = "SELECT s FROM Register r, TempStmd s " + "WHERE r.idno = s.idno AND r.type = 'N' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.campusCode = ? AND r.schoolType = ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + "AND r.newStudentReg IS NULL ORDER BY r.virClassNo ASC, r.virStudentNo ASC"; tempStmds = (List<TempStmd>) am.find(hql, new Object[] { year, sterm, campusCode, schoolType }); students = tranTempstmd2Student(year, sterm, tempStmds); // hql = "SELECT s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.type = 'O' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.campusCode = ? AND r.schoolType = ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + "ORDER BY r.realClassNo ASC, r.realStudentNo ASC"; students.addAll( (List<Student>) am.find(hql, new Object[] { year, sterm, campusCode, schoolType })); /* Oscar written: Maybe used after transfer TempStmd to student // String hql = "SELECT s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.type = 'N' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.campusCode = ? AND r.schoolType = ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + "AND r.isRegist IS NULL ORDER BY r.realClassNo ASC"; students = (List<Student>) am.find(hql, new Object[] { year, sterm, campusCode, schoolType }); // hql = "SELECT s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.type = 'O' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.campusCode = ? AND r.schoolType = ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + "ORDER BY r.realClassNo ASC"; students.addAll((List<Student>) am.find(hql, new Object[] { year, sterm, campusCode, schoolType })); */ } else { // String hql = "SELECT s FROM Register r, TempStmd s " + "WHERE r.idno = s.idno AND r.type = 'N' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + "AND r.newStudentReg IS NULL ORDER BY r.realClassNo ASC"; tempStmds = (List<TempStmd>) am.find(hql, new Object[] { year, sterm }); students = tranTempstmd2Student(year, sterm, tempStmds); // hql = "SELECT s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.type = 'O' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + "ORDER BY r.realClassNo ASC"; students.addAll((List<Student>) am.find(hql, new Object[] { year, sterm })); } } } else if ("2".equals(sterm)) { if (isDept) { String classNo = "___" + deptCode + "__"; String hql = "SELECT s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.type = 'O' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.realClassNo like ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + " ORDER BY r.realClassNo ASC, r.realStudentNo ASC"; students.addAll((List<Student>) am.find(hql, new Object[] { year, sterm, classNo })); } else { if (!isAll) { String hql = "SELECT s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.campusCode = ? AND r.schoolType = ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + "ORDER BY r.realClassNo ASC"; students = (List<Student>) am.find(hql, new Object[] { year, sterm, campusCode, schoolType }); } else { String hql = "SELECT s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) " + "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) " + "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) " + "AND (r.loanAmount IS NULL OR r.loanAmount = 0) " + "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) " + "ORDER BY r.realClassNo ASC"; students = (List<Student>) am.find(hql, new Object[] { year, sterm }); } } } if (students != null && !students.isEmpty()) { int index = 2; Graduate graduate = null; for (Student student : students) { Toolket.setCellValue(sheet, index, 0, String.valueOf(index - 1)); Toolket.setCellValue(sheet, index, 1, Toolket.getClassFullName(student.getDepartClass())); Toolket.setCellValue(sheet, index, 2, student.getStudentNo()); Toolket.setCellValue(sheet, index, 3, student.getStudentName()); Toolket.setCellValue(sheet, index, 4, student.getTelephone()); Toolket.setCellValue(sheet, index, 5, student.getCellPhone()); Toolket.setCellValue(sheet, index, 6, student.getEmail()); Toolket.setCellValue(sheet, index, 7, student.getCurrAddr()); graduate = mm.findGraduateByStudentNo(student.getStudentNo()); Toolket.setCellValue(sheet, index++, 8, graduate == null ? "" : Toolket.getStatus(graduate.getOccurStatus(), true)); } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "RegisterReportList1.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } }
From source file:tw.edu.chit.struts.action.registration.ReportPrintRegisterAction.java
/** * /??// w ww . j av a 2s.com * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printRegisterReportList2(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); HttpSession session = request.getSession(false); ServletContext context = request.getSession().getServletContext(); String year = am.findTermBy(IConstants.PARAMETER_SCHOOL_YEAR); String campusCode = form.getString("campusCode"); String schoolType = form.getString("schoolType"); String reportList = request.getParameter("rl"); boolean isAll = "A".equalsIgnoreCase(campusCode); boolean isPaied = "yes".equalsIgnoreCase(request.getParameter("pt")); String reportFile = ""; String hql = null; List<Object> ret = null; if (!isAll) hql = "SELECT r, s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.campusCode = ? AND r.schoolType = ? "; else hql = "SELECT r, s FROM Register r, Student s " + "WHERE r.idno = s.idno AND r.schoolYear = ? AND r.schoolTerm = ? "; if ("1".equals(reportList)) { // if (isPaied) hql += "AND (r.tuitionAmount IS NOT NULL AND r.tuitionAmount != 0) "; else hql += "AND (r.tuitionAmount IS NULL OR r.tuitionAmount = 0) "; reportFile = "RegisterReportList2-1.xls"; } else if ("2".equals(reportList)) { // if (isPaied) hql += "AND (r.agencyAmount IS NOT NULL AND r.agencyAmount != 0) "; else hql += "AND (r.agencyAmount IS NULL OR r.agencyAmount = 0) "; reportFile = "RegisterReportList2-2.xls"; } else if ("3".equals(reportList)) { // ? if (isPaied) hql += "AND (r.reliefTuitionAmount IS NOT NULL AND r.reliefTuitionAmount != 0) "; else hql += "AND (r.reliefTuitionAmount IS NULL OR r.reliefTuitionAmount = 0) "; reportFile = "RegisterReportList2-3.xls"; } else if ("4".equals(reportList)) { // if (isPaied) hql += "AND (r.loanAmount IS NOT NULL AND r.loanAmount != 0) "; else hql += "AND (r.loanAmount IS NULL OR r.loanAmount = 0) "; reportFile = "RegisterReportList2-3.xls"; } else if ("5".equals(reportList)) { // if (isPaied) hql += "AND (r.vulnerableAmount IS NOT NULL AND r.vulnerableAmount != 0) "; else hql += "AND (r.vulnerableAmount IS NULL OR r.vulnerableAmount = 0) "; reportFile = "RegisterReportList2-3.xls"; } hql += "ORDER BY s.departClass, s.studentNo"; if (!isAll) ret = (List<Object>) am.find(hql, new Object[] { year, sterm, campusCode, schoolType }); else ret = (List<Object>) am.find(hql, new Object[] { year, sterm }); File templateXLS = new File(context.getRealPath("/WEB-INF/reports/" + reportFile)); HSSFWorkbook workbook = Toolket.getHSSFWorkbook(templateXLS); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); DateFormat df = new SimpleDateFormat("yyyy/MM/dd"); HSSFSheet sheet = workbook.getSheetAt(0); Toolket.setCellValue(sheet, 0, 0, "?" + year + "" + sterm + "" + (isPaied ? "" : "") + getFeeName(reportList) + ""); if (ret != null && !ret.isEmpty()) { int index = 2; int totals = 0; Register reg = null; Student student = null; Object[] o = null; for (Object data : ret) { o = (Object[]) data; reg = (Register) o[0]; student = (Student) o[1]; Toolket.setCellValue(sheet, index, 0, String.valueOf(index - 1)); Toolket.setCellValue(sheet, index, 1, Toolket.getClassFullName(student.getDepartClass())); Toolket.setCellValue(sheet, index, 2, student.getStudentNo()); Toolket.setCellValue(sheet, index, 3, student.getStudentName()); if ("1".equals(reportList)) { Toolket.setCellValue(sheet, index, 4, reg.getTuitionFee() == null ? "" : reg.getTuitionFee().toString()); if (isPaied) { Toolket.setCellValue(sheet, index, 8, reg.getTuitionAmount() == null ? "" : reg.getTuitionAmount().toString()); Toolket.setCellValue(sheet, index, 9, StringUtils.trimToEmpty(reg.getTuitionAccountNo())); Toolket.setCellValue(sheet, index, 10, reg.getTuitionDate() == null ? "" : df.format(reg.getTuitionDate())); } totals += reg.getTuitionFee(); Toolket.setCellValue(sheet, index, 5, reg.getReliefTuitionAmount() == null ? "" : reg.getReliefTuitionAmount().toString()); Toolket.setCellValue(sheet, index, 6, reg.getLoanAmount() == null ? "" : reg.getLoanAmount().toString()); Toolket.setCellValue(sheet, index, 7, reg.getVulnerableAmount() == null ? "" : reg.getVulnerableAmount().toString()); } else if ("2".equals(reportList)) { Toolket.setCellValue(sheet, index, 4, reg.getAgencyFee() == null ? "" : reg.getAgencyFee().toString()); if (isPaied) { Toolket.setCellValue(sheet, index, 8, reg.getAgencyAmount() == null ? "" : reg.getAgencyAmount().toString()); Toolket.setCellValue(sheet, index, 9, StringUtils.trimToEmpty(reg.getAgencyAccountNo())); Toolket.setCellValue(sheet, index, 10, reg.getAgencyDate() == null ? "" : df.format(reg.getAgencyDate())); } totals += reg.getAgencyFee(); Toolket.setCellValue(sheet, index, 5, reg.getReliefTuitionAmount() == null ? "" : reg.getReliefTuitionAmount().toString()); Toolket.setCellValue(sheet, index, 6, reg.getLoanAmount() == null ? "" : reg.getLoanAmount().toString()); Toolket.setCellValue(sheet, index, 7, reg.getVulnerableAmount() == null ? "" : reg.getVulnerableAmount().toString()); } else { Toolket.setCellValue(sheet, index, 4, reg.getTuitionAmount() == null ? "" : reg.getAgencyAmount().toString()); Toolket.setCellValue(sheet, index, 5, reg.getAgencyAmount() == null ? "" : reg.getAgencyAmount().toString()); Toolket.setCellValue(sheet, index, 6, reg.getReliefTuitionAmount() == null ? "" : reg.getReliefTuitionAmount().toString()); Toolket.setCellValue(sheet, index, 7, reg.getLoanAmount() == null ? "" : reg.getLoanAmount().toString()); Toolket.setCellValue(sheet, index, 8, reg.getVulnerableAmount() == null ? "" : reg.getVulnerableAmount().toString()); } index++; } index++; sheet.addMergedRegion(new CellRangeAddress(index, index, 0, 1)); Toolket.setCellValue(workbook, sheet, index, 0, "??:" + ret.size() + "", fontSize10, HSSFCellStyle.ALIGN_LEFT, false, null); sheet.addMergedRegion(new CellRangeAddress(index, index, 2, 4)); Toolket.setCellValue(workbook, sheet, index, 2, "???:" + totals + "", fontSize10, HSSFCellStyle.ALIGN_LEFT, false, null); } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "RegisterReportList2.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); }
From source file:tw.edu.chit.struts.action.registration.ReportPrintRegisterAction.java
/** * //from w w w.j a va 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 printRegisterReportListSum(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); StudAffairJdbcDAO dao = (StudAffairJdbcDAO) getBean("studAffairJdbcDAO"); HttpSession session = request.getSession(false); ServletContext context = request.getSession().getServletContext(); String year = am.findTermBy(IConstants.PARAMETER_SCHOOL_YEAR); String campusCode = form.getString("campusCode"); String schoolType = form.getString("schoolType"); boolean isAll = "A".equalsIgnoreCase(campusCode); String deptCode = form.getString("deptCodeOpt"); String newReg = request.getParameter("nr"); boolean fillRegCard = true; if (newReg.equals("0")) { fillRegCard = false; } boolean isDept = false; if (!deptCode.trim().equals("")) { isDept = true; } int totalAll = 0; //() int total1D = 0; //?() int total1N = 0; int total1H = 0; int total2A = 0; int noRegAll = 0, noReg1D = 0, noReg1N = 0, noReg1H = 0, noReg2A = 0; int newAll = 0, new1D = 0, new1N = 0, new1H = 0, new2A = 0; int newnoAll = 0, newno1D = 0, newno1N = 0, newno1H = 0, newno2A = 0; String subsql = " AND (TuitionAmount IS NULL OR TuitionAmount = 0) " + "AND (AgencyAmount IS NULL OR AgencyAmount = 0) " + "AND (ReliefTuitionAmount IS NULL OR ReliefTuitionAmount = 0) " + "AND (LoanAmount IS NULL OR LoanAmount = 0) " + "AND (VulnerableAmount IS NULL OR VulnerableAmount = 0) "; String subsql2 = subsql + " AND NewStudentReg IS NULL"; totalAll = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' And Type='O'"); total1D = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='D' And Type='O'"); total1N = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='N' And Type='O'"); total1H = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='H' And Type='O' "); total2A = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='2' And Type='O'"); noRegAll = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' And Type='O'" + subsql); noReg1D = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='D' And Type='O' " + subsql); noReg1N = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='N' And Type='O' " + subsql); noReg1H = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='H' And Type='O' " + subsql); noReg2A = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='2' And Type='O'" + subsql); newAll = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' And Type='N'"); new1D = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='D' And Type='N'"); new1N = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='N' And Type='N'"); new1H = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='H' And Type='N'"); new2A = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='2' And Type='N'"); newnoAll = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' And Type='N'" + (fillRegCard ? subsql2 : subsql)); newno1D = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='D' And Type='N'" + (fillRegCard ? subsql2 : subsql)); newno1N = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='N' And Type='N'" + (fillRegCard ? subsql2 : subsql)); newno1H = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='1' AND SchoolType='H' And Type='N'" + (fillRegCard ? subsql2 : subsql)); newno2A = dao .getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' AND CampusCode='2' And Type='N'" + (fillRegCard ? subsql2 : subsql)); totalAll += newAll; total1D += new1D; total1N += new1N; total1H += new1H; total2A += new2A; noRegAll += newnoAll; noReg1D += newno1D; noReg1N += newno1N; noReg1H += newno1H; noReg2A += newno2A; List<Map> depts = Toolket.getCollegeDepartment(false); int deptAll[] = new int[depts.size() + 1]; int deptNo[] = new int[depts.size() + 1]; int idx = 0, depnewNo = 0, depoldNo = 0; for (Map dept : depts) { idx = depts.indexOf(dept); deptAll[idx] = dao.getRecordsCount( "Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' And VirClassNo like '___" + dept.get("idno").toString() + "__'"); depnewNo = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' And VirClassNo like '___" + dept.get("idno").toString() + "__' And Type='N' " + (fillRegCard ? subsql2 : subsql)); depoldNo = dao.getRecordsCount("Select count(*) From Register Where SchoolYear='" + year + "' And SchoolTerm='" + sterm + "' And VirClassNo like '___" + dept.get("idno").toString() + "__' And Type='O'" + subsql); deptNo[idx] = depnewNo + depoldNo; } File templateXLS = new File(context.getRealPath("/WEB-INF/reports/RegisterReportListSum.xls")); HSSFWorkbook workbook = Toolket.getHSSFWorkbook(templateXLS); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); HSSFSheet sheet = workbook.getSheetAt(0); Toolket.setCellValue(sheet, 0, 0, "?" + year + "" + sterm + "?"); int index = 2; NumberFormat nf = new DecimalFormat("###0.00%"); Toolket.setCellValueInt(sheet, index, 1, total1D); Toolket.setCellValueInt(sheet, index, 2, (total1D - noReg1D)); Toolket.setCellValueInt(sheet, index, 3, noReg1D); Toolket.setCellValueDbl(sheet, index, 4, (noReg1D / Double.parseDouble("" + total1D))); Toolket.setCellValueInt(sheet, index, 5, new1D); Toolket.setCellValueInt(sheet, index, 6, (new1D - newno1D)); Toolket.setCellValueInt(sheet, index, 7, newno1D); Toolket.setCellValueDbl(sheet, index, 8, (newno1D / Double.parseDouble("" + new1D))); index++; Toolket.setCellValueInt(sheet, index, 1, total1N); Toolket.setCellValueInt(sheet, index, 2, (total1N - noReg1N)); Toolket.setCellValueInt(sheet, index, 3, noReg1N); Toolket.setCellValueDbl(sheet, index, 4, (noReg1N / Double.parseDouble("" + total1N))); Toolket.setCellValueInt(sheet, index, 5, new1N); Toolket.setCellValueInt(sheet, index, 6, (new1N - newno1N)); Toolket.setCellValueInt(sheet, index, 7, newno1N); Toolket.setCellValueDbl(sheet, index, 8, (newno1N / Double.parseDouble("" + new1N))); index++; Toolket.setCellValueInt(sheet, index, 1, total1H); Toolket.setCellValueInt(sheet, index, 2, (total1H - noReg1H)); Toolket.setCellValueInt(sheet, index, 3, noReg1H); Toolket.setCellValueDbl(sheet, index, 4, (noReg1H / Double.parseDouble("" + total1H))); Toolket.setCellValueInt(sheet, index, 5, new1H); Toolket.setCellValueInt(sheet, index, 6, (new1H - newno1H)); Toolket.setCellValueInt(sheet, index, 7, newno1H); Toolket.setCellValueDbl(sheet, index, 8, (newno1H / Double.parseDouble("" + new1H))); index++; Toolket.setCellValueInt(sheet, index, 1, total2A); Toolket.setCellValueInt(sheet, index, 2, (total2A - noReg2A)); Toolket.setCellValueInt(sheet, index, 3, noReg2A); Toolket.setCellValueDbl(sheet, index, 4, (noReg2A / Double.parseDouble("" + total2A))); Toolket.setCellValueInt(sheet, index, 5, new2A); Toolket.setCellValueInt(sheet, index, 6, (new2A - newno2A)); Toolket.setCellValueInt(sheet, index, 7, newno2A); Toolket.setCellValueDbl(sheet, index, 8, (newno2A / Double.parseDouble("" + new2A))); index++; Toolket.setCellValueInt(sheet, index, 1, totalAll); Toolket.setCellValueInt(sheet, index, 2, (totalAll - noRegAll)); Toolket.setCellValueInt(sheet, index, 3, noRegAll); Toolket.setCellValueDbl(sheet, index, 4, (noRegAll / Double.parseDouble("" + totalAll))); Toolket.setCellValueInt(sheet, index, 5, newAll); Toolket.setCellValueInt(sheet, index, 6, (newAll - newnoAll)); Toolket.setCellValueInt(sheet, index, 7, newnoAll); Toolket.setCellValueDbl(sheet, index, 8, (newnoAll / Double.parseDouble("" + newAll))); index++; idx = 0; int sum = depts.size(); for (Map dept : depts) { idx = depts.indexOf(dept); Toolket.setCellValue(sheet, index, 0, dept.get("name").toString()); Toolket.setCellValueInt(sheet, index, 1, deptAll[idx]); Toolket.setCellValueInt(sheet, index, 2, (deptAll[idx] - deptNo[idx])); Toolket.setCellValueInt(sheet, index, 3, deptNo[idx]); if (deptAll[idx] > 0) Toolket.setCellValueDbl(sheet, index, 4, (deptNo[idx] / Double.parseDouble("" + deptAll[idx]))); else Toolket.setCellValueDbl(sheet, index, 4, 0.0d); deptAll[sum] += deptAll[idx]; deptNo[sum] += deptNo[idx]; index++; } Toolket.setCellValue(sheet, index, 0, ""); Toolket.setCellValueInt(sheet, index, 1, deptAll[sum]); Toolket.setCellValueInt(sheet, index, 2, (deptAll[sum] - deptNo[sum])); Toolket.setCellValueInt(sheet, index, 3, deptNo[sum]); Toolket.setCellValueDbl(sheet, index, 4, (deptNo[sum] / Double.parseDouble("" + deptAll[sum]))); File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "RegisterReportListSum.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); }
From source file:tw.edu.chit.struts.action.registration.ReportPrintRegisterAction.java
/** * ??//from ww w . ja v a 2s. c om * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printRegisterReportNew(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); HttpSession session = request.getSession(false); ServletContext context = request.getSession().getServletContext(); String year = am.findTermBy(IConstants.PARAMETER_SCHOOL_YEAR); String campusCode = form.getString("campusCode"); String schoolType = form.getString("schoolType"); boolean isAll = "A".equalsIgnoreCase(campusCode); String deptCode = form.getString("deptCodeOpt"); boolean isDept = false; if (!deptCode.trim().equals("")) { isDept = true; } File templateXLS = new File(context.getRealPath("/WEB-INF/reports/RegisterReportListNew.xls")); HSSFWorkbook workbook = Toolket.getHSSFWorkbook(templateXLS); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); HSSFSheet sheet = workbook.getSheetAt(0); Toolket.setCellValue(sheet, 0, 0, "?" + year + "" + sterm + " ??"); List<Register> registers = null; List<Student> students = null; if ("1".equals(sterm)) { if (!isAll) { String hql = "SELECT r FROM Register r " + "WHERE r.type = 'N' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "AND r.campusCode = ? AND r.schoolType = ? " + "And (tuitionAmount>0 Or agencyAmount>0 Or reliefTuitionAmount>0 Or loanAmount>0 Or vulnerableAmount>0 Or newStudentReg is not null) " + " ORDER BY r.virClassNo ASC, r.virStudentNo ASC"; registers = (List<Register>) am.find(hql, new Object[] { year, sterm, campusCode, schoolType }); } else { // String hql = "SELECT s FROM Register r " + "WHERE r.type = 'N' " + "AND r.schoolYear = ? AND r.schoolTerm = ? " + "And (tuitionAmount>0 Or agencyAmount>0 Or reliefTuitionAmount>0 Or loanAmount>0 Or vulnerableAmount>0 Or newStudentReg is not null) " + " ORDER BY r.virClassNo ASC, r.virStudentNo ASC"; registers = (List<Register>) am.find(hql, new Object[] { year, sterm }); } } if (registers != null && !registers.isEmpty()) { int index = 2; for (Register student : registers) { Toolket.setCellValue(sheet, index, 0, String.valueOf(index - 1)); if (student.getRealClassNo() != null) Toolket.setCellValue(sheet, index, 1, student.getRealClassNo()); //Toolket.setCellValue(sheet, index, 1, Toolket.getClassFullName(student.getRealClassNo())); if (student.getRealStudentNo() != null) Toolket.setCellValue(sheet, index, 2, student.getRealStudentNo()); Toolket.setCellValue(sheet, index, 3, student.getStudentName()); Toolket.setCellValue(sheet, index, 4, student.getIdno()); Toolket.setCellValue(sheet, index, 5, student.getVirClassNo()); Toolket.setCellValue(sheet, index, 6, student.getVirStudentNo()); if (student.getTuitionAmount() != null) Toolket.setCellValue(sheet, index, 7, student.getTuitionAmount() > 0 ? "" : "" + student.getTuitionAmount()); if (student.getAgencyAmount() != null) Toolket.setCellValue(sheet, index, 8, student.getAgencyAmount() > 0 ? "" : "" + student.getAgencyAmount()); if (student.getReliefTuitionAmount() != null) Toolket.setCellValue(sheet, index, 9, student.getReliefTuitionAmount() > 0 ? "" : "" + student.getReliefTuitionAmount()); if (student.getLoanAmount() != null) Toolket.setCellValue(sheet, index, 10, student.getLoanAmount() > 0 ? "" : "" + student.getLoanAmount()); if (student.getVulnerableAmount() != null) Toolket.setCellValue(sheet, index, 11, student.getVulnerableAmount() > 0 ? "" : "" + student.getVulnerableAmount()); Toolket.setCellValue(sheet, index, 12, student.getNewStudentReg() == null ? "" : ""); index++; } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "RegisterReportListNew.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } }
From source file:tw.edu.chit.struts.action.secretary.ReportPrintAction.java
/** * ?//from w w w . ja va 2s .com * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printStayTimeList(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); String year = cm.getNowBy("School_year"); String term = form.getString("sterm"); List<Empl> empls = mm.findAllTeacher(term); if (!empls.isEmpty()) { File templateXLS = new File(context.getRealPath("/WEB-INF/reports/TeachSchedAll.xls")); HSSFWorkbook workbook = Toolket.getHSSFWorkbook(templateXLS); HSSFFont fontSize12 = workbook.createFont(); fontSize12.setFontHeightInPoints((short) 12); fontSize12.setFontName(""); int sheetIndex = 0; int colOffset = 1, col = 0; boolean isLocationNull = false; HSSFSheet sheet = null; List<Map> map = null; List<TeacherStayTime> tsts = null; List<LifeCounseling> lcs = null; Map content = null; Short colorForStayTime = HSSFColor.AUTOMATIC.index; Short colorForLifeCounseling = HSSFColor.LIGHT_GREEN.index; for (Empl empl : empls) { if ("1".equalsIgnoreCase(empl.getCategory())) { sheet = workbook.getSheetAt(sheetIndex); workbook.setSheetName(sheetIndex++, empl.getCname()); isLocationNull = empl.getLocation() == null; Toolket.setCellValue(sheet, 0, 1, year + "" + term + "" + empl.getCname() + "?" + " (:" + (isLocationNull ? "" : StringUtils.defaultIfEmpty(empl.getLocation().getExtension(), "")) + " ?:" + (isLocationNull ? "" : StringUtils.defaultIfEmpty(empl.getLocation().getRoomId(), "")) + ")"); map = cm.findCourseByTeacherTermWeekdaySched(empl.getIdno(), term.toString()); for (int i = 0; i < 14; i++) { for (int j = 0; j < 7; j++) { content = map.get(j * 15 + i); if (!CollectionUtils.isEmpty(content)) { Toolket.setCellValue(sheet, i + 2, j + 2, (String) content.get("ClassName") + "\n" + (String) content.get("chi_name") + "\n" + (String) content.get("place")); } } } //tsts = empl.getStayTime(); List<TeacherStayTime> myTsts = cm.ezGetBy( " Select Week, Node1, Node2, Node3, Node4, Node5, Node6, Node7, Node8, Node9, Node10, " + " Node11, Node12, Node13, Node14 " + " From TeacherStayTime " + " Where SchoolYear='" + year + "'" + " And SchoolTerm='" + term + "' " + " And parentOid='" + empl.getOid() + "'"); List myTsts2 = new ArrayList(); for (int i = 0; i < myTsts.size(); i++) { //for (TeacherStayTime tst : tsts) { myTsts2.add(myTsts.get(i)); String s = myTsts2.get(i).toString(); col = Integer.parseInt(s.substring(6, 7)) + colOffset; //Week //col = tst.getWeek() + colOffset; //if (tst.getNode1() != null && tst.getNode1() == 1) { if (Integer.parseInt(s.substring(15, 16)) == 1) { //Node1 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 2, col))) Toolket.setCellValue(workbook, sheet, 2, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode2() != null && tst.getNode2() == 1) { if (Integer.parseInt(s.substring(24, 25)) == 1) { //Node2 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 3, col))) Toolket.setCellValue(workbook, sheet, 3, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode3() != null && tst.getNode3() == 1) { if (Integer.parseInt(s.substring(33, 34)) == 1) { //Node3 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 4, col))) Toolket.setCellValue(workbook, sheet, 4, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode4() != null && tst.getNode4() == 1) { if (Integer.parseInt(s.substring(42, 43)) == 1) { //Node4 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 5, col))) Toolket.setCellValue(workbook, sheet, 5, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode5() != null && tst.getNode5() == 1) { if (Integer.parseInt(s.substring(51, 52)) == 1) { //Node5 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 6, col))) Toolket.setCellValue(workbook, sheet, 6, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode6() != null && tst.getNode6() == 1) { if (Integer.parseInt(s.substring(60, 61)) == 1) { //Node6 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 7, col))) Toolket.setCellValue(workbook, sheet, 7, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode7() != null && tst.getNode7() == 1) { if (Integer.parseInt(s.substring(69, 70)) == 1) { //Node7 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 8, col))) Toolket.setCellValue(workbook, sheet, 8, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode8() != null && tst.getNode8() == 1) { if (Integer.parseInt(s.substring(78, 79)) == 1) { //Node8 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 9, col))) Toolket.setCellValue(workbook, sheet, 9, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode9() != null && tst.getNode9() == 1) { if (Integer.parseInt(s.substring(87, 88)) == 1) { //Node9 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 10, col))) Toolket.setCellValue(workbook, sheet, 10, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode10() != null && tst.getNode10() == 1) { if (Integer.parseInt(s.substring(97, 98)) == 1) { //Node10 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 11, col))) Toolket.setCellValue(workbook, sheet, 11, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode11() != null && tst.getNode11() == 1) { if (Integer.parseInt(s.substring(107, 108)) == 1) { //Node11 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 12, col))) Toolket.setCellValue(workbook, sheet, 12, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode12() != null && tst.getNode12() == 1) { if (Integer.parseInt(s.substring(117, 118)) == 1) { //Node12 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 13, col))) Toolket.setCellValue(workbook, sheet, 13, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode13() != null && tst.getNode13() == 1) { if (Integer.parseInt(s.substring(127, 128)) == 1) { //Node13 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 14, col))) Toolket.setCellValue(workbook, sheet, 14, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } //if (tst.getNode14() != null && tst.getNode14() == 1) { if (Integer.parseInt(s.substring(137, 138)) == 1) { //Node14 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 15, col))) Toolket.setCellValue(workbook, sheet, 15, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForStayTime); } } //lcs = empl.getLifeCounseling(); List<LifeCounseling> myLcs = cm.ezGetBy( " Select Week, Node1, Node2, Node3, Node4, Node5, Node6, Node7, Node8, Node9, Node10, " + " Node11, Node12, Node13, Node14 " + " From LifeCounseling Where ParentOid='" + empl.getOid() + "'"); List myLcs2 = new ArrayList(); colOffset = 1; col = 0; //for (LifeCounseling lc : lcs) { for (int y = 0; y < myLcs.size(); y++) { myLcs2.add(myLcs.get(y)); String st = myLcs2.get(y).toString(); col = Integer.parseInt(st.substring(6, 7)) + colOffset; //col = lc.getWeek() + colOffset; //if (lc.getNode1() != null && lc.getNode1() == 1) { if (Integer.parseInt(st.substring(15, 16)) == 1) { //Node1 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 2, col))) Toolket.setCellValue(workbook, sheet, 2, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode2() != null && lc.getNode2() == 1) { if (Integer.parseInt(st.substring(24, 25)) == 1) { //Node2 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 3, col))) Toolket.setCellValue(workbook, sheet, 3, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode3() != null && lc.getNode3() == 1) { if (Integer.parseInt(st.substring(33, 34)) == 1) { //Node3 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 4, col))) Toolket.setCellValue(workbook, sheet, 4, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode4() != null && lc.getNode4() == 1) { if (Integer.parseInt(st.substring(42, 43)) == 1) { //Node4 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 5, col))) Toolket.setCellValue(workbook, sheet, 5, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode5() != null && lc.getNode5() == 1) { if (Integer.parseInt(st.substring(51, 52)) == 1) { //Node5 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 6, col))) Toolket.setCellValue(workbook, sheet, 6, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode6() != null && lc.getNode6() == 1) { if (Integer.parseInt(st.substring(60, 61)) == 1) { //Node6 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 7, col))) Toolket.setCellValue(workbook, sheet, 7, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode7() != null && lc.getNode7() == 1) { if (Integer.parseInt(st.substring(69, 70)) == 1) { //Node7 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 8, col))) Toolket.setCellValue(workbook, sheet, 8, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode8() != null && lc.getNode8() == 1) { if (Integer.parseInt(st.substring(78, 79)) == 1) { //Node8 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 9, col))) Toolket.setCellValue(workbook, sheet, 9, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode9() != null && lc.getNode9() == 1) { if (Integer.parseInt(st.substring(87, 88)) == 1) { //Node9 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 10, col))) Toolket.setCellValue(workbook, sheet, 10, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode10() != null && lc.getNode10() == 1) { if (Integer.parseInt(st.substring(97, 98)) == 1) { //Node10 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 11, col))) Toolket.setCellValue(workbook, sheet, 11, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode11() != null && lc.getNode11() == 1) { if (Integer.parseInt(st.substring(107, 108)) == 1) { //Node11 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 12, col))) Toolket.setCellValue(workbook, sheet, 12, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode12() != null && lc.getNode12() == 1) { if (Integer.parseInt(st.substring(117, 118)) == 1) { //Node12 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 13, col))) Toolket.setCellValue(workbook, sheet, 13, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode13() != null && lc.getNode13() == 1) { if (Integer.parseInt(st.substring(127, 128)) == 1) { //Node13 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 14, col))) Toolket.setCellValue(workbook, sheet, 14, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } //if (lc.getNode14() != null && lc.getNode14() == 1) { if (Integer.parseInt(st.substring(137, 138)) == 1) { //Node14 if (StringUtils.isEmpty(Toolket.getCellValue(sheet, 15, col))) Toolket.setCellValue(workbook, sheet, 15, col, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, colorForLifeCounseling); } } } } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "StayTimeList.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } }
From source file:tw.edu.chit.struts.action.secretary.ReportPrintAction.java
/** * ?/*from w w w. ja v a 2s . c om*/ * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @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
/** * ?// ww w .j a va 2 s. com * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ private void printCalendarList(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); DynaActionForm aForm = (DynaActionForm) form; ServletContext context = request.getSession().getServletContext(); ActionMessages messages = new ActionMessages(); Date from = null, to = null; if (StringUtils.isNotBlank(aForm.getString("licenseValidDateStart")) || StringUtils.isNotBlank(aForm.getString("licenseValidDateEnd"))) { from = StringUtils.isBlank(aForm.getString("licenseValidDateStart")) ? null : Toolket.parseNativeDate(aForm.getString("licenseValidDateStart")); // ??? to = StringUtils.isBlank(aForm.getString("licenseValidDateEnd")) ? Calendar.getInstance().getTime() : Toolket.parseNativeDate(aForm.getString("licenseValidDateEnd")); } Calendar cal = Calendar.getInstance(); cal.setTime(to); cal.add(Calendar.DAY_OF_MONTH, 1); try { IConstants.GOOGLE_SERVICES.setUserCredentials(IConstants.GOOGLE_EMAIL_USERNAME, IConstants.GOOGLE_EMAIL_PASSWORD); } catch (AuthenticationException ae) { log.error(ae.getMessage(), ae); messages.add(ActionMessages.GLOBAL_MESSAGE, new ActionMessage("Course.errorN1", "??,??,,??!")); saveMessages(request, messages); } DateTime rangeFrom = Toolket.parseDateToGoogleDateTime(from); DateTime rangeTo = Toolket.parseDateToGoogleDateTime(cal.getTime()); CalendarEventEntry[] entries = am.findCalendarEventBy(IConstants.GOOGLE_SERVICES, rangeFrom, rangeTo); HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet(""); sheet.setColumnWidth(0, 5000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 3000); sheet.setColumnWidth(3, 8000); sheet.setColumnWidth(4, 8000); sheet.setColumnWidth(5, 8000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, "", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 4, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 5, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; List<ExtendedProperty> props = null; boolean flag = false; DateFormat dt = new SimpleDateFormat("kk:mm"); DateTime d = null; Calendar c = null; if (entries.length != 0) { for (CalendarEventEntry entry : entries) { props = entry.getExtendedProperty(); flag = false; for (ExtendedProperty prop : props) { if ("host".equalsIgnoreCase(prop.getName())) { Toolket.setCellValue(workbook, sheet, index, 2, StringUtils.trimToEmpty(prop.getValue()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); flag = true; } } if (!flag) Toolket.setCellValue(workbook, sheet, index, 2, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); d = entry.getTimes().get(0).getStartTime(); c = Calendar.getInstance(); c.setTimeInMillis(d.getValue()); Toolket.setCellValue(workbook, sheet, index, 0, Toolket.printNativeDate(c.getTime()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, dt.format(c.getTime()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, entry.getTitle().getPlainText(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index, 4, StringUtils.defaultIfEmpty(entry.getLocations().get(0).getValueString(), ""), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); if (StringUtils.contains(entry.getPlainTextContent(), "?")) Toolket.setCellValue(workbook, sheet, index++, 5, StringUtils.substringAfter(entry.getPlainTextContent().trim(), "\n"), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); else Toolket.setCellValue(workbook, sheet, index++, 5, entry.getPlainTextContent().trim(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); } } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "CalendarList.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); }
From source file:tw.edu.chit.struts.action.secretary.ReportPrintAction.java
/** * ????/*from w w w .j a 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 printOpinionList(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); DynaActionForm aForm = (DynaActionForm) form; ServletContext context = request.getSession().getServletContext(); String year = aForm.getStrings("year")[0]; String term = aForm.getString("sterm"); String hql = "SELECT COUNT(*), s.target FROM StdOpinionSuggestion s " + "WHERE s.schoolYear = ? AND s.schoolTerm = ? GROUP BY s.target"; List<Object> ret = (List<Object>) am.find(hql, new Object[] { year, term }); if (!ret.isEmpty()) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("???"); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 5000); sheet.setColumnWidth(3, 2000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, "???", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; Object[] obj = null; for (Object o : ret) { obj = (Object[]) o; Toolket.setCellValue(workbook, sheet, index, 0, year, fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, term, fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, Toolket.getOpinionDeptName((String) obj[1]), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index++, 3, ((Integer) obj[0]).toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "OpinionList.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } }
From source file:tw.edu.chit.struts.action.secretary.ReportPrintAction.java
/** * ????/*w w w . j a v a 2s. com*/ * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void printOpinionDetailList(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); DynaActionForm aForm = (DynaActionForm) form; ServletContext context = request.getSession().getServletContext(); String year = aForm.getStrings("year")[0]; String term = aForm.getString("sterm"); String deptCode = request.getParameter("odc"); List<StdOpinionSuggestion> ret = null; if (StringUtils.isNotBlank(deptCode)) { String hql = "FROM StdOpinionSuggestion s " + "WHERE s.schoolYear = ? AND s.schoolTerm = ? " + "AND s.target = ? ORDER BY s.lastModified"; ret = (List<StdOpinionSuggestion>) am.find(hql, new Object[] { year, term, deptCode }); } else { String hql = "FROM StdOpinionSuggestion s " + "WHERE s.schoolYear = ? AND s.schoolTerm = ? " + "ORDER BY s.lastModified"; ret = (List<StdOpinionSuggestion>) am.find(hql, new Object[] { year, term }); } if (!ret.isEmpty()) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("???"); sheet.setColumnWidth(0, 2500); sheet.setColumnWidth(1, 2500); sheet.setColumnWidth(2, 5000); sheet.setColumnWidth(3, 3000); sheet.setColumnWidth(4, 6000); sheet.setColumnWidth(5, 6000); sheet.setColumnWidth(6, 3000); sheet.setColumnWidth(7, 3000); sheet.setColumnWidth(8, 12000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 8)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, "???", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "???", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 4, "Email", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 5, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 6, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 7, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 8, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; Student student = null; DateFormat df = new SimpleDateFormat("yyyy/MM/dd hh:mm"); for (StdOpinionSuggestion s : ret) { Toolket.setCellValue(workbook, sheet, index, 0, year, fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, term, fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, Toolket.getOpinionDeptName(s.getTarget()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); student = mm.findStudentByOid(s.getStudentOid()); Toolket.setCellValue(workbook, sheet, index, 3, student == null ? "" : student.getStudentName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, s.getEmail(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index, 5, s.getTopic(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index, 6, s.getPlace(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 7, df.format(s.getLastModified()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 8, s.getContent(), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "OpinionDetailList.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } }
From source file:ua.com.ecotep.unianalysis.export.XLSDataExport.java
@Override public void exportData(String selectedFile, AnProperties props, ObservableList<ObservableList<Object>> exportData, List<String> columnTitles) throws Exception { if (selectedFile == null) { return;// ww w . j a v a 2s . c o m } System.setProperty("java.awt.headless", "true"); HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = wb.createSheet("Sheet1"); HSSFPrintSetup ps = sheet.getPrintSetup(); ps.setPaperSize(HSSFPrintSetup.A4_PAPERSIZE); ps.setLandscape(true); HSSFCellStyle cellStyleT = wb.createCellStyle(); HSSFFont font1 = wb.createFont(); font1.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); font1.setFontHeightInPoints((short) 8); cellStyleT.setFont(font1); int rnumber = 0; HSSFRow row = sheet.createRow(0); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10)); HSSFCell cell = row.createCell(0); cell.setCellValue( new HSSFRichTextString(" ??? ??? - " + DateConverters.getDateToStr(LocalDate.now()))); cell.setCellStyle(cellStyleT); rnumber++; if (props != null) { String val = " : " + DateConverters.getDateToStr(props.getDateFrom()) + " " + DateConverters.getDateToStr(props.getDateTo()); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; //---------- if (props.isSalMode()) { String type = props.getSalSalerType() == AnProperties.SALER_TYPES.PROFILE ? " " : " "; val = "? " + type + "| " + props.getSaler().getNameSaler(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getSalSalerType() == AnProperties.SALER_TYPES.SALER) { type = props.isSalDirectSales() ? "? " : " "; val = " : " + type; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.isSalFixedDepartment()) { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } } if (props.isGoodMode()) { if (props.getGoodClassLev0() != null) { val = "? 1: " + props.getGoodClassLev0().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getGoodClassLev1() != null) { val = "? 2: " + props.getGoodClassLev1().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getGoodClassLev2() != null) { val = "? 3: " + props.getGoodClassLev2().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; if (props.getGoodClassLev3() != null) { val = "? 4: " + props.getGoodClassLev3().getName(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } } if (props.getGoodCustomSearch() != null && !props.getGoodCustomSearch().isEmpty()) { val = " : " + props.getGoodCustomSearch(); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } if (props.isGoodPeriodAnalysis() && props.getGoodsIndateLst().size() == 2) { val = " : " + DateConverters.getDateToStr(props.getGoodsIndateLst().get(0)); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; val = " : " + DateConverters.getDateToStr(props.getGoodsIndateLst().get(1)); createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } if (!props.isGoodPeriodAnalysis() && props.getGoodsIndateLst().size() > 0) { val = "+: "; for (LocalDate ld : props.getGoodsIndateLst()) { val += DateConverters.getDateToStr(ld) + "; "; } createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } } if (!props.getClLst().isEmpty()) { if (props.isClIncluded()) { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } else { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } val = "+: "; for (ClientBean cb : props.getClLst()) { val += cb.getClientCl() + "; "; } createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } if (!props.getVLst().isEmpty()) { if (props.isVIncluded()) { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } else { val = " : "; createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } val = ""; for (String v : props.getVLst()) { val += v + "; "; } createHeaderCell(sheet, rnumber, val, cellStyleT); rnumber++; } } //---------- HSSFCellStyle cellStyleH = wb.createCellStyle(); HSSFFont font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyleH.setFont(font); cellStyleH.setWrapText(true); cellStyleH.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleH.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleH.setBorderLeft((short) 1); cellStyleH.setBorderRight((short) 1); cellStyleH.setBorderTop((short) 1); cellStyleH.setBorderBottom((short) 1); HSSFCellStyle cellStyleHh = wb.createCellStyle(); font = wb.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD); cellStyleHh.setFont(font); cellStyleHh.setWrapText(true); cellStyleHh.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleHh.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleHh.setBorderLeft((short) 1); cellStyleHh.setBorderRight((short) 1); cellStyleHh.setBorderTop((short) 1); cellStyleHh.setBorderBottom((short) 1); //filling table HSSFCellStyle cellStyleN = wb.createCellStyle(); cellStyleN.setAlignment(HSSFCellStyle.ALIGN_LEFT); cellStyleN.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleN.setBorderLeft((short) 1); cellStyleN.setBorderRight((short) 1); cellStyleN.setBorderTop((short) 1); cellStyleN.setBorderBottom((short) 1); HSSFCellStyle cellStyleI = wb.createCellStyle(); cellStyleI.setAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleI.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER); cellStyleI.setBorderLeft((short) 1); cellStyleI.setBorderRight((short) 1); cellStyleI.setBorderTop((short) 1); cellStyleI.setBorderBottom((short) 1); HSSFCellStyle cellStyleD = wb.createCellStyle(); cellStyleD.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleD.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT); HSSFDataFormat df = wb.createDataFormat(); cellStyleD.setDataFormat(df.getFormat("#,##0.0")); cellStyleD.setBorderLeft((short) 1); cellStyleD.setBorderRight((short) 1); cellStyleD.setBorderTop((short) 1); cellStyleD.setBorderBottom((short) 1); HSSFCellStyle cellStyleP = wb.createCellStyle(); cellStyleP.setAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleP.setVerticalAlignment(HSSFCellStyle.ALIGN_RIGHT); cellStyleP.setDataFormat(df.getFormat("0.0\\%")); cellStyleP.setBorderLeft((short) 1); cellStyleP.setBorderRight((short) 1); cellStyleP.setBorderTop((short) 1); cellStyleP.setBorderBottom((short) 1); // filling column headers row = sheet.createRow(rnumber); String rowTitle = null; row = sheet.createRow(rnumber); row.setHeightInPoints(40); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString("?")); cell.setCellStyle(cellStyleH); for (int t = 0; t < columnTitles.size(); t++) { cell = row.createCell(t + 1); cell.setCellValue(new HSSFRichTextString(columnTitles.get(t))); cell.setCellStyle(cellStyleH); } // filling table with data rnumber++; for (ObservableList<Object> line : exportData) { row = sheet.createRow(rnumber); cell = row.createCell(0); cell.setCellValue(new HSSFRichTextString((String) line.get(0))); cell.setCellStyle(cellStyleN); for (int i = 1; i < line.size(); i++) { Double val = (Double) line.get(i); cell = row.createCell(i); cell.setCellStyle(cellStyleD); cell.setCellValue(val); } rnumber++; } for (int t = 0; t < columnTitles.size(); t++) { sheet.autoSizeColumn((short) t); } saveWorkBook(wb, selectedFile); execute(selectedFile); }