Excel文档导出功能的实现。

Excel文档导出功能的实现。

在这里主要是记录该功能中主要用到的相关工具类。

1.Controller层：

/**
     * 入职补贴管理列表导出数据
     * @param searchParam
     * @return
	 * @throws UnsupportedEncodingException 
     */
	@ResponseBody
	@RequestMapping(value = "getEntryBonusFeeListexport")
	public void getFeeInfolistemport(HttpServletRequest request,HttpServletResponse response,
			String userName,String mobile,
			String corpName,String applyDate,
			String entryDate,String feeStatusNo  )throws UnsupportedEncodingException {
		Map<String, Object> param = new HashMap<String,Object>();
		param.put("entryDate", entryDate);
		param.put("feeStatusNo", feeStatusNo);
		param.put("mobile", mobile);
		param.put("corpName", corpName);
		param.put("userName", userName);
		param.put("applyDate", applyDate);
		 //RelativeDateFormat RelativeDateFormat = new RelativeDateFormat();
		JSONArray array = omoReIntentionService.selectForExport(param);//获取业务数据集
		Map<String, String> headMap = omoReIntentionService.getListHeadMap(1);//获取属性-列头
	    Date da = new Date();
	    String title = "入职补贴发放列表"+"-"+DateConvertUtils.getDateMonth();/* RelativeDateFormat.formatDate(da);*/
	    String datePattern = "yyyy-MM-dd";
	  //  PoiExportExcel.downloadExcelFile(URLEncoder.encode(title,"UTF-8"),headMap,array,datePattern,response);
	 if(isIESetBrowser(request)){
            try {
                PoiExportExcel.downloadExcelFile(URLEncoder.encode(title,"UTF-8"),headMap,array,datePattern,response);
            } catch (UnsupportedEncodingException e) {
                e.printStackTrace();
            }
        }else{
            PoiExportExcel.downloadExcelFile(title,headMap,array,datePattern,response);
        }
	}

1.1 判断IE浏览器类型：

  /**
     * IE浏览器类型判断
        * 兼容win10
     * @author tomm
     */
    public boolean isIESetBrowser(HttpServletRequest request) {
        String[] IEBrowserSignals = {"MSIE", "Trident", "Edge"};
        String userAgent = request.getHeader("User-Agent");
        for (String signal : IEBrowserSignals) {
            if (userAgent.contains(signal)){
                return true;
            }
        }
        return false;
    }

1.2 导出功能实现工具类PoiExportExcel中的导出方法：

    //Web 导出excel
    public static void downloadExcelFile(String title,Map<String,String> headMap,JSONArray ja,String datePattern,HttpServletResponse response){
        try {
            ByteArrayOutputStream os = new ByteArrayOutputStream();
            exportExcelX(title,headMap,ja,datePattern,0,os);
            byte[] content = os.toByteArray();
            InputStream is = new ByteArrayInputStream(content);
            // 设置response参数，可以打开下载页面
            response.reset();

            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); 
            response.setHeader("Content-Disposition", "attachment;filename="+ new String((title + System.currentTimeMillis() + ".xlsx").getBytes(), "iso-8859-1"));
            response.setContentLength(content.length);
            ServletOutputStream outputStream = response.getOutputStream();
            BufferedInputStream bis = new BufferedInputStream(is);
            BufferedOutputStream bos = new BufferedOutputStream(outputStream);
            byte[] buff = new byte[8192];
            int bytesRead;
            while (-1 != (bytesRead = bis.read(buff, 0, buff.length))) {
                bos.write(buff, 0, bytesRead);

            }
            bis.close();
            bos.close();
            outputStream.flush();
            outputStream.close();
        }catch (Exception e) {
            e.printStackTrace();
        }
    }

2.Service层：

2.1.1 获取业务数据集service接口：

  /**
	 * 获取导出的excel数据集
	 * @param setProjectClearingId
	 * @return
	 */
	public JSONArray selectForExport(Map<String, Object> param);

2.1.2 获取业务数据集service实现：

@Override
	public JSONArray selectForExport(Map<String, Object> param ) {
		Map<String,Object> params = new HashMap<>();
		//params.put("setProjectClearingId",setProjectClearingId);
		//查询补贴相关费用
		  List<OmoReIntention> infoList = omoReIntentionDao.findFeeInfoPage(params);
		//判断是否有数据
		JSONArray jsonInfo = new JSONArray();//初始化JSONArray数组用来接收 参与Excel数据导出的信息
		if(infoList!=null && infoList.size() > 0){
			for(OmoReIntention logs : infoList){
				//用于excel导出
				SettlementFinanceExportVo eportVo = new SettlementFinanceExportVo();//需要导出的数据实体
				//具体数据添加
				excelInfoPut(eportVo,logs);//将查出来的每条数据封装到导出数据实体中
				jsonInfo.add(eportVo);
			}
		}
		return jsonInfo;
	}

2.2.1 获取导出Excel导出数据表头信息：

/**
	 * excel表头
	 * @param clearType
	 * @return
	 */
   public Map<String,String> getListHeadMap(int clearType);

2.2.2 获取导出Excel导出数据表头信息实现：

@Override
	public Map<String, String> getListHeadMap(int clearType) {
		Map<String,String> headMap = new LinkedHashMap<String,String>();
		headMap.put("name", "姓名");
		headMap.put("gender", "性别");
		headMap.put("idCard", "身份证号");
		headMap.put("interviewTime", "面试时间");
		headMap.put("provider", "供应商");
		headMap.put("arrangeBy", "业务员");
		headMap.put("entrytime", "报道时间");
		headMap.put("dimissiontime", "离职日期");
		headMap.put("dayCount", "在职天数");
		headMap.put("receivablefees", "应收费用");
		headMap.put("feeStandard", "补贴标准");
		headMap.put("settlementconditions ", "是否达成结算条件");
		headMap.put("remittance", "是否打款");
		headMap.put("remittancemoney", "打款金额");
		headMap.put("remittancetime", "打款时间");
		headMap.put("extradays", "超期天数");
		return headMap;
	}

导出功能结束。