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();
}
}
}
- 导出的效果
