Examples with ExcelFileGenerator com.itrus.portal.utils.ExcelFileGenerator used on opensource projects

Example 16 with ExcelFileGenerator

use of com.itrus.portal.utils.ExcelFileGenerator in project portal by ixinportal.

the class ChargingController method excelExport.

/**
 * 导出excel
 * szy
 * 2017年3月16日 下午4:22:24
 * @param appId
 *            应用id
 * @param statPeriod
 *            统计周期
 * @param queryDate1
 *            统计时间开始
 * @param queryDate2
 *            统计时间结束
 * @param serviceName
 *            服务名称
 * @param servieType
 *            服务类别
 * @param userGe
 *            策略
 * @param resultForm
 *            返回结果形式
 * @return
 * String
 */
@RequestMapping(value = "/excel")
public String excelExport(@RequestParam(value = "appId", required = false) Long appId, @RequestParam(value = "statPeriod", required = false) Long statPeriod, @RequestParam(value = "queryDate1", required = false) Integer queryDate1, @RequestParam(value = "queryDate2", required = false) Integer queryDate2, @RequestParam(value = "serviceName", required = false) String serviceName, @RequestParam(value = "servieType", required = false) Long servieType, @RequestParam(value = "userGe", required = false) Long userGe, @RequestParam(value = "resultForm", required = false) Long resultForm, @RequestParam(value = "certItem", required = false) String certItem, HttpServletRequest request, HttpServletResponse response) {
    Map<String, Object> param = new HashMap<>();
    if (null != appId) {
        param.put("appId", appId);
    }
    if (null != statPeriod) {
        param.put("period", statPeriod);
    } else {
        statPeriod = 1L;
        param.put("period", 1);
    }
    if (null != queryDate1) {
        param.put("queryDate1", queryDate1);
    }
    if (null != queryDate2) {
        param.put("queryDate2", queryDate2);
    }
    if (null != userGe) {
        param.put("userGe", userGe);
    }
    boolean isEmpty = false;
    // 服务类别是选择企业 0 所有，1 企业，2 个人
    int serviceType = 0;
    if (null != servieType) {
        param.put("servieType", servieType);
        if (servieType.intValue() == 1) {
            serviceType = 1;
        } else {
            serviceType = 2;
        }
    }
    // 符合要求的应用
    Set<Long> appServices = null;
    // 搜索的认证项
    Long item = null;
    // 认证项是企业的 -1为默认值，1为企业， 2个人
    int isEnterprise = -1;
    // 默认是所有 -1， 1是企业，0 是个人
    int certType = -1;
    // 服务类别是选择企业 0 所有，1 企业，2 个人
    Long enterpriseItem = 0L;
    boolean isSdk = false;
    if ((null != certItem && !"".equals(certItem)) || resultForm != null) {
        if (StringUtils.isNotEmpty(certItem)) {
            item = new Long(certItem.substring(1));
            // 企业认证项以e开头，个人认证项 以p开头, 移动端sdk认证项 已s开头
            if (certItem.startsWith("e")) {
                isEnterprise = 1;
                enterpriseItem = 1L;
            } else {
                if (certItem.startsWith("s")) {
                    isSdk = true;
                }
                isEnterprise = 2;
                enterpriseItem = 2L;
            }
        }
        // 服务类型和认证项同为企业或者个人，否则返回空
        if (serviceType != 0 && isEnterprise != -1) {
            if (serviceType != isEnterprise) {
                isEmpty = true;
            }
        }
        if (serviceType == 1) {
            certType = 1;
        } else if (serviceType == 2) {
            certType = 0;
        }
        appServices = chargService.getAppList(certType, item, resultForm, isSdk);
        if (appServices != null && appServices.size() > 0) {
            param.put("appServices", appServices);
        } else {
            isEmpty = true;
        }
    }
    param.put("enterpriseItem", enterpriseItem);
    if (appServices != null && appServices.size() > 0) {
        param.put("appIds", appServices);
    }
    if (serviceName != null && !"".equals(serviceName)) {
        param.put("appServiceName", "%" + serviceName + "%");
    }
    List<Long> serviceLists = sqlSession.selectList("com.itrus.portal.db.ChargingMapper.selectServiceId", param);
    if (serviceLists != null && serviceLists.size() > 0) {
        param.put("serviceLists", serviceLists);
    } else {
        isEmpty = true;
    }
    // 获得serviceMap key:id value name
    Map<Long, String> serviceMap = chargService.getServiceMap();
    if (StringUtils.isNotEmpty(serviceName)) {
        List<Long> serviceList = chargService.getSqlCon(serviceName, serviceMap);
        if (serviceList != null && serviceList.size() > 0) {
            param.put("serviceList", serviceList);
        } else {
            isEmpty = true;
        }
    }
    String connSql = chargService.connSql(statPeriod, new Long(queryDate1), new Long(queryDate2));
    param.put("connSql", connSql);
    // FIXME 数据库为double, 费用可能有精度缺失问题， 小数保留位数 2
    List<Map<String, Object>> cAll = sqlSession.selectList("com.itrus.portal.db.ChargingMapper.selectByCondition", param);
    Map<String, Long> serviceKeyMap = chargService.getServiceKeyMap();
    if (!isEmpty) {
        // cAll = sqlSession.selectList("com.itrus.portal.db.ChargingMapper.selectByCondition", param);
        // //根据service id设置name
        // cAll = chargService.explainService(cAll, serviceMap);
        // //根据app_service 设置认证项和返回结果形式
        // cAll = chargService.explainCertItems(cAll, true, 1L);
        // //格式化数据
        // cAll = chargService.formatData(cAll);
        // cAll = chargService.addTotalRow(cAll);
        cAll = chargService.completionData(cAll, serviceKeyMap, new Long(queryDate1), new Long(queryDate2), statPeriod);
        cAll = chargService.explainService(cAll);
        cAll = chargService.explainCertItems(cAll, true, item);
        // //解析编码为汉字
        cAll = chargService.explainCode(cAll);
        // //增加总计行
        cAll = chargService.addTotalRow(cAll);
    }
    ArrayList<String> fildName = chargService.excelFildName(statPeriod);
    ArrayList<ArrayList<String>> excelFildData = chargService.excelFildData(cAll);
    ExcelFileGenerator generator = new ExcelFileGenerator(fildName, excelFildData);
    try {
        // 重置response对象中的缓冲区，该方法可以不写，但是你要保证response缓冲区没有其他数据，否则导出可能会出现问题，建议加上
        response.reset();
        String filename = "实名认证账单" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + // 设置下载时客户端Excel的名称，此处需要用到encodeFilename()方法来转换编码，否则中文会被过滤掉
        ".xls";
        filename = generator.encodeFilename(filename, request);
        response.setHeader("Content-disposition", "attachment;filename=" + filename);
        response.setCharacterEncoding("utf-8");
        // 由于导出格式是excel的文件，设置导出文件的响应头部信息
        response.setContentType("application/vnd.ms-excel");
        // 生成excel,传递输出流
        // 用response对象获取输出流
        OutputStream os = response.getOutputStream();
        generator.expordExcel(os);
        // 清理刷新缓冲区，将缓存中的数据将数据导出excel
        os.flush();
        // 关闭os
        if (os != null) {
            os.close();
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return null;
}

Example 17 with ExcelFileGenerator

use of com.itrus.portal.utils.ExcelFileGenerator in project portal by ixinportal.

the class StatisticsIssuingController method excelExport.

/**
 * 导出出证记录
 * @param serviceRequestPlatform
 * @param Statistics
 * @param year1
 * @param year2
 * @param page
 * @param size
 * @param hisApplicant
 * @param type
 * @param outReportTemplate
 * @param hisway
 * @param uiModel
 * @param request
 * @param response
 * @return
 * @throws Exception
 */
@RequestMapping(value = "/excel")
public String excelExport(@RequestParam(value = "serviceRequestPlatform", required = false) String serviceRequestPlatform, @RequestParam(value = "Statistics", required = false) Integer Statistics, @RequestParam(value = "year1", required = false) Integer year1, @RequestParam(value = "year2", required = false) Integer year2, @RequestParam(value = "page", required = false) Integer page, @RequestParam(value = "size", required = false) Integer size, @RequestParam(value = "hisApplicant", required = false) String hisApplicant, @RequestParam(value = "type", required = false) String type, @RequestParam(value = "outReportTemplate", required = false) String outReportTemplate, @RequestParam(value = "hisway", required = false) String hisway, Model uiModel, HttpServletRequest request, HttpServletResponse response) throws Exception {
    Map param = new HashMap();
    Integer year3 = null;
    if (year1 != null) {
        year3 = year1;
    }
    List<Map<Integer, Integer>> list = new ArrayList<Map<Integer, Integer>>();
    java.util.Date nowdate = new java.util.Date();
    String[] monthList = { "01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12" };
    String[] seasonList = { "S1", "S2", "S3", "S4" };
    String month = null;
    String season = null;
    String year = null;
    Calendar calendar = Calendar.getInstance();
    Calendar calendar2 = Calendar.getInstance();
    if (null != Statistics && Statistics == 12) {
        while (year1 <= year2) {
            for (int j = 0; j <= 11; j++) {
                calendar.set(Calendar.YEAR, year1);
                calendar.set(Calendar.MONTH, j);
                calendar.set(Calendar.DAY_OF_MONTH, 1);
                calendar.set(Calendar.HOUR_OF_DAY, 0);
                calendar.set(Calendar.MINUTE, 0);
                calendar.set(Calendar.SECOND, 0);
                calendar.set(Calendar.MILLISECOND, 0);
                Date dt1 = calendar.getTime();
                calendar2.set(Calendar.YEAR, year1);
                calendar2.set(Calendar.MONTH, j + 1);
                calendar2.set(Calendar.DAY_OF_MONTH, 1);
                calendar2.set(Calendar.HOUR_OF_DAY, 0);
                calendar2.set(Calendar.MINUTE, 0);
                calendar2.set(Calendar.SECOND, 0);
                calendar2.set(Calendar.MILLISECOND, 0);
                Date dt2 = calendar2.getTime();
                if (StringUtils.isNotEmpty(serviceRequestPlatform) && !"0".equals(serviceRequestPlatform)) {
                    param.put("serviceRequestPlatform", serviceRequestPlatform);
                }
                if (StringUtils.isNotEmpty(hisApplicant)) {
                    param.put("hisApplicant", "%" + hisApplicant + "%");
                }
                if (type != null && !"0".equals(type)) {
                    param.put("type", type);
                }
                if (outReportTemplate != null && !"0".equals(outReportTemplate)) {
                    param.put("outReportTemplate", outReportTemplate);
                }
                if (hisway != null && !"0".equals(hisway)) {
                    param.put("hisway", hisway);
                }
                month = year1 + "-" + monthList[j];
                param.put("queryDate1", dt1);
                param.put("queryDate2", dt2);
                int count = hiscertificate.selectcount(param);
                Map<Integer, Integer> map = new HashMap<Integer, Integer>();
                map.put(1, year1);
                map.put(2, count);
                list.add(map);
            }
            year1++;
        }
    }
    if (null != Statistics && Statistics == 4) {
        while (year1 <= year2) {
            for (int j = 0; j <= 9; j = j + 3) {
                calendar.set(Calendar.YEAR, year1);
                calendar.set(Calendar.MONTH, j);
                calendar.set(Calendar.DAY_OF_MONTH, 1);
                calendar.set(Calendar.HOUR_OF_DAY, 0);
                calendar.set(Calendar.MINUTE, 0);
                calendar.set(Calendar.SECOND, 0);
                calendar.set(Calendar.MILLISECOND, 0);
                Date dt1 = calendar.getTime();
                calendar2.set(Calendar.YEAR, year1);
                calendar2.set(Calendar.MONTH, j + 3);
                calendar2.set(Calendar.DAY_OF_MONTH, 1);
                calendar2.set(Calendar.HOUR_OF_DAY, 0);
                calendar2.set(Calendar.MINUTE, 0);
                calendar2.set(Calendar.SECOND, 0);
                calendar2.set(Calendar.MILLISECOND, 0);
                Date dt2 = calendar2.getTime();
                if (StringUtils.isNotEmpty(serviceRequestPlatform) && !"0".equals(serviceRequestPlatform)) {
                    param.put("serviceRequestPlatform", serviceRequestPlatform);
                }
                if (StringUtils.isNotEmpty(hisApplicant)) {
                    param.put("hisApplicant", "%" + hisApplicant + "%");
                }
                if (type != null && !"0".equals(type)) {
                    param.put("type", type);
                }
                if (outReportTemplate != null && !"0".equals(outReportTemplate)) {
                    param.put("outReportTemplate", outReportTemplate);
                }
                if (hisway != null && !"0".equals(hisway)) {
                    param.put("hisway", hisway);
                }
                month = year1 + "-" + monthList[j];
                param.put("queryDate1", dt1);
                param.put("queryDate2", dt2);
                int count = hiscertificate.selectcount(param);
                Map<Integer, Integer> map = new HashMap<Integer, Integer>();
                map.put(1, year1);
                map.put(2, count);
                list.add(map);
            }
            year1++;
        }
    }
    if (null != Statistics && Statistics == 1) {
        while (year1 <= year2) {
            calendar.set(Calendar.YEAR, year1);
            calendar.set(Calendar.MONTH, 0);
            calendar.set(Calendar.DAY_OF_MONTH, 1);
            calendar.set(Calendar.HOUR_OF_DAY, 0);
            calendar.set(Calendar.MINUTE, 0);
            calendar.set(Calendar.SECOND, 0);
            calendar.set(Calendar.MILLISECOND, 0);
            Date dt1 = calendar.getTime();
            calendar2.set(Calendar.YEAR, year1 + 1);
            calendar2.set(Calendar.MONTH, 0);
            calendar2.set(Calendar.DAY_OF_MONTH, 1);
            calendar2.set(Calendar.HOUR_OF_DAY, 0);
            calendar2.set(Calendar.MINUTE, 0);
            calendar2.set(Calendar.SECOND, 0);
            calendar2.set(Calendar.MILLISECOND, 0);
            Date dt2 = calendar2.getTime();
            if (StringUtils.isNotEmpty(serviceRequestPlatform) && !"0".equals(serviceRequestPlatform)) {
                param.put("serviceRequestPlatform", serviceRequestPlatform);
            }
            if (StringUtils.isNotEmpty(hisApplicant)) {
                param.put("hisApplicant", "%" + hisApplicant + "%");
            }
            if (type != null && !"0".equals(type)) {
                param.put("type", type);
            }
            if (outReportTemplate != null && !"0".equals(outReportTemplate)) {
                param.put("outReportTemplate", outReportTemplate);
            }
            if (hisway != null && !"0".equals(hisway)) {
                param.put("hisway", hisway);
            }
            year = year + "Y";
            param.put("queryDate1", dt1);
            param.put("queryDate2", dt2);
            int count = hiscertificate.selectcount(param);
            Map<Integer, Integer> map = new HashMap<Integer, Integer>();
            map.put(1, year1);
            map.put(2, count);
            list.add(map);
            year1++;
        }
    }
    List<EvidenceBasicInformation> basicinfo = basicInformationimpl.selectList();
    uiModel.addAttribute("basicinfo", basicinfo);
    List<EvidenceOutTemplate> outRemplate = outServiceConfigService.selectOutTemplateByList();
    uiModel.addAttribute("outRemplate", outRemplate);
    if (null == serviceRequestPlatform || "0".equals(serviceRequestPlatform)) {
        serviceRequestPlatform = "所有应用";
    }
    uiModel.addAttribute("list", list);
    // System.out.println(list);
    uiModel.addAttribute("year1", year3);
    uiModel.addAttribute("serviceRequestPlatform", serviceRequestPlatform);
    uiModel.addAttribute("year2", year2);
    uiModel.addAttribute("Statistics", Statistics);
    uiModel.addAttribute("hisApplicant", hisApplicant);
    uiModel.addAttribute("type", type);
    uiModel.addAttribute("outReportTemplate", outReportTemplate);
    uiModel.addAttribute("hisway", hisway);
    ArrayList<String> fieldName = statisticsissuing.excelFieldName();
    ArrayList<ArrayList<String>> fieldDatas = statisticsissuing.excelFieldData(list, serviceRequestPlatform, hisApplicant, type, outReportTemplate, hisway, Statistics);
    ExcelFileGenerator generator = new ExcelFileGenerator(fieldName, fieldDatas);
    try {
        // 重置response对象中的缓冲区，该方法可以不写，但是你要保证response缓冲区没有其他数据，否则导出可能会出现问题，建议加上
        response.reset();
        // 设置下载时客户端Excel的名称，此处需要用到encodeFilename()方法来转换编码，否则中文会被过滤掉
        String filename = "出证服务统计信息" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()) + ".xls";
        filename = generator.encodeFilename(filename, request);
        response.setHeader("Content-disposition", "attachment;filename=" + filename);
        response.setCharacterEncoding("utf-8");
        // 由于导出格式是excel的文件，设置导出文件的响应头部信息
        response.setContentType("application/vnd.ms-excel");
        // 生成excel,传递输出流
        // 用response对象获取输出流
        OutputStream os = response.getOutputStream();
        generator.expordExcel(os);
        // 清理刷新缓冲区，将缓存中的数据将数据导出excel
        os.flush();
        // 关闭os
        if (os != null) {
            os.close();
        }
    } catch (Exception e) {
        e.printStackTrace();
    }
    return null;
}