List of usage examples for org.apache.poi.hssf.usermodel HSSFSheet setColumnWidth
@Override public void setColumnWidth(int columnIndex, int width)
The maximum column width for an individual cell is 255 characters.
From source file:tw.edu.chit.struts.action.registration.ReportPrintAction.java
/** * 4-1//from w ww . j av a 2s. co m * * @param mapping * @param form * @param request * @param response * @param sterm * @throws Exception */ private void printListing41(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); Map<String, Integer> info = Toolket.getPreviousYearTerm(); String lastYear = info.get(IConstants.PARAMETER_SCHOOL_YEAR).toString(); String lastTerm = info.get(IConstants.PARAMETER_SCHOOL_TERM).toString(); List<DeptCode4Yun> deptCodes = mm.findDeptCode4YunBy(new DeptCode4Yun()); if (!deptCodes.isEmpty()) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("4-1"); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 4000); sheet.setColumnWidth(3, 5000); sheet.setColumnWidth(4, 3000); sheet.setColumnWidth(5, 3000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, lastYear + "" + lastTerm + "", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 4, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 5, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; Graduate graduate = new Graduate(); graduate.setOccurYear(Short.valueOf(lastYear)); graduate.setOccurTerm(lastTerm); graduate.setOccurStatus("6"); // graduate.setBirthDate(null); graduate.setBirthday2(null); graduate.setDepartClass2(null); graduate.setIdno(null); graduate.setInformixPass(null); graduate.setOccurStatus2(null); graduate.setPassword(null); graduate.setPriority(null); graduate.setSex2(null); graduate.setUndeleteReason(null); graduate.setUnit2(null); for (DeptCode4Yun code : deptCodes) { Toolket.setCellValue(workbook, sheet, index, 0, code.getDeptCode(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, code.getCampusCode(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, code.getDeptName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, code.getCampusName(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); graduate.setDepartClass(code.getClassNo()); graduate.setSex("1"); Toolket.setCellValue(workbook, sheet, index, 4, String.valueOf(mm.findGraduatesBy(graduate).size()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); graduate.setSex("2"); Toolket.setCellValue(workbook, sheet, index++, 5, String.valueOf(mm.findGraduatesBy(graduate).size()), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "List41Ntnu.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } }
From source file:tw.edu.chit.struts.action.registration.ReportPrintAction.java
/** * ?/* w ww. j a v a 2 s . c o m*/ * * @param mapping * @param form * @param request * @param response * @param sterm * @throws Exception */ private void printStmdUnSeld(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME); MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME); ScoreManager sm = (ScoreManager) getBean(IConstants.SCORE_MANAGER_BEAN_NAME); ServletContext context = request.getSession().getServletContext(); List<Clazz> clazzes = sm.findClassBy(new Clazz(processClassInfo(form)), getUserCredential(session).getClassInChargeAry(), false); String thisYear = cm.getNowBy("School_year"); String thisTerm = am.findTermBy(PARAMETER_SCHOOL_TERM); if (!clazzes.isEmpty()) { List<Student> students = null; HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook .createSheet(thisYear + "" + thisTerm + "?"); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 5000); sheet.setColumnWidth(3, 3500); sheet.setColumnWidth(4, 3500); sheet.setColumnWidth(5, 3500); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize12 = workbook.createFont(); fontSize12.setFontHeightInPoints((short) 12); fontSize12.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, "?", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "??", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "?", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 4, "", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 5, "Email", fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; List<Seld> selds = null; for (Clazz clazz : clazzes) { students = mm.findStudentsByClassNo(clazz.getClassNo()); if (!students.isEmpty()) { for (Student student : students) { selds = cm.findSeldByStudentNoAndTerm(student.getStudentNo(), thisTerm); if (selds.isEmpty()) { Toolket.setCellValue(workbook, sheet, index, 0, student.getStudentNo(), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, student.getStudentName(), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, Toolket.getClassFullName(student.getDepartClass()), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 3, StringUtils.defaultIfEmpty(student.getTelephone(), ""), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 4, StringUtils.defaultIfEmpty(student.getCellPhone(), ""), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index++, 5, StringUtils.defaultIfEmpty(student.getEmail(), ""), fontSize12, HSSFCellStyle.ALIGN_CENTER, true, null); } } } } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "StmdUnSeld.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } }
From source file:tw.edu.chit.struts.action.secretary.ReportPrintAction.java
/** * ?// ww w.j a 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 */ @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 v a2 s . c o m*/ * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ 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 www .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 printOpinionList(ActionMapping mapping, DynaActionForm form, HttpServletRequest request, HttpServletResponse response, String sterm) throws Exception { HttpSession session = request.getSession(false); AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME); DynaActionForm aForm = (DynaActionForm) form; ServletContext context = request.getSession().getServletContext(); String year = aForm.getStrings("year")[0]; String term = aForm.getString("sterm"); String hql = "SELECT COUNT(*), s.target FROM StdOpinionSuggestion s " + "WHERE s.schoolYear = ? AND s.schoolTerm = ? GROUP BY s.target"; List<Object> ret = (List<Object>) am.find(hql, new Object[] { year, term }); if (!ret.isEmpty()) { HSSFWorkbook workbook = new HSSFWorkbook(); HSSFSheet sheet = workbook.createSheet("???"); sheet.setColumnWidth(0, 3000); sheet.setColumnWidth(1, 3000); sheet.setColumnWidth(2, 5000); sheet.setColumnWidth(3, 2000); sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 3)); HSSFFont fontSize16 = workbook.createFont(); fontSize16.setFontHeightInPoints((short) 16); fontSize16.setFontName("Arial Unicode MS"); HSSFFont fontSize10 = workbook.createFont(); fontSize10.setFontHeightInPoints((short) 10); fontSize10.setFontName("Arial Unicode MS"); // Header Toolket.setCellValue(workbook, sheet, 0, 0, "???", fontSize16, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null); // Column Header Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 2, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, 1, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); int index = 2; Object[] obj = null; for (Object o : ret) { obj = (Object[]) o; Toolket.setCellValue(workbook, sheet, index, 0, year, fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 1, term, fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); Toolket.setCellValue(workbook, sheet, index, 2, Toolket.getOpinionDeptName((String) obj[1]), fontSize10, HSSFCellStyle.ALIGN_LEFT, true, null); Toolket.setCellValue(workbook, sheet, index++, 3, ((Integer) obj[0]).toString(), fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null); } File tempDir = new File( context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno() + (new SimpleDateFormat("yyyyMMdd").format(new Date())))); if (!tempDir.exists()) tempDir.mkdirs(); File output = new File(tempDir, "OpinionList.xls"); FileOutputStream fos = new FileOutputStream(output); workbook.write(fos); fos.close(); JasperReportUtils.printXlsToFrontEnd(response, output); output.delete(); tempDir.delete(); } }
From source file:tw.edu.chit.struts.action.secretary.ReportPrintAction.java
/** * ????//from w w w. ja v a 2 s. c o m * * @param mapping org.apache.struts.action.ActionMapping object * @param form org.apache.struts.action.ActionForm object * @param request request javax.servlet.http.HttpServletRequest object * @param response response javax.servlet.http.HttpServletResponse object * @param sterm */ @SuppressWarnings("unchecked") private void 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:xx.tream.chengxin.ms.action.TrainReportAction.java
@RequestMapping({ "/toExport" })
public String toExport(ModelMap modelMap, HttpServletRequest request, HttpServletResponse response,
FormMap formMap, Integer currentPage, Integer pageSize) throws IOException {
Map<String, Object> qm = formMap.getFormMap();
List<Map<String, Object>> list = this.trainService.queryForParam(qm);
//Map<String, Object> statisticsMap = this.trainService.statistics(qm);
String trainIds = this.getTrainIds(list);
List<Map<String, Object>> payingList = this.payingService.queryByTrainIds(trainIds);
List<Map<String, Object>> incomeList = this.incomeService.queryByTrainIds(trainIds);
List<Map<String, Object>> payoutList = this.payoutService.queryByTrainIds(trainIds);
Map<Long, List<Map<String, Object>>> payingMap = converList(payingList);
Map<Long, List<Map<String, Object>>> incomeMap = converList(incomeList);
Map<Long, List<Map<String, Object>>> payoutMap = converList(payoutList);
OutputStream os = response.getOutputStream();
response.reset();/*from w ww .j a v a 2s .co m*/
response.setCharacterEncoding("UTF-8");
String title = "?" + getDateFile() + ".xls";
response.setHeader("Content-disposition", "attachment; filename=" + URLEncoder.encode(title, "UTF-8"));
response.setContentType("application/vnd.ms-excel");
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.createSheet("?");
HSSFRow headrow = sheet.createRow(0);
HSSFCellStyle headcell = workbook.createCellStyle();
HSSFFont font = workbook.createFont();
font.setFontName("");
font.setFontHeightInPoints((short) 10);
headcell.setFont(font);
headrow.setRowStyle(headcell);
String payingValue[] = { "", "paying", "createUserName", "createTime", "createTime", "", "auditUserName",
"auditTime", "auditTime" };
String incomeValue[] = { "type", "income", "createUserName", "createTime", "createTime", "note",
"auditUserName", "auditTime", "auditTime" };
String payoutValue[] = { "type", "payout", "createUserName", "createTime", "createTime", "",
"auditUserName", "auditTime", "auditTime" };
String[] heads = { "", "?", "?", "??", "??", "",
"", "", "", "", "/", "/?", "C1/C2",
"", "", "", "", "", "?", "?",
"?", "?", "", "", "", "" };
String[] values = { "", "id", "autumnNumber", "name", "idcard", "pay", "allpaying", "count_all", "allip",
"canpay", "newOrOld", "type", "licenseTag", "createUserName", "createTime", "createTime", "note" };
HSSFCellStyle headStyle = ExcelUtil.headCell(workbook);
HSSFCell cell = null;
//
int cr = 0;
for (int i = 0; i < heads.length; i++) {
cell = headrow.createCell(cr);
cell.setCellValue(heads[i]);
cell.setCellStyle(headStyle);
sheet.setColumnWidth(cr, 5000);
cr++;
}
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
SimpleDateFormat sdf2 = new SimpleDateFormat("HH:mm");
HSSFCellStyle rowStyle = ExcelUtil.leftCell(workbook);
HSSFCellStyle paleBlueStyle = ExcelUtil.paleBlueForgroundCell(workbook);
//HSSFCellStyle redFontStyle = ExcelUtil.leftFontRedCell(workbook);
//HSSFCellStyle redFontForegroudStyle = ExcelUtil.leftFontRedForegroudCell(workbook);
HSSFCellStyle rightStyle = ExcelUtil.rightCell(workbook);
HSSFCellStyle rightForegroudStyle = ExcelUtil.rightPaleBlueForgroundCell(workbook);
HSSFCellStyle rightredFontStyle = ExcelUtil.rightFontRedCell(workbook);
HSSFCellStyle rightredFontForegroudStyle = ExcelUtil.rightFontRedForegroudCell(workbook);
double ac[] = new double[5];
//
int rn = 1;
HSSFRow row = null;
//??
for (int i = 0; i < list.size(); i++) {
int a = 0;
//
int pb = 0;
//
cr = 0;
row = sheet.createRow(rn++);
Map<String, Object> trainMap = (Map<String, Object>) list.get(i);
List<Map<String, Object>> payL = (List<Map<String, Object>>) payingMap.get((Long) trainMap.get("id"));
List<Map<String, Object>> incomeL = (List<Map<String, Object>>) incomeMap
.get((Long) trainMap.get("id"));
List<Map<String, Object>> payoutL = (List<Map<String, Object>>) payoutMap
.get((Long) trainMap.get("id"));
for (int v = 0; v < values.length; v++) {
cell = row.createCell(cr++);
if (trainMap.get(values[v]) != null) {
if (v == 14) {
cell.setCellValue(sdf.format((Date) trainMap.get(values[v])));
} else if (v == 15) {
cell.setCellValue(sdf2.format((Date) trainMap.get(values[v])));
} else if (v == 5 || v == 6 || v == 7 || v == 8 || v == 9) {
Double d = trainMap.get(values[v]) == null ? 0 : (Double) trainMap.get(values[v]);
ac[a] += d;
a++;
cell.setCellValue((Double) trainMap.get(values[v]));
} else if (v == 1) {
cell.setCellValue((Long) trainMap.get(values[v]));
} else {
cell.setCellValue((String) trainMap.get(values[v]));
}
} else {
if (v == 0) {
cell.setCellValue(i + 1);
} else {
cell.setCellValue("");
}
}
if (v == 5 || v == 6 || v == 7 || v == 8 || v == 9) {//?
cell.setCellStyle(rightForegroudStyle);
} else {
cell.setCellStyle(paleBlueStyle);
}
}
//
if (payL != null && payL.size() > 0) {
for (int p = 0; p < payL.size(); p++) {
Map<String, Object> pMap = payL.get(p);
cr = values.length;
for (int v = 0; v < payingValue.length; v++) {
cell = row.createCell(cr++);
if (v == 0) {
cell.setCellValue("" + (p + 1));
} else {
if (pMap.get(payingValue[v]) != null) {
if (v == 3 || v == 7) {
cell.setCellValue(sdf.format((Date) pMap.get(payingValue[v])));
} else if (v == 4 || v == 8) {
cell.setCellValue(sdf2.format((Date) pMap.get(payingValue[v])));
} else if (v == 1) {
Double nv = (Double) pMap.get(payingValue[v]);
cell.setCellValue(nv);
} else {
cell.setCellValue((String) pMap.get(payingValue[v]));
}
} else {
cell.setCellValue("");
}
}
if (v == 1) {//?
if (pb == 0) {
cell.setCellStyle(rightForegroudStyle);
} else {
cell.setCellStyle(rightStyle);
}
} else {
if (pb == 0) {
cell.setCellStyle(paleBlueStyle);
} else {
cell.setCellStyle(rowStyle);
}
}
}
pb++;
row = sheet.createRow(rn++);
}
}
//
if (incomeL != null && incomeL.size() > 0) {
for (int p = 0; p < incomeL.size(); p++) {
Map<String, Object> iMap = incomeL.get(p);
cr = values.length;
for (int v = 0; v < incomeValue.length; v++) {
cell = row.createCell(cr++);
if (v == 0) {
cell.setCellValue(iMap.get(incomeValue[v]) + "()");
} else {
if (iMap.get(incomeValue[v]) != null) {
if (v == 3 || v == 7) {
cell.setCellValue(sdf.format((Date) iMap.get(incomeValue[v])));
} else if (v == 4 || v == 8) {
cell.setCellValue(sdf2.format((Date) iMap.get(incomeValue[v])));
} else if (v == 1) {
cell.setCellValue((Double) iMap.get(incomeValue[v]));
} else {
cell.setCellValue((String) iMap.get(incomeValue[v]));
}
} else {
cell.setCellValue("");
}
}
if (v == 1) {//?
if (pb == 0) {
cell.setCellStyle(rightForegroudStyle);
} else {
cell.setCellStyle(rightStyle);
}
} else {
if (pb == 0) {
cell.setCellStyle(paleBlueStyle);
} else {
cell.setCellStyle(rowStyle);
}
}
}
pb++;
row = sheet.createRow(rn++);
}
}
boolean flag = false;
//
if (payoutL != null && payoutL.size() > 0) {
for (int p = 0; p < payoutL.size(); p++) {
Map<String, Object> pMap = payoutL.get(p);
cr = values.length;
for (int v = 0; v < payoutValue.length; v++) {
cell = row.createCell(cr++);
if (v == 0) {
cell.setCellValue(pMap.get(payoutValue[v]) + "()");
} else {
if (pMap.get(payoutValue[v]) != null) {
if (v == 3 || v == 7) {
cell.setCellValue(sdf.format((Date) pMap.get(payoutValue[v])));
} else if (v == 4 || v == 8) {
cell.setCellValue(sdf2.format((Date) pMap.get(payoutValue[v])));
} else if (v == 1) {
flag = true;
cell.setCellValue(0 - (Double) pMap.get(payoutValue[v]));
} else {
cell.setCellValue((String) pMap.get(payoutValue[v]));
}
} else {
cell.setCellValue("");
}
}
if (pb == 0 && flag) {
flag = false;
cell.setCellStyle(rightredFontForegroudStyle);
} else if (flag) {
flag = false;
cell.setCellStyle(rightredFontStyle);
} else if (pb == 0) {
cell.setCellStyle(paleBlueStyle);
} else {
cell.setCellStyle(rowStyle);
}
}
pb++;
if (p != payoutL.size() - 1) {
row = sheet.createRow(rn++);
}
}
}
}
if (list != null && list.size() > 0) {
row = sheet.createRow(rn++);
cell = row.createCell(0);
cell.setCellValue("?");
cell.setCellStyle(headStyle);
for (int i = 0; i < ac.length; i++) {
cell = row.createCell(5 + i);
cell.setCellValue(ac[i]);
cell.setCellStyle(rightStyle);
}
}
workbook.write(os);
os.flush();
os.close();
return null;
}
From source file:xx.tream.chengxin.ms.service.ReportServiceImpl.java
public HSSFWorkbook financialStatements(HSSFWorkbook workbook, Map<String, Object> queryMap) { HSSFSheet sheet = workbook.createSheet(""); HSSFRow headrow = sheet.createRow(0); HSSFCellStyle headcell = ExcelUtil.headCell(workbook); headrow.setRowStyle(headcell);//w w w .j a v a 2s . co m String start = (String) queryMap.get("beginTime"); String end = (String) queryMap.get("endTime"); Date startDate = DateUtil.stringToDate(start, "yyyy-MM-dd"); Date endDate = DateUtil.stringToDate(end, "yyyy-MM-dd"); HSSFCellStyle rightcell = ExcelUtil.rightCell(workbook); //HSSFCellStyle leftcell = ExcelUtil.leftCell(workbook); HSSFCellStyle centercell = ExcelUtil.centerCell(workbook); //? int icount = 0; if (endDate != null) { //1 Calendar cal = Calendar.getInstance(); cal.setTime(endDate); cal.add(Calendar.DATE, 1); endDate = cal.getTime(); end = DateUtil.DateToString(endDate, "yyyy-MM-dd"); queryMap.put("endTime", end); } // ExcelUtil.mergeRegion(sheet, 0, 1, 0, 0, "", headcell); sheet.setColumnWidth(0, 6500); icount++; // ExcelUtil.mergeRegion(sheet, 0, 1, 1, 1, "", headcell); sheet.setColumnWidth(1, 4500); HSSFRow row = sheet.createRow(1); icount++; int col = 2; // String[] incomeItems = ParamUtil.incomeItems; for (int i = 0; i < incomeItems.length - 1; i++) { //??? HSSFCell cell = row.createCell(col); cell.setCellValue(incomeItems[i]); cell.setCellStyle(headcell); sheet.setColumnWidth(col, 4500); col++; icount++; } String[] otherIncomeItems = reportParamService.queryIncomeOtherItem(startDate, endDate); int n = 0; if (otherIncomeItems != null && otherIncomeItems.length > 0) { for (int i = 0; i < otherIncomeItems.length; i++) { HSSFCell cell = row.createCell(col); cell.setCellValue("(" + otherIncomeItems[i] + ")"); cell.setCellStyle(headcell); sheet.setColumnWidth(col, 4500); icount++; col++; } n = otherIncomeItems.length; } int s = 2; int e = incomeItems.length + n; ExcelUtil.mergeRegion(sheet, 0, 0, s, e, "", headcell); // String[] incomeCommonItems = ParamUtil.incomeCommonItems; for (int i = 0; i < incomeCommonItems.length - 1; i++) { //??? HSSFCell cell = row.createCell(col); cell.setCellValue(incomeCommonItems[i]); cell.setCellStyle(headcell); sheet.setColumnWidth(col, 4500); col++; icount++; } String[] otherIncomeCommonItems = reportParamService.queryIncomeCommonOtherItem(startDate, endDate); n = 0; if (otherIncomeCommonItems != null && otherIncomeCommonItems.length > 0) { for (int i = 0; i < otherIncomeCommonItems.length; i++) { HSSFCell cell = row.createCell(col); cell.setCellValue("(" + otherIncomeCommonItems[i] + ")"); cell.setCellStyle(headcell); sheet.setColumnWidth(col, 4500); icount++; col++; } n = otherIncomeCommonItems.length; } s = e + 1; e = e + incomeCommonItems.length - 1 + n; ExcelUtil.mergeRegion(sheet, 0, 0, s, e, "", headcell); // n = 0; String payoutItems[] = ParamUtil.payoutItems; for (int i = 0; i < payoutItems.length - 1; i++) { //??? HSSFCell cell = row.createCell(col); cell.setCellValue(payoutItems[i]); cell.setCellStyle(headcell); sheet.setColumnWidth(col, 4500); col++; } String[] payoutOtherItems = reportParamService.queryPayoutOtherItem(startDate, endDate); if (payoutOtherItems != null && payoutOtherItems.length > 0) { for (int i = 0; i < payoutOtherItems.length; i++) { HSSFCell cell = row.createCell(col); cell.setCellValue("(" + payoutOtherItems[i] + ")"); cell.setCellStyle(headcell); sheet.setColumnWidth(col, 4500); col++; } n = payoutOtherItems.length; } s = e + 1; e = e + payoutItems.length - 1 + n; ExcelUtil.mergeRegion(sheet, 0, 0, s, e, "", headcell); // String[] payoutCommonItems = ParamUtil.payoutCommonItems; for (int i = 0; i < payoutCommonItems.length - 1; i++) { //??? HSSFCell cell = row.createCell(col); cell.setCellValue(payoutCommonItems[i]); cell.setCellStyle(headcell); sheet.setColumnWidth(col, 4500); col++; } String payoutCommonOtherTypes[] = reportParamService.queryPayoutCommonOtherItem(startDate, endDate); if (payoutCommonOtherTypes != null && payoutCommonOtherTypes.length > 0) { for (int i = 0; i < payoutCommonOtherTypes.length; i++) { HSSFCell cell = row.createCell(col); cell.setCellValue("(" + payoutCommonOtherTypes[i] + ")"); cell.setCellStyle(headcell); sheet.setColumnWidth(col, 4500); col++; } n = payoutCommonOtherTypes.length; } s = e + 1; e = e + payoutCommonItems.length - 1 + n; ExcelUtil.mergeRegion(sheet, 0, 0, s, e, "", headcell); HSSFCell cell = null; //? queryMap.put("", ""); List<Map<String, Object>> list = this.reportParamService.queryIncomeAndPayout(queryMap, incomeItems, otherIncomeItems, incomeCommonItems, otherIncomeCommonItems, payoutItems, payoutOtherItems, payoutCommonItems, payoutCommonOtherTypes); if (list != null && list.size() > 0) { double[] count = new double[col]; for (int i = 0; i < list.size(); i++) { col = 0; row = sheet.createRow(2 + i); Map<String, Object> map = list.get(i); cell = row.createCell(col); cell.setCellValue((String) map.get("audittime")); col++; cell = row.createCell(col); if (map.get("paying") != null) { Double v = (Double) map.get("paying"); count[col] = count[col] + v; cell.setCellValue(v); } col++; // if (incomeItems != null && incomeItems.length > 0) { for (int k = 0; k < incomeItems.length - 1; k++) { cell = row.createCell(col); if (map.get("i" + k) != null) { Double v = (Double) map.get("i" + k); count[col] = count[col] + v; cell.setCellValue(v); } col++; } } if (otherIncomeItems != null && otherIncomeItems.length > 0) { for (int k = 0; k < otherIncomeItems.length; k++) { cell = row.createCell(col); if (map.get("io" + k) != null) { Double v = (Double) map.get("io" + k); count[col] = count[col] + v; cell.setCellValue(v); } col++; } } // if (incomeCommonItems != null && incomeCommonItems.length > 0) { for (int k = 0; k < incomeCommonItems.length - 1; k++) { cell = row.createCell(col); if (map.get("ic" + k) != null) { Double v = (Double) map.get("ic" + k); count[col] = count[col] + v; cell.setCellValue(v); } col++; } } if (otherIncomeCommonItems != null && otherIncomeCommonItems.length > 0) { for (int k = 0; k < otherIncomeCommonItems.length; k++) { cell = row.createCell(col); if (map.get("ico" + k) != null) { Double v = (Double) map.get("ico" + k); count[col] = count[col] + v; cell.setCellValue(v); } col++; } } // if (payoutItems != null && payoutItems.length > 0) { for (int k = 0; k < payoutItems.length - 1; k++) { cell = row.createCell(col); if (map.get("p" + k) != null) { Double v = (Double) map.get("p" + k); count[col] = count[col] + v; cell.setCellValue(v); } col++; } } if (payoutOtherItems != null && payoutOtherItems.length > 0) { for (int k = 0; k < payoutOtherItems.length; k++) { cell = row.createCell(col); if (map.get("po" + k) != null) { Double v = (Double) map.get("po" + k); count[col] = count[col] + v; cell.setCellValue(v); } col++; } } // if (payoutCommonItems != null && payoutCommonItems.length > 0) { for (int k = 0; k < payoutCommonItems.length - 1; k++) { cell = row.createCell(col); if (map.get("pc" + k) != null) { Double v = (Double) map.get("pc" + k); count[col] = count[col] + v; cell.setCellValue(v); } col++; } } if (payoutCommonOtherTypes != null && payoutCommonOtherTypes.length > 0) { for (int k = 0; k < payoutCommonOtherTypes.length; k++) { cell = row.createCell(col); if (map.get("pco" + k) != null) { Double v = (Double) map.get("pco" + k); count[col] = count[col] + v; cell.setCellValue(v); } col++; } } } //? row = sheet.createRow(2 + list.size()); cell = null; for (int i = 0; i < count.length; i++) { if (i == 0) { cell = row.createCell(0); cell.setCellValue("?"); cell.setCellStyle(headcell); } else { cell = row.createCell(i); cell.setCellValue(count[i]); } } double ivalue = 0; double pvalue = 0; for (int i = 0; i < icount; i++) { ivalue += count[i]; } for (int i = icount; i < count.length; i++) { pvalue += count[i]; } // row = sheet.createRow(3 + list.size()); cell = row.createCell(0); cell.setCellValue(":"); cell.setCellStyle(headcell); cell = row.createCell(1); cell.setCellValue(ivalue); cell = row.createCell(2); cell.setCellValue(":"); cell.setCellStyle(headcell); cell = row.createCell(3); cell.setCellValue(pvalue); } int rowNum = 0; // List<Map<String, Object>> trainIPOList = this.queryTrainIncomePayout(queryMap); if (trainIPOList != null && trainIPOList.size() > 0) { sheet = workbook.createSheet(""); String heads[] = { "", "?", "", "", "", "", "" }; String values[] = { "name", "idcard", "payable", "allincome", "allpaying", "allpayout" }; row = sheet.createRow(rowNum); for (int i = 0; i < heads.length; i++) { cell = row.createCell(i); cell.setCellValue(heads[i]); cell.setCellStyle(headcell); sheet.setColumnWidth(i, 4500); } rowNum++; double countAll[] = new double[heads.length]; for (int i = 0; i < trainIPOList.size(); i++) { row = sheet.createRow(rowNum); Map<String, Object> map = trainIPOList.get(i); // double allpip = 0; for (int k = 0; k < values.length; k++) { cell = row.createCell(k); if (map.get(values[k]) != null) { if (k > 1) { double value = (Double) map.get(values[k]); if (k < 4) { allpip += value; } cell.setCellValue(value); countAll[k] += value; } else { cell.setCellValue((String) map.get(values[k])); } } } // cell = row.createCell(values.length); cell.setCellValue(allpip); countAll[values.length] += allpip; rowNum++; } //? row = sheet.createRow(rowNum); cell = row.createCell(0); cell.setCellValue("?"); cell.setCellStyle(headcell); for (int i = 2; i < countAll.length; i++) { cell = row.createCell(i); cell.setCellValue(countAll[i]); } rowNum++; } rowNum = 0; // List<Map<String, Object>> countList = this.queryCountByTime(queryMap); if (countList != null && countList.size() > 0) { sheet = workbook.createSheet(""); String heads[] = { "", "?", "?", "?", "?", "?", "?", "???" }; String values[] = { "audittime", "payable", "paying", "income", "incomeCommon", "payout", "payoutCommon", "canPay" }; row = sheet.createRow(rowNum); for (int i = 0; i < heads.length; i++) { cell = row.createCell(i); cell.setCellValue(heads[i]); cell.setCellStyle(headcell); sheet.setColumnWidth(i, 4500); } rowNum++; double countAll[] = new double[heads.length]; for (int i = 0; i < countList.size(); i++) { row = sheet.createRow(rowNum); Map<String, Object> map = countList.get(i); for (int k = 0; k < values.length; k++) { cell = row.createCell(k); if (map.get(values[k]) != null) { if (k > 0) { double value = (Double) map.get(values[k]); cell.setCellValue(value); cell.setCellStyle(rightcell); countAll[k] += value; } else { cell.setCellValue((String) map.get(values[k])); cell.setCellStyle(centercell); } } } rowNum++; } //? row = sheet.createRow(rowNum); cell = row.createCell(0); cell.setCellValue("?"); cell.setCellStyle(headcell); for (int i = 1; i < countAll.length; i++) { cell = row.createCell(i); cell.setCellValue(countAll[i]); if (countAll[i] >= 0) { cell.setCellStyle(rightcell); } else { } } rowNum++; } return workbook; }
From source file:xx.tream.chengxin.ms.service.ReportServiceImpl.java
public HSSFWorkbook financialStatementsDetail(HSSFWorkbook workbook, Map<String, Object> queryMap) { HSSFSheet sheet = null; HSSFCellStyle headcell = ExcelUtil.headCell(workbook); HSSFRow row = null;/*from w w w . j av a 2s. c o m*/ HSSFCell cell = null; HSSFCellStyle leftStyle = ExcelUtil.leftCell(workbook); HSSFCellStyle rightStyle = ExcelUtil.rightCell(workbook); String end = (String) queryMap.get("endTime"); Date endDate = DateUtil.stringToDate(end, "yyyy-MM-dd"); if (endDate != null) { //1 Calendar cal = Calendar.getInstance(); cal.setTime(endDate); cal.add(Calendar.DATE, 1); endDate = cal.getTime(); end = DateUtil.DateToString(endDate, "yyyy-MM-dd"); queryMap.put("endTime", end); } int rowNum = 0; // List<Map<String, Object>> trainIPOList = this.queryTrainIncomePayout(queryMap); if (trainIPOList != null && trainIPOList.size() > 0) { sheet = workbook.createSheet(""); String heads[] = { "", "", "?", "", "", "", "", "", "", "?", "" }; String values[] = { "", "name", "idcard", "payable", "allpaying", "allincome", "allpayout", "count_all", "newOrOld", "type", "licenseTag" }; row = sheet.createRow(rowNum); for (int i = 0; i < heads.length; i++) { cell = row.createCell(i); cell.setCellValue(heads[i]); cell.setCellStyle(headcell); sheet.setColumnWidth(i, 4500); } rowNum++; double countAll[] = new double[heads.length]; for (int i = 0; i < trainIPOList.size(); i++) { row = sheet.createRow(rowNum); Map<String, Object> map = trainIPOList.get(i); for (int k = 0; k < values.length; k++) { cell = row.createCell(k); if (map.get(values[k]) != null) { if (k > 2 && k < 8) { double value = (Double) map.get(values[k]); cell.setCellValue(value); countAll[k] += value; } else { cell.setCellValue((String) map.get(values[k])); } } else if (k == 0) { cell.setCellValue(i + 1); } if (k > 2 && k < 8) { cell.setCellStyle(rightStyle); } else { cell.setCellStyle(leftStyle); } } rowNum++; } // ? row = sheet.createRow(rowNum); cell = row.createCell(0); cell.setCellValue("?"); cell.setCellStyle(headcell); for (int i = 3; i < 8; i++) { cell = row.createCell(i); cell.setCellValue(countAll[i]); cell.setCellStyle(rightStyle); } } // List<Map<String, Object>> payList = this.payingService.queryByParam(queryMap); if (payList != null && payList.size() > 0) { rowNum = 0; sheet = workbook.createSheet(""); String heads[] = { "", "?", "", "?", "?", "?", "", "", "" }; String values[] = { "name", "idcard", "paying", "createUserName", "createTime", "createTime", "auditUserName", "auditTime", "auditTime" }; row = sheet.createRow(rowNum); double p = 0; for (int i = 0; i < heads.length; i++) { cell = row.createCell(i); cell.setCellValue(heads[i]); cell.setCellStyle(headcell); sheet.setColumnWidth(i, 4500); } rowNum++; for (int i = 0; i < payList.size(); i++) { row = sheet.createRow(rowNum); Map<String, Object> map = payList.get(i); for (int k = 0; k < values.length; k++) { cell = row.createCell(k); if (map.get(values[k]) != null) { if (k == 2) { p += (Double) map.get(values[k]); cell.setCellValue((Double) map.get(values[k])); } else if (k == 4 || k == 5 || k == 7 || k == 8) { if (k == 4 || k == 7) cell.setCellValue(DateUtil.DateToString((Date) map.get(values[k]), "yyyy-MM-dd")); if (k == 5 || k == 8) cell.setCellValue(DateUtil.DateToString((Date) map.get(values[k]), "HH:mm")); } else { cell.setCellValue((String) map.get(values[k])); } } if (k == 2) { cell.setCellStyle(rightStyle); } else { cell.setCellStyle(leftStyle); } } rowNum++; } //? row = sheet.createRow(rowNum); cell = row.createCell(0); cell.setCellValue("?"); cell.setCellStyle(headcell); cell = row.createCell(2); cell.setCellValue(p); cell.setCellStyle(rightStyle); rowNum++; } // List<Map<String, Object>> incomeList = this.incomeService.queryByParam(queryMap); if (incomeList != null && incomeList.size() > 0) { sheet = workbook.createSheet(""); rowNum = 0; double iv = 0; String heads[] = { "", "?", "", "?", "", "?", "?", "?", "", "", "" }; String values[] = { "name", "idcard", "type", "income", "note", "createUserName", "createTime", "createTime", "auditUserName", "auditTime", "auditTime" }; row = sheet.createRow(rowNum); for (int i = 0; i < heads.length; i++) { cell = row.createCell(i); cell.setCellValue(heads[i]); cell.setCellStyle(headcell); sheet.setColumnWidth(i, 4500); } rowNum++; for (int i = 0; i < incomeList.size(); i++) { row = sheet.createRow(rowNum); Map<String, Object> map = incomeList.get(i); for (int k = 0; k < values.length; k++) { cell = row.createCell(k); if (map.get(values[k]) != null) { if (k == 3) { iv += (Double) map.get(values[k]); cell.setCellValue((Double) map.get(values[k])); } else if (k == 6 || k == 7 || k == 9 || k == 10) { if (k == 6 || k == 9) cell.setCellValue(DateUtil.DateToString((Date) map.get(values[k]), "yyyy-MM-dd")); if (k == 7 || k == 10) cell.setCellValue(DateUtil.DateToString((Date) map.get(values[k]), "HH:mm")); } else { String v = (String) map.get(values[k]); if (k == 2 && v.equals("")) { v = "(" + map.get("otherType") + ")"; } cell.setCellValue(v); } } if (k == 3) { cell.setCellStyle(rightStyle); } else { cell.setCellStyle(leftStyle); } } rowNum++; } //? row = sheet.createRow(rowNum); cell = row.createCell(0); cell.setCellValue("?"); cell.setCellStyle(headcell); cell = row.createCell(3); cell.setCellValue(iv); cell.setCellStyle(rightStyle); rowNum++; } // List<Map<String, Object>> incomeCommonList = this.incomeCommonService.queryByParam(queryMap); if (incomeCommonList != null && incomeCommonList.size() > 0) { sheet = workbook.createSheet(""); rowNum = 0; double iv = 0; String heads[] = { "", "?", "", "?", "?", "?", "", "", "" }; String values[] = { "type", "income", "note", "createUserName", "createTime", "createTime", "auditUserName", "auditTime", "auditTime" }; row = sheet.createRow(rowNum); for (int i = 0; i < heads.length; i++) { cell = row.createCell(i); cell.setCellValue(heads[i]); cell.setCellStyle(headcell); sheet.setColumnWidth(i, 4500); } rowNum++; for (int i = 0; i < incomeCommonList.size(); i++) { row = sheet.createRow(rowNum); Map<String, Object> map = incomeCommonList.get(i); for (int k = 0; k < values.length; k++) { cell = row.createCell(k); if (map.get(values[k]) != null) { if (k == 1) { iv += (Double) map.get(values[k]); cell.setCellValue((Double) map.get(values[k])); } else if (k == 4 || k == 5 || k == 7 || k == 8) { if (k == 4 || k == 7) cell.setCellValue(DateUtil.DateToString((Date) map.get(values[k]), "yyyy-MM-dd")); if (k == 5 || k == 8) cell.setCellValue(DateUtil.DateToString((Date) map.get(values[k]), "HH:mm")); } else { String v = (String) map.get(values[k]); if (k == 0 && v.equals("")) { v = "(" + map.get("otherType") + ")"; } cell.setCellValue(v); } } if (k == 1) { cell.setCellStyle(rightStyle); } else { cell.setCellStyle(leftStyle); } } rowNum++; } //? row = sheet.createRow(rowNum); cell = row.createCell(0); cell.setCellValue("?"); cell.setCellStyle(headcell); cell = row.createCell(1); cell.setCellValue(iv); cell.setCellStyle(rightStyle); } // List<Map<String, Object>> payoutList = this.payoutService.queryByParam(queryMap); if (payoutList != null && payoutList.size() > 0) { sheet = workbook.createSheet(""); rowNum = 0; String heads[] = { "", "?", "", "?", "", "?", "?", "?", "", "", "" }; String values[] = { "name", "idcard", "type", "payout", "note", "createUserName", "createTime", "createTime", "auditUserName", "auditTime", "auditTime" }; row = sheet.createRow(rowNum); double pv = 0; for (int i = 0; i < heads.length; i++) { cell = row.createCell(i); cell.setCellValue(heads[i]); cell.setCellStyle(headcell); sheet.setColumnWidth(i, 4500); } rowNum++; for (int i = 0; i < payoutList.size(); i++) { row = sheet.createRow(rowNum); Map<String, Object> map = payoutList.get(i); for (int k = 0; k < values.length; k++) { cell = row.createCell(k); if (map.get(values[k]) != null) { if (k == 3) { pv += (Double) map.get(values[k]); cell.setCellValue((Double) map.get(values[k])); } else if (k == 6 || k == 7 || k == 9 || k == 10) { if (k == 6 || k == 9) cell.setCellValue(DateUtil.DateToString((Date) map.get(values[k]), "yyyy-MM-dd")); if (k == 7 || k == 10) cell.setCellValue(DateUtil.DateToString((Date) map.get(values[k]), "HH:mm")); } else { String v = (String) map.get(values[k]); if (k == 2 && v.equals("")) { v = "(" + map.get("otherType") + ")"; } cell.setCellValue(v); } } if (k == 3) { cell.setCellStyle(rightStyle); } else { cell.setCellStyle(leftStyle); } } rowNum++; } //? row = sheet.createRow(rowNum); cell = row.createCell(0); cell.setCellValue("?"); cell.setCellStyle(headcell); cell = row.createCell(3); cell.setCellValue(pv); cell.setCellStyle(rightStyle); } // List<Map<String, Object>> payoutCommonList = this.payoutCommonService.queryByParam(queryMap); if (payoutCommonList != null && payoutCommonList.size() > 0) { sheet = workbook.createSheet(""); rowNum = 0; String heads[] = { "", "?", "", "?", "?", "?", "", "", "" }; String values[] = { "type", "payout", "note", "createUserName", "createTime", "createTime", "auditUserName", "auditTime", "auditTime" }; row = sheet.createRow(rowNum); double pv = 0; for (int i = 0; i < heads.length; i++) { cell = row.createCell(i); cell.setCellValue(heads[i]); cell.setCellStyle(headcell); sheet.setColumnWidth(i, 4500); } rowNum++; for (int i = 0; i < payoutCommonList.size(); i++) { row = sheet.createRow(rowNum); Map<String, Object> map = payoutCommonList.get(i); for (int k = 0; k < values.length; k++) { cell = row.createCell(k); if (map.get(values[k]) != null) { if (k == 1) { pv += (Double) map.get(values[k]); cell.setCellValue((Double) map.get(values[k])); } else if (k == 4 || k == 5 || k == 7 || k == 8) { if (k == 4 || k == 7) cell.setCellValue(DateUtil.DateToString((Date) map.get(values[k]), "yyyy-MM-dd")); if (k == 5 || k == 8) cell.setCellValue(DateUtil.DateToString((Date) map.get(values[k]), "HH:mm")); } else { String v = (String) map.get(values[k]); if (k == 0 && v.equals("")) { v = "(" + map.get("otherType") + ")"; } cell.setCellValue(v); } } if (k == 1) { cell.setCellStyle(rightStyle); } else { cell.setCellStyle(leftStyle); } } rowNum++; } //? row = sheet.createRow(rowNum); cell = row.createCell(0); cell.setCellValue("?"); cell.setCellStyle(headcell); cell = row.createCell(1); cell.setCellValue(pv); cell.setCellStyle(rightStyle); } return workbook; }
From source file:xx.tream.chengxin.ms.service.ReportServiceImpl.java
public HSSFWorkbook payoutCommonReport(HSSFWorkbook workbook, Map<String, Object> queryMap) { HSSFSheet sheet = workbook.createSheet("new sheet"); HSSFCellStyle headcell = ExcelUtil.headCell(workbook); String heads[] = { "", "?", "", "?", "?", "?", "", "", "" }; String values[] = { "type", "payout", "note", "createUserName", "createTime", "createTime", "auditUserName", "auditTime", "auditTime" }; HSSFRow row = sheet.createRow(0);/*from www . j a va 2 s.c o m*/ for (int i = 0; i < heads.length; i++) { HSSFCell cell = row.createCell(i); cell.setCellValue(heads[i]); cell.setCellStyle(headcell); sheet.setColumnWidth(i, 4500); } String end = (String) queryMap.get("endTime"); Date endDate = DateUtil.stringToDate(end, "yyyy-MM-dd"); if (endDate != null) { //1 Calendar cal = Calendar.getInstance(); cal.setTime(endDate); cal.add(Calendar.DATE, 1); endDate = cal.getTime(); end = DateUtil.DateToString(endDate, "yyyy-MM-dd"); queryMap.put("endTime", end); } List<Map<String, Object>> list = this.payoutCommonService.queryByParam(queryMap); if (list != null && list.size() > 0) { Double payouts = 0d; for (int i = 0; i < list.size(); i++) { row = sheet.createRow(i + 1); Map<String, Object> map = list.get(i); for (int n = 0; n < values.length; n++) { HSSFCell cell = row.createCell(n); String value = ""; if (n == 0) { value = (String) map.get(values[n]); if (value != null && value.equals("")) { value = value + "(" + map.get("otherType") + ")"; } cell.setCellValue(value); } else if (n == 4 || n == 5 || n == 7 || n == 8) {// Date date = (Date) map.get(values[n]); //value = DateUtil.DateToString(date, "yyyy-MM-dd HH:mm"); if (n == 4 || n == 7) { value = DateUtil.DateToString(date, "yyyy-MM-dd"); } else if (n == 5 || n == 8) { value = DateUtil.DateToString(date, "HH:mm"); } cell.setCellValue(value); } else if (n == 1 && map.get(values[n]) != null) { Double v = (Double) map.get(values[n]); payouts += v; cell.setCellValue(v); } else { value = (String) map.get(values[n]); cell.setCellValue(value); } } } row = sheet.createRow(list.size() + 1); HSSFCell cell = row.createCell(0); cell.setCellValue("?:"); cell.setCellStyle(headcell); cell = row.createCell(1); cell.setCellValue(payouts); } return workbook; }