EXCEL导入导出

// 系统管理员登录，系统下资源管理-分机号列表内容导出
	@SuppressWarnings("rawtypes")
	@ResponseBody
	@RequestMapping(value = "/selectFenJiHaoInXiTongForExportByParam", produces = "application/json;charset=UTF-8")
	public JSONObject selectFenJiHaoInXiTongForExportByParam(
			@RequestParam(value = "fenJiHao", required = false) String fenJiHao,
			@RequestParam(value = "userId", required = false) Integer userId, // 租户id
			@RequestParam(value = "fenPeiState", required = false) Integer fenPeiState) {

		JSONObject result = new JSONObject();
		// token校验
		Boolean isToken = myToken.validationToken();
		log.info("isToken是：" + isToken);
		if (!isToken) {
			result.put("code", 10000);
			response.addIntHeader("code", 10000);
			return result;
		}
		Map<String, Object> param = new HashMap<String, Object>();
		param.put("fenJiHao", fenJiHao);
		param.put("userId", userId);
		param.put("fenPeiState", fenPeiState);
		//[{FEN_PEI_STATE=已分配, ZU_NAME=clj摩卓租户, CREATE_TIME=2018-01-06 16:16:09.0, FEN_JI_HAO=8079},{FEN_PEI_STATE=已分配, ZU_NAME=1219摩卓租户2, CREATE_TIME=2017-11-01 00:00:00.0, FEN_JI_HAO=8076}]
		List<HashMap<String, Object>> content = iZiYuanService.selectFenJiHaoInXiTongForExportByParam(param);
		if (content == null || content.size() == 0) {
			result.put("msg", "fail");
			result.put("text", "没有可导出的内容！");
			result.put("code", 20000);
			return result;
		}
		log.info("HashMap<String, Object>类型导出列表的行数是>>>>>>>>>>>>>>>>>>>>:" + content.size());
		List<HashMap<String, String>> contents = new ArrayList<HashMap<String, String>>();
		// 遍历，把HashMap<String, Object>变成HashMap<String, String>
		for (HashMap map : content) {
			HashMap<String, String> hashMap = new HashMap<String, String>();
			Iterator it = map.entrySet().iterator();
			while (it.hasNext()) {
				Map.Entry entry = (Map.Entry) it.next();
				String key = (String) entry.getKey();
				Object value = entry.getValue();
				log.info("库里取出的HashMap<String, Object>的vaule的类型是====：" + value.getClass().getName());
				String sValue = value.toString();
				log.info("HashMap<String, Object>转换成HashMap<String, string>后vaule的类型是====："
						+ sValue.getClass().getName());
				hashMap.put(key, sValue);
			}
			contents.add(hashMap);
		}
		log.info("转换成HashMap<String, String>类型导出列表的行数是>>>>>>>>>>>>>>>>>>>>:" + contents.size());
		String[] headers = new String[] { "分机号", "所属租户", "分配状态", "创建时间" };
		String[] keys = new String[] { "FEN_JI_HAO", "ZU_NAME", "FEN_PEI_STATE", "CREATE_TIME" };
		// 后台打印
		List list = Arrays.asList(headers);
		List list2 = Arrays.asList(keys);
		//log.info("contents是：" + contents);
		log.info("headers是：" + list);
		log.info("keys是：" + list2);

		SXSSFWorkbook wb = null;
		try {
			wb = MyExport.moreDataExportExcel(headers, keys, contents);
			response.setContentType("application/nd.ms-excel;charset=UTF-8");
			String fileName = new String("分机号信息.xlsx");
			String codedfilename = MyExport.processFileName(request, fileName);// 文件名乱码处理
			String headStr = "attachment; filename=\"" + codedfilename + "\"";
			response.setHeader("Content-Disposition", headStr);
			wb.write(response.getOutputStream());
		} catch (Exception e) {
			e.printStackTrace();
			result.put("msg", "fail");
			result.put("text", "导出失败！");
			result.put("code", 20000);
		} finally {
			try {
				wb.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		result.put("msg", "success");
		result.put("text", "导出成功！");
		result.put("code", 20000);
		return result;
	}
//导出excel工具类
@Component
public class MyExport {
	/**
	 * 导出Excel的公用方法, 需要保证Headers的数量等于内容的数量 数据量大时
	 * @param headers
	 * @param contents
	 * * @param keys contents的map的key
	 * @throws Exception 
	 * @throws DnnException 
	 * @remark create 2017-12-26 陈丽娟
	 */
	@SuppressWarnings("deprecation")
	public static SXSSFWorkbook moreDataExportExcel(String[] headers, String[] keys, List<HashMap<String, String>> contents) throws Exception
	{
	    if (headers == null || contents == null || keys == null)
	    {
	    	throw new Exception("导出时存在空指针......");
	    }
	    // 第一步，创建一个webbook，对应一个Excel文件  
	    SXSSFWorkbook xwb = new SXSSFWorkbook(10000);  
        // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet  
        SXSSFSheet sheet = xwb.createSheet("列表1");  
        // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short  
        SXSSFRow row = sheet.createRow((int) 0);  
        // 第四步，创建单元格，并设置值表头 设置表头居中  
        for (int i = 0; i < headers.length; i++)
        {
            SXSSFCell cell = row.createCell((short) i);  
            cell.setCellValue(headers[i]);  
        }
        
        int currentRowNum = 1;
        // 向单元格中添加数据
        for (int i = 0; i < contents.size(); i++)  
        {  
            row = sheet.createRow((int) currentRowNum++);
            Map<String, String> result = contents.get(i);
            // 第四步，创建单元格，并设置值
            
            for (int j = 0; j < keys.length; j++)
            {
                row.createCell((short) j, CellType.STRING).setCellValue(result.get(keys[j]));
            }
        }
        return xwb;
	}
}