Excel导出工具类，支持title别名

依赖

版本

<hutool.version>5.7.20</hutool.version>
<poi.ooxml>4.1.2</poi.ooxml>

<dependency>
	<groupId>cn.hutool</groupId>
	<artifactId>hutool-all</artifactId>
	<version>${hutool.version}</version>
</dependency>

<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>${poi.ooxml}</version>
</dependency>

工具类

/**
* Head 导出数据head (实体类字段)
* mapHead 字段的中文描述
* list 导出数据
* excelName excel的名称
**/
@Log4j2
public class ExportUtil {

    public static void exportTestByExcelWriter(List<String> Head,LinkedHashMap<String,String> mapHead,
                                  List<JSONObject> list,String excelName,
                                  HttpServletResponse response) {
        ServletOutputStream out = null;
        try {
            ExcelWriter writer = ExcelUtil.getWriter(true);
            writer.writeHeadRow(Head);
            writer.write(list);
            if (ObjectUtil.isNull(mapHead)) {
                unionHeader(Head, mapHead);
                writer.setCurrentRow(0);
                writer.writeHeadRow(Head);
            }
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(excelName, "UTF-8") + ".xlsx");
            response.setHeader("Access-Control-Expose-Headers", "fileName");
            response.setHeader("fileName", URLEncoder.encode(excelName, "UTF-8") + ".xlsx");
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            response.setContentType("application/octet-stream");
            response.setStatus(200);
            out = response.getOutputStream();
            writer.flush(out, true);
            writer.close();
        } catch (Exception e) {
            log.info(e);
            log.error("下载模板失败:{}", ExceptionUtil.stacktraceToOneLineString(e));
        } finally {
            IoUtil.close(out);
        }
    }
	
	/**
	* 从新覆盖excel的标题
	**/
    private static void unionHeader(List<String> mapHead, LinkedHashMap<String, String> boradHeads) {
        for (int i = 0; i < mapHead.size(); i++) {
            String key = mapHead.get(i);
            String name = boradHeads.get(key);
            if (ObjectUtil.isNotNull(name)) {
                mapHead.set(i, name);
            }
        }
    }
	
	/**
	* 导出另一种写法，不支持title别名
	**/
    public static void exportExcel(String sheetName, List<String> column, List<Map<String,Object>> data,
                                   HttpServletRequest request, HttpServletResponse response){
        //创建工作薄
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();
        //创建sheet
        HSSFSheet sheet = hssfWorkbook.createSheet(sheetName);
        // 表头
        HSSFRow headRow = sheet.createRow(0);
        for (int i = 0; i < column.size(); i++){
            headRow.createCell(i).setCellValue(column.get(i));
        }
        for (int i = 0; i < data.size(); i++) {
            HSSFRow dataRow = sheet.createRow(sheet.getLastRowNum() + 1);
            for (int x = 0; x < column.size(); x++) {
                dataRow.createCell(x).setCellValue(data.get(i).get(column.get(x))==null?"":data.get(i).get(column.get(x)).toString());
            }
        }
        response.setContentType("application/vnd.ms-excel");
        try {
            //获取浏览器名称
            String agent=request.getHeader("user-agent");
            String filename=sheetName+".xls";
            //不同浏览器需要对文件名做特殊处理
            if (agent.contains("Firefox")) { // 火狐浏览器
                filename = "=?UTF-8?B?"
                        + new BASE64Encoder().encode(filename.getBytes("utf-8"))
                        + "?=";
                filename = filename.replaceAll("\r\n", "");
            } else { // IE及其他浏览器
                filename = URLEncoder.encode(filename, "utf-8");
                filename = filename.replace("+"," ");
            }
            //推送浏览器
            response.setHeader("Content-Disposition","attachment;filename="+filename);
            hssfWorkbook.write(response.getOutputStream());
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

使用

/**
 *导出带有别名测试
 **/
@Override
public void exportCheckList(QueryListVO queryListVO, HttpServletResponse response) {
	 List<CheckStatusVO> data = checkStatusMapper.queryCheckList( queryListVO.getPageSize(),count.intValue(), queryListVO);
     if (data.size() <= 0) {
          return;
     }
     List<String> headList = Arrays.stream(data .get(0).getClass().getDeclaredFields()).map(Field::getName).collect(Collectors.toList());
     LinkedHashMap<String, String> headMap = new LinkedHashMap<>();
            for (String headName : headList) {
                switch (headName) {
                    case "chectDataId":
                        headMap.put(headName,"任务编码");
                        break;
                    case "applicationName":
                        headMap.put(headName,"应用名称");
                        break;
                    case "userName":
                        headMap.put(headName,"提交用户");
                        break;
                    case "status":
                        headMap.put(headName,"状态");
                        break;
                    case "createTime":
                        headMap.put(headName,"开始时间");
                        break;
                    case "updateTime":
                        headMap.put(headName,"结束时间");
                        break;
                    case "exceptionDetails":
                        headMap.put(headName,"失败明细");
                        break;
                }
            }
        List<JSONObject> jsonList = JsonUtil.toList(JsonUtil.toJSONString(list), JSONObject.class);
   		ExportUtil.exportTestByExcelWriter(headList,headMap,jsonList,StringPool.Export.DM_FACT_SHARE_DETAIL,response);
}

使用 不修改title别名测试

@Override
    public void reportLogByRoleImportPvUV(ReportLogDTO reportLogDTO, HttpServletRequest request, HttpServletResponse response) {
        try {
            List<Map<String, Object>> mapList = reportLogByRolePvUv(reportLogDTO);
            if (CollectionUtil.isEmpty(mapList)) {
                return;
            }
            List<String> headList = new ArrayList<>();
            headList.add("Role");
            headList.add("Report Name");
            headList.add("Role");
            headList.add("PV");
            headList.add("UV");
//            List<JSONObject> jsonList = JsonUtil.toList(JsonUtil.toJSONString(mapList), JSONObject.class);
//            ExportUtil.exportTest(headList,null,jsonList,"报表访问量访问明细-角色",response);
            ExportUtil.exportExcel("报表访问量访问明细-角色",headList,mapList,request,response);
        }catch (Exception e) {
            log.error("导出失败:{}", ExceptionUtil.stacktraceToOneLineString(e));
        }

    }