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();}}}

• 导出的效果