Poi高效导出Excel模板

前言

   项目中做报表，导入导出Excel,是常见也是不可或缺的功能，本文讲解导出Excel的写法，以及导出大数据量（50万起步）的优化方案

Poi高效导出Excel模板

public void exportExcel(){
List<List<Object>> excelDataResult = new CopyOnWriteArrayList<>(new ArrayList<>()); //Excel对应行的数据
		final String EXCEL_NAME ="导出的表名";
		List<Map<String, Object>> tempDataList = getExportData(); //获取导出数据
		List<Object> headExcelKey = new ArrayList<>();   //获取导出表头key容器  目的为了动态获取对应数据
		List<Object> headExcelValue = new ArrayList<>(); //获取Excel表头value容器导出   目的作为表头
		getHeaderKeyAndValue(headExcelValue, headExcelKey); //获取excel的表头key与value
		excelDataResult.add(headExcelValue); //加入导出Excel头信息
		SXSSFWorkbook sxssfWorkbook = getSheetsData(excelDataResult, EXCEL_NAME, tempDataList, headExcelKey);//获取excelData
		exportRelationExcel(EXCEL_NAME, sxssfWorkbook); //导出excel
}

	private void exportRelationExcel(String EXCEL_NAME, SXSSFWorkbook sxssfWorkbook) {
		response.setContentType("application/vnd.ms-excel");
		String fileName = EXCEL_NAME+"-"+df.format((new Date()))+".xlsx";
		String finalFileName ;
		try {
			if (StringUtils.contains(userAgent, "MSIE")) {
				finalFileName = URLEncoder.encode(fileName, "UTF8");
			} else if (StringUtils.contains(userAgent, "Mozilla")) {
				finalFileName = new String(fileName.getBytes("GBk"), "ISO-8859-1");
			} else {
				finalFileName = URLEncoder.encode(fileName, "UTF8");
			}
			response.setHeader("Content-disposition", "attachment;filename=" + finalFileName);
			OutputStream outputStream = response.getOutputStream();
			sxssfWorkbook.write(outputStream);
			outputStream.flush();
			outputStream.close();
		} catch (IOException e) {
			logger.error("导出失败,失败原因:{}");e.printStackTrace();
		}
	}
	
	private SXSSFWorkbook getSheetsData(List<List<Object>> excelDataResult, String EXCEL_NAME, List<Map<String, Object>> tempDataList, List<Object> headExcelKey) {
		SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook();
		Sheet sheet = sxssfWorkbook.createSheet(EXCEL_NAME);
		if(!tempDataList.isEmpty()&&tempDataList.size()>0){
			tempDataList.forEach(tempData->{
				List<Object> headExcelData = new ArrayList<>();
				headExcelKey.forEach(key->{
					headExcelData.add(tempData.get(key));
				});
				excelDataResult.add(headExcelData);
			});
		}
		for (int i = 0; i < excelDataResult.size(); i++) {
			Row row = sheet.createRow(i);
			List<Object> cellValue = excelDataResult.get(i);
			for (int j = 0; j < cellValue.size(); j++) {
				row.createCell(j).setCellValue(String.valueOf(null==cellValue.get(j)?"":cellValue.get(j)));
			}
		}
		return sxssfWorkbook;
	}
	
	
	private void getHeaderKeyAndValue(List<Object> headExcelValue, List<Object> headExcelKey) {
		Map<String,Object> headerMap = new LinkedHashMap<String,Object>();
		headerMap.put("name","姓名");
		headerMap.put("age","年龄");
		headerMap.put("phone","手机号码");
		headerMap.put("city","所在城市");
		headerMap.put("area","地区");
		headerMap.put("status","状态");
		headerMap.put("crateTime","创建日期");
		headerMap.put("createBy","创建人");
		headerMap.put("updateBy","修改人");
		headerMap.put("updateTime","更新日期");
		for(Map.Entry<String,Object> entry: headerMap.entrySet()){
			String key = entry.getKey();
			Object value = entry.getValue();
			headExcelValue.add(value);
			headExcelKey.add(key);
		}
	}


private List<Map<String, Object>> get() {
		String queryExportDataSql = "select name ,age,phone,city,area,status,crate_time as crateTime,create_by as createBy,update_by as updateBy,update_time as updateTime from user_info";
		return jdbcTemplate.queryForList(queryRelationSql);
	}