excel自定义导出实现（使用反射）

前言

项目中接到需求，需要对导出的字段进行自定义导出，用户可在前端选择自定义导出的字段（如图），实现过程做以下记录，仅供参考；
在这里插入图片描述

思路

跟前端约定好所有要导出的字段名称(headName)跟对应的实体名称(fieldName),勾选导出的字段后就传字段到后端，后端根据反射去匹配要导出的字段最后输出；

实现

约定导出的字段名称、实体名
导出字段实体

@Data
@AllArgsConstructor
@NoArgsConstructor
public class ApproveExcelHeader {
    /**
     * 要导出的字段名称
     */
    private String fieldName;
    /**
     * 要导出的表头名称
     */
    private String headName;
    /**
     * 是否导出
     */
    private String disabled;
}

定义接口

 /**
     * 根据前端传入的字段自定义导出的列
     * @param dto
     * @throws IOException
     * @throws NoSuchFieldException
     * @throws IllegalAccessException
     */
    @ApiOperation("导出")
    @PostMapping("/exportExcel")
    public void exportExcel(@RequestBody aqglRiskQuarterApproveDto dto, HttpServletResponse response) throws IOException, NoSuchFieldException, IllegalAccessException {
        Assert.notNull(dto.getRiskCycle(), "周期不能为空！");
        List<ApproveExcelHeader > exportFiledList =dto.getExportFiled();
        List<String> headNamelist = exportFiledList.stream().map(ApproveExcelHeader ::getHeadName).collect(Collectors.toList());
        if(headNamelist.isEmpty()){
            throw new BusinessException("要导出的字段不能为空！");
        }
        List<Map<String, Object>> exportList = approveService.riskQuarterRecognitionExportList(dto);
        List<String> headNameList = exportFiledList.stream().map(ApproveExcelHeader ::getHeadName).collect(Collectors.toList());
        String fileName ="导出文件名";
        ExportUtils.dynamicsExportExcel(fileName, headNameList, exportList,response);

    }

处理类
此处主要根据前端传进来的字段去查询出的字段利用反射匹配；

 public List<Map<String, Object>>riskQuarterRecognitionExportList(aqglRiskQuarterApproveDto dto) throws IllegalAccessException {      
        List<AqglRiskQuarterRecognitionExportVo> quarterRecognitionExportList = aqglRiskQuarterApproveMapper.riskQuarterRecognitionExportList(dto.getRiskCycle(), companyName, projectName, riskLevelCode, isPlanConditionCheck);
        List<Map<String, Object>> exportData = new ArrayList<>();
        if (!quarterRecognitionExportList.isEmpty()) {
            List<AqglQuarterApproveExcelHeader> exportFiledList = dto.getExportFiled();
            try {
                for (AqglRiskQuarterRecognitionExportVo quarterRecognitionExportVo : quarterRecognitionExportList) {
                	//此处根据反射去匹配查询出的所有字段跟前端传进来的字段
                    Class<?> voClass = quarterRecognitionExportVo.getClass();
                    Map<String, Object> row = new HashMap<>();
                    for (AqglQuarterApproveExcelHeader header : exportFiledList) {
                        Field field = voClass.getDeclaredField(header.getFieldName());
                        field.setAccessible(true);
                       // String fieldValue = field.get(quarterRecognitionExportVo).toString();
                        row.put(header.getHeadName(), field.get(quarterRecognitionExportVo));
                    }
                    exportData.add(row);
                }
            } catch (NoSuchFieldException e) {
                throw new BusinessException("未匹配到导出字段:" + e.getMessage());
            }
        }else {
            throw new BusinessException("未获取到需要导出的数据！");
        }
        return exportData;
    }

导出工具类（Workbook）

public static void dynamicsExportExcel(String fileName, List<String> headers, List<Map<String, Object>> data,HttpServletResponse response) throws IOException {
        Workbook workbook = new XSSFWorkbook(); // 创建工作簿
        Sheet sheet = workbook.createSheet("Sheet1"); // 创建工作表

        // sheet样式
        CellStyle sheetStyle = workbook.createCellStyle();
        sheetStyle.setBorderTop(BorderStyle.THIN); // 顶部边框
        sheetStyle.setBorderBottom(BorderStyle.THIN); // 底部边框
        sheetStyle.setBorderLeft(BorderStyle.THIN); // 左边边框
        sheetStyle.setBorderRight(BorderStyle.THIN); // 右边边框
        sheetStyle.setWrapText(true);  //设置自动换行
        sheetStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        sheetStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        // 创建表头
        Row headerRow = sheet.createRow(0);
        headerRow.setHeight((short) 1000);
        //表头样式
        CellStyle headerStyle = workbook.createCellStyle();
        headerStyle.setBorderTop(BorderStyle.THIN); // 顶部边框
        headerStyle.setBorderBottom(BorderStyle.THIN); // 底部边框
        headerStyle.setBorderLeft(BorderStyle.THIN); // 左边边框
        headerStyle.setBorderRight(BorderStyle.THIN); // 右边边框
        headerStyle.setWrapText(true);  //设置自动换行
        headerStyle.setAlignment(HorizontalAlignment.CENTER);//水平居中
        headerStyle.setVerticalAlignment(VerticalAlignment.CENTER);//垂直居中
        //设置字体
        XSSFFont font = (XSSFFont) workbook.createFont();
        font.setBold(true); // 设置加粗
        headerStyle.setFont(font);


        for (int i = 0; i < headers.size(); i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers.get(i));
            cell.setCellStyle(headerStyle);

        }


        // 填充数据、设置表格样式
        for (int i = 0; i < data.size(); i++) {
            Row dataRow = sheet.createRow(i + 1);
            Map<String, Object> rowData = data.get(i);
            for (int j = 0; j < headers.size(); j++) {
                Cell cell = dataRow.createCell(j);
                cell.setCellStyle(sheetStyle);
                Object value = rowData.get(headers.get(j));
                if (value != null) {
                    cell.setCellValue(value.toString());
                }
            }
        }

        //设置列宽度
        for (int i = 0; i < headers.size(); i++) {
            sheet.setColumnWidth(i,18 * 256);
        }

        ServletOutputStream outputStream =null;
        try {
            // 写入文件
            String name = URLEncoder.encode(fileName+".xlsx", "UTF-8");
            response.setCharacterEncoding("utf-8");
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename="+name);
            outputStream = response.getOutputStream();
            workbook.write(outputStream);

        } catch (IOException e) {
            e.printStackTrace();
            log.error("文件导出失败");
        } finally {
            try {
                workbook.close();
                log.info(fileName + "文件导出完成!");
            } catch (IOException e) {
                log.error("文件导出失败");
                e.printStackTrace();
            }
        }

    }