EasyExcel的简单导出
Controller层代码
@GetMapping("/download")
public void download(String name, HttpServletResponse response) {
fileManager.download(name, response);
}
Service处理代码
public void download(String name, HttpServletResponse response) {
try {
String template_path = "E://template//模板文件.xlsx";
if (!new File(template_path).exists()) {
String message = "模板文件不存在,路径:" + template_path;
log.error(message);
throw new XException(message);
}
String strFileName = String.format("%s_导出文件.xlsx", name);
List<SysColDto> sysColDtos = getData();
List<SysColExcelDto> list = new ArrayList<>();
for (SysColDto sysColDto : sysColDtos) {
SysColExcelDto sysColExcelDto = new SysColExcelDto();
sysColExcelDto.setModname(sysColDto.getModname());
sysColExcelDto.setLname(sysColDto.getLname());
sysColExcelDto.setCname(sysColDto.getCname());
list.add(sysColExcelDto);
}
CustomRowStyleHandler customRowStyleHandler = new CustomRowStyleHandler(1, 9);
response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
response.setHeader("Access-Control-Expose-Headers", "Content-Disposition");
response.setHeader("Content-Disposition", String.format("attachment; filename=\"%s\"", new String(strFileName.getBytes(StandardCharsets.UTF_8), StandardCharsets.ISO_8859_1)));
EasyExcel.write(response.getOutputStream())
.registerWriteHandler(customRowStyleHandler)
.withTemplate(template_path)
.sheet()
.doFill(list);
} catch (IOException ex) {
throw new XException(ex.getMessage());
}
}
SysColExcelDto.java类
- 这是使用easyExcel提供的注解来标记字段填充行数,这种情况适用于自动生成,如果是填充excel(doFill方式)可以不用ExcelProperty注解标识,但是需要在模板中标记数据填充位置
- 模板标记数据填充位置标记如下,name和number对应的excel对象的字段名,自行修改即可
@Data
public class SysColExcelDto {
@ExcelProperty("对应信息采集组")
private String modname;
@ExcelProperty("对应采集栏目名称")
private String lname;
@ExcelProperty("对应系统中文字段")
private String cname;
}
CustomRowStyleHandler.java
- 这是自定义行风格处理器,是因为某些情况下需要加格式,看需求
- 除了RowWriteHandler 可以实现,还有CellWriteHandler可以实现,顾名思义是处理单元格
public class CustomRowStyleHandler implements RowWriteHandler {
private Integer cellIndexStart;
private Integer cellIndexEnd;
public CustomRowStyleHandler(Integer cellIndexStart, Integer cellIndexEnd) {
this.cellIndexStart = cellIndexStart;
this.cellIndexEnd = cellIndexEnd;
}
public CustomRowStyleHandler() {
}
@Override
public void afterRowDispose(WriteSheetHolder writeSheetHolder, WriteTableHolder writeTableHolder, Row row, Integer relativeRowIndex, Boolean isHead) {
Sheet sheet = writeSheetHolder.getSheet();
Workbook workbook = sheet.getWorkbook();
CellStyle cellStyle = workbook.createCellStyle();
cellStyle.setBorderLeft(BorderStyle.THIN);
cellStyle.setBorderTop(BorderStyle.THIN);
cellStyle.setBorderRight(BorderStyle.THIN);
cellStyle.setBorderBottom(BorderStyle.THIN);
for (Integer i = cellIndexStart; i <= cellIndexEnd; i++) {
Cell cell = row.getCell(i);
cell.setCellStyle(cellStyle);
}
}
}