Example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createFont

List of usage examples for org.apache.poi.hssf.usermodel HSSFWorkbook createFont

  1. HOME
  2. Java
  3. org.apache.poi
  4. org.apache.poi.hssf.usermodel.*
  5. HSSFWorkbook
  6. createFont

Introduction

In this page you can find the example usage for org.apache.poi.hssf.usermodel HSSFWorkbook createFont.

Prototype


@Override
public HSSFFont createFont()

Source Link

Document

create a new Font and add it to the workbook's font table

Usage

From source file:tw.edu.chit.struts.action.deptassist.ReportPrintAction.java

/**
 * //from  ww w  .j a  v  a2s. c o  m
 * 
 * @param mapping org.apache.struts.action.ActionMapping object
 * @param form org.apache.struts.action.ActionForm object
 * @param request request javax.servlet.http.HttpServletRequest object
 * @param response response javax.servlet.http.HttpServletResponse object
 * @param sterm 
 */
@SuppressWarnings("unchecked")
private void printDeptStdSkillList1(ActionMapping mapping, DynaActionForm form, HttpServletRequest request,
        HttpServletResponse response, String sterm) throws Exception {

    HttpSession session = request.getSession(false);
    AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME);
    MemberManager mm = (MemberManager) getBean(MEMBER_MANAGER_BEAN_NAME);

    Member member = (Member) getUserCredential(session).getMember();
    Empl empl = mm.findEmplByOid(member.getOid());
    ServletContext context = request.getSession().getServletContext();

    CodeEmpl codeEmpl = new CodeEmpl();
    codeEmpl.setIdno(empl.getUnit());
    Example example4CodeEmpl = Example.create(codeEmpl).ignoreCase().enableLike(MatchMode.START);
    List<CodeEmpl> codeEmpls = (List<CodeEmpl>) am.findSQLWithCriteria(CodeEmpl.class, example4CodeEmpl, null,
            null);

    DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
    Calendar cal = Calendar.getInstance();
    Calendar cal1 = (Calendar) cal.clone();
    Calendar cal2 = (Calendar) cal.clone();
    if (StringUtils.isNotBlank(form.getString("licenseValidDateStart"))
            || StringUtils.isNotBlank(form.getString("licenseValidDateEnd"))) {
        Date from = StringUtils.isBlank(form.getString("licenseValidDateStart")) ? null
                : Toolket.parseNativeDate(form.getString("licenseValidDateStart"));
        // ???
        Date to = StringUtils.isBlank(form.getString("licenseValidDateEnd")) ? Calendar.getInstance().getTime()
                : Toolket.parseNativeDate(form.getString("licenseValidDateEnd"));

        cal1.setTime(from);
        cal1.set(Calendar.HOUR_OF_DAY, 0);
        cal1.set(Calendar.MINUTE, 0);
        cal1.set(Calendar.SECOND, 0);
        cal1.set(Calendar.MILLISECOND, 0);

        cal2.setTime(to);
        cal2.set(Calendar.HOUR_OF_DAY, 23);
        cal2.set(Calendar.MINUTE, 59);
        cal2.set(Calendar.SECOND, 59);
        cal2.set(Calendar.MILLISECOND, 999);
    }

    String hql = "SELECT count(*), s FROM StdSkill s WHERE s.deptNo = ? "
            + "AND s.licenseValidDate BETWEEN ? AND ? " + "GROUP BY s.licenseCode";

    List<Object> ret = (List<Object>) am.find(hql, new Object[] {
            //codeEmpls.get(0).getIdno2().trim(), cal1.getTime(),cal2.getTime() });
            empl.getUnit(), cal1.getTime(), cal2.getTime() });

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("");
    sheet.setColumnWidth(0, 1500);
    sheet.setColumnWidth(1, 10000);
    sheet.setColumnWidth(2, 3000);
    sheet.setColumnWidth(3, 3000);
    sheet.setColumnWidth(4, 8000);
    sheet.setColumnWidth(5, 1800);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));

    HSSFFont fontSize12 = workbook.createFont();
    fontSize12.setFontHeightInPoints((short) 12);
    fontSize12.setFontName("Arial Unicode MS");

    HSSFFont fontSize10 = workbook.createFont();
    fontSize10.setFontHeightInPoints((short) 10);
    fontSize10.setFontName("Arial Unicode MS");

    // Header
    Toolket.setCellValue(workbook, sheet, 0, 0,
            "?" + Toolket.getEmpUnit(empl.getUnit()) + "  ("
                    + df.format(cal1.getTime()) + "~" + df.format(cal2.getTime()) + ")",
            fontSize12, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null);

    // Column Header
    Toolket.setCellValue(workbook, sheet, 1, 0, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    Toolket.setCellValue(workbook, sheet, 1, 1, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 2, "/", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 4, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 5, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    int index = 2, totals = 0;
    StdSkill skill = null;
    List<LicenseCode> codes = null;
    List<LicenseCode961> code961s = null;
    LicenseCode code = null;
    LicenseCode961 code961 = null;
    Object[] data = null;

    for (Object o : ret) {

        data = (Object[]) o;
        totals += (Long) data[0];
        skill = (StdSkill) data[1];

        Toolket.setCellValue(workbook, sheet, index, 0, String.valueOf(index - 1), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);
        Toolket.setCellValue(workbook, sheet, index, 5, ((Long) data[0]).toString(), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);

        CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME);
        LicenseCode c = (LicenseCode) cm
                .hqlGetBy("FROM LicenseCode WHERE code='" + skill.getLicenseCode() + "'").get(0);

        //System.out.println("FROM LicenseCode WHERE code='"+skill.getLicenseCode()+"'");
        codes = (List<LicenseCode>) am.findLicenseCodesBy(c);

        LicenseType type = new LicenseType();

        if (!codes.isEmpty()) {
            code = codes.get(0);

            Toolket.setCellValue(workbook, sheet, index, 1, code.getName(), fontSize10,
                    HSSFCellStyle.ALIGN_LEFT, true, null);
            Toolket.setCellValue(workbook, sheet, index, 2, code.getLevel(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            try {

                Toolket.setCellValue(workbook, sheet, index, 3, code.getType(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 4, code.getDeptName(), fontSize10,
                        HSSFCellStyle.ALIGN_LEFT, true, null);
            } catch (Exception e) {
                e.printStackTrace();
                Toolket.setCellValue(workbook, sheet, index, 3, null, fontSize10, HSSFCellStyle.ALIGN_CENTER,
                        true, null);
                Toolket.setCellValue(workbook, sheet, index, 4, null, fontSize10, HSSFCellStyle.ALIGN_LEFT,
                        true, null);
            }

        } else {
            code961s = (List<LicenseCode961>) am
                    .findLicenseCode961sBy(new LicenseCode961(String.valueOf(skill.getLicenseCode())));
            if (!code961s.isEmpty()) {
                code961 = code961s.get(0);
                Toolket.setCellValue(workbook, sheet, index, 1, code961.getName(), fontSize10,
                        HSSFCellStyle.ALIGN_LEFT, true, null);
                Toolket.setCellValue(workbook, sheet, index, 2, code961.getLevel(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 3, code961.getType().toString(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 4, code961.getDeptName(), fontSize10,
                        HSSFCellStyle.ALIGN_LEFT, true, null);
            }
        }

        index++;
    }

    sheet.addMergedRegion(new CellRangeAddress(index, index, 0, 4));
    Toolket.setCellValue(workbook, sheet, index, 0, "?", fontSize12, HSSFCellStyle.ALIGN_CENTER, false,
            35.0F, null);
    Toolket.setCellValue(workbook, sheet, index, 5, String.valueOf(totals), fontSize12,
            HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null);

    File tempDir = new File(
            context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno()
                    + (new SimpleDateFormat("yyyyMMdd").format(new Date()))));
    if (!tempDir.exists())
        tempDir.mkdirs();

    File output = new File(tempDir, "DeptStdSkillList1.xls");
    FileOutputStream fos = new FileOutputStream(output);
    workbook.write(fos);
    fos.close();

    JasperReportUtils.printXlsToFrontEnd(response, output);
    output.delete();
    tempDir.delete();
}

From source file:tw.edu.chit.struts.action.deptassist.ReportPrintAction.java

/**
 * /* w w w .  j ava  2  s.  c o  m*/
 * 
 * @param mapping org.apache.struts.action.ActionMapping object
 * @param form org.apache.struts.action.ActionForm object
 * @param request request javax.servlet.http.HttpServletRequest object
 * @param response response javax.servlet.http.HttpServletResponse object
 * @param sterm 
 */
@SuppressWarnings("unchecked")
private void printDeptStdSkillList3(ActionMapping mapping, DynaActionForm form, HttpServletRequest request,
        HttpServletResponse response, String sterm) throws Exception {

    HttpSession session = request.getSession(false);
    AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME);
    MemberManager mm = (MemberManager) getBean(MEMBER_MANAGER_BEAN_NAME);

    Member member = (Member) getUserCredential(session).getMember();
    Empl empl = mm.findEmplByOid(member.getOid());
    ServletContext context = request.getSession().getServletContext();

    CodeEmpl codeEmpl = new CodeEmpl();
    codeEmpl.setIdno(empl.getUnit());
    Example example4CodeEmpl = Example.create(codeEmpl).ignoreCase().enableLike(MatchMode.START);
    List<CodeEmpl> codeEmpls = (List<CodeEmpl>) am.findSQLWithCriteria(CodeEmpl.class, example4CodeEmpl, null,
            null);
    Calendar cal = Calendar.getInstance();
    Calendar cal1 = (Calendar) cal.clone();
    Calendar cal2 = (Calendar) cal.clone();
    if (StringUtils.isNotBlank(form.getString("licenseValidDateStart"))
            || StringUtils.isNotBlank(form.getString("licenseValidDateEnd"))) {
        Date from = StringUtils.isBlank(form.getString("licenseValidDateStart")) ? null
                : Toolket.parseNativeDate(form.getString("licenseValidDateStart"));
        // ???
        Date to = StringUtils.isBlank(form.getString("licenseValidDateEnd")) ? Calendar.getInstance().getTime()
                : Toolket.parseNativeDate(form.getString("licenseValidDateEnd"));

        cal1.setTime(from);
        cal1.set(Calendar.HOUR_OF_DAY, 0);
        cal1.set(Calendar.MINUTE, 0);
        cal1.set(Calendar.SECOND, 0);
        cal1.set(Calendar.MILLISECOND, 0);

        cal2.setTime(to);
        cal2.set(Calendar.HOUR_OF_DAY, 23);
        cal2.set(Calendar.MINUTE, 59);
        cal2.set(Calendar.SECOND, 59);
        cal2.set(Calendar.MILLISECOND, 999);
    }

    String hql = "SELECT COUNT(*) CT, ss.licenseValidDate LVD, ss.licenseCode LC, "
            + "s.depart_class DC1, s.sex S1, gs.depart_class DC2, gs.sex S2 "
            + "FROM StdSkill ss LEFT JOIN stmd s ON ss.studentNo = s.student_no "
            + "LEFT JOIN Gstmd gs ON ss.studentNo = gs.student_no "
            + "WHERE ss.deptNo = ? AND ss.licenseValidDate >= ? AND ss.licenseValidDate <= ? "
            + "GROUP BY ss.licenseValidDate, s.depart_class, s.sex, " + "gs.depart_class, gs.sex "
            + "ORDER BY ss.licenseValidDate, s.depart_class, " + "s.sex, gs.depart_class, gs.sex";

    List<Map> ret = (List<Map>) am.findBySQL(hql, new Object[] {
            //codeEmpls.get(0).getIdno().trim(), cal1.getTime(),cal2.getTime() });   
            empl.getUnit(), cal1.getTime(), cal2.getTime() });

    DateFormat df = new SimpleDateFormat("yyyy/MM/dd");

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("");
    sheet.setColumnWidth(0, 1800);
    sheet.setColumnWidth(1, 2000);
    sheet.setColumnWidth(2, 2000);
    sheet.setColumnWidth(3, 3000);
    sheet.setColumnWidth(4, 5000);
    sheet.setColumnWidth(5, 3000);
    sheet.setColumnWidth(6, 8000);
    sheet.setColumnWidth(7, 3000);
    sheet.setColumnWidth(8, 4000);
    sheet.setColumnWidth(9, 2200);
    sheet.setColumnWidth(10, 2200);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));

    HSSFFont fontSize12 = workbook.createFont();
    fontSize12.setFontHeightInPoints((short) 12);
    fontSize12.setFontName("Arial Unicode MS");

    HSSFFont fontSize10 = workbook.createFont();
    fontSize10.setFontHeightInPoints((short) 10);
    fontSize10.setFontName("Arial Unicode MS");

    // Header
    Toolket.setCellValue(workbook, sheet, 0, 0,
            "?" + Toolket.getEmpUnit(empl.getUnit()) + "  ("
                    + df.format(cal1.getTime()) + "~" + df.format(cal2.getTime()) + ")",
            fontSize12, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null);

    // Column Header
    Toolket.setCellValue(workbook, sheet, 1, 0, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    Toolket.setCellValue(workbook, sheet, 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 2, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    Toolket.setCellValue(workbook, sheet, 1, 4, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    Toolket.setCellValue(workbook, sheet, 1, 5, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 6, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 7, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    Toolket.setCellValue(workbook, sheet, 1, 8, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 9, "()", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 10, "()", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    // Toolket.setCellValue(workbook, sheet, 1, 11, "?", fontSize10,
    // HSSFCellStyle.ALIGN_CENTER, true, null);

    int index = 2;
    boolean isGraduate = false;
    String departClass = null;
    List<LicenseCode> codes = null;
    List<LicenseCode961> code961s = null;
    List<DeptCode4Yun> yuns = null;
    LicenseCode code = null;
    LicenseCode961 code961 = null;
    DeptCode4Yun yun = null;
    Example example = null;

    for (Map data : ret) {

        Toolket.setCellValue(workbook, sheet, index, 0, String.valueOf(index - 1), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);

        if (data.get("DC1") != null) {
            departClass = (String) data.get("DC1");
            isGraduate = false;
        } else {
            departClass = (String) data.get("DC2");
            isGraduate = true;
        }

        yun = new DeptCode4Yun();
        yun.setClassNo(StringUtils.substring(departClass, 0, 4));
        example = Example.create(yun).ignoreCase().enableLike(MatchMode.START);
        yuns = (List<DeptCode4Yun>) am.findSQLWithCriteria(DeptCode4Yun.class, example, null, null);
        if (!yuns.isEmpty() && yuns.size() == 1) {
            yun = yuns.get(0);

            Toolket.setCellValue(workbook, sheet, index, 1, yun.getDeptCode(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index, 2, yun.getCampusCode(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index, 3, yun.getCampusName(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
        }

        Toolket.setCellValue(workbook, sheet, index, 4, Toolket.getClassFullName(departClass), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);
        Toolket.setCellValue(workbook, sheet, index, 5, (String) data.get("LC"), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);

        codes = (List<LicenseCode>) am
                .findLicenseCodesBy(new LicenseCode(String.valueOf((String) data.get("LC"))));

        if (!codes.isEmpty()) {
            code = codes.get(0);
            Toolket.setCellValue(workbook, sheet, index, 6, code.getName(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index, 7, code.getLevel(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
        } else {
            code961s = (List<LicenseCode961>) am
                    .findLicenseCode961sBy(new LicenseCode961(String.valueOf((String) data.get("LC"))));
            if (!code961s.isEmpty()) {
                code961 = code961s.get(0);
                Toolket.setCellValue(workbook, sheet, index, 6, code961.getName(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 7, code961.getLevel(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
            }
        }

        Toolket.setCellValue(workbook, sheet, index, 8, df.format((Date) data.get("LVD")), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);

        if (!isGraduate) {
            if ("1".equals((String) data.get("S1"))) {
                Toolket.setCellValue(workbook, sheet, index, 9, ((Long) data.get("CT")).toString(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 10, "", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                        true, null);
            } else if ("2".equals((String) data.get("S1"))) {
                Toolket.setCellValue(workbook, sheet, index, 9, "", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                        true, null);
                Toolket.setCellValue(workbook, sheet, index, 10, ((Long) data.get("CT")).toString(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
            }
        } else {
            if ("1".equals((String) data.get("S2"))) {
                Toolket.setCellValue(workbook, sheet, index, 9, ((Long) data.get("CT")).toString(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 10, "", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                        true, null);
            } else if ("2".equals((String) data.get("S2"))) {
                Toolket.setCellValue(workbook, sheet, index, 9, "", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                        true, null);
                Toolket.setCellValue(workbook, sheet, index, 10, ((Long) data.get("CT")).toString(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
            }
        }

        index++;
        // Toolket.setCellValue(workbook, sheet, index++, 11, ((Long) m
        // .get("CT")).toString(), fontSize10,
        // HSSFCellStyle.ALIGN_CENTER, true, null);
    }

    File tempDir = new File(
            context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno()
                    + (new SimpleDateFormat("yyyyMMdd").format(new Date()))));
    if (!tempDir.exists())
        tempDir.mkdirs();

    File output = new File(tempDir, "DeptStdSkillList3.xls");
    FileOutputStream fos = new FileOutputStream(output);
    workbook.write(fos);
    fos.close();

    JasperReportUtils.printXlsToFrontEnd(response, output);
    output.delete();
    tempDir.delete();
}

From source file:tw.edu.chit.struts.action.deptassist.ReportPrintAction.java

/**
 * ???// w ww .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 printDeptStdSkillList4(ActionMapping mapping, DynaActionForm form, HttpServletRequest request,
        HttpServletResponse response, String sterm) throws Exception {

    HttpSession session = request.getSession(false);
    AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME);
    MemberManager mm = (MemberManager) getBean(MEMBER_MANAGER_BEAN_NAME);

    Member member = (Member) getUserCredential(session).getMember();
    Empl empl = mm.findEmplByOid(member.getOid());
    ServletContext context = request.getSession().getServletContext();

    CodeEmpl codeEmpl = new CodeEmpl();
    codeEmpl.setIdno(empl.getUnit());
    Example example4CodeEmpl = Example.create(codeEmpl).ignoreCase().enableLike(MatchMode.START);
    List<CodeEmpl> codeEmpls = (List<CodeEmpl>) am.findSQLWithCriteria(CodeEmpl.class, example4CodeEmpl, null,
            null);

    DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
    Calendar cal = Calendar.getInstance();
    Calendar cal1 = (Calendar) cal.clone();
    Calendar cal2 = (Calendar) cal.clone();
    if (StringUtils.isNotBlank(form.getString("licenseValidDateStart"))
            || StringUtils.isNotBlank(form.getString("licenseValidDateEnd"))) {
        Date from = StringUtils.isBlank(form.getString("licenseValidDateStart")) ? null
                : Toolket.parseNativeDate(form.getString("licenseValidDateStart"));
        // ???
        Date to = StringUtils.isBlank(form.getString("licenseValidDateEnd")) ? Calendar.getInstance().getTime()
                : Toolket.parseNativeDate(form.getString("licenseValidDateEnd"));

        cal1.setTime(from);
        cal1.set(Calendar.HOUR_OF_DAY, 0);
        cal1.set(Calendar.MINUTE, 0);
        cal1.set(Calendar.SECOND, 0);
        cal1.set(Calendar.MILLISECOND, 0);

        cal2.setTime(to);
        cal2.set(Calendar.HOUR_OF_DAY, 23);
        cal2.set(Calendar.MINUTE, 59);
        cal2.set(Calendar.SECOND, 59);
        cal2.set(Calendar.MILLISECOND, 999);
    }

    // Y
    boolean flag = form.getString("amountDateType").equalsIgnoreCase("y");
    String hql = "";
    if (flag)
        hql = "SELECT COUNT(*), SUM(s.amount), s FROM StdSkill s "
                + "WHERE s.amountDate IS NOT NULL AND s.deptNo = ? "
                + "AND s.amountDate IS NOT NULL AND s.licenseValidDate BETWEEN ? AND ? "
                + "GROUP BY s.licenseCode";
    else
        // ?
        hql = "SELECT COUNT(*), SUM(s.amount), s FROM StdSkill s "
                + "WHERE s.amountDate IS NULL AND s.deptNo = ? "
                + "AND s.amountDate IS NULL AND s.licenseValidDate BETWEEN ? AND ? " + "GROUP BY s.licenseCode";

    List<Object> ret = (List<Object>) am.find(hql,
            new Object[] { codeEmpls.get(0).getIdno2().trim(), cal1.getTime(), cal2.getTime() });

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("???");
    sheet.setColumnWidth(0, 1500);
    sheet.setColumnWidth(1, 3000);
    sheet.setColumnWidth(2, 10000);
    sheet.setColumnWidth(3, 3000);
    sheet.setColumnWidth(4, 5000);
    sheet.setColumnWidth(5, 2400);
    sheet.setColumnWidth(6, 8000);
    sheet.setColumnWidth(7, 1800);
    sheet.setColumnWidth(8, 2200);
    sheet.setColumnWidth(9, 2400);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));

    HSSFFont fontSize12 = workbook.createFont();
    fontSize12.setFontHeightInPoints((short) 12);
    fontSize12.setFontName("Arial Unicode MS");

    HSSFFont fontSize10 = workbook.createFont();
    fontSize10.setFontHeightInPoints((short) 10);
    fontSize10.setFontName("Arial Unicode MS");

    // Header
    Toolket.setCellValue(workbook, sheet, 0, 0,
            "?" + Toolket.getEmpUnit(empl.getUnit())
                    + "??? (" + df.format(cal1.getTime()) + "~"
                    + df.format(cal2.getTime()) + ") - " + (flag ? "" : ""),
            fontSize12, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null);

    // Column Header
    Toolket.setCellValue(workbook, sheet, 1, 0, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    Toolket.setCellValue(workbook, sheet, 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 2, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 3, "/", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 4, "/", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 5, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 6, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 7, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    Toolket.setCellValue(workbook, sheet, 1, 8, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 9, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);

    int index = 2, totals = 0, sum = 0;
    StdSkill skill = null;
    List<LicenseCode> codes = null;
    List<LicenseCode961> code961s = null;
    LicenseCode code = null;
    LicenseCode961 code961 = null;
    Object[] data = null;

    for (Object o : ret) {

        data = (Object[]) o;
        totals += (Integer) data[0];
        sum += (Integer) data[1];
        skill = (StdSkill) data[2];

        Toolket.setCellValue(workbook, sheet, index, 0, String.valueOf(index - 1), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);
        Toolket.setCellValue(workbook, sheet, index, 7, ((Integer) data[0]).toString(), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);
        Toolket.setCellValue(workbook, sheet, index, 8, skill.getAmount().toString(), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);
        Toolket.setCellValue(workbook, sheet, index, 9, ((Integer) data[1]).toString(), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);

        codes = (List<LicenseCode>) am
                .findLicenseCodesBy(new LicenseCode(String.valueOf(skill.getLicenseCode())));

        if (!codes.isEmpty()) {
            code = codes.get(0);
            Toolket.setCellValue(workbook, sheet, index, 1, code.getCode(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index, 2, code.getName(), fontSize10,
                    HSSFCellStyle.ALIGN_LEFT, true, null);
            Toolket.setCellValue(workbook, sheet, index, 3, code.getLocale().toString(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index, 4, code.getLevel(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index, 5, code.getType().toString(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index, 6, code.getDeptName(), fontSize10,
                    HSSFCellStyle.ALIGN_LEFT, true, null);
        } else {
            code961s = (List<LicenseCode961>) am
                    .findLicenseCode961sBy(new LicenseCode961(String.valueOf(skill.getLicenseCode())));
            if (!code961s.isEmpty()) {
                code961 = code961s.get(0);
                Toolket.setCellValue(workbook, sheet, index, 1, code961.getCode().toString(), fontSize10,
                        HSSFCellStyle.ALIGN_LEFT, true, null);
                Toolket.setCellValue(workbook, sheet, index, 2, code961.getName(), fontSize10,
                        HSSFCellStyle.ALIGN_LEFT, true, null);
                Toolket.setCellValue(workbook, sheet, index, 3, code961.getLocale().toString(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 4, code961.getLevel(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 5, code961.getType().toString(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 6, code961.getDeptName(), fontSize10,
                        HSSFCellStyle.ALIGN_LEFT, true, null);
            }
        }

        index++;
    }

    Toolket.setCellValue(workbook, sheet, index, 6, "?", fontSize12, HSSFCellStyle.ALIGN_CENTER, false,
            35.0F, null);
    Toolket.setCellValue(workbook, sheet, index, 7, String.valueOf(totals), fontSize12,
            HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null);
    Toolket.setCellValue(workbook, sheet, index, 9, String.valueOf(sum), fontSize12, HSSFCellStyle.ALIGN_CENTER,
            false, 35.0F, null);

    File tempDir = new File(
            context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno()
                    + (new SimpleDateFormat("yyyyMMdd").format(new Date()))));
    if (!tempDir.exists())
        tempDir.mkdirs();

    File output = new File(tempDir, "DeptStdSkillList4.xls");
    FileOutputStream fos = new FileOutputStream(output);
    workbook.write(fos);
    fos.close();

    JasperReportUtils.printXlsToFrontEnd(response, output);
    output.delete();
    tempDir.delete();
}

From source file:tw.edu.chit.struts.action.deptassist.ReportPrintAction.java

/**
 * ?/*w  w  w .  j  a  v  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 
 */
@SuppressWarnings("unchecked")
private void printDeptStdSkillList5(ActionMapping mapping, DynaActionForm form, HttpServletRequest request,
        HttpServletResponse response, String sterm) throws Exception {

    HttpSession session = request.getSession(false);
    AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME);
    MemberManager mm = (MemberManager) getBean(MEMBER_MANAGER_BEAN_NAME);

    Member member = (Member) getUserCredential(session).getMember();
    Empl empl = mm.findEmplByOid(member.getOid());
    ServletContext context = request.getSession().getServletContext();

    CodeEmpl codeEmpl = new CodeEmpl();
    codeEmpl.setIdno(empl.getUnit());
    Example example4CodeEmpl = Example.create(codeEmpl).ignoreCase().enableLike(MatchMode.START);
    List<CodeEmpl> codeEmpls = (List<CodeEmpl>) am.findSQLWithCriteria(CodeEmpl.class, example4CodeEmpl, null,
            null);

    DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
    Calendar cal = Calendar.getInstance();
    Calendar cal1 = (Calendar) cal.clone();
    Calendar cal2 = (Calendar) cal.clone();
    if (StringUtils.isNotBlank(form.getString("licenseValidDateStart"))
            || StringUtils.isNotBlank(form.getString("licenseValidDateEnd"))) {
        Date from = StringUtils.isBlank(form.getString("licenseValidDateStart")) ? null
                : Toolket.parseNativeDate(form.getString("licenseValidDateStart"));
        // ???
        Date to = StringUtils.isBlank(form.getString("licenseValidDateEnd")) ? Calendar.getInstance().getTime()
                : Toolket.parseNativeDate(form.getString("licenseValidDateEnd"));

        cal1.setTime(from);
        cal1.set(Calendar.HOUR_OF_DAY, 0);
        cal1.set(Calendar.MINUTE, 0);
        cal1.set(Calendar.SECOND, 0);
        cal1.set(Calendar.MILLISECOND, 0);

        cal2.setTime(to);
        cal2.set(Calendar.HOUR_OF_DAY, 23);
        cal2.set(Calendar.MINUTE, 59);
        cal2.set(Calendar.SECOND, 59);
        cal2.set(Calendar.MILLISECOND, 999);
    }

    String hql = "SELECT COUNT(*), s.techIdno, s.licenseCode FROM StdSkill s "
            + "WHERE s.deptNo = ? AND s.techIdno IS NOT NULL " + "AND s.licenseValidDate BETWEEN ? AND ? "
            + "GROUP BY s.techIdno, s.licenseCode";

    List<Object> ret = (List<Object>) am.find(hql,
            new Object[] { codeEmpls.get(0).getIdno2().trim(), cal1.getTime(), cal2.getTime() });

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("?");
    sheet.setColumnWidth(0, 4000);
    sheet.setColumnWidth(1, 6000);
    sheet.setColumnWidth(2, 4000);
    sheet.setColumnWidth(3, 3000);
    sheet.setColumnWidth(4, 4000);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));

    HSSFFont fontSize12 = workbook.createFont();
    fontSize12.setFontHeightInPoints((short) 12);
    fontSize12.setFontName("Arial Unicode MS");

    HSSFFont fontSize10 = workbook.createFont();
    fontSize10.setFontHeightInPoints((short) 10);
    fontSize10.setFontName("Arial Unicode MS");

    // Header
    Toolket.setCellValue(workbook, sheet, 0, 0,
            "?" + Toolket.getEmpUnit(empl.getUnit())
                    + "? (" + df.format(cal1.getTime()) + "~"
                    + df.format(cal2.getTime()) + ")",
            fontSize12, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null);

    // Column Header
    Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER,
            true, null);
    Toolket.setCellValue(workbook, sheet, 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 2, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    Toolket.setCellValue(workbook, sheet, 1, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 4, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);

    int index = 2;
    Object[] data = null;
    DEmpl dempl = null;

    for (Object o : ret) {

        data = (Object[]) o;

        sheet.addMergedRegion(new CellRangeAddress(index, index + 2, 0, 0));
        empl = mm.findEmplByIdno((String) data[1]);
        if (empl == null) {
            dempl = mm.findDEmplByIdno((String) data[1]);
            //Toolket.setCellValue(workbook, sheet, index, 0, dempl
            //      .getCname(), fontSize10, HSSFCellStyle.ALIGN_CENTER,
            //      true, null);
        } else {
            Toolket.setCellValue(workbook, sheet, index, 0, empl.getCname(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
        }

        Toolket.setCellValue(workbook, sheet, index + 1, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);
        Toolket.setCellValue(workbook, sheet, index + 2, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);

        Toolket.setCellValue(workbook, sheet, index, 1, "", fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);
        Toolket.setCellValue(workbook, sheet, index + 1, 1, "", fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);
        Toolket.setCellValue(workbook, sheet, index + 2, 1, "", fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);

        Toolket.setCellValue(workbook, sheet, index, 2, "10", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);
        Toolket.setCellValue(workbook, sheet, index + 1, 2, "5", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                true, null);
        Toolket.setCellValue(workbook, sheet, index + 2, 2, "2", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                true, null);

        Toolket.setCellValue(workbook, sheet, index, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
        Toolket.setCellValue(workbook, sheet, index + 1, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);

        //((Integer) data[0]).toString() ?? "" ??
        Toolket.setCellValue(workbook, sheet, index + 2, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);
        /*???
        Toolket.setCellValue(workbook, sheet, index + 2, 3,
              ((Integer) data[0]).toString(), fontSize10,
              HSSFCellStyle.ALIGN_CENTER, true, null);
        */

        Toolket.setCellValue(workbook, sheet, index, 4, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
        Toolket.setCellValue(workbook, sheet, index + 1, 4, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);
        Toolket.setCellValue(workbook, sheet, index + 2, 4, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);

        index += 3;
    }

    index++;
    sheet.addMergedRegion(new CellRangeAddress(index, index, 0, 1));
    Toolket.setCellValue(workbook, sheet, index, 0, " : ?20", fontSize10,
            HSSFCellStyle.ALIGN_LEFT, false, null);
    File tempDir = new File(
            context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno()
                    + (new SimpleDateFormat("yyyyMMdd").format(new Date()))));
    if (!tempDir.exists())
        tempDir.mkdirs();

    File output = new File(tempDir, "DeptStdSkillList5.xls");
    FileOutputStream fos = new FileOutputStream(output);
    workbook.write(fos);
    fos.close();

    JasperReportUtils.printXlsToFrontEnd(response, output);
    output.delete();
    tempDir.delete();
}

From source file:tw.edu.chit.struts.action.deptassist.ReportPrintAction.java

/**
 * ?//  w  w  w .ja  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 
 */
@SuppressWarnings("unchecked")
private void printDeptStdSkillList6(ActionMapping mapping, DynaActionForm form, HttpServletRequest request,
        HttpServletResponse response, String sterm) throws Exception {

    HttpSession session = request.getSession(false);
    AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME);
    MemberManager mm = (MemberManager) getBean(MEMBER_MANAGER_BEAN_NAME);

    Member member = (Member) getUserCredential(session).getMember();
    Empl empl = mm.findEmplByOid(member.getOid());
    ServletContext context = request.getSession().getServletContext();

    CodeEmpl codeEmpl = new CodeEmpl();
    codeEmpl.setIdno(empl.getUnit());
    Example example4CodeEmpl = Example.create(codeEmpl).ignoreCase().enableLike(MatchMode.START);
    List<CodeEmpl> codeEmpls = (List<CodeEmpl>) am.findSQLWithCriteria(CodeEmpl.class, example4CodeEmpl, null,
            null);

    DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
    Calendar cal = Calendar.getInstance();
    Calendar cal1 = (Calendar) cal.clone();
    Calendar cal2 = (Calendar) cal.clone();
    if (StringUtils.isNotBlank(form.getString("licenseValidDateStart"))
            || StringUtils.isNotBlank(form.getString("licenseValidDateEnd"))) {
        Date from = StringUtils.isBlank(form.getString("licenseValidDateStart")) ? null
                : Toolket.parseNativeDate(form.getString("licenseValidDateStart"));
        // ???
        Date to = StringUtils.isBlank(form.getString("licenseValidDateEnd")) ? Calendar.getInstance().getTime()
                : Toolket.parseNativeDate(form.getString("licenseValidDateEnd"));

        cal1.setTime(from);
        cal1.set(Calendar.HOUR_OF_DAY, 0);
        cal1.set(Calendar.MINUTE, 0);
        cal1.set(Calendar.SECOND, 0);
        cal1.set(Calendar.MILLISECOND, 0);

        cal2.setTime(to);
        cal2.set(Calendar.HOUR_OF_DAY, 23);
        cal2.set(Calendar.MINUTE, 59);
        cal2.set(Calendar.SECOND, 59);
        cal2.set(Calendar.MILLISECOND, 999);
    }

    DeptCode4Yun yun = new DeptCode4Yun();
    yun.setClassNo("___" + codeEmpls.get(0).getIdno2().trim());
    Example example = Example.create(yun).ignoreCase().enableLike(MatchMode.START);
    List<Order> orders = new LinkedList<Order>();
    orders.add(Order.asc("classNo"));
    List<DeptCode4Yun> yuns = (List<DeptCode4Yun>) am.findSQLWithCriteria(DeptCode4Yun.class, example, null,
            orders);

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("?");
    sheet.setColumnWidth(0, 2000);
    sheet.setColumnWidth(1, 6000);
    sheet.setColumnWidth(2, 4000);
    sheet.setColumnWidth(3, 3000);
    sheet.setColumnWidth(4, 6000);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));

    HSSFFont fontSize12 = workbook.createFont();
    fontSize12.setFontHeightInPoints((short) 12);
    fontSize12.setFontName("Arial Unicode MS");

    HSSFFont fontSize10 = workbook.createFont();
    fontSize10.setFontHeightInPoints((short) 10);
    fontSize10.setFontName("Arial Unicode MS");

    // Header
    Toolket.setCellValue(workbook, sheet, 0, 0,
            "?" + Toolket.getEmpUnit(empl.getUnit())
                    + "? (" + df.format(cal1.getTime()) + "~"
                    + df.format(cal2.getTime()) + ")",
            fontSize12, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null);

    // Column Header
    Toolket.setCellValue(workbook, sheet, 1, 0, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    Toolket.setCellValue(workbook, sheet, 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    Toolket.setCellValue(workbook, sheet, 1, 2, "????", fontSize10, HSSFCellStyle.ALIGN_CENTER,
            true, null);
    Toolket.setCellValue(workbook, sheet, 1, 3, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 4, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);

    int index = 2;
    String sql = "SELECT s.student_no ST1, gs.student_no ST2 "
            + "FROM StdSkill ss LEFT JOIN stmd s ON ss.studentNo = s.student_no "
            + "LEFT JOIN Gstmd gs ON ss.studentNo = gs.student_no "
            + "WHERE ss.deptNo = ? AND ss.licenseValidDate BETWEEN ? AND ? "
            + "AND (s.depart_class LIKE ? OR gs.depart_class LIKE ?)";
    String classNo = null;
    List<Map> ret = null;
    Set<String> set = new HashSet<String>();

    for (DeptCode4Yun y : yuns) {
        classNo = y.getClassNo() + "%";
        ret = (List<Map>) am.findBySQL(sql, new Object[] { codeEmpls.get(0).getIdno2().trim(), cal1.getTime(),
                cal2.getTime(), classNo, classNo });

        set = new HashSet<String>();
        if (!ret.isEmpty()) {
            for (Map o : ret) {
                if (o.get("ST1") != null)
                    set.add((String) o.get("ST1"));
                else if (o.get("ST2") != null)
                    set.add((String) o.get("ST2"));
            }

            if (!set.isEmpty()) {
                Toolket.setCellValue(workbook, sheet, index, 0, String.valueOf(index - 1), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 1, y.getCampusName(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 2, y.getDeptName(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 3, String.valueOf(set.size()), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index++, 4, "", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                        true, null);
            }
        }
    }

    index++;
    Toolket.setCellValue(workbook, sheet, index, 4, "", fontSize10,
            HSSFCellStyle.ALIGN_RIGHT, false, null);

    File tempDir = new File(
            context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno()
                    + (new SimpleDateFormat("yyyyMMdd").format(new Date()))));
    if (!tempDir.exists())
        tempDir.mkdirs();

    File output = new File(tempDir, "DeptStdSkillList6.xls");
    FileOutputStream fos = new FileOutputStream(output);
    workbook.write(fos);
    fos.close();

    JasperReportUtils.printXlsToFrontEnd(response, output);
    output.delete();
    tempDir.delete();
}

From source file:tw.edu.chit.struts.action.language.ReportPrintAction.java

/**
 * /*  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 printDeptStdSkillList1(ActionMapping mapping, DynaActionForm form, HttpServletRequest request,
        HttpServletResponse response, String sterm) throws Exception {

    HttpSession session = request.getSession(false);
    AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME);
    MemberManager mm = (MemberManager) getBean(MEMBER_MANAGER_BEAN_NAME);

    Member member = (Member) getUserCredential(session).getMember();
    Empl empl = mm.findEmplByOid(member.getOid());
    ServletContext context = request.getSession().getServletContext();

    DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
    Calendar cal = Calendar.getInstance();
    Calendar cal1 = (Calendar) cal.clone();
    Calendar cal2 = (Calendar) cal.clone();
    if (StringUtils.isNotBlank(form.getString("licenseValidDateStart"))
            || StringUtils.isNotBlank(form.getString("licenseValidDateEnd"))) {
        Date from = StringUtils.isBlank(form.getString("licenseValidDateStart")) ? null
                : Toolket.parseNativeDate(form.getString("licenseValidDateStart"));
        // ???
        Date to = StringUtils.isBlank(form.getString("licenseValidDateEnd")) ? Calendar.getInstance().getTime()
                : Toolket.parseNativeDate(form.getString("licenseValidDateEnd"));

        cal1.setTime(from);
        cal1.set(Calendar.HOUR_OF_DAY, 0);
        cal1.set(Calendar.MINUTE, 0);
        cal1.set(Calendar.SECOND, 0);
        cal1.set(Calendar.MILLISECOND, 0);

        cal2.setTime(to);
        cal2.set(Calendar.HOUR_OF_DAY, 23);
        cal2.set(Calendar.MINUTE, 59);
        cal2.set(Calendar.SECOND, 59);
        cal2.set(Calendar.MILLISECOND, 999);
    }

    String hql = "SELECT count(*), s FROM StdSkill s WHERE s.deptNo = ? "
            + "AND s.licenseValidDate BETWEEN ? AND ? " + "GROUP BY s.licenseCode";

    List<Object> ret = (List<Object>) am.find(hql, new Object[] { "0", cal1.getTime(), cal2.getTime() }); // 

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("");
    sheet.setColumnWidth(0, 1500);
    sheet.setColumnWidth(1, 10000);
    sheet.setColumnWidth(2, 3000);
    sheet.setColumnWidth(3, 3000);
    sheet.setColumnWidth(4, 8000);
    sheet.setColumnWidth(5, 1800);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));

    HSSFFont fontSize12 = workbook.createFont();
    fontSize12.setFontHeightInPoints((short) 12);
    fontSize12.setFontName("Arial Unicode MS");

    HSSFFont fontSize10 = workbook.createFont();
    fontSize10.setFontHeightInPoints((short) 10);
    fontSize10.setFontName("Arial Unicode MS");

    // Header
    Toolket.setCellValue(workbook, sheet, 0, 0,
            "?" + Toolket.getEmpUnit(empl.getUnit()) + "  ("
                    + df.format(cal1.getTime()) + "~" + df.format(cal2.getTime()) + ")",
            fontSize12, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null);

    // Column Header
    Toolket.setCellValue(workbook, sheet, 1, 0, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    Toolket.setCellValue(workbook, sheet, 1, 1, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 2, "/", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 4, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 5, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    int index = 2, totals = 0;
    StdSkill skill = null;
    List<LicenseCode> codes = null;
    List<LicenseCode961> code961s = null;
    LicenseCode code = null;
    LicenseCode961 code961 = null;
    Object[] data = null;

    for (Object o : ret) {

        data = (Object[]) o;
        totals += (Integer) data[0];
        skill = (StdSkill) data[1];

        Toolket.setCellValue(workbook, sheet, index, 0, String.valueOf(index - 1), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);
        Toolket.setCellValue(workbook, sheet, index, 5, ((Integer) data[0]).toString(), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);

        codes = (List<LicenseCode>) am
                .findLicenseCodesBy(new LicenseCode(Integer.valueOf(skill.getLicenseCode())));

        if (!codes.isEmpty()) {
            code = codes.get(0);
            Toolket.setCellValue(workbook, sheet, index, 1, code.getName(), fontSize10,
                    HSSFCellStyle.ALIGN_LEFT, true, null);
            Toolket.setCellValue(workbook, sheet, index, 2, code.getLevel(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index, 3, code.getType().toString(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index, 4, code.getDeptName(), fontSize10,
                    HSSFCellStyle.ALIGN_LEFT, true, null);
        } else {
            code961s = (List<LicenseCode961>) am
                    .findLicenseCode961sBy(new LicenseCode961(Integer.valueOf(skill.getLicenseCode())));
            if (!code961s.isEmpty()) {
                code961 = code961s.get(0);
                Toolket.setCellValue(workbook, sheet, index, 1, code961.getName(), fontSize10,
                        HSSFCellStyle.ALIGN_LEFT, true, null);
                Toolket.setCellValue(workbook, sheet, index, 2, code961.getLevel(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 3, code961.getType().toString(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 4, code961.getDeptName(), fontSize10,
                        HSSFCellStyle.ALIGN_LEFT, true, null);
            }
        }

        index++;
    }

    sheet.addMergedRegion(new CellRangeAddress(index, index, 0, 4));
    Toolket.setCellValue(workbook, sheet, index, 0, "?", fontSize12, HSSFCellStyle.ALIGN_CENTER, false,
            35.0F, null);
    Toolket.setCellValue(workbook, sheet, index, 5, String.valueOf(totals), fontSize12,
            HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null);

    File tempDir = new File(
            context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno()
                    + (new SimpleDateFormat("yyyyMMdd").format(new Date()))));
    if (!tempDir.exists())
        tempDir.mkdirs();

    File output = new File(tempDir, "DeptStdSkillList1.xls");
    FileOutputStream fos = new FileOutputStream(output);
    workbook.write(fos);
    fos.close();

    JasperReportUtils.printXlsToFrontEnd(response, output);
    output.delete();
    tempDir.delete();
}

From source file:tw.edu.chit.struts.action.language.ReportPrintAction.java

/**
 * //from  w  w  w  .  java  2s.co m
 * 
 * @param mapping org.apache.struts.action.ActionMapping object
 * @param form org.apache.struts.action.ActionForm object
 * @param request request javax.servlet.http.HttpServletRequest object
 * @param response response javax.servlet.http.HttpServletResponse object
 * @param sterm 
 */
@SuppressWarnings("unchecked")
private void printDeptStdSkillList3(ActionMapping mapping, DynaActionForm form, HttpServletRequest request,
        HttpServletResponse response, String sterm) throws Exception {

    HttpSession session = request.getSession(false);
    AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME);
    MemberManager mm = (MemberManager) getBean(MEMBER_MANAGER_BEAN_NAME);

    Member member = (Member) getUserCredential(session).getMember();
    Empl empl = mm.findEmplByOid(member.getOid());
    ServletContext context = request.getSession().getServletContext();

    Calendar cal = Calendar.getInstance();
    Calendar cal1 = (Calendar) cal.clone();
    Calendar cal2 = (Calendar) cal.clone();
    if (StringUtils.isNotBlank(form.getString("licenseValidDateStart"))
            || StringUtils.isNotBlank(form.getString("licenseValidDateEnd"))) {
        Date from = StringUtils.isBlank(form.getString("licenseValidDateStart")) ? null
                : Toolket.parseNativeDate(form.getString("licenseValidDateStart"));
        // ???
        Date to = StringUtils.isBlank(form.getString("licenseValidDateEnd")) ? Calendar.getInstance().getTime()
                : Toolket.parseNativeDate(form.getString("licenseValidDateEnd"));

        cal1.setTime(from);
        cal1.set(Calendar.HOUR_OF_DAY, 0);
        cal1.set(Calendar.MINUTE, 0);
        cal1.set(Calendar.SECOND, 0);
        cal1.set(Calendar.MILLISECOND, 0);

        cal2.setTime(to);
        cal2.set(Calendar.HOUR_OF_DAY, 23);
        cal2.set(Calendar.MINUTE, 59);
        cal2.set(Calendar.SECOND, 59);
        cal2.set(Calendar.MILLISECOND, 999);
    }

    String hql = "SELECT COUNT(*) CT, ss.licenseValidDate LVD, ss.licenseCode LC, "
            + "s.depart_class DC1, s.sex S1, gs.depart_class DC2, gs.sex S2 "
            + "FROM StdSkill ss LEFT JOIN stmd s ON ss.studentNo = s.student_no "
            + "LEFT JOIN Gstmd gs ON ss.studentNo = gs.student_no "
            + "WHERE ss.deptNo = ? AND ss.licenseValidDate BETWEEN ? AND ? "
            + "GROUP BY ss.licenseValidDate, s.depart_class, s.sex, " + "gs.depart_class, gs.sex "
            + "ORDER BY ss.licenseValidDate, s.depart_class, " + "s.sex, gs.depart_class, gs.sex";

    List<Map> ret = (List<Map>) am.findBySQL(hql, new Object[] { "0", cal1.getTime(), cal2.getTime() }); // 

    DateFormat df = new SimpleDateFormat("yyyy/MM/dd");

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("");
    sheet.setColumnWidth(0, 1800);
    sheet.setColumnWidth(1, 2000);
    sheet.setColumnWidth(2, 2000);
    sheet.setColumnWidth(3, 3000);
    sheet.setColumnWidth(4, 5000);
    sheet.setColumnWidth(5, 3000);
    sheet.setColumnWidth(6, 8000);
    sheet.setColumnWidth(7, 3000);
    sheet.setColumnWidth(8, 4000);
    sheet.setColumnWidth(9, 2200);
    sheet.setColumnWidth(10, 2200);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 10));

    HSSFFont fontSize12 = workbook.createFont();
    fontSize12.setFontHeightInPoints((short) 12);
    fontSize12.setFontName("Arial Unicode MS");

    HSSFFont fontSize10 = workbook.createFont();
    fontSize10.setFontHeightInPoints((short) 10);
    fontSize10.setFontName("Arial Unicode MS");

    // Header
    Toolket.setCellValue(workbook, sheet, 0, 0,
            "?" + Toolket.getEmpUnit(empl.getUnit()) + "  ("
                    + df.format(cal1.getTime()) + "~" + df.format(cal2.getTime()) + ")",
            fontSize12, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null);

    // Column Header
    Toolket.setCellValue(workbook, sheet, 1, 0, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    Toolket.setCellValue(workbook, sheet, 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 2, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    Toolket.setCellValue(workbook, sheet, 1, 4, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    Toolket.setCellValue(workbook, sheet, 1, 5, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 6, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 7, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    Toolket.setCellValue(workbook, sheet, 1, 8, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 9, "()", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 10, "()", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    // Toolket.setCellValue(workbook, sheet, 1, 11, "?", fontSize10,
    // HSSFCellStyle.ALIGN_CENTER, true, null);

    int index = 2;
    boolean isGraduate = false;
    String departClass = null;
    List<LicenseCode> codes = null;
    List<LicenseCode961> code961s = null;
    List<DeptCode4Yun> yuns = null;
    LicenseCode code = null;
    LicenseCode961 code961 = null;
    DeptCode4Yun yun = null;
    Example example = null;

    for (Map data : ret) {

        Toolket.setCellValue(workbook, sheet, index, 0, String.valueOf(index - 1), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);

        if (data.get("DC1") != null) {
            departClass = (String) data.get("DC1");
            isGraduate = false;
        } else {
            departClass = (String) data.get("DC2");
            isGraduate = true;
        }

        yun = new DeptCode4Yun();
        yun.setClassNo(StringUtils.substring(departClass, 0, 4));
        example = Example.create(yun).ignoreCase().enableLike(MatchMode.START);
        yuns = (List<DeptCode4Yun>) am.findSQLWithCriteria(DeptCode4Yun.class, example, null, null);
        if (!yuns.isEmpty() && yuns.size() == 1) {
            yun = yuns.get(0);

            Toolket.setCellValue(workbook, sheet, index, 1, yun.getDeptCode(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index, 2, yun.getCampusCode(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index, 3, yun.getCampusName(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
        }

        Toolket.setCellValue(workbook, sheet, index, 4, Toolket.getClassFullName(departClass), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);
        Toolket.setCellValue(workbook, sheet, index, 5, (String) data.get("LC"), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);
        codes = (List<LicenseCode>) am
                .findLicenseCodesBy(new LicenseCode(Integer.valueOf((String) data.get("LC"))));
        if (!codes.isEmpty()) {
            code = codes.get(0);
            Toolket.setCellValue(workbook, sheet, index, 6, code.getName(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index, 7, code.getLevel(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
        } else {
            code961s = (List<LicenseCode961>) am
                    .findLicenseCode961sBy(new LicenseCode961(Integer.valueOf((String) data.get("LC"))));
            if (!code961s.isEmpty()) {
                code961 = code961s.get(0);
                Toolket.setCellValue(workbook, sheet, index, 6, code961.getName(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 7, code961.getLevel(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
            }
        }

        Toolket.setCellValue(workbook, sheet, index, 8, df.format((Date) data.get("LVD")), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);

        if (!isGraduate) {
            if ("1".equals((String) data.get("S1"))) {
                Toolket.setCellValue(workbook, sheet, index, 9, ((Long) data.get("CT")).toString(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 10, "", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                        true, null);
            } else if ("2".equals((String) data.get("S1"))) {
                Toolket.setCellValue(workbook, sheet, index, 9, "", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                        true, null);
                Toolket.setCellValue(workbook, sheet, index, 10, ((Long) data.get("CT")).toString(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
            }
        } else {
            if ("1".equals((String) data.get("S2"))) {
                Toolket.setCellValue(workbook, sheet, index, 9, ((Long) data.get("CT")).toString(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 10, "", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                        true, null);
            } else if ("2".equals((String) data.get("S2"))) {
                Toolket.setCellValue(workbook, sheet, index, 9, "", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                        true, null);
                Toolket.setCellValue(workbook, sheet, index, 10, ((Long) data.get("CT")).toString(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
            }
        }

        index++;
        // Toolket.setCellValue(workbook, sheet, index++, 11, ((Long) m
        // .get("CT")).toString(), fontSize10,
        // HSSFCellStyle.ALIGN_CENTER, true, null);
    }

    File tempDir = new File(
            context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno()
                    + (new SimpleDateFormat("yyyyMMdd").format(new Date()))));
    if (!tempDir.exists())
        tempDir.mkdirs();

    File output = new File(tempDir, "DeptStdSkillList3.xls");
    FileOutputStream fos = new FileOutputStream(output);
    workbook.write(fos);
    fos.close();

    JasperReportUtils.printXlsToFrontEnd(response, output);
    output.delete();
    tempDir.delete();
}

From source file:tw.edu.chit.struts.action.language.ReportPrintAction.java

/**
 * ?/*w w  w . j av  a  2 s.c o  m*/
 * 
 * @param mapping org.apache.struts.action.ActionMapping object
 * @param form org.apache.struts.action.ActionForm object
 * @param request request javax.servlet.http.HttpServletRequest object
 * @param response response javax.servlet.http.HttpServletResponse object
 * @param sterm 
 */
@SuppressWarnings("unchecked")
private void printDeptStdSkillList4(ActionMapping mapping, DynaActionForm form, HttpServletRequest request,
        HttpServletResponse response, String sterm) throws Exception {

    HttpSession session = request.getSession(false);
    AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME);
    MemberManager mm = (MemberManager) getBean(MEMBER_MANAGER_BEAN_NAME);

    Member member = (Member) getUserCredential(session).getMember();
    Empl empl = mm.findEmplByOid(member.getOid());
    ServletContext context = request.getSession().getServletContext();

    DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
    Calendar cal = Calendar.getInstance();
    Calendar cal1 = (Calendar) cal.clone();
    Calendar cal2 = (Calendar) cal.clone();
    if (StringUtils.isNotBlank(form.getString("licenseValidDateStart"))
            || StringUtils.isNotBlank(form.getString("licenseValidDateEnd"))) {
        Date from = StringUtils.isBlank(form.getString("licenseValidDateStart")) ? null
                : Toolket.parseNativeDate(form.getString("licenseValidDateStart"));
        // ???
        Date to = StringUtils.isBlank(form.getString("licenseValidDateEnd")) ? Calendar.getInstance().getTime()
                : Toolket.parseNativeDate(form.getString("licenseValidDateEnd"));

        cal1.setTime(from);
        cal1.set(Calendar.HOUR_OF_DAY, 0);
        cal1.set(Calendar.MINUTE, 0);
        cal1.set(Calendar.SECOND, 0);
        cal1.set(Calendar.MILLISECOND, 0);

        cal2.setTime(to);
        cal2.set(Calendar.HOUR_OF_DAY, 23);
        cal2.set(Calendar.MINUTE, 59);
        cal2.set(Calendar.SECOND, 59);
        cal2.set(Calendar.MILLISECOND, 999);
    }

    String hql = "SELECT COUNT(*), SUM(s.amount), s FROM StdSkill s WHERE s.deptNo = ? "
            + "AND s.amountDate IS NOT NULL AND s.licenseValidDate BETWEEN ? AND ? " + "GROUP BY s.licenseCode";

    List<Object> ret = (List<Object>) am.find(hql, new Object[] { "0", cal1.getTime(), cal2.getTime() }); // 

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("?");
    sheet.setColumnWidth(0, 1500);
    sheet.setColumnWidth(1, 3000);
    sheet.setColumnWidth(2, 10000);
    sheet.setColumnWidth(3, 3000);
    sheet.setColumnWidth(4, 5000);
    sheet.setColumnWidth(5, 2400);
    sheet.setColumnWidth(6, 8000);
    sheet.setColumnWidth(7, 1800);
    sheet.setColumnWidth(8, 2200);
    sheet.setColumnWidth(9, 2400);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 9));

    HSSFFont fontSize12 = workbook.createFont();
    fontSize12.setFontHeightInPoints((short) 12);
    fontSize12.setFontName("Arial Unicode MS");

    HSSFFont fontSize10 = workbook.createFont();
    fontSize10.setFontHeightInPoints((short) 10);
    fontSize10.setFontName("Arial Unicode MS");

    // Header
    Toolket.setCellValue(workbook, sheet, 0, 0,
            "?" + Toolket.getEmpUnit(empl.getUnit()) + "? ("
                    + df.format(cal1.getTime()) + "~" + df.format(cal2.getTime()) + ")",
            fontSize12, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null);

    // Column Header
    Toolket.setCellValue(workbook, sheet, 1, 0, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    Toolket.setCellValue(workbook, sheet, 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 2, "??", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 3, "/", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 4, "/", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 5, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 6, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 7, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    Toolket.setCellValue(workbook, sheet, 1, 8, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 9, "?", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);

    int index = 2, totals = 0, sum = 0;
    StdSkill skill = null;
    List<LicenseCode> codes = null;
    List<LicenseCode961> code961s = null;
    LicenseCode code = null;
    LicenseCode961 code961 = null;
    Object[] data = null;

    for (Object o : ret) {

        data = (Object[]) o;
        totals += (Integer) data[0];
        sum += (Integer) data[1];
        skill = (StdSkill) data[2];

        Toolket.setCellValue(workbook, sheet, index, 0, String.valueOf(index - 1), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);
        Toolket.setCellValue(workbook, sheet, index, 7, ((Integer) data[0]).toString(), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);
        Toolket.setCellValue(workbook, sheet, index, 8, skill.getAmount().toString(), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);
        Toolket.setCellValue(workbook, sheet, index, 9, ((Integer) data[1]).toString(), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);

        codes = (List<LicenseCode>) am
                .findLicenseCodesBy(new LicenseCode(Integer.valueOf(skill.getLicenseCode())));

        if (!codes.isEmpty()) {
            code = codes.get(0);
            Toolket.setCellValue(workbook, sheet, index, 1, code.getCode().toString(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index, 2, code.getName(), fontSize10,
                    HSSFCellStyle.ALIGN_LEFT, true, null);
            Toolket.setCellValue(workbook, sheet, index, 3, code.getLocale().toString(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index, 4, code.getLevel(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index, 5, code.getType().toString(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
            Toolket.setCellValue(workbook, sheet, index, 6, code.getDeptName(), fontSize10,
                    HSSFCellStyle.ALIGN_LEFT, true, null);
        } else {
            code961s = (List<LicenseCode961>) am
                    .findLicenseCode961sBy(new LicenseCode961(Integer.valueOf(skill.getLicenseCode())));
            if (!code961s.isEmpty()) {
                code961 = code961s.get(0);
                Toolket.setCellValue(workbook, sheet, index, 1, code961.getCode().toString(), fontSize10,
                        HSSFCellStyle.ALIGN_LEFT, true, null);
                Toolket.setCellValue(workbook, sheet, index, 2, code961.getName(), fontSize10,
                        HSSFCellStyle.ALIGN_LEFT, true, null);
                Toolket.setCellValue(workbook, sheet, index, 3, code961.getLocale().toString(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 4, code961.getLevel(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 5, code961.getType().toString(), fontSize10,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, index, 6, code961.getDeptName(), fontSize10,
                        HSSFCellStyle.ALIGN_LEFT, true, null);
            }
        }

        index++;
    }

    Toolket.setCellValue(workbook, sheet, index, 6, "?", fontSize12, HSSFCellStyle.ALIGN_CENTER, false,
            35.0F, null);
    Toolket.setCellValue(workbook, sheet, index, 7, String.valueOf(totals), fontSize12,
            HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null);
    Toolket.setCellValue(workbook, sheet, index, 9, String.valueOf(sum), fontSize12, HSSFCellStyle.ALIGN_CENTER,
            false, 35.0F, null);

    File tempDir = new File(
            context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno()
                    + (new SimpleDateFormat("yyyyMMdd").format(new Date()))));
    if (!tempDir.exists())
        tempDir.mkdirs();

    File output = new File(tempDir, "DeptStdSkillList4.xls");
    FileOutputStream fos = new FileOutputStream(output);
    workbook.write(fos);
    fos.close();

    JasperReportUtils.printXlsToFrontEnd(response, output);
    output.delete();
    tempDir.delete();
}

From source file:tw.edu.chit.struts.action.language.ReportPrintAction.java

/**
 * ?//from   w ww  .  j av 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 printDeptStdSkillList5(ActionMapping mapping, DynaActionForm form, HttpServletRequest request,
        HttpServletResponse response, String sterm) throws Exception {

    HttpSession session = request.getSession(false);
    AdminManager am = (AdminManager) getBean(IConstants.ADMIN_MANAGER_BEAN_NAME);
    MemberManager mm = (MemberManager) getBean(MEMBER_MANAGER_BEAN_NAME);

    Member member = (Member) getUserCredential(session).getMember();
    Empl empl = mm.findEmplByOid(member.getOid());
    ServletContext context = request.getSession().getServletContext();

    DateFormat df = new SimpleDateFormat("yyyy/MM/dd");
    Calendar cal = Calendar.getInstance();
    Calendar cal1 = (Calendar) cal.clone();
    Calendar cal2 = (Calendar) cal.clone();
    if (StringUtils.isNotBlank(form.getString("licenseValidDateStart"))
            || StringUtils.isNotBlank(form.getString("licenseValidDateEnd"))) {
        Date from = StringUtils.isBlank(form.getString("licenseValidDateStart")) ? null
                : Toolket.parseNativeDate(form.getString("licenseValidDateStart"));
        // ???
        Date to = StringUtils.isBlank(form.getString("licenseValidDateEnd")) ? Calendar.getInstance().getTime()
                : Toolket.parseNativeDate(form.getString("licenseValidDateEnd"));

        cal1.setTime(from);
        cal1.set(Calendar.HOUR_OF_DAY, 0);
        cal1.set(Calendar.MINUTE, 0);
        cal1.set(Calendar.SECOND, 0);
        cal1.set(Calendar.MILLISECOND, 0);

        cal2.setTime(to);
        cal2.set(Calendar.HOUR_OF_DAY, 23);
        cal2.set(Calendar.MINUTE, 59);
        cal2.set(Calendar.SECOND, 59);
        cal2.set(Calendar.MILLISECOND, 999);
    }

    String hql = "SELECT COUNT(*), s.techIdno, s.licenseCode FROM StdSkill s "
            + "WHERE s.deptNo = ? AND s.techIdno IS NOT NULL " + "AND s.licenseValidDate BETWEEN ? AND ? "
            + "GROUP BY s.techIdno, s.licenseCode";

    List<Object> ret = (List<Object>) am.find(hql, new Object[] { "0", cal1.getTime(), cal2.getTime() }); // 

    HSSFWorkbook workbook = new HSSFWorkbook();
    HSSFSheet sheet = workbook.createSheet("?");
    sheet.setColumnWidth(0, 4000);
    sheet.setColumnWidth(1, 6000);
    sheet.setColumnWidth(2, 4000);
    sheet.setColumnWidth(3, 3000);
    sheet.setColumnWidth(4, 4000);
    sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 4));

    HSSFFont fontSize12 = workbook.createFont();
    fontSize12.setFontHeightInPoints((short) 12);
    fontSize12.setFontName("Arial Unicode MS");

    HSSFFont fontSize10 = workbook.createFont();
    fontSize10.setFontHeightInPoints((short) 10);
    fontSize10.setFontName("Arial Unicode MS");

    // Header
    Toolket.setCellValue(workbook, sheet, 0, 0,
            "?" + Toolket.getEmpUnit(empl.getUnit())
                    + "? (" + df.format(cal1.getTime()) + "~"
                    + df.format(cal2.getTime()) + ")",
            fontSize12, HSSFCellStyle.ALIGN_CENTER, false, 35.0F, null);

    // Column Header
    Toolket.setCellValue(workbook, sheet, 1, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER,
            true, null);
    Toolket.setCellValue(workbook, sheet, 1, 1, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 2, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
    Toolket.setCellValue(workbook, sheet, 1, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
            null);
    Toolket.setCellValue(workbook, sheet, 1, 4, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);

    int index = 2;
    Object[] data = null;
    DEmpl dempl = null;

    for (Object o : ret) {

        data = (Object[]) o;

        sheet.addMergedRegion(new CellRangeAddress(index, index + 2, 0, 0));
        empl = mm.findEmplByIdno((String) data[1]);
        if (empl == null) {
            dempl = mm.findDEmplByIdno((String) data[1]);
            Toolket.setCellValue(workbook, sheet, index, 0, dempl.getCname(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
        } else {
            Toolket.setCellValue(workbook, sheet, index, 0, empl.getCname(), fontSize10,
                    HSSFCellStyle.ALIGN_CENTER, true, null);
        }

        Toolket.setCellValue(workbook, sheet, index + 1, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);
        Toolket.setCellValue(workbook, sheet, index + 2, 0, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);

        Toolket.setCellValue(workbook, sheet, index, 1, "", fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);
        Toolket.setCellValue(workbook, sheet, index + 1, 1, "", fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);
        Toolket.setCellValue(workbook, sheet, index + 2, 1, "", fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);

        Toolket.setCellValue(workbook, sheet, index, 2, "10", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);
        Toolket.setCellValue(workbook, sheet, index + 1, 2, "5", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                true, null);
        Toolket.setCellValue(workbook, sheet, index + 2, 2, "2", fontSize10, HSSFCellStyle.ALIGN_CENTER,
                true, null);

        Toolket.setCellValue(workbook, sheet, index, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
        Toolket.setCellValue(workbook, sheet, index + 1, 3, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);
        Toolket.setCellValue(workbook, sheet, index + 2, 3, ((Integer) data[0]).toString(), fontSize10,
                HSSFCellStyle.ALIGN_CENTER, true, null);

        Toolket.setCellValue(workbook, sheet, index, 4, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true, null);
        Toolket.setCellValue(workbook, sheet, index + 1, 4, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);
        Toolket.setCellValue(workbook, sheet, index + 2, 4, "", fontSize10, HSSFCellStyle.ALIGN_CENTER, true,
                null);

        index += 3;
    }

    index++;
    sheet.addMergedRegion(new CellRangeAddress(index, index, 0, 1));
    Toolket.setCellValue(workbook, sheet, index, 0, " : ?20", fontSize10,
            HSSFCellStyle.ALIGN_LEFT, false, null);
    File tempDir = new File(
            context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno()
                    + (new SimpleDateFormat("yyyyMMdd").format(new Date()))));
    if (!tempDir.exists())
        tempDir.mkdirs();

    File output = new File(tempDir, "DeptStdSkillList5.xls");
    FileOutputStream fos = new FileOutputStream(output);
    workbook.write(fos);
    fos.close();

    JasperReportUtils.printXlsToFrontEnd(response, output);
    output.delete();
    tempDir.delete();
}

From source file:tw.edu.chit.struts.action.registration.ReportPrintAction.java

/**
 * ?//from ww  w  .jav  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 printRegisterList(ActionMapping mapping, DynaActionForm form, HttpServletRequest request,
        HttpServletResponse response, String sterm) throws Exception {

    HttpSession session = request.getSession(false);
    MemberManager mm = (MemberManager) getBean(IConstants.MEMBER_MANAGER_BEAN_NAME);
    CourseManager cm = (CourseManager) getBean(IConstants.COURSE_MANAGER_BEAN_NAME);
    ScoreManager sm = (ScoreManager) getBean(IConstants.SCORE_MANAGER_BEAN_NAME);
    ServletContext context = request.getSession().getServletContext();
    String year = cm.getNowBy("School_year");
    String term = form.getString("sterm");
    String header = "?YEARTERM??"
            .replaceAll("YEAR", year).replaceAll("TERM", term);
    List<Clazz> clazzes = sm.findClassBy(new Clazz(processClassInfo(form)),
            getUserCredential(session).getClassInChargeAry(), false);
    if (!clazzes.isEmpty()) {
        List<Student> students = null;
        HSSFSheet sheet = null;
        HSSFWorkbook workbook = new HSSFWorkbook();
        HSSFFont fontSize16 = workbook.createFont();
        fontSize16.setFontHeightInPoints((short) 16);
        fontSize16.setFontName("Arial Unicode MS");
        HSSFFont fontSize12 = workbook.createFont();
        fontSize12.setFontHeightInPoints((short) 12);
        fontSize12.setFontName("Arial Unicode MS");
        for (Clazz clazz : clazzes) {
            if (Toolket.isDelayClass(clazz.getClassNo()))
                continue;

            students = mm.findStudentsByClassNo(clazz.getClassNo());
            if (!students.isEmpty()) {
                sheet = workbook.createSheet(clazz.getClassNo());
                sheet.setColumnWidth(0, 3000);
                sheet.setColumnWidth(1, 3000);
                sheet.setColumnWidth(2, 5000);
                sheet.setColumnWidth(3, 3500);
                sheet.setColumnWidth(4, 3500);
                sheet.setColumnWidth(5, 3500);
                sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, 5));
                sheet.addMergedRegion(new CellRangeAddress(1, 1, 0, 5));
                // Header
                Toolket.setCellValue(workbook, sheet, 0, 0, header, fontSize16, HSSFCellStyle.ALIGN_CENTER,
                        false, 35.0F, null);
                Toolket.setCellValue(workbook, sheet, 1, 0,
                        "?:" + Toolket.getClassFullName(clazz.getClassNo())
                                + "                       ???",
                        fontSize12, HSSFCellStyle.ALIGN_LEFT, false, null, null);

                // Column Header
                Toolket.setCellValue(workbook, sheet, 2, 0, "", fontSize12, HSSFCellStyle.ALIGN_CENTER,
                        true, null);
                Toolket.setCellValue(workbook, sheet, 2, 1, "??", fontSize12, HSSFCellStyle.ALIGN_CENTER,
                        true, null);
                Toolket.setCellValue(workbook, sheet, 2, 2, "", fontSize12,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, 2, 3, "?", fontSize12,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, 2, 4, "", fontSize12,
                        HSSFCellStyle.ALIGN_CENTER, true, null);
                Toolket.setCellValue(workbook, sheet, 2, 5, "", fontSize12, HSSFCellStyle.ALIGN_CENTER,
                        true, null);

                int index = 3;
                for (Student student : students) {
                    Toolket.setCellValue(workbook, sheet, index, 0, student.getStudentNo(), fontSize12,
                            HSSFCellStyle.ALIGN_CENTER, true, null);
                    Toolket.setCellValue(workbook, sheet, index, 1,
                            student.getStudentName().trim().replaceAll("", ""), fontSize12,
                            HSSFCellStyle.ALIGN_CENTER, true, null);
                    Toolket.setCellValue(workbook, sheet, index, 2, "", null, HSSFCellStyle.ALIGN_CENTER, true,
                            null);
                    Toolket.setCellValue(workbook, sheet, index, 3, "", null, HSSFCellStyle.ALIGN_CENTER, true,
                            null);
                    Toolket.setCellValue(workbook, sheet, index, 4, "", null, HSSFCellStyle.ALIGN_CENTER, true,
                            null);
                    Toolket.setCellValue(workbook, sheet, index++, 5, "", null, HSSFCellStyle.ALIGN_CENTER,
                            true, null);
                    if (39 == index) { // ?
                        Toolket.setCellValue(workbook, sheet, index, 0, "", fontSize12,
                                HSSFCellStyle.ALIGN_CENTER, true, null);
                        Toolket.setCellValue(workbook, sheet, index, 1, "??", fontSize12,
                                HSSFCellStyle.ALIGN_CENTER, true, null);
                        Toolket.setCellValue(workbook, sheet, index, 2, "", fontSize12,
                                HSSFCellStyle.ALIGN_CENTER, true, null);
                        Toolket.setCellValue(workbook, sheet, index, 3, "?", fontSize12,
                                HSSFCellStyle.ALIGN_CENTER, true, null);
                        Toolket.setCellValue(workbook, sheet, index, 4, "", fontSize12,
                                HSSFCellStyle.ALIGN_CENTER, true, null);
                        Toolket.setCellValue(workbook, sheet, index++, 5, "", fontSize12,
                                HSSFCellStyle.ALIGN_CENTER, true, null);
                    }
                }

                for (int i = 0; i <= 5; i++) {
                    if (i == 0) {
                        Toolket.setCellValue(workbook, sheet, index, i, "?", fontSize12,
                                HSSFCellStyle.ALIGN_CENTER, true, null);
                        continue;
                    }
                    Toolket.setCellValue(workbook, sheet, index, i, "", null, HSSFCellStyle.ALIGN_CENTER, true,
                            null);
                }

                sheet.addMergedRegion(new CellRangeAddress(++index, index, 0, 5));
                // Toolket.setCellValue(workbook, sheet, index, 0,
                // " ???",
                // fontSize12, HSSFCellStyle.ALIGN_LEFT, false, null);
            }
        }

        File tempDir = new File(
                context.getRealPath("/WEB-INF/reports/temp/" + getUserCredential(session).getMember().getIdno()
                        + (new SimpleDateFormat("yyyyMMdd").format(new Date()))));
        if (!tempDir.exists())
            tempDir.mkdirs();

        File output = new File(tempDir, "RegisterList.xls");
        FileOutputStream fos = new FileOutputStream(output);
        workbook.write(fos);
        fos.close();

        JasperReportUtils.printXlsToFrontEnd(response, output);
        output.delete();
        tempDir.delete();
    } else {
        Map<String, String> param = new HashMap<String, String>();
        File image = new File(context.getRealPath("/pages/images/2002chitS.jpg"));
        param.put("IMAGE", image.getAbsolutePath());
        byte[] bytes = JasperRunManager.runReportToPdf(JasperReportUtils.getNoResultReport(context), param,
                new JREmptyDataSource());
        JasperReportUtils.printPdfToFrontEnd(response, bytes);
    }
}