EasyExcel文件导出
EasyExcel导出工具类
public class EasyExcelExportUtil {
private static final short FONT_SIZE_TEN = 10;
private static final short FONT_SIZE_ELEVEN = 11;
public static void export(HttpServletResponse response, List<? extends Object> dataList, String fileName, String sheetName, Class clazz) throws Exception {
HorizontalCellStyleStrategy horizontalCellStyleStrategy = getStyle();
response.setHeader("Content-Type", "application/vnd.ms-excel");
response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "UTF-8") + ExcelTypeEnum.XLSX.getValue());
response.setCharacterEncoding("UTF-8");
EasyExcel.write(response.getOutputStream(), clazz).excelType(ExcelTypeEnum.XLSX).sheet(sheetName).registerWriteHandler(horizontalCellStyleStrategy).doWrite(dataList);
}
private static HorizontalCellStyleStrategy getStyle() {
WriteFont writeFont = new WriteFont();
writeFont.setBold(false);
writeFont.setFontName("宋体");
writeFont.setFontHeightInPoints(FONT_SIZE_TEN);
WriteCellStyle headWriteCellStyle = new WriteCellStyle();
headWriteCellStyle.setWriteFont(writeFont);
headWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
WriteCellStyle contentWriteCellStyle = new WriteCellStyle();
contentWriteCellStyle.setBorderTop(BorderStyle.THIN);
contentWriteCellStyle.setBorderBottom(BorderStyle.THIN);
contentWriteCellStyle.setBorderLeft(BorderStyle.THIN);
contentWriteCellStyle.setBorderRight(BorderStyle.THIN);
contentWriteCellStyle.setHorizontalAlignment(HorizontalAlignment.CENTER);
contentWriteCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
contentWriteCellStyle.setWrapped(true);
contentWriteCellStyle.setWriteFont(writeFont);
return new HorizontalCellStyleStrategy(headWriteCellStyle, contentWriteCellStyle);
}
}
导出对象定义
@ExcelIgnoreUnannotated
@ContentRowHeight(25)
@HeadRowHeight(25)
public class ExcelDTO {
@ColumnWidth(20)
@ExcelProperty(value = "序号")
private Integer order;
@ColumnWidth(20)
@ExcelProperty(value = "状态")
private String state;
@ColumnWidth(20)
@ExcelProperty(value = "日期", converter = LocalDateConverter.class)
private LocalDate date;
}
特殊数据转换(这里以日期转换为例)
public class LocalDateConverter implements Converter<LocalDate> {
@Override
public Class<LocalDate> supportJavaTypeKey() {
return LocalDate.class;
}
@Override
public CellDataTypeEnum supportExcelTypeKey() {
return CellDataTypeEnum.STRING;
}
@Override
public LocalDate convertToJavaData(CellData cellData, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
return LocalDate.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd"));
}
@Override
public CellData convertToExcelData(LocalDate value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) throws Exception {
String localDate = DateUtils.formatLocalDate(value, "yyyy-MM-dd");
if (localDate == null) {
return new CellData("");
} else {
return new CellData(localDate);
}
}
}
Controller调用
@PostMapping("/excel/list")
public void getExcelList(@RequestBody PageQueryDTO queryDTO, HttpServletResponse response) {
List<ExcelDTO> dataList = this.excelService.queryExcelExportRecord(queryDTO);
String fileName = "导出数据";
String sheetName = "导出数据";
try {
EasyExcelExportUtil.export(response, dataList, fileName, sheetName, RemitPayBookExcelDTO.class);
} catch (Exception e) {
throw new BusinessException(CommonEnums.BUSINESS_ERROR, e);
}
}