通过 @Excel 注解将要导出的字段标注在实体类上,没有标注的字段会自动忽略导出,通过工具类 ExcelUtils 实现共通处理,导出前只需要给出导出的列表数据,标注Excel注解即可。
下面给出关键代码片段:
注解:标注注解的字段将被导出
@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface Excel {
/**
* 列名
* @return
*/
String name() default "";
}
Controller:
/**
* 示例:导出 excel
* 必须设置 produces="application/octet-stream" 参数,否则swagger工具会出现乱码或不能下载提示报错
* @param response
*/
@ApiOperation(value = "示例:导出 excel", notes = "使用 hutool.ExcelUtil 工具导出", produces="application/octet-stream")
@GetMapping("/exportExcel")
public void exportExcel(HttpServletResponse response) {
List<ExportCustomersDTO> list = new ArrayList<>();
for (int i = 1; i < 10; i++) {
ExportCustomersDTO exportCustomersDTO = new ExportCustomersDTO();
exportCustomersDTO.setAddress("山东路 " + i + " 号");
exportCustomersDTO.setHomePhone("1350532000" + i);
exportCustomersDTO.setCompany("公司" + i);
list.add(exportCustomersDTO);
}
ExcelUtils.exportExcel(ExportCustomersDTO.class, list, response, "导出文件");
}
导出实体:
@Data
@Accessors(chain = true)
@EqualsAndHashCode(callSuper = true,onlyExplicitlyIncluded = true)
@ApiModel(value = "顾客导出实体")
public class ExportCustomersDTO extends BaseModel<ExportCustomersDTO> {
@Excel(name = "地址")
private String address;
/**
* 公司
*/
@Excel(name = "公司")
private String company;
/**
* 家庭电话
*/
@Excel(name = "家庭电话")
private String homePhone;
/**
* 业务电话
*/
private String businessPhone;
}
工具类ExcelUtils:
/**
* 导出 Excel
* 导出列的顺序与实体类属性的定义顺序一致
* @param cls 导出实体类
* @param exportData 导出数据
* @param response 响应对象
* @param fileName 导出文件名
* @param <T> 导出实体类型
*/
public static <T> void exportExcel(Class<T> cls, List<T> exportData, HttpServletResponse response, String fileName) {
ExcelWriter writer = ExcelUtil.getWriter(true);
Field[] declaredFields = cls.getDeclaredFields();
for (Field declaredField : declaredFields) {
if (declaredField.isAnnotationPresent(Excel.class)) {
Excel excelRow = declaredField.getAnnotation(Excel.class);
writer.addHeaderAlias(declaredField.getName(), excelRow.name());
}
}
// 一次性写出list内的对象到excel,使用默认样式,强制输出标题
writer.write(exportData, true);
// 设置浏览器响应的格式
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
ServletOutputStream out = null;
try {
fileName = URLEncoder.encode(fileName + "_", "UTF-8") + DateUtil.now();
response.setHeader("Content-Disposition", "attachment;filename=" + fileName + ".xlsx");
out = response.getOutputStream();
writer.flush(out, true);
} catch (UnsupportedEncodingException e) {
throw new RuntimeException(e);
} catch (IOException e) {
throw new RuntimeException(e);
} finally {
if (null != out) {
try {
out.close();
} catch (IOException e) {
throw new RuntimeException(e);
}
}
writer.close();
}
}